Dates when using Aspose to convert from Json to Excel

Aspose.Cells Product Family
1

Hi, we are using Aspose and it works great. Love it. But, one pretty simple thing we do is allow customers to download any report data as Excel. We do this by simply generating the JSON file and using Aspose to convert this to Excel. That works well, except when the JSON includes a date. Obviously JSON doesnt really have a date format, so these columns in the JSon file are strictly just strings. This means the resulting excel has a string column instead of a date/time column. You cannot then format this, or use any formulae or even sort by it in the resulting Excel file. Is there some special format we can use in the JSON file that will allow Aspose to recognise the data as a date/time and populate the excel file correctly?

The data we are supplying at the moment is a UTC format like this “2018-12-28T00:31:28.125412Z” - is that the issue? (given that excel doesnt seem to have any concept of UTC?

Or is there something else we should be doing?

2

@Andy_A,

Thanks for the details.

Well, you have to convert your date strings in JSON in real Date/Time values understood and supported by MS Excel by yourselves. Aspose.Cells follows MS Excel standards and speficications, so if your so called Date string is not parsed in MS Excel (you may test it in MS Excel by manually inserting it), then Aspose.Cells might not parse it as well.

3

Thanks for this information, do you know what formats are actually supported? either Aspose advice or a link to relevant microsoft information. Just to save a potentially fruitless trial and error process?

4

Ultimately i suspect we can apply any format mask we like to the date when we export it - so I dont really need a choice of formats, i probably just need to know what format would work best for the aspose/excel processing of a json file?

5

@Andy_A,

Aspose.Cells follows MS Excel standards and any Date/Time format which is supported by MS Excel should be supported by Aspose.Cells. If you find any format which is supported by MS Excel and not by Aspose.Cells, we can look into it and provide support (if possible). You may check and evaluate formats in MS Excel manually using Format Cells… dialog box.

And, you got it right, you should convert/mask your Date/Time strings to MS Excel supported formats.

6

@Andy_A,

After further evaluation, we have logged a ticket for importing data from JSON file. The ticket is logged as following:
CELLSNET-46553 - Support importing Json file

Once we have an update on it, we will let you know.

7

@Andy_A,

This is to inform you that we have fixed your issue now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

8

The issues you have found earlier (filed as CELLSNET-46553) have been fixed in Aspose.Cells for .NET v19.4. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi