Created attachment 186611 [details] How it looks in Calc In Calc, if you apply solid borders using Hairline or Very Thin line widths and export the file to XLSX, you'll get dashed lines in Excel. I've attached a screenshot of how it looks in Calc and how it gets exported to Excel. Solid lines should always be exported as solid lines, despite their widths. I believe this problem has existed for a very long time in Calc, but it has somewhat been "concealed" when the default line width in Calc became 0.75pt (Thin), which is the first width option that gets exported correctly to Excel. But Hairline and Very Thin still do not work. System info Version: 7.5.2.2 (X86_64) / LibreOffice Community Build ID: 50(Build:2) CPU threads: 16; OS: Linux 5.19; UI render: default; VCL: kf5 (cairo+xcb) Locale: pt-BR (pt_BR.UTF-8); UI: en-US Ubuntu package version: 4:7.5.2~rc2-0ubuntu0.22.10.1~lo1 Calc: threaded
Created attachment 186612 [details] How it looks in Excel
Created attachment 186613 [details] ODS file used in the example
Created attachment 186614 [details] XLSX file exported from the previous ODS file
I think, that this is not our bug. Excel has only a restricted set of border styles (18.18.3 ST_BorderStyle in ISO standard). So LibreOffice needs to map a range of thickness values to the same border style. In this case 0.75pt (LO Thin) is mapped to Excel "thin" and 0.05pt (LO Hairline) is mapped to Excel "hair". There is no value between "thin" and "hair" in Excel. So LibreOffice needs to map 0.5pt to either "hair" or "thin" and "hair" has been chosen. The dots are the way Excel renders "hair".
Hi Regina, thanks for your input. I'm not sure if this is a LO or MS bug, but I would argue that this is a LO bug because we know that "hair" is interpreted as a dashed line in Excel and even so we keep it this way. Moreover, I think MS is not worried about this at all, so the burden of compatibility always falls on our side. This border issue is a real problem... for example, at work I often create spreadsheets using Calc and send them to my students/colleagues/coworkers as XLSX files, and when they open the file the borders are all dashed, which gives the impression that LO is to blame. Is there a problem if we map all solid borders using "thin" for cases where the borders are thinner than 0.75pt ? It would be a simple change in lclGetBorderLine in file: /core/sc/source/filter/excel/xestyle.cxx
Here's the proposed fix: https://gerrit.libreoffice.org/c/core/+/150211 After applying the patch, open the ODS file and export it as XLSX. Notice that now the XLSX file opens with solid borders in Excel for all cases.
(In reply to Rafael Lima from comment #5) > Hi Regina, thanks for your input. > > Is there a problem if we map all solid borders using "thin" for cases where > the borders are thinner than 0.75pt ? We need to be able to recreate "Hairline" since "Hairline" has the special meaning of "1 device dot". Thus our "Hairline" must be mapped to Excel "hair". For the other solid styles there might be better mapping. Excel has "thin", "medium" and "thick", see attachment 186625 [details] in bug 149208. You should not use a new bug. The discussion of best mapping is already in bug 149208.
The mapping steps are in https://opengrok.libreoffice.org/xref/core/sc/source/filter/inc/xlconst.hxx?r=2219d378&fi=EXC_BORDER_HAIR#EXC_BORDER_HAIR. The unit is 0.5pt.
Sorry, unit is 0.05pt
(In reply to Regina Henschel from comment #7) > We need to be able to recreate "Hairline" since "Hairline" has the special > meaning of "1 device dot". Thus our "Hairline" must be mapped to Excel > "hair". What are the consequences of mapping it to "thin" instead? IMO we cannot keep converting solid hairline borders to something that we know that will be rendered incorrectly in Excel (as a dashed line in this case). If we cannot map it to "thin" and neither to "hair" (which causes this bug), then what's the option we have? I don't mean to sound rude, but this bug has annoyed me since always. It makes me really sad when I create a nice table in Calc, save it as XLSX and when people open it in Excel, dashed lines are shown instead of solid lines. And then they blame me for using LibreOffice and corrupting files. > For the other solid styles there might be better mapping. Excel has "thin", > "medium" and "thick", see attachment 186625 [details] in bug 149208. Indeed, we need to improve the mapping. > You should not use a new bug. The discussion of best mapping is already in > bug 149208. Sorry about that... I created a new ticket because I wanted to focus only on the Solid Hairline - Dashed line problem (instead of fixing the whole line mapping issue, which is far bigger).
Fun fact... if you create a file in Excel and apply their "thin" border, and then export it to ODS using Excel, the border will open as a solid Hairline in Calc.