From 4c29c2bfab18b25229e0bc71014fd2ca42ec5bff Mon Sep 17 00:00:00 2001 From: Eike Rathke Date: Tue, 31 Aug 2021 00:10:40 +0200 Subject: 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 Tested-by: Jenkins --- sc/source/ui/dbgui/asciiopt.cxx | 28 +++++--- sc/source/ui/dbgui/imoptdlg.cxx | 7 +- sc/source/ui/dbgui/scuiasciiopt.cxx | 37 ++++++++-- sc/source/ui/docshell/impex.cxx | 16 +++-- sc/source/ui/inc/asciiopt.hxx | 3 + sc/source/ui/inc/imoptdlg.hxx | 3 +- sc/source/ui/inc/scuiasciiopt.hxx | 1 + sc/uiconfig/scalc/ui/textimportcsv.ui | 125 +++++++++++++++++++++------------- 8 files changed, 148 insertions(+), 72 deletions(-) (limited to 'sc') 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(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 CSVImportOptionNames = { "MergeDelimiters", @@ -82,6 +90,7 @@ const ::std::vector CSVImportOptionNames = "TextSeparators", "FixedWidth", "RemoveSpace", + "EvaluateFormulas", "FromRow", "CharSet", "QuotedFieldAsText", @@ -164,16 +173,16 @@ static void lcl_CreatePropertiesNames ( OUString& rSepPath, Sequence& { 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& 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& 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 ) { SequenceaValues; 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 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(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 mxCkbQuotedAsText; std::unique_ptr mxCkbDetectNumber; + std::unique_ptr mxCkbEvaluateFormulas; std::unique_ptr mxCkbSkipEmptyCells; std::unique_ptr mxFtType; diff --git a/sc/uiconfig/scalc/ui/textimportcsv.ui b/sc/uiconfig/scalc/ui/textimportcsv.ui index 1a4daef1efd6..1ffbc9868736 100644 --- a/sc/uiconfig/scalc/ui/textimportcsv.ui +++ b/sc/uiconfig/scalc/ui/textimportcsv.ui @@ -579,64 +579,95 @@ 6 True - - F_ormat quoted field as text + + True - True - False - True - True - - - When this option is enabled, fields or cells whose values are quoted in their entirety (the first and last characters of the value equal the text delimiter) are imported as text. + False + 6 + 12 + + + F_ormat quoted field as text + True + True + False + True + True + + + When this option is enabled, fields or cells whose values are quoted in their entirety (the first and last characters of the value equal the text delimiter) are imported as text. + + + + 0 + 0 + - - - False - True - 0 - - - - - Detect special _numbers - True - True - False - True - True - - - When this option is enabled, Calc will automatically detect all number formats, including special number formats such as dates, time, and scientific notation. + + + Detect special _numbers + True + True + False + True + True + + + When this option is enabled, Calc will automatically detect all number formats, including special number formats such as dates, time, and scientific notation. + + + + + 1 + 0 + + + + + E_valuate formulas + True + True + False + If enabled, blank cells in source will not override the target. + True + True + + + When this option is enabled, cell content starting with an '=' equal sign is evaluated as formula expression. Otherwise, content is imported as text. + + + + 0 + 1 + - - - False - True - 1 - - - - - S_kip empty cells - True - True - False - If enabled, blank cells in source will not override the target. - True - True - - - When this option is enabled, Calc preserves previous content of cells when pasting empty ones. Otherwise, Calc deletes content of previous cells. + + + S_kip empty cells + True + True + False + If enabled, blank cells in source will not override the target. + True + True + + + When this option is enabled, Calc preserves previous content of cells when pasting empty ones. Otherwise, Calc deletes content of previous cells. + + + + 1 + 1 + False True - 2 + 0 -- cgit