Resolves: tdf#114878 Add 'Evaluate formulas' option to CSV import and paste

... and Text to Columns, defaulting to false for CSV import via dialog
and stored as config item option similar to all other CSV/Text options.

Change-Id: I742f447b905a07b05dcf5fc58796c46de212276b
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/121344
Reviewed-by: Eike Rathke <erack@redhat.com>
Tested-by: Jenkins

7 files changed, 70 insertions, 25 deletions

diff --git a/sc/source/ui/dbgui/asciiopt.cxx b/sc/source/ui/dbgui/asciiopt.cxx
index af65dbc54956..3bb97c5b3236 100644
--- a/sc/source/ui/dbgui/asciiopt.cxx
+++ b/sc/source/ui/dbgui/asciiopt.cxx
@@ -32,6 +32,7 @@ ScAsciiOptions::ScAsciiOptions() :
     bRemoveSpace    ( false ),
     bQuotedFieldAsText(false),
     bDetectSpecialNumber(false),
+    bEvaluateFormulas(true),
     bSkipEmptyCells(false),
     bSaveAsShown(true),
     bSaveFormulas(false),
@@ -176,14 +177,19 @@ void ScAsciiOptions::ReadFromString( const OUString& rString )
         bRemoveSpace = false;
 
     // Token 11: sheet to export for --convert-to csv
-    // Does not need to be evaluated here but may be present, so in case
-    // there'll be yet another token 12 then do some dummy like
-#if 0
+    // Does not need to be evaluated here but may be present.
     if (nPos >= 0)
     {
         rString.getToken(0, ',', nPos);
     }
-#endif
+
+    // Token 12: evaluate formulas.
+    if (nPos >= 0)
+    {
+        bEvaluateFormulas = rString.getToken(0, ',', nPos) == "true";
+    }
+    else
+        bEvaluateFormulas = true;   // default of versions that didn't add the parameter
 }
 
 OUString ScAsciiOptions::WriteToString() const
@@ -238,20 +244,22 @@ OUString ScAsciiOptions::WriteToString() const
     // Always keep in sync with ScImportOptions.
 
     aOutStr.append("," +
-               //Token 5: Language
+               // Token 5: Language
                OUString::number(static_cast<sal_uInt16>(eLang)) + "," +
-               //Token 6: Import quoted field as text.
+               // Token 6: Import quoted field as text.
                OUString::boolean( bQuotedFieldAsText ) + "," +
-               //Token 7: Detect special numbers.
+               // Token 7: Detect special numbers.
                OUString::boolean( bDetectSpecialNumber ) + "," +
                // Token 8: used for "Save as shown" in export options
                OUString::boolean( bSaveAsShown ) +"," +
                // Token 9: used for "Save cell formulas" in export options
                OUString::boolean( bSaveFormulas ) + "," +
-               //Token 10: Trim Space
+               // Token 10: Trim Space
                OUString::boolean( bRemoveSpace ) +
-               //Token 11: sheet to export, always 0 for current sheet
-               ",0"
+               // Token 11: sheet to export, always 0 for current sheet
+               ",0," +
+               // Token 12: evaluate formulas in import
+               OUString::boolean( bEvaluateFormulas )
             );
     return aOutStr.makeStringAndClear();
 }
diff --git a/sc/source/ui/dbgui/imoptdlg.cxx b/sc/source/ui/dbgui/imoptdlg.cxx
index a362e4df0ee7..2b279f1be10a 100644
--- a/sc/source/ui/dbgui/imoptdlg.cxx
+++ b/sc/source/ui/dbgui/imoptdlg.cxx
@@ -45,6 +45,7 @@ ScImportOptions::ScImportOptions( const OUString& rStr )
     bSaveFormulas = false;
     bRemoveSpace = false;
     nSheetToExport = 0;
+    bEvaluateFormulas = true;
     sal_Int32 nTokenCount = comphelper::string::getTokenCount(rStr, ',');
     if ( nTokenCount < 3 )
         return;
@@ -89,6 +90,8 @@ ScImportOptions::ScImportOptions( const OUString& rStr )
             else
                 nSheetToExport = -23;   // invalid, force error
         }
+        if ( nTokenCount >= 13 )
+            bEvaluateFormulas = rStr.getToken(0, ',', nIdx) == "true";
     }
 }
 
@@ -113,7 +116,9 @@ OUString ScImportOptions::BuildString() const
             "," +
             OUString::boolean( bRemoveSpace ) +  // same as "Remove space" in ScAsciiOptions
             "," +
-            OUString::number(nSheetToExport) ;  // Only available for command line --convert-to
+            OUString::number(nSheetToExport) +  // Only available for command line --convert-to
+            "," +
+            OUString::boolean( bEvaluateFormulas ) ;  // same as "Evaluate formulas" in ScAsciiOptions
 
     return aResult;
 }
diff --git a/sc/source/ui/dbgui/scuiasciiopt.cxx b/sc/source/ui/dbgui/scuiasciiopt.cxx
index 7390786c5cd9..0a951b928c5d 100644
--- a/sc/source/ui/dbgui/scuiasciiopt.cxx
+++ b/sc/source/ui/dbgui/scuiasciiopt.cxx
@@ -57,6 +57,7 @@ using namespace com::sun::star::uno;
 namespace {
 
 // Defines - CSV Import Preserve Options
+// For usage of index order see lcl_CreatePropertiesNames() below.
 enum CSVImportOptionsIndex
 {
     CSVIO_MergeDelimiters = 0,
@@ -64,17 +65,24 @@ enum CSVImportOptionsIndex
     CSVIO_TextSeparators,
     CSVIO_FixedWidth,
     CSVIO_RemoveSpace,
+    CSVIO_EvaluateFormulas,
+    // Settings for *all* dialog invocations above.
+    // Settings not for SC_TEXTTOCOLUMNS below.
     CSVIO_FromRow,
     CSVIO_Text2ColSkipEmptyCells = CSVIO_FromRow,
     CSVIO_CharSet,
     CSVIO_QuotedAsText,
     CSVIO_DetectSpecialNum,
     CSVIO_Language,
+    // Plus one not for SC_IMPORTFILE.
     CSVIO_PasteSkipEmptyCells
 };
 
 }
 
+// Config items for all three paths are defined in
+// officecfg/registry/schema/org/openoffice/Office/Calc.xcs
+// If not, options are neither loaded nor saved.
 const ::std::vector<OUString> CSVImportOptionNames =
 {
     "MergeDelimiters",
@@ -82,6 +90,7 @@ const ::std::vector<OUString> CSVImportOptionNames =
     "TextSeparators",
     "FixedWidth",
     "RemoveSpace",
+    "EvaluateFormulas",
     "FromRow",
     "CharSet",
     "QuotedFieldAsText",
@@ -164,16 +173,16 @@ static void lcl_CreatePropertiesNames ( OUString& rSepPath, Sequence<OUString>&
     {
         case SC_IMPORTFILE:
             rSepPath = aSep_Path;
-            nProperties = 10;
+            nProperties = 11;
             break;
         case SC_PASTETEXT:
             rSepPath = aSep_Path_Clpbrd;
-            nProperties = 11;
+            nProperties = 12;
             break;
         case SC_TEXTTOCOLUMNS:
         default:
             rSepPath = aSep_Path_Text2Col;
-            nProperties = 6;
+            nProperties = 7;
             break;
     }
     rNames.realloc( nProperties );
@@ -183,6 +192,7 @@ static void lcl_CreatePropertiesNames ( OUString& rSepPath, Sequence<OUString>&
     pNames[ CSVIO_TextSeparators ] =    CSVImportOptionNames[ CSVIO_TextSeparators ];
     pNames[ CSVIO_FixedWidth ] =        CSVImportOptionNames[ CSVIO_FixedWidth ];
     pNames[ CSVIO_RemoveSpace ] =       CSVImportOptionNames[ CSVIO_RemoveSpace ];
+    pNames[ CSVIO_EvaluateFormulas ] =  CSVImportOptionNames[ CSVIO_EvaluateFormulas ];
     if (eCall != SC_TEXTTOCOLUMNS)
     {
         pNames[ CSVIO_FromRow ] =       CSVImportOptionNames[ CSVIO_FromRow ];
@@ -202,7 +212,8 @@ static void lcl_CreatePropertiesNames ( OUString& rSepPath, Sequence<OUString>&
 static void lcl_LoadSeparators( OUString& rFieldSeparators, OUString& rTextSeparators,
                              bool& rMergeDelimiters, bool& rQuotedAsText, bool& rDetectSpecialNum,
                              bool& rFixedWidth, sal_Int32& rFromRow, sal_Int32& rCharSet,
-                             sal_Int32& rLanguage, bool& rSkipEmptyCells, bool& rRemoveSpace, ScImportAsciiCall eCall )
+                             sal_Int32& rLanguage, bool& rSkipEmptyCells, bool& rRemoveSpace,
+                             bool& rEvaluateFormulas, ScImportAsciiCall eCall )
 {
     Sequence<Any>aValues;
     const Any *pProperties;
@@ -228,6 +239,9 @@ static void lcl_LoadSeparators( OUString& rFieldSeparators, OUString& rTextSepar
     if( pProperties[ CSVIO_FixedWidth ].hasValue() )
         rFixedWidth = ScUnoHelpFunctions::GetBoolFromAny( pProperties[ CSVIO_FixedWidth ] );
 
+    if( pProperties[ CSVIO_EvaluateFormulas ].hasValue() )
+        rEvaluateFormulas = ScUnoHelpFunctions::GetBoolFromAny( pProperties[ CSVIO_EvaluateFormulas ] );
+
     if (eCall != SC_TEXTTOCOLUMNS)
     {
         if( pProperties[ CSVIO_FromRow ].hasValue() )
@@ -257,7 +271,8 @@ static void lcl_LoadSeparators( OUString& rFieldSeparators, OUString& rTextSepar
 static void lcl_SaveSeparators(
     const OUString& sFieldSeparators, const OUString& sTextSeparators, bool bMergeDelimiters, bool bQuotedAsText,
     bool bDetectSpecialNum, bool bFixedWidth, sal_Int32 nFromRow,
-    sal_Int32 nCharSet, sal_Int32 nLanguage, bool bSkipEmptyCells, bool bRemoveSpace, ScImportAsciiCall eCall )
+    sal_Int32 nCharSet, sal_Int32 nLanguage, bool bSkipEmptyCells, bool bRemoveSpace, bool bEvaluateFormulas,
+    ScImportAsciiCall eCall )
 {
     Sequence<Any> aValues;
     Any *pProperties;
@@ -273,6 +288,7 @@ static void lcl_SaveSeparators(
     pProperties[ CSVIO_Separators ] <<= sFieldSeparators;
     pProperties[ CSVIO_TextSeparators ] <<= sTextSeparators;
     pProperties[ CSVIO_FixedWidth ] <<= bFixedWidth;
+    pProperties[ CSVIO_EvaluateFormulas ] <<= bEvaluateFormulas;
     if (eCall != SC_TEXTTOCOLUMNS)
     {
         pProperties[ CSVIO_FromRow ] <<= nFromRow;
@@ -322,6 +338,7 @@ ScImportAsciiDlg::ScImportAsciiDlg(weld::Window* pParent, const OUString& aDatNa
     , mxCbTextSep(m_xBuilder->weld_combo_box("textdelimiter"))
     , mxCkbQuotedAsText(m_xBuilder->weld_check_button("quotedfieldastext"))
     , mxCkbDetectNumber(m_xBuilder->weld_check_button("detectspecialnumbers"))
+    , mxCkbEvaluateFormulas(m_xBuilder->weld_check_button("evaluateformulas"))
     , mxCkbSkipEmptyCells(m_xBuilder->weld_check_button("skipemptycells"))
     , mxFtType(m_xBuilder->weld_label("textcolumntype"))
     , mxLbType(m_xBuilder->weld_combo_box("columntype"))
@@ -355,6 +372,7 @@ ScImportAsciiDlg::ScImportAsciiDlg(weld::Window* pParent, const OUString& aDatNa
     bool bFixedWidth = false;
     bool bQuotedFieldAsText = false;
     bool bDetectSpecialNum = true;
+    bool bEvaluateFormulas = (meCall != SC_IMPORTFILE);
     bool bSkipEmptyCells = true;
     bool bRemoveSpace = false;
     sal_Int32 nFromRow = 1;
@@ -362,7 +380,7 @@ ScImportAsciiDlg::ScImportAsciiDlg(weld::Window* pParent, const OUString& aDatNa
     sal_Int32 nLanguage = 0;
     lcl_LoadSeparators (sFieldSeparators, sTextSeparators, bMergeDelimiters,
                          bQuotedFieldAsText, bDetectSpecialNum, bFixedWidth, nFromRow,
-                         nCharSet, nLanguage, bSkipEmptyCells, bRemoveSpace, meCall);
+                         nCharSet, nLanguage, bSkipEmptyCells, bRemoveSpace, bEvaluateFormulas, meCall);
     // load from saved settings
     maFieldSeparators = sFieldSeparators;
 
@@ -374,6 +392,8 @@ ScImportAsciiDlg::ScImportAsciiDlg(weld::Window* pParent, const OUString& aDatNa
         mxCkbRemoveSpace->set_active(true);
     if (bDetectSpecialNum)
         mxCkbDetectNumber->set_active(true);
+    if (bEvaluateFormulas)
+        mxCkbEvaluateFormulas->set_active(true);
     if (bSkipEmptyCells)
         mxCkbSkipEmptyCells->set_active(true);
     if (bFixedWidth && !bIsTSV)
@@ -480,6 +500,7 @@ ScImportAsciiDlg::ScImportAsciiDlg(weld::Window* pParent, const OUString& aDatNa
     mxCkbAsOnce->connect_toggled( aSeparatorClickHdl );
     mxCkbQuotedAsText->connect_toggled( aSeparatorClickHdl );
     mxCkbDetectNumber->connect_toggled( aSeparatorClickHdl );
+    mxCkbEvaluateFormulas->connect_toggled( aSeparatorClickHdl );
     mxCkbSkipEmptyCells->connect_toggled( aSeparatorClickHdl );
     mxCkbSpace->connect_toggled( aSeparatorClickHdl );
     mxCkbRemoveSpace->connect_toggled( aSeparatorClickHdl );
@@ -662,6 +683,7 @@ void ScImportAsciiDlg::GetOptions( ScAsciiOptions& rOpt )
 
     rOpt.SetQuotedAsText(mxCkbQuotedAsText->get_active());
     rOpt.SetDetectSpecialNumber(mxCkbDetectNumber->get_active());
+    rOpt.SetEvaluateFormulas(mxCkbEvaluateFormulas->get_active());
     rOpt.SetSkipEmptyCells(mxCkbSkipEmptyCells->get_active());
 }
 
@@ -673,7 +695,8 @@ void ScImportAsciiDlg::SaveParameters()
                      mxNfRow->get_value(),
                      mxLbCharSet->get_active(),
                      static_cast<sal_uInt16>(mxLbCustomLang->get_active_id()),
-                     mxCkbSkipEmptyCells->get_active(), mxCkbRemoveSpace->get_active(), meCall );
+                     mxCkbSkipEmptyCells->get_active(), mxCkbRemoveSpace->get_active(),
+                     mxCkbEvaluateFormulas->get_active(), meCall );
 }
 
 void ScImportAsciiDlg::SetSeparators( sal_Unicode cSep )
diff --git a/sc/source/ui/docshell/impex.cxx b/sc/source/ui/docshell/impex.cxx
index 905d758cc88c..c5102cf483b9 100644
--- a/sc/source/ui/docshell/impex.cxx
+++ b/sc/source/ui/docshell/impex.cxx
@@ -1017,7 +1017,7 @@ bool ScImportExport::Text2Doc( SvStream& rStrm )
 static bool lcl_PutString(
     ScDocumentImport& rDocImport, bool bUseDocImport,
     SCCOL nCol, SCROW nRow, SCTAB nTab, const OUString& rStr, sal_uInt8 nColFormat,
-    SvNumberFormatter* pFormatter, bool bDetectNumFormat, bool bSkipEmptyCells,
+    SvNumberFormatter* pFormatter, bool bDetectNumFormat, bool bEvaluateFormulas, bool bSkipEmptyCells,
     const ::utl::TransliterationWrapper& rTransliteration, CalendarWrapper& rCalendar,
     const ::utl::TransliterationWrapper* pSecondTransliteration, CalendarWrapper* pSecondCalendar )
 {
@@ -1037,11 +1037,12 @@ static bool lcl_PutString(
         return false;
     }
 
-    if ( nColFormat == SC_COL_TEXT )
+    const bool bForceFormulaText = (!bEvaluateFormulas && rStr[0] == '=');
+    if (nColFormat == SC_COL_TEXT || bForceFormulaText)
     {
         double fDummy;
         sal_uInt32 nIndex = 0;
-        if (pFormatter->IsNumberFormat(rStr, nIndex, fDummy))
+        if (bForceFormulaText || pFormatter->IsNumberFormat(rStr, nIndex, fDummy))
         {
             // Set the format of this cell to Text.
             sal_uInt32 nFormat = pFormatter->GetStandardFormat(SvNumFormatType::TEXT);
@@ -1413,6 +1414,7 @@ bool ScImportExport::ExtText2Doc( SvStream& rStrm )
     LanguageType eDocLang = pExtOptions->GetLanguage();
     SvNumberFormatter aNumFormatter( comphelper::getProcessComponentContext(), eDocLang);
     bool bDetectNumFormat = pExtOptions->IsDetectSpecialNumber();
+    bool bEvaluateFormulas = pExtOptions->IsEvaluateFormulas();
     bool bSkipEmptyCells = pExtOptions->IsSkipEmptyCells();
 
     // For date recognition
@@ -1511,7 +1513,8 @@ bool ScImportExport::ExtText2Doc( SvStream& rStrm )
 
                             bMultiLine |= lcl_PutString(
                                 aDocImport, !mbOverwriting, nCol, nRow, nTab, aCell, nFmt,
-                                &aNumFormatter, bDetectNumFormat, bSkipEmptyCells, aTransliteration, aCalendar,
+                                &aNumFormatter, bDetectNumFormat, bEvaluateFormulas, bSkipEmptyCells,
+                                aTransliteration, aCalendar,
                                 pEnglishTransliteration.get(), pEnglishCalendar.get());
 
                             nStartIdx = nNextIdx;
@@ -1556,8 +1559,9 @@ bool ScImportExport::ExtText2Doc( SvStream& rStrm )
 
                             bMultiLine |= lcl_PutString(
                                 aDocImport, !mbOverwriting, nCol, nRow, nTab, aCell, nFmt,
-                                &aNumFormatter, bDetectNumFormat, bSkipEmptyCells, aTransliteration,
-                                aCalendar, pEnglishTransliteration.get(), pEnglishCalendar.get());
+                                &aNumFormatter, bDetectNumFormat, bEvaluateFormulas, bSkipEmptyCells,
+                                aTransliteration, aCalendar,
+                                pEnglishTransliteration.get(), pEnglishCalendar.get());
                         }
                         ++nCol;
                     }
diff --git a/sc/source/ui/inc/asciiopt.hxx b/sc/source/ui/inc/asciiopt.hxx
index 7e438cb1fa98..86e83ee89f78 100644
--- a/sc/source/ui/inc/asciiopt.hxx
+++ b/sc/source/ui/inc/asciiopt.hxx
@@ -33,6 +33,7 @@ private:
     bool        bRemoveSpace;
     bool        bQuotedFieldAsText;
     bool        bDetectSpecialNumber;
+    bool        bEvaluateFormulas;
     bool        bSkipEmptyCells;
     bool        bSaveAsShown;
     bool        bSaveFormulas;
@@ -58,6 +59,7 @@ public:
     bool                IsRemoveSpace() const   { return bRemoveSpace; }
     bool                IsQuotedAsText() const  { return bQuotedFieldAsText; }
     bool                IsDetectSpecialNumber() const { return bDetectSpecialNumber; }
+    bool                IsEvaluateFormulas() const    { return bEvaluateFormulas; }
     bool                IsSkipEmptyCells() const      { return bSkipEmptyCells; }
     sal_Unicode         GetTextSep() const      { return cTextSep; }
     bool                IsFixedLen() const      { return bFixedLen; }
@@ -75,6 +77,7 @@ public:
     void    SetRemoveSpace( bool bSet )         { bRemoveSpace = bSet; }
     void    SetQuotedAsText(bool bSet)          { bQuotedFieldAsText = bSet; }
     void    SetDetectSpecialNumber(bool bSet)   { bDetectSpecialNumber = bSet; }
+    void    SetEvaluateFormulas(bool bSet)      { bEvaluateFormulas = bSet; }
     void    SetSkipEmptyCells(bool bSet)        { bSkipEmptyCells = bSet; }
     void    SetTextSep( sal_Unicode c )         { cTextSep = c; }
     void    SetStartRow( sal_Int32 nRow)        { nStartRow= nRow; }
diff --git a/sc/source/ui/inc/imoptdlg.hxx b/sc/source/ui/inc/imoptdlg.hxx
index 935f271b0c5c..fad5b4bf15f7 100644
--- a/sc/source/ui/inc/imoptdlg.hxx
+++ b/sc/source/ui/inc/imoptdlg.hxx
@@ -32,7 +32,7 @@ public:
             : nFieldSepCode(nFieldSep), nTextSepCode(nTextSep),
             bFixedWidth(false), bSaveAsShown(false), bQuoteAllText(false),
             bSaveNumberAsSuch(true), bSaveFormulas(false), bRemoveSpace(false),
-            nSheetToExport(0)
+            bEvaluateFormulas(true), nSheetToExport(0)
         { SetTextEncoding( nEnc ); }
 
     ScImportOptions& operator=( const ScImportOptions& rCpy ) = default;
@@ -51,6 +51,7 @@ public:
     bool        bSaveNumberAsSuch;
     bool        bSaveFormulas;
     bool        bRemoveSpace;
+    bool        bEvaluateFormulas;
     // "0" for 'current sheet', "-1" for all sheets (each to a separate file),
     // or 1-based specific sheet number (to a separate file).
     sal_Int32   nSheetToExport;
diff --git a/sc/source/ui/inc/scuiasciiopt.hxx b/sc/source/ui/inc/scuiasciiopt.hxx
index 2bb7e23252b1..ee7f512b3548 100644
--- a/sc/source/ui/inc/scuiasciiopt.hxx
+++ b/sc/source/ui/inc/scuiasciiopt.hxx
@@ -71,6 +71,7 @@ class ScImportAsciiDlg : public weld::GenericDialogController
 
     std::unique_ptr<weld::CheckButton> mxCkbQuotedAsText;
     std::unique_ptr<weld::CheckButton> mxCkbDetectNumber;
+    std::unique_ptr<weld::CheckButton> mxCkbEvaluateFormulas;
     std::unique_ptr<weld::CheckButton> mxCkbSkipEmptyCells;
 
     std::unique_ptr<weld::Label> mxFtType;