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