Values between 2^31 and 2^32 − 1 become two’s complement negatives when CSVs are converted to Excel using Aspose.Cells for Java (v24.1.0)

Aspose.Cells Product Family
1

We are using a the below script to generate excel files from csv:

// Values between 2^31 and 2^32 - 1 appear as their two’s complement negative equivalents when the CSV is converted to EXCEL using the below logic.
const aspose = {};
aspose.cells = require('aspose.cells');

const loadOptions = new aspose.cells.TxtLoadOptions(
    aspose.cells.LoadFormat.CSV
);
loadOptions.setConvertNumericData(false);
const workBook = new aspose.cells.Workbook('csvData.csv', loadOptions);
workBook.getSettings().setRegion(aspose.cells.CountryCode['UNITED_KINGDOM']);
const worksheet = workBook.getWorksheets().get(0);
worksheet.setName('Data');
const rowCount = worksheet.getCells().getMaxDataRow();
const numberColumnIndices = [1];
for (let row = 1; row <= rowCount; row++) {
    (numberColumnIndices || []).forEach((colIndex) => {
        const cell = worksheet.getCells().get(row, colIndex);
        const rawValue = cell.getValue();

        if (rawValue !== null && rawValue !== '' && !isNaN(rawValue)) {
            cell.setValue(parseFloat(rawValue));
        }
    });
}
const numberStyleFlag = new aspose.cells.StyleFlag();
numberStyleFlag.setNumberFormat(true);
const cells = worksheet.getCells();
numberColumnIndices.forEach((numberColIndex) => {
    const numberColumn = cells.getColumns().get(numberColIndex);
    const numberColumnStyle = numberColumn.getStyle();
    numberColumnStyle.setNumber(0);
    numberColumn.applyStyle(numberColumnStyle, numberStyleFlag);
});

workBook.save('csvData.xlsx');
process.exit(0);

Sample CSV data to replicate the issue:

Description,Value
2^30,1073741824
2^31,2147483648
2^31 - 1,2147483647
2^32,4294967296
2^32 - 1,4294967295
2^33,8589934592

We have to use aspose.cells.TxtLoadOptions, since using aspose.cells.LoadOptions had been causing other issues for us.

2

@rayanrayan

The issue you’re experiencing with values between (2^{31}) and (2^{32} - 1) being converted to their two’s complement negative equivalents in Excel is likely due to how these large numbers are interpreted during the CSV to Excel conversion process.

Since you’re using aspose.cells.TxtLoadOptions and have set loadOptions.setConvertNumericData(false), the library is treating these values as text rather than numbers. This can lead to incorrect interpretations when saving to Excel.

To resolve this, you can modify your script to ensure that these values are explicitly treated as numbers. Here’s a suggested approach:

  1. Remove the setConvertNumericData(false) line to allow Aspose.Cells to automatically convert numeric data.
  2. Ensure that the values are parsed correctly when setting them in the cells.

Here’s a modified version of your script:

const aspose = {};
aspose.cells = require('aspose.cells');

const loadOptions = new aspose.cells.TxtLoadOptions(
    aspose.cells.LoadFormat.CSV
);
const workBook = new aspose.cells.Workbook('csvData.csv', loadOptions);
workBook.getSettings().setRegion(aspose.cells.CountryCode['UNITED_KINGDOM']);
const worksheet = workBook.getWorksheets().get(0);
worksheet.setName('Data');
const rowCount = worksheet.getCells().getMaxDataRow();
const numberColumnIndices = [1];
for (let row = 1; row <= rowCount; row++) {
    (numberColumnIndices || []).forEach((colIndex) => {
        const cell = worksheet.getCells().get(row, colIndex);
        const rawValue = cell.getValue();

        if (rawValue !== null && rawValue !== '' && !isNaN(rawValue)) {
            // Ensure the value is treated as a number
            cell.setValue(Number(rawValue));
        }
    });
}
const numberStyleFlag = new aspose.cells.StyleFlag();
numberStyleFlag.setNumberFormat(true);
const cells = worksheet.getCells();
numberColumnIndices.forEach((numberColIndex) => {
    const numberColumn = cells.getColumns().get(numberColIndex);
    const numberColumnStyle = numberColumn.getStyle();
    numberColumnStyle.setNumber(0);
    numberColumn.applyStyle(numberColumnStyle, numberStyleFlag);
});

workBook.save('csvData.xlsx');
process.exit(0);

By allowing Aspose.Cells to handle numeric data conversion automatically, you should avoid the two’s complement issue for large numbers.

3

I have used your script, the same issue persists. Numbers between 2^31 and 2^32 - 1 are still being converted to their negative two’s complement counterparts. Also we have to keep the loadOptions.setConvertNumericData(false) line as removing it would cause other issues for us.

4

@rayanrayan
Please check the value of parseFloat(rawValue) and share the codes of this function.
If loadOptions.setConvertNumericData(false); we just simply load data as string value.

5

parseFloat(rawValue) is a native javascript function. The same issue persists if I use Number(rawValue) instead of parseFloat(rawValue) as well.

6

@rayanrayan,

You may try our latest version of Aspose.Cells for Node.js via Java, if it makes any difference?
If you still find the issue with latest version, kindly do provide your current output Excel file and your expected output Excel file (please zip the files prior attaching here). We will evaluate your issue and may log appropriate ticket(s) for it.

7

@rayanrayan
Please try the below code, it works fine now.

// Values between 2^31 and 2^32 - 1 appear as their two’s complement negative equivalents when the CSV is converted to EXCEL using the below logic.
const aspose = {};
aspose.cells = require('aspose.cells');

var java = require("java");

const loadOptions = new aspose.cells.TxtLoadOptions(
    aspose.cells.LoadFormat.CSV
);
loadOptions.setConvertNumericData(false);
const workBook = new aspose.cells.Workbook('csvData.csv', loadOptions);
workBook.getSettings().setRegion(aspose.cells.CountryCode['UNITED_KINGDOM']);
const worksheet = workBook.getWorksheets().get(0);
worksheet.setName('Data');
const rowCount = worksheet.getCells().getMaxDataRow();
const numberColumnIndices = [1];
for (let row = 1; row <= rowCount; row++) {
    (numberColumnIndices || []).forEach((colIndex) => {
        const cell = worksheet.getCells().get(row, colIndex);
        const rawValue = cell.getValue();

        if (rawValue !== null && rawValue !== '' && !isNaN(rawValue)) {
            cell.setValue(java.newDouble(parseFloat(rawValue)));
        }
    });
}
const numberStyleFlag = new aspose.cells.StyleFlag();
numberStyleFlag.setNumberFormat(true);
const cells = worksheet.getCells();
numberColumnIndices.forEach((numberColIndex) => {
    const numberColumn = cells.getColumns().get(numberColIndex);
    const numberColumnStyle = numberColumn.getStyle();
    numberColumnStyle.setNumber(0);
    numberColumn.applyStyle(numberColumnStyle, numberStyleFlag);
});

workBook.save('csvData.xlsx');
process.exit(0);

Please use java.newDouble or newLong to resolve the problem.

cell.setValue(java.newDouble(parseFloat(rawValue)));