sc/qa/unit/datatransformation_test.cxx | 322 ++++++++++++++
sc/source/ui/dataprovider/datatransformation.cxx | 526 ++++++++++++++++++++++-
sc/source/ui/inc/datatransformation.hxx | 20
3 files changed, 866 insertions(+), 2 deletions(-)
New commits:
commit 38ec31b6ff7341f7c1b050ba650798b35f13e52c
Author: Vikas Mahato <vikasmaha...@gmail.com>
AuthorDate: Fri Jul 27 07:26:48 2018 +0530
Commit: Markus Mohrhard <markus.mohrh...@googlemail.com>
CommitDate: Mon Aug 6 17:48:42 2018 +0200
Added date and time transformations
Change-Id: Icbea042f14ec6559597923f42fee26e666b2cc3c
Reviewed-on: https://gerrit.libreoffice.org/58152
Tested-by: Jenkins
Reviewed-by: Markus Mohrhard <markus.mohrh...@googlemail.com>
diff --git a/sc/qa/unit/datatransformation_test.cxx
b/sc/qa/unit/datatransformation_test.cxx
index 06d82181657c..97a8e49c79d8 100644
--- a/sc/qa/unit/datatransformation_test.cxx
+++ b/sc/qa/unit/datatransformation_test.cxx
@@ -52,6 +52,24 @@ public:
void testNumberOdd();
void testNumberSign();
void testReplaceNull();
+ void testGetDateString();
+ void testGetYear();
+ void testGetStartOfYear();
+ void testGetEndOfYear();
+ void testGetMonth();
+ void testGetMonthName();
+ void testGetStartOfMonth();
+ void testGetEndOfMonth();
+ void testGetDay();
+ void testGetDayOfWeek();
+ void testGetDayOfYear();
+ void testGetQuarter();
+ void testGetStartOfQuarter();
+ void testGetEndOfQuarter();
+ void testGetTime();
+ void testGetHour();
+ void testGetMinute();
+ void testGetSecond();
CPPUNIT_TEST_SUITE(ScDataTransformationTest);
CPPUNIT_TEST(testColumnRemove);
@@ -78,6 +96,24 @@ public:
CPPUNIT_TEST(testNumberOdd);
CPPUNIT_TEST(testNumberSign);
CPPUNIT_TEST(testReplaceNull);
+ CPPUNIT_TEST(testGetDateString);
+ CPPUNIT_TEST(testGetYear);
+ CPPUNIT_TEST(testGetStartOfYear);
+ CPPUNIT_TEST(testGetEndOfYear);
+ CPPUNIT_TEST(testGetMonth);
+ CPPUNIT_TEST(testGetMonthName);
+ CPPUNIT_TEST(testGetStartOfMonth);
+ CPPUNIT_TEST(testGetEndOfMonth);
+ CPPUNIT_TEST(testGetDay);
+ CPPUNIT_TEST(testGetDayOfWeek);
+ CPPUNIT_TEST(testGetDayOfYear);
+ CPPUNIT_TEST(testGetQuarter);
+ CPPUNIT_TEST(testGetStartOfQuarter);
+ CPPUNIT_TEST(testGetEndOfQuarter);
+ CPPUNIT_TEST(testGetTime);
+ CPPUNIT_TEST(testGetHour);
+ CPPUNIT_TEST(testGetMinute);
+ CPPUNIT_TEST(testGetSecond);
CPPUNIT_TEST_SUITE_END();
private:
@@ -505,6 +541,292 @@ void ScDataTransformationTest::testReplaceNull()
}
+void ScDataTransformationTest::testGetDateString()
+{
+ m_pDoc->SetValue(2, 0, 0, 43248.5624189815);
+ m_pDoc->SetValue(2, 1, 0, 42941.5624189815);
+ m_pDoc->SetValue(2, 2, 0, 42518.5624189815);
+
+ sc:: DateTimeTransformation aTransform({2},
sc::DATETIME_TRANSFORMATION_TYPE::DATE_STRING );
+ aTransform.Transform(*m_pDoc);
+
+ CPPUNIT_ASSERT_EQUAL(OUString("05/28/18"), m_pDoc->GetString(2, 0, 0));
+ CPPUNIT_ASSERT_EQUAL(OUString("07/25/17"), m_pDoc->GetString(2, 1, 0));
+ CPPUNIT_ASSERT_EQUAL(OUString("05/28/16"), m_pDoc->GetString(2, 2, 0));
+}
+
+void ScDataTransformationTest::testGetYear()
+{
+ m_pDoc->SetValue(2, 0, 0, 20);
+ m_pDoc->SetValue(2, 1, 0, 3342.44);
+ m_pDoc->SetValue(2, 2, 0, 955.05);
+ m_pDoc->SetValue(2, 3, 0, 4890.22);
+
+ sc:: DateTimeTransformation aTransform({2},
sc::DATETIME_TRANSFORMATION_TYPE::YEAR );
+ aTransform.Transform(*m_pDoc);
+
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(1900, m_pDoc->GetValue(2, 0, 0), 0);
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(1909, m_pDoc->GetValue(2, 1, 0), 0);
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(1902, m_pDoc->GetValue(2, 2, 0), 0);
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(1913, m_pDoc->GetValue(2, 3, 0), 0);
+}
+
+void ScDataTransformationTest::testGetStartOfYear()
+{
+ m_pDoc->SetValue(2, 0, 0, 43248.5624189815);
+ m_pDoc->SetValue(2, 1, 0, 42941.5624189815);
+ m_pDoc->SetValue(2, 2, 0, 42518.5624189815);
+ m_pDoc->SetValue(2, 3, 0, 44217.5624189815);
+
+ sc:: DateTimeTransformation aTransform({2},
sc::DATETIME_TRANSFORMATION_TYPE::START_OF_YEAR );
+ aTransform.Transform(*m_pDoc);
+
+ CPPUNIT_ASSERT_EQUAL(OUString("01/01/18"), m_pDoc->GetString(2, 0, 0));
+ CPPUNIT_ASSERT_EQUAL(OUString("01/01/17"), m_pDoc->GetString(2, 1, 0));
+ CPPUNIT_ASSERT_EQUAL(OUString("01/01/16"), m_pDoc->GetString(2, 2, 0));
+ CPPUNIT_ASSERT_EQUAL(OUString("01/01/21"), m_pDoc->GetString(2, 3, 0));
+}
+
+void ScDataTransformationTest::testGetEndOfYear()
+{
+ m_pDoc->SetValue(2, 0, 0, 43248.5624189815);
+ m_pDoc->SetValue(2, 1, 0, 42941.5624189815);
+ m_pDoc->SetValue(2, 2, 0, 42518.5624189815);
+ m_pDoc->SetValue(2, 3, 0, 44217.5624189815);
+
+ sc:: DateTimeTransformation aTransform({2},
sc::DATETIME_TRANSFORMATION_TYPE::END_OF_YEAR );
+ aTransform.Transform(*m_pDoc);
+
+ CPPUNIT_ASSERT_EQUAL(OUString("12/31/18"), m_pDoc->GetString(2, 0, 0));
+ CPPUNIT_ASSERT_EQUAL(OUString("12/31/17"), m_pDoc->GetString(2, 1, 0));
+ CPPUNIT_ASSERT_EQUAL(OUString("12/31/16"), m_pDoc->GetString(2, 2, 0));
+ CPPUNIT_ASSERT_EQUAL(OUString("12/31/21"), m_pDoc->GetString(2, 3, 0));
+}
+
+void ScDataTransformationTest::testGetMonth()
+{
+ m_pDoc->SetValue(2, 0, 0, 20);
+ m_pDoc->SetValue(2, 1, 0, 3342.44);
+ m_pDoc->SetValue(2, 2, 0, 955.05);
+ m_pDoc->SetValue(2, 3, 0, 4890.22);
+
+ sc:: DateTimeTransformation aTransform({2},
sc::DATETIME_TRANSFORMATION_TYPE::MONTH );
+ aTransform.Transform(*m_pDoc);
+
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(1, m_pDoc->GetValue(2, 0, 0), 0);
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(2, m_pDoc->GetValue(2, 1, 0), 0);
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(8, m_pDoc->GetValue(2, 2, 0), 0);
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(5, m_pDoc->GetValue(2, 3, 0), 0);
+}
+
+void ScDataTransformationTest::testGetMonthName()
+{
+ m_pDoc->SetValue(2, 0, 0, 20);
+ m_pDoc->SetValue(2, 1, 0, 3342.44);
+ m_pDoc->SetValue(2, 2, 0, 955.05);
+ m_pDoc->SetValue(2, 3, 0, 4890.22);
+
+ sc:: DateTimeTransformation aTransform({2},
sc::DATETIME_TRANSFORMATION_TYPE::MONTH_NAME);
+ aTransform.Transform(*m_pDoc);
+
+ CPPUNIT_ASSERT_EQUAL(OUString("January"), m_pDoc->GetString(2, 0, 0));
+ CPPUNIT_ASSERT_EQUAL(OUString("February"), m_pDoc->GetString(2, 1, 0));
+ CPPUNIT_ASSERT_EQUAL(OUString("August"), m_pDoc->GetString(2, 2, 0));
+ CPPUNIT_ASSERT_EQUAL(OUString("May"), m_pDoc->GetString(2, 3, 0));
+}
+
+void ScDataTransformationTest::testGetStartOfMonth()
+{
+ m_pDoc->SetValue(2, 0, 0, 43248.5624189815);
+ m_pDoc->SetValue(2, 1, 0, 42941.562418981);
+ m_pDoc->SetValue(2, 2, 0, 42518.5624189815);
+ m_pDoc->SetValue(2, 3, 0, 44217.5624189815);
+
+ sc:: DateTimeTransformation aTransform({2},
sc::DATETIME_TRANSFORMATION_TYPE::START_OF_MONTH );
+ aTransform.Transform(*m_pDoc);
+
+ CPPUNIT_ASSERT_EQUAL(OUString("05/01/18"), m_pDoc->GetString(2, 0, 0));
+ CPPUNIT_ASSERT_EQUAL(OUString("07/01/17"), m_pDoc->GetString(2, 1, 0));
+ CPPUNIT_ASSERT_EQUAL(OUString("05/01/16"), m_pDoc->GetString(2, 2, 0));
+ CPPUNIT_ASSERT_EQUAL(OUString("01/01/21"), m_pDoc->GetString(2, 3, 0));
+}
+
+void ScDataTransformationTest::testGetEndOfMonth()
+{
+ m_pDoc->SetValue(2, 0, 0, 43248.5624189815);
+ m_pDoc->SetValue(2, 1, 0, 42941.5624189815);
+ m_pDoc->SetValue(2, 2, 0, 42518.5624189815);
+ m_pDoc->SetValue(2, 3, 0, 44217.5624189815);
+
+ sc:: DateTimeTransformation aTransform({2},
sc::DATETIME_TRANSFORMATION_TYPE::END_OF_MONTH );
+ aTransform.Transform(*m_pDoc);
+
+ CPPUNIT_ASSERT_EQUAL(OUString("05/31/18"), m_pDoc->GetString(2, 0, 0));
+ CPPUNIT_ASSERT_EQUAL(OUString("07/31/17"), m_pDoc->GetString(2, 1, 0));
+ CPPUNIT_ASSERT_EQUAL(OUString("05/31/16"), m_pDoc->GetString(2, 2, 0));
+ CPPUNIT_ASSERT_EQUAL(OUString("01/31/21"), m_pDoc->GetString(2, 3, 0));
+}
+
+void ScDataTransformationTest::testGetDay()
+{
+ m_pDoc->SetValue(2, 0, 0, 20);
+ m_pDoc->SetValue(2, 1, 0, 3342.44);
+ m_pDoc->SetValue(2, 2, 0, 955.05);
+ m_pDoc->SetValue(2, 3, 0, 4890.22);
+
+ sc:: DateTimeTransformation aTransform({2},
sc::DATETIME_TRANSFORMATION_TYPE::DAY );
+ aTransform.Transform(*m_pDoc);
+
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(20, m_pDoc->GetValue(2, 0, 0), 0);
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(24, m_pDoc->GetValue(2, 1, 0), 0);
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(13, m_pDoc->GetValue(2, 2, 0), 0);
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(22, m_pDoc->GetValue(2, 3, 0), 0);
+}
+
+void ScDataTransformationTest::testGetDayOfWeek()
+{
+ m_pDoc->SetValue(2, 0, 0, 20);
+ m_pDoc->SetValue(2, 1, 0, 3342.44);
+ m_pDoc->SetValue(2, 2, 0, 955.05);
+ m_pDoc->SetValue(2, 3, 0, 4890.22);
+
+ sc:: DateTimeTransformation aTransform({2},
sc::DATETIME_TRANSFORMATION_TYPE::DAY_OF_WEEK );
+ aTransform.Transform(*m_pDoc);
+
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(5, m_pDoc->GetValue(2, 0, 0), 0);
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(2, m_pDoc->GetValue(2, 1, 0), 0);
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(2, m_pDoc->GetValue(2, 2, 0), 0);
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(3, m_pDoc->GetValue(2, 3, 0), 0);
+}
+
+void ScDataTransformationTest::testGetDayOfYear()
+{
+ m_pDoc->SetValue(2, 0, 0, 20);
+ m_pDoc->SetValue(2, 1, 0, 3342.44);
+ m_pDoc->SetValue(2, 2, 0, 955.05);
+ m_pDoc->SetValue(2, 3, 0, 4890.22);
+
+ sc:: DateTimeTransformation aTransform({2},
sc::DATETIME_TRANSFORMATION_TYPE::DAY_OF_YEAR );
+ aTransform.Transform(*m_pDoc);
+
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(20, m_pDoc->GetValue(2, 0, 0), 0);
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(55, m_pDoc->GetValue(2, 1, 0), 0);
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(225, m_pDoc->GetValue(2, 2, 0), 0);
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(142, m_pDoc->GetValue(2, 3, 0), 0);
+}
+
+void ScDataTransformationTest::testGetQuarter()
+{
+ m_pDoc->SetValue(2, 0, 0, 20);
+ m_pDoc->SetValue(2, 1, 0, 3342.44);
+ m_pDoc->SetValue(2, 2, 0, 955.05);
+ m_pDoc->SetValue(2, 3, 0, 4890.22);
+
+ sc:: DateTimeTransformation aTransform({2},
sc::DATETIME_TRANSFORMATION_TYPE::QUARTER );
+ aTransform.Transform(*m_pDoc);
+
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(1, m_pDoc->GetValue(2, 0, 0), 0);
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(1, m_pDoc->GetValue(2, 1, 0), 0);
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(3, m_pDoc->GetValue(2, 2, 0), 0);
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(2, m_pDoc->GetValue(2, 3, 0), 0);
+}
+
+void ScDataTransformationTest::testGetStartOfQuarter()
+{
+ m_pDoc->SetValue(2, 0, 0, 43148.5624189815);
+ m_pDoc->SetValue(2, 1, 0, 43264.3055555556);
+ m_pDoc->SetValue(2, 2, 0, 43306.4946990741);
+ m_pDoc->SetValue(2, 3, 0, 43406.4946990741);
+
+ sc:: DateTimeTransformation aTransform({2},
sc::DATETIME_TRANSFORMATION_TYPE::START_OF_QUARTER );
+ aTransform.Transform(*m_pDoc);
+
+ CPPUNIT_ASSERT_EQUAL(OUString("01/01/18"), m_pDoc->GetString(2, 0, 0));
+ CPPUNIT_ASSERT_EQUAL(OUString("04/01/18"), m_pDoc->GetString(2, 1, 0));
+ CPPUNIT_ASSERT_EQUAL(OUString("07/01/18"), m_pDoc->GetString(2, 2, 0));
+ CPPUNIT_ASSERT_EQUAL(OUString("10/01/18"), m_pDoc->GetString(2, 3, 0));
+}
+
+void ScDataTransformationTest::testGetEndOfQuarter()
+{
+ m_pDoc->SetValue(2, 0, 0, 43148.5624189815);
+ m_pDoc->SetValue(2, 1, 0, 43264.3055555556);
+ m_pDoc->SetValue(2, 2, 0, 43306.4946990741);
+ m_pDoc->SetValue(2, 3, 0, 43406.4946990741);
+
+ sc:: DateTimeTransformation aTransform({2},
sc::DATETIME_TRANSFORMATION_TYPE::END_OF_QUARTER );
+ aTransform.Transform(*m_pDoc);
+
+ CPPUNIT_ASSERT_EQUAL(OUString("03/31/18"), m_pDoc->GetString(2, 0, 0));
+ CPPUNIT_ASSERT_EQUAL(OUString("06/30/18"), m_pDoc->GetString(2, 1, 0));
+ CPPUNIT_ASSERT_EQUAL(OUString("09/30/18"), m_pDoc->GetString(2, 2, 0));
+ CPPUNIT_ASSERT_EQUAL(OUString("12/31/18"), m_pDoc->GetString(2, 3, 0));
+}
+
+void ScDataTransformationTest::testGetTime()
+{
+ m_pDoc->SetValue(2, 0, 0, 20.562419);
+ m_pDoc->SetValue(2, 1, 0, 43249.3077546296);
+ m_pDoc->SetValue(2, 2, 0, 43249.3990740741);
+ m_pDoc->SetValue(2, 3, 0, 43249.4234837963);
+
+ sc:: DateTimeTransformation aTransform({2},
sc::DATETIME_TRANSFORMATION_TYPE::TIME );
+ aTransform.Transform(*m_pDoc);
+
+ CPPUNIT_ASSERT_EQUAL(OUString("01:29:53 PM"), m_pDoc->GetString(2, 0, 0));
+ CPPUNIT_ASSERT_EQUAL(OUString("07:23:09 AM"), m_pDoc->GetString(2, 1, 0));
+ CPPUNIT_ASSERT_EQUAL(OUString("09:34:40 AM"), m_pDoc->GetString(2, 2, 0));
+ CPPUNIT_ASSERT_EQUAL(OUString("10:09:49 AM"), m_pDoc->GetString(2, 3, 0));
+}
+
+void ScDataTransformationTest::testGetHour()
+{
+ m_pDoc->SetValue(2, 0, 0, 20.562419);
+ m_pDoc->SetValue(2, 1, 0, 43249.3077546296);
+ m_pDoc->SetValue(2, 2, 0, 43249.3990740741);
+ m_pDoc->SetValue(2, 3, 0, 43249.4234837963);
+
+ sc:: DateTimeTransformation aTransform({2},
sc::DATETIME_TRANSFORMATION_TYPE::HOUR );
+ aTransform.Transform(*m_pDoc);
+
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(13, m_pDoc->GetValue(2, 0, 0), 0);
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(7, m_pDoc->GetValue(2, 1, 0), 0);
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(9, m_pDoc->GetValue(2, 2, 0), 0);
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(10, m_pDoc->GetValue(2, 3, 0), 0);
+}
+
+void ScDataTransformationTest::testGetMinute()
+{
+ m_pDoc->SetValue(2, 0, 0, 20.562419);
+ m_pDoc->SetValue(2, 1, 0, 43249.3077546296);
+ m_pDoc->SetValue(2, 2, 0, 43249.3990740741);
+ m_pDoc->SetValue(2, 3, 0, 43249.4234837963);
+
+ sc:: DateTimeTransformation aTransform({2},
sc::DATETIME_TRANSFORMATION_TYPE::MINUTE );
+ aTransform.Transform(*m_pDoc);
+
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(29, m_pDoc->GetValue(2, 0, 0), 0);
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(23, m_pDoc->GetValue(2, 1, 0), 0);
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(34, m_pDoc->GetValue(2, 2, 0), 0);
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(9, m_pDoc->GetValue(2, 3, 0), 0);
+}
+
+void ScDataTransformationTest::testGetSecond()
+{
+ m_pDoc->SetValue(2, 0, 0, 20.562419);
+ m_pDoc->SetValue(2, 1, 0, 43249.3077546296);
+ m_pDoc->SetValue(2, 2, 0, 43249.3990740741);
+ m_pDoc->SetValue(2, 3, 0, 43249.4234837963);
+
+ sc:: DateTimeTransformation aTransform({2},
sc::DATETIME_TRANSFORMATION_TYPE::SECOND );
+ aTransform.Transform(*m_pDoc);
+
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(53, m_pDoc->GetValue(2, 0, 0), 0);
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(9, m_pDoc->GetValue(2, 1, 0), 0);
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(40, m_pDoc->GetValue(2, 2, 0), 0);
+ CPPUNIT_ASSERT_DOUBLES_EQUAL(49, m_pDoc->GetValue(2, 3, 0), 0);
+}
+
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 9efe3ed447e9..ff3aab748fa9 100644
--- a/sc/source/ui/dataprovider/datatransformation.cxx
+++ b/sc/source/ui/dataprovider/datatransformation.cxx
@@ -12,9 +12,68 @@
#include <document.hxx>
#include <rtl/math.hxx>
#include <cmath>
+#include <tools/datetime.hxx>
+#include <svl/zforlist.hxx>
+namespace {
-namespace sc {
+int getHour(double nDateTime)
+{
+ long nDays = std::trunc(nDateTime);
+ double nTime = nDateTime - nDays;
+ return std::trunc(nTime*24);
+}
+
+int getMinute(double nDateTime)
+{
+ long nDays = std::trunc(nDateTime);
+ double nTime = nDateTime - nDays;
+ nTime = nTime*24;
+ nTime = nTime - std::trunc(nTime);
+ return std::trunc(nTime*60);
+}
+
+int getSecond(double nDateTime)
+{
+ double nDays = std::trunc(nDateTime);
+ double nTime = nDateTime - nDays;
+ nTime = nTime*24;
+ nTime = nTime - std::trunc(nTime);
+ nTime = nTime*60;
+ nTime = nTime - std::trunc(nTime);
+ return std::trunc(nTime*60);
+}
+
+OUString getTwoDigitString(OUString sString)
+{
+ if(sString.getLength() == 1)
+ sString = "0" + sString;
+ return sString;
+}
+
+DateTime getDate(double nDateTime, SvNumberFormatter* pFormatter)
+{
+ sal_Int32 nDays = std::trunc(nDateTime);
+ Date aDate = pFormatter->GetNullDate();
+ aDate.AddDays(nDays + 1);
+ return aDate;
+}
+
+OUString getTimeString(double nDateTime)
+{
+ OUString sHour = OUString::number(getHour(nDateTime));
+ sHour = getTwoDigitString(sHour);
+
+ OUString sMinute = OUString::number(getMinute(nDateTime));
+ sMinute = getTwoDigitString(sMinute);
+
+ OUString sSecond = OUString::number(getSecond(nDateTime));
+ sSecond = getTwoDigitString(sSecond);
+
+ return sHour + ":" + sMinute + ":" + sSecond;
+}
+}
+namespace sc {
DataTransformation::~DataTransformation()
{
}
@@ -697,6 +756,471 @@ TransformationType
ReplaceNullTransformation::getTransformationType() const
{
return TransformationType::REMOVE_NULL_TRANSFORMATION;
}
+
+
+DateTimeTransformation::DateTimeTransformation(const std::set<SCCOL>
nCol,const DATETIME_TRANSFORMATION_TYPE rType):
+ mnCol(nCol),
+ maType(rType)
+{
+}
+
+void DateTimeTransformation::Transform(ScDocument& rDoc) const
+{
+ SCROW nEndRow = 0;
+ for(auto& rCol : mnCol)
+ {
+ nEndRow = getLastRow(rDoc, rCol);
+ }
+
+ for(auto& rCol : mnCol)
+ {
+ switch (maType)
+ {
+ case DATETIME_TRANSFORMATION_TYPE::DATE_STRING:
+ {
+ 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);
+
+ SvNumberFormatter* pFormatter = rDoc.GetFormatTable();
+ SvNumFormatType nFormatType = SvNumFormatType::DATE;
+ LanguageType eLanguage = ScGlobal::eLnge;
+ ScAddress aAddress(rCol, nRow, 0);
+ sal_uLong nFormat = pFormatter->GetStandardFormat(
nFormatType, eLanguage );
+ rDoc.SetValue(rCol, nRow, 0, nVal);
+ rDoc.SetNumberFormat(aAddress, nFormat);
+ }
+ }
+ }
+ break;
+ case DATETIME_TRANSFORMATION_TYPE::YEAR:
+ {
+ 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);
+ SvNumberFormatter* pFormatter = rDoc.GetFormatTable();
+ Date aDate = getDate(nVal, pFormatter);
+ rDoc.SetValue(rCol, nRow, 0, aDate.GetYear());
+ }
+ }
+ }
+ break;
+ case DATETIME_TRANSFORMATION_TYPE::START_OF_YEAR:
+ {
+ 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);
+
+ SvNumberFormatter* pFormatter = rDoc.GetFormatTable();
+ Date aDate = getDate(nVal, pFormatter);
+ nVal -= aDate.GetDayOfYear() - 2;
+ nVal = std::trunc(nVal);
+ SvNumFormatType nFormatType = SvNumFormatType::DATE;
+ LanguageType eLanguage = ScGlobal::eLnge;
+ ScAddress aAddress(rCol, nRow, 0);
+ sal_uLong nFormat = pFormatter->GetStandardFormat(
nFormatType, eLanguage );
+ rDoc.SetValue(rCol, nRow, 0, nVal);
+
+ rDoc.SetNumberFormat(aAddress, nFormat);
+ }
+ }
+ }
+ break;
+ case DATETIME_TRANSFORMATION_TYPE::END_OF_YEAR:
+ {
+ 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);
+
+ SvNumberFormatter* pFormatter = rDoc.GetFormatTable();
+ Date aDate = getDate(nVal, pFormatter);
+ nVal += ( aDate.GetDaysInYear() - aDate.GetDayOfYear()
+ 1);
+ nVal = std::trunc(nVal);
+ SvNumFormatType nFormatType = SvNumFormatType::DATE;
+ LanguageType eLanguage = ScGlobal::eLnge;
+ ScAddress aAddress(rCol, nRow, 0);
+ sal_uLong nFormat = pFormatter->GetStandardFormat(
nFormatType, eLanguage );
+ rDoc.SetValue(rCol, nRow, 0, nVal);
+
+ rDoc.SetNumberFormat(aAddress, nFormat);
+ }
+ }
+ }
+ break;
+ case DATETIME_TRANSFORMATION_TYPE::MONTH:
+ {
+ 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);
+ SvNumberFormatter* pFormatter = rDoc.GetFormatTable();
+ Date aDate = getDate(nVal, pFormatter);
+ rDoc.SetValue(rCol, nRow, 0, aDate.GetMonth());
+ }
+ }
+ }
+ break;
+ case DATETIME_TRANSFORMATION_TYPE::MONTH_NAME:
+ {
+ for (SCROW nRow = 0; nRow <= nEndRow; ++nRow)
+ {
+ CellType eType;
+ rDoc.GetCellType(rCol, nRow, 0, eType);
+ if (eType == CELLTYPE_VALUE)
+ {
+ OUString aMonths[] = {"January", "February", "March",
"April", "May",
+ "June", "July", "August", "September", "October",
"November", "December"};
+
+ double nVal = rDoc.GetValue(rCol, nRow, 0);
+ SvNumberFormatter* pFormatter = rDoc.GetFormatTable();
+ Date aDate = getDate(nVal, pFormatter);
+ rDoc.SetString(rCol, nRow, 0, aMonths[aDate.GetMonth()
- 1]);
+ }
+ }
+ }
+ break;
+ case DATETIME_TRANSFORMATION_TYPE::START_OF_MONTH:
+ {
+ 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);
+ SvNumberFormatter* pFormatter = rDoc.GetFormatTable();
+ SvNumFormatType nFormatType = SvNumFormatType::DATE;
+ LanguageType eLanguage = ScGlobal::eLnge;
+ ScAddress aAddress(rCol, nRow, 0);
+ sal_uLong nFormat = pFormatter->GetStandardFormat(
nFormatType, eLanguage );
+
+ Date aDate = getDate(nVal, pFormatter);
+ Date aStart(1,aDate.GetMonth(), aDate.GetYear());
+ int nDays = aDate.GetDayOfYear() -
aStart.GetDayOfYear() - 1;
+ rDoc.SetValue(rCol, nRow, 0, nVal - nDays);
+ rDoc.SetNumberFormat(aAddress, nFormat);
+ }
+ }
+ }
+ break;
+ case DATETIME_TRANSFORMATION_TYPE::END_OF_MONTH:
+ {
+ 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);
+ SvNumberFormatter* pFormatter = rDoc.GetFormatTable();
+ SvNumFormatType nFormatType = SvNumFormatType::DATE;
+ LanguageType eLanguage = ScGlobal::eLnge;
+ ScAddress aAddress(rCol, nRow, 0);
+ sal_uLong nFormat = pFormatter->GetStandardFormat(
nFormatType, eLanguage );
+
+ Date aDate = getDate(nVal, pFormatter);
+ Date aEnd(aDate.GetDaysInMonth(),aDate.GetMonth(),
aDate.GetYear());
+
+ int nDays = aEnd.GetDayOfYear() - aDate.GetDayOfYear()
+ 1;
+ rDoc.SetValue(rCol, nRow, 0, nVal + nDays);
+ rDoc.SetNumberFormat(aAddress, nFormat);
+ }
+ }
+ }
+ break;
+ case DATETIME_TRANSFORMATION_TYPE::DAY:
+ {
+ 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);
+ SvNumberFormatter* pFormatter = rDoc.GetFormatTable();
+ Date aDate = getDate(nVal, pFormatter);
+ rDoc.SetValue(rCol, nRow, 0, aDate.GetDay());
+ }
+ }
+ }
+ break;
+ case DATETIME_TRANSFORMATION_TYPE::DAY_OF_WEEK:
+ {
+ 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);
+ SvNumberFormatter* pFormatter = rDoc.GetFormatTable();
+ Date aDate = getDate(nVal, pFormatter);
+ rDoc.SetValue(rCol, nRow, 0, aDate.GetDayOfWeek());
+ }
+ }
+ }
+ break;
+ case DATETIME_TRANSFORMATION_TYPE::DAY_OF_YEAR:
+ {
+ 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);
+ SvNumberFormatter* pFormatter = rDoc.GetFormatTable();
+ Date aDate = getDate(nVal, pFormatter);
+ rDoc.SetValue(rCol, nRow, 0, aDate.GetDayOfYear());
+ }
+ }
+ }
+ break;
+ case DATETIME_TRANSFORMATION_TYPE::QUARTER:
+ {
+ 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);
+ SvNumberFormatter* pFormatter = rDoc.GetFormatTable();
+ Date aDate = getDate(nVal, pFormatter);
+
+ int nMonth = 1 + aDate.GetMonth();
+
+ if(nMonth >= 1 && nMonth <=3)
+ rDoc.SetValue(rCol, nRow, 0, 1);
+
+ else if(nMonth >= 4 && nMonth <=6)
+ rDoc.SetValue(rCol, nRow, 0, 2);
+
+ else if(nMonth >= 7 && nMonth <=9)
+ rDoc.SetValue(rCol, nRow, 0, 3);
+
+ else if(nMonth >= 10 && nMonth <=12)
+ rDoc.SetValue(rCol, nRow, 0, 4);
+ else
+ rDoc.SetValue(rCol, nRow, 0, -1);
+
+ }
+ }
+ }
+ break;
+ case DATETIME_TRANSFORMATION_TYPE::START_OF_QUARTER:
+ {
+ 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);
+ SvNumberFormatter* pFormatter = rDoc.GetFormatTable();
+ SvNumFormatType nFormatType = SvNumFormatType::DATE;
+ LanguageType eLanguage = ScGlobal::eLnge;
+ ScAddress aAddress(rCol, nRow, 0);
+ sal_uLong nFormat = pFormatter->GetStandardFormat(
nFormatType, eLanguage );
+ Date aDate = getDate(nVal, pFormatter);
+
+ int nMonth = aDate.GetMonth();
+
+ if(nMonth >= 1 && nMonth <=3)
+ {
+ Date aQuarterDate(1,1,aDate.GetYear());
+ int days = aDate.GetDayOfYear() -
aQuarterDate.GetDayOfYear() - 1;
+ nVal -= days;
+ rDoc.SetValue(rCol, nRow, 0, nVal);
+ rDoc.SetNumberFormat(aAddress, nFormat);
+ }
+ else if(nMonth >= 4 && nMonth <=6)
+ {
+ Date aQuarterDate(1,4,aDate.GetYear());
+ int days = aDate.GetDayOfYear() -
aQuarterDate.GetDayOfYear() - 1;
+ nVal -= days;
+ rDoc.SetValue(rCol, nRow, 0, nVal);
+ rDoc.SetNumberFormat(aAddress, nFormat);
+ }
+ else if(nMonth >= 7 && nMonth <=9)
+ {
+ Date aQuarterDate(1,7,aDate.GetYear());
+ int days = aDate.GetDayOfYear() -
aQuarterDate.GetDayOfYear() - 1;
+ nVal -= days;
+ rDoc.SetValue(rCol, nRow, 0, nVal);
+ rDoc.SetNumberFormat(aAddress, nFormat);
+ }
+ else if(nMonth >= 10 && nMonth <=12)
+ {
+ Date aQuarterDate(1,10,aDate.GetYear());
+ int days = aDate.GetDayOfYear() -
aQuarterDate.GetDayOfYear() - 1;
+ nVal -= days;
+ rDoc.SetValue(rCol, nRow, 0, nVal);
+ rDoc.SetNumberFormat(aAddress, nFormat);
+ }
+ else
+ rDoc.SetValue(rCol, nRow, 0, -1);
+ }
+ }
+ }
+ break;
+ case DATETIME_TRANSFORMATION_TYPE::END_OF_QUARTER:
+ {
+ for (SCROW nRow = 0; nRow <= nEndRow; ++nRow)
+ {
+ SvNumberFormatter* pFormatter = rDoc.GetFormatTable();
+ SvNumFormatType nFormatType = SvNumFormatType::DATE;
+ LanguageType eLanguage = ScGlobal::eLnge;
+ ScAddress aAddress(rCol, nRow, 0);
+ sal_uLong nFormat = pFormatter->GetStandardFormat(
nFormatType, eLanguage );
+ CellType eType;
+ rDoc.GetCellType(rCol, nRow, 0, eType);
+ if (eType == CELLTYPE_VALUE)
+ {
+ double nVal = rDoc.GetValue(rCol, nRow, 0);
+ nVal = std::trunc(nVal);
+ Date aDate = getDate(nVal, pFormatter);
+
+ int nMonth = aDate.GetMonth();
+
+ if(nMonth >= 1 && nMonth <=3)
+ {
+ Date aQuarterDate(31,3,aDate.GetYear());
+ int days = aQuarterDate.GetDayOfYear() -
aDate.GetDayOfYear() + 1;
+ nVal += days;
+ rDoc.SetValue(rCol, nRow, 0, nVal);
+ rDoc.SetNumberFormat(aAddress, nFormat);
+ }
+
+ else if(nMonth >= 4 && nMonth <=6)
+ {
+ Date aQuarterDate(30,6,aDate.GetYear());
+ int days = aQuarterDate.GetDayOfYear() -
aDate.GetDayOfYear() + 1;
+ nVal += days;
+ rDoc.SetValue(rCol, nRow, 0, nVal);
+ rDoc.SetNumberFormat(aAddress, nFormat);
+ }
+
+ else if(nMonth >= 7 && nMonth <=9)
+ {
+ Date aQuarterDate(30,9,aDate.GetYear());
+ int days = aQuarterDate.GetDayOfYear() -
aDate.GetDayOfYear() + 1;
+ nVal += days;
+ rDoc.SetValue(rCol, nRow, 0, nVal);
+ rDoc.SetNumberFormat(aAddress, nFormat);
+ }
+
+ else if(nMonth >= 10 && nMonth <=12)
+ {
+ Date aQuarterDate(31,12,aDate.GetYear());
+ int days = aQuarterDate.GetDayOfYear() -
aDate.GetDayOfYear() + 1;
+ nVal += days;
+ rDoc.SetValue(rCol, nRow, 0, nVal);
+ rDoc.SetNumberFormat(aAddress, nFormat);
+ }
+ else
+ rDoc.SetValue(rCol, nRow, 0, -1);
+
+ }
+ }
+ }
+ break;
+ case DATETIME_TRANSFORMATION_TYPE::TIME:
+ {
+ 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);
+ rDoc.SetString(rCol, nRow, 0, getTimeString(nVal));
+ }
+ }
+ }
+ break;
+ case DATETIME_TRANSFORMATION_TYPE::HOUR:
+ {
+ 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);
+ rDoc.SetValue(rCol, nRow, 0, getHour(nVal));
+ }
+ }
+ }
+ break;
+ case DATETIME_TRANSFORMATION_TYPE::MINUTE:
+ {
+ 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);
+ rDoc.SetValue(rCol, nRow, 0, getMinute(nVal));
+ }
+ }
+ }
+ break;
+ case DATETIME_TRANSFORMATION_TYPE::SECOND:
+ {
+ 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);
+ rDoc.SetValue(rCol, nRow, 0, getSecond(nVal));
+ }
+ }
+ }
+ break;
+ default:
+ break;
+ }
+ }
+}
+
+TransformationType DateTimeTransformation::getTransformationType() const
+{
+ return TransformationType::DATETIME_TRANSFORMATION;
+}
+
+DATETIME_TRANSFORMATION_TYPE
DateTimeTransformation::getDateTimeTransfromationType() const
+{
+ return maType;
+}
+
+std::set<SCCOL>DateTimeTransformation::getColumn() const
+{
+ return mnCol;
+}
+
}
/* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/source/ui/inc/datatransformation.hxx
b/sc/source/ui/inc/datatransformation.hxx
index 48651699a4ef..4d68046ffd85 100644
--- a/sc/source/ui/inc/datatransformation.hxx
+++ b/sc/source/ui/inc/datatransformation.hxx
@@ -30,7 +30,8 @@ enum class TransformationType
TEXT_TRANSFORMATION,
AGGREGATE_FUNCTION,
NUMBER_TRANSFORMATION,
- REMOVE_NULL_TRANSFORMATION
+ REMOVE_NULL_TRANSFORMATION,
+ DATETIME_TRANSFORMATION
};
enum class TEXT_TRANSFORM_TYPE { TO_LOWER, TO_UPPER, CAPITALIZE, TRIM };
@@ -40,6 +41,10 @@ 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 };
+enum class DATETIME_TRANSFORMATION_TYPE { DATE_STRING, YEAR, START_OF_YEAR,
END_OF_YEAR, MONTH,
+ MONTH_NAME, START_OF_MONTH, END_OF_MONTH, DAY, DAY_OF_WEEK, DAY_OF_YEAR,
QUARTER, START_OF_QUARTER,
+ END_OF_QUARTER, TIME, HOUR, MINUTE, SECOND };
+
class SC_DLLPUBLIC DataTransformation
{
protected:
@@ -162,6 +167,19 @@ class SC_DLLPUBLIC ReplaceNullTransformation : public
DataTransformation
OUString getReplaceString() const;
};
+class SC_DLLPUBLIC DateTimeTransformation : public DataTransformation
+{
+ std::set<SCCOL> mnCol;
+ DATETIME_TRANSFORMATION_TYPE maType;
+
+ public:
+ DateTimeTransformation(const std::set<SCCOL> nCol, const
DATETIME_TRANSFORMATION_TYPE rType);
+ virtual void Transform(ScDocument& rDoc) const override;
+ virtual TransformationType getTransformationType() const override;
+ DATETIME_TRANSFORMATION_TYPE getDateTimeTransfromationType() const;
+ std::set<SCCOL> getColumn() const;
+};
+
}
#endif
_______________________________________________
Libreoffice-commits mailing list
libreoffice-comm...@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-commits
