private void AddBugChart( ExcelInterop.Worksheet sheet, int chartStartRow, string chartStartCol, int chartEndRow, string chartEndCol, string bugDataSource, string chartTitle) { ExcelInterop.Range bugChartRange = sheet.Range[sheet.Cells[chartStartRow, chartStartCol], sheet.Cells[chartEndRow, chartEndCol]]; ExcelInterop.ChartObjects charts = sheet.ChartObjects(Type.Missing) as ExcelInterop.ChartObjects; Utility.AddNativieResource(charts); ExcelInterop.ChartObject bugChartObject = charts.Add(0, 0, bugChartRange.Width, bugChartRange.Height); Utility.AddNativieResource(bugChartObject); ExcelInterop.Chart bugChart = bugChartObject.Chart;//设置图表数据区域。 Utility.AddNativieResource(bugChart); ExcelInterop.Range datasource = sheet.get_Range(bugDataSource);//不是:"B14:B25","F14:F25" Utility.AddNativieResource(datasource); bugChart.SetSourceData(datasource); bugChart.ChartType = ExcelInterop.XlChartType.xlColumnClustered; //bugChart.ChartWizard(datasource, XlChartType.xlColumnClustered, Type.Missing, XlRowCol.xlColumns, 1, 1, false, chartTitle, "", "", Type.Missing); bugChart.ApplyDataLabels();//图形上面显示具体的值 //将图表移到数据区域之下。 bugChartObject.Left = Convert.ToDouble(bugChartRange.Left) + 20; bugChartObject.Top = Convert.ToDouble(bugChartRange.Top) + 20; bugChartObject.Locked = false; bugChartObject.Select(); bugChartObject.Activate(); }
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); } } }
public static void Chart_Creation(int totalrun, int totalpass, int totalfail, int errors, int skipped) { xla = new Application(); Workbook wb = xla.Workbooks.Open(Constants.mREPORTPATH, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); Worksheet ws = (Worksheet)xla.ActiveSheet; Range range; // Now create the chart. ChartObjects chartObjs = (ChartObjects)ws.ChartObjects(Type.Missing); ChartObject chartObj = chartObjs.Add(Variables.Variables.chartDoubleLeft, Variables.Variables.chartDoubleTop, Variables.Variables.chartDoubleWidth, Variables.Variables.chartDoubleHeight); ExcelQ.Chart xlChart = chartObj.Chart; ws.Range[ws.Cells[Variables.Variables.wsCellsMergeStartObjectRowIndex, Variables.Variables.wsCellsMergeStartObjectColumnIndex], ws.Cells[Variables.Variables.wsCellsMergeEndObjectRowIndex, Variables.Variables.wsCellsMergeEndObjectColumnIndex]].Merge(); Range headerRange = ws.get_Range(Variables.Variables.headerRange); headerRange.Value = Variables.Variables.headerRangeValue; headerRange.Font.Color = Variables.Variables.headerRangeColor; headerRange.Font.Bold = true; headerRange.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter; headerRange.Interior.Color = Variables.Variables.headerRangeInteriorColor; //Adding table row and column ws.Cells[Variables.Variables.wsCellsTableRow1Column1StartObjectRowIndex, Variables.Variables.wsCellsTableRow1Column1StartObjectColumnIndex] = Variables.Variables.rowHeader1; ws.Cells[Variables.Variables.wsCellsTableRow1Column1StartObjectRowIndex, Variables.Variables.wsCellsTableRow1Column1StartObjectColumnIndex].Font.Bold = true; ws.Cells[Variables.Variables.wsCellsTableRow2Column1StartObjectRowIndex, Variables.Variables.wsCellsTableRow1Column1StartObjectColumnIndex] = Variables.Variables.rowHeader2; ws.Cells[Variables.Variables.wsCellsTableRow2Column1StartObjectRowIndex, Variables.Variables.wsCellsTableRow1Column1StartObjectColumnIndex].Font.Bold = true; ws.Cells[Variables.Variables.wsCellsTableRow3Column1StartObjectRowIndex, Variables.Variables.wsCellsTableRow1Column1StartObjectColumnIndex] = Variables.Variables.rowHeader3; ws.Cells[Variables.Variables.wsCellsTableRow3Column1StartObjectRowIndex, Variables.Variables.wsCellsTableRow1Column1StartObjectColumnIndex].Font.Bold = true; ws.Cells[Variables.Variables.wsCellsTableRow4Column1StartObjectRowIndex, Variables.Variables.wsCellsTableRow1Column1StartObjectColumnIndex] = Variables.Variables.rowHeader4; ws.Cells[Variables.Variables.wsCellsTableRow4Column1StartObjectRowIndex, Variables.Variables.wsCellsTableRow1Column1StartObjectColumnIndex].Font.Bold = true; ws.Cells[Variables.Variables.wsCellsTableRow1Column2EndObjectRowIndex, Variables.Variables.wsCellsTableRow1Column2EndObjectColumnIndex] = totalrun; ws.Cells[Variables.Variables.wsCellsTableRow2Column2EndObjectRowIndex, Variables.Variables.wsCellsTableRow1Column2EndObjectColumnIndex] = skipped; ws.Cells[Variables.Variables.wsCellsTableRow3Column2EndObjectRowIndex, Variables.Variables.wsCellsTableRow1Column2EndObjectColumnIndex] = totalfail; ws.Cells[Variables.Variables.wsCellsTableRow4Column2EndObjectRowIndex, Variables.Variables.wsCellsTableRow1Column2EndObjectColumnIndex] = totalpass; range = ws.UsedRange; Range fullTableRange = ws.get_Range(Variables.Variables.fullTableRangeStart, Variables.Variables.fullTableRangeEnd); Range pieChartContentTableRange = ws.get_Range(Variables.Variables.pieChartTableContentRangeStart, Variables.Variables.pieChartTableContentRangeEnd); fullTableRange.Style.Font.Name = Variables.Variables.fullTableRangeFontName; fullTableRange.Style.Font.Size = Variables.Variables.fullTableRangeFontSize; fullTableRange.Style.Interior.Pattern = XlPattern.xlPatternSolid; BorderAround(fullTableRange, 000000); xlChart.ChartType = XlChartType.xlPie; xlChart.ChartArea.Interior.Color = Color.WhiteSmoke; xlChart.ApplyLayout(1); xlChart.ChartTitle.Text = Variables.Variables.headerRangeValue; xlChart.SetSourceData(pieChartContentTableRange, Type.Missing); xlChart.SeriesCollection(1).Format.Fill.Transparency = 1; xlChart.ApplyDataLabels(ExcelQ.XlDataLabelsType.xlDataLabelsShowLabelAndPercent, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); ws.Shapes.Item("Chart 1").Top = 130; ws.Shapes.Item("Chart 1").Left = 1150; wb.Save(); wb.Close(true, Type.Missing, Type.Missing); xla.Quit(); releaseObject(ws); releaseObject(wb); releaseObject(xla); string path = Path.Combine(Constants.globalRecentResultsPath, " Automation_Test_Report" + ".xlsm"); //Creating the directory to store Log files if (!Directory.Exists(Constants.globalRecentResultsPath)) { Directory.CreateDirectory(Constants.globalRecentResultsPath); } File.Copy(Constants.mREPORTPATH, Constants.RecentREPORTPATH, true); }