diff options
-rw-r--r-- | sc/qa/unit/datatransformation_test.cxx | 213 | ||||
-rw-r--r-- | sc/source/ui/dataprovider/datatransformation.cxx | 236 | ||||
-rw-r--r-- | sc/source/ui/inc/datatransformation.hxx | 19 |
3 files changed, 467 insertions, 1 deletions
diff --git a/sc/qa/unit/datatransformation_test.cxx b/sc/qa/unit/datatransformation_test.cxx index c011941a6cb7..3546104fdbee 100644 --- a/sc/qa/unit/datatransformation_test.cxx +++ b/sc/qa/unit/datatransformation_test.cxx @@ -39,6 +39,18 @@ public: void testAggregateAverage(); void testAggregateMin(); void testAggregateMax(); + void testNumberRound(); + void testNumberRoundUp(); + void testNumberRoundDown(); + void testNumberAbsolute(); + void testNumberLogE(); + void testNumberLog10(); + void testNumberCube(); + void testNumberSquare(); + void testNumberSquareRoot(); + void testNumberEven(); + void testNumberOdd(); + void testNumberSign(); CPPUNIT_TEST_SUITE(ScDataTransformationTest); CPPUNIT_TEST(testColumnRemove); @@ -52,6 +64,18 @@ public: CPPUNIT_TEST(testAggregateAverage); CPPUNIT_TEST(testAggregateMin); CPPUNIT_TEST(testAggregateMax); + CPPUNIT_TEST(testNumberRound); + CPPUNIT_TEST(testNumberRoundUp); + CPPUNIT_TEST(testNumberRoundDown); + CPPUNIT_TEST(testNumberAbsolute); + CPPUNIT_TEST(testNumberLogE); + CPPUNIT_TEST(testNumberLog10); + CPPUNIT_TEST(testNumberCube); + CPPUNIT_TEST(testNumberSquare); + CPPUNIT_TEST(testNumberSquareRoot); + CPPUNIT_TEST(testNumberEven); + CPPUNIT_TEST(testNumberOdd); + CPPUNIT_TEST(testNumberSign); CPPUNIT_TEST_SUITE_END(); private: @@ -272,6 +296,195 @@ void ScDataTransformationTest::testAggregateMax() CPPUNIT_ASSERT_DOUBLES_EQUAL(2034, m_pDoc->GetValue(3, 4, 0), 1e-10); } +void ScDataTransformationTest::testNumberRound() +{ + m_pDoc->SetValue(2, 0, 0, 2034.342453456); + m_pDoc->SetValue(2, 1, 0, 2342.252678567542); + m_pDoc->SetValue(2, 2, 0, 57453.651345687654345676); + m_pDoc->SetValue(2, 3, 0, -453.22234567543); + + sc::NumberTransformation aTransform(2, sc::NUMBER_TRANSFORM_TYPE::ROUND, 4); + aTransform.Transform(*m_pDoc); + + CPPUNIT_ASSERT_EQUAL(2034.3425, m_pDoc->GetValue(2, 0, 0)); + CPPUNIT_ASSERT_EQUAL(2342.2527, m_pDoc->GetValue(2, 1, 0)); + CPPUNIT_ASSERT_EQUAL(57453.6513, m_pDoc->GetValue(2, 2, 0)); + CPPUNIT_ASSERT_EQUAL(-453.2223, m_pDoc->GetValue(2, 3, 0)); +} + +void ScDataTransformationTest::testNumberRoundUp() +{ + m_pDoc->SetValue(2, 0, 0, 2034.34); + m_pDoc->SetValue(2, 1, 0, 2342.22); + m_pDoc->SetValue(2, 2, 0, 57453.65); + m_pDoc->SetValue(2, 3, 0, -453.22); + + sc::NumberTransformation aTransform(2, sc::NUMBER_TRANSFORM_TYPE::ROUND_UP); + aTransform.Transform(*m_pDoc); + + CPPUNIT_ASSERT_EQUAL(2035.0, m_pDoc->GetValue(2, 0, 0)); + CPPUNIT_ASSERT_EQUAL(2343.0, m_pDoc->GetValue(2, 1, 0)); + CPPUNIT_ASSERT_EQUAL(57454.0, m_pDoc->GetValue(2, 2, 0)); + CPPUNIT_ASSERT_EQUAL(-453.0, m_pDoc->GetValue(2, 3, 0)); +} + +void ScDataTransformationTest::testNumberRoundDown() +{ + m_pDoc->SetValue(2, 0, 0, 2034.34); + m_pDoc->SetValue(2, 1, 0, 2342.22); + m_pDoc->SetValue(2, 2, 0, 57453.65); + m_pDoc->SetValue(2, 3, 0, -453.22); + + sc::NumberTransformation aTransform(2, sc::NUMBER_TRANSFORM_TYPE::ROUND_DOWN); + aTransform.Transform(*m_pDoc); + + CPPUNIT_ASSERT_EQUAL(2034.0, m_pDoc->GetValue(2, 0, 0)); + CPPUNIT_ASSERT_EQUAL(2342.0, m_pDoc->GetValue(2, 1, 0)); + CPPUNIT_ASSERT_EQUAL(57453.0, m_pDoc->GetValue(2, 2, 0)); + CPPUNIT_ASSERT_EQUAL(-454.0, m_pDoc->GetValue(2, 3, 0)); +} + +void ScDataTransformationTest::testNumberAbsolute() +{ + m_pDoc->SetValue(2, 0, 0, 2034.34); + m_pDoc->SetValue(2, 1, 0, -2342.22); + m_pDoc->SetValue(2, 2, 0, 57453.65); + m_pDoc->SetValue(2, 3, 0, -453.22); + + sc::NumberTransformation aTransform(2, sc::NUMBER_TRANSFORM_TYPE::ABSOLUTE); + aTransform.Transform(*m_pDoc); + + CPPUNIT_ASSERT_EQUAL(2034.34, m_pDoc->GetValue(2, 0, 0)); + CPPUNIT_ASSERT_EQUAL(2342.22, m_pDoc->GetValue(2, 1, 0)); + CPPUNIT_ASSERT_EQUAL(57453.65, m_pDoc->GetValue(2, 2, 0)); + CPPUNIT_ASSERT_EQUAL(453.22, m_pDoc->GetValue(2, 3, 0)); +} + +void ScDataTransformationTest::testNumberLogE() +{ + m_pDoc->SetValue(2, 0, 0, 1); + m_pDoc->SetValue(2, 1, 0, 5); + m_pDoc->SetValue(2, 2, 0, -9); + m_pDoc->SetValue(2, 3, 0, 500); + + sc::NumberTransformation aTransform(2, sc::NUMBER_TRANSFORM_TYPE::LOG_E); + aTransform.Transform(*m_pDoc); + + CPPUNIT_ASSERT_DOUBLES_EQUAL(0, m_pDoc->GetValue(2, 0, 0), 1e-10); + CPPUNIT_ASSERT_DOUBLES_EQUAL(1.60943791243, m_pDoc->GetValue(2, 1, 0), 1e-10); + CPPUNIT_ASSERT_EQUAL(OUString(""), m_pDoc->GetString(2, 2, 0)); + CPPUNIT_ASSERT_DOUBLES_EQUAL(6.21460809842, m_pDoc->GetValue(2, 3, 0), 1e-10); +} + +void ScDataTransformationTest::testNumberLog10() +{ + m_pDoc->SetValue(2, 0, 0, 1); + m_pDoc->SetValue(2, 1, 0, 10); + m_pDoc->SetValue(2, 2, 0, -9); + m_pDoc->SetValue(2, 3, 0, 500); + + sc::NumberTransformation aTransform(2, sc::NUMBER_TRANSFORM_TYPE::LOG_10); + aTransform.Transform(*m_pDoc); + + CPPUNIT_ASSERT_DOUBLES_EQUAL(0, m_pDoc->GetValue(2, 0, 0), 1e-10); + CPPUNIT_ASSERT_DOUBLES_EQUAL(1.0, m_pDoc->GetValue(2, 1, 0), 1e-10); + CPPUNIT_ASSERT_EQUAL(OUString(), m_pDoc->GetString(2, 2, 0)); + CPPUNIT_ASSERT_DOUBLES_EQUAL(2.69897000434, m_pDoc->GetValue(2, 3, 0), 1e-10); +} + +void ScDataTransformationTest::testNumberCube() +{ + m_pDoc->SetValue(2, 0, 0, 2); + m_pDoc->SetValue(2, 1, 0, -2); + m_pDoc->SetValue(2, 2, 0, 8); + m_pDoc->SetValue(2, 3, 0, -8); + + sc::NumberTransformation aTransform(2, sc::NUMBER_TRANSFORM_TYPE::CUBE); + aTransform.Transform(*m_pDoc); + + CPPUNIT_ASSERT_EQUAL(8.0, m_pDoc->GetValue(2, 0, 0)); + CPPUNIT_ASSERT_EQUAL(-8.0, m_pDoc->GetValue(2, 1, 0)); + CPPUNIT_ASSERT_EQUAL(512.0, m_pDoc->GetValue(2, 2, 0)); + CPPUNIT_ASSERT_EQUAL(-512.0, m_pDoc->GetValue(2, 3, 0)); +} + +void ScDataTransformationTest::testNumberSquare() +{ + m_pDoc->SetValue(2, 0, 0, 2); + m_pDoc->SetValue(2, 1, 0, -2); + m_pDoc->SetValue(2, 2, 0, 8); + m_pDoc->SetValue(2, 3, 0, -8); + + sc::NumberTransformation aTransform(2, sc::NUMBER_TRANSFORM_TYPE::SQUARE); + aTransform.Transform(*m_pDoc); + + CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(2, 0, 0)); + CPPUNIT_ASSERT_EQUAL(4.0, m_pDoc->GetValue(2, 1, 0)); + CPPUNIT_ASSERT_EQUAL(64.0, m_pDoc->GetValue(2, 2, 0)); + CPPUNIT_ASSERT_EQUAL(64.0, m_pDoc->GetValue(2, 3, 0)); +} + +void ScDataTransformationTest::testNumberSquareRoot() +{ + m_pDoc->SetValue(2, 0, 0, 8); + m_pDoc->SetValue(2, 1, 0, 4); + m_pDoc->SetValue(2, 2, 0, 9); + + sc::NumberTransformation aTransform(2, sc::NUMBER_TRANSFORM_TYPE::SQUARE_ROOT); + aTransform.Transform(*m_pDoc); + + CPPUNIT_ASSERT_DOUBLES_EQUAL(2.82842712475, m_pDoc->GetValue(2, 0, 0), 1e-10); + CPPUNIT_ASSERT_DOUBLES_EQUAL(2.0, m_pDoc->GetValue(2, 1, 0), 1e-10); + CPPUNIT_ASSERT_DOUBLES_EQUAL(3.0, m_pDoc->GetValue(2, 2, 0), 1e-10); +} + +void ScDataTransformationTest::testNumberEven() +{ + m_pDoc->SetValue(2, 0, 0, 2034); + m_pDoc->SetValue(2, 1, 0, 2343); + m_pDoc->SetValue(2, 2, 0, 57453.65); + m_pDoc->SetValue(2, 3, 0, -453); + + sc::NumberTransformation aTransform(2, sc::NUMBER_TRANSFORM_TYPE::IS_EVEN); + aTransform.Transform(*m_pDoc); + + CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(2, 0, 0)); + CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc->GetValue(2, 1, 0)); + CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc->GetValue(2, 2, 0)); + CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc->GetValue(2, 3, 0)); +} + +void ScDataTransformationTest::testNumberOdd() +{ + m_pDoc->SetValue(2, 0, 0, 2034); + m_pDoc->SetValue(2, 1, 0, 2343); + m_pDoc->SetValue(2, 2, 0, 57453.65); + m_pDoc->SetValue(2, 3, 0, -453); + + sc::NumberTransformation aTransform(2, sc::NUMBER_TRANSFORM_TYPE::IS_ODD); + aTransform.Transform(*m_pDoc); + + CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc->GetValue(2, 0, 0)); + CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(2, 1, 0)); + CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc->GetValue(2, 2, 0)); + CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(2, 3, 0)); +} + +void ScDataTransformationTest::testNumberSign() +{ + m_pDoc->SetValue(2, 0, 0, 2034.34); + m_pDoc->SetValue(2, 1, 0, -2342.22); + m_pDoc->SetValue(2, 2, 0, 0); + m_pDoc->SetValue(2, 3, 0, -453.22); + + sc::NumberTransformation aTransform(2, sc::NUMBER_TRANSFORM_TYPE::SIGN); + aTransform.Transform(*m_pDoc); + + CPPUNIT_ASSERT_EQUAL(1.0, m_pDoc->GetValue(2, 0, 0)); + CPPUNIT_ASSERT_EQUAL(-1.0, m_pDoc->GetValue(2, 1, 0)); + CPPUNIT_ASSERT_EQUAL(0.0, m_pDoc->GetValue(2, 2, 0)); + CPPUNIT_ASSERT_EQUAL(-1.0, m_pDoc->GetValue(2, 3, 0)); +} ScDataTransformationTest::ScDataTransformationTest() : ScBootstrapFixture( "sc/qa/unit/data/dataprovider" ), diff --git a/sc/source/ui/dataprovider/datatransformation.cxx b/sc/source/ui/dataprovider/datatransformation.cxx index 27bf47d75ea0..a1eae17c0df9 100644 --- a/sc/source/ui/dataprovider/datatransformation.cxx +++ b/sc/source/ui/dataprovider/datatransformation.cxx @@ -11,6 +11,8 @@ #include <document.hxx> #include <limits> +#include <rtl/math.hxx> +#include <cmath> namespace sc { @@ -366,6 +368,240 @@ TransformationType AggregateFunction::getTransformationType() const return TransformationType::AGGREGATE_FUNCTION; } +NumberTransformation::NumberTransformation(SCCOL nCol, const NUMBER_TRANSFORM_TYPE rType): + mnCol(nCol), + maType(rType), + maPrecision(-1) +{ +} + +NumberTransformation::NumberTransformation(SCCOL nCol,const NUMBER_TRANSFORM_TYPE rType, int nPrecision): + mnCol(nCol), + maType(rType), + maPrecision(nPrecision) +{ +} + +void NumberTransformation::Transform(ScDocument& rDoc) const +{ + SCROW nEndRow = getLastRow(rDoc, mnCol); + + switch (maType) + { + case NUMBER_TRANSFORM_TYPE::ROUND: + { + if(maPrecision > -1) + { + for (SCROW nRow = 0; nRow <= nEndRow; ++nRow) + { + CellType eType; + rDoc.GetCellType(mnCol, nRow, 0, eType); + if (eType == CELLTYPE_VALUE) + { + double nVal = rDoc.GetValue(mnCol, nRow, 0); + rDoc.SetValue(mnCol, nRow, 0, rtl::math::round(nVal, maPrecision)); + } + } + } + } + break; + case NUMBER_TRANSFORM_TYPE::ROUND_UP: + { + for (SCROW nRow = 0; nRow <= nEndRow; ++nRow) + { + CellType eType; + rDoc.GetCellType(mnCol, nRow, 0, eType); + if (eType == CELLTYPE_VALUE) + { + double nVal = rDoc.GetValue(mnCol, nRow, 0); + rDoc.SetValue(mnCol, nRow, 0, rtl::math::approxCeil(nVal)); + } + } + } + break; + case NUMBER_TRANSFORM_TYPE::ROUND_DOWN: + { + for (SCROW nRow = 0; nRow <= nEndRow; ++nRow) + { + CellType eType; + rDoc.GetCellType(mnCol, nRow, 0, eType); + if (eType == CELLTYPE_VALUE) + { + double nVal = rDoc.GetValue(mnCol, nRow, 0); + rDoc.SetValue(mnCol, nRow, 0, rtl::math::approxFloor(nVal)); + } + } + } + break; + case NUMBER_TRANSFORM_TYPE::ABSOLUTE: + { + for (SCROW nRow = 0; nRow <= nEndRow; ++nRow) + { + CellType eType; + rDoc.GetCellType(mnCol, nRow, 0, eType); + if (eType == CELLTYPE_VALUE) + { + double nVal = rDoc.GetValue(mnCol, nRow, 0); + if(rtl::math::isSignBitSet(nVal)) + rDoc.SetValue(mnCol, nRow, 0, -1 * nVal); + } + } + } + break; + case NUMBER_TRANSFORM_TYPE::LOG_E: + { + for (SCROW nRow = 0; nRow <= nEndRow; ++nRow) + { + CellType eType; + rDoc.GetCellType(mnCol, nRow, 0, eType); + if (eType == CELLTYPE_VALUE) + { + double nVal = rDoc.GetValue(mnCol, nRow, 0); + if (nVal > 0) + { + rDoc.SetValue(mnCol, nRow, 0, rtl::math::log1p(nVal-1)); + } + else + { + rDoc.SetString(mnCol, nRow, 0, OUString()); + } + } + } + } + break; + case NUMBER_TRANSFORM_TYPE::LOG_10: + { + for (SCROW nRow = 0; nRow <= nEndRow; ++nRow) + { + CellType eType; + rDoc.GetCellType(mnCol, nRow, 0, eType); + if (eType == CELLTYPE_VALUE) + { + double nVal = rDoc.GetValue(mnCol, nRow, 0); + if (nVal > 0) + { + rDoc.SetValue(mnCol, nRow, 0, log10(nVal)); + } + else + { + rDoc.SetString(mnCol, nRow, 0, OUString()); + } + } + } + } + break; + case NUMBER_TRANSFORM_TYPE::CUBE: + { + for (SCROW nRow = 0; nRow <= nEndRow; ++nRow) + { + CellType eType; + rDoc.GetCellType(mnCol, nRow, 0, eType); + if (eType == CELLTYPE_VALUE) + { + double nVal = rDoc.GetValue(mnCol, nRow, 0); + rDoc.SetValue(mnCol, nRow, 0, nVal * nVal * nVal); + } + } + } + break; + case NUMBER_TRANSFORM_TYPE::SQUARE: + { + for (SCROW nRow = 0; nRow <= nEndRow; ++nRow) + { + CellType eType; + rDoc.GetCellType(mnCol, nRow, 0, eType); + if (eType == CELLTYPE_VALUE) + { + double nVal = rDoc.GetValue(mnCol, nRow, 0); + rDoc.SetValue(mnCol, nRow, 0, nVal * nVal); + } + } + } + break; + case NUMBER_TRANSFORM_TYPE::SQUARE_ROOT: + { + for (SCROW nRow = 0; nRow <= nEndRow; ++nRow) + { + CellType eType; + rDoc.GetCellType(mnCol, nRow, 0, eType); + if (eType == CELLTYPE_VALUE) + { + double nVal = rDoc.GetValue(mnCol, nRow, 0); + if (!rtl::math::isSignBitSet(nVal)) + { + rDoc.SetValue(mnCol, nRow, 0, sqrt(nVal)); + } + else + { + rDoc.SetString(mnCol, nRow, 0, OUString()); + } + } + } + } + break; + case NUMBER_TRANSFORM_TYPE::IS_EVEN: + { + for (SCROW nRow = 0; nRow <= nEndRow; ++nRow) + { + CellType eType; + rDoc.GetCellType(mnCol, nRow, 0, eType); + if (eType == CELLTYPE_VALUE) + { + double nVal = rDoc.GetValue(mnCol, nRow, 0); + if (fmod(nVal, 1) == 0 && fmod(nVal, 2) == 0) + rDoc.SetValue(mnCol, nRow, 0, 1); + else + rDoc.SetValue(mnCol, nRow, 0, 0); + } + } + } + break; + case NUMBER_TRANSFORM_TYPE::IS_ODD: + { + for (SCROW nRow = 0; nRow <= nEndRow; ++nRow) + { + CellType eType; + rDoc.GetCellType(mnCol, nRow, 0, eType); + if (eType == CELLTYPE_VALUE) + { + double nVal = rDoc.GetValue(mnCol, nRow, 0); + if (fmod(nVal, 1) == 0 && fmod(nVal, 2) != 0) + rDoc.SetValue(mnCol, nRow, 0, 1); + else + rDoc.SetValue(mnCol, nRow, 0, 0); + } + } + } + break; + case NUMBER_TRANSFORM_TYPE::SIGN: + { + for (SCROW nRow = 0; nRow <= nEndRow; ++nRow) + { + CellType eType; + rDoc.GetCellType(mnCol, nRow, 0, eType); + if (eType == CELLTYPE_VALUE) + { + double nVal = rDoc.GetValue(mnCol, nRow, 0); + if (nVal > 0) + rDoc.SetValue(mnCol, nRow, 0, 1); + else if (nVal < 0) + rDoc.SetValue(mnCol, nRow, 0, -1); + else + rDoc.SetValue(mnCol, nRow, 0, 0); + } + } + } + break; + default: + break; + } +} + +TransformationType NumberTransformation::getTransformationType() const +{ + return TransformationType::NUMBER_TRANSFORMATION; +} + } /* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/sc/source/ui/inc/datatransformation.hxx b/sc/source/ui/inc/datatransformation.hxx index 6e49a3263bc9..611f108abb54 100644 --- a/sc/source/ui/inc/datatransformation.hxx +++ b/sc/source/ui/inc/datatransformation.hxx @@ -28,13 +28,17 @@ enum class TransformationType DELETE_TRANSFORMATION, SORT_TRANSFORMATION, TEXT_TRANSFORMATION, - AGGREGATE_FUNCTION + AGGREGATE_FUNCTION, + NUMBER_TRANSFORMATION }; enum class TEXT_TRANSFORM_TYPE { TO_LOWER, TO_UPPER, CAPITALIZE, TRIM }; enum class AGGREGATE_FUNCTION { SUM, AVERAGE, MIN, MAX }; +enum class NUMBER_TRANSFORM_TYPE { ROUND, ROUND_UP, ROUND_DOWN, ABSOLUTE, LOG_E, LOG_10, CUBE, + SQUARE, SQUARE_ROOT, EXPONENT, IS_EVEN, IS_ODD, SIGN }; + class SC_DLLPUBLIC DataTransformation { protected: @@ -124,6 +128,19 @@ class SC_DLLPUBLIC AggregateFunction : public DataTransformation virtual TransformationType getTransformationType() const override; }; +class SC_DLLPUBLIC NumberTransformation : public DataTransformation +{ + SCCOL mnCol; + NUMBER_TRANSFORM_TYPE maType; + int maPrecision; + + public: + NumberTransformation(SCCOL nCol, const NUMBER_TRANSFORM_TYPE rType); + NumberTransformation(SCCOL nCol, const NUMBER_TRANSFORM_TYPE rType, int nPrecision); + virtual void Transform(ScDocument& rDoc) const override; + virtual TransformationType getTransformationType() const override; +}; + } #endif |