...
Two-axis charts can add a lot to the visual presentation of your report. This demo shows revenue and cost data as columns, so they can easily be compared to each other for a specific time period. The profit, calculated by an Excel formula on the revenue and cost figures, is displayed as a line so that the trend is clear.
Code
Code Block |
---|
public class TwoAxisChart { private ExcelApplication xlw; private Workbook wb; private Worksheet wsData; private Worksheet wsBalance; private Chartsheet wsTwoAxisChart; private Chartsheet wsPieChart; private bool ProfitLine = true; /// <summary> /// Build the report with ExcelApplication /// </summary> public void GenerateReport() { // Create an instance of ExcelApplication and // open the template workbook. xlw = new ExcelApplication(); // Open the template workbook string templatePath = @"..\..\TemplatesExcelTemplateFiles\twoAxisTemplate.xlsx"; wb = xlw.Open(templatePath); // Get references to two existing worksheets // in the workbook. wsData = wb.Worksheets["FinData"]; wsBalance = wb.Worksheets["BalanceSheet"]; // Create the Two Axis chart this.CreateTwoAxisChart(ProfitLine); // Create the pie chart this.CreatePieChart(); wb.Worksheets.MoveSheet(wsPieChart, 0); // Move the sheets in the workbook wb.Worksheets.MoveSheet(wsTwoAxisChart, 0); // Save the report xlw.Save(wb, @"..\..\OutputExcelOutputFiles\TwoAxisChart_output.xlsx"); } /// <summary> Add a pie chart to the wsPieChart Chartsheet.</summary> private void CreatePieChart() { // Create a chart sheet for the pie chart. // Name the sheet "PieChartSheet" and insert it // as the first worksheet in the workbook. wsPieChart = wb.Worksheets.CreateChartsheet(ChartType.Pie.StandardPie, "PieChartSheet", 0); Chart chrtPie = wsPieChart.Chart; // Set category data string categoryFormula = "BalanceSheet!A8:A13,BalanceSheet!A17:A18"; chrtPie.SeriesCollection.CategoryData = categoryFormula; // Create a series object string seriesFormula = "BalanceSheet!B8:B13,BalanceSheet!B17:B18"; Series srsAssets = chrtPie.SeriesCollection.CreateSeries(seriesFormula); srsAssets.Name = "Assets"; // Show data labels on the pie chart as percentages srsAssets.DataLabels.ContainsValueAsPercentage = true; } /// <summary> Create a two-axis column/line chart on a new chartsheet.</summary> /// <param name="bProfitLine">If true, add the second axis to the chart.</param> private void CreateTwoAxisChart(bool ProfitLine) { // Create a chartsheet with a clustered column chart. // Name it "ChartSheet" and make it the first worksheet // in the workbook. wsTwoAxisChart = wb.Worksheets.CreateChartsheet(ChartType.Column.Clustered, "ChartSheet", 0); Chart chrtTwoAxis = wsTwoAxisChart.Chart; // Set category data and assign it to the primary axis chrtTwoAxis.SeriesCollection.CategoryData = "FinData!C3:F3"; // Create two series objects in the chart. // One for revenue and one for cost. Series srsRevenue = chrtTwoAxis.SeriesCollection.CreateSeries("FinData!C4:F4"); srsRevenue.NameFormula = "FinData!A4"; Series srsCost = chrtTwoAxis.SeriesCollection.CreateSeries("FinData!C5:F5"); srsCost.NameFormula = "FinData!A5"; // Create some colors to stylize the chart. Color clrRed = wb.Palette.GetClosestColor(255,48,58); Color clrBlue = wb.Palette.GetClosestColor(89,69,255); Color clrLighterBlue = wb.Palette.GetClosestColor(51, 155, 232); // Set the color of the bars of the revenue and cost series objects. srsRevenue.Interior.ForegroundColor = clrRed; srsCost.Interior.ForegroundColor = clrBlue; // Add Profit Line // Add a third series to display profit. Series srsProfit = chrtTwoAxis.SeriesCollection.CreateSeries("FinData!C6:F6"); // Display as a standard line, and set it to // the secondary axis. srsProfit.ChartType = ChartType.Line.StandardLine; srsProfit.AxisType = AxisType.Secondary; srsProfit.NameFormula = "FinData!A6"; // Change the appearance of the line chart's // data point markers. Set the type and color. srsProfit.DataPointMarker.MarkerType = DataPointMarker.ShapeType.X; srsProfit.DataPointMarker.BackgroundColor = clrLighterBlue; srsProfit.DataPointMarker.ForegroundColor = clrLighterBlue; // Change the color and weight of the line itself. srsProfit.Line.Visible = true; srsProfit.Line.Color = clrLighterBlue; srsProfit.Line.Weight = ChartLine.LineWeight.Medium; // Set the title of the chart and set the title font ChartText chrtTitle = chrtTwoAxis.Title; chrtTitle.Formula = "FinData!A1"; chrtTitle.Font.Name = "Trebuchet"; chrtTitle.Font.Size = 14; chrtTitle.Font.Bold = true; } } |
Downloads
- Template: TwoAxisTemplate.xlsx
- Output: TwoAxisChart_output.xlsx