Null Value Is Taken as Zero in Line Chart when Using Aspose.Slides for Java

Aspose.Slides Product Family
,
1

Hi Team,

When we try to create a LINE chart using aspose slides java with null values in the series, those are considered as zero values instead of null.

Output screenshot as below -

image.png (112.9 KB)

series 3 and cat 4 is a null but considered as zero.

ppt file -> AsposeLineChart-3.zip (32.9 KB)

Code used to create the same

Presentation pres = new Presentation();

ISlide slide = pres.getSlides().get_Item(0);

// Creating the default chart
IChart chart = pres.getSlides().get_Item(0).getShapes().addChart(ChartType.LineWithMarkers, 10f, 50f, 600f, 350f);

// Getting the default chart data worksheet index
int defaultWorksheetIndex = 0;

// Getting the chart data worksheet
IChartDataWorkbook fact = chart.getChartData().getChartDataWorkbook();

// Delete demo series
chart.getChartData().getSeries().clear();

// Add new series
chart.getChartData().getSeries().add(fact.getCell(defaultWorksheetIndex, 0, 1, "Series 1"), chart.getType());
chart.getChartData().getSeries().add(fact.getCell(defaultWorksheetIndex, 0, 2, "Series 2"), chart.getType());
chart.getChartData().getSeries().add(fact.getCell(defaultWorksheetIndex, 0, 3, "Series 3"), chart.getType());
chart.getChartData().getSeries().add(fact.getCell(defaultWorksheetIndex, 0, 4, "Series 4"), chart.getType());

// Adding new categories
chart.getChartData().getCategories().add(fact.getCell(defaultWorksheetIndex, 1, 0, "Cat 1"));
chart.getChartData().getCategories().add(fact.getCell(defaultWorksheetIndex, 2, 0, "Cat 2"));
chart.getChartData().getCategories().add(fact.getCell(defaultWorksheetIndex, 3, 0, "Cat 3"));
chart.getChartData().getCategories().add(fact.getCell(defaultWorksheetIndex, 4, 0, "Cat 4"));

// Take first chart series
IChartSeries series = chart.getChartData().getSeries().get_Item(0);
// Now populating series data
series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 1, 1, 20));
series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 1, 2, 50));
series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 1, 3, 30));
series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 1, 4, 40));
// Setting fill color for series
series.getFormat().getFill().setFillType(FillType.Solid);
series.getFormat().getFill().getSolidFillColor().setColor(Color.RED);

// Take second chart series
series = chart.getChartData().getSeries().get_Item(1);
// Now populating series data
series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 2, 1, 30));
series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 2, 2, 22));
series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 2, 3, 15));
series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 2, 4, 25));
series.getFormat().getFill().setFillType(FillType.Solid);
series.getFormat().getFill().getSolidFillColor().setColor(Color.GREEN);

// Take third chart series
series = chart.getChartData().getSeries().get_Item(2);
// Now populating series data
series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 3, 1, 25));
series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 3, 2, 19));
series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 3, 3, 18));
series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 3, 4, 40));
series.getFormat().getFill().setFillType(FillType.Solid);
series.getFormat().getFill().getSolidFillColor().setColor(Color.BLUE);

// Take third chart series
series = chart.getChartData().getSeries().get_Item(3);
// Now populating series data
series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 4, 1, 22));
series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 4, 2, 8));
series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 4, 3, null));
series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 4, 4, 26));
series.getFormat().getFill().setFillType(FillType.Solid);
series.getFormat().getFill().getSolidFillColor().setColor(Color.BLACK);

pres.save("AsposeLineChart-3.pptx", SaveFormat.Pptx);

Could you please check this and update ASAP ?

2

@Thilakbabu,
Yes, you can set null values in the series, and I see PowerPoint treats such values as zeroes. The issue is not related to Aspose.Slides. Could you please describe the purposes you want to achieve? We will do our best to help you.

3

@Thilakbabu,
We have linked this forum thread to the following ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): SLIDESJAVA-39282

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

4

Hi Team,

Is this released as part of any of the Aspose release? Please provide the aspose release version for this fix.

5

@Thilakbabu,
I apologize for not providing a response in a timely manner.

MS PowerPoint has an option that controls how to handle empty cells (it can be found under Chart Design > Select Data > Hidden and Empty Cells > Show empty cells as). Aspose.Slides allows you to specify this option using the DisplayBlanksAs property:

chart.setDisplayBlanksAs(DisplayBlanksAsType.Gap);

Also, null should be used to create charts with gaps, and the empty string ("") is not applicable. When an empty string is used, the corresponding Excel cell in the generated embedded workbook has a shared string value equal to the specified empty string. So technically the cell is not empty (MS Excel has similar behavior when working with such cells); therefore, there should not be any gaps.

Please try using the following code snippet:

Presentation pres = new Presentation();
try {
    ISlide sld = pres.getSlides().get_Item(0);
    IChart chart = sld.getShapes().addChart(ChartType.Line, 20, 85, 600, 400);
    int defaultWorksheetIndex = 0;
    IChartDataWorkbook fact = chart.getChartData().getChartDataWorkbook();
    chart.getChartData().getSeries().clear();

    // Add new series
    chart.getChartData().getSeries().add(fact.getCell(defaultWorksheetIndex, 0, 1, "Series 1"), chart.getType());
    chart.getChartData().getSeries().add(fact.getCell(defaultWorksheetIndex, 0, 2, "Series 2"), chart.getType());
    chart.getChartData().getSeries().add(fact.getCell(defaultWorksheetIndex, 0, 3, "Series 3"), chart.getType());
    chart.getChartData().getSeries().add(fact.getCell(defaultWorksheetIndex, 0, 4, "Series 4"), chart.getType());

    // Adding new categories
    chart.getChartData().getCategories().add(fact.getCell(defaultWorksheetIndex, 1, 0, "Category 1"));
    chart.getChartData().getCategories().add(fact.getCell(defaultWorksheetIndex, 2, 0, "Category 2"));
    chart.getChartData().getCategories().add(fact.getCell(defaultWorksheetIndex, 3, 0, "Category 3"));
    chart.getChartData().getCategories().add(fact.getCell(defaultWorksheetIndex, 4, 0, "Category 4"));

    // Take first chart series
    IChartSeries series = chart.getChartData().getSeries().get_Item(0);
    // Now populating series data
    series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 1, 1, 29.3));
    series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 2, 1, 35.5));
    series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 3, 1, 48.9));
    series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 4, 1, 54.5));

    // Take second chart series
    series = chart.getChartData().getSeries().get_Item(1);
    // Now populating series data
    series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 1, 2, 42.4));
    series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 2, 2, 28.6));
    series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 3, 2, 31.8));
    series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 4, 2, 0));

    // Take third chart series
    series = chart.getChartData().getSeries().get_Item(2);
    // Now populating series data
    series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 1, 3, 20.3));
    series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 2, 3, 12.8));
    series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 3, 3, 40.3));
    series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 4, 3, null));

    // Take fourth chart series
    series = chart.getChartData().getSeries().get_Item(3);
    // Now populating series data
    series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 1, 4, 12.3));
    series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 2, 4, null));
    series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 3, 4, 9.1));
    series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 4, 4, 22.5));

    // Specify Gap
    chart.setDisplayBlanksAs(DisplayBlanksAsType.Gap);
    chart.getLegend().setPosition(LegendPositionType.Bottom);

    chart.getAxes().getVerticalAxis().getFormat().getLine().getFillFormat().setFillType(FillType.NoFill);
    chart.getAxes().getHorizontalAxis().getFormat().getLine().getFillFormat().setFillType(FillType.NoFill);

    pres.save(folderPath + "output.pptx", SaveFormat.Pptx);
} finally {
    if (pres != null) pres.dispose();
}