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

New Mapping from LO to MS, decription in
sc/qa/unit/data/README.cellborders

Add a Unit-Test for testing the mapping

Change-Id: I5324bf85b6299dbc63ce3c8ef479f19b0b1b3f1f
Reviewed-on: https://gerrit.libreoffice.org/22665
Reviewed-by: Katarina Behrens <Katarina.Behrens@cib.de>
Tested-by: Katarina Behrens <Katarina.Behrens@cib.de>

3 files changed, 183 insertions, 0 deletions

diff --git a/sc/qa/unit/data/README.cellborders b/sc/qa/unit/data/README.cellborders
new file mode 100644
index 000000000000..00db85a5db40
--- /dev/null
+++ b/sc/qa/unit/data/README.cellborders
@@ -0,0 +1,93 @@
+
+Differences in borders between Excel and LibreOffice Calc.
+
+The sources for this Document is:
+ - sc/qa/unit/data/ods/test_borders_export.ods  (new Test-Document)
+ - sc/qa/unit/subsequent_export-test.cxx        (ScExportTest::testBordersExchangeXLSX)
+
+ - sc/source/filter/excel/xestyle.cxx           (void lclGetBorderLine)
+ - sc/source/filter/oox/stylesbuffer.cxx        (Border::convertBorderLine)
+
+ Bug-Ticket: tdf#79787
+
+
+In LibreOffice the lines are defined by two information: the style and the width of the line. There are seven different line styles available. And the following line width can be selected: 0.05 pt, 0.25 pt, 0.50 pt, 0.75 pt, 1.0 pt, 1.25 pt, 1.50 pt, … until 9pt.
+In Excel you can only define the style of the line. There are 13 different styles available. The width cannot be entered explicitly, instead you have different styles for a solid line with different width (s. Screenshots below).
+The question is now: how to map the borderlines of LibreOffice to Excel and vice versa.
+In the overview below you can see the mapping as it is and some suggestions how to improve it. As the borders are defined different, it will not be possible to define a 1:1 mapping."
+
+The current mapping is mainly defined of the width, but i think better is the line-style, i think user see that first
+
+Base for the evaluation is LibreOffice master (commit  [bbfeab3b13b48c99cfa2f94c8c34bc3efef7faa9] ) and Excel 2013
+
+                           EXCEL                                      ||                         LIBREOFFICE
+Excel-Line Name                                      Value            ||        LO-Line  Name                                      Value
+  Keine    EXC_LINE_NONE                               0              ||          none   table::BorderLineStyle::NONE              32767 (-1)
+    1      EXC_LINE_HAIR                               7              ||           1     table::BorderLineStyle::SOLID                 0
+    2      EXC_LINE_DOTTED                             4              ||           2     table::BorderLineStyle::DOTTED                1
+    3      EXC_LINE_THIN_DASHDOTDOT                    11             ||           3     table::BorderLineStyle::DASHED                2
+    4      EXC_LINE_THIN_DASHDOT                       9              ||           4     table::BorderLineStyle::FINE_DASHED           14
+    5      EXC_LINE_DASHED                             3              ||           5     table::BorderLineStyle::DASH_DOT              16
+    6      EXC_LINE_THIN                               1              ||           6     table::BorderLineStyle::DASH_DOT_DOT          17
+    7      EXC_LINE_MEDIUM_DASHDOTDOT                  12             ||           7     table::BorderLineStyle::DOUBLE_THIN           15
+    8      EXC_LINE_MEDIUM_SLANT_DASHDOT               13             ||
+    9      EXC_LINE_MEDIUM_DASHDOT                     10             ||
+    10     EXC_LINE_MEDIUM_DASHED                      8              ||
+    11     EXC_LINE_MEDIUM                             2              ||
+    12     EXC_LINE_THICK                              5              ||
+    13     EXC_LINE_DOUBLE                             6              ||
+
+In the following cases a 1:1 mapping is not possible.
+
+    Excel -> LibreOffice: Create in Excel and open it in LibreOffice. ||    LibreOffice -> Excel: Create Sheet in LibreOffice and save as XLSX.
+Create in|      In Excel save as XLSX,         | In Excel save as ODS,||  Created in     |        Load saved xlsx-file         | Load saved xlsx-file
+Excel2013|       Load in LibreOffice           |  Load in LibreOffice ||LibreOffice with |          in LibreOffice             |      in Excel
+_______________________________________________________________________________________________________________________________________________________
+   Line  |current transformation|new suggestion|current transformation||Line      Width  |current transformation|new suggestion|current|new suggestion
+         |   Line      Width    | Line   Width |   Line      Width    ||                 |   Line      Width    | Line   Width |  Line |    Line
+    13   |    7         2,50    |  7      1,75 |    1         0,05    || 2     0,05 - 0,5|    1         0,05    |  2      0,75 |   6   |     2
+                                                                      || 2     1,75 - 2,2|    1         1,75    |  4      1,75 |   6   |     8
+                                                                      || 2     2,50 - 9,0|    1         2,50    |  4      1,75 |   6   |     8
+                                                                      || 3     0,05 - 0,5|    1         0,05    |  2      0,75 |   6   |     2
+                                                                      || 3     0,75 - 1,5|    4         0,75    |  4      0,75 |   5   |     5
+                                                                      || 3     2,50 - 9,0|    1         2,50    |  3      1,75 |   6   |     10
+                                                                      || 4     0,05 - 0,5|    1         0,05    |  4      0,75 |   6   |     5
+                                                                      || 4     2,50 - 9,0|    1         2,50    |  4      1,75 |   6   |     8
+                                                                      || 5     0,05 - 0,5|    1         0,05    |  4      0,75 |   6   |     5
+                                                                      || 5     2,50 - 9,0|    1         2,50    |  5      1,75 |   6   |     9
+                                                                      || 6     0,05 - 0,5|    1         0,05    |  4      0,75 |   6   |     5
+                                                                      || 6     2,50 - 9,0|    1         2,50    |  6      1,75 |   6   |     7
+                                                                      || 7     0,05 - 9,0|    7         2,50    |  7      1,75 |   13  |     13
+
+
+No similar border available => choose one that is not used similar one and make the roundtrip via xlsx-file possible (create in LO, save as xlsx, open in LO and
+border is not changed)
+
+    Excel -> LibreOffice: Create in Excel and open it in LibreOffice. ||    LibreOffice -> Excel: Create Sheet in LibreOffice and save as XLSX.
+Create in|      In Excel save as XLSX,         | In Excel save as ODS,||  Created in     |        Load saved xlsx-file         | Load saved xlsx-file
+Excel2013|       Load in LibreOffice           |  Load in LibreOffice ||LibreOffice with |          in LibreOffice             |      in Excel
+_______________________________________________________________________________________________________________________________________________________
+   Line  |current transformation|new suggestion|current transformation||Line      Width  |current transformation|new suggestion|current|new suggestion
+         |   Line      Width    | Line   Width |   Line      Width    ||                 |   Line      Width    | Line   Width |  Line |    Line
+    8    |    5        1,75     |  4      1,75 |    1         0,05    || 4     1,75 - 2,2|    1         1,75    |  4      1,75 |   6   |     8
+
+
+In the following cases the mapping is o.k. and should not be changed.
+
+    Excel -> LibreOffice: Create in Excel and open it in LibreOffice. ||    LibreOffice -> Excel: Create Sheet in LibreOffice and save as XLSX.
+Create in|      In Excel save as XLSX,         | In Excel save as ODS,||  Created in     |        Load saved xlsx-file         | Load saved xlsx-file
+Excel2013|       Load in LibreOffice           |  Load in LibreOffice ||LibreOffice with |          in LibreOffice             |      in Excel
+_______________________________________________________________________________________________________________________________________________________
+   Line  |current transformation|new suggestion|current transformation||Line      Width  |current transformation               |current
+         |   Line      Width    | Line   Width |   Line      Width    ||                 |   Line      Width                   |  Line
+    1    |    1        0,05     |  1      0,05 |    1         0,05    || 1     0,05 - 0,5|    1        0,05                    |   1
+    2    |    2        0,75     |  2      0,75 |    1         0,05    || 1     0,75 - 1,5|    1        0,75                    |   6
+    3    |    6        0,75     |  6      0,75 |    1         0,05    || 1     1,75 - 2,2|    1        1,75                    |   11
+    4    |    5        0,75     |  5      0,75 |    1         0,05    || 1     2,50 - 9,0|    1        2,50                    |   12
+    5    |    4        0,75     |  4      0,75 |    1         0,05    || 2     0,75 - 1,5|    2        0,75                    |   2
+    6    |    1        0,75     |  1      0,75 |    1         0,05    || 3     1,75 - 2,2|    3        1,75                    |   10
+    7    |    6        1,75     |  6      1,75 |    1         0,05    || 4     0,75 - 1,5|    4        0,75                    |   5
+    9    |    5        1,75     |  5      1,75 |    1         0,05    || 5     0,75 - 1,5|    5        0,75                    |   4
+    10   |    3        1,75     |  3      1,75 |    3         2,00    || 5     1,75 - 2,2|    5        1,75                    |   9
+    11   |    1        1,75     |  1      1,75 |    1         2,00    || 6     0,75 - 1,5|    6        0,75                    |   3
+    12   |    1        2,50     |  1      2,50 |    1         2,50    || 6     1,75 - 2,2|    6        1,75                    |   7
diff --git a/sc/qa/unit/data/ods/test_borders_export.ods b/sc/qa/unit/data/ods/test_borders_export.ods
new file mode 100644
index 000000000000..394ec4e53140
--- /dev/null
+++ b/sc/qa/unit/data/ods/test_borders_export.ods
diff --git a/sc/qa/unit/subsequent_export-test.cxx b/sc/qa/unit/subsequent_export-test.cxx
index caeb15d732de..cf75d8766fc7 100644
--- a/sc/qa/unit/subsequent_export-test.cxx
+++ b/sc/qa/unit/subsequent_export-test.cxx
@@ -116,6 +116,7 @@ public:
 
     void testCellBordersXLS();
     void testCellBordersXLSX();
+    void testBordersExchangeXLSX();
     void testTrackChangesSimpleXLSX();
     void testSheetTabColorsXLSX();
 
@@ -190,6 +191,7 @@ public:
     CPPUNIT_TEST(testSheetProtectionXLSX);
     CPPUNIT_TEST(testCellBordersXLS);
     CPPUNIT_TEST(testCellBordersXLSX);
+    CPPUNIT_TEST(testBordersExchangeXLSX);
     CPPUNIT_TEST(testTrackChangesSimpleXLSX);
     CPPUNIT_TEST(testSheetTabColorsXLSX);
     CPPUNIT_TEST(testSharedFormulaExportXLS);
@@ -1529,6 +1531,94 @@ void ScExportTest::testCellBordersXLSX()
     testExcelCellBorders(FORMAT_XLSX);
 }
 
+void ScExportTest::testBordersExchangeXLSX()
+{
+    // Document: sc/qa/unit/data/README.cellborders
+
+    // short name for the table
+    const ::editeng::SvxBorderStyle None     = table::BorderLineStyle::NONE;
+    const ::editeng::SvxBorderStyle Solid    = table::BorderLineStyle::SOLID;
+    const ::editeng::SvxBorderStyle Dotted   = table::BorderLineStyle::DOTTED;
+    const ::editeng::SvxBorderStyle Dashed   = table::BorderLineStyle::DASHED;
+    const ::editeng::SvxBorderStyle FineDash = table::BorderLineStyle::FINE_DASHED;
+    const ::editeng::SvxBorderStyle DashDot  = table::BorderLineStyle::DASH_DOT;
+    const ::editeng::SvxBorderStyle DashDoDo = table::BorderLineStyle::DASH_DOT_DOT;
+    const ::editeng::SvxBorderStyle DoubThin = table::BorderLineStyle::DOUBLE_THIN;
+
+    const size_t nMaxCol = 18;
+    const size_t nMaxRow = 7;
+
+    static struct
+    {
+        ::editeng::SvxBorderStyle BorderStyleTop, BorderStyleBottom;
+        long                      WidthTop, WidthBottom;
+    } aCheckBorderWidth[nMaxCol][nMaxRow] =
+    {
+/*  Line               1                                2                              3                            4                             5                                6                              7
+                     SOLID                           DOTTED                          DASHED                     FINE_DASHED                    DASH_DOT                      DASH_DOT_DOT                  DOUBLE_THIN          */
+/*Width */
+/* 0,05 */   {{Solid   , Solid   ,  1,  1}, {Dotted  , Dotted  , 15, 15}, {Dotted  , Dotted  , 15, 15}, {FineDash, FineDash, 15, 15}, {FineDash, FineDash, 15, 15}, {FineDash, FineDash, 15, 15}, {None    , None    ,  0,  0}},
+/* 0,25 */   {{Solid   , Solid   ,  1,  1}, {Dotted  , Dotted  , 15, 15}, {Dotted  , Dotted  , 15, 15}, {FineDash, FineDash, 15, 15}, {FineDash, FineDash, 15, 15}, {FineDash, FineDash, 15, 15}, {None    , None    ,  0,  0}},
+/* 0,50 */   {{Solid   , Solid   ,  1,  1}, {Dotted  , Dotted  , 15, 15}, {Dotted  , Dotted  , 15, 15}, {FineDash, FineDash, 15, 15}, {FineDash, FineDash, 15, 15}, {FineDash, FineDash, 15, 15}, {None    , None    ,  0,  0}},
+/* 0,75 */   {{Solid   , Solid   , 15, 15}, {Dotted  , Dotted  , 15, 15}, {FineDash, FineDash, 15, 15}, {FineDash, FineDash, 15, 15}, {DashDot , DashDot , 15, 15}, {DashDoDo, DashDoDo, 15, 15}, {None    , None    ,  0,  0}},
+/* 1,00 */   {{Solid   , Solid   , 15, 15}, {Dotted  , Dotted  , 15, 15}, {FineDash, FineDash, 15, 15}, {FineDash, FineDash, 15, 15}, {DashDot , DashDot , 15, 15}, {DashDoDo, DashDoDo, 15, 15}, {None    , None    ,  0,  0}},
+/* 1,25 */   {{Solid   , Solid   , 15, 15}, {Dotted  , Dotted  , 15, 15}, {FineDash, FineDash, 15, 15}, {FineDash, FineDash, 15, 15}, {DashDot , DashDot , 15, 15}, {DashDoDo, DashDoDo, 15, 15}, {DoubThin, DoubThin, 35, 35}},
+/* 1,50 */   {{Solid   , Solid   , 15, 15}, {Dotted  , Dotted  , 15, 15}, {FineDash, FineDash, 15, 15}, {FineDash, FineDash, 15, 15}, {DashDot , DashDot , 15, 15}, {DashDoDo, DashDoDo, 15, 15}, {DoubThin, DoubThin, 35, 35}},
+
+/* 1,75 */   {{Solid   , Solid   , 35, 35}, {FineDash, FineDash, 35, 35}, {Dashed  , Dashed  , 35, 35}, {FineDash, FineDash, 35, 35}, {DashDot , DashDot , 35, 35}, {DashDoDo, DashDoDo, 35, 35}, {DoubThin, DoubThin, 35, 35}},
+/* 2,00 */   {{Solid   , Solid   , 35, 35}, {FineDash, FineDash, 35, 35}, {Dashed  , Dashed  , 35, 35}, {FineDash, FineDash, 35, 35}, {DashDot , DashDot , 35, 35}, {DashDoDo, DashDoDo, 35, 35}, {DoubThin, DoubThin, 35, 35}},
+/* 2,25 */   {{Solid   , Solid   , 35, 35}, {FineDash, FineDash, 35, 35}, {Dashed  , Dashed  , 35, 35}, {FineDash, FineDash, 35, 35}, {DashDot , DashDot , 35, 35}, {DashDoDo, DashDoDo, 35, 35}, {DoubThin, DoubThin, 35, 35}},
+
+/* 2,50 */   {{Solid   , Solid   , 50, 50}, {FineDash, FineDash, 35, 35}, {Dashed  , Dashed  , 35, 35}, {FineDash, FineDash, 35, 35}, {DashDot , DashDot , 35, 35}, {DashDoDo, DashDoDo, 35, 35}, {DoubThin, DoubThin, 35, 35}},
+/* 2,75 */   {{Solid   , Solid   , 50, 50}, {FineDash, FineDash, 35, 35}, {Dashed  , Dashed  , 35, 35}, {FineDash, FineDash, 35, 35}, {DashDot , DashDot , 35, 35}, {DashDoDo, DashDoDo, 35, 35}, {DoubThin, DoubThin, 35, 35}},
+/* 3,00 */   {{Solid   , Solid   , 50, 50}, {FineDash, FineDash, 35, 35}, {Dashed  , Dashed  , 35, 35}, {FineDash, FineDash, 35, 35}, {DashDot , DashDot , 35, 35}, {DashDoDo, DashDoDo, 35, 35}, {DoubThin, DoubThin, 35, 35}},
+/* 3,50 */   {{Solid   , Solid   , 50, 50}, {FineDash, FineDash, 35, 35}, {Dashed  , Dashed  , 35, 35}, {FineDash, FineDash, 35, 35}, {DashDot , DashDot , 35, 35}, {DashDoDo, DashDoDo, 35, 35}, {DoubThin, DoubThin, 35, 35}},
+/* 4,00 */   {{Solid   , Solid   , 50, 50}, {FineDash, FineDash, 35, 35}, {Dashed  , Dashed  , 35, 35}, {FineDash, FineDash, 35, 35}, {DashDot , DashDot , 35, 35}, {DashDoDo, DashDoDo, 35, 35}, {DoubThin, DoubThin, 35, 35}},
+/* 5,00 */   {{Solid   , Solid   , 50, 50}, {FineDash, FineDash, 35, 35}, {Dashed  , Dashed  , 35, 35}, {FineDash, FineDash, 35, 35}, {DashDot , DashDot , 35, 35}, {DashDoDo, DashDoDo, 35, 35}, {DoubThin, DoubThin, 35, 35}},
+/* 7,00 */   {{Solid   , Solid   , 50, 50}, {FineDash, FineDash, 35, 35}, {Dashed  , Dashed  , 35, 35}, {FineDash, FineDash, 35, 35}, {DashDot , DashDot , 35, 35}, {DashDoDo, DashDoDo, 35, 35}, {DoubThin, DoubThin, 35, 35}},
+/* 9,00 */   {{Solid   , Solid   , 50, 50}, {FineDash, FineDash, 35, 35}, {Dashed  , Dashed  , 35, 35}, {FineDash, FineDash, 35, 35}, {DashDot , DashDot , 35, 35}, {DashDoDo, DashDoDo, 35, 35}, {DoubThin, DoubThin, 35, 35}}
+    };
+
+    ScDocShellRef xShell    = loadDoc("test_borders_export.", FORMAT_ODS);  // load the ods with our Borders
+    CPPUNIT_ASSERT(xShell.Is());
+
+    ScDocShellRef xDocSh = saveAndReload(&(*xShell), FORMAT_XLSX);          // save the ods to xlsx and load xlsx
+    CPPUNIT_ASSERT(xDocSh.Is());
+    ScDocument& rDoc    = xDocSh->GetDocument();
+
+    for (size_t mnCol = 0; mnCol < nMaxCol; ++mnCol)
+    {
+        for (size_t mnRow = 0; mnRow < nMaxRow; ++mnRow)
+        {
+            const editeng::SvxBorderLine* pLineTop    = nullptr;
+            const editeng::SvxBorderLine* pLineBottom = nullptr;
+            rDoc.GetBorderLines(mnCol + 2, (mnRow * 2) + 8, 0, nullptr, &pLineTop, nullptr, &pLineBottom);
+            if((mnCol < 5) && (mnRow == 6))
+            {   // in this range no lines
+                CPPUNIT_ASSERT(pLineTop == nullptr);
+                CPPUNIT_ASSERT(pLineBottom == nullptr);
+                continue;
+            }
+            else
+            {
+                CPPUNIT_ASSERT(pLineTop);
+                CPPUNIT_ASSERT(pLineBottom);
+            }
+
+            CPPUNIT_ASSERT_EQUAL_MESSAGE("Top Border-Line-Style wrong", aCheckBorderWidth[mnCol][mnRow].BorderStyleTop,
+                                          pLineTop->GetBorderLineStyle());
+            CPPUNIT_ASSERT_EQUAL_MESSAGE("Bottom Border-Line-Style wrong", aCheckBorderWidth[mnCol][mnRow].BorderStyleBottom,
+                                          pLineBottom->GetBorderLineStyle());
+            CPPUNIT_ASSERT_EQUAL_MESSAGE("Top Width-Line wrong", aCheckBorderWidth[mnCol][mnRow].WidthTop,
+                                          pLineTop->GetWidth());
+            CPPUNIT_ASSERT_EQUAL_MESSAGE("Bottom Width-Line wrong", aCheckBorderWidth[mnCol][mnRow].WidthBottom,
+                                          pLineBottom->GetWidth());
+        }
+    }
+
+    xDocSh->DoClose();
+}
+
 OUString toString( const ScBigRange& rRange )
 {
     OUStringBuffer aBuf;