private void ConfigureExcelChartFormat(string sheetName, string seriesName, Excel._Chart oChart)
{
//Serie format
Excel.Series oSeries = (Excel.Series)oChart.SeriesCollection(1);
oSeries.Name = seriesName;
oSeries.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlLine;
oSeries.Smooth = true;
oSeries.Border.ColorIndex = 3;
oSeries.Border.Weight = 3;
//Plot area format
oChart.PlotArea.Interior.ColorIndex = 0;
oChart.PlotArea.Border.ColorIndex = 5;
//X axis format
Excel.Axis xAxis = (Excel.Axis)oChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
xAxis.MajorGridlines.Border.ColorIndex = 5;
xAxis.Border.ColorIndex = 5;
xAxis.TickLabels.Font.Name = "Verdana";
xAxis.TickLabels.Font.Size = 8.0f;
xAxis.TickLabels.AutoScaleFont = false;
//Y axis format
Excel.Axis yAxis = (Excel.Axis)oChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
yAxis.TickLabels.Font.Name = "Verdana";
yAxis.TickLabels.Font.Size = 8.0f;
yAxis.TickLabels.AutoScaleFont = false;
yAxis.Border.ColorIndex = 5;
yAxis.MajorTickMark = Microsoft.Office.Interop.Excel.XlTickMark.xlTickMarkNone;
//Legend format
oChart.Legend.Font.Name = "Verdana";
oChart.Legend.Font.Size = 9.0f;
oChart.Legend.Font.Bold = true;
oChart.Legend.AutoScaleFont = false;
oChart.Legend.Position = Microsoft.Office.Interop.Excel.XlLegendPosition.xlLegendPositionTop;
//Chart format
oChart.HasTitle = false;
oChart.Location(Excel.XlChartLocation.xlLocationAsObject, sheetName);
}
private void CreateChart(Excel._Worksheet oSheet, BackgroundWorker backgroundWorker)
{
//Connect to workbook
Excel._Workbook oWB = (Excel._Workbook)oSheet.Parent;
//Get range of data in which the chart will reference to
Excel.Range oRng = oSheet.get_Range("A1", "C" + getLastUsedRow(oSheet));
//Create chart using ChartWizard
Excel._Chart oChart = (Excel._Chart)oWB.Charts.Add(Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
oChart.ChartWizard(oRng, Excel.XlChartType.xlColumnStacked, Missing.Value,
Excel.XlRowCol.xlRows, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value);
oChart.Location(Excel.XlChartLocation.xlLocationAsObject, oSheet.Name);
//Set location, height and width of the chart
oSheet.Shapes.Item(1).Top = 75;
oSheet.Shapes.Item(1).Left = 600;
oSheet.Shapes.Item(1).Width = 1304;
oSheet.Shapes.Item(1).Height = 419;
backgroundWorker.ReportProgress(95);
}
