Problem

With the ExcelApplication object, it is possible to create and manipulate charts, including bar charts with series on both the primary and secondary axes. In this example, we will cover how to use OfficeWriter to create a dual axis bar chart.

Note: The ExcelApplication object is only available in OfficeWriter Enterprise Edition (EE).

Solution

This example assumes that you are familiar with creating charts with ExcelApplication. If you’re not, we recommend that you check out Charts in ExcelApplication.

Let’s start with given data in worksheet Sheet1:

The following code snippet creates a bar chart with two series from that data:

Worksheet wkst = wb.Worksheets["Sheet1"];
Anchor anchr = wkst.CreateAnchor(5, 5, 0, 0);

//Create the chart
Chart chrt = wkst.Charts.CreateChart(ChartType.Bar.Clustered, anchr);

//Set the category axis data for the chart
chrt.SeriesCollection.CategoryData = "A2:A4";

//Create a cluster bar series on the primary axis
Series series1 = chrt.SeriesCollection.CreateSeries("B2:B4", ChartType.Bar.Clustered, AxisType.Primary);

//Create a cluster bar series on the secondary axis
Series series2 = chrt.SeriesCollection.CreateSeries("C2:C4", ChartType.Bar.Clustered, AxisType.Secondary);

If you are working with an existing chart, you can set the Series.AxisType property to AxisType.Secondary.

 series2.AxisType = AxisType.Secondary 
This will produce a chart that looks like the following:

The reason why only one series appears on the chart is because of an issue with how Excel displays bar and column charts on two axes.

To get around this, you should pad your data with zeroed values which act as placeholders for the bar or column chart series. Peltier Tech has a blog post with details on how to show a Column Chart on Two Axes. These principles also apply to bar charts.

This workaround can also be applied to OfficeWriter charts.

Dual Axis Chart Workaround

1. Pad the data to include the zeroed columns.

2. Add code for the place holder series on the primary and secondary axes


//Create a cluster bar series on the primary axis
Series series1 = chrt.SeriesCollection.CreateSeries("B2:B4", ChartType.Bar.Clustered, AxisType.Primary);

//Create the zeroed placeholder series for the primary axis
Series series2_pad = chrt.SeriesCollection.CreateSeries("C2:C4", ChartType.Bar.Clustered, AxisType.Primary);

//Create the zeroed placeholder series for the secondary axis
Series series1_pad = chrt.SeriesCollection.CreateSeries("D2:D4", ChartType.Bar.Clustered, AxisType.Secondary);

//Create a cluster bar series on the secondary axis
Series series2 = chrt.SeriesCollection.CreateSeries("E2:E4", ChartType.Bar.Clustered, AxisType.Secondary);
3. Add code to hide the legend entries for the placeholder series:
//Hide the placeholder legend entries
series2_pad.ShowLegendEntry = false;
series1_pad.ShowLegendEntry = false;
The final chart should display both series correctly on the separate axes:

Note: The change in color is because there are technically 4 series in the chart and Excel’s automatic coloring reflects this.