Bug 136236 - PDF Export of Excel file date format
Summary: PDF Export of Excel file date format
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Printing and PDF export (show other bugs)
Version:
(earliest affected)
6.4.4.2 release
Hardware: All Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: PDF-Export
  Show dependency treegraph
 
Reported: 2020-08-28 16:36 UTC by mrogers
Modified: 2022-10-17 15:22 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Attachments
This is the test file I have been using (5.38 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2020-08-28 16:39 UTC, mrogers
Details
this is the result (9.50 KB, application/pdf)
2020-08-28 16:53 UTC, BogdanB
Details
This is my result (9.44 KB, application/pdf)
2020-08-28 17:05 UTC, mrogers
Details
I have done what I told you to do, and this is the result. It's ok. (9.27 KB, application/pdf)
2020-08-28 17:58 UTC, BogdanB
Details
the exact file for testing (5.49 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2020-09-01 18:48 UTC, BogdanB
Details
video showing the bug (6.62 MB, video/mp4)
2020-09-01 18:48 UTC, BogdanB
Details

Note You need to log in before you can comment on or make changes to this bug.
Description mrogers 2020-08-28 16:36:45 UTC
Description:
When converting a .xlsx file to PDF if the excel file contains multiple source date formats then the output PDF outputs all dates in the Locale specific date format regardless of the style within excel.   

 

Steps to Reproduce:
1.Create a document in Excel with multiple columns.   Format column a as numeric with British date format.   Format column b as numeric with American date format
2./opt/libreoffice6.4/program/soffice  --headless --invisible --nodefault --view --nolockcheck --nologo --norestore --nofirststartwizard --convert-to pdf /mer/MultiDates.xlsx
3.Look at the generated PDF document.  And observe that the dates are all in the same format

Actual Results:
A PDF export of the .xlsx document is produced,  however all the date fields are in the same format.

Expected Results:
I expected that the exported PDF would match what I see in Excel with regard to the date style.


Reproducible: Always


User Profile Reset: No



Additional Info:
Note that libra office itself is able to display the 2 columns in different different date formats.

The date format produced is dependent upon the LANG environment vars.  So I can switch all dates to American or all dates to British.
Comment 1 mrogers 2020-08-28 16:39:56 UTC
Created attachment 164820 [details]
This is the test file I have been using
Comment 2 BogdanB 2020-08-28 16:53:37 UTC
Created attachment 164821 [details]
this is the result

This is the result after generating from your document. It is ok?...
Comment 3 mrogers 2020-08-28 17:03:34 UTC
I might be having problems with bugzilla but that file does not correct.   I see a grey background only.
Comment 4 mrogers 2020-08-28 17:05:12 UTC
Created attachment 164822 [details]
This is my result

Note that the US Dates and the UK dates are in the same format,  one of them is wrong
Comment 5 BogdanB 2020-08-28 17:52:04 UTC
Download my attachment if you have problem in your broiwser, and open the file from your folder, not with the help of the browser.
Comment 6 BogdanB 2020-08-28 17:56:19 UTC
What I see is that dates are formatted by your locales, and the same in my case.

From what I have tested you have to select for example the "UK Date" fields and right click - Format Cells - Number - and form here change the language to English - UK. 

The same in US DATE, change to English (USA).

And now see if this works.
Comment 7 BogdanB 2020-08-28 17:58:40 UTC
Created attachment 164824 [details]
I have done what I told you to do, and this is the result. It's ok.
Comment 8 mrogers 2020-09-01 09:21:09 UTC
I managed to download your attachments.    

The attachemnt "this is the result" shows the problem.  In particular the American date column is displayed as dd/mm/yyyy 


Your second attachment"I have done what I told you to do, and this is the result. It's ok."   Shows the result I would expect.
Comment 9 BogdanB 2020-09-01 09:41:07 UTC
In this case you have to follow this steps:
-------------------
- You have to select for example the "UK Date" fields and right click - Format Cells - Number - and form here change the language to English - UK.
- The same in US DATE, change to English (USA).
-------------------

Please do this, and I am waiting for your answer if it worked.
Comment 10 mrogers 2020-09-01 10:29:02 UTC
I've repeated those steps on a new file and got the same incorrect results.   

So both columns have American format so 9/11/2001 rather that 11/09/2001 and 09/11/2001.

For clarification I have done the formatting in Excel rather than Libra Office.   Office 365 version 16.36.
Comment 11 BogdanB 2020-09-01 11:41:26 UTC
I've repeated those steps on a new file and got the same incorrect results.   

So both columns have American format so 9/11/2001 rather that 11/09/2001 and 09/11/2001.
---> You need to change the format as I told you. With LibreOffice or Excel.

For clarification I have done the formatting in Excel rather than Libra Office.   Office 365 version 16.36.
---> You need to change the format as I told you. With LibreOffice or Excel.

If you open the file with LibreOffice and the format Amercian for American column and UK format for UK column, and still shows wrong than is a LibreOffice bug. 


Waiting for your tests.
Comment 12 mrogers 2020-09-01 12:43:11 UTC
If I create the test file in Libra Office then it does work correctly and the UK dates and USA dates are formatted correctly.
Comment 13 BogdanB 2020-09-01 14:54:50 UTC
So, I think the problem is in Excel. It's not a LibreOffice bug.
Comment 14 mrogers 2020-09-01 16:05:05 UTC
How can it be an Excel Bug when the transformed output from the .XLSX to PDF does not match what libra-office (or Excel) would itself display?    

There is a discrepancy in the convert to PDF logic.
Comment 15 BogdanB 2020-09-01 18:47:42 UTC
Sorry, I tested now this. I see the problem now.
I created a video in order to see the problem.

When exported the PDF from Export to PDF from the file itself it's ok (example 3 in the video), but when generated from terminal is not ok (example 2 in the video). Example 1 is not ok, because the file itself is not ok set up.

Confirm with
Version: 7.1.0.0.alpha0+
Build ID: e69f547bce7de376a0af464c5f7af5e7d2c8784a
CPU threads: 4; OS: Linux 5.4; UI render: default; VCL: gtk3
Locale: ro-RO (ro_RO.UTF-8); UI: en-US
TinderBox: Linux-rpm_deb-x86_64@86-TDF, Branch:master, Time: 2020-06-23_15:52:52
Calc: threaded
Comment 16 BogdanB 2020-09-01 18:48:11 UTC
Created attachment 164986 [details]
the exact file for testing
Comment 17 BogdanB 2020-09-01 18:48:33 UTC
Created attachment 164987 [details]
video showing the bug
Comment 18 BogdanB 2020-09-01 18:49:23 UTC
The code in order to test this
------------------

./instdir/program/soffice  --headless --invisible --nodefault --view --nolockcheck --nologo --norestore --nofirststartwizard --convert-to pdf MultipleDatesTest.xlsx
Comment 19 QA Administrators 2022-09-02 03:38:52 UTC Comment hidden (obsolete)
Comment 20 BogdanB 2022-10-17 15:22:34 UTC
Seems to be resolved until now.
Pleasse check with a new version.

I have tested with this version and everything seems fine also on export and from terminal.
Version: 7.5.0.0.alpha0+ / LibreOffice Community
Build ID: a36173359d4614a8935e2f764acadfcf736e091c
CPU threads: 16; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: ro-RO (ro_RO.UTF-8); UI: en-US
Calc: threaded