Need to Change the String Column to Date Filterable column in nodejs

Aspose.Cells Product Family
1

HI ,

I have a sheet which is having the data in one column having ‘2022-01-01’ ( GENERAL FORMAT ) because of which my filter is not giving correctly ( YEAR --> MONTH --> DATE )

The Filter is coming in the format given below
Screenshot 2023-03-14 at 11.57.49 PM.png (40.5 KB)

But I need the Filter option to be YEAR filterable and then month & Date also ( like in the below screenshot )

Screenshot 2023-03-15 at 12.02.36 AM.png (29.2 KB)

Is there a way to change the GENERAL TYPE of column to DATE type for all the data of that paritcular column ??
Can you please help on this as soon as possible

Thanks ,
veerendra

2

@Veerendra1234,

Thanks for the screenshots.

You have to convert/change the existing string data in the cells to DateTime yourselves. You may also re-insert the existing string/data using the overload Cell.PutValue(string stringValue, bool isConverted) into the those cells in the column for conversion. See the line of code for your reference.

cell.PutValue(cell.StringValue, true);

The above line will convert existing string value to DateTime type (if appropriate) automatically. The (last) Boolean parameter will make sure to convert to proper data type. You may use the above line of code to loop through each cell in your desired column to convert to Date/Time type. Thus your (applied) formatting and other options would work on it.

3

@amjad.sahi
I understood what you are mentioning but if the data is more in the excel , it will be time taking to change all the values

Is there a way if we can handle it in the sheet creation itself , can we apply style or something ??

I have a JSON OBJECT which will be converted to EXCEL , if I can somehow manage to maintain the DATA TYPE of the column while creating the sheet then it will be solved right ??

Example JSON ::

[
{
“mongo_id”: “5af05801b87fd”,
“user_id”: “4224”,
“employee id”: “Naveen_09”,
“unique user id”: “4224”,
“first name”: “Vamsi8”,
“last name”: “Asif740”,
“company”: “Naveen AT”,
“gender”: “Male”,
“current fixed ctc”: “30000000”,
“marital status”: “Married”,
“date” : “2022-01-01”
},
{
“mongo_id”: “5af05807ba4f9”,
“user_id”: “4244”,
“employee id”: “Naveen_29”,
“unique user id”: “4244”,
“first name”: “Vamsi28”,
“last name”: “Asif760”,
“company”: “Naveen AT”,
“gender”: “Female”,
“current fixed ctc”: “30000000”,
“marital status”: “Widowed”,
“date” : “2022-02-01”
},
{
“mongo_id”: “5af05810d2653”,
“user_id”: “4274”,
“employee id”: “Naveen_59”,
“unique user id”: “4274”,
“first name”: “Vamsi58”,
“last name”: “Asif790”,
“company”: “Naveen AT”,
“gender”: “Male”,
“current fixed ctc”: “30000000”,
“marital status”: “Single”,
“date” : “2022-03-01”
},
{
“mongo_id”: “5af058134d77b”,
“user_id”: “4282”,
“employee id”: “Naveen_67”,
“unique user id”: “4282”,
“first name”: “Vamsi66”,
“last name”: “Asif798”,
“company”: “Naveen AT”,
“gender”: “Male”,
“current fixed ctc”: “30000000”,
“marital status”: “Committed”,
“date” : “2022-04-01”
},
{
“mongo_id”: “5af14cc9d8b8d”,
“user_id”: “9201”,
“employee id”: “Naveen_4835”,
“unique user id”: “9201”,
“first name”: “Abhi3834”,
“last name”: “Vamsi3834”,
“company”: “Naveen AT”,
“gender”: “Boy”,
“current fixed ctc”: “30000000”,
“marital status”: “LiveIn”,
“date” : “2022-05-01”
},
{
“mongo_id”: “5af14ccc9be57”,
“user_id”: “9205”,
“employee id”: “Naveen_4839”,
“unique user id”: “9205”,
“first name”: “Abhi3838”,
“last name”: “Vamsi3838”,
“company”: “Naveen AT”,
“gender”: “Male”,
“current fixed ctc”: “30000000”,
“marital status”: “Mingled”,
“date” : “2023-05-01”
},
{
“mongo_id”: “5af14ce00450a”,
“user_id”: “9233”,
“employee id”: “Naveen_4867”,
“unique user id”: “9233”,
“first name”: “Abhi3866”,
“last name”: “Vamsi3866”,
“company”: “Naveen AT”,
“gender”: “Girl”,
“current fixed ctc”: “30000000”,
“marital status”: “Widowed”,
“date” : “2023-07-01”
}
]

4

@Veerendra1234,

If you are importing JSON data to Excel spreadsheet via JsonUtility.ImportData() method, you may specify JsonLayoutOptions.ConvertNumericOrDate Boolean attribute to true.

5

HI
i tried it out , but still my data in the sheet is showing up as string filterable only

var layoutOptions = aspose.cells.JsonLayoutOptions()
layoutOptions.ArrayAsTable = true;
layoutOptions.ConvertNumericOrDate = true;
aspose.cells.JsonUtility.importData(JSON.stringify(jsonData), worksheet.getCells(), 0, 0, layoutOptions)

I have written the above code but still my sheet is having data with GENERAL DATA TYPE for DATE columns
Please let me know , if I am missing something here

it would be great , if you could give me a sample working code or similar use case which I can go through once please

Thanks ,
Veerendra

6

@Veerendra1234
Please try the following codes:

    public static void main(String[] args) throws Throwable {
            Workbook workbook = new Workbook();
            Worksheet worksheet = workbook.getWorksheets().get(0);
            JsonLayoutOptions layoutOptions = new JsonLayoutOptions();
            layoutOptions.setArrayAsTable(true);
            layoutOptions.setConvertNumericOrDate(true);
          JsonUtility.importData("{\"mongo_id\": \"5af05801b87fd\",\"date\" : \"2022-01-01\"}", worksheet.getCells(), 0, 0, layoutOptions);
            workbook.save(dir + "dest.xlsx");
              }

If you still have any problem, please share your generated file and Region setting. We will check it soon.