Leading zeros are getting removed

Aspose.Cells Product Family
1

Hi ,
I am converting the JSON to excel as mentioned below

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);
layoutOptions.setConvertNumericOrDate(true);
layoutOptions.setNumberFormat("0");
layoutOptions.setDateFormat(“DD-MM-YYYY”);
aspose.cells.JsonUtility.importData("{“mongo_id”: “5af05801b87fd”,“date” : “01-09-2022”,“account_number” : "000020201010”}", worksheet.getCells(), 0, 0, layoutOptions);
workbook.save(“dest.xlsx”);

But somehow the trailing zeros are getting removed in the excel file ( attaching screenshot for ref )

Screenshot 2023-04-26 at 10.05.06 PM.png (18.2 KB)

Need some urgent help on this

Thanks

2

@Veerendra1234 A solution for your case would be to remove the line layoutOptions.setNumberFormat("0") that keeps the trailing zeros in your output worksheet. However, it has an inconvenience: if you insert another account number that doesn’t start with zero, the number will be represented in scientific notation.

A workaround to solve this is to set the ConvertNumericOrDate property to false and then format your date column as a date. For example:

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);
layoutOptions.setConvertNumericOrDate(false);
aspose.cells.JsonUtility.importData("[{“mongo_id”: “5af05801b87fd”,“date” : “01-09-2022”,“account_number” : “000020201010”}, {“mongo_id”: “5af05801b87fd”,“date” : “01-09-2022”,“account_number” : “459620201010”}]", worksheet.getCells(), 0, 0, layoutOptions);

CellsFactory factory = new CellsFactory();
Style style = factory.createStyle();
style.setNumber(14);

worksheet.getCells().applyColumnStyle(1, style,
        new StyleFlag() {{
            setNumberFormat(true);
        }});

workbook.save(“dest.xlsx”);
3

In that case then i have to apply styling for each date column right ( there can be 30 date columns , setting all those columns might be a big task )
isn’t there any other way for this ??

And also , can you please share the sample working code for the node.js version it would help a lot

Thanks

4

@Veerendra1234 Sure, I can prepare an example for you.

Unfortunately, if you want to insert all the data at once, there is no other solution. However, dealing with JSON in NodeJS tends to be easier than in other languages. Therefore, a good solution for you would be to split the JSON objects. This way, you can insert the rest of the data using the JsonUtility importer capabilities and then insert your account numbers separately in a properly formatted way.

5

Splitting the JSON and re-inserting the data into excel will not possible ( Need to maintain the order of columns as well )

I will try the styling logic which you have mentioned above , can you please share the working example for nodeJS version so that I can use it reference

Thanks

6

@Veerendra1234

Splitting the JSON and re-inserting the data into excel will not possible ( Need to maintain the order of columns as well )

Please notice that you can specify in the importData method the row and col from where you want to insert the JSON data.

I will try the styling logic which you have mentioned above , can you please share the working example for nodeJS version so that I can use it reference

I’m already working on it.

7

Hi
@eduardo.canal

I have tried the code which you have mentioned for the trailing zeros
Trailing zeros seems to be fixed , but I need the String Date columns to be a Date columns

var workbook = new aspose.cells.Workbook(aspose.cells.FileFormatType.XLSX);

workbook.getSettings().setRegion(aspose.cells.CountryCode['UNITED_KINGDOM']);// DD-MM-YYYY

var worksheet = workbook.getWorksheets().get(0);

var layoutOptions = new aspose.cells.JsonLayoutOptions();

layoutOptions.setArrayAsTable(true);

layoutOptions.setConvertNumericOrDate(false);

layoutOptions.setDateFormat('DD-MMM-YYYY');

aspose.cells.JsonUtility.importData("{\"mongo_id\": \"5af05801b87fd\",\"date\" : \"01-09-2022\",\"AccountNumber\" : \"00000940104495187\"}", worksheet.getCells(), 0, 0, layoutOptions);

const style = new aspose.cells.Style();

style.setNumber(14);

const StyleFlag = new aspose.cells.StyleFlag();

StyleFlag.setNumberFormat(true);

worksheet.getCells().applyColumnStyle(1,style,StyleFlag);

let filePath = path.normalize(`${process.cwd()}/result/account1.xls`);

workbook.save(filePath);

I need to apply date formatting on the date related columns and also format the column to Date from String type

Can you please help , Am i missing anything here ??

Attaching the screenshot of the excel

Screenshot 2023-04-27 at 12.45.01 AM.png (86.9 KB)

Expectation is column (DATE )is coming as String which should be of Date Column so that we can apply different formats on top of it like changing the format to dd-MMM-yyyy , YYYY-mm-dd etc
The filter should look like this at the end for the date columns

Screenshot 2023-04-27 at 12.48.10 AM.png (12.7 KB)

As of now , it is coming as a direct string

Please help on this

Thanks

8

@Veerendra1234 I noticed that as well. Sorry for the inconvenience. This issue is caused because the date format in the JSON objects is not valid for Excel. The cells in the columns are formatted as dates, but MS Excel app cannot detect that the text in those columns represents a valid date.

The solution to this issue is to fix the date format in your JSON data (setting the data in the current format m/d/yyyy). However, if the account numbers will always have 12 characters, you can resolve all the problems by running the following code:

layoutOptions.setConvertNumericOrDate(true);
layoutOptions.setNumberFormat('000000000000');
layoutOptions.setDateFormat('DD-MMM-YYYY');

Please note that this will also affect the display format of all the numeric data in your JSON data.

9

I don’t think it is a feasible solution
If the number is having length more than 12 digits , then again the trailing zeros will be removed right

If we can handle the dateFormat in this below code , then I think it would be done

var workbook = new aspose.cells.Workbook(aspose.cells.FileFormatType.XLSX);

workbook.getSettings().setRegion(aspose.cells.CountryCode['UNITED_KINGDOM']);// DD-MM-YYYY

var worksheet = workbook.getWorksheets().get(0);

var layoutOptions = new aspose.cells.JsonLayoutOptions();

layoutOptions.setArrayAsTable(true);

layoutOptions.setConvertNumericOrDate(false);

layoutOptions.setDateFormat('DD-MMM-YYYY');

aspose.cells.JsonUtility.importData("{\"mongo_id\": \"5af05801b87fd\",\"date\" : \"01-09-2022\",\"AccountNumber\" : \"00000940104495187\"}", worksheet.getCells(), 0, 0, layoutOptions);

const style = new aspose.cells.Style();

style.setNumber(14);

const StyleFlag = new aspose.cells.StyleFlag();

StyleFlag.setNumberFormat(true);

worksheet.getCells().applyColumnStyle(1,style,StyleFlag);

let filePath = path.normalize(`${process.cwd()}/result/account1.xls`);

workbook.save(filePath);

Isn’t there a way to convert the string column to Date column using style & styleFlags ??

Thanks

10

@Veerendra1234
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-53268

We will look into how to keep number format of the numberic value.

11

@Veerendra1234
Which version are you using? Please check the attached 23.4.zip (6.1 KB)
generated by 23.4 withe following codes:

Workbook workbook = new Workbook(FileFormatType.XLSX);
		Worksheet worksheet = workbook.getWorksheets().get(0);
		JsonLayoutOptions layoutOptions = new JsonLayoutOptions();
		layoutOptions.setArrayAsTable(true);
		layoutOptions.setConvertNumericOrDate(true);
		layoutOptions.setDateFormat("DD-MM-YYYY");
		JsonUtility.importData("{\"mongo_id\": \"5af05801b87fd\",\"date\" : \"01-09-2022\",\"AccountNumber\" : \"00000940104495187\"}", worksheet.getCells(), 0, 0, layoutOptions);
		workbook.save(dir + "dest.xlsx");
12

Hi @simon.zhao

I am currently using aspose.cells (23.3.0) nodejs version

The code which you have mentioned , will work but if the trailing zeros are not there in the number then it will be converted to scientific format
with this line ( we can retain the string instead of converting it to scientific format but then the trailing zeros are getting removed

layoutOptions.setNumberFormat(“0”)

adding screenshot of the excel as well for your reference

if i use the code which you have given ( removing the setNumberFormat then trailing zeros are coming which is expected )

Screenshot 2023-04-27 at 9.27.01 AM.png (11.8 KB)

If in case the trailing zeros are not there in the number , then the number is getting converted to scientific format ( which should be handled )

Screenshot 2023-04-27 at 9.27.19 AM.png (9.5 KB)

Hope , you have understood the issue
Please let me know , if there is anything that can help here

Thanks

13

@Veerendra1234
If you set layoutOptions.setNumberFormat(“0”), the original format will be ignored and the number format of the cell will be “0”.
It’s only way that keeping the value as the string and not parsing it to support your need.

14

Hi ,
Facing one weird issue,
when I am using layoutOptions.setNumberFormat(“0”) , the digits after 0 are not being considered

var aspose = aspose || {};

aspose.cells = require("aspose.cells");

const path = require('path');

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);// convert string to Date

layoutOptions.setNumberFormat("0");

layoutOptions.setDateFormat('DD-MMM-YYYY');

aspose.cells.JsonUtility.importData("{\"mongo_id\": \"5af05801b87fd\",\"date\" : \"01-09-2022\",\"AccountNumber\" : \"0001234567891234564980076832\"}", worksheet.getCells(), 0, 0, layoutOptions);

const date = new Date();

let time = date.toLocaleTimeString('en-US');

let filePath = path.normalize(`${process.cwd()}/result/aspose2.xls`);

workbook.save(filePath);

Attaching sample code and screenshot for your reference

expected output is

AccountNumber :: 0001234567891234564980076832

actual output coming in excel is
AccountNumber :: 1234567891234560000000000 ( trailing zeros are removed and data is also wrong for the column )
Screenshot 2023-04-27 at 1.28.20 PM.png (11.9 KB)

Also , is there any way to get the date filters like shown in the below screenshot using STYLE & STYLE FLAGS ??

Screenshot 2023-04-27 at 1.30.10 PM.jpg (474.8 KB)

Need some help on this

Thanks

15

@Veerendra1234
The string starting from 0 will no longer be parsed and will be saved as a string directly. Please wait for the next version. Once there are updates, we will notify you in time.

16

The issues you have found earlier (filed as CELLSNET-53268) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi

17

@johnson.shi
Can you please provide me the version details for Aspose.Cells for Node.js via Java
The link which was posted above is not opening for me

Thanks

18

@Veerendra1234,

We released newer version of .NET APIs, i.e., Aspose.Cells for .NET v23.5. Java version will be published in the next couple of days. After publishing Java version, we will then publish Node.js version.

We will fix it soon.

19

@Veerendra1234
Aspose.Cells for Node.js via Java v23.5 has been released. Please try aspose.cells - npm