Need to increase precision to more than two decimal points for Number type column

[Pravallika]

Hello team,

I want to increase the precision value to more than two decimal points. I read data from attached excel sheet where second column [Amount] is type 'Number". Here i input amount value with decimals "545324555.005435" , then after reading in aspose it gives only "545324555". So I set the cell data style as below :

Style style = worksheet.getCells().getCellStyle(i,1);

style.setNumber(4);

row.get(1).setStyle(style);

After this code change, i am able to read the decimal points but only up tp two recision. I need amount value which is exact value in excel sheet. No rounding of required. I had a requirement to make that column type as "Number".

[Amjad_Sahi]

Hi,

Well, you may use Style.setCustom() method as there is no constant value for Style.setNumber() that could support any numeric formatting with more than two decimal places. Please see the sample code below for your complete reference for your needs (I used your template file in the code segment to change the number formatting for the B4 cell to display all the value for your requirements):

e.g

Sample code:

//Instantiating a Workbook object

Workbook workbook = new Workbook(“Schedule+Import.xlsx”);

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

Cells cells = worksheet.getCells();

//Adding the current system date to “B4” cell

Cell cell = cells.get(“B4”);

Style style = cell.getStyle();

//Set the number formatting to 6 decimal places

style.setCustom("#,##0.000000");

cell.setStyle(style);

//Get the original underlyting value

System.out.println(cell.getStringValue());

//Saving the modified Excel file in default format

workbook.save(“out1.xlsx”);

Note: When you right click on B4 cell to display Format Cells… dialog box, you will see the “Number” category is selected in the output file.

Hope, this helps you a bit.

Thank you.

[Pravallika]

Hello Amjad, Thanks for the infomration . It worked well.

i have one more requirement : If i set style custom as style.setCustom("#,##0.000000"); , then i t is reading value up to 6 decimal points. But i need to read if excel amount has value 1005.9100000022188 . So Could you please provide me the solution which will auto set the custom/ adjust precision value .

[Amjad_Sahi]

Hi,

Well, there is no specific custom formatting string that you may apply to show all the decimal places accordingly but you may try to use “#.###############” as custom string which will accommodate your data to the maximum decimal places. Actually in MS Excel there is a limit (you may confirm it in MS Excel) i.e…, both integer part and decimal part cannot exceed to 15 chars in total. Using “#.###############” will trim any tailing zeros if there is any so it will suit your needs for most of your values.

For your given value “1005.9100000022188”, I am afraid, no formatting can print to give the exact same results (specially for decimal part) as per the original value, it is MS Excel’s limit (15 characters limit in count as mentioned above) and nothing to do with Aspose.Cells. So, when you use “#.###############” as custom string (via Style.setCustom() method), you would get “1005.91000000221” as the last two decimal points would be omitted. Moreover, if there is less value for integer part, so you may extend the decimal part accordingly.

Hope, you understand now.

Thank you.