Create waterfall chart with legend customization in Aspose.Cells for Java

Aspose.Cells Product Family
1

Hi Team,

Could you please share the code snippet to render a waterfall chart for the below data using Aspose Cells Java version 25.6 ?

Period Savings
PrevYear -9
January 1
February 3
March 4
April -3
May 2
June 7
July 3
August 6
YTD 23

Output should be like
image.png (8.6 KB)

And I have below two requests

  1. I want to change the name of the legend from Increase/Decrease to Positive/Negative.
  2. Also, I would like to know if there is an option to hide the legend for Total alone without hiding the other 2 legends.

Sharing Excel file reference
AsposeWaterfallChart.zip (11.1 KB)

2

@VaradS

To create a waterfall chart with legend customization in Aspose.Cells for Java, you can use the following code snippet. This example demonstrates how to render the chart based on your provided data and customize the legend names.

import com.aspose.cells.*;

public class WaterfallChartExample {
    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);

        // Define your data
        String[] periods = {"PrevYear", "January", "February", "March", "April", "May", "June", "July", "August", "YTD"};
        double[] savings = {-9, 1, 3, 4, -3, 2, 7, 3, 6, 23};

        // Insert data into the worksheet
        for (int i = 0; i < periods.length; i++) {
            worksheet.getCells().get(i, 0).setValue(periods[i]);
            worksheet.getCells().get(i, 1).setValue(savings[i]);
        }

        // Add a waterfall chart
        int chartIndex = worksheet.getCharts().add(ChartType.WATERFALL, 5, 0, 15, 5);
        Chart chart = worksheet.getCharts().get(chartIndex);
        chart.getNSeries().add("B1:B10", true);
        chart.getNSeries().setCategoryData("A1:A10");

        // Customize the legend
        chart.getLegend().setIsVisible(true);
        chart.getLegend().setText("Positive/Negative");
        
        // Hide the legend for Total
        chart.getNSeries().get(0).getPoints().get(9).setLegendEntryVisible(false); // Assuming YTD is the last point

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

Key Customizations:

  1. Legend Name Change: The legend name is set to “Positive/Negative” using chart.getLegend().setText("Positive/Negative");.
  2. Hide Total Legend: The total legend entry (YTD) is hidden by setting its visibility to false.

Make sure to adjust the indices if your data structure changes. This code will create a waterfall chart based on your specified data and apply the requested customizations.

3

@VaradS,

Here is sample code (using Aspose.Cells) of how you can create the Waterfall chart in the Excel template file you provided.
e.g.,
Sample code:

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.getWorksheets().get(0);

// Define data
String[] periods = {"PrevYear", "January", "February", "March", "April", "May", "June", "July", "August", "YTD"};
double[] savings = {-9, 1, 3, 4, -3, 2, 7, 3, 6, 23};

worksheet.getCells().get("B2").putValue("Periods");
worksheet.getCells().get("C2").putValue("Savings");

// Insert data into the worksheet
for (int i = 0; i < periods.length; i++) {
            worksheet.getCells().get((i+2), 1).setValue(periods[i]);
            worksheet.getCells().get((i+2), 2).setValue(savings[i]);
}

// Add a waterfall chart
int chartIndex = worksheet.getCharts().add(ChartType.WATERFALL, 5, 2, 25, 15);
Chart chart = worksheet.getCharts().get(chartIndex);
chart.getNSeries().add("C3:C12", true);
chart.getNSeries().setCategoryData("B3:B12");

chart.getNSeries().get(0).setName("=Sheet1!$C$2");
chart.getNSeries().get(0).getDataLabels().setShowValue(true);

// Set chart title
chart.getTitle().setText("Waterfall Chart");

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

out1.zip (9.9 KB)

Now come to your desired legend customizations. How can you achieve the following legend customization tasks in MS Excel?

1. I want to change the name of the legend from Increase/Decrease to Positive/Negative.
2. Also, I would like to know if there is an option to hide the legend for Total alone without hiding the other 2 legends

I attempted to perform these tasks manually in MS Excel but was unable to do so. So, I cannot do the same with Aspose.Cells API. Would you kindly perform these tasks in MS Excel manually and create the chart with your desired legend customization, save the Excel file, and share the final file with us? We will then review how this can be done using the Aspose.Cells API.

4

@amjad.sahi
Thanks for your reply could you please check the latest file I’m uploading here,
I need sample code for it.
I have added first and last bar as a Total. and also I need positive (increase) value in green color and negative (decrease) value in red color.

AsposeWaterfallChart.zip (11.2 KB)

5

@VaradS,

Thanks for the sample file containing your desired chart with custom formatting.

See the following sample code to generate your desired Waterfall chart with custom formatting. You may refer to or try the sample code to accomplish your task.
e.g.,
Sample code:


        // Create a workbook and get the first worksheet
        Workbook workbook = new Workbook();
        Worksheet worksheet = workbook.getWorksheets().get(0);

        // Define your data
        String[] periods = {"PrevYear", "January", "February", "March", "April", "May", "June", "July", "August", "YTD"};
        double[] savings = {-9, 1, 3, 4, -3, 2, 7, 3, 6, 23};

        worksheet.getCells().get("B2").putValue("Periods");

        worksheet.getCells().get("C2").putValue("Savings");

        // Insert data into the worksheet
        for (int i = 0; i < periods.length; i++) {
            worksheet.getCells().get((i+2), 1).setValue(periods[i]);
            worksheet.getCells().get((i+2), 2).setValue(savings[i]);
        }

        // Add a waterfall chart
        int chartIndex = worksheet.getCharts().add(ChartType.WATERFALL, 5, 2, 25, 15);
        Chart chart = worksheet.getCharts().get(chartIndex);
        chart.getNSeries().add("C3:C12", true);
        chart.getNSeries().setCategoryData("B3:B12");

        chart.getNSeries().get(0).setName("=Sheet1!$C$2");
        chart.getNSeries().get(0).getDataLabels().setShowValue(true);

        //Set first and 10th chart (data) point in the first series as Total
        chart.getNSeries().get(0).getLayoutProperties().setSubtotals(new int[] { 0,9 });

        // Customize series for waterfall
        Series series = chart.getNSeries().get(0);

        // Set positive (increase) bar fill color: GREEN and negative as read
        series.setColorVaried(true);
        for (int i = 0; i < series.getPoints().getCount(); i++) {
            ChartPoint dp = series.getPoints().get(i);
            double value =  worksheet.getCells().get(i + 2, 2).getDoubleValue();

            if (value > 0 && i != 0 && i != 0  && i != 9) { // positive and not total bar
                dp.getArea().getFillFormat().setFillType(FillType.SOLID);
                dp.getArea().getFillFormat().getSolidFill().setColor(com.aspose.cells.Color.getGreen());
            } else if (value < 0) { // negative bar
                dp.getArea().getFillFormat().setFillType(FillType.SOLID);
                dp.getArea().getFillFormat().getSolidFill().setColor(com.aspose.cells.Color.getRed());
            }
        }

        // Make the first and last Total series in your desired colors.
        series.getPoints().get(0).getArea().getFillFormat().setFillType(FillType.SOLID);
        series.getPoints().get(0).getArea().getFillFormat().getSolidFill().setColor(com.aspose.cells.Color.getGray());

        series.getPoints().get(9).getArea().getFillFormat().setFillType(FillType.SOLID);
        series.getPoints().get(9).getArea().getFillFormat().getSolidFill().setColor(com.aspose.cells.Color.getGray());

        chart.getTitle().setText("Waterfall Chart");
        
        // Save the workbook
        workbook.save("d:\\files\\outChart1.xlsx");

outChart1.zip (10.1 KB)

Hope, this helps a bit.