[Libreoffice-commits] core.git: sc/inc sc/source
Jean-Sebastien Bevilacqua
jsbevilacqua at linagora.com
Wed Sep 13 17:03:34 UTC 2017
sc/inc/globstr.hrc | 2
sc/source/core/data/table3.cxx | 153 ++++++++++++++++++++++++++---------------
2 files changed, 99 insertions(+), 56 deletions(-)
New commits:
commit e8d370e84af5dc9b8817cbf5aa66e50db150a0c6
Author: Jean-Sebastien Bevilacqua <jsbevilacqua at linagora.com>
Date: Thu Sep 7 11:15:21 2017 +0200
tdf#107267: Fix grand total calculation
To fix the grand total calculation, we add another step.
This step loop through all row to find the min and max of each value.
These min and max are then used by the grand total.
Patch by Linagora
Change-Id: If3200840764d0ad9cb63231ac9f67b5d5ed197f1
Reviewed-on: https://gerrit.libreoffice.org/42042
Tested-by: Jenkins <ci at libreoffice.org>
Reviewed-by: Eike Rathke <erack at redhat.com>
diff --git a/sc/inc/globstr.hrc b/sc/inc/globstr.hrc
index 644ecae7afe2..bd27e9ef0bba 100644
--- a/sc/inc/globstr.hrc
+++ b/sc/inc/globstr.hrc
@@ -137,7 +137,7 @@
#define STR_MSSG_SOLVE_2 NC_("STR_MSSG_SOLVE_2", "Goal Seek failed.\n\n")
#define STR_MSSG_SOLVE_3 NC_("STR_MSSG_SOLVE_3", "Insert the closest value (")
#define STR_MSSG_SOLVE_4 NC_("STR_MSSG_SOLVE_4", ") into the variable cell anyway?")
-#define STR_TABLE_GESAMTERGEBNIS NC_("STR_TABLE_GESAMTERGEBNIS", "Grand Total")
+#define STR_TABLE_GRAND NC_("STR_TABLE_GRAND", "Grand")
#define STR_TABLE_ERGEBNIS NC_("STR_TABLE_ERGEBNIS", "Result")
#define STR_UNDO_SPELLING NC_("STR_UNDO_SPELLING", "Spellcheck")
#define STR_TABLE_UND NC_("STR_TABLE_UND", "AND")
diff --git a/sc/source/core/data/table3.cxx b/sc/source/core/data/table3.cxx
index 6aa3cdf1e3c2..3b1a1c393a9b 100644
--- a/sc/source/core/data/table3.cxx
+++ b/sc/source/core/data/table3.cxx
@@ -1964,6 +1964,30 @@ typedef struct lcl_ScTable_DoSubTotals_RowEntry
SCROW nFuncEnd;
} RowEntry;
+
+static const char* lcl_GetSubTotalStrId(int id)
+{
+ switch ( id )
+ {
+ case SUBTOTAL_FUNC_AVE: return STR_FUN_TEXT_AVG;
+ case SUBTOTAL_FUNC_CNT:
+ case SUBTOTAL_FUNC_CNT2: return STR_FUN_TEXT_COUNT;
+ case SUBTOTAL_FUNC_MAX: return STR_FUN_TEXT_MAX;
+ case SUBTOTAL_FUNC_MIN: return STR_FUN_TEXT_MIN;
+ case SUBTOTAL_FUNC_PROD: return STR_FUN_TEXT_PRODUCT;
+ case SUBTOTAL_FUNC_STD:
+ case SUBTOTAL_FUNC_STDP: return STR_FUN_TEXT_STDDEV;
+ case SUBTOTAL_FUNC_SUM: return STR_FUN_TEXT_SUM;
+ case SUBTOTAL_FUNC_VAR:
+ case SUBTOTAL_FUNC_VARP: return STR_FUN_TEXT_VAR;
+ default:
+ {
+ return STR_EMPTYDATA;
+ // added to avoid warnings
+ }
+ }
+}
+
// new intermediate results
// rParam.nRow2 is changed!
@@ -2019,10 +2043,9 @@ bool ScTable::DoSubTotals( ScSubTotalParam& rParam )
RowEntry aRowEntry;
::std::vector< RowEntry > aRowVector;
- for (sal_uInt16 nLevel=0; nLevel<=nLevelCount && bSpaceLeft; nLevel++) // including grand total
+ for (sal_uInt16 nLevel=0; nLevel<nLevelCount && bSpaceLeft; nLevel++)
{
- bool bTotal = ( nLevel == nLevelCount );
- aRowEntry.nGroupNo = bTotal ? 0 : (nLevelCount-nLevel-1);
+ aRowEntry.nGroupNo = nLevelCount - nLevel - 1;
// how many results per level
SCCOL nResCount = rParam.nSubTotals[aRowEntry.nGroupNo];
@@ -2050,31 +2073,28 @@ bool ScTable::DoSubTotals( ScSubTotalParam& rParam )
else
{
bChanged = false;
- if (!bTotal)
+ OUString aString;
+ for (i=0; i<=aRowEntry.nGroupNo && !bChanged; i++)
{
- OUString aString;
- for (i=0; i<=aRowEntry.nGroupNo && !bChanged; i++)
- {
- GetString( nGroupCol[i], nRow, aString );
- if (bIgnoreCase)
- aString = ScGlobal::pCharClass->uppercase(aString);
- // when sorting, blanks are separate group
- // otherwise blank cells are allowed below
- bChanged = ( ( !aString.isEmpty() || rParam.bDoSort ) &&
- aString != aCompString[i] );
- }
- if ( bChanged && bTestPrevSub )
+ GetString( nGroupCol[i], nRow, aString );
+ if (bIgnoreCase)
+ aString = ScGlobal::pCharClass->uppercase(aString);
+ // when sorting, blanks are separate group
+ // otherwise blank cells are allowed below
+ bChanged = ( ( !aString.isEmpty() || rParam.bDoSort ) &&
+ aString != aCompString[i] );
+ }
+ if ( bChanged && bTestPrevSub )
+ {
+ // No group change on rows that will contain subtotal formulas
+ for ( ::std::vector< RowEntry >::const_iterator
+ iEntry( aRowVector.begin());
+ iEntry != aRowVector.end(); ++iEntry)
{
- // No group change on rows that will contain subtotal formulas
- for ( ::std::vector< RowEntry >::const_iterator
- iEntry( aRowVector.begin());
- iEntry != aRowVector.end(); ++iEntry)
+ if ( iEntry->nDestRow == nRow )
{
- if ( iEntry->nDestRow == nRow )
- {
- bChanged = false;
- break;
- }
+ bChanged = false;
+ break;
}
}
}
@@ -2110,36 +2130,14 @@ bool ScTable::DoSubTotals( ScSubTotalParam& rParam )
// collect formula positions
aRowVector.push_back( aRowEntry );
- if (bTotal) // "Grand total"
- aOutString = ScGlobal::GetRscString( STR_TABLE_GESAMTERGEBNIS );
- else
- { // "Result"
- aOutString = aSubString;
- if (aOutString.isEmpty())
- aOutString = ScGlobal::GetRscString( STR_EMPTYDATA );
- aOutString += " ";
- const char* pStrId = STR_TABLE_ERGEBNIS;
- if ( nResCount == 1 )
- switch ( eResFunc[0] )
- {
- case SUBTOTAL_FUNC_AVE: pStrId = STR_FUN_TEXT_AVG; break;
- case SUBTOTAL_FUNC_CNT:
- case SUBTOTAL_FUNC_CNT2: pStrId = STR_FUN_TEXT_COUNT; break;
- case SUBTOTAL_FUNC_MAX: pStrId = STR_FUN_TEXT_MAX; break;
- case SUBTOTAL_FUNC_MIN: pStrId = STR_FUN_TEXT_MIN; break;
- case SUBTOTAL_FUNC_PROD: pStrId = STR_FUN_TEXT_PRODUCT; break;
- case SUBTOTAL_FUNC_STD:
- case SUBTOTAL_FUNC_STDP: pStrId = STR_FUN_TEXT_STDDEV; break;
- case SUBTOTAL_FUNC_SUM: pStrId = STR_FUN_TEXT_SUM; break;
- case SUBTOTAL_FUNC_VAR:
- case SUBTOTAL_FUNC_VARP: pStrId = STR_FUN_TEXT_VAR; break;
- default:
- {
- // added to avoid warnings
- }
- }
- aOutString += ScGlobal::GetRscString(pStrId);
- }
+ aOutString = aSubString;
+ if (aOutString.isEmpty())
+ aOutString = ScGlobal::GetRscString( STR_EMPTYDATA );
+ aOutString += " ";
+ const char* pStrId = STR_TABLE_ERGEBNIS;
+ if ( nResCount == 1 )
+ pStrId = lcl_GetSubTotalStrId(eResFunc[0]);
+ aOutString += ScGlobal::GetRscString(pStrId);
SetString( nGroupCol[aRowEntry.nGroupNo], aRowEntry.nDestRow, nTab, aOutString );
ApplyStyle( nGroupCol[aRowEntry.nGroupNo], aRowEntry.nDestRow, pStyle );
@@ -2161,6 +2159,51 @@ bool ScTable::DoSubTotals( ScSubTotalParam& rParam )
}
}
+ // generate global total
+ SCROW nGlobalStartRow = aRowVector[0].nSubStartRow;
+ SCROW nGlobalStartFunc = aRowVector[0].nFuncStart;
+ SCROW nGlobalEndRow = 0;
+ SCROW nGlobalEndFunc = 0;
+ for ( ::std::vector< RowEntry >::const_iterator iEntry( aRowVector.begin());
+ iEntry != aRowVector.end(); ++iEntry)
+ {
+ nGlobalEndRow = (nGlobalEndRow < iEntry->nDestRow) ? iEntry->nDestRow : nGlobalEndRow;
+ nGlobalEndFunc = (nGlobalEndFunc < iEntry->nFuncEnd) ? iEntry->nFuncEnd : nGlobalEndRow;
+ }
+
+ for (sal_uInt16 nLevel=0; nLevel<nLevelCount; nLevel++)
+ {
+ // increment end row
+ nGlobalEndRow++;
+
+ // add row entry for formula
+ aRowEntry.nGroupNo = nLevelCount-nLevel-1;
+ aRowEntry.nSubStartRow = nGlobalStartRow;
+ aRowEntry.nFuncStart = nGlobalStartFunc;
+ aRowEntry.nDestRow = nGlobalEndRow;
+ aRowEntry.nFuncEnd = nGlobalEndFunc;
+
+ // increment row
+ nGlobalEndFunc++;
+
+ bSpaceLeft = pDocument->InsertRow( 0, nTab, MAXCOL, nTab, aRowEntry.nDestRow, 1 );
+
+ if (bSpaceLeft)
+ {
+ aRowVector.push_back( aRowEntry );
+ nEndRow++;
+ DBShowRow(aRowEntry.nDestRow, true);
+
+ // insert label
+ ScSubTotalFunc* eResFunc = rParam.pFunctions[aRowEntry.nGroupNo];
+ OUString label = ScGlobal::GetRscString( STR_TABLE_GRAND );
+ label += " ";
+ label += ScGlobal::GetRscString(lcl_GetSubTotalStrId(eResFunc[0]));
+ SetString( nGroupCol[aRowEntry.nGroupNo], aRowEntry.nDestRow, nTab, label );
+ ApplyStyle( nGroupCol[aRowEntry.nGroupNo], aRowEntry.nDestRow, pStyle );
+ }
+ }
+
// now insert the formulas
ScComplexRefData aRef;
aRef.InitFlags();
More information about the Libreoffice-commits
mailing list