sc/inc/dbdata.hxx | 20 - sc/inc/document.hxx | 4 sc/inc/subtotalparam.hxx | 23 + sc/inc/table.hxx | 4 sc/source/core/data/documen3.cxx | 8 sc/source/core/data/subtotalparam.cxx | 69 +++- sc/source/core/data/table3.cxx | 56 +-- sc/source/core/tool/dbdata.cxx | 424 +++++++++++++++++++++------ sc/source/filter/excel/xedbdata.cxx | 34 +- sc/source/filter/inc/tablecolumnsbuffer.hxx | 15 sc/source/filter/inc/tablecolumnscontext.hxx | 1 sc/source/filter/oox/tablecolumnsbuffer.cxx | 52 ++- sc/source/filter/oox/tablecolumnscontext.cxx | 23 + sc/source/ui/docshell/dbdocfun.cxx | 12 sc/source/ui/view/cellsh2.cxx | 9 sc/source/ui/view/dbfunc3.cxx | 2 sc/source/ui/view/gridwin.cxx | 8 sc/source/ui/view/tableshell.cxx | 9 18 files changed, 591 insertions(+), 182 deletions(-)
New commits: commit 097d9dd4d888d1f9ac6ed719eb5685e359ae42dd Author: Balazs Varga <[email protected]> AuthorDate: Thu Oct 30 17:49:09 2025 +0100 Commit: Balazs Varga <[email protected]> CommitDate: Thu Jan 29 09:29:01 2026 +0100 Disable original SubTotal functionality if table style is applied on the Dbrange (same as MSO). Change-Id: I9ad8d3f31ea8e417e7c40e930659c0bdbc55d9f7 Reviewed-on: https://gerrit.libreoffice.org/c/core/+/193226 Tested-by: Balazs Varga <[email protected]> Reviewed-by: Balazs Varga <[email protected]> Reviewed-on: https://gerrit.libreoffice.org/c/core/+/193683 Tested-by: Andras Timar <[email protected]> Reviewed-by: Andras Timar <[email protected]> Reviewed-on: https://gerrit.libreoffice.org/c/core/+/196751 Reviewed-on: https://gerrit.libreoffice.org/c/core/+/197977 Tested-by: Jenkins diff --git a/sc/source/ui/view/cellsh2.cxx b/sc/source/ui/view/cellsh2.cxx index 57a40c6d00de..63ee51ccc376 100644 --- a/sc/source/ui/view/cellsh2.cxx +++ b/sc/source/ui/view/cellsh2.cxx @@ -1213,6 +1213,15 @@ void ScCellShell::GetDBState( SfxItemSet& rSet ) rSet.DisableItem(nWhich); } } + else if (nWhich == SCITEM_SUBTDATA) + { + // Disable if table style is applied (same as MSO) + ScDBData* pDBData = pTabViewShell->GetDBData(false, SC_DB_OLD); + if (pDBData && pDBData->GetTableStyleInfo()) + { + rSet.DisableItem(nWhich); + } + } } } break; commit 14ad80426102220ff1a9a3cc92fd0a8785afa470 Author: Balazs Varga <[email protected]> AuthorDate: Wed Oct 29 14:54:20 2025 +0100 Commit: Balazs Varga <[email protected]> CommitDate: Thu Jan 29 09:28:52 2026 +0100 Table Style improvements: Handle custom functions in Total row of table styles Also rework and better (more dynamic) handling of Total row. Also fix the additional ooxml import problems around Total row. cherry-pick from: bca5864a3245021601991e9be8ff6401ffd812b5 Include fix unit test builds failer because of missing function parameteres. Include: ooxml export of Total Row attributes. XML_totalsRowLabel, XML_totalsRowFunction, XML_totalsRowFormula and XML_totalsRowShown. cherry-pick from: dbe65f04327924e0d3c7bcefab5bbfba07687033 Change-Id: I894c309362cfaf525ebbf47e7f49bc67b8da8495 Reviewed-on: https://gerrit.libreoffice.org/c/core/+/193205 Reviewed-by: Balazs Varga <[email protected]> Tested-by: Balazs Varga <[email protected]> Reviewed-on: https://gerrit.libreoffice.org/c/core/+/193681 Reviewed-by: Andras Timar <[email protected]> Tested-by: Andras Timar <[email protected]> Reviewed-on: https://gerrit.libreoffice.org/c/core/+/196749 Reviewed-on: https://gerrit.libreoffice.org/c/core/+/197975 Tested-by: Jenkins diff --git a/sc/inc/dbdata.hxx b/sc/inc/dbdata.hxx index 7c7ae6d81bda..3acae421796b 100644 --- a/sc/inc/dbdata.hxx +++ b/sc/inc/dbdata.hxx @@ -36,6 +36,7 @@ class ScDocument; struct ScSortParam; struct ScQueryParam; struct ScSubTotalParam; +class ScTokenArray; class SC_DLLPUBLIC ScDatabaseSettingItem final : public SfxPoolItem { @@ -83,6 +84,7 @@ struct TableColumnAttributes { std::optional<OUString> maTotalsRowLabel = std::nullopt; std::optional<OUString> maTotalsFunction = std::nullopt; + std::optional<OUString> maCustomFunction = std::nullopt; }; // xmlColumnPr attributes @@ -175,7 +177,6 @@ private: bool bModified; ///< is set/cleared for/by(?) UpdateReference ::std::vector< OUString > maTableColumnNames; ///< names of table columns - ::std::vector< TableColumnAttributes > maTableColumnAttributes; ///< attributes of table columns ::std::vector< TableColumnModel > maTableColumnModel; bool mbTableColumnNamesDirty; SCSIZE nFilteredRowCount; @@ -233,8 +234,6 @@ public: void EndTableColumnNamesListener(); SC_DLLPUBLIC void SetTableColumnNames( ::std::vector< OUString >&& rNames ); SC_DLLPUBLIC const ::std::vector< OUString >& GetTableColumnNames() const { return maTableColumnNames; } - SC_DLLPUBLIC void SetTableColumnAttributes( ::std::vector< TableColumnAttributes >&& rAttributes ); - SC_DLLPUBLIC const ::std::vector< TableColumnAttributes >& GetTableColumnAttributes() const { return maTableColumnAttributes; } SC_DLLPUBLIC void SetTableColumnModel( TableColumnModel& rModel ) { maTableColumnModel.push_back(std::move(rModel)); @@ -279,7 +278,17 @@ public: SC_DLLPUBLIC void GetSubTotalParam(ScSubTotalParam& rSubTotalParam) const; SC_DLLPUBLIC void SetSubTotalParam(const ScSubTotalParam& rSubTotalParam); - SC_DLLPUBLIC void CreateSubTotalParam(ScSubTotalParam& rSubTotalParam) const; + + // Total row param handling for Table Styles + SC_DLLPUBLIC void ImportTotalRowParam(ScSubTotalParam& rSubTotalParam, + const std::vector<TableColumnAttributes>& rAttributesVector, + formula::FormulaGrammar::Grammar eGrammar) const; + SC_DLLPUBLIC void CreateTotalRowParam(ScSubTotalParam& rSubTotalParam) const; + + SC_DLLPUBLIC std::vector<TableColumnAttributes> + GetTotalRowAttributes(formula::FormulaGrammar::Grammar eGrammar) const; + + OUString GetSimpleSubTotalFunction(const ScTokenArray* pTokens, SCCOL nCol, SCROW nHeaderRow) const; void GetImportParam(ScImportParam& rImportParam) const; void SetImportParam(const ScImportParam& rImportParam); @@ -315,10 +324,11 @@ public: SC_DLLPUBLIC const ScTableStyleParam* GetTableStyleInfo() const; static ScSubTotalFunc GetSubTotalFuncFromString(std::u16string_view sFunction); + static OUString GetStringFromSubTotalFunc(ScSubTotalFunc eFunc); private: - void AdjustTableColumnAttributes( UpdateRefMode eUpdateRefMode, SCCOL nDx, SCCOL nCol1, + void AdjustTableColumnNames( UpdateRefMode eUpdateRefMode, SCCOL nDx, SCCOL nCol1, SCCOL nOldCol1, SCCOL nOldCol2, SCCOL nNewCol1, SCCOL nNewCol2 ); void InvalidateTableColumnNames( bool bSwapToEmptyNames ); }; diff --git a/sc/inc/document.hxx b/sc/inc/document.hxx index 539431ead370..1fff76c3b0cf 100644 --- a/sc/inc/document.hxx +++ b/sc/inc/document.hxx @@ -1199,7 +1199,7 @@ public: bool DoSubTotals( SCTAB nTab, ScSubTotalParam& rParam ); void RemoveSubTotals( SCTAB nTab, ScSubTotalParam& rParam ); // Table SubTotals - bool DoTableSubTotals( SCTAB nTab, ScSubTotalParam& rParam, sal_uInt16 nIndex ); + bool DoTableSubTotals( SCTAB nTab, ScSubTotalParam& rParam ); void RemoveTableSubTotals( SCTAB nTab, ScSubTotalParam& rParam, const ScSubTotalParam& rOldParam ); bool TestRemoveSubTotals( SCTAB nTab, const ScSubTotalParam& rParam ); @@ -2253,7 +2253,7 @@ public: void Reorder( const sc::ReorderParam& rParam ); void PrepareQuery( SCTAB nTab, ScQueryParam& rQueryParam ); - SCSIZE Query( SCTAB nTab, const ScQueryParam& rQueryParam, bool bKeepSub ); + SCSIZE Query( SCTAB nTab, const ScQueryParam& rQueryParam, bool bKeepSub, bool bKeepTotals = false ); SC_DLLPUBLIC bool CreateQueryParam( const ScRange& rRange, ScQueryParam& rQueryParam ); OUString GetUpperCellString(SCCOL nCol, SCROW nRow, SCTAB nTab); diff --git a/sc/inc/subtotalparam.hxx b/sc/inc/subtotalparam.hxx index a990481be961..fcfc8ef04244 100644 --- a/sc/inc/subtotalparam.hxx +++ b/sc/inc/subtotalparam.hxx @@ -10,6 +10,7 @@ #pragma once #include "global.hxx" +#include "tokenarray.hxx" #include <memory> #include <span> @@ -40,13 +41,16 @@ struct SC_DLLPUBLIC ScSubTotalParam bool bActive = false; ///< active groups SCCOL nField = 0; ///< associated field SCCOL nSubTotals = 0; ///< number of SubTotals + SCCOL nCustFuncs = 0; ///< number of Custom Functions SCCOL nSubLabels = 0; ///< number of SubLabels using Pair = std::pair<SCCOL, ScSubTotalFunc>; + using FuncPair = std::pair<SCCOL, std::unique_ptr<ScTokenArray>>; using LabelPair = std::pair<SCCOL, rtl::OUString>; // array of columns to be calculated, and associated functions std::unique_ptr<Pair[]> pSubTotals; - std::unique_ptr<LabelPair[]> pSubLabels; + std::unique_ptr<FuncPair[]> pCustFuncs; // custom functions + std::unique_ptr<LabelPair[]> pSubLabels; // Total labels SubtotalGroup() = default; SubtotalGroup(const SubtotalGroup& r); @@ -55,8 +59,10 @@ struct SC_DLLPUBLIC ScSubTotalParam bool operator==(const SubtotalGroup& r) const; void AllocSubTotals(SCCOL n); + void AllocCustFuncs(SCCOL n); void AllocSubLabels(SCCOL n); void SetSubtotals(const css::uno::Sequence<css::sheet::SubTotalColumn>& seq); + //void SetCustFuncs(const css::uno::Sequence<css::sheet::SubTotalColumn>& seq); //void SetSublabels(const css::uno::Sequence<css::sheet::SubTotalColumn>& seq); // Totals @@ -65,6 +71,12 @@ struct SC_DLLPUBLIC ScSubTotalParam SCCOL& col(SCCOL n) { return subtotals()[n].first; } SCCOL col(SCCOL n) const { return subtotals()[n].first; } ScSubTotalFunc func(SCCOL n) const { return subtotals()[n].second; } + // Total Functions + std::span<FuncPair> custfuncs() { return std::span(pCustFuncs.get(), nCustFuncs); } + std::span<const FuncPair> custfuncs() const { return std::span(pCustFuncs.get(), nCustFuncs); } + SCCOL& colcust(SCCOL n) { return custfuncs()[n].first; } + SCCOL colcust(SCCOL n) const { return custfuncs()[n].first; } + ScTokenArray* custToken(SCCOL n) const { return custfuncs()[n].second.get(); } // Labels std::span<LabelPair> sublabels() { return std::span(pSubLabels.get(), nSubLabels); } std::span<const LabelPair> sublabels() const { return std::span(pSubLabels.get(), nSubLabels); } @@ -77,15 +89,20 @@ struct SC_DLLPUBLIC ScSubTotalParam ScSubTotalParam() = default; ScSubTotalParam(const ScSubTotalParam&) = default; + ScSubTotalParam(ScSubTotalParam&&) noexcept = default; + ScSubTotalParam& operator=(ScSubTotalParam&&) noexcept = default; + ScSubTotalParam& operator=(const ScSubTotalParam&) = default; inline bool operator==(const ScSubTotalParam&) const = default; void SetSubTotals( sal_uInt16 nGroup, const SCCOL* ptrSubTotals, const ScSubTotalFunc* ptrFunctions, sal_uInt16 nCount ); + void SetCustFuncs( sal_uInt16 nGroup, + std::vector<std::pair<SCCOL, std::unique_ptr<ScTokenArray>>>& rColFuncs, + sal_uInt16 nCount ); void SetSubLabels( sal_uInt16 nGroup, - const SCCOL* ptrSubLabels, - const rtl::OUString* ptrSubNames, + std::vector<std::pair<SCCOL, rtl::OUString>>& rColLabels, sal_uInt16 nCount ); }; diff --git a/sc/inc/table.hxx b/sc/inc/table.hxx index 20790134159a..5c3586990923 100644 --- a/sc/inc/table.hxx +++ b/sc/inc/table.hxx @@ -328,7 +328,7 @@ public: void RemoveSubTotals( ScSubTotalParam& rParam ); void RemoveSimpleSubTotals( ScSubTotalParam& rParam, const ScSubTotalParam& rOldParam ); bool DoSubTotals( ScSubTotalParam& rParam ); - bool DoSimpleSubTotals( ScSubTotalParam& rParam, sal_uInt16 nIndex ); + bool DoSimpleSubTotals( ScSubTotalParam& rParam ); const ScSheetEvents* GetSheetEvents() const { return pSheetEvents.get(); } void SetSheetEvents( std::unique_ptr<ScSheetEvents> pNew ); @@ -1033,7 +1033,7 @@ public: // For ValidQuery() see ScQueryEvalutor class. void TopTenQuery( ScQueryParam& ); void PrepareQuery( ScQueryParam& rQueryParam ); - SCSIZE Query(const ScQueryParam& rQueryParam, bool bKeepSub); + SCSIZE Query(const ScQueryParam& rQueryParam, bool bKeepSub, bool bKeepTotals = false); bool CreateQueryParam(SCCOL nCol1, SCROW nRow1, SCCOL nCol2, SCROW nRow2, ScQueryParam& rQueryParam); void GetFilterEntries(SCCOL nCol, SCROW nRow1, SCROW nRow2, ScFilterEntries& rFilterEntries, bool bFiltering = false); diff --git a/sc/source/core/data/documen3.cxx b/sc/source/core/data/documen3.cxx index 015f29499656..98c4531e9c08 100644 --- a/sc/source/core/data/documen3.cxx +++ b/sc/source/core/data/documen3.cxx @@ -794,10 +794,10 @@ void ScDocument::RemoveTableSubTotals( SCTAB nTab, ScSubTotalParam& rParam, cons pTable->RemoveSimpleSubTotals( rParam, rOldParam ); } -bool ScDocument::DoTableSubTotals( SCTAB nTab, ScSubTotalParam& rParam, sal_uInt16 nIndex ) +bool ScDocument::DoTableSubTotals( SCTAB nTab, ScSubTotalParam& rParam ) { ScTable* pTable = FetchTable(nTab); - return pTable && pTable->DoSimpleSubTotals(rParam, nIndex); + return pTable && pTable->DoSimpleSubTotals(rParam); } bool ScDocument::HasSubTotalCells( const ScRange& rRange ) @@ -1484,10 +1484,10 @@ void ScDocument::PrepareQuery( SCTAB nTab, ScQueryParam& rQueryParam ) } } -SCSIZE ScDocument::Query(SCTAB nTab, const ScQueryParam& rQueryParam, bool bKeepSub) +SCSIZE ScDocument::Query(SCTAB nTab, const ScQueryParam& rQueryParam, bool bKeepSub, bool bKeepTotals) { if (ScTable* pTable = FetchTable(nTab)) - return pTable->Query(rQueryParam, bKeepSub); + return pTable->Query(rQueryParam, bKeepSub, bKeepTotals); OSL_FAIL("missing tab"); return 0; diff --git a/sc/source/core/data/subtotalparam.cxx b/sc/source/core/data/subtotalparam.cxx index 8b44a5df5a8b..be9a95fc6767 100644 --- a/sc/source/core/data/subtotalparam.cxx +++ b/sc/source/core/data/subtotalparam.cxx @@ -28,6 +28,19 @@ ScSubTotalParam::SubtotalGroup::SubtotalGroup(const SubtotalGroup& r) std::copy_n(r.pSubTotals.get(), r.nSubTotals, pSubTotals.get()); } + if (r.nCustFuncs > 0) + { + assert(r.pCustFuncs); + AllocCustFuncs(r.nCustFuncs); + for (SCCOL i = 0; i < r.nCustFuncs; ++i) + { + if (r.pCustFuncs[i].second) + pCustFuncs[i] = std::make_pair(r.pCustFuncs[i].first, r.pCustFuncs[i].second->Clone()); + else + pCustFuncs[i] = std::make_pair(r.pCustFuncs[i].first, nullptr); + } + } + if (r.nSubLabels > 0) { assert(r.pSubLabels); @@ -48,6 +61,19 @@ ScSubTotalParam::SubtotalGroup& ScSubTotalParam::SubtotalGroup::operator=(const std::copy_n(r.pSubTotals.get(), r.nSubTotals, pSubTotals.get()); } + AllocCustFuncs(r.nCustFuncs); + if (r.nCustFuncs > 0) + { + assert(r.pCustFuncs); + for (SCCOL i = 0; i < r.nCustFuncs; ++i) + { + if (r.pCustFuncs[i].second) + pCustFuncs[i] = std::make_pair(r.pCustFuncs[i].first, r.pCustFuncs[i].second->Clone()); + else + pCustFuncs[i] = std::make_pair(r.pCustFuncs[i].first, nullptr); + } + } + AllocSubLabels(r.nSubLabels); if (r.nSubLabels > 0) { @@ -63,6 +89,8 @@ bool ScSubTotalParam::SubtotalGroup::operator==(const SubtotalGroup& r) const return bActive == r.bActive && nField == r.nField && nSubTotals == r.nSubTotals && nSubLabels == r.nSubLabels && (!nSubTotals || std::equal(pSubTotals.get(), pSubTotals.get() + nSubTotals, r.pSubTotals.get())) + && (!nCustFuncs + || std::equal(pCustFuncs.get(), pCustFuncs.get() + nCustFuncs, r.pCustFuncs.get())) && (!nSubLabels || std::equal(pSubLabels.get(), pSubLabels.get() + nSubLabels, r.pSubLabels.get())); } @@ -76,6 +104,15 @@ void ScSubTotalParam::SubtotalGroup::AllocSubTotals(SCCOL n) } } +void ScSubTotalParam::SubtotalGroup::AllocCustFuncs(SCCOL n) +{ + if (nCustFuncs != n) + { + nCustFuncs = std::max(n, SCCOL(0)); + pCustFuncs.reset(nCustFuncs ? new std::pair<SCCOL, std::unique_ptr<ScTokenArray>>[nCustFuncs] : nullptr); + } +} + void ScSubTotalParam::SubtotalGroup::AllocSubLabels(SCCOL n) { if (nSubLabels != n) @@ -93,6 +130,11 @@ void ScSubTotalParam::SubtotalGroup::SetSubtotals(const css::uno::Sequence<css:: ScDPUtil::toSubTotalFunc(static_cast<ScGeneralFunction>(seq[i].Function)) }; } +//void ScSubTotalParam::SubtotalGroup::SetCustFuncs(const css::uno::Sequence<css::sheet::SubTotalColumn>& /*seq*/) +//{ +// TODO UNO::API: SubTotalColumn has no token array member +//} + //void ScSubTotalParam::SubtotalGroup::SetSublabels(const css::uno::Sequence<css::sheet::SubTotalColumn>& /*seq*/) //{ // TODO UNO::API: SubTotalColumn has no LabelName member @@ -124,26 +166,33 @@ void ScSubTotalParam::SetSubTotals( sal_uInt16 nGroup, aGroups[nGroup].pSubTotals[i] = { ptrSubTotals[i], ptrFunctions[i] }; } +void ScSubTotalParam::SetCustFuncs(sal_uInt16 nGroup, + std::vector<std::pair<SCCOL, std::unique_ptr<ScTokenArray>>>& rColFuncs, + sal_uInt16 nCount ) +{ + OSL_ENSURE((nGroup <= MAXSUBTOTAL), "ScSubTotalParam::SetCustFuncs(): nGroup > MAXSUBTOTAL!"); + OSL_ENSURE((nCount > 0), "ScSubTotalParam::SetCustFuncs(): nCount == 0!"); + if (!(nCount > 0 && nGroup <= MAXSUBTOTAL)) + return; + + aGroups[nGroup].AllocCustFuncs(nCount); + for (sal_uInt16 i = 0; i < nCount; i++) + aGroups[nGroup].pCustFuncs[i] = std::make_pair(rColFuncs[i].first, std::move(rColFuncs[i].second)); +} + void ScSubTotalParam::SetSubLabels(sal_uInt16 nGroup, - const SCCOL* ptrSubLabels, - const rtl::OUString* ptrSubNames, + std::vector<std::pair<SCCOL, rtl::OUString>>& rColLabels, sal_uInt16 nCount ) { OSL_ENSURE((nGroup <= MAXSUBTOTAL), "ScSubTotalParam::SetSubLabels(): nGroup > MAXSUBTOTAL!"); - OSL_ENSURE(ptrSubLabels, "ScSubTotalParam::SetSubLabels(): ptrSubLabels == NULL!"); - OSL_ENSURE(ptrSubNames, "ScSubTotalParam::SetSubLabels(): ptrSubNames == NULL!"); OSL_ENSURE((nCount > 0), "ScSubTotalParam::SetSubLabels(): nCount == 0!"); - if (!(ptrSubLabels && ptrSubNames && (nCount > 0) && (nGroup <= MAXSUBTOTAL))) + if (!(nCount > 0 && nGroup <= MAXSUBTOTAL)) return; - // 0 is interpreted as 1, otherwise decrementing the array index - if (nGroup != 0) - nGroup--; - aGroups[nGroup].AllocSubLabels(nCount); for (sal_uInt16 i = 0; i < nCount; i++) - aGroups[nGroup].pSubLabels[i] = { ptrSubLabels[i], ptrSubNames[i] }; + aGroups[nGroup].pSubLabels[i] = std::make_pair(rColLabels[i].first, std::move(rColLabels[i].second)); } /* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/sc/source/core/data/table3.cxx b/sc/source/core/data/table3.cxx index 6db9cb19a7bb..754ad9050e69 100644 --- a/sc/source/core/data/table3.cxx +++ b/sc/source/core/data/table3.cxx @@ -2366,12 +2366,12 @@ bool ScTable::DoSubTotals( ScSubTotalParam& rParam ) return bSpaceLeft; } -bool ScTable::DoSimpleSubTotals( ScSubTotalParam& rParam, sal_uInt16 nIndex ) +bool ScTable::DoSimpleSubTotals( ScSubTotalParam& rParam ) { RowEntry aRowEntry; - aRowEntry.nGroupNo = 0; - aRowEntry.nSubStartRow = rParam.nRow1 + static_cast<SCROW>(rParam.bHasHeader); // Header - aRowEntry.nFuncStart = rParam.nRow1 + static_cast<SCROW>(rParam.bHasHeader); // Header + aRowEntry.nGroupNo = 0; // only one group can have + //aRowEntry.nSubStartRow = rParam.nRow1 + static_cast<SCROW>(rParam.bHasHeader); // Header + //aRowEntry.nFuncStart = rParam.nRow1 + static_cast<SCROW>(rParam.bHasHeader); // Header aRowEntry.nDestRow = rParam.nRow2 + 1; aRowEntry.nFuncEnd = rParam.nRow2; @@ -2394,38 +2394,27 @@ bool ScTable::DoSimpleSubTotals( ScSubTotalParam& rParam, sal_uInt16 nIndex ) } else { - SetString(group.nField, aRowEntry.nDestRow, nTab, ScResId(STR_TABLE_TOTAL)); + SetString(rParam.nCol1, aRowEntry.nDestRow, nTab, ScResId(STR_TABLE_TOTAL)); } // insert the formulas - if (group.nSubTotals > 0) + if (group.nCustFuncs > 0) { - for (SCCOL nResult = 0; nResult < group.nSubTotals; ++nResult) + for (SCCOL nResult = 0; nResult < group.nCustFuncs; ++nResult) { - ScTokenArray aArr(rDocument); - aArr.AddOpCode(ocSubTotal); - aArr.AddOpCode(ocOpen); - aArr.AddDouble(static_cast<double>(group.func(nResult))); - aArr.AddOpCode(ocSep); - // Table refs structure - aArr.AddTableRef(nIndex); - aArr.AddOpCode(ocTableRefOpen); - ScSingleRefData aSingleRef; - aSingleRef.InitAddress(group.col(nResult), aRowEntry.nFuncStart - 1, nTab); - aArr.AddSingleReference(aSingleRef); - aArr.AddOpCode(ocTableRefClose); - // Table refs structure end - aArr.AddOpCode(ocClose); - aArr.AddOpCode(ocStop); - ScFormulaCell* pCell = new ScFormulaCell( - rDocument, ScAddress(group.col(nResult), aRowEntry.nDestRow, nTab), aArr); - if (rParam.bIncludePattern) - pCell->SetNeedNumberFormat(true); - - SetFormulaCell(group.col(nResult), aRowEntry.nDestRow, pCell); - if (group.col(nResult) != group.nField) + if (ScTokenArray* pArray = group.custToken(nResult)) { - lcl_RemoveNumberFormat(this, group.col(nResult), aRowEntry.nDestRow); + ScFormulaCell* pCell = new ScFormulaCell( + rDocument, ScAddress(group.colcust(nResult), aRowEntry.nDestRow, nTab), + *pArray); + if (rParam.bIncludePattern) + pCell->SetNeedNumberFormat(true); + + SetFormulaCell(group.colcust(nResult), aRowEntry.nDestRow, pCell); + if (group.colcust(nResult) != group.nField) + { + lcl_RemoveNumberFormat(this, group.colcust(nResult), aRowEntry.nDestRow); + } } } } @@ -2680,7 +2669,7 @@ void ScTable::PrepareQuery( ScQueryParam& rQueryParam ) lcl_PrepareQuery(&rDocument, this, rQueryParam, false); } -SCSIZE ScTable::Query(const ScQueryParam& rParamOrg, bool bKeepSub) +SCSIZE ScTable::Query(const ScQueryParam& rParamOrg, bool bKeepSub, bool bKeepTotals) { ScQueryParam aParam( rParamOrg ); typedef std::unordered_set<OUString> StrSetType; @@ -2713,6 +2702,11 @@ SCSIZE ScTable::Query(const ScQueryParam& rParamOrg, bool bKeepSub) { bool bResult; // Filter result bool bValid = queryEvaluator.ValidQuery(j, nullptr, &blockPos); + // Keep Totals row (last) even if we have no any cell formula! + if (!bValid && bKeepTotals && j == nRealRow2) + { + bValid = true; + } if (!bValid && bKeepSub) // Keep subtotals { for (SCCOL nCol=aParam.nCol1; nCol<=aParam.nCol2 && !bValid; nCol++) diff --git a/sc/source/core/tool/dbdata.cxx b/sc/source/core/tool/dbdata.cxx index eb4be588ac61..2d2dcc5ba4a8 100644 --- a/sc/source/core/tool/dbdata.cxx +++ b/sc/source/core/tool/dbdata.cxx @@ -25,6 +25,8 @@ #include <unotools/charclass.hxx> #include <dbdata.hxx> +#include <compiler.hxx> +#include <tokenarray.hxx> #include <globalnames.hxx> #include <refupdat.hxx> #include <document.hxx> @@ -321,7 +323,6 @@ ScDBData::ScDBData( const ScDBData& rData ) : bAutoFilter (rData.bAutoFilter), bModified (rData.bModified), maTableColumnNames (rData.maTableColumnNames), - maTableColumnAttributes(rData.maTableColumnAttributes), mbTableColumnNamesDirty(rData.mbTableColumnNamesDirty), nFilteredRowCount (rData.nFilteredRowCount) { @@ -359,7 +360,6 @@ ScDBData::ScDBData( const OUString& rName, const ScDBData& rData ) : bAutoFilter (rData.bAutoFilter), bModified (rData.bModified), maTableColumnNames (rData.maTableColumnNames), - maTableColumnAttributes(rData.maTableColumnAttributes), mbTableColumnNamesDirty (rData.mbTableColumnNamesDirty), nFilteredRowCount (rData.nFilteredRowCount) { @@ -417,7 +417,6 @@ ScDBData& ScDBData::operator= (const ScDBData& rData) else { maTableColumnNames = rData.maTableColumnNames; - maTableColumnAttributes = rData.maTableColumnAttributes; mbTableColumnNamesDirty = rData.mbTableColumnNamesDirty; } @@ -697,7 +696,9 @@ void ScDBData::SetSubTotalParam(const ScSubTotalParam& rSubTotalParam) mpSubTotal.reset(new ScSubTotalParam(rSubTotalParam)); } -void ScDBData::CreateSubTotalParam(ScSubTotalParam& rSubTotalParam) const +void ScDBData::ImportTotalRowParam(ScSubTotalParam& rSubTotalParam, + const std::vector<TableColumnAttributes>& rAttributesVector, + formula::FormulaGrammar::Grammar eGrammar) const { rSubTotalParam.bDoSort = false; rSubTotalParam.bGroupedBy = false; @@ -706,79 +707,316 @@ void ScDBData::CreateSubTotalParam(ScSubTotalParam& rSubTotalParam) const const size_t nEntryCount = rSubTotalParam.nCol2 - rSubTotalParam.nCol1 + 1; // col count if (nEntryCount > 0) { - // how many col we do subtotal - size_t nTotalsCount = std::count_if( - GetTableColumnAttributes().begin(), GetTableColumnAttributes().end(), - [](const TableColumnAttributes& attr) { return attr.maTotalsFunction.has_value(); }); - if (nTotalsCount > 0) + SCCOL nCol = rSubTotalParam.nCol1; + std::vector<std::pair<SCCOL, OUString>> vColLabels; + std::vector<std::pair<SCCOL, std::unique_ptr<ScTokenArray>>> vColFuncs; + for (const auto& rxTableColumn : rAttributesVector) { - std::unique_ptr<ScSubTotalFunc[]> pFunctions; - std::unique_ptr<SCCOL[]> pSubTotals; - pFunctions.reset(new ScSubTotalFunc[nTotalsCount]); - pSubTotals.reset(new SCCOL[nTotalsCount]); + if (rxTableColumn.maTotalsRowLabel.has_value()) + { + OUString aStr = rxTableColumn.maTotalsRowLabel.value(); + if (!aStr.isEmpty()) + vColLabels.push_back(std::make_pair(nCol, std::move(aStr))); + } + else if (mpContainer && rxTableColumn.maTotalsFunction.has_value()) + { + ScDocument& rDoc = mpContainer->GetDocument(); + const OUString& sFuncName = rxTableColumn.maTotalsFunction.value(); + if (sFuncName == u"custom") + { + if (rxTableColumn.maCustomFunction.has_value()) + { + SCROW nLastRow = rSubTotalParam.nRow2; + if (!HasTotals()) + nLastRow++; + + ScAddress aPos(nCol, nLastRow, nTable); + ScCompiler aComp(rDoc, aPos, eGrammar, true, + false); + std::unique_ptr<ScTokenArray> pArr + = aComp.CompileString(rxTableColumn.maCustomFunction.value()); + if (pArr) + { + vColFuncs.push_back(std::make_pair(nCol, std::move(pArr))); + } + } + } + else + { + ScSubTotalFunc eSubType = GetSubTotalFuncFromString(sFuncName); + if (eSubType != SUBTOTAL_FUNC_NONE) + { + std::unique_ptr<ScTokenArray> pArr(new ScTokenArray(rDoc)); + pArr->AddOpCode(ocSubTotal); + pArr->AddOpCode(ocOpen); + pArr->AddDouble(static_cast<double>(eSubType)); + pArr->AddOpCode(ocSep); + // Table refs structure + pArr->AddTableRef(GetIndex()); + pArr->AddOpCode(ocTableRefOpen); + ScSingleRefData aSingleRef; + aSingleRef.InitAddress(nCol, rSubTotalParam.nRow1, nTable); + pArr->AddSingleReference(aSingleRef); + pArr->AddOpCode(ocTableRefClose); + // Table refs structure end + pArr->AddOpCode(ocClose); + pArr->AddOpCode(ocStop); + // Store + vColFuncs.push_back(std::make_pair(nCol, std::move(pArr))); + } + } + } + nCol++; + } + rSubTotalParam.SetSubLabels(static_cast<sal_uInt16>(0), vColLabels, vColLabels.size()); + rSubTotalParam.SetCustFuncs(static_cast<sal_uInt16>(0), vColFuncs, vColFuncs.size()); + } +} - for (size_t i = 0, nCheck = 0; i < nEntryCount; i++) +void ScDBData::CreateTotalRowParam(ScSubTotalParam& rSubTotalParam) const +{ + rSubTotalParam.bDoSort = false; + rSubTotalParam.bGroupedBy = false; + rSubTotalParam.aGroups[0].nField = rSubTotalParam.nCol1; // which column we add 'Summary' + + const size_t nEntryCount = rSubTotalParam.nCol2 - rSubTotalParam.nCol1 + 1; // col count + if (nEntryCount > 0 && mpContainer) + { + ScDocument& rDoc = mpContainer->GetDocument(); + ScHorizontalCellIterator aIter(rDoc, nTable, rSubTotalParam.nCol1, rSubTotalParam.nRow2, + rSubTotalParam.nCol2, + rSubTotalParam.nRow2); // Total row only + ScRefCellValue* pCell; + SCCOL nCol = rSubTotalParam.nCol1 - 1; + SCROW nRow; + std::vector<std::pair<SCCOL, OUString>> vColLabels; + std::vector<std::pair<SCCOL, std::unique_ptr<ScTokenArray>>> vColFuncs; + while ((pCell = aIter.GetNext(nCol, nRow)) != nullptr) + { + if (pCell->getType() != CELLTYPE_FORMULA) + { + OUString aStr = pCell->getString(rDoc); + if (!aStr.isEmpty()) + vColLabels.push_back(std::make_pair(nCol, std::move(aStr))); + } + else { - if (GetTableColumnAttributes().size() <= i) + if (ScFormulaCell* pFC = pCell->getFormula()) { - SAL_WARN("sc.core", - "ScDBData::CreateSubTotalParam - column attributes size mismatch"); - break; + std::unique_ptr<ScTokenArray> pTokens = pFC->GetCode()->Clone(); + if (pTokens) + { + vColFuncs.push_back(std::make_pair(nCol, std::move(pTokens))); + } } - if (GetTableColumnAttributes()[i].maTotalsFunction.has_value()) + } + } + rSubTotalParam.SetSubLabels(static_cast<sal_uInt16>(0), vColLabels, vColLabels.size()); + rSubTotalParam.SetCustFuncs(static_cast<sal_uInt16>(0), vColFuncs, vColFuncs.size()); + } +} + +std::vector<TableColumnAttributes> ScDBData::GetTotalRowAttributes(formula::FormulaGrammar::Grammar eGrammar) const +{ + ScSubTotalParam rParam; + GetSubTotalParam(rParam); + + const SCCOL nEntryCount = rParam.nCol2 - rParam.nCol1 + 1; // col count + std::vector<TableColumnAttributes> aAttributesVector(nEntryCount); + if (nEntryCount > 0) + { + if (HasTotals()) + { + if (!mpContainer) + assert(!"ScDBData::GetTotalRowAttributes - how did we end up here without container?"); + else + { + ScDocument& rDoc = mpContainer->GetDocument(); + ScHorizontalCellIterator aIter(rDoc, nTable, rParam.nCol1, rParam.nRow2, + rParam.nCol2, + rParam.nRow2); // Total row only + ScRefCellValue* pCell; + SCCOL nCol = rParam.nCol1 - 1; + SCROW nRow; + while ((pCell = aIter.GetNext(nCol, nRow)) != nullptr) { - pSubTotals[nCheck] = rSubTotalParam.nCol1 + i; - const OUString& sFuncName = GetTableColumnAttributes()[i].maTotalsFunction.value(); - //if (mpContainer && sFuncName == u"custom") - //{ - // // TODO: store custom formula tokenarrays somewhere - // ScFormulaCell* pFC = mpContainer->GetDocument().GetFormulaCell( - // ScAddress(rSubTotalParam.nCol1 + i, rSubTotalParam.nRow2, nTable)); - // if (pFC) - // { - // std::unique_ptr<ScTokenArray> pTokenArray = pFC->GetCode()->Clone(); - // } - //} - pFunctions[nCheck] = ScDBData::GetSubTotalFuncFromString(sFuncName); - nCheck++; + TableColumnAttributes aNameAttr; + if (pCell->getType() != CELLTYPE_FORMULA) + { + OUString aStr = pCell->getString(rDoc); + if (!aStr.isEmpty()) + aNameAttr.maTotalsRowLabel = aStr; + } + else + { + if (ScFormulaCell* pFC = pCell->getFormula()) + { + bool bSubTotal = pFC->IsSubTotal(); + ScTokenArray* pTokens = pFC->GetCode(); + if (bSubTotal && pTokens) + { + OUString aFunctype = GetSimpleSubTotalFunction(pTokens, nCol, rParam.nRow1); + if (aFunctype != u"custom") + aNameAttr.maTotalsFunction = aFunctype; + else + bSubTotal = false; // fallback to custom + } + + if (!bSubTotal && pTokens) + { + ScAddress aPos(nCol, rParam.nRow2, nTable); + ScCompiler aComp(rDoc, aPos, *pTokens, eGrammar); + OUStringBuffer aBuf; + aComp.CreateStringFromTokenArray(aBuf); + OUString aFormula = aBuf.makeStringAndClear(); + aNameAttr.maTotalsFunction = "custom"; + aNameAttr.maCustomFunction = aFormula; + } + } + } + SCCOL nPos = nCol - rParam.nCol1; + if (nPos < nEntryCount) + aAttributesVector[nPos] = std::move(aNameAttr); + else + SAL_WARN("sc.core", "ScDBData::GetTotalRowAttributes - invalid attributes/columns"); } } - rSubTotalParam.SetSubTotals(static_cast<sal_uInt16>(0), // group number - pSubTotals.get(), pFunctions.get(), - nTotalsCount); // number of array elements } + else + { + const auto& group = rParam.aGroups[0]; + if (group.nSubLabels > 0) + { + for (SCCOL nResult = 0; nResult < group.nSubLabels; ++nResult) + { + SCCOL nPos = group.collabels(nResult) - rParam.nCol1; + if (nPos < nEntryCount) + aAttributesVector[nPos].maTotalsRowLabel = group.label(nResult); + else + SAL_WARN("sc.core", "ScDBData::GetTotalRowAttributes - invalid attributes/columns"); + } + } - // how many col we have totals Row Label - size_t nLabelsCount = std::count_if( - GetTableColumnAttributes().begin(), GetTableColumnAttributes().end(), - [](const TableColumnAttributes& attr) { return attr.maTotalsRowLabel.has_value(); }); - if (nLabelsCount > 0) + // insert the formulas + if (group.nCustFuncs > 0) + { + for (SCCOL nResult = 0; nResult < group.nCustFuncs; ++nResult) + { + if (ScTokenArray* pTokens = group.custToken(nResult)) + { + SCCOL nCol = group.colcust(nResult); + bool bSubTotal = pTokens->HasOpCode(ocSubTotal); + if (bSubTotal) + { + OUString aFunctype = GetSimpleSubTotalFunction(pTokens, nCol, rParam.nRow1); + if (aFunctype != u"custom") + { + SCCOL nPos = nCol - rParam.nCol1; + if (nPos < nEntryCount) + aAttributesVector[nPos].maTotalsFunction = aFunctype; + else + SAL_WARN("sc.core", "ScDBData::GetTotalRowAttributes - invalid " + "attributes/columns"); + } + else + bSubTotal = false; // fallback to custom + } + + if (!bSubTotal) + { + if (!mpContainer) + assert(!"ScDBData::GetTotalRowAttributes - how did we end up here without container?"); + else + { + ScDocument& rDoc = mpContainer->GetDocument(); + ScAddress aPos(nCol, rParam.nRow2 + 1, nTable); + ScCompiler aComp(rDoc, aPos, *pTokens, eGrammar); + OUStringBuffer aBuf; + aComp.CreateStringFromTokenArray(aBuf); + OUString aFormula = aBuf.makeStringAndClear(); + + SCCOL nPos = nCol - rParam.nCol1; + if (nPos < nEntryCount) + { + aAttributesVector[nPos].maTotalsFunction = "custom"; + aAttributesVector[nPos].maCustomFunction = aFormula; + } + else + SAL_WARN("sc.core", "ScDBData::GetTotalRowAttributes - invalid " + "attributes/columns"); + } + } + } + } + } + } + } + return aAttributesVector; +} + +OUString ScDBData::GetSimpleSubTotalFunction(const ScTokenArray* pTokens, SCCOL nCol, SCROW nHeaderRow) const +{ + std::vector<std::pair<OpCode, formula::StackVar>> expected + = { { ocSubTotal, formula::svByte }, { ocOpen, formula::svSep }, + { ocPush, formula::svDouble }, { ocSep, formula::svSep }, + { ocTableRef, formula::svIndex }, { ocTableRefOpen, formula::svSep }, + { ocPush, formula::svSingleRef }, { ocTableRefClose, formula::svSep }, + { ocClose, formula::svSep } }; + + size_t nIdx = 0; + ScSubTotalFunc eSubType = SUBTOTAL_FUNC_NONE; + formula::FormulaTokenArrayPlainIterator aIterResult(*pTokens); + for (formula::FormulaToken* t = aIterResult.NextNoSpaces(); t; t = aIterResult.NextNoSpaces()) + { + // check for subtotal opcode + OpCode eOpCode = t->GetOpCode(); + formula::StackVar eType = t->GetType(); + + if (nIdx < expected.size() && eOpCode == expected[nIdx].first + && eType == expected[nIdx].second) { - std::unique_ptr<OUString[]> pLabels; - std::unique_ptr<SCCOL[]> pSubLabels; - pLabels.reset(new OUString[nLabelsCount]); - pSubLabels.reset(new SCCOL[nLabelsCount]); + if (nIdx == 2) // { ocPush, formula::svDouble } + { + sal_Int32 nFunc = static_cast<sal_Int32>(t->GetDouble()); + if (nFunc > 100.) + nFunc -= 100; - for (size_t i = 0, nCheck = 0; i < nEntryCount; i++) + if (nFunc < 1 || nFunc > 11) + { + return u"custom"_ustr; + } + else + eSubType = static_cast<ScSubTotalFunc>(nFunc); + } + else if (nIdx == 4) // { ocTableRef, formula::svIndex } { - if (GetTableColumnAttributes().size() <= i) + sal_uInt16 nDbIndex = t->GetIndex(); + if (GetIndex() != nDbIndex) { - SAL_WARN("sc.core", - "ScDBData::CreateSubTotalParam - column attributes size mismatch"); - break; + return u"custom"_ustr; } - if (GetTableColumnAttributes()[i].maTotalsRowLabel.has_value()) + } + else if (nIdx == 6) // { ocPush, formula::svSingleRef } + { + const ScSingleRefData* pRef = t->GetSingleRef(); + if (!(pRef && pRef->Col() == nCol && pRef->Row() == nHeaderRow)) { - pSubLabels[nCheck] = rSubTotalParam.nCol1 + i; - pLabels[nCheck] = GetTableColumnAttributes()[i].maTotalsRowLabel.value(); - nCheck++; + return u"custom"_ustr; } } - rSubTotalParam.SetSubLabels(static_cast<sal_uInt16>(0), // group number - pSubLabels.get(), pLabels.get(), - nLabelsCount); // number of array elements + else + { /*Nothing to do*/ + } + + ++nIdx; + } + else + { + return u"custom"_ustr; } } + + return GetStringFromSubTotalFunc(eSubType); } void ScDBData::GetImportParam(ScImportParam& rImportParam) const @@ -881,7 +1119,6 @@ void ScDBData::UpdateMoveTab(SCTAB nOldPos, SCTAB nNewPos) aRange.aEnd.Row()); // Do not use SetTableColumnNames() because that resets mbTableColumnNamesDirty. maTableColumnNames = aNames; - maTableColumnAttributes.resize(aNames.size()); mbTableColumnNamesDirty = bTableColumnNamesDirty; } @@ -914,7 +1151,7 @@ bool ScDBData::UpdateReference(const ScDocument& rDoc, UpdateRefMode eUpdateRefM if (bDoUpdate && eRet != UR_INVALID) { // MoveTo() invalidates column names via SetArea(); adjust, remember and set new. - AdjustTableColumnAttributes( eUpdateRefMode, nDx, nCol1, nOldCol1, nOldCol2, theCol1, theCol2); + AdjustTableColumnNames( eUpdateRefMode, nDx, nCol1, nOldCol1, nOldCol2, theCol1, theCol2); ::std::vector<OUString> aNames( maTableColumnNames); bool bTableColumnNamesDirty = mbTableColumnNamesDirty; // tdf#48025, tdf#141946: update the column index of the filter criteria, @@ -925,7 +1162,6 @@ bool ScDBData::UpdateReference(const ScDocument& rDoc, UpdateRefMode eUpdateRefM MoveTo( theTab1, theCol1, theRow1, theCol2, theRow2 ); // Do not use SetTableColumnNames() because that resets mbTableColumnNamesDirty. maTableColumnNames = aNames; - maTableColumnAttributes.resize(aNames.size()); mbTableColumnNamesDirty = bTableColumnNamesDirty; } @@ -1009,12 +1245,7 @@ void ScDBData::SetTableColumnNames( ::std::vector< OUString >&& rNames ) mbTableColumnNamesDirty = false; } -void ScDBData::SetTableColumnAttributes( ::std::vector< TableColumnAttributes >&& rAttributes ) -{ - maTableColumnAttributes = std::move(rAttributes); -} - -void ScDBData::AdjustTableColumnAttributes( UpdateRefMode eUpdateRefMode, SCCOL nDx, SCCOL nCol1, +void ScDBData::AdjustTableColumnNames( UpdateRefMode eUpdateRefMode, SCCOL nDx, SCCOL nCol1, SCCOL nOldCol1, SCCOL nOldCol2, SCCOL nNewCol1, SCCOL nNewCol2 ) { if (maTableColumnNames.empty()) @@ -1026,7 +1257,6 @@ void ScDBData::AdjustTableColumnAttributes( UpdateRefMode eUpdateRefMode, SCCOL return; // not moved or entirely moved, nothing to do ::std::vector<OUString> aNewNames; - ::std::vector<TableColumnAttributes> aNewAttributes; if (eUpdateRefMode == URM_INSDEL) { if (nDx > 0) @@ -1043,26 +1273,22 @@ void ScDBData::AdjustTableColumnAttributes( UpdateRefMode eUpdateRefMode, SCCOL if (nDx > 0) n += nDx; aNewNames.resize(n); - aNewAttributes.resize(n); // Copy head. for (size_t i = 0; i < nHead; ++i) { aNewNames[i] = maTableColumnNames[i]; - aNewAttributes[i] = maTableColumnAttributes[i]; } // Copy tail, inserted middle range, if any, stays empty. for (size_t i = n - nTail, j = maTableColumnNames.size() - nTail; i < n; ++i, ++j) { aNewNames[i] = maTableColumnNames[j]; - aNewAttributes[i] = maTableColumnAttributes[j]; } } } // else empty aNewNames invalidates names/offsets SAL_WARN_IF( !maTableColumnNames.empty() && aNewNames.empty(), - "sc.core", "ScDBData::AdjustTableColumnAttributes - invalidating column attributes/offsets"); + "sc.core", "ScDBData::AdjustTableColumnNames - invalidating column names/offsets"); aNewNames.swap( maTableColumnNames); - aNewAttributes.swap(maTableColumnAttributes); if (maTableColumnNames.empty()) mbTableColumnNamesDirty = true; if (mbTableColumnNamesDirty) @@ -1198,7 +1424,6 @@ void ScDBData::RefreshTableColumnNames( ScDocument* pDoc ) } aNewNames.swap( maTableColumnNames); - maTableColumnAttributes.resize(maTableColumnNames.size()); mbTableColumnNamesDirty = false; } @@ -1326,29 +1551,58 @@ ScSubTotalFunc ScDBData::GetSubTotalFuncFromString(std::u16string_view sFunction { if (sFunction == u"sum") return SUBTOTAL_FUNC_SUM; - if (sFunction == u"countNums") + else if (sFunction == u"countNums") return SUBTOTAL_FUNC_CNT; - if (sFunction == u"count") + else if (sFunction == u"count") return SUBTOTAL_FUNC_CNT2; - /*if (sFunction) + /*else if (sFunction) return SUBTOTAL_FUNC_PROD;*/ - if (sFunction == u"average") + else if (sFunction == u"average") return SUBTOTAL_FUNC_AVE; - /*if (sFunction) + /*else if (sFunction) return SUBTOTAL_FUNC_MED;*/ - if (sFunction == u"max") + else if (sFunction == u"max") return SUBTOTAL_FUNC_MAX; - if (sFunction == u"min") + else if (sFunction == u"min") return SUBTOTAL_FUNC_MIN; - if (sFunction == u"stdDev") + else if (sFunction == u"stdDev") return SUBTOTAL_FUNC_STD; - /*if (sFunction) + /*else if (sFunction) return SUBTOTAL_FUNC_STDP;*/ - if (sFunction == u"var") + else if (sFunction == u"var") return SUBTOTAL_FUNC_VAR; - /*if (sFunction) + /*else if (sFunction) return SUBTOTAL_FUNC_VARP;*/ - return SUBTOTAL_FUNC_NONE; + else + return SUBTOTAL_FUNC_NONE; +} + +OUString ScDBData::GetStringFromSubTotalFunc(ScSubTotalFunc eFunc) +{ + if (eFunc == SUBTOTAL_FUNC_SUM) + return u"sum"_ustr; + else if (eFunc == SUBTOTAL_FUNC_CNT) + return u"countNums"_ustr; + else if (eFunc == SUBTOTAL_FUNC_CNT2) + return u"count"_ustr; + else if (eFunc == SUBTOTAL_FUNC_PROD) + return u"custom"_ustr; // ooxml not support in Total row + else if (eFunc == SUBTOTAL_FUNC_AVE) + return u"average"_ustr; + else if (eFunc == SUBTOTAL_FUNC_MED) + return u"custom"_ustr; // ooxml not support in Total row + else if (eFunc == SUBTOTAL_FUNC_MAX) + return u"max"_ustr; + else if (eFunc == SUBTOTAL_FUNC_MIN) + return u"min"_ustr; + else if (eFunc == SUBTOTAL_FUNC_STD) + return u"stdDev"_ustr; + else if (eFunc == SUBTOTAL_FUNC_STDP) + return u"custom"_ustr; // ooxml not support in Total row + else if (eFunc == SUBTOTAL_FUNC_VAR) + return u"var"_ustr; + else + return u"none"_ustr; } namespace { diff --git a/sc/source/filter/excel/xedbdata.cxx b/sc/source/filter/excel/xedbdata.cxx index 97fa10cf62b4..2440213419b4 100644 --- a/sc/source/filter/excel/xedbdata.cxx +++ b/sc/source/filter/excel/xedbdata.cxx @@ -189,6 +189,18 @@ void XclExpTables::SaveTableXml( XclExpXmlStream& rStrm, const Entry& rEntry ) if (hasTableTypeAttr) tableType = rData.GetTableType(); + const std::vector<TableColumnAttributes> aTotalValues + = rData.GetTotalRowAttributes(formula::FormulaGrammar::GRAM_OOXML); + + // if the Total row have ever been showed it will be true + bool hasAnySetValue = std::any_of(aTotalValues.begin(), aTotalValues.end(), + [](const TableColumnAttributes& attr) + { + return attr.maTotalsRowLabel.has_value() + || attr.maTotalsFunction.has_value() + || attr.maCustomFunction.has_value(); + }); + pTableStrm->startElement( XML_table, XML_xmlns, rStrm.getNamespaceURL(OOX_NS(xls)).toUtf8(), XML_id, OString::number( rEntry.mnTableId), @@ -198,7 +210,7 @@ void XclExpTables::SaveTableXml( XclExpXmlStream& rStrm, const Entry& rEntry ) XML_tableType, tableType, XML_headerRowCount, ToPsz10(rData.HasHeader()), XML_totalsRowCount, ToPsz10(rData.HasTotals()), - XML_totalsRowShown, ToPsz10(rData.HasTotals()) // we don't support that but if there are totals they are shown + XML_totalsRowShown, ToPsz10(hasAnySetValue) // OOXTODO: XML_comment, ..., // OOXTODO: XML_connectionId, ..., // OOXTODO: XML_dataCellStyle, ..., @@ -230,10 +242,13 @@ void XclExpTables::SaveTableXml( XclExpXmlStream& rStrm, const Entry& rEntry ) } const std::vector< OUString >& rColNames = rData.GetTableColumnNames(); - const std::vector< TableColumnAttributes >& rColAttributes = rData.GetTableColumnAttributes(); const std::vector< TableColumnModel >& rTableColumnModel = rData.GetTableColumnModel(); if (!rColNames.empty()) { + // rColNames and aTotalValues size should always be equal + assert((rColNames.size() == aTotalValues.size()) && + "XclExpTables::SaveTableXml - mismatch between column names and total values"); + pTableStrm->startElement(XML_tableColumns, XML_count, OString::number(aRange.aEnd.Col() - aRange.aStart.Col() + 1)); @@ -243,8 +258,6 @@ void XclExpTables::SaveTableXml( XclExpXmlStream& rStrm, const Entry& rEntry ) // which case we'd need start/endElement XML_tableColumn for such // column. - // OOXTODO: write <totalsRowFormula> once we support it. - std::optional<OUString> uniqueName = std::nullopt; // uniqueName should only be used when this table's tableType is queryTable or xml. @@ -261,8 +274,8 @@ void XclExpTables::SaveTableXml( XclExpXmlStream& rStrm, const Entry& rEntry ) XML_id, OString::number(i+1), XML_uniqueName, uniqueName, XML_name, rColNames[i].toUtf8(), - XML_totalsRowLabel, (i < rColAttributes.size() ? rColAttributes[i].maTotalsRowLabel : std::nullopt), - XML_totalsRowFunction, (i < rColAttributes.size() ? rColAttributes[i].maTotalsFunction : std::nullopt) + XML_totalsRowLabel, (i < aTotalValues.size() ? aTotalValues[i].maTotalsRowLabel : std::nullopt), + XML_totalsRowFunction, (i < aTotalValues.size() ? aTotalValues[i].maTotalsFunction : std::nullopt) // OOXTODO: XML_dataCellStyle, ..., // OOXTODO: XML_dataDxfId, ..., // OOXTODO: XML_headerRowCellStyle, ..., @@ -289,6 +302,15 @@ void XclExpTables::SaveTableXml( XclExpXmlStream& rStrm, const Entry& rEntry ) pTableStrm->singleElement(XML_xmlColumnPr, pXmlColumnPrAttrList); } + if (i < aTotalValues.size() && aTotalValues[i].maTotalsFunction.has_value() + && aTotalValues[i].maTotalsFunction.value() == u"custom") + { + // write custom functions + pTableStrm->startElement(XML_totalsRowFormula); + pTableStrm->writeEscaped(aTotalValues[i].maCustomFunction.value()); + pTableStrm->endElement(XML_totalsRowFormula); + } + // put </tableColumn> pTableStrm->endElement(XML_tableColumn); } diff --git a/sc/source/filter/inc/tablecolumnsbuffer.hxx b/sc/source/filter/inc/tablecolumnsbuffer.hxx index b85a9f4d9927..8abc3915afb6 100644 --- a/sc/source/filter/inc/tablecolumnsbuffer.hxx +++ b/sc/source/filter/inc/tablecolumnsbuffer.hxx @@ -42,20 +42,29 @@ public: void importTableColumn( SequenceInputStream& rStrm ); /** Gets the name of this column. */ const OUString& getName() const; - /** Gets the attributes of this column. */ - const TableColumnAttributes& getColumnAttributes() const; /** Imports XML column properties for the xmlColumnPr element. */ void importXmlColumnPr(const AttributeList& rAttribs); /** Returns access to the table column model data. */ TableColumnModel& getModel() { return maModel; } + /** Gets the Total Row Label of this column. */ + const std::optional<OUString>& getColumnRowLabel() const; + /** Gets the Subtotal function of this column. */ + const std::optional<OUString>& getColumnSubTotal() const; + /** Gets the Custom function of this column. */ + const std::optional<OUString>& getColumnFunction() const; + /** Sets the function of this column. */ + void setFunc( const OUString& rChars ); private: OUString maName; sal_Int32 mnId; sal_Int32 mnDataDxfId; - TableColumnAttributes maColumnAttributes; TableColumnModel maModel; + + std::optional<OUString> maRowLabel = std::nullopt; + std::optional<OUString> maSubTotal = std::nullopt; + std::optional<OUString> maFunction = std::nullopt; }; class TableColumns : public WorkbookHelper diff --git a/sc/source/filter/inc/tablecolumnscontext.hxx b/sc/source/filter/inc/tablecolumnscontext.hxx index 1f5a5ba046b5..21d123713f9d 100644 --- a/sc/source/filter/inc/tablecolumnscontext.hxx +++ b/sc/source/filter/inc/tablecolumnscontext.hxx @@ -33,6 +33,7 @@ public: protected: virtual ::oox::core::ContextHandlerRef onCreateContext( sal_Int32 nElement, const AttributeList& rAttribs ) override; + virtual void onCharacters( const rtl::OUString& rChars ) override; virtual void onStartElement( const AttributeList& rAttribs ) override; virtual ::oox::core::ContextHandlerRef onCreateRecordContext( sal_Int32 nRecId, SequenceInputStream& rStrm ) override; diff --git a/sc/source/filter/oox/tablecolumnsbuffer.cxx b/sc/source/filter/oox/tablecolumnsbuffer.cxx index e815bfa5156f..c93f49e2b171 100644 --- a/sc/source/filter/oox/tablecolumnsbuffer.cxx +++ b/sc/source/filter/oox/tablecolumnsbuffer.cxx @@ -20,8 +20,10 @@ #include <tablecolumnsbuffer.hxx> #include <sal/log.hxx> +#include <formula/grammar.hxx> #include <oox/helper/attributelist.hxx> #include <oox/token/tokens.hxx> +#include <dbdata.hxx> #include <subtotalparam.hxx> XmlColumnPrModel::XmlColumnPrModel() : @@ -57,9 +59,9 @@ void TableColumn::importTableColumn( const AttributeList& rAttribs ) maModel.maUniqueName = rAttribs.getXString( XML_uniqueName, OUString() ); mnDataDxfId = rAttribs.getInteger( XML_dataDxfId, -1 ); if ( rAttribs.hasAttribute(XML_totalsRowLabel ) ) - maColumnAttributes.maTotalsRowLabel = rAttribs.getStringDefaulted( XML_totalsRowLabel ); + maRowLabel = rAttribs.getStringDefaulted(XML_totalsRowLabel); if ( rAttribs.hasAttribute( XML_totalsRowFunction ) ) - maColumnAttributes.maTotalsFunction = rAttribs.getStringDefaulted( XML_totalsRowFunction ); + maSubTotal = rAttribs.getStringDefaulted(XML_totalsRowFunction); } void TableColumn::importTableColumn( SequenceInputStream& /*rStrm*/ ) @@ -73,9 +75,24 @@ const OUString& TableColumn::getName() const return maName; } -const TableColumnAttributes& TableColumn::getColumnAttributes() const +const std::optional<OUString>& TableColumn::getColumnRowLabel() const { - return maColumnAttributes; + return maRowLabel; +} + +const std::optional<OUString>& TableColumn::getColumnSubTotal() const +{ + return maSubTotal; +} + +const std::optional<OUString>& TableColumn::getColumnFunction() const +{ + return maFunction; +} + +void TableColumn::setFunc( const OUString& rChars ) +{ + maFunction = rChars; } void TableColumn::importXmlColumnPr(const AttributeList& rAttribs) @@ -127,26 +144,37 @@ bool TableColumns::finalizeImport( ScDBData* pDBData ) { /* TODO: use svl::SharedString for names */ ::std::vector< OUString > aNames( maTableColumnVector.size()); - ::std::vector< TableColumnAttributes > aAttributesVector( maTableColumnVector.size() ); + ::std::vector< TableColumnAttributes > aAttributes( maTableColumnVector.size() ); size_t i = 0; + bool hasAnySetValue = false; for (const auto& rxTableColumn : maTableColumnVector) { aNames[i] = rxTableColumn->getName(); - aAttributesVector[i] = rxTableColumn->getColumnAttributes(); pDBData->SetTableColumnModel( rxTableColumn->getModel() ); + aAttributes[i].maTotalsRowLabel = rxTableColumn->getColumnRowLabel(); + aAttributes[i].maTotalsFunction = rxTableColumn->getColumnSubTotal(); + aAttributes[i].maCustomFunction = rxTableColumn->getColumnFunction(); + + if (!hasAnySetValue + && (aAttributes[i].maTotalsRowLabel.has_value() + || aAttributes[i].maTotalsFunction.has_value() + || aAttributes[i].maCustomFunction.has_value())) + { + hasAnySetValue = true; + } + ++i; } pDBData->SetTableColumnNames( std::move(aNames) ); - pDBData->SetTableColumnAttributes( std::move(aAttributesVector) ); - // set subtotal parameters for columns - if (pDBData->HasTotals()) + + // Import subtotal parameters for columns + if (hasAnySetValue) { ScSubTotalParam aSubTotalParam; pDBData->GetSubTotalParam(aSubTotalParam); aSubTotalParam.bHasHeader = pDBData->HasHeader(); - aSubTotalParam.bRemoveOnly = false; - aSubTotalParam.bReplace = false; - pDBData->CreateSubTotalParam(aSubTotalParam); + pDBData->ImportTotalRowParam(aSubTotalParam, aAttributes, + formula::FormulaGrammar::GRAM_OOXML); pDBData->SetSubTotalParam(aSubTotalParam); } return true; diff --git a/sc/source/filter/oox/tablecolumnscontext.cxx b/sc/source/filter/oox/tablecolumnscontext.cxx index 1129bcd22f7d..e1115c41a375 100644 --- a/sc/source/filter/oox/tablecolumnscontext.cxx +++ b/sc/source/filter/oox/tablecolumnscontext.cxx @@ -20,6 +20,7 @@ #include <tablecolumnscontext.hxx> #include <tablecolumnsbuffer.hxx> +#include <oox/helper/attributelist.hxx> #include <oox/token/namespaces.hxx> namespace oox::xls { @@ -32,20 +33,38 @@ TableColumnContext::TableColumnContext( WorksheetContextBase& rParent, TableColu { } +void TableColumnContext::onCharacters( const rtl::OUString& rChars ) +{ + switch( getCurrentElement() ) + { + case XLS_TOKEN( totalsRowFormula ): + mrTableColumn.setFunc(rChars); + break; + } +} + ContextHandlerRef TableColumnContext::onCreateContext( sal_Int32 nElement, const AttributeList& rAttribs ) { switch (nElement) { case XLS_TOKEN(xmlColumnPr): mrTableColumn.importXmlColumnPr( rAttribs ); - break; + break; + case XLS_TOKEN(totalsRowFormula): + { + if (getCurrentElement() == XLS_TOKEN(tableColumn) && + rAttribs.getToken(XML_t, XML_normal) != XML_TOKEN_INVALID) + return this; + } + break; } return nullptr; } void TableColumnContext::onStartElement( const AttributeList& rAttribs ) { - mrTableColumn.importTableColumn( rAttribs ); + if (getCurrentElement() == XLS_TOKEN(tableColumn)) + mrTableColumn.importTableColumn( rAttribs ); } ContextHandlerRef TableColumnContext::onCreateRecordContext( sal_Int32 /*nRecId*/, SequenceInputStream& /*rStrm*/ ) diff --git a/sc/source/ui/docshell/dbdocfun.cxx b/sc/source/ui/docshell/dbdocfun.cxx index de0daf0634b9..1146736cd1d2 100644 --- a/sc/source/ui/docshell/dbdocfun.cxx +++ b/sc/source/ui/docshell/dbdocfun.cxx @@ -829,14 +829,17 @@ bool ScDBDocFunc::Query( SCTAB nTab, const ScQueryParam& rQueryParam, weld::WaitObject aWait( ScDocShell::GetActiveDialogParent() ); bool bKeepSub = false; // repeat existing partial results? + bool bKeepTotals = false; if (rQueryParam.GetEntry(0).bDoQuery) // not at cancellation { ScSubTotalParam aSubTotalParam; pDBData->GetSubTotalParam( aSubTotalParam ); // partial results exist? - if ((aSubTotalParam.aGroups[0].bActive && !aSubTotalParam.bRemoveOnly) - || (pDBData->HasTotals() && pDBData->GetTableStyleInfo())) + if (aSubTotalParam.aGroups[0].bActive && !aSubTotalParam.bRemoveOnly) bKeepSub = true; + + if (pDBData->HasTotals() && pDBData->GetTableStyleInfo()) + bKeepTotals = true; } ScDocumentUniquePtr pUndoDoc; @@ -902,7 +905,7 @@ bool ScDBDocFunc::Query( SCTAB nTab, const ScQueryParam& rQueryParam, } // execute filtering on the document - SCSIZE nCount = rDoc.Query( nTab, rQueryParam, bKeepSub ); + SCSIZE nCount = rDoc.Query( nTab, rQueryParam, bKeepSub, bKeepTotals ); pDBData->CalcSaveFilteredCount( nCount ); if (bCopy) { @@ -1275,7 +1278,6 @@ void ScDBDocFunc::DoTableSubTotals( SCTAB nTab, const ScDBData& rNewData, const ScSubTotalParam aNewParam; rNewData.GetSubTotalParam(aNewParam); // end of range is being changed - // ScSubTotalParam aNewParam(rParam); ScDocumentUniquePtr pUndoDoc; std::unique_ptr<ScRangeName> pUndoRange; std::unique_ptr<ScDBCollection> pUndoDB; @@ -1310,7 +1312,7 @@ void ScDBDocFunc::DoTableSubTotals( SCTAB nTab, const ScDBData& rNewData, const bool bSuccess = true; if (bDo) { - bSuccess = rDoc.DoTableSubTotals(nTab, aNewParam, rNewData.GetIndex()); + bSuccess = rDoc.DoTableSubTotals(nTab, aNewParam); rDoc.SetDrawPageSize(nTab); } ScRange aDirtyRange(aNewParam.nCol1, aNewParam.nRow1, nTab, aNewParam.nCol2, aNewParam.nRow2, diff --git a/sc/source/ui/view/dbfunc3.cxx b/sc/source/ui/view/dbfunc3.cxx index 94469fa6d6ba..1c9a7e45fb69 100644 --- a/sc/source/ui/view/dbfunc3.cxx +++ b/sc/source/ui/view/dbfunc3.cxx @@ -670,7 +670,7 @@ void ScDBFunc::DoTableSubTotals( const ScDBData& rNewData, const ScSubTotalParam bool bSuccess = true; if (bDo) { - bSuccess = rDoc.DoTableSubTotals(nTab, aNewParam, rNewData.GetIndex()); + bSuccess = rDoc.DoTableSubTotals(nTab, aNewParam); } ScRange aDirtyRange(aNewParam.nCol1, aNewParam.nRow1, nTab, aNewParam.nCol2, aNewParam.nRow2, nTab); diff --git a/sc/source/ui/view/gridwin.cxx b/sc/source/ui/view/gridwin.cxx index 4facd482f6d8..589f28c3eea0 100644 --- a/sc/source/ui/view/gridwin.cxx +++ b/sc/source/ui/view/gridwin.cxx @@ -2518,11 +2518,9 @@ void ScGridWindow::MouseButtonUp( const MouseEvent& rMEvt ) ScSubTotalParam aSubTotalParam; pDBData->GetSubTotalParam(aSubTotalParam); aSubTotalParam.bHasHeader = aNewDBData.HasHeader(); - if (!aNewDBData.HasSubTotalParam()) - { - pDBData->CreateSubTotalParam(aSubTotalParam); - aNewDBData.SetSubTotalParam(aSubTotalParam); - } + // store current subtotal settings + pDBData->CreateTotalRowParam(aSubTotalParam); + aNewDBData.SetSubTotalParam(aSubTotalParam); // add/replace total row aSubTotalParam.bRemoveOnly = false; aSubTotalParam.bReplace = true; diff --git a/sc/source/ui/view/tableshell.cxx b/sc/source/ui/view/tableshell.cxx index 153ff9d67929..52744dd972bb 100644 --- a/sc/source/ui/view/tableshell.cxx +++ b/sc/source/ui/view/tableshell.cxx @@ -99,15 +99,12 @@ void ScTableShell::ExecuteDatabaseSettings(SfxRequest& rReq) ScSubTotalParam aSubTotalParam; aNewDBData.GetSubTotalParam(aSubTotalParam); aSubTotalParam.bHasHeader = aNewDBData.HasHeader(); - if (!aNewDBData.HasSubTotalParam()) - { - pDBData->CreateSubTotalParam(aSubTotalParam); - aNewDBData.SetSubTotalParam(aSubTotalParam); - } if (!aNewDBData.HasTotals()) { - // remove total row + // store current subtotal settings before removing total row + pDBData->CreateTotalRowParam(aSubTotalParam); + aNewDBData.SetSubTotalParam(aSubTotalParam); aSubTotalParam.bRemoveOnly = true; aSubTotalParam.bReplace = true; aFunc.DoTableSubTotals(aNewDBData.GetTab(), aNewDBData, aSubTotalParam,
