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);
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.
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.
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)
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.
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();
and try to force French locale at the beginning of my script, no change on the number display result.
If I try this :
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.
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.
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);