Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Intro

Excerpt

Create a chart with ExcelApplication that has two axes: primary axis and secondary axis.

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 = @"..\..\ExcelTemplateFiles\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, @"..\..\ExcelOutputFiles\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;
        }
    }

...