Free Support Forum - aspose.com

Remove Scientific format for numbers using Aspose.Cells for Java

Hi,

We are using a template file and a CSV (created at runtime through another application) to create a workbook then a full Excel file using Aspose Cells library. Our CSV has different columns that may or may not be present at runtime, which means that the columns in the output file are not known in advance when developing. A groovy/java code works for the most part, except for columns displaying contract numbers. The code ise based on the documentation and use TxtLoadOptions class.
TxtLoadOptions options = new TxtLoadOptions(LoadFormat.CSV);
options.setConvertNumericData(true);
options.setKeepPrecision(true);
options.setConvertDateTimeData(false);
options.setSeparator(CSV_SEPARATOR.charAt(0));
sheet.getCells().importCSV(is, options, currentRowNum, 0);

These numbers who have a length between 10 and 15 are in this format : 1234567891234 and they are displayed in scientific notation, which is an unwanted behavior.

Creating a custom parser like explained here Excel is automatically converting a numeric value to scientific notation does not seem like a good idea, since we don’t know which columns will be numbers and which one won’t.

Adding a ‘ character in front of numbers won’t work either, as the character is then displayed in the Excel with the data, instead of just the number.

How can we get these numbers to display in regular numbers in the output Excel ?

Best regards.

@engiesynapse,
You may try to set cells formatting to built-in Decimal format and let us know your feedback.

Cells cells = sheet.getCells();
Style style = cells.getStyle();
style.setNumber(1);
cells.setStyle(style);

If it is not suitable for your CSV file then share complete runnable code with input and output files for our analysis.

@ahsaniqbalsidiqui :
I share with you some files :

  • a csv input data
  • our minimized Java script (I removed several logic due to Boomi and additionnal sheet in the workbook)
  • an output Excel file without number parsing
  • an output Excel file with some number parsing
  • the input template file (I remove some extra lines with static informations and sheets)
    AsposeCase.zip (30.1 KB)

Please keep in mind that :

  • we want to keep all zéro at the start of some number (for n°document or n°FMC/FUM colums)
  • we don’t know the column order because they are received dynamically by the script
  • dates should be at french format
  • “montant” column should be with two decimals
  • for all others number we don’t want any scientific format

Regards.

@engiesynapse,

I am afraid, there seems no better way to cope with it except using custom parser(s) or specify style/formatting to your cells area (as suggested by Ahsan Iqbal).

Anyways, we will further check and if we could find a better alternatives to share with you.

@engiesynapse,

We evaluated further and could not find any reliable way to determine whether to parse one column and not to parse other columns for your CSV file. Please note, if there is no rule (as you mentioned), then how it is possible for even yourselves to determine the data position.