tdf#100946 Fix width calculation and add customWidth support (.xlsx)

On some MS Excel version (OS X), the column "width" is
not applied, if "customWidth" key (in "col") is not set to "true".
It means that in case of .xlsx files, exported by LibreOffice,
all columns have default width. To resolve that "customWidth"
key was added during export into .xlsx file format.
During development it appears that Default Column Width is wrongly
calculated, and it was done not according to MS documentation.
This issue was also fixed. After fix default column width is
properly set.
MS documentation:
https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.column.aspx

Change-Id: I0d1944081a5ea445d1e4284db62e9b4d504bf1c0
(cherry picked from commit 40d892a2db4d750aaf0562c63004e693c028273c)
Reviewed-on: https://gerrit.libreoffice.org/31645
Reviewed-by: Eike Rathke <erack@redhat.com>
Tested-by: Eike Rathke <erack@redhat.com>
Tested-by: Jenkins <ci@libreoffice.org>

8 files changed, 122 insertions, 8 deletions

diff --git a/sc/qa/unit/data/ods/custom_column_width.ods b/sc/qa/unit/data/ods/custom_column_width.ods
new file mode 100644
index 000000000000..cab589b1ace6
--- /dev/null
+++ b/sc/qa/unit/data/ods/custom_column_width.ods
diff --git a/sc/qa/unit/subsequent_export-test.cxx b/sc/qa/unit/subsequent_export-test.cxx
index ddd2b994c31b..eb4f61d4c461 100644
--- a/sc/qa/unit/subsequent_export-test.cxx
+++ b/sc/qa/unit/subsequent_export-test.cxx
@@ -110,6 +110,7 @@ public:
     void testCellNoteExportXLS();
     void testFormatExportODS();
 
+    void testCustomColumnWidthExportXLSX();
     void testOutlineExportXLSX();
     void testHiddenEmptyRowsXLSX();
     void testLandscapeOrientationXLSX();
@@ -196,6 +197,7 @@ public:
     CPPUNIT_TEST(testCellNoteExportXLS);
     CPPUNIT_TEST(testFormatExportODS);
 
+    CPPUNIT_TEST(testCustomColumnWidthExportXLSX);
     CPPUNIT_TEST(testOutlineExportXLSX);
     CPPUNIT_TEST(testHiddenEmptyRowsXLSX);
     CPPUNIT_TEST(testLandscapeOrientationXLSX);
@@ -477,6 +479,100 @@ void ScExportTest::testFormatExportODS()
     xDocSh->DoClose();
 }
 
+
+void ScExportTest::testCustomColumnWidthExportXLSX()
+{
+    //tdf#100946 FILESAVE Excel on OS X ignored column widths in XLSX last saved by LO
+    ScDocShellRef xShell = loadDoc("custom_column_width.", FORMAT_ODS);
+    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);
+
+    // First column, has everything default
+    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");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[1]", "collapsed", "false");
+    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
+    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");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[2]", "collapsed", "false");
+    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
+    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");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[3]", "collapsed", "false");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[3]", "min", "3");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[3]", "max", "3");
+
+    // Fourth column has custom width. Columns from 4 to 7 are hidden
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[4]", "hidden", "true");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[4]", "outlineLevel", "0");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[4]", "customWidth", "true");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[4]", "collapsed", "false");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[4]", "min", "4");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[4]", "max", "4");
+
+    // 5th column has custom width. Columns from 4 to 7 are hidden
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[5]", "hidden", "true");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[5]", "outlineLevel", "0");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[5]", "customWidth", "true");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[5]", "collapsed", "false");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[5]", "min", "5");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[5]", "max", "5");
+
+    // 6th and 7th columns has default width and it are hidden
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[6]", "hidden", "true");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[6]", "outlineLevel", "0");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[6]", "customWidth", "false");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[6]", "collapsed", "false");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[6]", "min", "6");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[6]", "max", "7");
+
+    // 8th column has everything default
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[7]", "hidden", "false");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[7]", "outlineLevel", "0");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[7]", "customWidth", "false");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[7]", "collapsed", "false");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[7]", "min", "8");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[7]", "max", "8");
+
+    // 9th column has custom width
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[8]", "hidden", "false");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[8]", "outlineLevel", "0");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[8]", "customWidth", "true");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[8]", "collapsed", "false");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[8]", "min", "9");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[8]", "max", "9");
+
+    // Rest of columns are default
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[9]", "hidden", "false");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[9]", "outlineLevel", "0");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[9]", "customWidth", "false");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[9]", "collapsed", "false");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[9]", "min", "10");
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col[9]", "max", "1025");
+
+    // We expected that exactly 9 unique Nodes will be produced
+    assertXPath(pSheet, "/x:worksheet/x:cols/x:col", 9);
+
+    assertXPath(pSheet, "/x:worksheet/x:sheetData/x:row[1]", "hidden", "false");
+    assertXPath(pSheet, "/x:worksheet/x:sheetData/x:row[1]", "outlineLevel", "0");
+    assertXPath(pSheet, "/x:worksheet/x:sheetData/x:row[1]", "collapsed", "false");
+    assertXPath(pSheet, "/x:worksheet/x:sheetData/x:row[1]", "customFormat", "false");
+    assertXPath(pSheet, "/x:worksheet/x:sheetData/x:row[1]", "customHeight", "false");
+}
+
+
 void ScExportTest::testOutlineExportXLSX()
 {
     //tdf#100347 FILESAVE FILEOPEN after exporting to .xlsx format grouping are lost
diff --git a/sc/qa/unit/subsequent_filters-test.cxx b/sc/qa/unit/subsequent_filters-test.cxx
index 3ff7c44f88a3..15cf041123d7 100644
--- a/sc/qa/unit/subsequent_filters-test.cxx
+++ b/sc/qa/unit/subsequent_filters-test.cxx
@@ -1662,7 +1662,7 @@ void ScFiltersTest::testChartImportXLS()
     const SdrOle2Obj* pOleObj = getSingleChartObject(rDoc, 0);
     CPPUNIT_ASSERT_MESSAGE("Failed to retrieve a chart object from the 2nd sheet.", pOleObj);
 
-    CPPUNIT_ASSERT_EQUAL(11148L, pOleObj->GetLogicRect().getWidth());
+    CPPUNIT_ASSERT_EQUAL(11137L, pOleObj->GetLogicRect().getWidth());
     CPPUNIT_ASSERT(8640L > pOleObj->GetLogicRect().getHeight());
 
     xDocSh->DoClose();
diff --git a/sc/source/filter/excel/xetable.cxx b/sc/source/filter/excel/xetable.cxx
index beae3c002a4a..fb3f7f73c203 100644
--- a/sc/source/filter/excel/xetable.cxx
+++ b/sc/source/filter/excel/xetable.cxx
@@ -1565,20 +1565,28 @@ XclExpDefcolwidth::XclExpDefcolwidth( const XclExpRoot& rRoot ) :
 bool XclExpDefcolwidth::IsDefWidth( sal_uInt16 nXclColWidth ) const
 {
     double fNewColWidth = lclGetCorrectedColWidth( GetRoot(), nXclColWidth );
+    // This formula is taking number of characters with GetValue()
+    // and it is translating it into default column width. 0.5 means half character.
+    // https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.column.aspx
+    long defaultColumnWidth = static_cast< long >( 256.0 * ( GetValue() + 0.5 ) );
+
     // exactly matched, if difference is less than 1/16 of a character to the left or to the right
-    return std::abs( static_cast< long >( GetValue() * 256.0 - fNewColWidth + 0.5 ) ) < 16;
+    return std::abs( defaultColumnWidth - fNewColWidth ) < 16;
 }
 
 void XclExpDefcolwidth::SetDefWidth( sal_uInt16 nXclColWidth )
 {
     double fNewColWidth = lclGetCorrectedColWidth( GetRoot(), nXclColWidth );
-    SetValue( limit_cast< sal_uInt16 >( fNewColWidth / 256.0 + 0.5 ) );
+    // This function is taking width and translate it into number of characters
+    // Next this number of characters are stored. 0.5 means half character.
+    SetValue( limit_cast< sal_uInt16 >( fNewColWidth / 256.0 - 0.5 ) );
 }
 
 XclExpColinfo::XclExpColinfo( const XclExpRoot& rRoot,
         SCCOL nScCol, SCROW nLastScRow, XclExpColOutlineBuffer& rOutlineBfr ) :
     XclExpRecord( EXC_ID_COLINFO, 12 ),
     XclExpRoot( rRoot ),
+    mbCustomWidth( false ),
     mnWidth( 0 ),
     mnScWidth( 0 ),
     mnFlags( 0 ),
@@ -1597,9 +1605,14 @@ XclExpColinfo::XclExpColinfo( const XclExpRoot& rRoot,
     sal_uInt16 nScWidth = rDoc.GetColWidth( nScCol, nScTab, false );
     mnWidth = XclTools::GetXclColumnWidth( nScWidth, GetCharWidth() );
     mnScWidth =  sc::TwipsToHMM( nScWidth );
+
     // column flags
     ::set_flag( mnFlags, EXC_COLINFO_HIDDEN, rDoc.ColHidden(nScCol, nScTab) );
 
+    // TODO Do we need to save customWidth information also for .xls (with mnFlags)?
+    XclExpDefcolwidth defColWidth = XclExpDefcolwidth( rRoot );
+    mbCustomWidth = !defColWidth.IsDefWidth( mnWidth );
+
     // outline data
     rOutlineBfr.Update( nScCol );
     ::set_flag( mnFlags, EXC_COLINFO_COLLAPSED, rOutlineBfr.IsCollapsed() );
@@ -1659,7 +1672,7 @@ void XclExpColinfo::SaveXml( XclExpXmlStream& rStrm )
     rStrm.GetCurrentStream()->singleElement( XML_col,
             // OOXTODO: XML_bestFit,
             XML_collapsed,      XclXmlUtils::ToPsz( ::get_flag( mnFlags, EXC_COLINFO_COLLAPSED ) ),
-            // OOXTODO: XML_customWidth,
+            XML_customWidth,    XclXmlUtils::ToPsz( mbCustomWidth ),
             XML_hidden,         XclXmlUtils::ToPsz( ::get_flag( mnFlags, EXC_COLINFO_HIDDEN ) ),
             XML_outlineLevel,   OString::number( mnOutlineLevel ).getStr(),
             XML_max,            OString::number( (nLastXclCol + 1) ).getStr(),
diff --git a/sc/source/filter/excel/xltools.cxx b/sc/source/filter/excel/xltools.cxx
index 0780908b38d1..3b3c1c852407 100644
--- a/sc/source/filter/excel/xltools.cxx
+++ b/sc/source/filter/excel/xltools.cxx
@@ -310,13 +310,13 @@ sal_Int32 XclTools::GetHmmFromTwips( sal_Int32 nTwips )
 
 sal_uInt16 XclTools::GetScColumnWidth( sal_uInt16 nXclWidth, long nScCharWidth )
 {
-    double fScWidth = static_cast< double >( nXclWidth ) / 256.0 * nScCharWidth + 0.5;
+    double fScWidth = static_cast< double >( nXclWidth ) / 256.0 * nScCharWidth - 0.5;
     return limit_cast< sal_uInt16 >( fScWidth );
 }
 
 sal_uInt16 XclTools::GetXclColumnWidth( sal_uInt16 nScWidth, long nScCharWidth )
 {
-    double fXclWidth = static_cast< double >( nScWidth ) * 256.0 / nScCharWidth + 0.5;
+    double fXclWidth = ( static_cast< double >( nScWidth ) + 0.5 ) * 256.0 / nScCharWidth;
     return limit_cast< sal_uInt16 >( fXclWidth );
 }
 
@@ -627,6 +627,7 @@ bool XclTools::GetBuiltInStyleId( sal_uInt8& rnStyleId, sal_uInt8& rnLevel, cons
             return true;
         }
     }
+
     rnStyleId = EXC_STYLE_USERDEF;
     rnLevel = EXC_STYLE_NOLEVEL;
     return false;
diff --git a/sc/source/filter/inc/xetable.hxx b/sc/source/filter/inc/xetable.hxx
index 71c1ed99aff3..3109b3794bba 100644
--- a/sc/source/filter/inc/xetable.hxx
+++ b/sc/source/filter/inc/xetable.hxx
@@ -683,6 +683,9 @@ private:
     if the '0' character fits 10 times into a cell in a column with default
     width.
 
+    Half of character width is reserved for non character display.
+    It is margin padding (two on each side) and padding for the gridlines.
+
     The IsDefWidth() function returns true, if the passed width (measured in
     1/256 of the width of the '0' character) could be converted exactly to the
     default width. If the passed width is rounded up or down to get the default
@@ -740,6 +743,7 @@ private:
 
 private:
     XclExpXFId          maXFId;             /// The XF identifier for column default format.
+    bool                mbCustomWidth;      /// True = Column width is different from default width
     sal_uInt16          mnWidth;            /// Excel width of the column.
     sal_uInt16          mnScWidth;          /// Calc width of the column.
     sal_uInt16          mnFlags;            /// Additional column flags.
diff --git a/sc/source/filter/inc/xltools.hxx b/sc/source/filter/inc/xltools.hxx
index fa0b952d6375..ca3965bbf17a 100644
--- a/sc/source/filter/inc/xltools.hxx
+++ b/sc/source/filter/inc/xltools.hxx
@@ -143,6 +143,7 @@ public:
     static sal_Int32    GetHmmFromTwips( sal_Int32 nTwips );
 
     /** Returns the Calc column width (twips) for the passed Excel width.
+     *  Excel Column width is stored as 1/256th of a character.
         @param nScCharWidth  Width of the '0' character in Calc (twips). */
     static sal_uInt16       GetScColumnWidth( sal_uInt16 nXclWidth, long nScCharWidth );
     /** Returns the Excel column width for the passed Calc width (twips).
diff --git a/sc/source/filter/oox/worksheethelper.cxx b/sc/source/filter/oox/worksheethelper.cxx
index cda819bafa0f..41c7302d7b3f 100644
--- a/sc/source/filter/oox/worksheethelper.cxx
+++ b/sc/source/filter/oox/worksheethelper.cxx
@@ -1206,11 +1206,10 @@ sal_Int32 getColumnWidth(UnitConverter& rConverter, double nWidth)
 
     long nPixel = aDev->LogicToPixel(Point(nCoeff, 0), MapMode(MAP_100TH_MM)).getX();
 
-
     // the 1.047 has been experimentally chosen based on measurements with a screen ruler
     // TODO: fix the display of cells so that it no longer requires this hack
     // algorithm from OOXML spec part1: 18.3.1.13
-    sal_Int32 nColWidthPixel= std::floor(((256*nWidth + std::floor(128.0/nPixel))/256.0)*nPixel) * 1.047;
+    sal_Int32 nColWidthPixel= std::floor( ( ( 256 * nWidth + std::floor( 128.0 / nPixel ) ) / 256.0 ) * nPixel ) * 1.047;
 
     return aDev->PixelToLogic(Point(nColWidthPixel, 0), MapMode(MAP_100TH_MM)).getX();
 }