Hi ,
The data sheet and the filter tab are having 2 different date formats
As you can see in the below screenshot
Screenshot 2023-04-21 at 10.55.41 PM.png (11.0 KB)
Date Value for the column Date of Joining is 09-01-2023 and the format of the date is dd-mm-YYYY ( which is expected )
When I try filtering the Data it shows the filter as Screenshot 2023-04-21 at 10.57.00 PM.jpg (114.0 KB)
Which is incorrect
I was using
jsonLayoutOptions.setConvertNumericOrDate(true); // to make the string type to date type
jsonLayoutOptions.setDateFormat(‘DD-MM-YYYY’); // to set the DateFormat of the Date Fields
Can you please help why the Date format is getting changed in the filter part ???
need some urgent help on this
@Veerendra1234,
Thanks for the screenshots.
I tested your scenario/case a bit with latest version (Aspose.Cells for Java 23.4) using the following sample code, it works fine and as expected:
e.g.
Sample code:
Workbook workbook = new Workbook();
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\" : \"2022-01-09\"}", worksheet.getCells(), 0, 0, layoutOptions);
workbook.save("f:\\files\\dest.xlsx");
Please find attached the output Excel file for your reference.
dest.zip (6.2 KB)
Could you please share sample files (e.g., input Excel file(if any), JSON data/file, output file, etc.) and complete sample (runnable) code that you are using to reproduce the issue, we will check it soon.
Hi ,
you can find the JSON data here in this Zip newData.json.zip (10.0 KB)
I am trying to convert this json data into Excel file which should follow the date formatting
Screenshot 2023-04-21 at 11.51.41 PM.png (28.8 KB)
As you can see in the above screenshot , Date of Birth column is showing 08-12-1981 in the cell area but where as in the top cell it is showing 12-08-1981
I am attaching the excel for you reference , please check it ( you can apply filter on employeeId 101 )
empreporttttt_21-04-2023_42ec9afb-f4f8-442f-a0f9-67e80c558af0.xlsx.zip (267.5 KB)
please do let me know , if you need any other details but need some help on checking this issue …it is creating a lot of confusion while checking the data and applying filters
@Veerendra1234 The MS Excel application sets the data format in the cells of the spreadsheet. In the formula bar, the data is displayed according to the system format, which is not an issue, but the behavior of the MS Excel app.
If you need to continue processing the workbook using Aspose.Cells API, please always use the Value property of the cell instead of the Formula.
Hi ,
i didn’t understand , if the format is getting changed as per the system format then how come only one column is getting effected , not the other date columns
And moreover , the column type is showing as CUSTOM which ideally should be of Date
As per my understanding
jsonLayoutOptions.setConvertNumericOrDate(true);
should convert the string date fields into Date Columns right ??
Any reason , why it is not happening sometimes ?
As you can see , Column is coming as Date for one column in excel
Screenshot 2023-04-22 at 12.25.12 AM.png (126.6 KB)
Here is one more screenshot where the Type is Custom ( ideally it should have been date right ?? )
Screenshot 2023-04-22 at 12.35.15 AM.png (146.0 KB)
is there any setting which I am missing to add ??
because of this difference in types , the data in the filters is correct for one column and incorrect for the other so need some help in fixing that
@Veerendra1234 I can see the reported issue in the document that you uploaded, but I cannot reproduce it using the latest version of Aspose.Cells API (Aspose.Cells for Java 23.4) and the code provided by @amjad.sahi (in this thread), but using the json data provided by you.
This is my output: output.zip (39.0 KB)