Empty Values are Considered as Zeros when Creating PowerPoint Line Chart in Java

Aspose.Slides Product Family
,
1

Hi @andrey.potapov, Thanks for the response.

Created a LINE chart using the example code, Aspose generated chart is not matching with the manually created one when empty values are present.

If we have a zero value, the line is populated till zero which is expected.
But when there are empty values, it is considered as zero with aspose which is not wrong.
Expected is no line for empty values.

Refer to the below files.
Manually created chart: LineChart-CreatedManually.zip (34.7 KB)
Chart created with Aspose: AsposeLineChart-01.zip (33.1 KB)

The below code is used for creating the line chart:

Presentation pres = new Presentation();
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, "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, 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, ""));

// 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, ""));
series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 3, 4, 9.1));
series.getDataPoints().addDataPointForLineSeries(fact.getCell(defaultWorksheetIndex, 4, 4, 22.5));

chart.getLegend().setPosition(LegendPositionType.Bottom);
pres.save("AsposeLineChart-01.pptx", SaveFormat.Pptx);

Could you please take a look and help me in resolving this issue?

2

@SrideviG,
Thank you for reporting on the issue. I’ve reproduced the problem with the empty values you described.

We have opened the following new 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.

3

Hi @andrey.potapov,

Any update on this issue?
We have a dependency on the Empty/null values for the charts and our clients are asking for this fix.
Could you please let us know in which release we can expect it?

Thanks in advance

4

@SrideviG,
I’ve requested plans for the issue from our development team. We will let you know soon.

5

@SrideviG,
PowerPoint has an option that controls how to handle empty cells (it can be found in Chart Design > Select Data > Hidden and Empty Cells > Show empty cells as). This option has different values for the expected (Gaps) and output (Zero, which is default value in Aspose.Slides) results.

Aspose.Slides allows to specify this option using DisplayBlanksAs property:

chart.setDisplayBlanksAs(DisplayBlanksAsType.Gap);

Also, null should be used to create chart with gaps and empty string (“”) is not applicable. When empty string is used the corresponding excel cell (in generated embedded workbook) has ‘shared string value’ (equal to the specified empty string). So technically the cell is not empty (MS Excel has similar behavior while working with such cells), therefore there shouldn’t be any gaps.

Please use the following code snippet to create a chart with the expected appearance:

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();
}

More examples: