sc/qa/unit/data/ods/different-column-width.ods |binary sc/qa/unit/data/xlsx/different-column-width-excel2010.xlsx |binary sc/qa/unit/data/xlsx/xf_default_values.xlsx |binary sc/qa/unit/subsequent_export-test.cxx | 138 ++++++++++++- sc/source/filter/oox/stylesbuffer.cxx | 1 sc/source/filter/oox/worksheethelper.cxx | 22 -- 6 files changed, 137 insertions(+), 24 deletions(-)
New commits: commit 962b53a3a36c388d7fa9706541e3d630292861c4 Author: Bartosz Kosiorek <gan...@poczta.onet.pl> Date: Mon Aug 8 01:13:05 2016 +0200 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. Reviewed-on: https://gerrit.libreoffice.org/27943 Tested-by: Jenkins <c...@libreoffice.org> Reviewed-by: Eike Rathke <er...@redhat.com> Tested-by: Eike Rathke <er...@redhat.com> (cherry picked from commit f2a75fecdfb5d92d27074312f29dc7dc4afb16ec) Conflicts: sc/source/filter/oox/worksheethelper.cxx Change-Id: I26d074b203d08a4bf4e9e31e52dbfd7ca7d3beb1 Reviewed-on: https://gerrit.libreoffice.org/31648 Reviewed-by: Eike Rathke <er...@redhat.com> Tested-by: Eike Rathke <er...@redhat.com> Tested-by: Jenkins <c...@libreoffice.org> 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 0000000..3f2e34c Binary files /dev/null and b/sc/qa/unit/data/ods/different-column-width.ods differ 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 0000000..93f133f Binary files /dev/null and b/sc/qa/unit/data/xlsx/different-column-width-excel2010.xlsx differ diff --git a/sc/qa/unit/subsequent_export-test.cxx b/sc/qa/unit/subsequent_export-test.cxx index 758c86b..efcb38f 100644 --- a/sc/qa/unit/subsequent_export-test.cxx +++ b/sc/qa/unit/subsequent_export-test.cxx @@ -112,6 +112,8 @@ public: void testCustomColumnWidthExportXLSX(); void testXfDefaultValuesXLSX(); + void testColumnWidthResaveXLSX(); + void testColumnWidthExportFromODStoXLSX(); void testOutlineExportXLSX(); void testHiddenEmptyRowsXLSX(); void testLandscapeOrientationXLSX(); @@ -200,6 +202,8 @@ public: CPPUNIT_TEST(testCustomColumnWidthExportXLSX); CPPUNIT_TEST(testXfDefaultValuesXLSX); + CPPUNIT_TEST(testColumnWidthResaveXLSX); + CPPUNIT_TEST(testColumnWidthExportFromODStoXLSX); CPPUNIT_TEST(testOutlineExportXLSX); CPPUNIT_TEST(testHiddenEmptyRowsXLSX); CPPUNIT_TEST(testLandscapeOrientationXLSX); @@ -491,7 +495,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"); @@ -499,7 +503,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"); @@ -507,7 +511,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"); @@ -597,6 +601,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 diff --git a/sc/source/filter/oox/worksheethelper.cxx b/sc/source/filter/oox/worksheethelper.cxx index 41c7302..c8f18dc 100644 --- a/sc/source/filter/oox/worksheethelper.cxx +++ b/sc/source/filter/oox/worksheethelper.cxx @@ -1197,30 +1197,12 @@ void WorksheetGlobals::convertColumns() convertOutlines( aColLevels, nMaxCol + 1, 0, false, false ); } -namespace { - -sal_Int32 getColumnWidth(UnitConverter& rConverter, double nWidth) -{ - double nCoeff = rConverter.getCoefficient(UNIT_DIGIT); - ScopedVclPtrInstance<VirtualDevice> aDev; - - 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; - - return aDev->PixelToLogic(Point(nColWidthPixel, 0), MapMode(MAP_100TH_MM)).getX(); -} - -} - void WorksheetGlobals::convertColumns( OutlineLevelVec& orColLevels, const ValueRange& rColRange, const ColumnModel& rModel ) { // column width: convert 'number of characters' to column width in 1/100 mm - sal_Int32 nWidth = getColumnWidth(getUnitConverter(), rModel.mfWidth); + sal_Int32 nWidth = getUnitConverter().scaleToMm100( rModel.mfWidth, UNIT_DIGIT ); + // macro sheets have double width if( meSheetType == SHEETTYPE_MACROSHEET ) nWidth *= 2; commit c88c3f66984df5ac4ca65431e7eacbeb93cb2217 Author: Bartosz Kosiorek <gan...@poczta.onet.pl> Date: Fri Aug 12 10:51:11 2016 +0200 tdf#70565 Set correct default values to "0" of xfId attribute When xfId is not exist during .xlsx import it must have values set to "0". It is not impacts spreadsheets created with MS Excel, as xfId attribute is always created during export to .xlsx Not setting "0" value is causing wrong .xlsx import by LibreOffice, for spreadsheets created by external applications (ex. SAP BI). Change-Id: Ia4986236d5e902d0ff6f7a7a8da8f142b2c5061f Reviewed-on: https://gerrit.libreoffice.org/28069 Tested-by: Jenkins <c...@libreoffice.org> Reviewed-by: Markus Mohrhard <markus.mohrh...@googlemail.com> (cherry picked from commit 9b9bcef5ef1858c63c8708bfae2ecea3d398eeb8) Reviewed-on: https://gerrit.libreoffice.org/31647 Reviewed-by: Eike Rathke <er...@redhat.com> Tested-by: Eike Rathke <er...@redhat.com> diff --git a/sc/qa/unit/data/xlsx/xf_default_values.xlsx b/sc/qa/unit/data/xlsx/xf_default_values.xlsx new file mode 100644 index 0000000..79aaea7 Binary files /dev/null and b/sc/qa/unit/data/xlsx/xf_default_values.xlsx differ diff --git a/sc/qa/unit/subsequent_export-test.cxx b/sc/qa/unit/subsequent_export-test.cxx index 75ac9d3..758c86b 100644 --- a/sc/qa/unit/subsequent_export-test.cxx +++ b/sc/qa/unit/subsequent_export-test.cxx @@ -111,6 +111,7 @@ public: void testFormatExportODS(); void testCustomColumnWidthExportXLSX(); + void testXfDefaultValuesXLSX(); void testOutlineExportXLSX(); void testHiddenEmptyRowsXLSX(); void testLandscapeOrientationXLSX(); @@ -198,6 +199,7 @@ public: CPPUNIT_TEST(testFormatExportODS); CPPUNIT_TEST(testCustomColumnWidthExportXLSX); + CPPUNIT_TEST(testXfDefaultValuesXLSX); CPPUNIT_TEST(testOutlineExportXLSX); CPPUNIT_TEST(testHiddenEmptyRowsXLSX); CPPUNIT_TEST(testLandscapeOrientationXLSX); @@ -479,7 +481,6 @@ void ScExportTest::testFormatExportODS() xDocSh->DoClose(); } - void ScExportTest::testCustomColumnWidthExportXLSX() { //tdf#100946 FILESAVE Excel on OS X ignored column widths in XLSX last saved by LO @@ -572,6 +573,29 @@ void ScExportTest::testCustomColumnWidthExportXLSX() assertXPath(pSheet, "/x:worksheet/x:sheetData/x:row[1]", "customHeight", "false"); } +void ScExportTest::testXfDefaultValuesXLSX() +{ + //tdf#70565 FORMATTING: User Defined Custom Formatting is not applied during importing XLSX documents + ScDocShellRef xShell = loadDoc("xf_default_values.", 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/styles.xml"); + CPPUNIT_ASSERT(pSheet); + + // cellStyleXfs don't need xfId, so we need to make sure it is not saved + assertXPathNoAttribute(pSheet, "/x:styleSheet/x:cellStyleXfs/x:xf[1]", "xfId"); + + // Because numFmtId fontId fillId borderId xfId are not existing during import + // it should be created during export, with values set to "0" + assertXPath(pSheet, "/x:styleSheet/x:cellXfs/x:xf[1]", "xfId", "0"); + assertXPath(pSheet, "/x:styleSheet/x:cellXfs/x:xf[2]", "xfId", "0"); + assertXPath(pSheet, "/x:styleSheet/x:cellXfs/x:xf[3]", "xfId", "0"); + assertXPath(pSheet, "/x:styleSheet/x:cellXfs/x:xf[4]", "xfId", "0"); + + // We expected that exactly 15 cellXfs:xf Nodes will be produced + assertXPath(pSheet, "/x:styleSheet/x:cellXfs/x:xf", 14); +} void ScExportTest::testOutlineExportXLSX() { diff --git a/sc/source/filter/oox/stylesbuffer.cxx b/sc/source/filter/oox/stylesbuffer.cxx index 3f73c09..6818f03 100644 --- a/sc/source/filter/oox/stylesbuffer.cxx +++ b/sc/source/filter/oox/stylesbuffer.cxx @@ -2092,6 +2092,7 @@ void Xf::importXf( const AttributeList& rAttribs, bool bCellXf ) maModel.mnBorderId = rAttribs.getInteger( XML_borderId, -1 ); maModel.mnFillId = rAttribs.getInteger( XML_fillId, -1 ); + /* Default value of the apply*** attributes is dependent on context: true in cellStyleXfs element, false in cellXfs element... */ maModel.mbAlignUsed = rAttribs.getBool( XML_applyAlignment, !maModel.mbCellXf ); _______________________________________________ Libreoffice-commits mailing list libreoffice-comm...@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-commits