A combination chart uses two chart types to emphasize different kinds of data or to make comparing two sets of data easier. As in Excel, it is possible to create charts containing a combination of chart types with ExcelWriter. The ExcelApplication object/class can be used programmatically to do so.
The ExcelApplication COM object in v5 and later supports programmatic generation and manipulation of Column/Line combination charts in ASP (and ASP.NET using the SAExcelApplicationDotNet wrapper class). The native .NET and Java ExcelApplication classes introduced in v6 have full support for all types of combination charts.
By assigning one or more series of data to the secondary axis, the chart type for those series can be changed to a type different that that used by the series on the primary axis. This allows two different chart types to be simultaneously displayed within the same chart.
The following code samples demonstrate creating a Column/Line combination chart using the COM ExcelApplication object available in v5 and later and the native .NET and Java ExcelApplication classes introduced in v6.
Creating a combination chart
Input file
Both examples below start with the following Excel file, which contains two columns of data:
Example 1: Create a Column/Line chart using the COM ExcelApplication object (or SAExcelApplicationDotNet wrapper class in ASP.NET)
Key steps:
- Add a Chart of type saxlsColumnLineChart using Charts.Add.
- Add a Series for the bar chart and a Series for the line chart using SeriesCollection.Add.
- Change the AxisGroup property of the line series to saxlsSecondaryAxis.
- Change the ChartType property of the line series to saxlsLineChart.
|
[COM/ASP]
|
[C#]
|
[VB.NET]
|
|
'--- Instantiate ExcelApplication object
Set xla = Server.CreateObject("SoftArtisans.ExcelWriter")
'--- Open workbook which contains data
xla.Open Server.MapPath("in.xls"), False
'--- Get first worksheet
Set sheet1 = xla.Worksheets(1)
'--- Add combination chart
Set comboChart = sheet1.Charts.Add(saxlsColumnLineChart, 0, 7, 0, 19, 7)
'--- Set category axis data
comboChart.SeriesCollection.CategoryData = "A4:A6"
'--- Add series for Projected values as a column chart
Set barSeries = comboChart.SeriesCollection.Add("B4:B6", , 1)
barSeries.Name = "Projected"
'--- Add series for Actual values as a line chart
Set lineSeries = comboChart.SeriesCollection.Add("C4:C6", , 1)
lineSeries.Name = "Actual"
lineSeries.AxisGroup = saxlsSecondaryAxis
lineSeries.ChartType = saxlsLineChart
'--- Save workbook
xla.Save "out.xls", saOpenInExcel, saFileFormatDefault, Response
//--- Instantiate SAExcelApplicationDotNet class
SAExcelApplicationDotNet xla = new SAExcelApplicationDotNet();
//--- Open workbook which contains data
xla.Open(Server.MapPath("in.xls"), false);
//--- Get first worksheet
SAWorksheet sheet1 = xla.Worksheets[1];
//--- Add combination chart
IChart comboChart = sheet1.Charts.Add(ASXChartType.saxlsColumnLineChart,
0, 7, 0, 19, 7);
//--- Set category axis data
comboChart.SeriesCollection.CategoryData = "A4:A6";
//--- Add series for Projected values as a column chart
ISeries barSeries = comboChart.SeriesCollection.Add("B4:B6", -1, 1);
barSeries.Name = "Projected";
//--- Add series for Actual values as a line chart
ISeries lineSeries = comboChart.SeriesCollection.Add("C4:C6", -1, 1);
lineSeries.Name = "Actual";
lineSeries.AxisGroup = ASXAxisGroup.saxlsSecondaryAxis;
lineSeries.ChartType = ASXChartType.saxlsLineChart;
//--- Save workbook
xla.Save("out.xls", SASaveMethod.saOpenInExcel,
SAFileFormat.saFileFormatDefault, Response);
'--- Instantiate SAExcelApplicationDotNet class
Dim xla As New SAExcelApplicationDotNet
'--- Open workbook which contains data
xla.Open(Server.MapPath("in.xls"), False)
'--- Get first worksheet
Dim sheet1 As SAWorksheet = xla.Worksheets(1)
'--- Add combination chart
Dim comboChart As IChart = sheet1.Charts.Add(ASXChartType.saxlsColumnLineChart, _
0, 7, 0, 19, 7)
'--- Set category axis data
comboChart.SeriesCollection.CategoryData = "A4:A6"
'--- Add series for Projected values as a column chart
Dim barSeries As ISeries = comboChart.SeriesCollection.Add("B4:B6", -1, 1)
barSeries.Name = "Projected"
'--- Add series for Actual values as a line chart
Dim lineSeries As ISeries = comboChart.SeriesCollection.Add("C4:C6", -1, 1)
lineSeries.Name = "Actual"
lineSeries.AxisGroup = ASXAxisGroup.saxlsSecondaryAxis
lineSeries.ChartType = ASXChartType.saxlsLineChart
'--- Save workbook
xla.Save("out.xls", SASaveMethod.saOpenInExcel, _
SAFileFormat.saFileFormatDefault, Response)
|
Example 2: Create a Column/Line chart using the native .NET or Java ExcelApplication class
Key steps:
- Create a Chart of type ChartType.Column.Clustered using Charts.CreateChart().
(We are using ChartType.Column.Clustered in this example, but you can use any available chart type.)
- Create a Series for the bar chart and a Series for the line chart using SeriesCollection.CreateSeries().
- Change the AxisType property of the line series to AxisType.Secondary.
- Change the ChartType property of the line series to ChartType.Line.StandardLine.
(Unlike when using the COM ExcelApplication object, this ChartType property can be set to any chart type available, allowing any combination of chart types on the primary and secondary axes.)
|
[C#]
|
[VB.NET]
|
[Java]
|
|
//--- Instantiate ExcelApplication class
ExcelApplication xla = new ExcelApplication();
//--- Open workbook
Workbook wb = xla.Open(Server.MapPath("in.xls"));
//--- Set first worksheet
Worksheet sheet1 = wb.Worksheets[0];
//--- Create combination chart
Chart comboChart = sheet1.Charts.CreateChart(ChartType.Column.Clustered,
sheet1.CreateAnchor(7, 0, 0, 0));
//--- Set category axis data
comboChart.SeriesCollection.CategoryData = "A4:A6";
//--- Create series for Projected values as a column chart
Series barSeries = comboChart.SeriesCollection.CreateSeries("B4:B6");
barSeries.NameFormula = "B3";
//--- Create series for Actual values as a line chart
Series lineSeries = comboChart.SeriesCollection.CreateSeries("C4:C6");
lineSeries.NameFormula = "C3";
lineSeries.AxisType = AxisType.Secondary;
lineSeries.ChartType = ChartType.Line.StandardLine;
//--- Save workbook
xla.Save(wb, Response, "out2.xls", false);
'--- Instantiate ExcelApplication class
Dim xla As New ExcelApplication()
'--- Open workbook
Dim wb As Workbook = xla.Open("in.xls")
'--- Set first worksheet
Dim sheet1 As Worksheet = wb.Worksheets(0)
'--- Create combination chart
Dim comboChart As Chart = sheet1.Charts.CreateChart(ChartType.Column.Clustered, _
sheet1.CreateAnchor(7, 0, 0, 0))
'--- Set category axis data
comboChart.SeriesCollection.CategoryData = "A4:A6"
'--- Create series for Projected values as a column chart
Dim barSeries As Series = comboChart.SeriesCollection.CreateSeries("B4:B6")
barSeries.NameFormula = "B3"
'--- Create series for Actual values as a line chart
Dim lineSeries As Series = comboChart.SeriesCollection.CreateSeries("C4:C6")
lineSeries.NameFormula = "C3"
lineSeries.AxisType = AxisType.Secondary
lineSeries.ChartType = ChartType.Line.StandardLine
'--- Save workbook
xla.Save(wb, Response, "out2.xls", False)
//--- Instantiate ExcelApplication class
ExcelApplication xla = new ExcelApplication();
//--- Open workbook
Workbook wb = xla.open(request.getRealPath("in.xls"));
//--- Set first worksheet
Worksheet sheet1 = wb.getWorksheet(0);
//--- Create combination chart
Chart comboChart = sheet1.getCharts().createChart(ChartType.Column.Clustered,
sheet1.createAnchor(7, 0, 0, 0));
//--- Set category axis data
comboChart.getSeriesCollection().setCategoryData("A4:A6");
//--- Create series for Projected values as a column chart
Series barSeries = comboChart.getSeriesCollection().createSeries("B4:B6");
barSeries.setNameFormula("B3");
//--- Create series for Actual values as a line chart
Series lineSeries = comboChart.getSeriesCollection().createSeries("C4:C6");
lineSeries.setNameFormula("C3");
lineSeries.setAxisType(AxisType.Secondary);
lineSeries.setChartType(ChartType.Line.StandardLine);
//--- Save workbook
xla.save(wb, response, "out2.xls", false);
|
Output file
Both examples result in output similar to the following:
Modifying an existing combination chart
Using the COM ExcelApplication object, you cannot access any existing charts on a worksheet. However, with the native .NET or Java ExcelApplication class, existing charts (including combination charts) can be accessed and modified. Simply use the Worksheet.Charts collection (.NET) or Worksheet.getCharts().getChart() method (Java) to access a chart, as you would any other.
For example:
|
[C#]
|
[VB.NET]
|
[Java]
|
|
//--- Instantiate ExcelApplication class
ExcelApplication xla = new ExcelApplication();
//--- Open workbook
Workbook wb = xla.Open(Server.MapPath("in.xls"));
//--- Set first worksheet
Worksheet sheet1 = wb.Worksheets[0];
//--- Get existing combination chart
Chart comboChart = sheet1.Charts[0];
...
'--- Instantiate ExcelApplication class
Dim xla As New ExcelApplication()
'--- Open workbook
Dim wb As Workbook = xla.Open("in.xls")
'--- Set first worksheet
Dim sheet1 As Worksheet = wb.Worksheets(0)
'--- Get existing combination chart
Dim comboChart As Chart = sheet1.Charts(0)
...
//--- Instantiate ExcelApplication class
ExcelApplication xla = new ExcelApplication();
//--- Open workbook
Workbook wb = xla.open(request.getRealPath("in.xls"));
//--- Set first worksheet
Worksheet sheet1 = wb.getWorksheet(0);
//--- Get existing combination chart
Chart comboChart = sheet1.getCharts().getChart(0);
...
|
|
