sc/inc/dbdata.hxx | 12 + sc/source/core/data/table3.cxx | 6 sc/source/core/tool/dbdata.cxx | 260 ++++++++++++++++++++++++++-- sc/source/filter/excel/xedbdata.cxx | 52 ++++- sc/source/filter/oox/tablecolumnsbuffer.cxx | 4 sc/source/ui/view/cellsh2.cxx | 9 sc/source/ui/view/gridwin.cxx | 2 sc/source/ui/view/tableshell.cxx | 5 8 files changed, 317 insertions(+), 33 deletions(-)
New commits: commit 13d573254ece0ff4f4d4189a317a0db0d9aedeb6 Author: Balazs Varga <[email protected]> AuthorDate: Thu Oct 30 17:49:09 2025 +0100 Commit: Balazs Varga <[email protected]> CommitDate: Fri Oct 31 16:28:53 2025 +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]> diff --git a/sc/source/ui/view/cellsh2.cxx b/sc/source/ui/view/cellsh2.cxx index 9d67cdc59c1b..c92ead3148fd 100644 --- a/sc/source/ui/view/cellsh2.cxx +++ b/sc/source/ui/view/cellsh2.cxx @@ -1196,6 +1196,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 dbe65f04327924e0d3c7bcefab5bbfba07687033 Author: Balazs Varga <[email protected]> AuthorDate: Thu Oct 30 14:14:41 2025 +0100 Commit: Balazs Varga <[email protected]> CommitDate: Fri Oct 31 16:28:38 2025 +0100 Table Style improvements: ooxml export of Total Row attributes. XML_totalsRowLabel, XML_totalsRowFunction, XML_totalsRowFormula and XML_totalsRowShown. Change-Id: I2b7c356cf71abe940ddbe6672c8fdb52d2513f79 Reviewed-on: https://gerrit.libreoffice.org/c/core/+/193206 Reviewed-by: Balazs Varga <[email protected]> Tested-by: Balazs Varga <[email protected]> diff --git a/sc/inc/dbdata.hxx b/sc/inc/dbdata.hxx index 7d44abcb0b41..800a09157d63 100644 --- a/sc/inc/dbdata.hxx +++ b/sc/inc/dbdata.hxx @@ -244,10 +244,17 @@ public: SC_DLLPUBLIC void GetSubTotalParam(ScSubTotalParam& rSubTotalParam) const; SC_DLLPUBLIC void SetSubTotalParam(const ScSubTotalParam& rSubTotalParam); - SC_DLLPUBLIC void ImportSubTotalParam(ScSubTotalParam& rSubTotalParam, + + // 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 CreateSubTotalParam(ScSubTotalParam& rSubTotalParam) const; + SC_DLLPUBLIC void CreateTotalRowParam(ScSubTotalParam& rSubTotalParam) const; + + SC_DLLPUBLIC const 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); @@ -283,6 +290,7 @@ public: SC_DLLPUBLIC const ScTableStyleParam* GetTableStyleInfo() const; static ScSubTotalFunc GetSubTotalFuncFromString(std::u16string_view sFunction); + static OUString GetStringFromSubTotalFunc(ScSubTotalFunc eFunc); private: diff --git a/sc/source/core/data/table3.cxx b/sc/source/core/data/table3.cxx index be8931c3ab1e..c976be11bd33 100644 --- a/sc/source/core/data/table3.cxx +++ b/sc/source/core/data/table3.cxx @@ -2305,9 +2305,9 @@ bool ScTable::DoSubTotals( ScSubTotalParam& rParam ) 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; diff --git a/sc/source/core/tool/dbdata.cxx b/sc/source/core/tool/dbdata.cxx index a1a0f30ccc1e..d90c912b2f54 100644 --- a/sc/source/core/tool/dbdata.cxx +++ b/sc/source/core/tool/dbdata.cxx @@ -690,7 +690,7 @@ void ScDBData::SetSubTotalParam(const ScSubTotalParam& rSubTotalParam) mpSubTotal.reset(new ScSubTotalParam(rSubTotalParam)); } -void ScDBData::ImportSubTotalParam(ScSubTotalParam& rSubTotalParam, +void ScDBData::ImportTotalRowParam(ScSubTotalParam& rSubTotalParam, const std::vector<TableColumnAttributes>& rAttributesVector, formula::FormulaGrammar::Grammar eGrammar) const { @@ -767,7 +767,7 @@ void ScDBData::ImportSubTotalParam(ScSubTotalParam& rSubTotalParam, } } -void ScDBData::CreateSubTotalParam(ScSubTotalParam& rSubTotalParam) const +void ScDBData::CreateTotalRowParam(ScSubTotalParam& rSubTotalParam) const { rSubTotalParam.bDoSort = false; rSubTotalParam.bGroupedBy = false; @@ -810,6 +810,209 @@ void ScDBData::CreateSubTotalParam(ScSubTotalParam& rSubTotalParam) const } } +const std::vector<TableColumnAttributes> ScDBData::GetTotalRowAttributes(formula::FormulaGrammar::Grammar eGrammar) const +{ + ScSubTotalParam rParam; + GetSubTotalParam(rParam); + + const size_t 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) + { + 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"); + } + } + } + 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"); + } + } + + // 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) + { + if (nIdx == 2) // { ocPush, formula::svDouble } + { + sal_Int32 nFunc = static_cast<sal_Int32>(t->GetDouble()); + if (nFunc > 100.) + nFunc -= 100; + + if (nFunc < 1 || nFunc > 11) + { + return OUString("custom"); + } + else + eSubType = static_cast<ScSubTotalFunc>(nFunc); + } + else if (nIdx == 4) // { ocTableRef, formula::svIndex } + { + sal_uInt16 nDbIndex = t->GetIndex(); + if (GetIndex() != nDbIndex) + { + return OUString("custom"); + } + } + else if (nIdx == 6) // { ocPush, formula::svSingleRef } + { + const ScSingleRefData* pRef = t->GetSingleRef(); + if (!(pRef && pRef->Col() == nCol && pRef->Row() == nHeaderRow)) + { + return OUString("custom"); + } + } + else + { /*Nothing to do*/ + } + + ++nIdx; + } + else + { + return OUString("custom"); + } + } + + return GetStringFromSubTotalFunc(eSubType); +} + void ScDBData::GetImportParam(ScImportParam& rImportParam) const { rImportParam = *mpImportParam; @@ -1342,29 +1545,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 OUString("sum"); + else if (eFunc == SUBTOTAL_FUNC_CNT) + return OUString("countNums"); + else if (eFunc == SUBTOTAL_FUNC_CNT2) + return OUString("count"); + else if (eFunc == SUBTOTAL_FUNC_PROD) + return OUString("custom"); // ooxml not support in Total row + else if (eFunc == SUBTOTAL_FUNC_AVE) + return OUString("average"); + else if (eFunc == SUBTOTAL_FUNC_MED) + return OUString("custom"); // ooxml not support in Total row + else if (eFunc == SUBTOTAL_FUNC_MAX) + return OUString("max"); + else if (eFunc == SUBTOTAL_FUNC_MIN) + return OUString("min"); + else if (eFunc == SUBTOTAL_FUNC_STD) + return OUString("stdDev"); + else if (eFunc == SUBTOTAL_FUNC_STDP) + return OUString("custom"); // ooxml not support in Total row + else if (eFunc == SUBTOTAL_FUNC_VAR) + return OUString("var"); + else + return OUString("none"); } namespace { diff --git a/sc/source/filter/excel/xedbdata.cxx b/sc/source/filter/excel/xedbdata.cxx index ecc715b1acbd..a77f0d10e938 100644 --- a/sc/source/filter/excel/xedbdata.cxx +++ b/sc/source/filter/excel/xedbdata.cxx @@ -181,6 +181,19 @@ void XclExpTables::SaveTableXml( XclExpXmlStream& rStrm, const Entry& rEntry ) ScRange aRange( ScAddress::UNINITIALIZED); rData.GetArea( aRange); sax_fastparser::FSHelperPtr& pTableStrm = rStrm.GetCurrentStream(); + + 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), @@ -189,7 +202,7 @@ void XclExpTables::SaveTableXml( XclExpXmlStream& rStrm, const Entry& rEntry ) XML_ref, XclXmlUtils::ToOString(rStrm.GetRoot().GetDoc(), aRange), 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, ..., @@ -224,6 +237,10 @@ void XclExpTables::SaveTableXml( XclExpXmlStream& rStrm, const Entry& rEntry ) const std::vector< OUString >& rColNames = rData.GetTableColumnNames(); 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)); @@ -233,13 +250,31 @@ 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. - - pTableStrm->singleElement( XML_tableColumn, + if (i < aTotalValues.size() && aTotalValues[i].maTotalsFunction.has_value() + && aTotalValues[i].maTotalsFunction.value() == u"custom") + { + pTableStrm->startElement( XML_tableColumn, XML_id, OString::number(i+1), - 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_name, rColNames[i].toUtf8(), + XML_totalsRowLabel, (i < aTotalValues.size() ? aTotalValues[i].maTotalsRowLabel : std::nullopt), + XML_totalsRowFunction, (i < aTotalValues.size() ? aTotalValues[i].maTotalsFunction : std::nullopt) + // OOXTODO... + ); + + // write custom functions + pTableStrm->startElement(XML_totalsRowFormula); + pTableStrm->writeEscaped(aTotalValues[i].maCustomFunction.value()); + pTableStrm->endElement(XML_totalsRowFormula); + + pTableStrm->endElement(XML_tableColumn); + } + else + { + pTableStrm->singleElement( XML_tableColumn, + XML_id, OString::number(i+1), + XML_name, rColNames[i].toUtf8(), + 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, ..., @@ -248,7 +283,8 @@ void XclExpTables::SaveTableXml( XclExpXmlStream& rStrm, const Entry& rEntry ) // OOXTODO: XML_totalsRowCellStyle, ..., // OOXTODO: XML_totalsRowDxfId, ..., // OOXTODO: XML_uniqueName, ... - ); + ); + } } pTableStrm->endElement( XML_tableColumns); diff --git a/sc/source/filter/oox/tablecolumnsbuffer.cxx b/sc/source/filter/oox/tablecolumnsbuffer.cxx index 447ec0c976a2..f909251510ed 100644 --- a/sc/source/filter/oox/tablecolumnsbuffer.cxx +++ b/sc/source/filter/oox/tablecolumnsbuffer.cxx @@ -132,12 +132,12 @@ bool TableColumns::finalizeImport( ScDBData* pDBData ) pDBData->SetTableColumnNames( std::move(aNames) ); // Import subtotal parameters for columns - if (hasAnySetValue && !pDBData->HasTotals()) + if (hasAnySetValue) { ScSubTotalParam aSubTotalParam; pDBData->GetSubTotalParam(aSubTotalParam); aSubTotalParam.bHasHeader = pDBData->HasHeader(); - pDBData->ImportSubTotalParam(aSubTotalParam, aAttributes, + pDBData->ImportTotalRowParam(aSubTotalParam, aAttributes, formula::FormulaGrammar::GRAM_OOXML); pDBData->SetSubTotalParam(aSubTotalParam); } diff --git a/sc/source/ui/view/gridwin.cxx b/sc/source/ui/view/gridwin.cxx index 1917a5e9b0af..4a464545d69a 100644 --- a/sc/source/ui/view/gridwin.cxx +++ b/sc/source/ui/view/gridwin.cxx @@ -2485,7 +2485,7 @@ void ScGridWindow::MouseButtonUp( const MouseEvent& rMEvt ) pDBData->GetSubTotalParam(aSubTotalParam); aSubTotalParam.bHasHeader = aNewDBData.HasHeader(); // store current subtotal settings - pDBData->CreateSubTotalParam(aSubTotalParam); + pDBData->CreateTotalRowParam(aSubTotalParam); aNewDBData.SetSubTotalParam(aSubTotalParam); // add/replace total row aSubTotalParam.bRemoveOnly = false; diff --git a/sc/source/ui/view/tableshell.cxx b/sc/source/ui/view/tableshell.cxx index df51311df8f2..edbadacfa26b 100644 --- a/sc/source/ui/view/tableshell.cxx +++ b/sc/source/ui/view/tableshell.cxx @@ -101,10 +101,9 @@ void ScTableShell::ExecuteDatabaseSettings(SfxRequest& rReq) if (!aNewDBData.HasTotals()) { - // store current subtotal settings - pDBData->CreateSubTotalParam(aSubTotalParam); + // store current subtotal settings before removing total row + pDBData->CreateTotalRowParam(aSubTotalParam); aNewDBData.SetSubTotalParam(aSubTotalParam); - // remove total row aSubTotalParam.bRemoveOnly = true; aSubTotalParam.bReplace = true; aFunc.DoTableSubTotals(aNewDBData.GetTab(), aNewDBData, aSubTotalParam,
