Implement unit conversion for ranges.

Not entirely finished yet, some further refactoring needed elsewhere
to allow sensible implementation of the header editing.

Change-Id: I81af74d698098f901b17fcda413e7aac04c94274

4 files changed, 205 insertions, 0 deletions

diff --git a/sc/inc/units.hxx b/sc/inc/units.hxx
index 9837d3664c20..381ec7dc08e1 100644
--- a/sc/inc/units.hxx
+++ b/sc/inc/units.hxx
@@ -16,6 +16,7 @@
 
 class ScAddress;
 class ScDocument;
+class ScRange;
 class ScTokenArray;
 
 namespace sc {
@@ -75,6 +76,27 @@ public:
                                          const OUString& rsNewUnit,
                                          const OUString& rsOldUnit) = 0;
 
+    /**
+     * Convert cells from one unit to another.
+     *
+     * If possible the input unit will be determined automatically (using local
+     * and header units).
+     *
+     * Returns false if input units are not compatible with the desired output units,
+     * (including the case where some of the input units are compatibles but others
+     *  aren't).
+     *
+     * Local and header unit annotations are modified as appropriate such that the output
+     * remains unambiguous. Hence, if the header cell is included in rRange, its unit
+     * annotation is also updated as appropriate. If instead the header is excluded,
+     * but all other cells are selected in a column, then local annotations are added.
+     *
+     * rsInputUnit overrides the automatic determination of input units, i.e. disables
+     * input unit detection.
+     */
+    virtual bool convertCellUnits(const ScRange& rRange,
+                                  ScDocument* pDoc,
+                                  const OUString& rsOutputUnit) = 0;
 
     virtual ~Units() {}
 };
diff --git a/sc/qa/unit/units.cxx b/sc/qa/unit/units.cxx
index e6ed77995d60..8e834830647c 100644
--- a/sc/qa/unit/units.cxx
+++ b/sc/qa/unit/units.cxx
@@ -48,6 +48,7 @@ public:
     void testUnitFromHeaderExtraction();
 
     void testCellConversion();
+    void testRangeConversion();
 
     CPPUNIT_TEST_SUITE(UnitsTest);
 
@@ -59,6 +60,7 @@ public:
     CPPUNIT_TEST(testUnitFromHeaderExtraction);
 
     CPPUNIT_TEST(testCellConversion);
+    CPPUNIT_TEST(testRangeConversion);
 
     CPPUNIT_TEST_SUITE_END();
 
@@ -524,6 +526,88 @@ void UnitsTest::testCellConversion() {
     // to pass in the output of isCellConversionRecommended).
 }
 
+void UnitsTest::testRangeConversion() {
+    const SCTAB nTab = 1;
+    mpDoc->EnsureTable(nTab);
+
+    // Column 1: convert [cm] to [cm].
+    ScAddress headerAddress(0, 0, nTab);
+    mpDoc->SetString(headerAddress, "length [cm]");
+
+    ScAddress address(headerAddress);
+
+    vector<double> values({10, 20, 30, 40, 1, 0.5, 0.25});
+    address.IncRow();
+    mpDoc->SetValues(address, values);
+
+    // Test conversion of range _not_ including header
+    ScAddress endAddress( address.Col(), address.Row() + values.size() - 1, nTab);
+
+    ScRange aRange(address, endAddress);
+    CPPUNIT_ASSERT(mpUnitsImpl->convertCellUnits(aRange, mpDoc, "cm"));
+    CPPUNIT_ASSERT(!mpUnitsImpl->convertCellUnits(aRange, mpDoc, "kg"));
+
+    CPPUNIT_ASSERT(mpDoc->GetString(headerAddress) == "length [cm]");
+
+    for (double d: values) {
+        // Test that the value is unchanged
+        CPPUNIT_ASSERT(mpDoc->GetValue(address) == d);
+        // And NO annotation has been added
+        CPPUNIT_ASSERT(mpDoc->GetString(address) == OUString::number(d));
+        address.IncRow();
+    }
+
+    // Test conversion of range including header (from cm to cm)
+    aRange = ScRange(headerAddress, endAddress);
+    CPPUNIT_ASSERT(mpUnitsImpl->convertCellUnits(aRange, mpDoc, "cm"));
+    CPPUNIT_ASSERT(!mpUnitsImpl->convertCellUnits(aRange, mpDoc, "kg"));
+
+    CPPUNIT_ASSERT(mpDoc->GetString(headerAddress) == "length [cm]");
+
+    address = headerAddress;
+    address.IncRow();
+    for (double d: values) {
+        CPPUNIT_ASSERT_DOUBLES_EQUAL(mpDoc->GetValue(address), d, 1e-7);
+        // And NO annotation has been added
+        CPPUNIT_ASSERT(mpDoc->GetString(address) == OUString::number(d));
+        address.IncRow();
+    }
+
+    // Convert just the values (but not header): [cm] to [m]
+    address.SetRow(1);
+    aRange = ScRange(address, endAddress);
+    CPPUNIT_ASSERT(mpUnitsImpl->convertCellUnits(aRange, mpDoc, "m"));
+
+    CPPUNIT_ASSERT(mpDoc->GetString(headerAddress) == "length [cm]");
+
+    for (double d: values) {
+        CPPUNIT_ASSERT_DOUBLES_EQUAL(mpDoc->GetValue(address), d/100, 1e-7);
+        // AND test annotation
+        // Disabled for now until the precision problems are figured out
+        // CPPUNIT_ASSERT(mpDoc->GetString(address) == OUString::number(d/100) + "m");
+        address.IncRow();
+    }
+
+    // Convert everything (including header) to mm: [m] to [mm]
+    aRange = ScRange(headerAddress, endAddress);
+    CPPUNIT_ASSERT(mpUnitsImpl->convertCellUnits(aRange, mpDoc, "mm"));
+
+    CPPUNIT_ASSERT(mpDoc->GetString(headerAddress) == "length [mm]");
+
+    address.SetRow(1);
+
+    for (double d: values) {
+        CPPUNIT_ASSERT_DOUBLES_EQUAL(mpDoc->GetValue(address), d*10, 1e-7);
+        // And the annotation has been REMOVED
+        CPPUNIT_ASSERT(mpDoc->GetString(address) == OUString::number(d*10));
+        address.IncRow();
+    }
+
+    // TODO: we need to test:
+    // 1. mixture of units that can't be converted
+    // 2. mixtures of local and header annotations
+}
+
 CPPUNIT_TEST_SUITE_REGISTRATION(UnitsTest);
 
 CPPUNIT_PLUGIN_IMPLEMENT();
diff --git a/sc/source/core/units/unitsimpl.cxx b/sc/source/core/units/unitsimpl.cxx
index e8fb261f8ec5..aa3fc1ea5579 100644
--- a/sc/source/core/units/unitsimpl.cxx
+++ b/sc/source/core/units/unitsimpl.cxx
@@ -690,4 +690,99 @@ bool UnitsImpl::convertCellToHeaderUnit(const ScAddress& rCellAddress,
     return false;
 }
 
+bool UnitsImpl::convertCellUnits(const ScRange& rRange,
+                                 ScDocument* pDoc,
+                                 const OUString& rsOutputUnit) {
+    UtUnit aOutputUnit;
+    if (!UtUnit::createUnit(rsOutputUnit, aOutputUnit, mpUnitSystem)) {
+        return false;
+    }
+
+    ScRange aRange(rRange);
+    aRange.PutInOrder();
+
+    SCCOL nStartCol, nEndCol;
+    SCROW nStartRow, nEndRow;
+    SCTAB nStartTab, nEndTab;
+    aRange.GetVars(nStartCol, nStartRow, nStartTab,
+                   nEndCol, nEndRow, nEndTab);
+
+    // Can only handle ranges in a single sheet for now
+    assert(nStartTab == nEndTab);
+
+    // Each column is independent hence we are able to handle each separately.
+    for (SCCOL nCol = nStartCol; nCol <= nEndCol; nCol++) {
+        ScAddress aCurrentHeaderAddress(ScAddress::INITIALIZE_INVALID);
+        UtUnit aCurrentHeaderUnit;
+        OUString sHeaderUnitString;
+
+        for (SCROW nRow = nEndRow; nRow >= nStartRow; nRow--) {
+            ScAddress aCurrent(nCol, nRow, nStartTab);
+
+            if (aCurrent == aCurrentHeaderAddress) {
+                // TODO: rewrite this to use HeaderUnitDescriptor once implemented.
+                // We can't do a dumb replace since that might overwrite other characters
+                // (many units are just single characters).
+                OUString sHeader = pDoc->GetString(aCurrent);
+                sHeader = sHeader.replaceAll(sHeaderUnitString, rsOutputUnit);
+                pDoc->SetString(aCurrent, sHeader);
+
+                aCurrentHeaderAddress.SetInvalid();
+            } else if (pDoc->GetCellType(aCurrent) != CELLTYPE_STRING) {
+                if (!aCurrentHeaderUnit.isValid()) {
+                    aCurrentHeaderUnit = findHeaderUnitForCell(aCurrent, pDoc, sHeaderUnitString, aCurrentHeaderAddress);
+
+                    // If there is no header we get an invalid unit returned from findHeaderUnitForCell,
+                    // and therfore assume the dimensionless unit 1.
+                    if (!aCurrentHeaderUnit.isValid()) {
+                        UtUnit::createUnit("", aCurrentHeaderUnit, mpUnitSystem);
+                    }
+                }
+
+                OUString sLocalUnit(extractUnitStringForCell(aCurrent, pDoc));
+                UtUnit aLocalUnit;
+                if (sLocalUnit.isEmpty()) {
+                    aLocalUnit = aCurrentHeaderUnit;
+                } else { // override header unit with annotation unit
+                    if (!UtUnit::createUnit(sLocalUnit, aLocalUnit, mpUnitSystem)) {
+                        // but assume dimensionless if invalid
+                        UtUnit::createUnit("", aLocalUnit, mpUnitSystem);
+                    }
+                }
+
+                bool bLocalAnnotationRequired = (!aRange.In(aCurrentHeaderAddress)) &&
+                                                (aOutputUnit != aCurrentHeaderUnit);
+                double nValue = pDoc->GetValue(aCurrent);
+
+                if (!aLocalUnit.areConvertibleTo(aOutputUnit)) {
+                    // TODO: in future we should undo all our changes here.
+                    return false;
+                }
+
+                double nNewValue = aLocalUnit.convertValueTo(nValue, aOutputUnit);
+                pDoc->SetValue(aCurrent, nNewValue);
+
+                if (bLocalAnnotationRequired) {
+                    // All a local dirty hack too - needs to be refactored and improved.
+                    // And ideally we should reuse the existing format.
+                    OUString sNewFormat = "General\"" + rsOutputUnit + "\"";
+                    sal_uInt32 nFormatKey;
+                    short nType = css::util::NumberFormat::DEFINED;
+                    sal_Int32 nErrorPosition; // Unused, because we should be creating working number formats.
+
+                    SvNumberFormatter* pFormatter = pDoc->GetFormatTable();
+                    pFormatter->PutEntry(sNewFormat, nErrorPosition, nType, nFormatKey);
+                    pDoc->SetNumberFormat(aCurrent, nFormatKey);
+                } else {
+                    // The number formats will by definition be wrong once we've converted, so just reset completely.
+                    pDoc->SetNumberFormat(aCurrent, 0);
+                }
+            }
+
+        }
+    }
+
+    return true;
+}
+
 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/source/core/units/unitsimpl.hxx b/sc/source/core/units/unitsimpl.hxx
index 0eea1292723f..ff927f2a1393 100644
--- a/sc/source/core/units/unitsimpl.hxx
+++ b/sc/source/core/units/unitsimpl.hxx
@@ -92,6 +92,10 @@ public:
                                          const OUString& rsNewUnit,
                                          const OUString& rsOldUnit) SAL_OVERRIDE;
 
+    virtual bool convertCellUnits(const ScRange& rRange,
+                                  ScDocument* pDoc,
+                                  const OUString& rsOutputUnit) SAL_OVERRIDE;
+
 private:
     UnitsResult getOutputUnitsForOpCode(std::stack< RAUSItem >& rStack, const formula::FormulaToken* pToken, ScDocument* pDoc);