Data is coming in scientific format in the sheet which should be converted

Aspose.Cells Product Family
1

Hi ,

we are converting the json data into excel using the following code

var workbook = new aspose.cells.Workbook(aspose.cells.FileFormatType.XLSX);
var worksheet = workbook.getWorksheets().get(0);
var layoutOptions = new aspose.cells.JsonLayoutOptions();
layoutOptions.setArrayAsTable(true);
workbook.getSettings().setRegion(aspose.cells.CountryCode['USA']);
layoutOptions.setConvertNumericOrDate(true);
layoutOptions.setDateFormat('DD-MMM-YYYY');
aspose.cells.JsonUtility.importData("{\"mongo_id\": \"5af05801b87fd\",\"date\" : \"01-09-2022\",\"**AccountNumber**\" : \"**940104495187**\"}", worksheet.getCells(), 0, 0, layoutOptions);
let filePath = path.normalize(`${process.cwd()}/result/ataskjgakhdgvljabfclhjb.xls`);
workbook.save(filePath);

Here the account number value is getting converted as a scientific format ( shown below in the screenshot ), which is not required for us

Screenshot 2023-04-24 at 7.08.14 PM.png (9.9 KB)

is there a way to handle it in the sheet level settings or something ??

Need some help on this as soon as possible…

2

@Veerendra1234 you can set the property ConvertNumericOrDate to false:

layoutOptions.setConvertNumericOrDate(false);
4

@Veerendra1234,

When you enter “940104495187” into MS Excel worksheet cell manually, MS Excel will convert to scientific (numeric) notation, so Aspose.Cells does the same when converting it to numeric value (using ConvertNumericOrDate Boolean attribute). Please note, in MS Excel, since Date values are stored in numeric values, setting “ConvertNumericOrDate” to false won’t convert DateTime value to numeric value and this results in your Date value won’t be formatted properly.

I think for your needs, you should not set “ConvertNumericOrDate” attribute to false. Rather you may add a line to your code to format numbers to relevant custom format. See the following line of code that you may add to your code segment and it will work for the purpose.
layoutOptions.setNumberFormat("0");

5

No @eduardo.canal

layoutOptions.setConvertNumericOrDate(**true**);

This has to be true for me , because there are some date formats which has to be applied on the data

I need to remove the scientific format data and also at the same time need to convert the string data into Date type so that date formatting can be applied

isn’t there any other way for this ?

6

@Veerendra1234,

Please check my previous reply especially the part:

1 Like