Date Format setting for the sheet

Veerendra1234 · April 24, 2023, 9:48am

Hi ,
I am using aspose.cells using nodejs via java

I am creating a worksheet from json Data using the below code

const layoutOptions = aspose.cells.JsonLayoutOptions();
layoutOptions.setConvertNumericOrDate(true);
aspose.cells.JsonUtility.importData(JSON.stringify(data), worksheet.getCells(), 0, 0, layoutOptions)

I have some date Columns in the sheets which has to be converted into different formats like ‘‘dd-MMM-yyyy’’ , ‘‘dd-MM-yyyy’’,“dd-MM-yy” , ‘MM-dd-yyyy’ , ‘yyyy-MM-dd’

I am maintaining the date format which has to be applied in a local variable called dtFormat

so , if dtFormat = ‘dd-MM-yyyy’ , then all the date columns in my sheet should be converted to this format

Can you please guide me on achieving that , it’s a bit priority ??

amjad.sahi · April 24, 2023, 10:12am

@Veerendra1234,

Please try our latest version/fix: Aspose.Cells for Node.js via Java 23.4.0. I tested using the following sample code, it works fine. The output Excel file (attached) is fine tuned. The date format is properly set for the JSON data:
e.g.
Sample 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);
layoutOptions.setConvertNumericOrDate(true);
layoutOptions.setDateFormat("DD-MM-YYYY");
aspose.cells.JsonUtility.importData("{\"mongo_id\": \"5af05801b87fd\",\"date\" : \"2022-01-09\"}", worksheet.getCells(), 0, 0, layoutOptions);
workbook.save("dest.xlsx");

dest.zip (6.9 KB)

If you still find any issue, kindly do provide sample (runnable) code with JSON data/file and Excel file(s), we will check it soon.

Veerendra1234 · April 24, 2023, 11:26am

Hi @amjad.sahi ,

I have a doubt here ,

if my json data is in the format MM-DD-YYYY , then If i try to change the format to DD-MM-YYYY
then i think this sample code would fail right ??

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

the date in the above json is in the format MM-DD-YYYY ( 09 JAN 2022 ) but it will be converted to 01 SEPT 2022

is there a way to avoid this ??

is there a standard Json date format which should be there before applying the date Format ??

need some help on this ,
thanks .

johnson.shi · April 24, 2023, 12:02pm

@Veerendra1234,

By default we parse the date time value according to the default date time format of current regional settings. For your requirement, please set the region/locale to the one whose format is what you expected before importing the json data. For example, if your date time values are saved as MM-DD-YYYY, you may set the region( setRegion(int value)) as USA; If they are saved as DD-MM-YYYY, you may set the region as UNITED_KINGDOM.

Veerendra1234 · April 24, 2023, 1:12pm

ohh okay ,
thanks for the update
it would be a great help to me , if can you please give me the regions for these 5 date formats ( just to be sure )

DD-MM-YYYY
DD-MMM-YYYY
DD-MM-YY
MM-DD-YYYY
YYYY-MM-DD

johnson.shi · April 24, 2023, 3:23pm

@Veerendra1234,

Because the year is always 4-digits, so it can be recognized by the parser anyways. Then the critical part for parsing is the sequence of month and day. So it should be simple for you to use UNITED_KINGDOM for your date formats of 1-3 and USA for 4 and 5.

Darwinbox · April 28, 2023, 6:46am

Hi ,

I was trying to set date format using the style and styleFlag mentioned below

const path = require('path');
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);
aspose.cells.JsonUtility.importData("{\"mongo_id\": \"5af05801b87fd\",\"date\" : \"01-09-2022\",\"AccountNumber\" : \"000009401044951879870096\"}", worksheet.getCells(), 0, 0, layoutOptions);
const style = new aspose.cells.Style();
style.setNumber(49);//converting it to text for the trailing zeros
const StyleFlag = new aspose.cells.StyleFlag();
StyleFlag.setNumberFormat(true);
worksheet.getCells().applyColumnStyle(2,style,StyleFlag);
const datestyle = new aspose.cells.Style();
datestyle.setNumber(14);// converting it to date to format the date column
const flag = new aspose.cells.StyleFlag();
worksheet.getCells().applyColumnStyle(1,datestyle,flag);
const date = new Date();
let time = date.toLocaleTimeString('en-US');
let filePath = path.normalize(`${process.cwd()}/result/account_${time}.xls`);
workbook.save(filePath);

As per my understanding , the 3rd column will be of text type and 2nd column should be of Date Type

But date type is not getting converted , any reason for that ??
account_11:59:31 AM.xls.zip (1.8 KB)

Need some urgent help on this

Thanks

johnson.shi · April 28, 2023, 8:43am

@Darwinbox,

This means all cell values(including the 2nd column) are string values. The number format you applied only can take effect for numeric values, and setting such kind of number format cannot convert cell’s data to numeric automatically. To get the formatted result you expect, you have to convert those cell values to numeric. You may remove this line from your code.

Veerendra1234 · April 28, 2023, 9:12am

@johnson.shi

Data conversion to Numeric is working fine , I was trying to change the Date Format but it was not happening because the data is of GENERAL TYPE

I was trying to change column type to DATE TYPE using
const datestyle = new aspose.cells.Style();
datestyle.setNumber(14);// converting it to date to format the date column
const flag = new aspose.cells.StyleFlag();
worksheet.getCells().applyColumnStyle(1,datestyle,flag);

As per the above code , it should be converted to date right ?? , which was not happening

So can you please help on this

Thanks

simon.zhao · April 28, 2023, 9:33am

@Veerendra1234
Please set what do you want to apply.

StyleFlag flag = new StyleFlag();
		flag.setNumberFormat(true);

Veerendra1234 · April 28, 2023, 10:11am

Hi @simon.zhao

I need the Value to be converted to date Type but it is still showing as the String in filters ( shown below )

Screenshot 2023-04-28 at 3.38.26 PM.png (8.7 KB)

But i need is the date should be filterable not like a string ( as shown below )

Screenshot 2023-04-28 at 3.38.14 PM.png (16.7 KB)

Is there any way , I can achieve this but adding any setting or anything to the following code

var aspose = aspose || {};

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

const path = require('path');

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);

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

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

style.setNumber(49);//converting it to text for the trailing zeros

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

StyleFlag.setNumberFormat(true);

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

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

datestyle.setNumber(14);// converting it to date to format the date column

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

flag.setNumberFormat(true);

worksheet.getCells().applyColumnStyle(1,datestyle,flag);

const date = new Date();

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

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

workbook.save(filePath);

Thanks

simon.zhao · April 28, 2023, 10:20am

@Veerendra1234
Please set layoutOptions.setConvertNumericOrDate(true); and flag.setNumberFormat(true); together.
As the following codes:

Workbook workbook = new Workbook();
		workbook.getSettings().setRegion(CountryCode.UNITED_KINGDOM);
		Worksheet worksheet = workbook.getWorksheets().get(0);

		JsonLayoutOptions layoutOptions = new 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");

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

		datestyle.setNumber(14);
		StyleFlag flag = new StyleFlag();
		worksheet.getCells().applyColumnStyle(1,datestyle,flag);

		workbook.save(dir +"dest.xlsx");

Veerendra1234 · April 28, 2023, 10:46am

@simon.zhao

If i set the below params

layoutOptions.setConvertNumericOrDate(true);
layoutOptions.setNumberFormat(“0”);

then the account Number is coming wrongly because of the zeros added in it

var aspose = aspose || {};

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

const path = require('path');

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.setConvertNumericOrDate(true);

layoutOptions.setNumberFormat("0");

layoutOptions.setArrayAsTable(true);

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

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

style.setNumber(49);//converting it to text for the trailing zeros

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

StyleFlag.setNumberFormat(true);

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

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

datestyle.setNumber(14);// converting it to date to format the date column

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

flag.setNumberFormat(true);

worksheet.getCells().applyColumnStyle(1,datestyle,flag);

const date = new Date();

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

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

workbook.save(filePath);

this is the final code which I am using , here if you see
AccountNumber" : “000009401044951879870096” ( expected to come same in the excel as well ) but the account number is coming as 000012345608078780900 ( which is not expected )

attaching zip file of excel for your reference and sharing the screenshot as well
aspose10_4:08:30 PM.xls.zip (1.8 KB)

Screenshot 2023-04-28 at 4.15.21 PM.png (11.9 KB)

please help and tell me , if i am missing anything

Thanks

amjad.sahi · April 28, 2023, 11:35am

@Veerendra1234,

I do not think there is any better way to cope with it. As a workaround, you may define account number as string data in JSON. For this, you may simply add " ’ " when specifying in JSON data. For example, you may change the line of code:

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

to:

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

simon.zhao · April 28, 2023, 1:13pm

@Veerendra1234
As Johnson said in Date Format setting for the sheet - #8 by johnson.shi,
you have to set layoutOptions.setConvertNumericOrDate(true); then we can parse date string and numberic string value to date or number.
For the issue of zeros added, I have fixed this issue in the next version 23.5 which will be released in the next month.

Veerendra1234 · April 28, 2023, 1:42pm

ohhh , great !!
It would be great if you share whether it will Start of the next month or any approximate time so that we can plan our roadmap accordingly

Thanks

amjad.sahi · April 28, 2023, 1:46pm

@Veerendra1234,

We cannot share exact date of the next release as releases are published once ready. But you may expect it in the first half of May, 2023.

johnson.shi · May 19, 2023, 6:36am

@Veerendra1234,

For the heading zeros, please try our latest release here to check whether it can fit your requirement.