cell.putValue rendering BigDecimal object as a String

Aspose.Cells Product Family
1

I was converting an version 2.5.4 pivot table project to 7.0.3 and I was experiencing my total columns on my pivot table being rendered as COUNT instead of SUM. Looking at the source data told me that the cells used for totaling were getting rendered as String objects. When I write these cells, I used a cell.putValue(XX) where XX was a BigDecimal object. Shouldn’t the API know that a BigDecimal object is a number, and to not write it as a String?

This didnt work…
cell = cells.get(rownum, cellnum++);
cell.putValue(sales);
Style style = cell.getStyle();
style.setNumber(38);
cell.setStyle(style);

This worked…
cell = cells.get(rownum, cellnum++);
cell.putValue(sales.doubleValue());
Style style = cell.getStyle();
style.setNumber(38);
cell.setStyle(style);

2

Hi,

Thanks for your posting.

Please let me know if sales a string value or double value? Do you want both of the code should run same? Please elaborate.

3

What I mean is that sales is a BigDecimal object. Since this is always a representation of a number, the API should treat it like a number and not write it to the cell as a string. There are also some other java objects that wrap primitives that should also be treated like a number in the API, like Double, Integer, and Float. I’m guessing that if I do a cell.putValue(x), and x is an object type and not a primitive, that the API is treating it like a string and it shouldn’t if it is a BigDecimal, Double, Integer, Float, etc object.

4

Hi,

Thanks, I got your point, I think it is a correct behavior, because bigdecimal can hold much larger value than double and it is also an object rather than a value type or built-in type.

When you simply put bigdecimal, it’s string value is stored and nothing is lost but if you store it as a double, its double value is rounded as illustrated in the screenshot.

Please see the sample test code below and the output xls file with screenshot.

Java


String path = “F:\Shak-Data-RW\Downloads\test.xls”;


Workbook workbook = new Workbook();


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


BigDecimal bdNumber = new BigDecimal(“1234567802930293567”);


Cell cellA1 = worksheet.getCells().get(“A1”);

Cell cellA2 = worksheet.getCells().get(“A2”);


cellA1.putValue(bdNumber);

cellA2.putValue(bdNumber.doubleValue());


Style style = cellA1.getStyle();

style.setNumber(38);

cellA1.setStyle(style);


style = cellA2.getStyle();

style.setNumber(38);

cellA2.setStyle(style);


workbook.save(path + “.out.xls”);

Screenshot:

5

My point is that if I have a BigDecimal, and use a cell.putValue(bigDecimal), that is should write the cell as a number, not a string. If I have a very large BigDecimal value, larger than double can hold, and wish to write it to a cell, how would I do this? If i simply put the BigDecimal object to the cell, the worksheet cell writes it as a string. I am forced to have to convert it to a primitive before I put it to the cell.The old version of the api (2.5.4) handled this correctly. It’s changed in 7.0.3.

6

Hi,

Here is a test code and screenshot for 2.5 version. The older version was incorrect. I will ask the development team to look into it and if they decide, they will change it accordingly.

This issue has been logged as CELLSJAVA-40035.

Java - 2.5v


String path = “F:\Shak-Data-RW\Downloads\test.xls”;


Workbook workbook = new Workbook();


Worksheet worksheet = workbook.getWorksheets().getSheet(0);


BigDecimal bdNumber = new BigDecimal(“1234567802930293567”);


Cell cellA1 = worksheet.getCells().getCell(“A1”);

Cell cellA2 = worksheet.getCells().getCell(“A2”);


cellA1.setValue(bdNumber);

cellA2.setValue(bdNumber.doubleValue());


Style style = cellA1.getStyle();

style.setNumber(38);

cellA1.setStyle(style);


style = cellA2.getStyle();

style.setNumber(38);

cellA2.setStyle(style);


workbook.save(path + “.out2.xls”);

Screenshot:

7

Hi,


By the way using the latest versions e.g v7.0.3, you may write your code like following, it will convert the BigDecimal object (string value) to Numeric value. You need to use Cell.putValue(string, boolean) overloaded version of the method, it works fine and may give you your desired output.

Sample code:

Java

String path = “F:\Shak-Data-RW\Downloads\test.xls”;


Workbook workbook = new Workbook();


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


BigDecimal bdNumber = new BigDecimal(“1234567802930293567”);


Cell cellA1 = worksheet.getCells().get(“A1”);

Cell cellA2 = worksheet.getCells().get(“A2”);


cellA1.putValue(bdNumber.toString(), true);

cellA2.putValue(bdNumber.doubleValue());


Style style = cellA1.getStyle();

style.setNumber(38);

cellA1.setStyle(style);


style = cellA2.getStyle();

style.setNumber(38);

cellA2.setStyle(style);


workbook.save(path + “.out.xls”);
8

Hi,

We will support to convert BigDecimal to double value when using Cell.putValue(BigDecimal) for your requirement soon. But if the value of BigDecimal object is out of range, the value set to cell will lose precision, just as what is shown in mshakeel.faiz’s reply for you. We will provide the fix before the end of this week.

9

I agree with the requestor of the feature.
We have spent a lot of men days on this Aspose version migration and we stumbled upon the exact same issue yesterday. Now using getDoubleValue(). But I was expecting ascendant compatibility…

I hope this migration is for the better.

10
Hi,

Please try the new fix v7.0.3.1. We have supported to set BigDecimal object as double value for a cell.

Thank you.
11

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

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