protected void Page_Load(object sender, EventArgs e)
{
ChartWorks();
PivotWorks();
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 });
}
|