tdf#159483 sc HTML paste: handle data-sheets-formula attribute

When a formula cell gets copied from google docs, the value of the <td>
element only contains the formula result. This means once value cells
and formula cells gets copied together, the pasted formula cell won't
updated anymore when the input values change.

Turns out there is a data-sheets-formula attribute on <td> that contains
the formula, it seems it uses the R1C1 format.

Fix the problem by extending ScHTMLLayoutParser::TableDataOn() to parse
this attribute and set a formula on the target cell (rather than a
value) if the formula is available.

This required also extending ScEEImport a bit, since the HTML paste
builds on top of the RTF one in Calc.

(cherry picked from commit 7812adb2ed11a3e08be24d3f2f94d14bfd740c55)

Change-Id: I720df96ce74a5e865b7329d06f3b719551f31b96

5 files changed, 53 insertions, 0 deletions

diff --git a/sc/qa/filter/html/data/formula.html b/sc/qa/filter/html/data/formula.html
new file mode 100644
index 000000000000..f6c9245d4c02
--- /dev/null
+++ b/sc/qa/filter/html/data/formula.html
@@ -0,0 +1,7 @@
+<table>
+  <tr>
+    <td data-sheets-value="{&quot;1&quot;:3,&quot;3&quot;:1}">1</td>
+    <td data-sheets-value="{&quot;1&quot;:3,&quot;3&quot;:2}">2</td>
+    <td data-sheets-value="{&quot;1&quot;:3,&quot;3&quot;:3}" data-sheets-formula="=SUM(R[0]C[-2]:R[0]C[-1])">3</td>
+  </tr>
+</table>
diff --git a/sc/qa/filter/html/html.cxx b/sc/qa/filter/html/html.cxx
index b66976396f99..83e35d9f8281 100644
--- a/sc/qa/filter/html/html.cxx
+++ b/sc/qa/filter/html/html.cxx
@@ -143,6 +143,31 @@ CPPUNIT_TEST_FIXTURE(Test, testPasteTdAsFormattedNumber)
     CPPUNIT_ASSERT_EQUAL(static_cast<double>(1000),
                          pDoc->GetValue(/*col=*/0, /*row=*/0, /*tab=*/0));
 }
+
+CPPUNIT_TEST_FIXTURE(Test, testPasteTdAsFormula)
+{
+    // Given an empty document:
+    createScDoc();
+
+    // When pasting HTML with cells containing a formula:
+    ScDocument* pDoc = getScDoc();
+    ScAddress aCellPos(/*nColP=*/0, /*nRowP=*/0, /*nTabP=*/0);
+    ScImportExport aImporter(*pDoc, aCellPos);
+    SvFileStream aFile(createFileURL(u"formula.html"), StreamMode::READ);
+    SvMemoryStream aMemory;
+    aMemory.WriteStream(aFile);
+    aMemory.Seek(0);
+    CPPUNIT_ASSERT(aImporter.ImportStream(aMemory, OUString(), SotClipboardFormatId::HTML));
+
+    // Then make sure C1 is a sum and it evaluates to 3:
+    // Without the accompanying fix in place, this test would have failed with:
+    // - Expected: =SUM(A1:B1)
+    // - Actual  :
+    // i.e. only the formula result was imported, not the formula.
+    CPPUNIT_ASSERT_EQUAL(OUString("=SUM(A1:B1)"),
+                         pDoc->GetFormula(/*col=*/2, /*row=*/0, /*tab=*/0));
+    CPPUNIT_ASSERT_EQUAL(static_cast<double>(3), pDoc->GetValue(/*col=*/2, /*row=*/0, /*tab=*/0));
+}
 }
 
 CPPUNIT_PLUGIN_IMPLEMENT();
diff --git a/sc/source/filter/html/htmlpars.cxx b/sc/source/filter/html/htmlpars.cxx
index 78aaafdad70b..5db879db75dd 100644
--- a/sc/source/filter/html/htmlpars.cxx
+++ b/sc/source/filter/html/htmlpars.cxx
@@ -155,6 +155,14 @@ void ParseDataSheetsNumberformat(const OUString& rDataSheetsValue, std::optional
         }
     }
 }
+
+/// data-sheets-formula from google sheets, grammar is R1C1 reference style.
+void ParseDataSheetsFormula(const OUString& rDataSheetsFormula, std::optional<OUString>& rVal,
+                            std::optional<formula::FormulaGrammar::Grammar>& rGrammar)
+{
+    rVal = rDataSheetsFormula;
+    rGrammar = formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1;
+}
 }
 
 ScHTMLStyles::ScHTMLStyles() : maEmpty() {}
@@ -1074,6 +1082,12 @@ void ScHTMLLayoutParser::TableDataOn( HtmlImportInfo* pInfo )
                 ParseDataSheetsNumberformat(rOption.GetString(), mxActEntry->pNumStr);
             }
             break;
+            case HtmlOptionId::DSFORMULA:
+            {
+                ParseDataSheetsFormula(rOption.GetString(), mxActEntry->moFormulaStr,
+                                       mxActEntry->moFormulaGrammar);
+            }
+            break;
             default: break;
         }
     }
diff --git a/sc/source/filter/inc/eeparser.hxx b/sc/source/filter/inc/eeparser.hxx
index ebc383e32dac..e890a80dd111 100644
--- a/sc/source/filter/inc/eeparser.hxx
+++ b/sc/source/filter/inc/eeparser.hxx
@@ -56,6 +56,8 @@ struct ScEEParseEntry
     ESelection          aSel;           // Selection in EditEngine
     std::optional<OUString>
                         pValStr;        // HTML possibly SDVAL string
+    std::optional<OUString> moFormulaStr;
+    std::optional<formula::FormulaGrammar::Grammar> moFormulaGrammar;
     std::optional<OUString>
                         pNumStr;        // HTML possibly SDNUM string
     std::optional<OUString>
diff --git a/sc/source/filter/rtf/eeimpars.cxx b/sc/source/filter/rtf/eeimpars.cxx
index 70562308120f..57d81598bfd9 100644
--- a/sc/source/filter/rtf/eeimpars.cxx
+++ b/sc/source/filter/rtf/eeimpars.cxx
@@ -339,6 +339,11 @@ void ScEEImport::WriteToDocument( bool bSizeColsRows, double nOutputFactor, SvNu
 
                 if (!aValStr.isEmpty())
                     mpDoc->SetValue( nCol, nRow, nTab, fVal );
+                else if (pE->moFormulaStr && pE->moFormulaGrammar)
+                {
+                    mpDoc->SetFormula(ScAddress(nCol, nRow, nTab), *pE->moFormulaStr,
+                                      *pE->moFormulaGrammar);
+                }
                 else if ( !pE->aSel.HasRange() )
                 {
                     // maybe ALT text of IMG or similar