Writing Something

Pivot table çok gelişmiş bir veri özetleme aracıdır. excelde open office calc’te yada başka bir elektronik tablolama programında eklenmiş olarak bulabilirsiniz. en genel olarak sıralama, veri sayma, veri işleme gibi özellikleri vardır.

Pivot tablolara matrix tablolarda diyebiliriz.

Bu makaledeki amacımız c# koduyla excel verisinden pivota tablosu ve grafik oluşturmak.

Visual Studio 2008 ile hazırlandı.

Kodları indirmek için Tıklayınız

Örneğin Kodları

protected void Page_Load(object sender, EventArgs e)
{
//System.Globalization.CultureInfo eskiKultur = System.Threading.Thread.CurrentThread.CurrentCulture;
//System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo(“en-US”);

ChartWorks();

PivotWorks();

//System.Threading.Thread.CurrentThread.CurrentCulture = eskiKultur;

Response.Write(“İşlem Tamamlandı…”);

}

private void PivotWorks()
{
Excel.Application xlApp = new Excel.Application();

if (xlApp == null)
{
throw new Exception(“Excel Başlatılamadı. Excel Kurulu Olmaya Bilir.”);
}

xlApp.Visible = true;
xlApp.DisplayAlerts = false;

Excel.Workbook wb = xlApp.Workbooks.Open(
Server.MapPath(ConfigurationManager.AppSettings["PivotFile"]),
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);

Excel.Worksheet ws = (Excel.Worksheet)wb.Sheets[1];

Excel.Range sourceData = ws.get_Range(“A1″, “G37″);

Excel.Worksheet ws2 = (Excel.Worksheet)wb.Sheets.Add(Missing.Value,
Missing.Value, Missing.Value, Missing.Value);

CreatePivotTable(wb, ws2, sourceData, “MARKA”, “TARİH”, “ADET”,
“Adet Toplamı”,
Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum);

wb.Close(true, Missing.Value, Missing.Value);
xlApp.Quit();
}

private void ChartWorks()
{
Excel.Application xlApp = new Excel.Application();

if (xlApp == null)
{
throw new Exception(“Excel Başlatılamadı. Excel Kurulu Olmaya Bilir.”);
}

xlApp.Visible = false;
xlApp.DisplayAlerts = false;

Excel.Workbook wb = xlApp.Workbooks.Open(
Server.MapPath(
ConfigurationManager.AppSettings["OkunacakExcelFile"]),
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);

Excel.Worksheet ws = (Excel.Worksheet)wb.Sheets.Add(Missing.Value,
Missing.Value, Missing.Value, Missing.Value);

Excel.Worksheet ws2 = (Excel.Worksheet)wb.Sheets[2];

Excel.Range ChartRange = (Excel.Range)ws2.get_Range(“A1″, “B15″);

CreateChart(wb, ws, ChartRange, “Grafik Nesnesi”,
Microsoft.Office.Interop.Excel.XlRowCol.xlRows,
10, 20, 700, 400);

wb.Close(true, Missing.Value, Missing.Value);
xlApp.Quit();
}

private void CreateChart(Excel.Workbook book, Excel.Worksheet sheet,
Excel.Range range, string name, Excel.XlRowCol plotby,
int top, int left, int width, int height)
{
Excel._Chart oChart;
oChart = (Excel._Chart)book.Charts.Add(Missing.Value, Missing.Value,
Missing.Value, Missing.Value);

oChart.ChartType =
Microsoft.Office.Interop.Excel.XlChartType.xlBarClustered;

oChart.SetSourceData(range, plotby);

oChart.ApplyDataLabels(
Microsoft.Office.Interop.Excel.XlDataLabelsType.xlDataLabelsShowLabel,
Missing.Value, Missing.Value, false, false, false,
true, false, false, Missing.Value);

oChart.PlotArea.Interior.Color = System.Drawing.ColorTranslator.ToOle(
System.Drawing.Color.White);
oChart.ChartArea.Shadow = true;

Excel.Axis axis =
(Excel.Axis)oChart.Axes(Excel.XlAxisType.xlValue,
Excel.XlAxisGroup.xlPrimary);

axis.TickLabels.Font.Size = 7;
axis.TickLabels.Font.Name = “Tahoma”;

Excel.Axis axis2 = (Excel.Axis)oChart.Axes(
Excel.XlAxisType.xlCategory,
Excel.XlAxisGroup.xlPrimary);

axis2.TickLabels.Font.Name = “Tahoma”;
axis2.TickLabels.Font.Size = 7;

oChart.Legend.Font.Size = 7;
oChart.Legend.Font.Name = “Tahoma”;
oChart.Legend.Position =
Microsoft.Office.Interop.Excel.XlLegendPosition.xlLegendPositionBottom;

oChart.Location(Excel.XlChartLocation.xlLocationAsObject, sheet.Name);

sheet.Shapes.Item(1).Top = top;
sheet.Shapes.Item(1).Left = left;
sheet.Shapes.Item(1).Width = width;
sheet.Shapes.Item(1).Height = height;
}

private void CreatePivotTable(Excel.Workbook workbook, Excel.Worksheet sheet,
Excel.Range range, string rowfiledname, string columnfieldname,
string valuefieldname, string valuelabel, Excel.XlConsolidationFunction function)
{
workbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase,
range).CreatePivotTable(sheet.get_Range(“A2″,
Type.Missing), “PivotTable”
+ sheet.Name, Type.Missing, Type.Missing);

Excel.PivotTable PT = (Excel.PivotTable)sheet.PivotTables(“PivotTable”
+ sheet.Name);
PT.NullString = “0″;
PT.GrandTotalName = “Toplam”;

Excel.PivotField _RowField = (Excel.PivotField)PT.PivotFields(rowfiledname);
_RowField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
_RowField.Position = 1;

Excel.PivotField _ColumnField =
(Excel.PivotField)PT.PivotFields(columnfieldname);
_ColumnField.Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
_ColumnField.Position = 1;

PT.AddDataField(PT.PivotFields(valuefieldname), valuelabel, function);

Excel.Range _grpRange = (Excel.Range)sheet.Cells[3, 2];

_grpRange.Group(Missing.Value, Missing.Value, 5,
new bool[] { false, false, false, false, true, false, true });

}