Remove Scientific format for numbers using Aspose.Cells for Java

Aspose.Cells Product Family
,
1

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 - #2 by shakeel.faiz 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.

2

@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.

3

@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.

4

@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.

5

@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.

6

Hi,
We have some additionnals rows that I removed for the example. The row number 13 could be used to determine the column to parse with the code as suggested by Ahsan Iqbal. I can correctly determine cells to apply this ccde, so far, so good.

The problem is that the data still not really a number. For example, I want 2 decimals displayed as line 14 and I insert row 15 with dot separator image.png (3.5 KB)

Can you help me ?

7

@engiesynapse,

The sample code segment shared by Ahsan Iqbal can only formats numeric data. If data is strings or not a valid numeric data, then it won’t apply the style. Since you have some mixed types of data, so may be you could use custom formattings (via setCustom()). See how you can apply custom numbers formatting for your reference.

8

Thanks.

If I use the setCustom method, I can specified my custom format for the correct fields.

But my main problem is that the format in the cell is still with a dot, exemple on line 14 to 19.
image.png (2.7 KB)

I try to put this format :
style = cell.getStyle();
style.setCustom("#,000");
cell.setStyle(style);
and try to force French locale at the beginning of my script, no change on the number display result.

If I try this :
style.setNumber(4);
I have the same result.

How can I have this result “123456789,000”, show on the folowwing link
image.png (1.1 KB)
?
Note that the number length could change before the comma, but not the decimals.

Best regards.

9

@engiesynapse,

When you use/set Custom formatting, it won’t change accordingly when the locale is changed. If you want numbers formatting should be dependent to your regional or locale settings, you will use Style.setNumber() attribute.

Please share a sample Excel file containing your desired formatting intact, we will check it soon. Also, share your current output by Aspose.Cells which has not your desired formatting intact.

PS. please zip the file(s) prior attaching.

10

AsposeCase_2.zip (17.8 KB)

11

@engiesynapse,

I checked your template file and found that numbers are stored as text/string in the sheet (see the screenshot attached), so you cannot apply formatting to the cells properly unless those are converted to numeric data (type). In MS Excel, if you double-click in the cell and then Enter, MS Excel will convert to numeric data. For Aspose.Cells, you have to convert the text (so called numbers) to numeric values, so style/formatting could be applied properly. If you do not have any control over import data, then even you can convert to numeric data by re-entering into the cells (same as MS Excel) using the following line of code:
i.e.,
cell.PutValue(cell.StringValue, true);

Hope, this helps a bit.
sc_shot1.png (53.7 KB)

12

When I try this code, I encountered the following error :
image.png (9.1 KB)

My code is :
cell.PutValue(cell.getStringValue(), true);

I am using the following jar “aspose-cells-20.11.jar” of the library. What is the problem ?
Seems java.lang.Boolean and boolean are not the same object but I just put true in the code …

PS : I am coding in groovy encapsuled in Dell Boomi so it is not really pure java

13

@engiesynapse,

Since you are using Aspose.Cells for Java, so kindly change the line to:
cell.putValue(cell.getStringValue(), true);

it should work fine now.