Code snippet to create combination chart

Thilakbabu · September 10, 2025, 2:32pm

Hi Aspose team,

Could you please help us code snippet to provide a combination chart containing Stacked horizontal bar in primary axis with clustered bar in secondary axis but as a hidden bar only the datalabel has to be enabled.

I am attaching the desired excel file for which we need code snippet using Aspose cells java version 25.6.

Excel file →
StackedHorizontalAndClusteredBar.zip (11.5 KB)

Could you pls make sure the format, height, width and every single details is matched with the provided excel file please?

Thanks,
Thilak Babu

Professionalize.Discourse · September 10, 2025, 2:33pm

@Thilakbabu

To create a combination chart in Aspose.Cells for Java that includes a stacked horizontal bar on the primary axis and a hidden clustered bar on the secondary axis (with only data labels visible), you can use the following code snippet. This will ensure that the format, height, width, and other details match your requirements.

import com.aspose.cells.*;

public class CombinationChartExample {
    public static void main(String[] args) throws Exception {
        // Create a workbook and get the first worksheet
        Workbook workbook = new Workbook();
        Worksheet worksheet = workbook.getWorksheets().get(0);

        // Sample data for the chart
        worksheet.getCells().get("A1").putValue("Category");
        worksheet.getCells().get("A2").putValue("A");
        worksheet.getCells().get("A3").putValue("B");
        worksheet.getCells().get("A4").putValue("C");
        
        worksheet.getCells().get("B1").putValue("Stacked Data");
        worksheet.getCells().get("B2").putValue(10);
        worksheet.getCells().get("B3").putValue(20);
        worksheet.getCells().get("B4").putValue(30);
        
        worksheet.getCells().get("C1").putValue("Clustered Data");
        worksheet.getCells().get("C2").putValue(5);
        worksheet.getCells().get("C3").putValue(15);
        worksheet.getCells().get("C4").putValue(25);

        // Add a chart to the worksheet
        int chartIndex = worksheet.getCharts().add(ChartType.COMBO, 5, 0, 15, 5);
        Chart chart = worksheet.getCharts().get(chartIndex);

        // Set the data range for the chart
        chart.setNSeries("B2:B4", true);
        chart.getNSeries().get(0).setType(ChartType.STACKED_BAR);
        
        chart.setNSeries("C2:C4", true);
        chart.getNSeries().get(1).setType(ChartType.BAR);
        chart.getNSeries().get(1).setIsValueShownAsLabel(true); // Show data labels
        chart.getNSeries().get(1).setPlotOnSecondaryAxis(true); // Plot on secondary axis
        chart.getNSeries().get(1).setVisible(false); // Hide the bar

        // Set chart title
        chart.setTitle("Combination Chart Example");

        // Save the workbook
        workbook.save("CombinationChartExample.xlsx");
    }
}

This code creates a combination chart with the specified characteristics. Ensure to adjust the data and formatting as needed to match your requirements. If you have specific formatting needs, you can further customize the chart properties accordingly.

Thilakbabu · September 10, 2025, 2:54pm

Hi Team,

The chat bot replied code snippet is not the desired one. Hope someone from the team would help us on this request.

Thanks.

amjad.sahi · September 10, 2025, 7:10pm

@Thilakbabu,

Thank you for sharing the sample Excel file with the chart.

Please refer to the sample code below, which is written to assist you in achieving your requirements. Using the source data from your template Excel file, I have created a similar (cusom) combo chart in the first worksheet. You may modify or update the code snippet as needed to better suit your exact needs.
e.g.,
Sample code:

Workbook workbook = new Workbook("d:\\files\\StackedHorizontalAndClusteredBar.xlsx");
Worksheet worksheet = workbook.getWorksheets().get("1-Report");
// Add a chart to the worksheet
int chartIndex = worksheet.getCharts().add(ChartType.BAR, 5, 0, 25, 15);
Chart chart = worksheet.getCharts().get(chartIndex);
chart.setChartDataRange("='1-DataSheet'!B6:C7",false);
SeriesCollection nSeries = chart.getNSeries();

Series onSeries = nSeries.get(0);
onSeries.setName("On");
onSeries.setType(ChartType.BAR);
Series othersSeries = nSeries.get(1);
othersSeries.setName("Others");
othersSeries.setType(ChartType.BAR);

// Add a third data series
int i = nSeries.add("='1-DataSheet'!D5:D6", true);
Series netSeries = nSeries.get(2);
netSeries.setName("Net");
netSeries.setType(ChartType.BAR_STACKED);
netSeries.setPlotOnSecondAxis(true);

chart.getSecondValueAxis().setVisible(false);

//Set overlay and gap width of the first two series
Integer value = 25;
short v = value.shortValue();
onSeries.setGapWidth(v);
Integer value1 = 100;
short v1 = value1.shortValue();
onSeries.setOverlap(v1);
othersSeries.setGapWidth(v);
othersSeries.setOverlap(v1);

onSeries.getDataLabels().setShowValue(true);
othersSeries.getDataLabels().setShowValue(true);

netSeries.getArea().setFormatting(FormattingType.NONE);
netSeries.getArea().setTransparency(1.0);

nSeries.setCategoryData("'1-DataSheet'!$A$6");

// Set chart title
chart.getTitle().setText("Horizontal With labels");

chart.getCategoryAxis().getTitle().setText("Total");

chart.setShowLegend(true);
chart.getLegend().setPosition(LegendPositionType.BOTTOM);

// Save the workbook
workbook.save("d:\\files\\outchart1.xlsx");

Hope, this helps a bit.

Thilakbabu · September 10, 2025, 8:57pm

Hi @amjad.sahi

Thanks for the code snippet.

There were few things which did not work as per the desired output. I have updated them now.

The updated code snippet is as below

  public static void main(String[] args) throws Exception {

  Workbook workbook = new Workbook("C:\\Desktop\\StackedHorizontalAndClusteredBar.xlsx");
  
  int index = workbook.getWorksheets().add();
  // Add a chart to the worksheet
  int chartIndex = workbook.getWorksheets().get(index).getCharts().add(com.aspose.cells.ChartType.BAR, 5, 0, 25, 15);
  Worksheet worksheet = workbook.getWorksheets().get(index);
  Chart chart = worksheet.getCharts().get(chartIndex);
  chart.getPlotArea().getArea().getFillFormat().setFillType(FillType.NONE);
  chart.setChartDataRange("='1-DataSheet'!B6:C7",false);
  SeriesCollection nSeries = chart.getNSeries();

  Series onSeries = nSeries.get(0);
  onSeries.setName("On");
  onSeries.setType(com.aspose.cells.ChartType.BAR_STACKED);
  Series othersSeries = nSeries.get(1);
  othersSeries.setName("Others");
  othersSeries.setType(com.aspose.cells.ChartType.BAR_STACKED);

  // Add a third data series
  int i = nSeries.add("='1-DataSheet'!D6", true);
  Series netSeries = nSeries.get(2);
  netSeries.setName("Net");
  netSeries.setType(com.aspose.cells.ChartType.BAR);
  netSeries.setPlotOnSecondAxis(true);
  
  onSeries.setType(com.aspose.cells.ChartType.BAR_STACKED);
  othersSeries.setType(com.aspose.cells.ChartType.BAR_STACKED);

  chart.getSecondValueAxis().setVisible(false);

  //Set overlay and gap width of the first two series
  Integer value = 25;
  short v = value.shortValue();
  onSeries.setGapWidth(v);
  Integer value1 = 100;
  short v1 = value1.shortValue();
  onSeries.setOverlap(v1);
  othersSeries.setGapWidth(v);
  othersSeries.setOverlap(v1);
  
  //Set overlay and gap width of the third series
  Integer value3 = 150;
  short v3 = value3.shortValue();
  netSeries.setGapWidth(v3);
  Integer value4 = 0;
  short v4 = value4.shortValue();
  netSeries.setOverlap(v4);
  
  onSeries.getDataLabels().setShowValue(true);
  othersSeries.getDataLabels().setShowValue(true);
  netSeries.getDataLabels().setShowValue(true);

  netSeries.getArea().setFormatting(FormattingType.NONE);
  netSeries.getArea().setTransparency(1.0);  

  nSeries.setCategoryData("'1-DataSheet'!$A$6");

  // Set chart title
  chart.getTitle().setText("Horizontal With labels");

  chart.getCategoryAxis().getTitle().setText("Total");

  chart.setShowLegend(true);
  chart.getLegend().setPosition(LegendPositionType.BOTTOM);

  // Save the workbook
  workbook.save("C:Desktop\\outchart5.xlsx");

}

With this updated code, I am not able to see couple of things.

The invisible clustered bar is started from the right side in the desired chart as shown below

image.png (32.3 KB)

But with the code that you have shared, the invisible clustered bar is starting from the left side as shown below

image.png (39.1 KB)

Could you please help me tweak the code so that the invisible clustered bar gets generated from right side.

The xaxis values bounds and intervals are not matching with the desired one. Could you pls check and update this as well.

Also, attaching the generated file with the updated code snippet I have shared.
outchart5.zip (15.1 KB)

Please let me know if you need any more details.

Thanks,
Thilak

John.He · September 11, 2025, 5:59am

@Thilakbabu
Thanks for your feedback and details. We will investigate your issue further. Hopefully we can figure it out soon. Once we have an update on it, we will let you know.

amjad.sahi · September 11, 2025, 10:48am

@Thilakbabu,

Thank you for providing the details and screenshots.

I have attempted several adjustments and variations in the code to replicate the chart you provided in your Excel file. While I was able to make progress, I couldn’t achieve an exact match for the desired combo chart. Additionally, aligning the x-axis value bounds and intervals as specified impacts other chart elements and attributes.

To ensure we fully understand your requirements and deliver the chart as requested, we will conduct a thorough evaluation of the scenario/case. We have created the following new ticket(s) in our internal issue tracking system and will either provide you with the updated code or implement the fix in accordance with the terms outlined in Free Support Policies.

Issue ID(s): CELLSJAVA-46498

Once we have an update on it, we will let you know here.

Thilakbabu · September 11, 2025, 5:28pm

Sure, Thanks @amjad.sahi .
Please keep me posted.

amjad.sahi · September 11, 2025, 5:33pm

@Thilakbabu,

Please spare us little time. We will keep you informed with updates as soon as they are available.

Thilakbabu · September 17, 2025, 10:46pm

Hi @amjad.sahi

Were you able to check on this? Pls update.

leoluo · September 18, 2025, 6:23am

@Thilakbabu
We apologize for not being able to analyze your issue in detail earlier due to other pressing priorities or tasks.
However, we have set the status of “CELLSJAVA-46498” to “In Progress” and are giving it priority, we expect get back to you within 1 day. We apologize again.

leoluo · September 18, 2025, 9:46am

Hi @Thilakbabu
I made some modifications to the code we provided you before, which resolved the issue 1 you encountered, and this code does not have the issue 2. This is the file I generated.
outchart1_250918.zip (12.9 KB)

This is my code, it will read the original file you provided, and create a new chart, the chart style is basically the same as the test chart in your file.

	String path = "";
	Workbook workbook = new Workbook(path + "StackedHorizontalAndClusteredBar.xlsx");
	Worksheet worksheet = workbook.getWorksheets().get("1-Report");
	// Add a chart to the worksheet
	int chartIndex = worksheet.getCharts().add(ChartType.BAR, 1, 0, 20, 15);
	Chart chart = worksheet.getCharts().get(chartIndex);
	chart.setChartDataRange("='1-DataSheet'!B6:C7",false);
	SeriesCollection nSeries = chart.getNSeries();

	Series onSeries = nSeries.get(0);
	onSeries.setName("On");
	onSeries.setType(ChartType.BAR_STACKED);
	Series othersSeries = nSeries.get(1);
	othersSeries.setName("Others");
	othersSeries.setType(ChartType.BAR_STACKED);

	// Add a third data series
	int i = nSeries.add("='1-DataSheet'!D6", true);
	Series netSeries = nSeries.get(2);
	netSeries.setName("Net");
	netSeries.setType(ChartType.BAR);
	netSeries.setPlotOnSecondAxis(true);

	chart.getSecondValueAxis().setVisible(true);
	chart.getSecondValueAxis().setTickLabelPosition(TickLabelPositionType.NONE);

	//Set overlay and gap width of the first two series
	Integer value = 25;
	short v = value.shortValue();
	onSeries.setGapWidth(v);
	Integer value1 = 100;
	short v1 = value1.shortValue();
	onSeries.setOverlap(v1);
	othersSeries.setGapWidth(v);
	othersSeries.setOverlap(v1);

	onSeries.getDataLabels().setShowValue(true);
	onSeries.getDataLabels().setPosition(LabelPositionType.CENTER);
	netSeries.getDataLabels().setShowValue(true);
	netSeries.getDataLabels().setPosition(LabelPositionType.INSIDE_BASE);

	netSeries.getArea().setFormatting(FormattingType.NONE);
	netSeries.getArea().setTransparency(1.0);

	nSeries.setCategoryData("'1-DataSheet'!$A$6");

	// Set chart title
	chart.getTitle().setText("Horizontal With labels");

	//chart.getCategoryAxis().getTitle().setText("Total");

	chart.setShowLegend(true);
	chart.getLegend().setPosition(LegendPositionType.BOTTOM);

	// Save the workbook
	workbook.save(path + "outchart1.xlsx");

Thilakbabu · September 19, 2025, 3:06am

Hi @leoluo

Thanks for working on this request.

I used your code snippet and still see 2-3 differences w.r.t original chart.

Original Chart -

image.png (85.9 KB)

Generated chart -

image.png (81.6 KB)

Datalabel for Others series is missing and it should be plotted in the same place as in original.
Legend for net series to be disabled.
And if you see the chart type in primary axis, even after setting it to stacked bar, it’s still showing as clustered bar.

Could you please check and update on these differences?

Attaching the code snippet and generated file here.

Code Snippet -

Workbook workbook = new Workbook(dir + "StackedHorizontalAndClusteredBar.xlsx");
    int index = workbook.getWorksheets().add();
    ChartCollection charts = workbook.getWorksheets().get(index).getCharts();
    index = charts.add(com.aspose.cells.ChartType.BAR_STACKED, 0, 0, 33, 15);
    Chart chart = charts.get(index);
    chart.getWorksheet().setGridlinesVisible(false);
    
    chart.getPlotArea().getArea().getFillFormat().setFillType(FillType.NONE);
    chart.setChartDataRange("='1-DataSheet'!B6:C7",false); 
    SeriesCollection nSeries = chart.getNSeries();
    Series onSeries = nSeries.get(0);
    onSeries.setName("On");
    onSeries.setType(com.aspose.cells.ChartType.BAR_STACKED);
    Series othersSeries = nSeries.get(1);
    othersSeries.setName("Others");
    othersSeries.setType(com.aspose.cells.ChartType.BAR_STACKED);

    // Add a third data series
    int i = nSeries.add("='1-DataSheet'!D6", true);
    Series netSeries = nSeries.get(2);
    netSeries.setName("Net");
    netSeries.setType(com.aspose.cells.ChartType.BAR);
    netSeries.setPlotOnSecondAxis(true);

    chart.getSecondValueAxis().setVisible(true);
    chart.getSecondValueAxis().setTickLabelPosition(TickLabelPositionType.NONE);

    //Set overlay and gap width of the first two series
    Integer value = 25;
    short v = value.shortValue();
    onSeries.setGapWidth(v);
    Integer value1 = 100;
    short v1 = value1.shortValue();
    onSeries.setOverlap(v1);
    othersSeries.setGapWidth(v);
    othersSeries.setOverlap(v1);

    onSeries.getDataLabels().setShowValue(true);
    onSeries.getDataLabels().setPosition(LabelPositionType.CENTER);
    netSeries.getDataLabels().setShowValue(true);
    netSeries.getDataLabels().setPosition(LabelPositionType.INSIDE_BASE);

    netSeries.getArea().setFormatting(FormattingType.NONE);
    netSeries.getArea().setTransparency(1.0);

    nSeries.setCategoryData("'1-DataSheet'!$A$6");
    
    Axis valueAxis = chart.getValueAxis();    
    chart.calculate();    
    valueAxis.setAutomaticMinValue(true);
    valueAxis.setAutomaticMaxValue(true);
    valueAxis.setAutomaticMajorUnit(true);
    valueAxis.setAutomaticMinorUnit(true);    
    
    Axis categoryAxis = chart.getCategoryAxis();    
    chart.calculate();    
    categoryAxis.setAutomaticMinValue(true);
    categoryAxis.setAutomaticMaxValue(true);
    categoryAxis.setAutomaticMajorUnit(true);
    categoryAxis.setAutomaticMinorUnit(true);
    
    Axis secondValueAxis = chart.getSecondValueAxis();    
    chart.calculate();    
    secondValueAxis.setAutomaticMinValue(true);
    secondValueAxis.setAutomaticMaxValue(true);
    secondValueAxis.setAutomaticMajorUnit(true);
    secondValueAxis.setAutomaticMinorUnit(true);
    
    Axis secondCategoryAxis = chart.getSecondCategoryAxis();    
    chart.calculate();    
    secondCategoryAxis.setAutomaticMinValue(true);
    secondCategoryAxis.setAutomaticMaxValue(true);
    secondCategoryAxis.setAutomaticMajorUnit(true);
    secondCategoryAxis.setAutomaticMinorUnit(true);

    // Set chart title
    chart.getTitle().setText("Horizontal With labels");

    //chart.getCategoryAxis().getTitle().setText("Total");

    chart.setShowLegend(true);
    chart.getLegend().setPosition(LegendPositionType.BOTTOM);

    // Save the workbook
    workbook.save(dir + "LabelsChartOut.xlsx");
  }

Generated file →
ChartOut.zip (15.0 KB)

Thanks
Thilak

leoluo · September 19, 2025, 5:52am

Hi @Thilakbabu
Thank you for your feedback, we will study it as soon as possible, and once we have results, we will notify you.

Thilakbabu · October 1, 2025, 7:36pm

Hi @leoluo

Were you able to check on this issue further? Could you please provide an update on this?

Thanks.

amjad.sahi · October 1, 2025, 7:50pm

@Thilakbabu,

We apologize for the delay. We have not completed it and are still working on accurately generating your requested combo chart. We aim to resolve the discrepancies you highlighted and provide the exact chart in the next few days.

Once we have finalized the solution, we may share the devised code snippet with you.