Retain the default date format in Excel cell

askgowda · April 8, 2021, 6:14pm

Hello,
I’m on Aspose Cells for Java 20.2.0

I’m using SheetRender API to convert the spreadsheet into an Image (PNG).
I have an issue when the excel document contains date. For ex: In the attached test file, the column value is 24/07/1988, but the output from Aspose Cells is 7/24/1988.

Below is the sample code snippet I’m using

         worksheet.getPageSetup().setPaperSize(PaperSizeType.PAPER_A_4);
         ImageOrPrintOptions options = new ImageOrPrintOptions();
         options.setPrintingPage(PrintingPageType.IGNORE_BLANK);
         worksheet.getPageSetup().setPrintArea(range);
         SheetRender sr = new SheetRender(worksheet, options);
         sr.toImage(0, g2D);

I’m aware that Aspose is using the Region and Language Code settings and converting it into respective formats.

By using the below settings, the issue is solved

loadOptions.setLanguageCode(CountryCode.INDIA);
¸(CountryCode.INDIA);

I have two questions here

Is there a way to force Aspose Cells to treat the date columns as just a String data (instead of date)?
When using the loadOptions.setLanguageCode and loadOptions.setLanguageCode, the delimiter between date, month, and year got changed to an hyphen (-) from forward slash (/). Is there a way t avoid this?

Note: Cell.setStyle() would not be feasible for me. Please refer to the sample code snipped pasted above

date.zip (6.4 KB)

amjad.sahi · April 8, 2021, 7:03pm

@askgowda,

Aspose.Cells parses data in accordance with the region or locale settings of the system where the code is processed.

No, for Date type or numeric values stored in native Excel files (XLS, XLSX, etc.), Aspose.Cells does not take it as strings. You got to convert the DateTime values to string to be re-inserted into the cells by yourselves for your desired range in the worksheet. Please note, we do have ConvertNumericData and ConvertDateTimeData Boolean attributes in TxtLoadOptions which can be set for text or CSV file formats only.
You already told above that when setting the language and region, it works fine as per your requirements. Here you are saying it is changing delimeter from forward slash to hyphen. Do you still find the issue even after setting the region or language setting? Do you mean the Date “24/07/1988” is changed to “24-07-1988”, please elaborate with details and sample files?

askgowda · April 20, 2021, 8:55am

@Amjad_Sahi

You mentioned that, Aspose.Cells parses data in accordance with the region or locale settings of the system where the code is processed.

But, I have an isssue with this.

I have attached two files - IND.xlsx and UK.xlsx
Both of these files have the same date (24/07/88) in cell no H-14.

When I convert these two documents into PNG using Aspose.Cells on a system having US region & locale, I see that the date format in UK.xlsx is retained, but the date format is IND.xlsx has been changed to U.S locale.

If Aspose Cells is parsing data in accordance with region settings of the system where the code is processed, don’t you think the date in UK.xlsx must have been changed to U.S locale?

testfiles.zip (12.8 KB)

amjad.sahi · April 20, 2021, 9:44am

@askgowda,

This is not an issue in Aspose.Cells by any means. Please open your UK file into MS Excel in US region and locale, you will notice that same date is shown as per the image generated by Aspose.Cells. In short, in the file, the date value is formatted in accordance with Armenian location. See the screenshot attached for your reference.
sc_shot_UK_file_date_shown_in_MS_Excel_in_usa_locale.png (80.4 KB)

askgowda · April 20, 2021, 10:24am

@Amjad_Sahi

For the Test files I attached in my previous comment, can you run the sample Aspose Code snippet I provided and compare the output?

I have attached the Aspose Output for those files in case if you need it handy.

You can clearly see there is a difference in the output for those files.

My question is, why there is a difference in the date format when both of those files are processed on the same machine.

Aspose_Output.zip (68.8 KB)

amjad.sahi · April 20, 2021, 10:48am

@askgowda,

I think you did not check my previous reply with the screenshot. Yes, I did test sample code with both files using Aspose.Cells APIs and found the same results which MS Excel shows (when opening the files into MS Excel manually) in US locale/regional settings.

Please open your UK.xlsx and IND.xlsx files into MS Excel (in US region and locale), you will notice that same date values are shown as per the output images generated by Aspose.Cells APIs. In short, for UK.xlsx file, the date value is formatted in accordance with Armenian location. attached for your reference:
sc_shot_UK_file_date_shown_in_MS_Excel_in_usa_locale.png

PS. please note what (Date values) you see in MS Excel manually in US region/locale when opening the files into it, Aspose.Cells will render the images with same output(s).

askgowda · April 20, 2021, 10:56am

@Amjad_Sahi

Can you attach the snapshot of the Aspose output for both the documents?
I’m surprised because, for me the output is different.

amjad.sahi · April 20, 2021, 11:44am

@askgowda,

Please find attached the output image (using your code segment) for UK.xlsx in US locale/regional settings.
outUK.png (1.7 KB)

askgowda · April 20, 2021, 1:19pm

@Amjad_Sahi

Thank you.
As requested in my previous reply, Can you also attach the output for IND.xlsx please?

I want to compare both the outputs.

amjad.sahi · April 20, 2021, 5:37pm

@askgowda,

Please find the output image against IND.xlsx for your reference.
outIND.png (1.7 KB)

askgowda · April 20, 2021, 6:07pm

@Amjad_Sahi

Well. That explains my question.

When both of those documents are been processed on the same machine, why do we see the difference in the Aspose output?

You mentioned earlier that, “Aspose.Cells parses data in accordance with the region or locale settings of the System where the code is processed”

But, looking at the output, it appears as though the Aspose.Cells is NOT honoring the Locale setttings of the System, instead, its honoring the Locale settings of a Cell within the document

Am sorry if I’m confusing you, please let me know if you need more clarity on my question.

amjad.sahi · April 20, 2021, 7:16pm

@askgowda,

This is due to your UK file’s Date value which is additionally formatted with Armenian location. That’s why it does not care at all about the underlying locale/region settings. In short, when you open the UK.xlsx file into MS Excel manually in US locale/region, you will see same display as per the output image of Aspose.Cells APIs. If you could re-format the Date value (similar to your IND.xlsx file’s date formatting) and re-save the file. Now use the updated file to render to image by Aspose.Cells APIs and you will get desired results (both in MS Excel and in the output image).

Please note only one thing, i.e., Aspose.Cells renders to image/PDF based on what DateTime values are shown in MS Excel (manually) in the underlying locale/regional settings (in your case US region).

Thanks for your understanding!