Bug 75130 - Cell Borders not exported properly to XLS
Summary: Cell Borders not exported properly to XLS
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.1.1 release
Hardware: Other All
: highest critical
Assignee: Kohei Yoshida
URL:
Whiteboard: target:4.3.0 target:4.2.2
Keywords: regression
: 52578 75143 75546 75638 76533 76547 (view as bug list)
Depends on:
Blocks:
 
Reported: 2014-02-18 06:01 UTC by Ashok
Modified: 2016-02-22 13:59 UTC (History)
9 users (show)

See Also:
Crash report or crash signature:


Attachments
document before save (28.48 KB, image/jpeg)
2014-02-20 04:38 UTC, Jaise James
Details
document on save (61.66 KB, image/jpeg)
2014-02-20 04:39 UTC, Jaise James
Details
document after reopening (37.81 KB, image/jpeg)
2014-02-20 04:39 UTC, Jaise James
Details
Test document that contains ALL Excel line types. (13.50 KB, application/vnd.ms-excel)
2014-02-20 15:21 UTC, Kohei Yoshida
Details
screenshot, LiO v 4.2.4.2 (same applies for 4.2.5.2) (157.01 KB, image/png)
2014-06-20 20:37 UTC, Alex
Details
screenshot, LiO v 4.2.4.2 (same applies for 4.2.5.2) (157.01 KB, image/png)
2014-06-20 20:38 UTC, Alex
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Ashok 2014-02-18 06:01:23 UTC
Certain Cell borders made with Calc (4.2.1.1 RC1) are lost when saved to XLS and reopened with Calc
I feel that most of the issues are export related as import of borders from a XLS file made using Excel is almost perfect now.

The following export works fine
Single Line Borders 0.75 -> Translates to normal Excel Border
Single Line Borders 1.75 -> Translates to thick Excel Border
Single Line Borders 2.50 -> Translates to thickest Excel Border
Dots 0.75 -> Translates to Excel Dots
Long Dash 1.75 -> Translates to Thick Excel Dash
All Double Lines -> Translates to Excel Double Line

The export issues are:
Single Line Borders 0.05 -> not saved (should be saved as hairline)
Dots 0.05 -> not saved (should be saved as 0.75 dots)
Dots 1.75 -> saved as Border 1.75  (should be saved as 0.75 dots...?)
Dots 2.50 -> saved as Border 2.50 (should be saved as 0.75 dots...?)
Dash 0.05 and 0.75 -> not saved (should be saved as 0.75 dash)
Dash 1.75 -> saved as Border 1.75  (should be saved as 0.75 dash...?)
Dash 2.50 -> saved as Border 2.50 (should be saved as 0.75 dash...?)
Long Dash 0.05 -> not saved
Long Dash 0.75 -> saved as Single Line Border 0.05
Long Dash 2.50 -> saved as Single Line Border 2.50 

Unlike Cacl, excel has no option to change the border thickness.  The thickness is assigned with the style. So we cannot expect all Borders in calc to be saved as xls. I think some issues can be avoided if the default line thickness in calc is made as 0.75 to match that of Excel.
Comment 1 Kevin Suo 2014-02-18 06:06:03 UTC
Repro in 4.2.1.1, ubuntu 13.10 x86. Is a regression against 4.2.0 release.

Set as Highest Critial.
Comment 2 Kevin Suo 2014-02-18 06:08:30 UTC
This issue not only happen when export to .xls, but also .xlsx.
Comment 3 Jaise James 2014-02-20 04:38:54 UTC
Created attachment 94402 [details]
document before save
Comment 4 Jaise James 2014-02-20 04:39:20 UTC
Created attachment 94403 [details]
document on save
Comment 5 Jaise James 2014-02-20 04:39:37 UTC
Created attachment 94404 [details]
document after reopening
Comment 6 Jaise James 2014-02-20 04:40:11 UTC
Any New Border added on cells getting lost on saving as xls file. 
Version with problem is 4.2.1 rc1. 

Used OS windows XP sp3

Existing border preserves saved using old versions & copy-paste also preserves on save. But If add a new border, on save it lost & reopen shows no border.
Comment 7 Michael Stahl 2014-02-20 13:05:40 UTC
guess it's most likely due to big border re-write (commit 9a19e8d838753128504274e1885eb3ce8ec1dbb8)
Comment 8 Kohei Yoshida 2014-02-20 15:19:40 UTC
In my testing, only the hairline is lost on save, while the rest look okay.
Comment 9 Kohei Yoshida 2014-02-20 15:21:39 UTC
Created attachment 94443 [details]
Test document that contains ALL Excel line types.

Test document that contains ALL Excel line types.
Comment 10 Kohei Yoshida 2014-02-20 15:23:23 UTC
I'll focus on just preserving the Excel line types during opening and re-saving.  The rest are different issues that should be handled in different bugs (due to the larger scope of the whole border line issues).
Comment 11 Kohei Yoshida 2014-02-20 15:39:14 UTC
And the dashed line becomes hairline on resave.  Those 2 are the only issues I see.
Comment 12 Commit Notification 2014-02-20 16:30:07 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=6d5270dab5c8ea8bae1ac3462aa5f6768f7d2c8f

fdo#75130: Move these tests to the export test and test their export.



The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 13 Commit Notification 2014-02-20 16:30:20 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=1c1f42fdbad774cae7c45a7f8d60b9af766dee0a

fdo#75130: Export cell borders to xls and xlsx correctly.



The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 14 Kohei Yoshida 2014-02-20 16:31:31 UTC
4.2 backport request: https://gerrit.libreoffice.org/8147
Comment 15 Ashok 2014-02-20 19:18:47 UTC
Not only single line, Dots, dash and Long Dash of 0.05 thick are not saved when exported to XLS also dash of 0.75 is not saved
dots and dash of 1.75 and 2.5 thickness are saved as single line of 1.75 and 2.5

In import from XLS only Dash Dot Dash and Dot Dot Dash are not imported properly (are imported as single line 0.75 thick).  We cannot expect calc to import it as such since the two formats are not supported in Calc.  However instead of single line 0.75 thick, it could have been imported as dashes.
Comment 16 Ashok 2014-02-20 19:21:40 UTC
I don't know if I should be asking this, but can border styles/thickness not supported by Excel be made to hide from the border menu when the file save type is XLS or if the file opened is XLS
Comment 17 Kohei Yoshida 2014-02-20 19:25:20 UTC
(In reply to comment #16)
> I don't know if I should be asking this, but can border styles/thickness not
> supported by Excel be made to hide from the border menu when the file save
> type is XLS or if the file opened is XLS

Please don't do this here. Focus on the bug reported.
Comment 18 Kohei Yoshida 2014-02-20 19:31:10 UTC
(In reply to comment #15)
> Not only single line, Dots, dash and Long Dash of 0.05 thick are not saved
> when exported to XLS also dash of 0.75 is not saved
> dots and dash of 1.75 and 2.5 thickness are saved as single line of 1.75 and
> 2.5
> 
> In import from XLS only Dash Dot Dash and Dot Dot Dash are not imported
> properly (are imported as single line 0.75 thick).  We cannot expect calc to
> import it as such since the two formats are not supported in Calc.  However
> instead of single line 0.75 thick, it could have been imported as dashes.

Don't mix multiple issues please. As I said in Comment 10 which I repeat here

"I'll focus on just preserving the Excel line types during opening and re-saving.  The rest are different issues that should be handled in different bugs (due to the larger scope of the whole border line issues)."

Other issues such as missing border types MUST be handled separately from this bug.  Thanks for your corporation.
Comment 19 Kohei Yoshida 2014-02-20 19:50:05 UTC
And just be upfront, those line types that we don't currently support will NOT be retained when re-saving to Excel. They will become solid lines for now.  When we have a chance to support those missing line types, those types will be saved back to Excel intact.
Comment 20 Commit Notification 2014-02-20 23:57:25 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-4-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=d6d06b230bf923492c4a6ff42781cca25a28ef25&h=libreoffice-4-2

fdo#75130: Export cell borders to xls and xlsx correctly.


It will be available in LibreOffice 4.2.3.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 21 Kohei Yoshida 2014-02-21 00:09:40 UTC
I know we still have a lot of work to be done on borders.  These things take time and we can only do it one at a time.
Comment 22 Kohei Yoshida 2014-02-21 00:10:06 UTC
Fixed.
Comment 23 Kohei Yoshida 2014-02-24 03:57:58 UTC
*** Bug 75143 has been marked as a duplicate of this bug. ***
Comment 24 Commit Notification 2014-02-25 22:58:35 UTC
Kohei Yoshida committed a patch related to this issue.
It has been pushed to "libreoffice-4-2-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=a09cedbcea86e0af0db1ced6df6cf95ab5ba946b&h=libreoffice-4-2-2

fdo#75130: Export cell borders to xls and xlsx correctly.


It will be available already in LibreOffice 4.2.2.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 25 m.a.riosv 2014-02-26 23:02:27 UTC
*** Bug 75546 has been marked as a duplicate of this bug. ***
Comment 26 m.a.riosv 2014-03-02 02:01:10 UTC
*** Bug 75638 has been marked as a duplicate of this bug. ***
Comment 27 Kohei Yoshida 2014-03-08 08:09:07 UTC
*** Bug 52578 has been marked as a duplicate of this bug. ***
Comment 28 m.a.riosv 2014-03-24 21:47:27 UTC
*** Bug 76533 has been marked as a duplicate of this bug. ***
Comment 29 m.a.riosv 2014-03-24 22:21:13 UTC
*** Bug 76547 has been marked as a duplicate of this bug. ***
Comment 30 Alex 2014-06-20 20:37:32 UTC
Created attachment 101453 [details]
screenshot, LiO v 4.2.4.2 (same applies for 4.2.5.2)
Comment 31 Alex 2014-06-20 20:38:47 UTC
Created attachment 101454 [details]
screenshot, LiO v 4.2.4.2 (same applies for 4.2.5.2)

Versions 4.2.4 and 4.2.5 (maybe 4.2.3) are turning solid outline of the cell thinner than 0,75pt into dot outline when exporting in excel .xls or .xlsx

Not sure if it's a bug or a feature but assuming that you can't (in easy way at least) set the default thickness of outline once for every new created document, this behaviour is kind of very annoying

Here's the screenshot of what I'm talking about
Comment 32 Kevin Suo 2014-06-21 00:12:08 UTC
(In reply to comment #31)

@Alex:
> Versions 4.2.4 and 4.2.5 (maybe 4.2.3) are turning solid outline of the cell
> thinner than 0,75pt into dot outline when exporting in excel .xls or .xlsx

For this behaviour you mentioned above, I have already filed a bug report:
Bug 79787 - Normal cell borders are showing dashed/dotted when export to xlsx/xls and reopen in MSO
I add it to SEE ALSO of this bug.

However, for this bug (75130), it had been UNCONFIRMED->NEW->RESOLVED-FIXED. Althrough the issue you mentioned may be related, I don't think you should set this back to UNCONFIRMED.
I'll add you to bug 79787 cc list, and I am setting this one back to RESOLVED FIXED