offapi/com/sun/star/sheet/Spreadsheet.idl | 13 ++
oox/source/token/properties.txt | 1
sc/inc/document.hxx | 2
sc/inc/subtotalparam.hxx | 1
sc/inc/table.hxx | 4
sc/inc/unonames.hxx | 1
sc/inc/unowids.hxx | 3
sc/qa/uitest/data/tdf162262.ods |binary
sc/qa/uitest/sort/subtotals.py | 109 ++++++++++++++++++++
sc/qa/unit/data/xlsx/subtotal-above.xlsx |binary
sc/qa/unit/subsequent_export_test4.cxx | 12 ++
sc/source/core/data/document.cxx | 19 +++
sc/source/core/data/subtotalparam.cxx | 6 -
sc/source/core/data/table3.cxx | 79 ++++++++++----
sc/source/filter/excel/excdoc.cxx | 3
sc/source/filter/excel/excrecds.cxx | 10 +
sc/source/filter/inc/excrecds.hxx | 3
sc/source/filter/oox/worksheetsettings.cxx | 6 +
sc/source/ui/dbgui/tpsubt.cxx | 6 +
sc/source/ui/inc/tpsubt.hxx | 1
sc/source/ui/unoobj/cellsuno.cxx | 6 +
sc/source/ui/view/cellsh1.cxx | 6 +
sc/uiconfig/scalc/ui/subtotaloptionspage.ui | 152 +++++++++++++++-------------
23 files changed, 346 insertions(+), 97 deletions(-)
New commits:
commit 26c08356d1b2a963efdca570979cb04388371400
Author: Balazs Varga <balazs.varga.ext...@allotropia.de>
AuthorDate: Thu Oct 24 14:17:46 2024 +0200
Commit: Balazs Varga <balazs.varga.ext...@allotropia.de>
CommitDate: Mon Oct 28 13:28:21 2024 +0100
tdf#162262 sc add "Summary below data" option for Subtotal dialog
With this option we can set where the summary rows should appear,
above or below the datas when we create a new Subtotal area. The default
option is "True" (which means the summary rows are below the datas)
when we create a new subtotal area. Unless if we already have one subtotal
area on the same sheet, in that case the sheet level property,
"TotalsRowBelow"
contains where should the summary rows be for the new Subtotal's.
TODO: add new ODF xml attribute for Summary below
Change-Id: Icf86c85041d75c24919cb528846d5bb2b517ca78
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/175556
Tested-by: Jenkins
Reviewed-by: Balazs Varga <balazs.varga.ext...@allotropia.de>
diff --git a/offapi/com/sun/star/sheet/Spreadsheet.idl
b/offapi/com/sun/star/sheet/Spreadsheet.idl
index d1a1dfcd0f18..605055c7b4ec 100644
--- a/offapi/com/sun/star/sheet/Spreadsheet.idl
+++ b/offapi/com/sun/star/sheet/Spreadsheet.idl
@@ -169,6 +169,19 @@ service Spreadsheet
/** specifies all conditional formats of that sheet
*/
[optional, property] com::sun::star::sheet::XConditionalFormats
ConditionalFormats;
+
+ /** specifies whether summary rows appear below detail in an outline,
+ when applying an outline.
+
+ <p> When true a summary row is inserted below the detailed data being
+ summarized and a new outline level is established on that row.</p>
+
+ <p> When false a summary row is inserted above the detailed data being
+ summarized and a new outline level is established on that row.</p>
+
+ @since LibreOffice 25.2
+ */
+ [optional, property] boolean TotalsRowBelow;
};
diff --git a/oox/source/token/properties.txt b/oox/source/token/properties.txt
index 6140d192b55d..0cb978129ccc 100644
--- a/oox/source/token/properties.txt
+++ b/oox/source/token/properties.txt
@@ -613,6 +613,7 @@ TopBorderComplexColor
TopBorderDistance
TopMargin
TotalsRow
+TotalsRowBelow
Transformation
TransitionDirection
TransitionDuration
diff --git a/sc/inc/document.hxx b/sc/inc/document.hxx
index 43a08fcdc7b0..bf8eaa34f2f2 100644
--- a/sc/inc/document.hxx
+++ b/sc/inc/document.hxx
@@ -982,6 +982,8 @@ public:
SC_DLLPUBLIC bool GetName( SCTAB nTab, OUString& rName ) const;
SC_DLLPUBLIC bool GetCodeName( SCTAB nTab, OUString& rName ) const;
SC_DLLPUBLIC bool SetCodeName( SCTAB nTab, const OUString& rName );
+ SC_DLLPUBLIC bool GetTotalsRowBelow( SCTAB nTab ) const;
+ SC_DLLPUBLIC bool SetTotalsRowBelow( SCTAB nTab, bool bVal );
SC_DLLPUBLIC bool GetTable( const OUString& rName, SCTAB& rTab ) const;
SC_DLLPUBLIC SCCOL MaxCol() const { return mxSheetLimits->mnMaxCol; }
SC_DLLPUBLIC SCROW MaxRow() const { return mxSheetLimits->mnMaxRow; }
diff --git a/sc/inc/subtotalparam.hxx b/sc/inc/subtotalparam.hxx
index 8e36dad83987..3b379edb167d 100644
--- a/sc/inc/subtotalparam.hxx
+++ b/sc/inc/subtotalparam.hxx
@@ -24,6 +24,7 @@ struct SC_DLLPUBLIC ScSubTotalParam
bool bPagebreak:1; ///< page break at change of
group
bool bCaseSens:1;
bool bDoSort:1; ///< presort
+ bool bSummaryBelow:1; ///< Summary below or above
(default: below)
bool bAscending:1; ///< sort ascending
bool bUserDef:1; ///< sort user defined
bool bIncludePattern:1; ///< sort formats
diff --git a/sc/inc/table.hxx b/sc/inc/table.hxx
index 7da55e4cb3ee..b70f77c95445 100644
--- a/sc/inc/table.hxx
+++ b/sc/inc/table.hxx
@@ -253,6 +253,7 @@ private:
bool bActiveScenario:1;
bool mbPageBreaksValid:1;
bool mbForceBreaks:1;
+ bool mbTotalsRowBelow:1;
/** this is touched from formula group threading context */
std::atomic<bool> bStreamValid;
@@ -394,6 +395,9 @@ public:
const OUString& GetCodeName() const { return aCodeName; }
void SetCodeName( const OUString& rNewName ) { aCodeName =
rNewName; }
+ bool GetTotalsRowBelow() const { return mbTotalsRowBelow; }
+ void SetTotalsRowBelow( bool bNewVal ) { mbTotalsRowBelow =
bNewVal; }
+
const OUString& GetUpperName() const;
const OUString& GetPageStyle() const { return
aPageStyle; }
diff --git a/sc/inc/unonames.hxx b/sc/inc/unonames.hxx
index 916fd839d4b7..0d8fd9dbd696 100644
--- a/sc/inc/unonames.hxx
+++ b/sc/inc/unonames.hxx
@@ -192,6 +192,7 @@ inline constexpr OUString SC_UNONAME_TABLAYOUT =
u"TableLayout"_ustr;
inline constexpr OUString SC_UNONAME_AUTOPRINT =
u"AutomaticPrintArea"_ustr;
inline constexpr OUString SC_UNONAME_TABCOLOR = u"TabColor"_ustr;
inline constexpr OUString SC_UNONAME_CONDFORMAT =
u"ConditionalFormats"_ustr;
+inline constexpr OUString SC_UNONAME_TOTALBELOW = u"TotalsRowBelow"_ustr;
inline constexpr OUString SC_UNONAME_VISFLAG = u"VisibleFlag"_ustr;
diff --git a/sc/inc/unowids.hxx b/sc/inc/unowids.hxx
index b5de8f1f292c..389b2dda0da2 100644
--- a/sc/inc/unowids.hxx
+++ b/sc/inc/unowids.hxx
@@ -75,7 +75,8 @@
#define SC_WID_UNO_FORMRT2 ( SC_WID_UNO_START + 46 )
#define SC_WID_UNO_CELLCONTENTTYPE ( SC_WID_UNO_START + 47 )
#define SC_WID_UNO_SOLVERSETTINGS ( SC_WID_UNO_START + 48 )
-#define SC_WID_UNO_END ( SC_WID_UNO_START + 48 )
+#define SC_WID_UNO_TOTALBELOW ( SC_WID_UNO_START + 49 )
+#define SC_WID_UNO_END ( SC_WID_UNO_START + 49 )
inline bool IsScUnoWid( sal_uInt16 nWid )
{
diff --git a/sc/qa/uitest/data/tdf162262.ods b/sc/qa/uitest/data/tdf162262.ods
new file mode 100644
index 000000000000..138348366298
Binary files /dev/null and b/sc/qa/uitest/data/tdf162262.ods differ
diff --git a/sc/qa/uitest/sort/subtotals.py b/sc/qa/uitest/sort/subtotals.py
index b824dcab98c8..6eddd1bc03b4 100644
--- a/sc/qa/uitest/sort/subtotals.py
+++ b/sc/qa/uitest/sort/subtotals.py
@@ -137,4 +137,113 @@ class Subtotals(UITestCase):
self.assertEqual(get_cell_by_position(calc_doc, 0, 0,
1).getValue(), 1)
self.assertEqual(get_cell_by_position(calc_doc, 0, 1,
1).getValue(), 2)
+ def test_tdf162262(self):
+ with self.ui_test.load_file(get_url_for_data_file("tdf162262.ods")) as
calc_doc:
+ XcalcDoc = self.xUITest.getTopFocusWindow()
+ gridwin = XcalcDoc.getChild("grid_window")
+ # One group level
+ # Select cell range
+ gridwin.executeAction("SELECT", mkPropertyValues({"RANGE":
"A1:C15"}))
+ # Select from the menu bar Data
+ # Select option subtotal
+ # Subtotal dialog displays
+ with
self.ui_test.execute_dialog_through_command(".uno:DataSubTotals") as xDialog:
+ # Select group by: Day
+ xGroupBy = xDialog.getChild("group_by1")
+ select_by_text(xGroupBy, "Day")
+ # Select 'Calculate subtotals for' -> Value 1 and Value 2
+ xCheckListMenu = xDialog.getChild("grid1")
+ xTreeList = xCheckListMenu.getChild("columns1")
+ xFirstEntry = xTreeList.getChild("1")
+ xFirstEntry.executeAction("CLICK", tuple())
+ xFirstEntry = xTreeList.getChild("2")
+ xFirstEntry.executeAction("CLICK", tuple())
+
+ # Select tab options
+ xTabs = xDialog.getChild("tabcontrol")
+ select_pos(xTabs, "3")
+ # Unselect option Summary below -> false
+ xSummarybelow = xDialog.getChild("summarybelow")
+ xSummarybelow.executeAction("CLICK", tuple())
+ # apply with OK
+
+ self.assertEqual(get_cell_by_position(calc_doc, 0, 0,
1).getString(), "Grand Sum")
+ self.assertEqual(get_cell_by_position(calc_doc, 0, 1,
1).getValue(), 105)
+ self.assertEqual(get_cell_by_position(calc_doc, 0, 2,
1).getValue(), 119)
+
+ self.assertEqual(get_cell_by_position(calc_doc, 0, 0,
2).getString(), "Friday Result")
+ self.assertEqual(get_cell_by_position(calc_doc, 0, 1,
2).getValue(), 19)
+ self.assertEqual(get_cell_by_position(calc_doc, 0, 2,
2).getValue(), 21)
+
+ self.assertEqual(get_cell_by_position(calc_doc, 0, 0,
20).getString(), "Wednesday Result")
+ self.assertEqual(get_cell_by_position(calc_doc, 0, 1,
20).getValue(), 11)
+ self.assertEqual(get_cell_by_position(calc_doc, 0, 2,
20).getValue(), 13)
+
+ def test_tdf162262_multi(self):
+ with self.ui_test.load_file(get_url_for_data_file("tdf162262.ods")) as
calc_doc:
+ XcalcDoc = self.xUITest.getTopFocusWindow()
+ gridwin = XcalcDoc.getChild("grid_window")
+ # Multi group level
+ # Select cell range
+ gridwin.executeAction("SELECT", mkPropertyValues({"RANGE":
"A1:C15"}))
+ # Select from the menu bar Data
+ # Select option subtotal
+ # Subtotal dialog displays
+ with
self.ui_test.execute_dialog_through_command(".uno:DataSubTotals") as xDialog:
+ # Select group by 1: Day
+ xGroupBy = xDialog.getChild("group_by1")
+ select_by_text(xGroupBy, "Day")
+ # Select 'Calculate subtotals for' -> Value 1
+ xCheckListMenu = xDialog.getChild("grid1")
+ xTreeList = xCheckListMenu.getChild("columns1")
+ # Select 1 column
+ xFirstEntry = xTreeList.getChild("1")
+ xFirstEntry.executeAction("CLICK", tuple())
+
+ # Select tab Group by 2
+ xTabs = xDialog.getChild("tabcontrol")
+ select_pos(xTabs, "1")
+
+ # Select group by 2: Day
+ xGroupBy = xDialog.getChild("group_by2")
+ select_by_text(xGroupBy, "Day")
+ # Select 'Calculate subtotals for' -> Value 2
+ xCheckListMenu = xDialog.getChild("grid2")
+ xTreeList = xCheckListMenu.getChild("columns2")
+ # Select second column
+ xFirstEntry = xTreeList.getChild("2")
+ xFirstEntry.executeAction("CLICK", tuple())
+
+ # Select tab options
+ xTabs = xDialog.getChild("tabcontrol")
+ select_pos(xTabs, "3")
+ # Unselect option Summary below -> false
+ xSummarybelow = xDialog.getChild("summarybelow")
+ xSummarybelow.executeAction("CLICK", tuple())
+ # apply with OK
+
+ self.assertEqual(get_cell_by_position(calc_doc, 0, 0,
1).getString(), "Grand Sum")
+ self.assertEqual(get_cell_by_position(calc_doc, 0, 1,
1).getValue(), 0)
+ self.assertEqual(get_cell_by_position(calc_doc, 0, 2,
1).getValue(), 119)
+
+ self.assertEqual(get_cell_by_position(calc_doc, 0, 0,
2).getString(), "Grand Sum")
+ self.assertEqual(get_cell_by_position(calc_doc, 0, 1,
2).getValue(), 105)
+ self.assertEqual(get_cell_by_position(calc_doc, 0, 2,
2).getValue(), 0)
+
+ self.assertEqual(get_cell_by_position(calc_doc, 0, 0,
3).getString(), "Friday Sum")
+ self.assertEqual(get_cell_by_position(calc_doc, 0, 1,
3).getValue(), 0)
+ self.assertEqual(get_cell_by_position(calc_doc, 0, 2,
3).getValue(), 21)
+
+ self.assertEqual(get_cell_by_position(calc_doc, 0, 0,
4).getString(), "Friday Sum")
+ self.assertEqual(get_cell_by_position(calc_doc, 0, 1,
4).getValue(), 19)
+ self.assertEqual(get_cell_by_position(calc_doc, 0, 2,
4).getValue(), 0)
+
+ self.assertEqual(get_cell_by_position(calc_doc, 0, 0,
27).getString(), "Wednesday Sum")
+ self.assertEqual(get_cell_by_position(calc_doc, 0, 1,
27).getValue(), 0)
+ self.assertEqual(get_cell_by_position(calc_doc, 0, 2,
27).getValue(), 13)
+
+ self.assertEqual(get_cell_by_position(calc_doc, 0, 0,
28).getString(), "Wednesday Sum")
+ self.assertEqual(get_cell_by_position(calc_doc, 0, 1,
28).getValue(), 11)
+ self.assertEqual(get_cell_by_position(calc_doc, 0, 2,
28).getValue(), 0)
+
# vim: set shiftwidth=4 softtabstop=4 expandtab:
diff --git a/sc/qa/unit/data/xlsx/subtotal-above.xlsx
b/sc/qa/unit/data/xlsx/subtotal-above.xlsx
new file mode 100644
index 000000000000..b271dbab19be
Binary files /dev/null and b/sc/qa/unit/data/xlsx/subtotal-above.xlsx differ
diff --git a/sc/qa/unit/subsequent_export_test4.cxx
b/sc/qa/unit/subsequent_export_test4.cxx
index e6b5a6389af3..fdb3b6f9a876 100644
--- a/sc/qa/unit/subsequent_export_test4.cxx
+++ b/sc/qa/unit/subsequent_export_test4.cxx
@@ -398,6 +398,18 @@ CPPUNIT_TEST_FIXTURE(ScExportTest4, testTdf81470)
assertXPath(pHeaders, "/x:headers/x:header[3]", "userName", u"Kohei
Yoshida");
}
+CPPUNIT_TEST_FIXTURE(ScExportTest4, testTdf162262)
+{
+ createScDoc("xlsx/subtotal-above.xlsx");
+
+ save(u"Calc Office Open XML"_ustr);
+
+ xmlDocUniquePtr pSheet = parseExport(u"xl/worksheets/sheet1.xml"_ustr);
+ CPPUNIT_ASSERT(pSheet);
+
+ assertXPath(pSheet, "/x:worksheet/x:sheetPr/x:outlinePr", "summaryBelow",
u"0");
+}
+
CPPUNIT_TEST_FIXTURE(ScExportTest4, testTdf122331)
{
createScDoc("ods/tdf122331.ods");
diff --git a/sc/source/core/data/document.cxx b/sc/source/core/data/document.cxx
index f97fca6ef8a9..dbcdbe089163 100644
--- a/sc/source/core/data/document.cxx
+++ b/sc/source/core/data/document.cxx
@@ -228,6 +228,25 @@ bool ScDocument::GetCodeName( SCTAB nTab, OUString& rName
) const
return false;
}
+bool ScDocument::SetTotalsRowBelow( SCTAB nTab, bool bVal )
+{
+ if (ScTable* pTable = FetchTable(nTab))
+ {
+ pTable->SetTotalsRowBelow(bVal);
+ return true;
+ }
+ return false;
+}
+
+bool ScDocument::GetTotalsRowBelow( SCTAB nTab ) const
+{
+ if (const ScTable* pTable = FetchTable(nTab))
+ {
+ return pTable->GetTotalsRowBelow();
+ }
+ return true;
+}
+
bool ScDocument::GetTable( const OUString& rName, SCTAB& rTab ) const
{
static OUString aCacheName, aCacheUpperName;
diff --git a/sc/source/core/data/subtotalparam.cxx
b/sc/source/core/data/subtotalparam.cxx
index e8f32954297c..6fd8e18c6b0e 100644
--- a/sc/source/core/data/subtotalparam.cxx
+++ b/sc/source/core/data/subtotalparam.cxx
@@ -26,7 +26,7 @@ ScSubTotalParam::ScSubTotalParam()
ScSubTotalParam::ScSubTotalParam( const ScSubTotalParam& r ) :
nCol1(r.nCol1),nRow1(r.nRow1),nCol2(r.nCol2),nRow2(r.nRow2),nUserIndex(r.nUserIndex),
bRemoveOnly(r.bRemoveOnly),bReplace(r.bReplace),bPagebreak(r.bPagebreak),bCaseSens(r.bCaseSens),
- bDoSort(r.bDoSort),bAscending(r.bAscending),bUserDef(r.bUserDef),
+ bDoSort(r.bDoSort), bSummaryBelow(r.bSummaryBelow),
bAscending(r.bAscending), bUserDef(r.bUserDef),
bIncludePattern(r.bIncludePattern)
{
for (sal_uInt16 i=0; i<MAXSUBTOTAL; i++)
@@ -59,7 +59,7 @@ void ScSubTotalParam::Clear()
nRow1=nRow2 = 0;
nUserIndex = 0;
bPagebreak=bCaseSens=bUserDef=bIncludePattern=bRemoveOnly = false;
- bAscending=bReplace=bDoSort = true;
+ bAscending=bReplace=bDoSort=bSummaryBelow = true;
for (sal_uInt16 i=0; i<MAXSUBTOTAL; i++)
{
@@ -90,6 +90,7 @@ ScSubTotalParam& ScSubTotalParam::operator=( const
ScSubTotalParam& r )
bPagebreak = r.bPagebreak;
bCaseSens = r.bCaseSens;
bDoSort = r.bDoSort;
+ bSummaryBelow = r.bSummaryBelow;
bAscending = r.bAscending;
bUserDef = r.bUserDef;
nUserIndex = r.nUserIndex;
@@ -135,6 +136,7 @@ bool ScSubTotalParam::operator==( const ScSubTotalParam&
rOther ) const
&& (bReplace == rOther.bReplace)
&& (bPagebreak == rOther.bPagebreak)
&& (bDoSort == rOther.bDoSort)
+ && (bSummaryBelow == rOther.bSummaryBelow)
&& (bCaseSens == rOther.bCaseSens)
&& (bAscending == rOther.bAscending)
&& (bUserDef == rOther.bUserDef)
diff --git a/sc/source/core/data/table3.cxx b/sc/source/core/data/table3.cxx
index d022ba2c4473..fed72506c2a3 100644
--- a/sc/source/core/data/table3.cxx
+++ b/sc/source/core/data/table3.cxx
@@ -2060,9 +2060,10 @@ bool ScTable::DoSubTotals( ScSubTotalParam& rParam )
if (nResCount > 0) // otherwise
only sort
{
+ SCROW nAboveRows = rParam.bSummaryBelow ? nStartRow : nStartRow +
nLevel;
for (sal_uInt16 i = 0; i <= aRowEntry.nGroupNo; ++i)
{
- aSubString = GetString( nGroupCol[i], nStartRow );
+ aSubString = GetString( nGroupCol[i], nAboveRows );
if ( bIgnoreCase )
aCompString[i] = ScGlobal::getCharClass().uppercase(
aSubString );
else
@@ -2070,8 +2071,8 @@ bool ScTable::DoSubTotals( ScSubTotalParam& rParam )
} // aSubString
stays on the last
bool bBlockVis = false; // group visible?
- aRowEntry.nSubStartRow = nStartRow;
- for (SCROW nRow=nStartRow; nRow<=nEndRow+1 && bSpaceLeft; nRow++)
+ aRowEntry.nSubStartRow = nAboveRows;
+ for (SCROW nRow=nAboveRows; nRow<=nEndRow+1 && bSpaceLeft; nRow++)
{
bool bChanged;
if (nRow>nEndRow)
@@ -2099,9 +2100,21 @@ bool ScTable::DoSubTotals( ScSubTotalParam& rParam )
}
if ( bChanged )
{
- aRowEntry.nDestRow = nRow;
- aRowEntry.nFuncStart = aRowEntry.nSubStartRow;
- aRowEntry.nFuncEnd = nRow-1;
+ if (rParam.bSummaryBelow)
+ {
+ aRowEntry.nDestRow = nRow;
+ aRowEntry.nFuncStart = aRowEntry.nSubStartRow;
+ aRowEntry.nFuncEnd = nRow - 1;
+ }
+ else
+ {
+ aRowEntry.nDestRow = aRowEntry.nSubStartRow;
+ aRowEntry.nFuncStart = aRowEntry.nSubStartRow + 1;
+ if (nRow != nEndRow + 1)
+ aRowEntry.nFuncEnd = nRow - nLevel;
+ else
+ aRowEntry.nFuncEnd = nRow;
+ }
bSpaceLeft = rDocument.InsertRow( 0, nTab,
rDocument.MaxCol(), nTab,
aRowEntry.nDestRow, 1 );
@@ -2157,17 +2170,27 @@ bool ScTable::DoSubTotals( ScSubTotalParam& rParam )
if (!aRowVector.empty())
{
- // generate global total
- SCROW nGlobalStartRow = aRowVector[0].nSubStartRow;
- SCROW nGlobalStartFunc = aRowVector[0].nFuncStart;
SCROW nGlobalEndRow = 0;
SCROW nGlobalEndFunc = 0;
- for (const auto& rRowEntry : aRowVector)
+ for (auto& rRowEntry : aRowVector)
{
+ if (!rParam.bSummaryBelow)
+ {
+ // if we have Global summary above, we need to shift summary
rows down
+ rRowEntry.nDestRow = rRowEntry.nDestRow + nLevelCount;
+ rRowEntry.nFuncEnd = rRowEntry.nFuncEnd + nLevelCount;
+ rRowEntry.nFuncStart = rRowEntry.nFuncStart + nLevelCount -
rRowEntry.nGroupNo;
+ rRowEntry.nSubStartRow = rRowEntry.nSubStartRow + nLevelCount;
+ }
+
nGlobalEndRow = (nGlobalEndRow < rRowEntry.nDestRow) ?
rRowEntry.nDestRow : nGlobalEndRow;
nGlobalEndFunc = (nGlobalEndFunc < rRowEntry.nFuncEnd) ?
rRowEntry.nFuncEnd : nGlobalEndRow;
}
+ // generate global total
+ SCROW nGlobalStartRow = aRowVector[0].nSubStartRow;
+ SCROW nGlobalStartFunc = aRowVector[0].nFuncStart;
+
for (sal_uInt16 nLevel = 0; nLevel<nLevelCount; nLevel++)
{
const sal_uInt16 nGroupNo = nLevelCount - nLevel - 1;
@@ -2179,18 +2202,30 @@ bool ScTable::DoSubTotals( ScSubTotalParam& rParam )
continue;
}
- // increment end row
- nGlobalEndRow++;
-
- // add row entry for formula
- aRowEntry.nGroupNo = nGroupNo;
- aRowEntry.nSubStartRow = nGlobalStartRow;
- aRowEntry.nFuncStart = nGlobalStartFunc;
- aRowEntry.nDestRow = nGlobalEndRow;
- aRowEntry.nFuncEnd = nGlobalEndFunc;
-
- // increment row
- nGlobalEndFunc++;
+ if (rParam.bSummaryBelow)
+ {
+ // increment end row
+ nGlobalEndRow++;
+
+ // add row entry for formula
+ aRowEntry.nGroupNo = nGroupNo;
+ aRowEntry.nSubStartRow = nGlobalStartRow;
+ aRowEntry.nFuncStart = nGlobalStartFunc;
+ aRowEntry.nDestRow = nGlobalEndRow;
+ aRowEntry.nFuncEnd = nGlobalEndFunc;
+
+ // increment row
+ nGlobalEndFunc++;
+ }
+ else
+ {
+ // if we have Global summary we need to shift summary rows down
+ aRowEntry.nGroupNo = nGroupNo;
+ aRowEntry.nSubStartRow = nGlobalStartRow - nGroupNo - 1;
+ aRowEntry.nFuncStart = nGlobalStartFunc - nGroupNo - 1;
+ aRowEntry.nDestRow = nGlobalStartRow - nGroupNo - 1;
+ aRowEntry.nFuncEnd = nGlobalEndFunc;
+ }
bSpaceLeft = rDocument.InsertRow(0, nTab, rDocument.MaxCol(),
nTab, aRowEntry.nDestRow, 1);
diff --git a/sc/source/filter/excel/excdoc.cxx
b/sc/source/filter/excel/excdoc.cxx
index 847e6d457cbf..7907a87223c0 100644
--- a/sc/source/filter/excel/excdoc.cxx
+++ b/sc/source/filter/excel/excdoc.cxx
@@ -586,8 +586,9 @@ void ExcTable::FillAsTableXml()
XclExtLstRef xExtLst = new XclExtLst( GetRoot() );
bool bFitToPages = xPageSett->GetPageData().mbFitToPages;
+ bool bSummaryBelow = GetRoot().GetDoc().GetTotalsRowBelow(mnScTab);
Color aTabColor = GetRoot().GetDoc().GetTabBgColor(mnScTab);
- Add(new XclExpXmlSheetPr(bFitToPages, mnScTab, aTabColor,
&GetFilterManager()));
+ Add(new XclExpXmlSheetPr(bFitToPages, mnScTab, aTabColor, bSummaryBelow,
&GetFilterManager()));
// GUTS (count & size of outline icons)
aRecList.AppendRecord( mxCellTable->CreateRecord( EXC_ID_GUTS ) );
diff --git a/sc/source/filter/excel/excrecds.cxx
b/sc/source/filter/excel/excrecds.cxx
index 381836c10c12..afd38447d865 100644
--- a/sc/source/filter/excel/excrecds.cxx
+++ b/sc/source/filter/excel/excrecds.cxx
@@ -368,8 +368,8 @@ XclExpWsbool::XclExpWsbool( bool bFitToPages )
SetValue( GetValue() | EXC_WSBOOL_FITTOPAGE );
}
-XclExpXmlSheetPr::XclExpXmlSheetPr( bool bFitToPages, SCTAB nScTab, const
Color& rTabColor, XclExpFilterManager* pManager ) :
- mnScTab(nScTab), mpManager(pManager), mbFitToPage(bFitToPages),
maTabColor(rTabColor) {}
+XclExpXmlSheetPr::XclExpXmlSheetPr( bool bFitToPages, SCTAB nScTab, const
Color& rTabColor, bool bSummaryBelow, XclExpFilterManager* pManager ) :
+ mnScTab(nScTab), mpManager(pManager), mbFitToPage(bFitToPages),
maTabColor(rTabColor), mbSummaryBelow(bSummaryBelow) {}
void XclExpXmlSheetPr::SaveXml( XclExpXmlStream& rStrm )
{
@@ -388,11 +388,13 @@ void XclExpXmlSheetPr::SaveXml( XclExpXmlStream& rStrm )
// Note : the order of child elements is significant. Don't change the
order.
- // OOXTODO: XML_outlinePr
-
if (maTabColor != COL_AUTO)
rWorksheet->singleElement(XML_tabColor, XML_rgb,
XclXmlUtils::ToOString(maTabColor));
+ // OOXTODO: XML_outlinePr --> XML_applyStyles, XML_showOutlineSymbols,
XML_summaryBelow, XML_summaryRight
+ if (!mbSummaryBelow)
+ rWorksheet->singleElement(XML_outlinePr, XML_summaryBelow, "0");
+
rWorksheet->singleElement(XML_pageSetUpPr,
// OOXTODO: XML_autoPageBreaks,
XML_fitToPage, ToPsz(mbFitToPage));
diff --git a/sc/source/filter/inc/excrecds.hxx
b/sc/source/filter/inc/excrecds.hxx
index c7ab0aa96bd2..629ddfd18b5a 100644
--- a/sc/source/filter/inc/excrecds.hxx
+++ b/sc/source/filter/inc/excrecds.hxx
@@ -304,7 +304,7 @@ class XclExpXmlSheetPr : public XclExpRecordBase
{
public:
explicit XclExpXmlSheetPr(
- bool bFitToPages, SCTAB nScTab, const Color& rTabColor,
XclExpFilterManager* pManager );
+ bool bFitToPages, SCTAB nScTab, const Color& rTabColor, bool
bSummaryBelow, XclExpFilterManager* pManager );
virtual void SaveXml( XclExpXmlStream& rStrm ) override;
@@ -313,6 +313,7 @@ private:
XclExpFilterManager* mpManager;
bool mbFitToPage;
Color maTabColor;
+ bool mbSummaryBelow;
};
class XclExpFiltermode : public XclExpEmptyRecord
diff --git a/sc/source/filter/oox/worksheetsettings.cxx
b/sc/source/filter/oox/worksheetsettings.cxx
index 988207aa9067..46d633044115 100644
--- a/sc/source/filter/oox/worksheetsettings.cxx
+++ b/sc/source/filter/oox/worksheetsettings.cxx
@@ -287,6 +287,12 @@ void WorksheetSettings::finalizeImport()
::Color nColor = maSheetSettings.maTabColor.getColor(
getBaseFilter().getGraphicHelper() );
aPropSet.setProperty( PROP_TabColor, nColor );
}
+
+ // Summary data below or above the contents
+ if ( !maSheetSettings.mbSummaryBelow )
+ {
+ aPropSet.setProperty( PROP_TotalsRowBelow, false );
+ }
}
} // namespace oox::xls
diff --git a/sc/source/ui/dbgui/tpsubt.cxx b/sc/source/ui/dbgui/tpsubt.cxx
index c657f482da34..e0fcf640f756 100644
--- a/sc/source/ui/dbgui/tpsubt.cxx
+++ b/sc/source/ui/dbgui/tpsubt.cxx
@@ -447,6 +447,7 @@ ScTpSubTotalOptions::ScTpSubTotalOptions(weld::Container*
pPage, weld::DialogCon
, m_xBtnPagebreak(m_xBuilder->weld_check_button(u"pagebreak"_ustr))
, m_xBtnCase(m_xBuilder->weld_check_button(u"case"_ustr))
, m_xBtnSort(m_xBuilder->weld_check_button(u"sort"_ustr))
+ , m_xBtnSummary(m_xBuilder->weld_check_button(u"summarybelow"_ustr))
, m_xFlSort(m_xBuilder->weld_label(u"label2"_ustr))
, m_xBtnAscending(m_xBuilder->weld_radio_button(u"ascending"_ustr))
, m_xBtnDescending(m_xBuilder->weld_radio_button(u"descending"_ustr))
@@ -490,6 +491,7 @@ void ScTpSubTotalOptions::Reset( const SfxItemSet* /*
rArgSet */ )
m_xBtnCase->set_active( rSubTotalData.bCaseSens );
m_xBtnFormats->set_active( rSubTotalData.bIncludePattern );
m_xBtnSort->set_active( rSubTotalData.bDoSort );
+ m_xBtnSummary->set_active( rSubTotalData.bSummaryBelow );
m_xBtnAscending->set_active( rSubTotalData.bAscending );
m_xBtnDescending->set_active( !rSubTotalData.bAscending );
@@ -524,6 +526,10 @@ bool ScTpSubTotalOptions::FillItemSet( SfxItemSet* rArgSet
)
theSubTotalData.bCaseSens = m_xBtnCase->get_active();
theSubTotalData.bIncludePattern = m_xBtnFormats->get_active();
theSubTotalData.bDoSort = m_xBtnSort->get_active();
+
+ theSubTotalData.bSummaryBelow = m_xBtnSummary->get_active();
+ pDoc->SetTotalsRowBelow(pViewData->GetTabNo(),
theSubTotalData.bSummaryBelow);
+
theSubTotalData.bAscending = m_xBtnAscending->get_active();
theSubTotalData.bUserDef = m_xBtnUserDef->get_active();
theSubTotalData.nUserIndex = (m_xBtnUserDef->get_active())
diff --git a/sc/source/ui/inc/tpsubt.hxx b/sc/source/ui/inc/tpsubt.hxx
index ecfa2ec18522..42eed5e4046e 100644
--- a/sc/source/ui/inc/tpsubt.hxx
+++ b/sc/source/ui/inc/tpsubt.hxx
@@ -135,6 +135,7 @@ private:
std::unique_ptr<weld::CheckButton> m_xBtnPagebreak;
std::unique_ptr<weld::CheckButton> m_xBtnCase;
std::unique_ptr<weld::CheckButton> m_xBtnSort;
+ std::unique_ptr<weld::CheckButton> m_xBtnSummary;
std::unique_ptr<weld::Label> m_xFlSort;
std::unique_ptr<weld::RadioButton> m_xBtnAscending;
std::unique_ptr<weld::RadioButton> m_xBtnDescending;
diff --git a/sc/source/ui/unoobj/cellsuno.cxx b/sc/source/ui/unoobj/cellsuno.cxx
index 9cc4117ed403..556edb426ba9 100644
--- a/sc/source/ui/unoobj/cellsuno.cxx
+++ b/sc/source/ui/unoobj/cellsuno.cxx
@@ -787,6 +787,7 @@ static const SfxItemPropertySet* lcl_GetSheetPropertySet()
{ SC_UNO_CODENAME, SC_WID_UNO_CODENAME,
cppu::UnoType<OUString>::get(), 0, 0},
{ SC_UNO_NAMEDRANGES, SC_WID_UNO_NAMES,
cppu::UnoType<sheet::XNamedRanges>::get(), 0, 0 },
{ SC_UNO_SOLVERSETTINGS, SC_WID_UNO_SOLVERSETTINGS,
cppu::UnoType<sheet::XSolverSettings>::get(), 0, 0 },
+ { SC_UNONAME_TOTALBELOW, SC_WID_UNO_TOTALBELOW,
cppu::UnoType<bool>::get(), 0, 0 },
};
static SfxItemPropertySet aSheetPropertySet( aSheetPropertyMap_Impl );
return &aSheetPropertySet;
@@ -8062,6 +8063,11 @@ void ScTableSheetObj::SetOnePropertyValue( const
SfxItemPropertyMapEntry* pEntry
// how to set the format correctly
}
}
+ else if (pEntry->nWID == SC_WID_UNO_TOTALBELOW)
+ {
+ bool bTotalsRowBelow = ScUnoHelpFunctions::GetBoolFromAny(aValue);
+ rDoc.SetTotalsRowBelow(nTab, bTotalsRowBelow);
+ }
else
ScCellRangeObj::SetOnePropertyValue(pEntry, aValue); // base
class, no Item WID
}
diff --git a/sc/source/ui/view/cellsh1.cxx b/sc/source/ui/view/cellsh1.cxx
index 1e4b9f1bdc33..71a827abc1f3 100644
--- a/sc/source/ui/view/cellsh1.cxx
+++ b/sc/source/ui/view/cellsh1.cxx
@@ -3643,6 +3643,12 @@ void ScCellShell::ExecuteSubtotals(SfxRequest& rReq)
}
pDBData->GetSubTotalParam( aSubTotalParam );
+
+ ScDocument& rDoc = GetViewData().GetDocument();
+ SCTAB nTab = GetViewData().GetTabNo();
+ if (!rDoc.GetTotalsRowBelow(nTab))
+ aSubTotalParam.bSummaryBelow = false;
+
aSubTotalParam.bRemoveOnly = false;
if (bAnonymous)
{
diff --git a/sc/uiconfig/scalc/ui/subtotaloptionspage.ui
b/sc/uiconfig/scalc/ui/subtotaloptionspage.ui
index d81fb987be61..0d1c54f510d0 100644
--- a/sc/uiconfig/scalc/ui/subtotaloptionspage.ui
+++ b/sc/uiconfig/scalc/ui/subtotaloptionspage.ui
@@ -1,39 +1,39 @@
<?xml version="1.0" encoding="UTF-8"?>
-<!-- Generated with glade 3.36.0 -->
+<!-- Generated with glade 3.38.2 -->
<interface domain="sc">
<requires lib="gtk+" version="3.20"/>
<object class="GtkBox" id="SubTotalOptionsPage">
<property name="visible">True</property>
- <property name="can_focus">False</property>
- <property name="border_width">6</property>
+ <property name="can-focus">False</property>
+ <property name="border-width">6</property>
<property name="orientation">vertical</property>
<property name="spacing">12</property>
<child>
<object class="GtkFrame" id="frame1">
<property name="visible">True</property>
- <property name="can_focus">False</property>
+ <property name="can-focus">False</property>
<property name="hexpand">True</property>
- <property name="label_xalign">0</property>
- <property name="shadow_type">none</property>
+ <property name="label-xalign">0</property>
+ <property name="shadow-type">none</property>
<child>
- <!-- n-columns=1 n-rows=1 -->
+ <!-- n-columns=1 n-rows=4 -->
<object class="GtkGrid" id="grid1">
<property name="visible">True</property>
- <property name="can_focus">False</property>
- <property name="hexpand">True</property>
- <property name="vexpand">True</property>
- <property name="row_spacing">6</property>
+ <property name="can-focus">False</property>
<property name="margin-start">12</property>
<property name="margin-top">6</property>
+ <property name="hexpand">True</property>
+ <property name="vexpand">True</property>
+ <property name="row-spacing">6</property>
<child>
<object class="GtkCheckButton" id="pagebreak">
<property name="label" translatable="yes"
context="subtotaloptionspage|pagebreak">_Page break between groups</property>
<property name="visible">True</property>
- <property name="can_focus">True</property>
- <property name="receives_default">False</property>
+ <property name="can-focus">True</property>
+ <property name="receives-default">False</property>
<property name="hexpand">True</property>
- <property name="use_underline">True</property>
- <property name="draw_indicator">True</property>
+ <property name="use-underline">True</property>
+ <property name="draw-indicator">True</property>
<child internal-child="accessible">
<object class="AtkObject" id="pagebreak-atkobject">
<property name="AtkObject::accessible-description"
translatable="yes" context="subtotaloptionspage|extended_tip|pagebreak">Inserts
a new page after each group of subtotaled data.</property>
@@ -41,19 +41,19 @@
</child>
</object>
<packing>
- <property name="left_attach">0</property>
- <property name="top_attach">0</property>
+ <property name="left-attach">0</property>
+ <property name="top-attach">0</property>
</packing>
</child>
<child>
<object class="GtkCheckButton" id="case">
<property name="label" translatable="yes"
context="subtotaloptionspage|case">_Case sensitive</property>
<property name="visible">True</property>
- <property name="can_focus">True</property>
- <property name="receives_default">False</property>
+ <property name="can-focus">True</property>
+ <property name="receives-default">False</property>
<property name="hexpand">True</property>
- <property name="use_underline">True</property>
- <property name="draw_indicator">True</property>
+ <property name="use-underline">True</property>
+ <property name="draw-indicator">True</property>
<child internal-child="accessible">
<object class="AtkObject" id="case-atkobject">
<property name="AtkObject::accessible-description"
translatable="yes" context="subtotaloptionspage|extended_tip|case">Recalculates
subtotals when you change the case of a data label.</property>
@@ -61,19 +61,19 @@
</child>
</object>
<packing>
- <property name="left_attach">0</property>
- <property name="top_attach">1</property>
+ <property name="left-attach">0</property>
+ <property name="top-attach">1</property>
</packing>
</child>
<child>
<object class="GtkCheckButton" id="sort">
<property name="label" translatable="yes"
context="subtotaloptionspage|sort">Pre-_sort area according to groups</property>
<property name="visible">True</property>
- <property name="can_focus">True</property>
- <property name="receives_default">False</property>
+ <property name="can-focus">True</property>
+ <property name="receives-default">False</property>
<property name="hexpand">True</property>
- <property name="use_underline">True</property>
- <property name="draw_indicator">True</property>
+ <property name="use-underline">True</property>
+ <property name="draw-indicator">True</property>
<child internal-child="accessible">
<object class="AtkObject" id="sort-atkobject">
<property name="AtkObject::accessible-description"
translatable="yes" context="subtotaloptionspage|extended_tip|sort">Sorts the
area that you selected in the Group by box of the Group tabs according to the
columns that you selected.</property>
@@ -81,8 +81,28 @@
</child>
</object>
<packing>
- <property name="left_attach">0</property>
- <property name="top_attach">2</property>
+ <property name="left-attach">0</property>
+ <property name="top-attach">2</property>
+ </packing>
+ </child>
+ <child>
+ <object class="GtkCheckButton" id="summarybelow">
+ <property name="label" translatable="yes"
context="subtotaloptionspage|sort">_Summary below data</property>
+ <property name="visible">True</property>
+ <property name="can-focus">True</property>
+ <property name="receives-default">False</property>
+ <property name="hexpand">True</property>
+ <property name="use-underline">True</property>
+ <property name="draw-indicator">True</property>
+ <child internal-child="accessible">
+ <object class="AtkObject" id="summarybelow-atkobject">
+ <property name="AtkObject::accessible-description"
translatable="yes"
context="subtotaloptionspage|extended_tip|summarybelow">Decide if the subtotals
below or above the data. Reposition subtotals when you change the summary below
data options.</property>
+ </object>
+ </child>
+ </object>
+ <packing>
+ <property name="left-attach">0</property>
+ <property name="top-attach">3</property>
</packing>
</child>
</object>
@@ -90,7 +110,7 @@
<child type="label">
<object class="GtkLabel" id="label1">
<property name="visible">True</property>
- <property name="can_focus">False</property>
+ <property name="can-focus">False</property>
<property name="label" translatable="yes"
context="subtotaloptionspage|label1">Groups</property>
<attributes>
<attribute name="weight" value="bold"/>
@@ -107,31 +127,31 @@
<child>
<object class="GtkFrame" id="frame2">
<property name="visible">True</property>
- <property name="can_focus">False</property>
+ <property name="can-focus">False</property>
<property name="hexpand">True</property>
<property name="vexpand">True</property>
- <property name="label_xalign">0</property>
- <property name="shadow_type">none</property>
+ <property name="label-xalign">0</property>
+ <property name="shadow-type">none</property>
<child>
- <!-- n-columns=1 n-rows=1 -->
+ <!-- n-columns=1 n-rows=5 -->
<object class="GtkGrid" id="grid2">
<property name="visible">True</property>
- <property name="can_focus">False</property>
- <property name="hexpand">True</property>
- <property name="vexpand">True</property>
- <property name="row_spacing">6</property>
+ <property name="can-focus">False</property>
<property name="margin-start">12</property>
<property name="margin-top">6</property>
+ <property name="hexpand">True</property>
+ <property name="vexpand">True</property>
+ <property name="row-spacing">6</property>
<child>
<object class="GtkRadioButton" id="ascending">
<property name="label" translatable="yes"
context="subtotaloptionspage|ascending">_Ascending</property>
<property name="visible">True</property>
- <property name="can_focus">True</property>
- <property name="receives_default">False</property>
+ <property name="can-focus">True</property>
+ <property name="receives-default">False</property>
<property name="hexpand">True</property>
- <property name="use_underline">True</property>
+ <property name="use-underline">True</property>
<property name="active">True</property>
- <property name="draw_indicator">True</property>
+ <property name="draw-indicator">True</property>
<child internal-child="accessible">
<object class="AtkObject" id="ascending-atkobject">
<property name="AtkObject::accessible-description"
translatable="yes" context="subtotaloptionspage|extended_tip|ascending">Sorts
beginning with the lowest value. You can define the sort rules on Data - Sort -
Options.</property>
@@ -139,19 +159,19 @@
</child>
</object>
<packing>
- <property name="left_attach">0</property>
- <property name="top_attach">0</property>
+ <property name="left-attach">0</property>
+ <property name="top-attach">0</property>
</packing>
</child>
<child>
<object class="GtkRadioButton" id="descending">
<property name="label" translatable="yes"
context="subtotaloptionspage|descending">D_escending</property>
<property name="visible">True</property>
- <property name="can_focus">True</property>
- <property name="receives_default">False</property>
+ <property name="can-focus">True</property>
+ <property name="receives-default">False</property>
<property name="hexpand">True</property>
- <property name="use_underline">True</property>
- <property name="draw_indicator">True</property>
+ <property name="use-underline">True</property>
+ <property name="draw-indicator">True</property>
<property name="group">ascending</property>
<child internal-child="accessible">
<object class="AtkObject" id="descending-atkobject">
@@ -160,19 +180,19 @@
</child>
</object>
<packing>
- <property name="left_attach">0</property>
- <property name="top_attach">1</property>
+ <property name="left-attach">0</property>
+ <property name="top-attach">1</property>
</packing>
</child>
<child>
<object class="GtkCheckButton" id="formats">
<property name="label" translatable="yes"
context="subtotaloptionspage|formats">I_nclude formats</property>
<property name="visible">True</property>
- <property name="can_focus">True</property>
- <property name="receives_default">False</property>
+ <property name="can-focus">True</property>
+ <property name="receives-default">False</property>
<property name="hexpand">True</property>
- <property name="use_underline">True</property>
- <property name="draw_indicator">True</property>
+ <property name="use-underline">True</property>
+ <property name="draw-indicator">True</property>
<child internal-child="accessible">
<object class="AtkObject" id="formats-atkobject">
<property name="AtkObject::accessible-description"
translatable="yes" context="subtotaloptionspage|extended_tip|formats">Considers
formatting attributes when sorting.</property>
@@ -180,40 +200,40 @@
</child>
</object>
<packing>
- <property name="left_attach">0</property>
- <property name="top_attach">2</property>
+ <property name="left-attach">0</property>
+ <property name="top-attach">2</property>
</packing>
</child>
<child>
<object class="GtkCheckButton" id="btnuserdef">
<property name="label" translatable="yes"
context="subtotaloptionspage|btnuserdef">C_ustom sort order</property>
<property name="visible">True</property>
- <property name="can_focus">True</property>
- <property name="receives_default">False</property>
+ <property name="can-focus">True</property>
+ <property name="receives-default">False</property>
<property name="hexpand">True</property>
- <property name="use_underline">True</property>
- <property name="draw_indicator">True</property>
+ <property name="use-underline">True</property>
+ <property name="draw-indicator">True</property>
<accessibility>
<relation type="label-for" target="lbuserdef"/>
</accessibility>
</object>
<packing>
- <property name="left_attach">0</property>
- <property name="top_attach">3</property>
+ <property name="left-attach">0</property>
+ <property name="top-attach">3</property>
</packing>
</child>
<child>
<object class="GtkComboBoxText" id="lbuserdef">
<property name="visible">True</property>
- <property name="can_focus">False</property>
+ <property name="can-focus">False</property>
<property name="margin-start">12</property>
<accessibility>
<relation type="labelled-by" target="btnuserdef"/>
</accessibility>
</object>
<packing>
- <property name="left_attach">0</property>
- <property name="top_attach">4</property>
+ <property name="left-attach">0</property>
+ <property name="top-attach">4</property>
</packing>
</child>
</object>
@@ -221,7 +241,7 @@
<child type="label">
<object class="GtkLabel" id="label2">
<property name="visible">True</property>
- <property name="can_focus">False</property>
+ <property name="can-focus">False</property>
<property name="label" translatable="yes"
context="subtotaloptionspage|label2">Sort</property>
<attributes>
<attribute name="weight" value="bold"/>
