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