tdf#35208 Allow choosing encoding for old Excel files

Lots of BIFF2-BIFF5 files out there don't have CODEPAGE
record at all, or have one with a wrong value, and/or
FONT records with wrong charset value.

To solve that, this patch adds a new "Choose Encoding"
entry to the file picker, so that users could specify the
encoding to use for a given file, instead of relying on
the information that might be present (or not) inside the
file. It can be also used in headless mode,
e.g. --infilter="MS Excel (encoded)":61

This doesn't affect BIFF8 import, nor the default behavior
when not explicitly using the "Choose Encoding" entry.

Change-Id: I89d850d7679b81bd399044478fac7a02e8b7680e

18 files changed, 103 insertions, 10 deletions

diff --git a/filter/Configuration_filter.mk b/filter/Configuration_filter.mk
index 5dd20f7f12f7..1fcd3a51100a 100644
--- a/filter/Configuration_filter.mk
+++ b/filter/Configuration_filter.mk
@@ -502,6 +502,7 @@ $(eval $(call filter_Configuration_add_filters,fcfg_langpack,fcfg_calc_filters.x
 	MS_Excel_95_Vorlage_Template \
 	MS_Excel_97 \
 	MS_Excel_97_Vorlage_Template \
+	MS_Excel__encoded_ \
 	Rich_Text_Format__StarCalc_ \
 	SYLK \
 	StarOffice_XML__Calc_ \
diff --git a/filter/source/config/fragments/filters/MS_Excel__encoded_.xcu b/filter/source/config/fragments/filters/MS_Excel__encoded_.xcu
new file mode 100644
index 000000000000..767be941f1b9
--- /dev/null
+++ b/filter/source/config/fragments/filters/MS_Excel__encoded_.xcu
@@ -0,0 +1,30 @@
+<!--
+ * This file is part of the LibreOffice project.
+ *
+ * This Source Code Form is subject to the terms of the Mozilla Public
+ * License, v. 2.0. If a copy of the MPL was not distributed with this
+ * file, You can obtain one at http://mozilla.org/MPL/2.0/.
+ *
+ * This file incorporates work covered by the following license notice:
+ *
+ *   Licensed to the Apache Software Foundation (ASF) under one or more
+ *   contributor license agreements. See the NOTICE file distributed
+ *   with this work for additional information regarding copyright
+ *   ownership. The ASF licenses this file to you under the Apache
+ *   License, Version 2.0 (the "License"); you may not use this file
+ *   except in compliance with the License. You may obtain a copy of
+ *   the License at http://www.apache.org/licenses/LICENSE-2.0 .
+-->
+<node oor:name="MS Excel (encoded)" oor:op="replace">
+    <prop oor:name="Flags"><value>IMPORT ALIEN</value></prop>
+    <prop oor:name="UIComponent"><value>com.sun.star.comp.Calc.FilterOptionsDialog</value></prop>
+    <prop oor:name="FilterService"/>
+    <prop oor:name="UserData"/>
+    <prop oor:name="UIName">
+        <value xml:lang="en-US">Microsoft Excel - Choose Encoding</value>
+    </prop>
+    <prop oor:name="FileFormatVersion"><value>0</value></prop>
+    <prop oor:name="Type"><value>calc_MS_Excel_40</value></prop>
+    <prop oor:name="TemplateName"/>
+    <prop oor:name="DocumentService"><value>com.sun.star.sheet.SpreadsheetDocument</value></prop>
+</node>
diff --git a/officecfg/registry/data/org/openoffice/TypeDetection/UISort.xcu b/officecfg/registry/data/org/openoffice/TypeDetection/UISort.xcu
index 2dd54edbd963..cf811a7a5f7c 100644
--- a/officecfg/registry/data/org/openoffice/TypeDetection/UISort.xcu
+++ b/officecfg/registry/data/org/openoffice/TypeDetection/UISort.xcu
@@ -21,7 +21,7 @@
   <node oor:name="ModuleDependendFilterOrder">
     <node oor:name="com.sun.star.sheet.SpreadsheetDocument" oor:op="replace" install:module="calc">
       <prop oor:name="SortedFilterList">
-        <value oor:separator=";">calc8;calc8_template;StarOffice XML (Calc);calc_StarOffice_XML_Calc_Template;OpenDocument Spreadsheet Flat XML;UOF spreadsheet;Calc MS Excel 2007 XML;Calc MS Excel 2007 XML Template;Calc MS Excel 2007 Binary;MS Excel 2003 XML;MS Excel 97;MS Excel 97 Vorlage/Template;MS Excel 95;MS Excel 95 Vorlage/Template;MS Excel 5.0/95;MS Excel 5.0/95 Vorlage/Template;MS Excel 4.0;MS Excel 4.0 Vorlage/Template;;DIF;dBase;HTML (StarCalc);Lotus;Pocket Excel;Quattro Pro 6.0;Rich Text Format (StarCalc);SYLK;Text - txt - csv (StarCalc);calc_HTML_WebQuery;Calc Office Open XML;Calc Office Open XML Template</value>
+        <value oor:separator=";">calc8;calc8_template;StarOffice XML (Calc);calc_StarOffice_XML_Calc_Template;OpenDocument Spreadsheet Flat XML;UOF spreadsheet;Calc MS Excel 2007 XML;Calc MS Excel 2007 XML Template;Calc MS Excel 2007 Binary;MS Excel 2003 XML;MS Excel 97;MS Excel 97 Vorlage/Template;MS Excel 95;MS Excel 95 Vorlage/Template;MS Excel 5.0/95;MS Excel 5.0/95 Vorlage/Template;MS Excel 4.0;MS Excel 4.0 Vorlage/Template;MS Excel (encoded);DIF;dBase;HTML (StarCalc);Lotus;Pocket Excel;Quattro Pro 6.0;Rich Text Format (StarCalc);SYLK;Text - txt - csv (StarCalc);calc_HTML_WebQuery;Calc Office Open XML;Calc Office Open XML Template</value>
       </prop>
     </node>
     <node oor:name="com.sun.star.drawing.DrawingDocument" oor:op="replace" install:module="draw">
diff --git a/sc/inc/filter.hxx b/sc/inc/filter.hxx
index 8ea69de84f71..a987f8d7747b 100644
--- a/sc/inc/filter.hxx
+++ b/sc/inc/filter.hxx
@@ -84,7 +84,7 @@ class SAL_DLLPUBLIC_RTTI ScFormatFilterPlugin {
     // various import filters
     virtual FltError ScImportLotus123( SfxMedium&, ScDocument*, rtl_TextEncoding eSrc = RTL_TEXTENCODING_DONTKNOW ) = 0;
     virtual FltError ScImportQuattroPro( SfxMedium &rMedium, ScDocument *pDoc ) = 0;
-    virtual FltError ScImportExcel( SfxMedium&, ScDocument*, const EXCIMPFORMAT ) = 0;
+    virtual FltError ScImportExcel( SfxMedium&, ScDocument*, const EXCIMPFORMAT, rtl_TextEncoding eSrc = RTL_TEXTENCODING_DONTKNOW ) = 0;
         // eFormat == EIF_AUTO  -> matching filter is used automatically
         // eFormat == EIF_BIFF5 -> only Biff5 stream is read successfully (in an Excel97 doc, too)
         // eFormat == EIF_BIFF8 -> only Biff8 stream is read successfully (only in Excel97 docs)
diff --git a/sc/inc/globstr.hrc b/sc/inc/globstr.hrc
index 81e44932d49b..638a01cfa246 100644
--- a/sc/inc/globstr.hrc
+++ b/sc/inc/globstr.hrc
@@ -708,7 +708,9 @@
 
 #define STR_FILTER_SELCOUNT             533
 
-#define SC_GLOBSTR_STR_COUNT            534     /**< the count of permanently resident strings */
+#define STR_IMPORT_EXCEL_ENCODING       534
+
+#define SC_GLOBSTR_STR_COUNT            535     /**< the count of permanently resident strings */
 
 #endif
 
diff --git a/sc/qa/unit/data/xls/biff5enc.xls b/sc/qa/unit/data/xls/biff5enc.xls
new file mode 100644
index 000000000000..6f71d661bd00
--- /dev/null
+++ b/sc/qa/unit/data/xls/biff5enc.xls
diff --git a/sc/qa/unit/helper/qahelper.cxx b/sc/qa/unit/helper/qahelper.cxx
index ac61dc2f9955..2fe290a181d6 100644
--- a/sc/qa/unit/helper/qahelper.cxx
+++ b/sc/qa/unit/helper/qahelper.cxx
@@ -86,7 +86,8 @@ const FileFormat ScBootstrapFixture::aFileFormats[] = {
     { "dif", "DIF", "calc_DIF", DIF_FORMAT_TYPE },
     { "xml", "MS Excel 2003 XML", "calc_MS_Excel_2003_XML", XLS_XML_FORMAT_TYPE },
     { "xlsb", "Calc MS Excel 2007 Binary", "MS Excel 2007 Binary", XLSB_XML_FORMAT_TYPE },
-    { "fods", "OpenDocument Spreadsheet Flat XML", "calc_ODS_FlatXML", FODS_FORMAT_TYPE }
+    { "fods", "OpenDocument Spreadsheet Flat XML", "calc_ODS_FlatXML", FODS_FORMAT_TYPE },
+    { "xls" , "MS Excel (encoded)", "calc_MS_Excel_40", XLS_ENC_FORMAT_TYPE }
 };
 
 bool testEqualsWithTolerance( long nVal1, long nVal2, long nTol )
@@ -556,6 +557,10 @@ ScDocShellRef ScBootstrapFixture::load( bool bReadWrite,
     {
         pSet->Put(SfxStringItem(SID_PASSWORD, *pPassword));
     }
+    if (rFilter == "MS Excel (encoded)")
+    {
+        pSet->Put(SfxStringItem(SID_FILE_FILTEROPTIONS, "34"));
+    }
     pSet->Put(SfxUInt16Item(SID_MACROEXECMODE,css::document::MacroExecMode::ALWAYS_EXECUTE_NO_WARN));
     SAL_INFO( "sc.qa", "about to load " << rURL );
     if (!xDocShRef->DoLoad(pSrcMed))
diff --git a/sc/qa/unit/helper/qahelper.hxx b/sc/qa/unit/helper/qahelper.hxx
index 9929c6c800c1..973c4ee0bdf8 100644
--- a/sc/qa/unit/helper/qahelper.hxx
+++ b/sc/qa/unit/helper/qahelper.hxx
@@ -52,6 +52,7 @@
 #define XLS_XML_FORMAT_TYPE  (SfxFilterFlags::IMPORT | SfxFilterFlags::EXPORT | SfxFilterFlags::ALIEN)
 #define XLSB_XML_FORMAT_TYPE (SfxFilterFlags::IMPORT |                          SfxFilterFlags::ALIEN | SfxFilterFlags::STARONEFILTER | SfxFilterFlags::PREFERED)
 #define FODS_FORMAT_TYPE     (SfxFilterFlags::IMPORT | SfxFilterFlags::EXPORT | SfxFilterFlags::OWN | SfxFilterFlags::STARONEFILTER )
+#define XLS_ENC_FORMAT_TYPE  (SfxFilterFlags::IMPORT |                          SfxFilterFlags::ALIEN)
 
 #define FORMAT_ODS      0
 #define FORMAT_XLS      1
@@ -64,6 +65,7 @@
 #define FORMAT_XLS_XML  8
 #define FORMAT_XLSB     9
 #define FORMAT_FODS     10
+#define FORMAT_XLS_ENC  11
 
 enum StringType { PureString, FormulaValue, StringValue };
 
diff --git a/sc/qa/unit/subsequent_filters-test.cxx b/sc/qa/unit/subsequent_filters-test.cxx
index 0c1428ec3c06..01111cf698e2 100644
--- a/sc/qa/unit/subsequent_filters-test.cxx
+++ b/sc/qa/unit/subsequent_filters-test.cxx
@@ -101,6 +101,7 @@ public:
     virtual void tearDown() override;
 
     //ods, xls, xlsx filter tests
+    void testEncodedXLS();
     void testBooleanFormatXLSX();
     void testBasicCellContentODS();
     void testRangeNameXLS();
@@ -216,6 +217,7 @@ public:
     void testBnc762542();
 
     CPPUNIT_TEST_SUITE(ScFiltersTest);
+    CPPUNIT_TEST(testEncodedXLS);
     CPPUNIT_TEST(testBooleanFormatXLSX);
     CPPUNIT_TEST(testBasicCellContentODS);
     CPPUNIT_TEST(testRangeNameXLS);
@@ -399,6 +401,17 @@ void ScFiltersTest::testBasicCellContentODS()
     xDocSh->DoClose();
 }
 
+void ScFiltersTest::testEncodedXLS()
+{
+    ScDocShellRef xDocSh = loadDoc("biff5enc.", FORMAT_XLS_ENC);
+    ScDocument& rDoc = xDocSh->GetDocument();
+
+    OUString aText = rDoc.GetString(0, 0, 0);
+    CPPUNIT_ASSERT_EQUAL(OUString(sal_Unicode(0x43F)), aText);
+
+    xDocSh->DoClose();
+}
+
 void ScFiltersTest::testBooleanFormatXLSX()
 {
     ScDocShellRef xDocSh = loadDoc("check-boolean.", FORMAT_XLSX);
diff --git a/sc/source/filter/excel/excel.cxx b/sc/source/filter/excel/excel.cxx
index 73ee93ed3773..323e209892fa 100644
--- a/sc/source/filter/excel/excel.cxx
+++ b/sc/source/filter/excel/excel.cxx
@@ -45,7 +45,7 @@
 
 #include <memory>
 
-FltError ScFormatFilterPluginImpl::ScImportExcel( SfxMedium& rMedium, ScDocument* pDocument, const EXCIMPFORMAT eFormat )
+FltError ScFormatFilterPluginImpl::ScImportExcel( SfxMedium& rMedium, ScDocument* pDocument, const EXCIMPFORMAT eFormat, rtl_TextEncoding eSrc )
 {
     // check the passed Calc document
     OSL_ENSURE( pDocument, "::ScImportExcel - no document" );
@@ -129,6 +129,11 @@ FltError ScFormatFilterPluginImpl::ScImportExcel( SfxMedium& rMedium, ScDocument
             case EXC_BIFF3:
             case EXC_BIFF4:
             case EXC_BIFF5:
+                if ( eSrc != RTL_TEXTENCODING_DONTKNOW )
+                {
+                    aImpData.meTextEnc = eSrc;
+                    aImpData.mbForcedTextEnc = true;
+                }
                 xFilter.reset( new ImportExcel( aImpData, *pBookStrm ) );
             break;
             case EXC_BIFF8:
diff --git a/sc/source/filter/excel/xlroot.cxx b/sc/source/filter/excel/xlroot.cxx
index 46d9d28c2ad8..91f90727155d 100644
--- a/sc/source/filter/excel/xlroot.cxx
+++ b/sc/source/filter/excel/xlroot.cxx
@@ -95,6 +95,7 @@ XclRootData::XclRootData( XclBiff eBiff, SfxMedium& rMedium,
     mfScreenPixelY( 50.0 ),
     mnCharWidth( 110 ),
     mnScTab( 0 ),
+    mbForcedTextEnc( false ),
     mbExport( bExport )
 {
  maUserName = SvtUserOptions().GetLastName();
@@ -191,7 +192,7 @@ XclRoot& XclRoot::operator=( const XclRoot& rRoot )
 
 void XclRoot::SetTextEncoding( rtl_TextEncoding eTextEnc )
 {
-    if( eTextEnc != RTL_TEXTENCODING_DONTKNOW )
+    if( eTextEnc != RTL_TEXTENCODING_DONTKNOW && !mrData.mbForcedTextEnc )
         mrData.meTextEnc = eTextEnc;
 }
 
diff --git a/sc/source/filter/inc/ftools.hxx b/sc/source/filter/inc/ftools.hxx
index 1fb6b03c321d..10817ef8df1d 100644
--- a/sc/source/filter/inc/ftools.hxx
+++ b/sc/source/filter/inc/ftools.hxx
@@ -269,7 +269,7 @@ public:
     // various import filters
     virtual FltError ScImportLotus123( SfxMedium&, ScDocument*, rtl_TextEncoding eSrc = RTL_TEXTENCODING_DONTKNOW ) override;
     virtual FltError ScImportQuattroPro( SfxMedium &rMedium, ScDocument *pDoc ) override;
-    virtual FltError ScImportExcel( SfxMedium&, ScDocument*, const EXCIMPFORMAT ) override;
+    virtual FltError ScImportExcel( SfxMedium&, ScDocument*, const EXCIMPFORMAT, rtl_TextEncoding eSrc = RTL_TEXTENCODING_DONTKNOW ) override;
         // eFormat == EIF_AUTO  -> passender Filter wird automatisch verwendet
         // eFormat == EIF_BIFF5 -> nur Biff5-Stream fuehrt zum Erfolg (auch wenn in einem Excel97-Doc)
         // eFormat == EIF_BIFF8 -> nur Biff8-Stream fuehrt zum Erfolg (nur in Excel97-Docs)
diff --git a/sc/source/filter/inc/xlroot.hxx b/sc/source/filter/inc/xlroot.hxx
index 50829231bef8..dd927ceeab90 100644
--- a/sc/source/filter/inc/xlroot.hxx
+++ b/sc/source/filter/inc/xlroot.hxx
@@ -109,6 +109,7 @@ struct XclRootData
     double              mfScreenPixelY;     /// Height of a screen pixel (1/100 mm).
     long                mnCharWidth;        /// Width of '0' in default font (twips).
     SCTAB               mnScTab;            /// Current Calc sheet index.
+    bool                mbForcedTextEnc;    /// true = Text encoding is not allowed to be changed.
     const bool          mbExport;           /// false = Import, true = Export.
 
     explicit            XclRootData( XclBiff eBiff, SfxMedium& rMedium,
diff --git a/sc/source/ui/docshell/docsh.cxx b/sc/source/ui/docshell/docsh.cxx
index de05c84cc7cc..599fce43d954 100644
--- a/sc/source/ui/docshell/docsh.cxx
+++ b/sc/source/ui/docshell/docsh.cxx
@@ -153,6 +153,7 @@ static const sal_Char pFilterExcel95[]  = "MS Excel 95";
 static const sal_Char pFilterEx95Temp[] = "MS Excel 95 Vorlage/Template";
 static const sal_Char pFilterExcel97[]  = "MS Excel 97";
 static const sal_Char pFilterEx97Temp[] = "MS Excel 97 Vorlage/Template";
+static const sal_Char pFilterExcelEnc[] = "MS Excel (encoded)";
 static const sal_Char pFilterDBase[]        = "dBase";
 static const sal_Char pFilterDif[]      = "DIF";
 static const sal_Char pFilterSylk[]     = "SYLK";
@@ -1110,7 +1111,8 @@ bool ScDocShell::ConvertFrom( SfxMedium& rMedium )
             bSetColWidths = true;
             bSetRowHeights = true;
         }
-        else if ( aFltName == pFilterExcel4 || aFltName == pFilterExcel5 ||
+        else if (  aFltName == pFilterExcelEnc ||
+                   aFltName == pFilterExcel4 || aFltName == pFilterExcel5 ||
                    aFltName == pFilterExcel95 || aFltName == pFilterExcel97 ||
                    aFltName == pFilterEx4Temp || aFltName == pFilterEx5Temp ||
                    aFltName == pFilterEx95Temp || aFltName == pFilterEx97Temp )
@@ -1124,9 +1126,24 @@ bool ScDocShell::ConvertFrom( SfxMedium& rMedium )
             else if ( aFltName == pFilterExcel97 || aFltName == pFilterEx97Temp )
                 eFormat = EIF_BIFF8;
 
+            rtl_TextEncoding eTextEnc = RTL_TEXTENCODING_DONTKNOW;
+            if ( eFormat != EIF_BIFF8 )
+            {
+                OUString sItStr;
+                SfxItemSet* pSet = rMedium.GetItemSet();
+                const SfxPoolItem* pItem;
+                if ( pSet && SfxItemState::SET ==
+                     pSet->GetItemState( SID_FILE_FILTEROPTIONS, true, &pItem ) )
+                {
+                    sItStr = static_cast<const SfxStringItem*>(pItem)->GetValue();
+                    if ( !sItStr.isEmpty() )
+                        eTextEnc = ScGlobal::GetCharsetValue( sItStr );
+                }
+            }
+
             MakeDrawLayer(); //! In the filter
             CalcOutputFactor(); // prepare update of row height
-            FltError eError = ScFormatFilter::Get().ScImportExcel( rMedium, &aDocument, eFormat );
+            FltError eError = ScFormatFilter::Get().ScImportExcel( rMedium, &aDocument, eFormat, eTextEnc );
             aDocument.UpdateFontCharSet();
             if ( aDocument.IsChartListenerCollectionNeedsUpdate() )
                 aDocument.UpdateChartListenerCollection(); //! For all imports?
@@ -2598,6 +2615,11 @@ OUString ScDocShell::GetDifFilterName()
     return OUString(pFilterDif);
 }
 
+OUString ScDocShell::GetExcelEncodingFilterName()
+{
+    return OUString(pFilterExcelEnc);
+}
+
 bool ScDocShell::HasAutomaticTableName( const OUString& rFilter )
 {
     //  sal_True for those filters that keep the default table name
diff --git a/sc/source/ui/docshell/impex.cxx b/sc/source/ui/docshell/impex.cxx
index 47a796414ce8..9b23831e1c99 100644
--- a/sc/source/ui/docshell/impex.cxx
+++ b/sc/source/ui/docshell/impex.cxx
@@ -2217,7 +2217,7 @@ class ScFormatFilterMissing : public ScFormatFilterPlugin {
     virtual ~ScFormatFilterMissing() {}
     virtual FltError ScImportLotus123( SfxMedium&, ScDocument*, rtl_TextEncoding ) override { return eERR_INTERN; }
     virtual FltError ScImportQuattroPro( SfxMedium &, ScDocument * ) override { return eERR_INTERN; }
-    virtual FltError ScImportExcel( SfxMedium&, ScDocument*, const EXCIMPFORMAT ) override { return eERR_INTERN; }
+    virtual FltError ScImportExcel( SfxMedium&, ScDocument*, const EXCIMPFORMAT, rtl_TextEncoding ) override { return eERR_INTERN; }
     virtual FltError ScImportStarCalc10( SvStream&, ScDocument* ) override { return eERR_INTERN; }
     virtual FltError ScImportDif( SvStream&, ScDocument*, const ScAddress&,
                  const rtl_TextEncoding, sal_uInt32 ) override { return eERR_INTERN; }
diff --git a/sc/source/ui/inc/docsh.hxx b/sc/source/ui/inc/docsh.hxx
index 079b542e68dc..914f369d6ba1 100644
--- a/sc/source/ui/inc/docsh.hxx
+++ b/sc/source/ui/inc/docsh.hxx
@@ -398,6 +398,7 @@ public:
     static OUString   GetLotusFilterName();
     static OUString   GetDBaseFilterName();
     static OUString   GetDifFilterName();
+    static OUString   GetExcelEncodingFilterName();
     static bool       HasAutomaticTableName( const OUString& rFilter );
 
     DECL_LINK_TYPED( RefreshDBDataHdl, Timer*, void );
diff --git a/sc/source/ui/src/globstr.src b/sc/source/ui/src/globstr.src
index c74373f662b3..f026a13e5640 100644
--- a/sc/source/ui/src/globstr.src
+++ b/sc/source/ui/src/globstr.src
@@ -2109,6 +2109,10 @@ Resource RID_GLOBSTR
     {
         Text [ en-US ] = "%1 and %2 more";
     };
+    String STR_IMPORT_EXCEL_ENCODING
+    {
+        Text [ en-US ] = "Import Excel file";
+    };
 };
 
 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/source/ui/unoobj/filtuno.cxx b/sc/source/ui/unoobj/filtuno.cxx
index 96f74ce7b949..eac4e805da4c 100644
--- a/sc/source/ui/unoobj/filtuno.cxx
+++ b/sc/source/ui/unoobj/filtuno.cxx
@@ -274,6 +274,12 @@ sal_Int16 SAL_CALL ScFilterOptionsObj::execute() throw(uno::RuntimeException, st
             // common for DIF import/export
             eEncoding = RTL_TEXTENCODING_MS_1252;
         }
+        else if ( aFilterString == ScDocShell::GetExcelEncodingFilterName() )
+        {
+            SAL_WARN_IF( bExport, "sc.ui", "Filter Options for Excel Export is not implemented" );
+            aTitle = ScGlobal::GetRscString( STR_IMPORT_EXCEL_ENCODING );
+            eEncoding = RTL_TEXTENCODING_MS_1252;
+        }
 
         ScImportOptions aOptions( cAsciiDel, cStrDel, eEncoding);