Download it now!
Bug 79787 - Normal cell borders are showing dashed/dotted when export to xlsx/xls and reopen in MSO
Summary: Normal cell borders are showing dashed/dotted when export to xlsx/xls and reo...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: LibreOffice (show other bugs)
Version:
(earliest affected)
4.3.0.0.beta2
Hardware: Other Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:5.2.0 interopability
Keywords:
: 79788 82877 88743 (view as bug list)
Depends on:
Blocks: Cell-Border XLSX-Limitations
  Show dependency treegraph
 
Reported: 2014-06-08 07:26 UTC by Kevin Suo
Modified: 2019-02-25 21:39 UTC (History)
16 users (show)

See Also:
Crash report or crash signature:


Attachments
xlsx file with cell borders saved by libreoffice 4.3.0 beta2 (4.28 KB, application/vnd.openxmlformats-officedocument.spreadsheetml)
2014-06-08 07:26 UTC, Kevin Suo
Details
screenshot showing the file open with MSO 2010 (65.95 KB, image/png)
2014-06-08 07:39 UTC, Kevin Suo
Details
Example ODS for testing (9.77 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-01-15 13:55 UTC, Juergen Funk (CIB)
Details
Picture of the Lines LO and MS (75.70 KB, image/jpeg)
2016-01-15 13:57 UTC, Juergen Funk (CIB)
Details
New Mapping LO-Excel-Lines (209.32 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-02-10 11:21 UTC, Juergen Funk (CIB)
Details
New Mapping LO-Excel-Lines 1.1 (210.63 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-02-23 07:21 UTC, Juergen Funk (CIB)
Details
New Mapping LO-Excel-Lines 1.2.ods (210.93 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-09-28 05:50 UTC, Juergen Funk (CIB)
Details
Comparison LO 6.2 vs MS Excel 2010 (132.73 KB, image/png)
2018-11-28 06:02 UTC, Roman Kuznetsov
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Kevin Suo 2014-06-08 07:26:44 UTC
Created attachment 100636 [details]
xlsx file with cell borders saved by libreoffice 4.3.0 beta2

Problem description:
When apply borders to cells by clicking the toolbar icon, then save as xlsx or xls format, when repened with MSO the borders are showing dashed/dotted rather than normal line borders.

Steps to reproduce:
1. New spreadsheet, apply borders from the toolbar icon to some cells.
2. Save as xlsx or xls format.
3. Reopen with MSO 2010.

Current behaviour:
Borders are showing dashed/dotted when reopen with MSO 2010.

Expected:
Borders should show normal line when reopen with MSO at step 3.

OS: Windows XP SP3
Reproducible in version 4.3.0 beta2, and version 4.2.5.1.
Comment 1 Kevin Suo 2014-06-08 07:36:04 UTC
Bug 79788 and bug 79787 may be the same issue, but I reported as to saparated bugs. Adding as see also.
Comment 2 Kevin Suo 2014-06-08 07:39:39 UTC
Created attachment 100638 [details]
screenshot showing the file open with MSO 2010
Comment 3 Jacques Guilleron 2014-06-10 10:20:39 UTC
Hello suokunlong,

Repro with LO 4.3.0.0.beta2+
Build ID: 4b5975b1f777c85259bc38afbfae8e1160fbebbe
TinderBox: Win-x86@42, Branch:libreoffice-4-3, Time: 2014-06-08_21:29:21
Windows 7 Home Premium.

Set Status to New.

Jacques
Comment 4 Kevin Suo 2014-06-21 00:17:12 UTC
Alex's comment at:
https://bugs.freedesktop.org/show_bug.cgi?id=75130#c31
may be the same issue as this bug, added bug 75130 to see also.

Also, this bug may be caused by the fix of bug 75130.
Comment 5 Urmas 2014-08-21 01:29:22 UTC
*** Bug 82877 has been marked as a duplicate of this bug. ***
Comment 6 Kevin Suo 2014-10-24 06:31:46 UTC
Added Kohei Yoshida  to cc list, as he worked on the fix to bug 73487.
Comment 7 QA Administrators 2015-12-20 16:11:40 UTC Comment hidden (obsolete)
Comment 8 Juergen Funk (CIB) 2016-01-15 13:55:25 UTC
Created attachment 121955 [details]
Example ODS for testing
Comment 9 Juergen Funk (CIB) 2016-01-15 13:57:14 UTC
Created attachment 121956 [details]
Picture of the Lines LO and MS
Comment 10 Juergen Funk (CIB) 2016-01-15 14:18:48 UTC Comment hidden (obsolete)
Comment 11 Juergen Funk (CIB) 2016-02-10 11:21:32 UTC
Created attachment 122494 [details]
New Mapping LO-Excel-Lines
Comment 12 Juergen Funk (CIB) 2016-02-10 11:23:54 UTC
I have made a new document look to "New Mapping LO-Excel-Lines"  you can see the current mapping, and my suggestion how we can do other, what do you mean, other suggestion, or it is okay and i can change!?


AND FORGET the comment:10
Comment 13 Yousuf Philips (jay) (retired) 2016-02-22 21:31:52 UTC
@Juergen: Is it not possible to extend LO's available list of line styles, so we could have a 1:1 relationship with MSO? If not, having interoperability work correctly is a good option, but what happens when an unknowing user selects one of the styles which is mapped to a different looking style and then export it to xlsx?
Comment 14 Juergen Funk (CIB) 2016-02-23 07:21:27 UTC
Created attachment 122897 [details]
New Mapping LO-Excel-Lines 1.1

A new description of the mapping
Comment 15 Juergen Funk (CIB) 2016-02-23 07:24:48 UTC
Hi Yousuf (Jay) Philips,
i do not change the available Line-Styles, at the moment we have and after my patch are always 7. I only change the mapping Line-Style to MS-Lines, and this mapping i descript in "New Mapping LO-Excel-Lines 1.1"
At the moment i work on the Unit-Test for the mapping (the mapping is complete), now. It is arranged with the design-committee.

Hope it is okay, for you
Comment 16 Yousuf Philips (jay) (retired) 2016-02-24 01:16:29 UTC
(In reply to Juergen Funk (CIB) from comment #15)
> i do not change the available Line-Styles, at the moment we have and after
> my patch are always 7.

Yes i was aware you didnt change the line-styles, but the question was whether it was possible to do so.

> I only change the mapping Line-Style to MS-Lines, and
> this mapping i descript in "New Mapping LO-Excel-Lines 1.1"
> At the moment i work on the Unit-Test for the mapping (the mapping is
> complete), now. It is arranged with the design-committee.

I have added it to the design PAD so it can be discussed this friday at the design meeting.
Comment 17 Juergen Funk (CIB) 2016-02-24 09:00:54 UTC
Hi  Yousuf (Jay) Philips,

i think it is a good idea, but has nothing to do with this bug. 
I mean a new Line-Styles are a new feature, we have 17 different BorderLineStyles
look here: http://api.libreoffice.org/docs/idl/ref/namespacecom_1_1sun_1_1star_1_1table_1_1BorderLineStyle.html
but for example the MS-Line Slant-DashDot is missing, and the other LineStyles not all equal to the MS-Lines, then we need new BorderLines. 

I'm testing now the Unit-test and want commit, it is okay for you (we need this patch as soon as possible).

When we improve the BorderLineStyle then we should improve this mapping and the Unit-Test
Comment 18 Juergen Funk (CIB) 2016-02-24 14:08:11 UTC
I have push to gerrit here: https://gerrit.libreoffice.org/22665

have tested under windows, linux, the good and bad case.
That is my first unit-test, hope it is okay
Comment 19 Pedro 2016-03-03 14:22:04 UTC
Maybe this older bug report is also related

https://bugs.documentfoundation.org/show_bug.cgi?id=77369
Comment 20 Yousuf Philips (jay) (retired) 2016-03-03 19:49:27 UTC
(In reply to Pedro from comment #19)
> Maybe this older bug report is also related
> 
> https://bugs.documentfoundation.org/show_bug.cgi?id=77369

Nope not related as this is about compatibility and that is about functionality.
Comment 21 Commit Notification 2016-03-31 12:56:34 UTC
Juergen Funk committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=51737960911d41593ffd9792a6a85aeaa86824fd

tdf#79787 Normal cell borders are showing dashed/dotted reopen in MSO

It will be available in 5.2.0.

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 22 Cor Nouws 2016-03-31 16:32:05 UTC
ah wow :)
Comment 23 raal 2016-04-10 06:39:24 UTC
*** Bug 99190 has been marked as a duplicate of this bug. ***
Comment 24 Roeland 2016-07-18 18:02:49 UTC
*** Bug 88743 has been marked as a duplicate of this bug. ***
Comment 25 Robinson Tryon (qubit) 2016-08-25 05:49:29 UTC Comment hidden (obsolete)
Comment 26 Xisco Faulí 2016-09-26 11:22:36 UTC
Hello Juergen,
Is this bug fixed?
If so, could you please close it as RESOLVED FIXED?
Comment 27 Juergen Funk (CIB) 2016-09-27 06:56:32 UTC
Fixed and tested
Comment 28 Kevin Suo 2016-09-27 07:51:56 UTC
(In reply to Juergen Funk (CIB) from comment #27)
Bug still exists under linux.

Steps:
1. New Calc;
2. Apply border using the toolbar icon; (the border is solid)
3. Save as xlsx/xls and reopen with MSO. (the border is dashed)

Version: 5.2.2.2
Build ID: 8f96e87c890bf8fa77463cd4b640a2312823f3ad
CPU Threads: 4; OS Version: Linux 4.4; UI Render: default; 
Locale: zh-CN (zh_CN.UTF-8); Calc: group
Ubuntu 16.04 LTS x64.

Am I missing something?
Comment 29 Roman Kuznetsov 2016-09-27 09:23:52 UTC
bug repro in 

Version: 5.3.0.0.alpha0+
Build ID: 70c7e82003a539ed7f7ccbe596bde5ac9031d15c
CPU Threads: 4; OS Version: Windows 6.1; UI Render: default; 
TinderBox: Win-x86@42, Branch:master, Time: 2016-09-23_06:53:48
Locale: ru-RU (ru_RU); Calc: group
Comment 30 Juergen Funk (CIB) 2016-09-28 05:42:46 UTC
Hi Kevin Suo,

that was my first idea, you not have the right version, and of the other hand, not all mapping between LO and MS are possible, in this case you can look to the attachment "New_Mapping_LO-Excel-Lines_1.2"
Comment 31 Juergen Funk (CIB) 2016-09-28 05:50:57 UTC
Created attachment 127693 [details]
New Mapping LO-Excel-Lines 1.2.ods
Comment 32 Juergen Funk (CIB) 2016-09-28 06:01:23 UTC
Hi Kevin Suo, or kompilainenn 

can you set the Status back to RESOLVED fixed. 
For me it is works
Comment 33 Kevin Suo 2016-09-28 06:21:33 UTC
(In reply to Juergen Funk (CIB) from comment #30)

As already stated in my original bug report, the bug is: 
When you apply borders using the default toolbar icon command, it applied a border which is solid, but after save as xlsx the border become dashed when open with MSO.

The default toolbar icon command apply borders with 0.05 pt, and based on your mapping the line is EXC_LINE_HAIR in MSO. This is the problem.

So, the further solution is:
1. Give the toolbar command a new value (for example, 0.75pt) which can produce a solid line when open with MSO. Or
2. Mapp 0.05 to one of the solid line type.

For me it really does not matter if I apply border using the cell formatting dialog with 0.05pt and then the border becomes dashed in MSO. But, it really matters when I apply border using the toolbar and it shows me a solid border, but becomes dashed when reopen with MSO. 99% of the time people apply borders using the toolbar, not using the formatting dialog.
Comment 34 Juergen Funk (CIB) 2016-09-28 07:00:46 UTC
(In reply to Kevin Suo from comment #33)
 
> The default toolbar icon command apply borders with 0.05 pt, and based on
> your mapping the line is EXC_LINE_HAIR in MSO. This is the problem.

BUT this mapping i have not changed

> So, the further solution is:
> 1. Give the toolbar command a new value (for example, 0.75pt) which can
     produce a solid line when open with MSO. 
> 2. Mapp 0.05 to one of the solid line type.

The best solution is 1, when change the mapping in this case the roundtrip is break, that was my main point, not change always the borders between LO and MS.

I think, this bug is solved, but yours is a new bug/feature, and i can not decide what for solution is the right, but i think the best and easiers is 1
Comment 35 Kevin Suo 2016-09-28 08:29:16 UTC
(In reply to Juergen Funk (CIB) from comment #34)

Yes, your patch has brought a lot of improvements to the border behaviour between ods and xlsx/xls, and thanks a lot for your hard work.

But do you think the issue I mentioned in the original bug report is resolved? I don't think so. 

Quote from original bug report:
> Problem description:
> When apply borders to cells by clicking the toolbar icon, then save as xlsx or xls format, when repened with MSO the borders are showing dashed/dotted rather than normal line borders.

My concern above is not a new issue.
Comment 36 Roman Kuznetsov 2016-09-28 10:15:01 UTC
(In reply to Kevin Suo from comment #35)
> (In reply to Juergen Funk (CIB) from comment #34)
> 
> Yes, your patch has brought a lot of improvements to the border behaviour
> between ods and xlsx/xls, and thanks a lot for your hard work.
> 
> But do you think the issue I mentioned in the original bug report is
> resolved? I don't think so. 
> 
> Quote from original bug report:
> > Problem description:
> > When apply borders to cells by clicking the toolbar icon, then save as xlsx or xls format, when repened with MSO the borders are showing dashed/dotted rather than normal line borders.
> 
> My concern above is not a new issue.

I support this view
Comment 37 Juergen Funk (CIB) 2016-09-29 07:07:31 UTC
(In reply to kompilainenn from comment #36)
> (In reply to Kevin Suo from comment #35)
> I support this view

The problem is when we change the mapping from LO-Line-1 0,05 to Excel-Line-6 we lost this witdh information (0,05-0,50), and this mapping was before my change.

Then i prefer we change the default to 0,75.

I give that to the design-team for decision, and then i hope i get a time slice for change.
Comment 38 Kevin Suo 2016-09-29 07:22:37 UTC
(In reply to Juergen Funk (CIB) from comment #37)

> I give that to the design-team for decision, and then i hope i get a time slice for change.

How can we ping the design-team?
Comment 39 Roman Kuznetsov 2016-09-29 07:39:43 UTC
(In reply to Kevin Suo from comment #38)
> (In reply to Juergen Funk (CIB) from comment #37)
> 
> > I give that to the design-team for decision, and then i hope i get a time slice for change.
> 
> How can we ping the design-team?

IRC-channel #libreoffice-design on Freenode.net
Comment 40 Heiko Tietze 2016-09-29 09:15:18 UTC
(In reply to Kevin Suo from comment #38)
> How can we ping the design-team?

Keyword needsUXEval is set and ux-advice is CC'ed so the design team is aware of this ticket.

To summarize: aiming for compatibility with MSO the options are
1) change the predefined border size from 0.05 to 0.75
2) map hairlines to something else, I guess that means to save/export 0.05 in a different style

Solution 1) sounds very simple, and users can change the line width back to 0.05 - ending up in the same trouble but intentionally in this case. Probably I do not understand 2 but isn't this the best solution?
Comment 41 Samuel Mehrbrodt (CIB) 2016-09-29 09:27:35 UTC
> 1) change the predefined border size from 0.05 to 0.75

Please, before we discuss changing the default line thickness to something else, try *printing* a table with some text in it with the two different line thicknesses (Please do print, as you won't see much difference on the screen).

* 0.05
* 0.75

I did that and I would very much prefer sticking with the 0.05. The 0.75 line is much thicker and makes reading the text in the table harder.

I know that you can always change it back, but most users will stick with the default line thickness.

Also note that the roundtrip LO -> xlsx -> LO works fine. The problem is only when opening it in Excel.
We definitely need to live with some differences here, since the line styles in Excel differ quite a lot from the ones in Calc. Attachment 127693 [details] has all the details.
Comment 42 Heiko Tietze 2016-09-29 10:07:02 UTC
(In reply to Samuel Mehrbrodt (CIB) from comment #41)
> We definitely need to live with some differences here...

Solution 3): WONTFIX (sounds good to me too).
Comment 43 Roman Kuznetsov 2016-09-29 10:44:22 UTC
(In reply to Heiko Tietze from comment #42)
> (In reply to Samuel Mehrbrodt (CIB) from comment #41)
> > We definitely need to live with some differences here...
> 
> Solution 3): WONTFIX (sounds good to me too).

This is not good idea. That can say about all bugs in bugzilla. 
I think, need resolve with different style of border line.
Comment 44 Yousuf Philips (jay) (retired) 2016-10-04 20:48:14 UTC
We had similar discussions of what should be the default border line width for tables in bug 99027.

So if we check out the competition, all of them (Excel, WPS/Kingsoft, Google Sheets, Gnumeric, Calligra Sheets, Quattro Pro) provide style presets that combine both the border line style and width. Calligra Sheets is the only one that allows a user to create a custom style and width, but limits the width to being only whole numbers.

(In reply to Juergen Funk (CIB) from comment #37)
> The problem is when we change the mapping from LO-Line-1 0,05 to
> Excel-Line-6 we lost this witdh information (0,05-0,50), and this mapping
> was before my change.

That is only natural because of format differences. When excel exports its default border to ods, Calc opens it up at 0.05 and Calligra opens it at 1.

> Then i prefer we change the default to 0,75.

I would agree with this to provide maximum compatibility.

(In reply to Samuel Mehrbrodt (CIB) from comment #41)
> Please, before we discuss changing the default line thickness to something
> else, try *printing* a table with some text in it with the two different
> line thicknesses (Please do print, as you won't see much difference on the
> screen).

Yes someone with a printer should print out a sheet with various border widths and give a recommendation of what they think should be default. I'd recommend testing in 0.25 increments.
Comment 45 Bartosz 2016-12-10 23:38:48 UTC
*** Bug 79788 has been marked as a duplicate of this bug. ***
Comment 46 Bartosz 2016-12-10 23:43:39 UTC
Added to XLSX format limitation in bug 88175
Comment 47 Yousuf Philips (jay) (retired) 2017-11-27 12:08:21 UTC
So in bug 99027, we've decided to go with 0.50.
Comment 48 QA Administrators 2018-11-28 03:48:10 UTC Comment hidden (obsolete)
Comment 49 Roman Kuznetsov 2018-11-28 06:02:01 UTC
Created attachment 147080 [details]
Comparison LO 6.2 vs MS Excel 2010
Comment 50 Roman Kuznetsov 2018-11-28 06:07:25 UTC
bug still repro in

Version: 6.2.0.0.beta1
Build ID: d1b41307be3f8c19fe6f1938cf056e7ff1eb1d18
CPU threads: 4; OS: Windows 6.1; UI render: default; VCL: win; 
Locale: ru-RU (ru_RU); UI-Language: en-US
Calc: threaded