Excel to CSV - Control rounding off behaviour during conversion

Aspose.Cells Product Family
1

Hi,

I have a number 999999999999999 in a worksheet. On converting it to CSV file, the number becomes 1000000000000000. How do I stop this rounding off?

Here is my code and file:

public static void main(String[] args) throws Exception {
	
	Workbook workbook = new Workbook("C:\\Users\\sandipan.ghosh\\Desktop\\Test15digits.xlsx");
	
	System.out.println("Fifteen 9s : " + BigDecimal.valueOf(Double.parseDouble(workbook.getWorksheets().get("Sheet1").getCells().get(0, 1).getValue().toString())).toPlainString());
	System.out.println("Fourteen 9s : " + BigDecimal.valueOf(Double.parseDouble(workbook.getWorksheets().get("Sheet1").getCells().get(1, 1).getValue().toString())).toPlainString());
	//The values printed above are fine.
	
	workbook.save("C:\\Users\\sandipan.ghosh\\Desktop\\test123.csv", SaveFormat.CSV);
	//The 15 digit value in the CSV is rounded which is wrong.
}

xlsx file upload is not being supported, so attached the screenshots of the file values.

Screenshot (20).png (37.2 KB)
Screenshot (19).png (53.9 KB)

2

And the issue is not there when I have 14 9’s.
It happens only in the case of 15 9’s.

3

@sandipanghosh111,

Thanks for the sample code, screenshots and details.

After an initial test, I am able to reproduce the issue as you mentioned by using your sample code with a simple template file (attached). I found 15-digit number is rounded off on saving workbook as CSV file format. I have logged a ticket with an id “CELLSJAVA-43001” for your issue. We will look into it soon.

Once we have an update on it, we will let you know.

4

@sandipanghosh111,
It is the default behavior of saving CSV file, and it is also MS Excel’s behavior to save such kind of value to CSV file. Aspose.Cells does provide user the ability to change it. You may try the option TxtSaveOptions.FormatStrategy with value CellValueFormatStrategy.None.

5

@sandipanghosh111,

And, please see the updated code segment that works fine as I tested with template file:
e.g
Sample code:

    Workbook workbook = new Workbook("C:\\Users\\sandipan.ghosh\\Desktop\\Test15digits.xlsx");
	
	System.out.println("Fifteen 9s : " + BigDecimal.valueOf(Double.parseDouble(workbook.getWorksheets().get("Sheet1").getCells().get(0, 1).getValue().toString())).toPlainString());
	System.out.println("Fourteen 9s : " + BigDecimal.valueOf(Double.parseDouble(workbook.getWorksheets().get("Sheet1").getCells().get(1, 1).getValue().toString())).toPlainString());
	//The values printed above are fine.
	
TxtSaveOptions options = new TxtSaveOptions (SaveFormat.CSV);
		options.setFormatStrategy(CellValueFormatStrategy.NONE);

	workbook.save("C:\\Users\\sandipan.ghosh\\Desktop\\test123.csv", options);
	
}
6

Thanks that worked fine :slight_smile:

7

@sandipanghosh111,

Good to know that your issue is sorted out by the suggested code segment. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.