If the number is more than 15 digits , the numbers after the 15th digit are getting converted as ZEROS

Aspose.Cells Product Family
1

Hi ,

I am using the following code to convert the JSON data into Excel

var aspose = aspose || {};
aspose.cells = require("aspose.cells");
const path = require('path');
const data = [
      {
          "Employee Id": "T510",
          "First Name": "Darwinboxcore76",
          "Last Name": "Employee76",
          "Gender": "Male",
          "Date Of Birth": "24-02-1976",
          "Date Of Joining": "03-10-2022",
          "Date Of Exit": " ",
          "Bank Account Number": "3174105301830007",
          "Bank Ifsc Code": " "
      }
]

const workbook = aspose.cells.Workbook(aspose.cells.FileFormatType.XLSX);
workbook.getSettings().setRegion(aspose.cells.CountryCode['UNITED_KINGDOM']);
var worksheet = workbook.getWorksheets().get(0);
var layoutOptions = aspose.cells.JsonLayoutOptions();
layoutOptions.setArrayAsTable(true);
layoutOptions.setConvertNumericOrDate(true);
layoutOptions.setNumberFormat("0");
layoutOptions.setDateFormat('DD-MMM-YYYY');
aspose.cells.JsonUtility.importData(JSON.stringify(data), worksheet.getCells(), 0, 0, layoutOptions)
let range = worksheet.getCells().getMaxDisplayRange();
console.log(range.getRefersTo());
let filePath = path.normalize(`${process.cwd()}/result/sample2933012497.xls`);
workbook.save(filePath);

As you can see the BANK ACCOUNT NUMBER is having the value as 3174105301830007 which is expected to come in the excel report but somehow the last digit 7 is getting replaced with the DIGIT ZERO and data in the report is coming as shown in the below screenshot

Screenshot 2023-07-03 at 3.14.42 PM.png (7.7 KB)

which is a concern for us , because the account number data cannot be changed in the report and it is impacting our payroll system

Can you please help on this

I am using aspose.cells :: 23.6.0 latest NPM package version

Thanks ,
veerendra

2

@Veerendra1234,

Thanks for the screenshot and details.

I tested your scenario/case using your sample with Aspose.Cells for Node.js via Java API and noticed this behavior you have talked about. The last digit 7 is getting replaced with the DIGIT ZERO. Then I tested the scenario/case a bit by entering “3174105301830007” manually into a cell in MS Excel. I set the formatting for the cell as “0” (number). I noticed MS Excel does convert the number to “3174105301830000” which is same in the output by Aspose.Cells for Node.js via Java. In short, this is not the issue by Aspose.Cells.

You need not to convert the numeric string to number and keep it as string. For your needs, you may try to change the line of code:

layoutOptions.setConvertNumericOrDate(true);

to:

layoutOptions.setConvertNumericOrDate(false);
3

Hi

I need this setConvertNumericOrDate to be set to true , since I am trying to convert string formatted date to DATE type and also making it filterable

Is there any other way to handle this case ?

I am listing all the use cases which I am trying to achieve

–> setConvertNumericOrDate to TRUE , for converting string DATE type to DATE type and making it filterable

  • Once the Date is converted , setDateFormat is being used to convert date format

  • setNumberFormat(“0”) is being used , to convert the strings to Numbers and to support TRAILING ZEROS, ENDING ZEROS , Number values having more than 15 digits should NOT be converted to SCIENTIFIC FORMAT

Thanks,
veerendra

4

@Veerendra1234,

I found no way to do that for a number value. If you could accomplish the task in MS Excel manually, let us know with details and sample Excel file containing your desired value inserted as number. We will check on how to do it via Aspose.Cells.

5

Hi @amjad.sahi

I have edited my answer above and added all the CASES which we want to achieve , can you please have a look and let me know if there is a way

Thanks

6

@Veerendra1234,

My answer would be more or less the same as I already told you in previous reply. If you still think this can be done in MS Excel (a cell having a number with more than 15 digits), please share a sample Excel file containing your desired value inserted as number type. We will check on how to do it via Aspose.Cells.

7

@Veerendra1234,

For your requirement, we think you may try to add one heading quote sign for those long numbers which you need them to be taken as string value instead of number. For example:

“Bank Account Number”: “3174105301830007”

Changing it to:

“Bank Account Number”: “'3174105301830007”

Then this number will be taken and formatted as text in the generated workbook.