[Libreoffice-commits] core.git: sc/qa sc/source
Vikas
vikasmahato0 at gmail.com
Fri Jul 6 13:25:44 UTC 2018
sc/qa/unit/datatransformation_test.cxx | 80 ++++++++++++++++++
sc/source/ui/dataprovider/datatransformation.cxx | 98 +++++++++++++++++++++++
sc/source/ui/inc/datatransformation.hxx | 16 +++
3 files changed, 193 insertions(+), 1 deletion(-)
New commits:
commit 8981adda6a24a172245b8cd0dd60e31644e23c75
Author: Vikas <vikasmahato0 at gmail.com>
Date: Tue Jul 3 17:05:36 2018 +0530
Added aggregate functions for external data
- Sum, which returns the sum of values in the column.
- Average, which returns the average of values in the column.
- Min, which returns the minimum of the values in the column.
- Max, which returns the maximum of the values in the column.
Change-Id: I196eb2d367d2f8c50ceec42735f6f56e2067e401
Reviewed-on: https://gerrit.libreoffice.org/56862
Tested-by: Jenkins
Reviewed-by: Markus Mohrhard <markus.mohrhard at googlemail.com>
diff --git a/sc/qa/unit/datatransformation_test.cxx b/sc/qa/unit/datatransformation_test.cxx
index 8cec163eeb43..c011941a6cb7 100644
--- a/sc/qa/unit/datatransformation_test.cxx
+++ b/sc/qa/unit/datatransformation_test.cxx
@@ -35,6 +35,10 @@ public:
void testTextToUpper();
void testTextCapitalize();
void testTextTrim();
+ void testAggregateSum();
+ void testAggregateAverage();
+ void testAggregateMin();
+ void testAggregateMax();
CPPUNIT_TEST_SUITE(ScDataTransformationTest);
CPPUNIT_TEST(testColumnRemove);
@@ -44,6 +48,10 @@ public:
CPPUNIT_TEST(testTextToUpper);
CPPUNIT_TEST(testTextCapitalize);
CPPUNIT_TEST(testTextTrim);
+ CPPUNIT_TEST(testAggregateSum);
+ CPPUNIT_TEST(testAggregateAverage);
+ CPPUNIT_TEST(testAggregateMin);
+ CPPUNIT_TEST(testAggregateMax);
CPPUNIT_TEST_SUITE_END();
private:
@@ -193,6 +201,78 @@ void ScDataTransformationTest::testTextTrim()
CPPUNIT_ASSERT_EQUAL(OUString("Paris"), m_pDoc->GetString(2, 2, 0));
}
+void ScDataTransformationTest::testAggregateSum()
+{
+ m_pDoc->SetValue(2, 0, 0, 2034);
+ m_pDoc->SetValue(2, 1, 0, 2342);
+ m_pDoc->SetValue(2, 2, 0, 57452);
+
+ m_pDoc->SetValue(4, 0, 0, 4829.98);
+ m_pDoc->SetValue(4, 1, 0, 53781.3);
+ m_pDoc->SetValue(4, 2, 0, 9876.4);
+ m_pDoc->SetValue(4, 3, 0, 0);
+
+ sc::AggregateFunction aTransform({2, 4}, sc::AGGREGATE_FUNCTION::SUM);
+ aTransform.Transform(*m_pDoc);
+
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(61828, m_pDoc->GetValue(2, 4, 0), 1e-10);
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(68487.68, m_pDoc->GetValue(4, 4, 0), 1e-10);
+}
+
+void ScDataTransformationTest::testAggregateAverage()
+{
+ m_pDoc->SetValue(2, 0, 0, 2034);
+ m_pDoc->SetValue(2, 1, 0, 2342);
+ m_pDoc->SetValue(2, 2, 0, 57453);
+
+ m_pDoc->SetValue(3, 0, 0, 4);
+ m_pDoc->SetValue(3, 1, 0, 4);
+ m_pDoc->SetValue(3, 2, 0, 4);
+
+ sc::AggregateFunction aTransform({2, 3}, sc::AGGREGATE_FUNCTION::AVERAGE);
+ aTransform.Transform(*m_pDoc);
+
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(20609.6666666667, m_pDoc->GetValue(2, 3, 0), 1e-10);
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(4, m_pDoc->GetValue(3, 3, 0), 1e-10);
+}
+
+void ScDataTransformationTest::testAggregateMin()
+{
+ m_pDoc->SetValue(2, 0, 0, 2034);
+ m_pDoc->SetValue(2, 1, 0, 2342);
+ m_pDoc->SetValue(2, 2, 0, 57453);
+
+ m_pDoc->SetValue(3, 0, 0, 2034);
+ m_pDoc->SetValue(3, 1, 0, -2342);
+ m_pDoc->SetValue(3, 2, 0, 57453);
+
+ sc::AggregateFunction aTransform({2, 3}, sc::AGGREGATE_FUNCTION::MIN);
+ aTransform.Transform(*m_pDoc);
+
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(2034, m_pDoc->GetValue(2, 3, 0), 1e-10);
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(-2342, m_pDoc->GetValue(3, 3, 0), 1e-10);
+}
+
+void ScDataTransformationTest::testAggregateMax()
+{
+ m_pDoc->SetValue(2, 0, 0, 2034);
+ m_pDoc->SetValue(2, 1, 0, 2342);
+ m_pDoc->SetValue(2, 2, 0, 57453);
+ m_pDoc->SetValue(2, 3, 0, -453);
+
+ m_pDoc->SetValue(3, 0, 0, 2034);
+ m_pDoc->SetValue(3, 1, 0, -2342);
+ m_pDoc->SetValue(3, 2, 0, -57453);
+ m_pDoc->SetValue(3, 3, 0, -453);
+
+ sc::AggregateFunction aTransform({2, 3}, sc::AGGREGATE_FUNCTION::MAX);
+ aTransform.Transform(*m_pDoc);
+
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(57453, m_pDoc->GetValue(2, 4, 0), 1e-10);
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(2034, m_pDoc->GetValue(3, 4, 0), 1e-10);
+}
+
+
ScDataTransformationTest::ScDataTransformationTest() :
ScBootstrapFixture( "sc/qa/unit/data/dataprovider" ),
m_pDoc(nullptr)
diff --git a/sc/source/ui/dataprovider/datatransformation.cxx b/sc/source/ui/dataprovider/datatransformation.cxx
index 84df313aacdc..fd779bf6587a 100644
--- a/sc/source/ui/dataprovider/datatransformation.cxx
+++ b/sc/source/ui/dataprovider/datatransformation.cxx
@@ -10,6 +10,7 @@
#include <datatransformation.hxx>
#include <document.hxx>
+#include <limits>
namespace sc {
@@ -268,6 +269,103 @@ TransformationType TextTransformation::getTransformationType() const
return TransformationType::TEXT_TRANSFORMATION;
}
+AggregateFunction::AggregateFunction(const std::set<SCCOL>& rColumns, const AGGREGATE_FUNCTION rType):
+ maColumns(rColumns),
+ maType(rType)
+{
+}
+
+void AggregateFunction::Transform(ScDocument& rDoc) const
+{
+ SCROW nEndRow = 0;
+ for (auto& itr : maColumns)
+ {
+ nEndRow = getLastRow(rDoc, itr);
+ }
+
+ for (auto& rCol : maColumns)
+ {
+ switch (maType)
+ {
+ case AGGREGATE_FUNCTION::SUM:
+ {
+ double nSum = 0;
+ for (SCROW nRow = 0; nRow <= nEndRow; ++nRow)
+ {
+ CellType eType;
+ rDoc.GetCellType(rCol, nRow, 0, eType);
+ if (eType == CELLTYPE_VALUE)
+ {
+ double nVal = rDoc.GetValue(rCol, nRow, 0);
+ nSum += nVal;
+ }
+ }
+ rDoc.SetValue(rCol, nEndRow + 1, 0, nSum);
+ }
+ break;
+ case AGGREGATE_FUNCTION::AVERAGE:
+ {
+ double nSum = 0;
+ for (SCROW nRow = 0; nRow <= nEndRow; ++nRow)
+ {
+ CellType eType;
+ rDoc.GetCellType(rCol, nRow, 0, eType);
+ if (eType == CELLTYPE_VALUE)
+ {
+ double nVal = rDoc.GetValue(rCol, nRow, 0);
+ nSum += nVal;
+ }
+ }
+
+ double nAvg = nSum / (nEndRow + 1);
+ rDoc.SetValue(rCol, nEndRow + 1, 0, nAvg);
+ }
+ break;
+ case AGGREGATE_FUNCTION::MIN:
+ {
+ double nMin = std::numeric_limits<double>::max();
+ for (SCROW nRow = 0; nRow <= nEndRow; ++nRow)
+ {
+ CellType eType;
+ rDoc.GetCellType(rCol, nRow, 0, eType);
+ if (eType == CELLTYPE_VALUE)
+ {
+ double nVal = rDoc.GetValue(rCol, nRow, 0);
+ if(nVal < nMin)
+ nMin = nVal;
+ }
+ }
+ rDoc.SetValue(rCol, nEndRow + 1, 0, nMin);
+ }
+ break;
+ case AGGREGATE_FUNCTION::MAX:
+ {
+ double nMax = std::numeric_limits<double>::lowest();
+ for (SCROW nRow = 0; nRow <= nEndRow; ++nRow)
+ {
+ CellType eType;
+ rDoc.GetCellType(rCol, nRow, 0, eType);
+ if (eType == CELLTYPE_VALUE)
+ {
+ double nVal = rDoc.GetValue(rCol, nRow, 0);
+ if(nMax < nVal)
+ nMax = nVal;
+ }
+ }
+ rDoc.SetValue(rCol, nEndRow + 1, 0, nMax);
+ }
+ break;
+ default:
+ break;
+ }
+ }
+}
+
+TransformationType AggregateFunction::getTransformationType() const
+{
+ return TransformationType::AGGREGATE_FUNCTION;
+}
+
}
/* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/source/ui/inc/datatransformation.hxx b/sc/source/ui/inc/datatransformation.hxx
index 522c039851ae..305e35a3cd54 100644
--- a/sc/source/ui/inc/datatransformation.hxx
+++ b/sc/source/ui/inc/datatransformation.hxx
@@ -27,11 +27,14 @@ enum class TransformationType
SPLIT_TRANSFORMATION,
DELETE_TRANSFORMATION,
SORT_TRANSFORMATION,
- TEXT_TRANSFORMATION
+ TEXT_TRANSFORMATION,
+ AGGREGATE_FUNCTION
};
enum class TEXT_TRANSFORM_TYPE { TO_LOWER, TO_UPPER, CAPITALIZE, TRIM };
+enum class AGGREGATE_FUNCTION { SUM, AVERAGE, MIN, MAX };
+
class SC_DLLPUBLIC DataTransformation
{
protected:
@@ -110,6 +113,17 @@ class SC_DLLPUBLIC TextTransformation : public DataTransformation
virtual TransformationType getTransformationType() const override;
};
+class SC_DLLPUBLIC AggregateFunction : public DataTransformation
+{
+ std::set<SCCOL> maColumns;
+ AGGREGATE_FUNCTION maType;
+
+ public:
+ AggregateFunction(const std::set<SCCOL>& rColumns, const AGGREGATE_FUNCTION rType);
+ virtual void Transform(ScDocument& rDoc) const override;
+ virtual TransformationType getTransformationType() const override;
+};
+
}
#endif
More information about the Libreoffice-commits
mailing list