Display blank as gaps/zeroes

Aspose.Cells Product Family
1

Hi,


I am using aspose cells for Java. Below is the version that I got from the manifect file.
Implementation-Version: 7.2.2.1

Is there a way to treat empty as gaps/zeroes while drawing a chart? Attached is the way to do this with excel. Seems like aspose treats the blanks as zeroes by default.

Thanks,
Pravat
2

Hi,

Thanks for your question and using Aspose.Cells for Java.

Please download and use the latest version:
Aspose.Cells
for Java v7.3.1.6

Please try the property Chart.setPlotEmptyCellsType() method and see if it works fine.

Java.


chart.setPlotEmptyCellsType(PlotEmptyCellsType.ZERO);


If you still face any issue, please provide us your source and expected output xls/xlsx file.

We will look into it and help you asap.
3

Hi ,


Thanks for responding. The above solution worked fine for Excel 2010, but it is not working for 2007 version. Can you please help me how to fix this.

Thanks,
Pravat
4

Hi,

Thanks for your feedback.

Could you please provide us your sample runnable code replicating your issue with Ms-Excel 2007?

We will soon check it and if it is a bug, we will fix it asap.

5

Hi,


Thanks for your response. Here is the sample runnable code. Attached the input and output files.
Workbook workbook = new Workbook(“C:/Pravat/Eclipse_WS/AW/export/trunk/test/resource/Population-In.xlsx”);
ChartCollection charts = workbook.getWorksheets().get(0).getCharts();
Chart chart = charts.get(charts.add(ChartType.PIE, 0, 0, 15, 8));
Series series = chart.getNSeries().get(chart.getNSeries().add("=Sheet1!$K5:N5", false));
series.setType(ChartType.PIE);
series.setXValues("=Sheet1!$K4:N4");

chart.setPlotEmptyCellsType(PlotEmptyCellsType.NOT_PLOTTED);
series.getDataLabels().setShowValue(true);
workbook.save(“C:/Pravat/Eclipse_WS/AW/export/trunk/test/resource/Population-Out.xlsx”);
6

Hi,

Thanks for your posting a sample code.

We were able to replicate this issue using the latest version with the following given code.

I have attached both output files (xls/xlsx) and screenshot for your reference. As you can see in a screenshot. Xls format is working fine but Xlsx format is not working.

We will look into this issue and fix it. Once there is some update for you, we will let you know asap.

This issue has been logged as CELLSJAVA-40341.

Java


String filePath = “F:\Shak-Data-RW\Downloads\Population-In.xlsx”;


Workbook workbook = new Workbook(filePath);


ChartCollection charts = workbook.getWorksheets().get(0).getCharts();

Chart chart = charts.get(charts.add(ChartType.PIE, 0, 0, 15, 8));


Series series = chart.getNSeries().get(chart.getNSeries().add("=Sheet1!$K5:N5", false));

series.setType(ChartType.PIE);


series.setXValues("=Sheet1!$K4:N4");


chart.setPlotEmptyCellsType(PlotEmptyCellsType.NOT_PLOTTED);

series.getDataLabels().setShowValue(true);

workbook.save(filePath + “.out.xlsx”, SaveFormat.XLSX);

workbook.save(filePath + “.out.xls”, SaveFormat.EXCEL_97_TO_2003);

Screenshot:
7

Hi,


Please try this fix/version: Aspose.Cells for Java v7.3.2.3

Your issue should be fixed in it.

Also, you may try the new
fix and change the format to hide the zero value data labels by
dataLabels.setNumberFormat(“[=0]"";#,##0.0”).<o:p></o:p>

Thank you.
8

I tried this but it does not work with the attached version with excel 2007. However with the code


dataLabels.setNumberFormat("[=0]"";#,##0.0")

works. But I can’t use the above code as not always that I apply a number formatting to the data labels.(this is my requirement). I am looking for a way, whether or not a number formatting is applied, this should work when there is no value.
9

Hi Pravat,


I am afraid, at the moment you have to make use of our suggested line of code to cope with your issue. However, we will further look into it. I have logged your comments against your issue id. Our concerned developer will further investigate it and we will get back to you soon with latest update.

Thank you.
10

Hi,

I am waiting for this to be fixed. For accepting your solution, I need to buy a new license/upgrade. But I don’t think the solution provided is worth buying a new license as my existing code base was affected because of some previous upgrade.

Please let me know when this gets fixed properly so that I may consider buying a new license.

Thanks!!
11

Hi,


I have asked the concerned developer to update on it as you need a fix for it. I have also logged your comments against your existing issue “CELLSJAVA-40341”. Our concerned developer will look into it and once we have any update on it, we will let you know here.

Thank you.
12

The issues you have found earlier (filed as CELLSJAVA-40341) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.