The built-in Excel time format (displays as *3/14/01 in Office for Mac) is incorrectly represented in Aspose for Java as Number 14 which reflects a four-digit-year. The resulting string is represented as a four-digit-year rather than two-digit as displayed in Excel.
So 8/11/87 displays as 8/11/1987 instead (when converted to PDF for example).
Number14.zip (36.8 KB)
@workiva_rjackson
Could you please share a PDF file created by MS Excel and what is default locale for your application. You can get it in your application by code:
System.out.println(Locale.getDefault());
Or you can find it from clock settings. Also share a screenshot of those settings, JDK details and the code snippet to convert this workbook to PDF.
I will put together a sample project today or tomorrow but in the interim here is the other information you requested:
System.out.println(Locale.getDefault());
en_US
java version “1.8.0_102”
Java™ SE Runtime Environment (build 1.8.0_102-b14)
Java HotSpot™ 64-Bit Server VM (build 25.102-b14, mixed mode)
Updated ZIP archive which includes the PDF produced by Excel (Office for Mac 2011):
Number14.zip (55.9 KB)
@workiva_rjackson,
When I open your template file Number14.xlsx file in Excel in Mac, it shows “11/08/1987”. When this file is changed to PDF using Aspose.Cells for Java 19.5.5 and Excel, both the PDF files show four digit year.
I changed the date format using Excel such that it shows two digits year. In this case Aspose.Cells also creates PDF with two digits year. This shows that this is not an issue with Aspose.Cells. Whatever the date format is shown in Excel, the same is rendered to PDF using Aspose.Cells. Sample output files are attached here for your reference. Please give it a try using the latest version 19.5.x and share the feedback.
Number14OriginalByAspose19.5.5.pdf (40.2 KB)
outputByAspose19.5.5.pdf (39.7 KB)
I can try that version later today. This is a screenshot of the display in my version of Excel for Mac 2011.
Screen Shot 2019-06-06 at 10.37.03.png (25.2 KB)
@workiva_rjackson,
You may please also send us the image of the property page showing the format of the cell having date value as follows:
Screenshot 2019-06-06 at 9.57.36 PM.jpg (407.8 KB)
Sure. I notice that my Calendar option (the only choice available in the Excel UI) is “Western” whereas yours shows “Gregorian”.
Screen Shot 2019-06-06 at 11.25.11.png (70.0 KB)
Here is a sample project demonstrating the most basic use of the API.
AsposeExcelDateIssue.zip (295.4 KB)
@workiva_rjackson,
Well, I have gone through the information and feel that its difference in the behavior of Excel 11 which you are using in your environment. When I simply open your file in my Excel 2016 in macOS, it shows 4 digits year and same format settings. Using Aspose.Cells, when the same file is converted to PDF, four digits are shown in the year as expected.
Now when I open the template workbook in Excel and set the formatting to two digits in the year and save it. When this resaved file is converted to PDF using Aspose.Cells, the year part shows two digits as expected.
I am afraid that we are not able to observe any issue while comparing the behavior of Excel and Aspose.Cells, therefore we cannot resolve this issue. You may please test this scenario using the latest Excel version in macOS and share your feedback.
I’ve attached a screenshot of the actual format Excel is using (experience demonstrates that when a built-in format is selected and one then selects the “Custom” option, the raw format of that built-in selection is displayed).
In this case it is “m/d/yy”. If I select this “Custom” format, I still get a four-digit year in the PDF. Perhaps Aspose Cells needs to account for the Calendar settings (if available in the XML) and/or source of the Excel file (Office for Mac 20xx, etc.) to account for the changing behavior of these built-in types.
Screen Shot 2019-06-07 at 07.57.39.png (25.7 KB)
@workiva_rjackson,
Although we were not able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix (if applicable). Once, we will have some news for you, we will update you in this topic.
This issue has been logged as
CELLSJAVA-42925 - Built-in Excel time format not followed in the rendered PDF
Great. Thank you. I examined the individual XML files in the Excel file I’ve sent you and all I see is use of <xf numFmtId="14" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>. This suggests it is internal to Excel and probably affected by Locale.
@workiva_rjackson,
It looks like you have identified the issue in your Excel file and hence it is not an issue with Aspose.Cells. Do you need any further assistance from our side or we may close this ticket?
No, that isn’t what I am saying at all. For an Excel file (on the same machine as being processed by Aspose Cells for Java) that displays as “m/d/yy”, the Aspose transformation causes it to display as “m/d/yyyy”. It is possible that Aspose is not respecting the current Locale or whatever Excel uses to determine the style. This looks to me to be an issue in Cells - it needs to report “m/d/yy” as the (correct) format string.
@workiva_rjackson,
Thank you for the feedback. We have recorded your comments with the ticket and will consider them while working on this issue. We will write back here once any feedback is ready to share.
Yes, the settings for the number formatting is only the built-in number 14. In fact it is just the main trouble: we have no other information which can help to determine the digits of the year.
We have tested on mac with Excel 11 and 16 but unfortunately, we can only get the year displayed as four digits, just same with what we got with MS Excel on windows. Obviously, it is not the same result with yours.
Of course the formatted results are different for different locales for the same built-in format and we also support this dynamic change. However, we do not know how the formatted result changes according to different platforms and different MS Excel versions. For this case, as we said, we got different results from the yours even with the same Excel version on mac.
We are not sure whether the Calendar option may cause such kind of difference because we found there is only one value for it can be selected. So we cannot change this options for testing purpose. We are afraid we cannot find a way to gather enough information about the rules used for this case and cannot fix it currently. To make the year be formatted as two digits, you may use custom format instead as a workaround.
Thank you for looking into the problem. Based on the XML I was afraid that this might be the case. Thanks again.
You are welcome. And, hopefully you could sort out your issue accordingly either using your own way or using custom format (as proposed).