C Sharp ile Excel Grafiği ve Pivot Table Oluşturmak

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.

Excel PivotTable, C#

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

Excel Grafik, C#

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 });

}

Be Sociable, Share!

    Category: Csharp - C#, Excel - 4 comments »

    4 Responses to “C Sharp ile Excel Grafiği ve Pivot Table Oluşturmak”

    1. emre

      örneğin dosyası silinmiş, yenileyebilir misiniz.

    2. serdar

      linki düzelttim.

    3. ilham

      legend’lerin ismini nasil degistirdiyimizi soylleyebilirmisiniz?aradim bulamadim:((

    4. serdar

      legend’da gelen isimler datadan geliyor… satırlardaki değerleri değiştirsen yetmeli.

    Back to top