tdf#91475 Fix column width during .xlsx export

With commit 16726a1b37df8bdcae02b3c7699df814977222bd the new import algorithm
was introduced. Unfortunately it looks like that some specific column width
are improved, but for other widths it is worse.
More information is available at:
https://bugs.documentfoundation.org/show_bug.cgi?id=91475#c29

In this commit I added unit tests which prooves proper working of old
algorithm and I have switched to old algorithm.
Unit test which was done:
 1. After resave .xlsx file, the column width values did not change
 2. After export .ods to .xlsx displayed column width did not change
 3. After save .xlsx to .ods displayed column width did not change

This commit resolve column width after exporting to .xlsx, and
correctly preserve column width for LibreOffice.

Change-Id: I26d074b203d08a4bf4e9e31e52dbfd7ca7d3beb1
Reviewed-on: https://gerrit.libreoffice.org/27943
Tested-by: Jenkins <ci@libreoffice.org>
Reviewed-by: Eike Rathke <erack@redhat.com>
Tested-by: Eike Rathke <erack@redhat.com>
(cherry picked from commit f2a75fecdfb5d92d27074312f29dc7dc4afb16ec)
Reviewed-on: https://gerrit.libreoffice.org/31625

3 files changed, 109 insertions, 3 deletions

diff --git a/sc/qa/unit/data/ods/different-column-width.ods b/sc/qa/unit/data/ods/different-column-width.ods
new file mode 100644
index 000000000000..3f2e34ce88dc
--- /dev/null
+++ b/sc/qa/unit/data/ods/different-column-width.ods
diff --git a/sc/qa/unit/data/xlsx/different-column-width-excel2010.xlsx b/sc/qa/unit/data/xlsx/different-column-width-excel2010.xlsx
new file mode 100644
index 000000000000..93f133f8db49
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/different-column-width-excel2010.xlsx
diff --git a/sc/qa/unit/subsequent_export-test.cxx b/sc/qa/unit/subsequent_export-test.cxx
index ab80b095db55..50bb2a62ea13 100644
--- a/sc/qa/unit/subsequent_export-test.cxx
+++ b/sc/qa/unit/subsequent_export-test.cxx
@@ -110,6 +110,8 @@ public:
 
     void testCustomColumnWidthExportXLSX();
     void testXfDefaultValuesXLSX();
+    void testColumnWidthResaveXLSX();
+    void testColumnWidthExportFromODStoXLSX();
     void testOutlineExportXLSX();
     void testHiddenEmptyRowsXLSX();
     void testEmptyRowsWithBackgroundColorXLSX();
@@ -209,6 +211,8 @@ public:
 
     CPPUNIT_TEST(testCustomColumnWidthExportXLSX);
     CPPUNIT_TEST(testXfDefaultValuesXLSX);
+    CPPUNIT_TEST(testColumnWidthResaveXLSX);
+    CPPUNIT_TEST(testColumnWidthExportFromODStoXLSX);
     CPPUNIT_TEST(testOutlineExportXLSX);
     CPPUNIT_TEST(testHiddenEmptyRowsXLSX);
     CPPUNIT_TEST(testEmptyRowsWithBackgroundColorXLSX);
@@ -505,7 +509,7 @@ void ScExportTest::testCustomColumnWidthExportXLSX()
     xmlDocPtr pSheet = XPathHelper::parseExport(pXPathFile, m_xSFactory, "xl/worksheets/sheet1.xml");
     CPPUNIT_ASSERT(pSheet);
 
-    // First column, has everything default
+    // First column, has everything default (width in Calc: 1280)
     assertXPath(pSheet, "/x:worksheet/x:cols/x:col[1]", "hidden", "false");
     assertXPath(pSheet, "/x:worksheet/x:cols/x:col[1]", "outlineLevel", "0");
     assertXPath(pSheet, "/x:worksheet/x:cols/x:col[1]", "customWidth", "false");
@@ -513,7 +517,7 @@ void ScExportTest::testCustomColumnWidthExportXLSX()
     assertXPath(pSheet, "/x:worksheet/x:cols/x:col[1]", "min", "1");
     assertXPath(pSheet, "/x:worksheet/x:cols/x:col[1]", "max", "1");
 
-    // Second column, has custom width
+    // Second column, has custom width (width in Calc: 1225)
     assertXPath(pSheet, "/x:worksheet/x:cols/x:col[2]", "hidden", "false");
     assertXPath(pSheet, "/x:worksheet/x:cols/x:col[2]", "outlineLevel", "0");
     assertXPath(pSheet, "/x:worksheet/x:cols/x:col[2]", "customWidth", "true");
@@ -521,7 +525,7 @@ void ScExportTest::testCustomColumnWidthExportXLSX()
     assertXPath(pSheet, "/x:worksheet/x:cols/x:col[2]", "min", "2");
     assertXPath(pSheet, "/x:worksheet/x:cols/x:col[2]", "max", "2");
 
-    // Third column, has everything default
+    // Third column, has everything default (width in Calc: 1280)
     assertXPath(pSheet, "/x:worksheet/x:cols/x:col[3]", "hidden", "false");
     assertXPath(pSheet, "/x:worksheet/x:cols/x:col[3]", "outlineLevel", "0");
     assertXPath(pSheet, "/x:worksheet/x:cols/x:col[3]", "customWidth", "false");
@@ -611,6 +615,108 @@ void ScExportTest::testXfDefaultValuesXLSX()
     assertXPath(pSheet, "/x:styleSheet/x:cellXfs/x:xf", 14);
 }
 
+void ScExportTest::testColumnWidthResaveXLSX()
+{
+    // tdf#91475 FILESAVE: Column width is not preserved in XLSX / after round trip.
+    // Test if after resave .xlsx file, columns width is identical with with previous one
+    ScDocShellRef xShell = loadDoc("different-column-width-excel2010.", FORMAT_XLSX);
+    CPPUNIT_ASSERT(xShell.Is());
+
+    std::shared_ptr<utl::TempFile> pXPathFile = ScBootstrapFixture::exportTo(&(*xShell), FORMAT_XLSX);
+    xmlDocPtr pSheet = XPathHelper::parseExport(pXPathFile, m_xSFactory, "xl/worksheets/sheet1.xml");
+    CPPUNIT_ASSERT(pSheet);
+
+    // In original Excel document the width is "24"
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[1]", "width", "24");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[1]", "customWidth", "true");
+
+    // In original Excel document the width is "12"
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[2]", "width", "12");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[2]", "customWidth", "true");
+
+    // In original Excel document the width is "6"
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[3]", "width", "6");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[3]", "customWidth", "true");
+
+    // In original Excel document the width is "1"
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[4]", "width", "1");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[4]", "customWidth", "true");
+
+    // In original Excel document the width is "250"
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[5]", "width", "250");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[5]", "customWidth", "true");
+
+    // The last column [6] is not existing in Excel sheet, and it is added only by LibreOffice.
+    // This column width is default and it is depended on operating system.
+
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col", 6);
+}
+
+
+void ScExportTest::testColumnWidthExportFromODStoXLSX()
+{
+    // tdf#91475 FILESAVE: Column width is not preserved in XLSX / after round trip.
+    // Test if after export .ods to .xlsx format, displayed columns width
+    // is identical with with previous (.ods) one
+
+    ScDocShellRef xShell = loadDoc("different-column-width.", FORMAT_ODS);
+
+    CPPUNIT_ASSERT( xShell.Is() );
+
+    ScDocument& rOdsDoc = xShell->GetDocument();
+
+    // Col 1, Tab 0 (Column width 2.00 in)
+    sal_uInt16 nExpectedColumn0Width = rOdsDoc.GetColWidth(static_cast<SCCOL>(0), static_cast<SCTAB>(0), false);
+    CPPUNIT_ASSERT_EQUAL( static_cast< sal_uInt16 >( 2880 ), nExpectedColumn0Width );
+
+    // Col 2, Tab 0 (Column width 1.00 in)
+    sal_uInt16 nExpectedColumn1Width = rOdsDoc.GetColWidth(static_cast<SCCOL>(1), static_cast<SCTAB>(0), false);
+    CPPUNIT_ASSERT_EQUAL( static_cast< sal_uInt16 >( 1440 ), nExpectedColumn1Width );
+
+    // Col 3, Tab 0 (Column width 0.50 in)
+    sal_uInt16 nExpectedColumn2Width = rOdsDoc.GetColWidth(static_cast<SCCOL>(2), static_cast<SCTAB>(0), false);
+    CPPUNIT_ASSERT_EQUAL( static_cast< sal_uInt16 >( 720 ), nExpectedColumn2Width );
+
+    // Col 4, Tab 0 (Column width 0.25 in)
+    sal_uInt16 nExpectedColumn3Width = rOdsDoc.GetColWidth(static_cast<SCCOL>(3), static_cast<SCTAB>(0), false);
+    CPPUNIT_ASSERT_EQUAL( static_cast< sal_uInt16 >( 360 ), nExpectedColumn3Width  );
+
+    // Col 5, Tab 0 (Column width 13.57 in)
+    sal_uInt16 nExpectedColumn4Width = rOdsDoc.GetColWidth(static_cast<SCCOL>(4), static_cast<SCTAB>(0), false);
+    CPPUNIT_ASSERT_EQUAL( static_cast< sal_uInt16 >( 19539 ), nExpectedColumn4Width );
+
+    // Export to .xlsx and compare column width with the .ods
+    // We expect that column width from .ods will be exactly the same as imported from .xlsx
+
+    ScDocShellRef xXlsxDocSh = saveAndReload( xShell.get(), FORMAT_XLSX );
+    CPPUNIT_ASSERT( xXlsxDocSh.Is() );
+
+    ScDocument& rDoc = xXlsxDocSh->GetDocument();
+
+    // Col 1, Tab 0
+    sal_uInt16 nCalcWidth;
+    nCalcWidth = rDoc.GetColWidth(static_cast<SCCOL>(0), static_cast<SCTAB>(0), false);
+    CPPUNIT_ASSERT_EQUAL( nExpectedColumn0Width, nCalcWidth );
+
+    // Col 2, Tab 0
+    nCalcWidth = rDoc.GetColWidth(static_cast<SCCOL>(1), static_cast<SCTAB>(0), false);
+    CPPUNIT_ASSERT_EQUAL( nExpectedColumn1Width, nCalcWidth );
+
+    // Col 3, Tab 0
+    nCalcWidth = rDoc.GetColWidth(static_cast<SCCOL>(2), static_cast<SCTAB>(0), false);
+    CPPUNIT_ASSERT_EQUAL( nExpectedColumn2Width, nCalcWidth );
+
+    // Col 4, Tab 0
+    nCalcWidth = rDoc.GetColWidth(static_cast<SCCOL>(3), static_cast<SCTAB>(0), false);
+    CPPUNIT_ASSERT_EQUAL( nExpectedColumn3Width, nCalcWidth );
+
+    // Col 5, Tab 0
+    nCalcWidth = rDoc.GetColWidth(static_cast<SCCOL>(4), static_cast<SCTAB>(0), false);
+    CPPUNIT_ASSERT_EQUAL( nExpectedColumn4Width, nCalcWidth );
+
+    xXlsxDocSh->DoClose();
+}
+
 void ScExportTest::testOutlineExportXLSX()
 {
     //tdf#100347 FILESAVE FILEOPEN after exporting to .xlsx format grouping are lost