private void GenerateChart() { Excel.Worksheet activeSheet = null; Excel.Range selectedRange = null; Excel.Shapes shapes = null; Excel.Chart chart = null; Excel.ChartTitle chartTitle = null; try { activeSheet = (Excel.Worksheet)(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet; Excel.Range rangePivot; rangePivot = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A1", "AA2000"); // selectedRange = (Excel.Range)(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.Selection; selectedRange = IdentifyPivotRanges(rangePivot); shapes = activeSheet.Shapes; shapes.AddChart2(Style: 201, XlChartType: Excel.XlChartType.xlColumnClustered, Left: 480, Top: 190, Width: 450, Height: Type.Missing, NewLayout: true).Select(); chart = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveChart; chart.HasTitle = false; // chart.ChartTitle.Text = "Threats History"; chart.ChartArea.Interior.Color = System.Drawing.Color.FromArgb(242, 244, 244); // Change chart to light gray // chart.ChartArea.Interior.Color = System.Drawing.Color.FromRgb(0, 255, 0); chart.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowValue, true, true, true, true, true, true, true, true, true); // Turn on data labels chart.HasLegend = true; chart.SetSourceData(selectedRange); (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A2", "A2").Select(); } finally { if (chartTitle != null) { Marshal.ReleaseComObject(chartTitle); } if (chart != null) { Marshal.ReleaseComObject(chart); } if (shapes != null) { Marshal.ReleaseComObject(shapes); } if (selectedRange != null) { Marshal.ReleaseComObject(selectedRange); } } }
public void CreateChart(string pivotTableName, int chartTop, string cTitle) { Excel.Worksheet activeSheet = null; Excel.Range selectedRange = null; Excel.Shapes shapes = null; Excel.Chart chart = null; Excel.ChartTitle chartTitle = null; try { activeSheet = (Excel.Worksheet)(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet; Excel.Range rangePivot; rangePivot = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A1", "AA20000"); selectedRange = IdentifyPivotRangesByName(pivotTableName); shapes = activeSheet.Shapes; // Width original 255 if (Globals.ExcelVersion == "15.0" || Globals.ExcelVersion == "16.0") { // 204 is a nice style with shadow shapes.AddChart2(Style: 259, XlChartType: Excel.XlChartType.xlColumnClustered, Left: 10, Top: chartTop, Width: 450, Height: 210, NewLayout: true).Select(); } else { shapes.AddChart(XlChartType: Excel.XlChartType.xlColumnClustered, Left: 10, Top: chartTop, Width: 450, Height: 210).Select(); } chart = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveChart; chart.HasTitle = true; chart.ChartTitle.Text = cTitle; chart.ChartTitle.Format.TextFrame2.TextRange.Font.Caps = Microsoft.Office.Core.MsoTextCaps.msoNoCaps; chart.ChartArea.Interior.Color = System.Drawing.Color.FromArgb(242, 244, 244); // Change chart to light gray // chart.ChartArea.Interior.Color = System.Drawing.Color.FromRgb(0, 255, 0); // chart.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowValue, true, true, true, true, true, true, true, true, true); // Turn on data labels chart.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowValue, true, true, true, false, false, true, true, true, true); // Turn on data labels chart.SetSourceData(selectedRange); chart.HasLegend = false; chart.ApplyDataLabels(); if (Globals.ExcelVersion == "15.0" || Globals.ExcelVersion == "16.0") { chart.FullSeriesCollection(1).DataLabels.ShowValue = true; } else { chart.SeriesCollection(1).DataLabels.ShowValue = true; } if (pivotTableName == "PivotTableApplicationName") { chart.Axes(Excel.XlAxisType.xlCategory).TickLabels.Orientation = Excel.XlOrientation.xlUpward; } selectedRange.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; selectedRange.Borders[Excel.XlBordersIndex.xlEdgeRight].ColorIndex = 0; selectedRange.Borders[Excel.XlBordersIndex.xlEdgeRight].TintAndShade = 0; selectedRange.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin; selectedRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; selectedRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].ColorIndex = 0; selectedRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].TintAndShade = 0; selectedRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin; Globals.ChartBottom = (int)chart.ChartArea.Top + (int)chart.ChartArea.Height + 15; (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A2", "A2").Select(); // chart.Export(pivotTableName + ".png"); } catch (Exception ex) { // ex.Data.Add("ExcelHelper", "Error occurred in the CreateChart() method"); ex.Data.Add("ExcelHelper", " Error occurred in the CreateChart() method\r\n" + ex.Message); throw; } finally { if (chartTitle != null) { Marshal.ReleaseComObject(chartTitle); } if (chart != null) { Marshal.ReleaseComObject(chart); } if (shapes != null) { Marshal.ReleaseComObject(shapes); } if (selectedRange != null) { Marshal.ReleaseComObject(selectedRange); } } }