Merging upstream version 4:24.2.2.

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

15 files changed, 514 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 0000000000..f6c9245d4c
--- /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/data/numberformat.html b/sc/qa/filter/html/data/numberformat.html
new file mode 100644
index 0000000000..3f7b3f56d6
--- /dev/null
+++ b/sc/qa/filter/html/data/numberformat.html
@@ -0,0 +1,8 @@
+<table>
+  <tr>
+    <td data-sheets-value="{&quot;1&quot;:3,&quot;3&quot;:1000}" data-sheets-numberformat="{&quot;1&quot;:2,&quot;2&quot;:&quot;#,##0.00&quot;,&quot;3&quot;:1}">1,000.00</td>
+  </tr>
+  <tr>
+    <td data-sheets-value="{&quot;1&quot;:3,&quot;3&quot;:2000}" data-sheets-numberformat="{&quot;1&quot;:2,&quot;2&quot;:&quot;#,##0.00&quot;,&quot;3&quot;:1}">2,000.00</td>
+  </tr>
+</table>
diff --git a/sc/qa/filter/html/data/single-cell.html b/sc/qa/filter/html/data/single-cell.html
new file mode 100644
index 0000000000..0b5613f7e2
--- /dev/null
+++ b/sc/qa/filter/html/data/single-cell.html
@@ -0,0 +1 @@
+<span style="font-size:10pt;font-family:Arial;font-style:normal;text-align:right;" data-sheets-root="1" data-sheets-value="{&quot;1&quot;:3,&quot;3&quot;:3}" data-sheets-formula="=SUM(R[0]C[-2]:R[0]C[-1])">3</span>
diff --git a/sc/qa/filter/html/html.cxx b/sc/qa/filter/html/html.cxx
index 6ab2cc7fb0..391806be03 100644
--- a/sc/qa/filter/html/html.cxx
+++ b/sc/qa/filter/html/html.cxx
@@ -116,6 +116,192 @@ CPPUNIT_TEST_FIXTURE(Test, testPasteTdAsBools)
     CPPUNIT_ASSERT_EQUAL(OUString("BOOLEAN"), pNumberFormat->GetFormatstring());
     CPPUNIT_ASSERT_EQUAL(static_cast<double>(0), pDoc->GetValue(/*col=*/0, /*row=*/1, /*tab=*/0));
 }
+
+CPPUNIT_TEST_FIXTURE(Test, testPasteTdAsFormattedNumber)
+{
+    // Given an empty document:
+    createScDoc();
+
+    // When pasting HTML with cells containing formatted numbers:
+    ScDocument* pDoc = getScDoc();
+    ScAddress aCellPos(/*nColP=*/0, /*nRowP=*/0, /*nTabP=*/0);
+    ScImportExport aImporter(*pDoc, aCellPos);
+    SvFileStream aFile(createFileURL(u"numberformat.html"), StreamMode::READ);
+    SvMemoryStream aMemory;
+    aMemory.WriteStream(aFile);
+    aMemory.Seek(0);
+    CPPUNIT_ASSERT(aImporter.ImportStream(aMemory, OUString(), SotClipboardFormatId::HTML));
+
+    // Then make sure A1's type is a formatted number, value is 1000:
+    sal_uInt32 nNumberFormat = pDoc->GetNumberFormat(/*col=*/0, /*row=*/0, /*tab=*/0);
+    const SvNumberformat* pNumberFormat = pDoc->GetFormatTable()->GetEntry(nNumberFormat);
+    // Without the accompanying fix in place, this test would have failed with:
+    // - Expected: #,##0.00
+    // - Actual  : General
+    // i.e. the number was wasted without a matching number format.
+    CPPUNIT_ASSERT_EQUAL(OUString("#,##0.00"), pNumberFormat->GetFormatstring());
+    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_TEST_FIXTURE(Test, testPasteSingleCell)
+{
+    // Given a document with '1' in A1 and '2' in B1:
+    createScDoc();
+    ScDocument* pDoc = getScDoc();
+    pDoc->SetValue(ScAddress(0, 0, 0), 1.0);
+    pDoc->SetValue(ScAddress(1, 0, 0), 2.0);
+
+    // When pasting SUM(A1:B1) into C1:
+    ScAddress aCellPos(/*nColP=*/2, /*nRowP=*/0, /*nTabP=*/0);
+    ScImportExport aImporter(*pDoc, aCellPos);
+    SvFileStream aFile(createFileURL(u"single-cell.html"), StreamMode::READ);
+    CPPUNIT_ASSERT(aImporter.ImportStream(aFile, 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. data-sheets-* on <td> worked, but not on <span>.
+    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_TEST_FIXTURE(Test, testCopyText)
+{
+    // Given a document with 01 in A1:
+    createScDoc();
+    ScDocument* pDoc = getScDoc();
+    ScAddress aCellPos(/*nColP=*/0, /*nRowP=*/0, /*nTabP=*/0);
+    pDoc->SetString(aCellPos, "'01");
+
+    // When copying that text from A1:
+    ScImportExport aExporter(*pDoc, aCellPos);
+    SvMemoryStream aStream;
+    CPPUNIT_ASSERT(aExporter.ExportStream(aStream, OUString(), SotClipboardFormatId::HTML));
+
+    // Then make sure A1 is text:
+    // Without the accompanying fix in place, this test would have failed with:
+    // - XPath '//td' no attribute 'data-sheets-value' exist
+    // i.e. metadata was missing to avoid converting 01 to 1 (number).
+    aStream.Seek(0);
+    htmlDocUniquePtr pHtmlDoc = parseHtmlStream(&aStream);
+    assertXPath(pHtmlDoc, "//td"_ostr, "data-sheets-value"_ostr, "{ \"1\": 2, \"2\": \"01\"}");
+}
+
+CPPUNIT_TEST_FIXTURE(Test, testCopyBoolean)
+{
+    // Given a document with boolean values in A1-A2:
+    createScDoc();
+    ScDocument* pDoc = getScDoc();
+    ScAddress aCellPos1(/*nColP=*/0, /*nRowP=*/0, /*nTabP=*/0);
+    pDoc->SetString(aCellPos1, "TRUE");
+    ScAddress aCellPos2(/*nColP=*/0, /*nRowP=*/1, /*nTabP=*/0);
+    pDoc->SetString(aCellPos2, "FALSE");
+
+    // When copying those values:
+    ScImportExport aExporter(*pDoc, ScRange(aCellPos1, aCellPos2));
+    SvMemoryStream aStream;
+    CPPUNIT_ASSERT(aExporter.ExportStream(aStream, OUString(), SotClipboardFormatId::HTML));
+
+    // Then make sure the values are booleans:
+    aStream.Seek(0);
+    htmlDocUniquePtr pHtmlDoc = parseHtmlStream(&aStream);
+    // Without the accompanying fix in place, this test would have failed with:
+    // - XPath '//td' no attribute 'data-sheets-value' exist
+    // i.e. metadata was missing to avoid converting TRUE to text.
+    assertXPath(pHtmlDoc, "(//td)[1]"_ostr, "data-sheets-value"_ostr, "{ \"1\": 4, \"4\": 1}");
+    assertXPath(pHtmlDoc, "(//td)[2]"_ostr, "data-sheets-value"_ostr, "{ \"1\": 4, \"4\": 0}");
+}
+
+CPPUNIT_TEST_FIXTURE(Test, testCopyFormattedNumber)
+{
+    // Given a document with formatted numbers in A1-A2:
+    createScDoc();
+    ScDocument* pDoc = getScDoc();
+    sal_Int32 nCheckPos;
+    SvNumFormatType nType;
+    sal_uInt32 nFormat;
+    OUString aNumberFormat("#,##0.00");
+    SvNumberFormatter* pFormatter = pDoc->GetFormatTable();
+    pFormatter->PutEntry(aNumberFormat, nCheckPos, nType, nFormat);
+    ScAddress aCellPos1(/*nColP=*/0, /*nRowP=*/0, /*nTabP=*/0);
+    pDoc->SetNumberFormat(aCellPos1, nFormat);
+    pDoc->SetString(aCellPos1, "1000");
+    ScAddress aCellPos2(/*nColP=*/0, /*nRowP=*/1, /*nTabP=*/0);
+    pDoc->SetNumberFormat(aCellPos2, nFormat);
+    pDoc->SetString(aCellPos2, "2000");
+
+    // When copying those values:
+    ScImportExport aExporter(*pDoc, ScRange(aCellPos1, aCellPos2));
+    SvMemoryStream aStream;
+    CPPUNIT_ASSERT(aExporter.ExportStream(aStream, OUString(), SotClipboardFormatId::HTML));
+
+    // Then make sure the values are numbers:
+    aStream.Seek(0);
+    htmlDocUniquePtr pHtmlDoc = parseHtmlStream(&aStream);
+    // Without the accompanying fix in place, this test would have failed with:
+    // - XPath '(//td)[1]' no attribute 'data-sheets-value' exist
+    // i.e. only a formatted number string was written, without a float value.
+    assertXPath(pHtmlDoc, "(//td)[1]"_ostr, "data-sheets-value"_ostr, "{ \"1\": 3, \"3\": 1000}");
+    assertXPath(pHtmlDoc, "(//td)[1]"_ostr, "data-sheets-numberformat"_ostr,
+                "{ \"1\": 2, \"2\": \"#,##0.00\", \"3\": 1}");
+    assertXPath(pHtmlDoc, "(//td)[2]"_ostr, "data-sheets-value"_ostr, "{ \"1\": 3, \"3\": 2000}");
+    assertXPath(pHtmlDoc, "(//td)[2]"_ostr, "data-sheets-numberformat"_ostr,
+                "{ \"1\": 2, \"2\": \"#,##0.00\", \"3\": 1}");
+}
+
+CPPUNIT_TEST_FIXTURE(Test, testCopyFormula)
+{
+    // Given a document with a formula in A3:
+    createScDoc();
+    ScDocument* pDoc = getScDoc();
+    ScAddress aCellPos1(/*nColP=*/0, /*nRowP=*/0, /*nTabP=*/0);
+    pDoc->SetString(aCellPos1, "1000");
+    ScAddress aCellPos2(/*nColP=*/0, /*nRowP=*/1, /*nTabP=*/0);
+    pDoc->SetString(aCellPos2, "2000");
+    ScAddress aCellPos3(/*nColP=*/0, /*nRowP=*/2, /*nTabP=*/0);
+    pDoc->SetFormula(aCellPos3, "=SUM(A1:A2)", pDoc->GetGrammar());
+
+    // When copying those cells:
+    ScImportExport aExporter(*pDoc, ScRange(aCellPos1, aCellPos3));
+    SvMemoryStream aStream;
+    CPPUNIT_ASSERT(aExporter.ExportStream(aStream, OUString(), SotClipboardFormatId::HTML));
+
+    // Then make sure the formula is exported in A3:
+    aStream.Seek(0);
+    htmlDocUniquePtr pHtmlDoc = parseHtmlStream(&aStream);
+    // Without the accompanying fix in place, this test would have failed with:
+    // - XPath '(//td)[3]' no attribute 'data-sheets-formula' exist
+    // i.e. only the formula result was exported, not the formula.
+    assertXPath(pHtmlDoc, "(//td)[3]"_ostr, "data-sheets-formula"_ostr, "=SUM(R[-2]C:R[-1]C)");
+}
 }
 
 CPPUNIT_PLUGIN_IMPLEMENT();
diff --git a/sc/qa/uitest/autofilter2/tdf159420.py b/sc/qa/uitest/autofilter2/tdf159420.py
new file mode 100644
index 0000000000..87ee159d22
--- /dev/null
+++ b/sc/qa/uitest/autofilter2/tdf159420.py
@@ -0,0 +1,126 @@
+# -*- tab-width: 4; indent-tabs-mode: nil; py-indent-offset: 4 -*-
+#
+# This file is part of the LibreOffice project.
+#
+# This Source Code Form is subject to the terms of the Mozilla Public
+# License, v. 2.0. If a copy of the MPL was not distributed with this
+# file, You can obtain one at http://mozilla.org/MPL/2.0/.
+#
+from uitest.framework import UITestCase
+from uitest.uihelper.calc import enter_text_to_cell
+from uitest.uihelper.common import get_state_as_dict
+from libreoffice.uno.propertyvalue import mkPropertyValues
+from libreoffice.calc.document import is_row_hidden
+
+class tdf159420(UITestCase):
+
+    def testTdf159420(self):
+        with self.ui_test.create_doc_in_start_center("calc") as calcDoc:
+            xCalcDoc = self.xUITest.getTopFocusWindow()
+            xGridWin = xCalcDoc.getChild("grid_window")
+
+            # Fill the sheet with test data
+            enter_text_to_cell(xGridWin, "A1", "a")
+            enter_text_to_cell(xGridWin, "A2", "2")
+            enter_text_to_cell(xGridWin, "A3", "2")
+            enter_text_to_cell(xGridWin, "A4", "2")
+            enter_text_to_cell(xGridWin, "A5", "4")
+
+            enter_text_to_cell(xGridWin, "B1", "b")
+            enter_text_to_cell(xGridWin, "B2", "")
+            enter_text_to_cell(xGridWin, "B3", "")
+            enter_text_to_cell(xGridWin, "B4", "8")
+            enter_text_to_cell(xGridWin, "B5", "8")
+
+            enter_text_to_cell(xGridWin, "C1", "c")
+
+            # Select the data range and set autofilter
+            xGridWin.executeAction("SELECT", mkPropertyValues({"RANGE": "A1:C5"}))
+            self.xUITest.executeCommand(".uno:DataFilterAutoFilter")
+
+            # Click the autofilter dropdown in column A
+            xGridWin.executeAction("LAUNCH", mkPropertyValues({"AUTOFILTER": "", "COL": "0", "ROW": "0"}))
+            xFloatWindow = self.xUITest.getFloatWindow()
+            xCheckListMenu = xFloatWindow.getChild("FilterDropDown")
+            xTreeList = xCheckListMenu.getChild("check_list_box")
+
+            self.assertEqual(2, len(xTreeList.getChildren()))
+
+            xEntry1 = xTreeList.getChild(0)
+            self.assertEqual("2", get_state_as_dict(xEntry1)['Text'])
+            self.assertEqual("true", get_state_as_dict(xEntry1)['IsChecked'])
+            self.assertEqual("false", get_state_as_dict(xEntry1)['IsSemiTransparent'])
+
+            xEntry2 = xTreeList.getChild(1)
+            self.assertEqual("4", get_state_as_dict(xEntry2)['Text'])
+            self.assertEqual("true", get_state_as_dict(xEntry2)['IsChecked'])
+            self.assertEqual("false", get_state_as_dict(xEntry2)['IsSemiTransparent'])
+
+            #  Uncheck the second entry
+            xEntry2.executeAction("CLICK", tuple())
+
+            xOkButton = xFloatWindow.getChild("ok")
+            xOkButton.executeAction("CLICK", tuple())
+
+            # Check that only row#2 is visible
+            self.assertFalse(is_row_hidden(calcDoc, 1))
+            self.assertFalse(is_row_hidden(calcDoc, 2))
+            self.assertFalse(is_row_hidden(calcDoc, 3))
+            self.assertTrue(is_row_hidden(calcDoc, 4))
+
+            # Click the autofilter dropdown in column B
+            xGridWin.executeAction("LAUNCH", mkPropertyValues({"AUTOFILTER": "", "COL": "1", "ROW": "0"}))
+            xFloatWindow = self.xUITest.getFloatWindow()
+            xCheckListMenu = xFloatWindow.getChild("FilterDropDown")
+            xTreeList = xCheckListMenu.getChild("check_list_box")
+
+            self.assertEqual(2, len(xTreeList.getChildren()))
+
+            xEntry1 = xTreeList.getChild(0)
+            self.assertEqual("(empty)", get_state_as_dict(xEntry1)['Text'])
+            self.assertEqual("true", get_state_as_dict(xEntry1)['IsChecked'])
+            self.assertEqual("false", get_state_as_dict(xEntry1)['IsSemiTransparent'])
+
+            xEntry2 = xTreeList.getChild(1)
+            self.assertEqual("8", get_state_as_dict(xEntry2)['Text'])
+            self.assertEqual("true", get_state_as_dict(xEntry2)['IsChecked'])
+            self.assertEqual("false", get_state_as_dict(xEntry2)['IsSemiTransparent'])
+
+            #  Uncheck the first entry
+            xEntry1.executeAction("CLICK", tuple())
+
+            # Close the popup window
+            xOkButton = xFloatWindow.getChild("ok")
+            xOkButton.executeAction("CLICK", tuple())
+
+            self.assertTrue(is_row_hidden(calcDoc, 1))
+            self.assertTrue(is_row_hidden(calcDoc, 2))
+            self.assertFalse(is_row_hidden(calcDoc, 3))
+            self.assertTrue(is_row_hidden(calcDoc, 4))
+
+            # Click the autofilter dropdown in column C
+            xGridWin.executeAction("LAUNCH", mkPropertyValues({"AUTOFILTER": "", "COL": "2", "ROW": "0"}))
+            xFloatWindow = self.xUITest.getFloatWindow()
+            xCheckListMenu = xFloatWindow.getChild("FilterDropDown")
+            xTreeList = xCheckListMenu.getChild("check_list_box")
+
+            self.assertEqual(1, len(xTreeList.getChildren()))
+
+            xEntry1 = xTreeList.getChild(0)
+            self.assertEqual("(empty)", get_state_as_dict(xEntry1)['Text'])
+
+            # Without the fix in place, this test would have failed with
+            # AssertionError: 'true' != 'false'
+            self.assertEqual("true", get_state_as_dict(xEntry1)['IsChecked'])
+            self.assertEqual("false", get_state_as_dict(xEntry1)['IsSemiTransparent'])
+
+            # Close the popup window
+            xOkButton = xFloatWindow.getChild("ok")
+            xOkButton.executeAction("CLICK", tuple())
+
+            self.assertTrue(is_row_hidden(calcDoc, 1))
+            self.assertTrue(is_row_hidden(calcDoc, 2))
+            self.assertFalse(is_row_hidden(calcDoc, 3))
+            self.assertTrue(is_row_hidden(calcDoc, 4))
+
+# vim: set shiftwidth=4 softtabstop=4 expandtab:
diff --git a/sc/qa/uitest/data/tdf129701.ods b/sc/qa/uitest/data/tdf129701.ods
new file mode 100644
index 0000000000..94407fcd56
--- /dev/null
+++ b/sc/qa/uitest/data/tdf129701.ods
diff --git a/sc/qa/uitest/pasteSpecial/tdf129701-PasteUnformated.py b/sc/qa/uitest/pasteSpecial/tdf129701-PasteUnformated.py
new file mode 100644
index 0000000000..596cd62f8b
--- /dev/null
+++ b/sc/qa/uitest/pasteSpecial/tdf129701-PasteUnformated.py
@@ -0,0 +1,70 @@
+# -*- tab-width: 4; indent-tabs-mode: nil; py-indent-offset: 4 -*-
+#
+# This file is part of the LibreOffice project.
+#
+# This Source Code Form is subject to the terms of the Mozilla Public
+# License, v. 2.0. If a copy of the MPL was not distributed with this
+# file, You can obtain one at http://mozilla.org/MPL/2.0/.
+#
+from uitest.framework import UITestCase
+from libreoffice.uno.propertyvalue import mkPropertyValues
+from uitest.uihelper.common import get_url_for_data_file
+from uitest.uihelper.common import get_state_as_dict
+from libreoffice.calc.document import get_cell_by_position
+
+class tdf129701(UITestCase):
+
+    def test_tdf129701(self):
+
+        with self.ui_test.load_file(get_url_for_data_file("tdf129701.ods")):
+            xCalcDoc = self.xUITest.getTopFocusWindow()
+            gridwin = xCalcDoc.getChild("grid_window")
+            gridwin.executeAction("SELECT", mkPropertyValues({"RANGE": "D21:F25"}))
+            self.xUITest.executeCommand(".uno:Copy")
+
+            gridwin.executeAction("SELECT", mkPropertyValues({"CELL": "J4"}))
+            with self.ui_test.execute_dialog_through_command(".uno:PasteUnformatted", close_button="ok") as xDialog:
+
+                xSkipEmtyCells   = xDialog.getChild("skipemptycells")
+                xSeparatedBy     = xDialog.getChild("toseparatedby")
+                xTab             = xDialog.getChild("tab")
+                xMergeDelimiters = xDialog.getChild("mergedelimiters")
+
+                xSeparatedBy.executeAction("CLICK", tuple())
+                if get_state_as_dict(xTab)['Selected'] == 'false':
+                    xTab.executeAction("CLICK", tuple())
+                if get_state_as_dict(xMergeDelimiters)['Selected'] == 'true':
+                    xMergeDelimiters.executeAction("CLICK", tuple())
+                if get_state_as_dict(xSkipEmtyCells)['Selected'] == 'true':
+                    xSkipEmtyCells.executeAction("CLICK", tuple())
+                # Check wether Skip empty cells is unselected
+                self.assertEqual('false', get_state_as_dict(xSkipEmtyCells)['Selected'])
+
+            document = self.ui_test.get_component()
+            # Without the fix in place, this test would have failed with
+            # non empty cells in column 11
+
+            self.assertEqual( "x1", get_cell_by_position(document, 0, 9, 3).getString())
+            self.assertEqual( ""  , get_cell_by_position(document, 0,10, 3).getString())
+            self.assertEqual( ""  , get_cell_by_position(document, 0,11, 3).getString())
+            self.assertEqual("A16", get_cell_by_position(document, 0,12, 3).getString())
+            self.assertEqual( ""  , get_cell_by_position(document, 0, 9, 4).getString())
+            self.assertEqual( "x2", get_cell_by_position(document, 0,10, 4).getString())
+            self.assertEqual( ""  , get_cell_by_position(document, 0,11, 4).getString())
+            self.assertEqual("A17", get_cell_by_position(document, 0,12, 4).getString())
+            self.assertEqual( ""  , get_cell_by_position(document, 0, 9, 5).getString())
+            self.assertEqual( ""  , get_cell_by_position(document, 0,10, 5).getString())
+            self.assertEqual( "x3", get_cell_by_position(document, 0,11, 5).getString())
+            self.assertEqual("A18", get_cell_by_position(document, 0,12, 5).getString())
+            self.assertEqual( ""  , get_cell_by_position(document, 0, 9, 6).getString())
+            self.assertEqual( "x4", get_cell_by_position(document, 0,10, 6).getString())
+            self.assertEqual( ""  , get_cell_by_position(document, 0,11, 6).getString())
+            self.assertEqual("A19", get_cell_by_position(document, 0,12, 6).getString())
+            self.assertEqual( "x5", get_cell_by_position(document, 0, 9, 7).getString())
+            self.assertEqual( "x6", get_cell_by_position(document, 0,10, 7).getString())
+            self.assertEqual( "x7", get_cell_by_position(document, 0,11, 7).getString())
+            self.assertEqual("A20", get_cell_by_position(document, 0,12, 7).getString())
+
+            self.ui_test.close_doc()
+
+# vim: set shiftwidth=4 softtabstop=4 expandtab:
diff --git a/sc/qa/unit/data/ods/tdf158735.ods b/sc/qa/unit/data/ods/tdf158735.ods
new file mode 100644
index 0000000000..6003f29bf3
--- /dev/null
+++ b/sc/qa/unit/data/ods/tdf158735.ods
diff --git a/sc/qa/unit/data/ods/tdf160003_page_anchored_object.ods b/sc/qa/unit/data/ods/tdf160003_page_anchored_object.ods
new file mode 100644
index 0000000000..565eb1bf6a
--- /dev/null
+++ b/sc/qa/unit/data/ods/tdf160003_page_anchored_object.ods
diff --git a/sc/qa/unit/data/xlsx/PivotTable_CachedDefinitionAndDataInSync.xlsx b/sc/qa/unit/data/xlsx/PivotTable_CachedDefinitionAndDataInSync.xlsx
new file mode 100644
index 0000000000..f425f978cb
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/PivotTable_CachedDefinitionAndDataInSync.xlsx
diff --git a/sc/qa/unit/data/xlsx/PivotTable_CachedDefinitionAndDataNotInSync_SheetColumnsRemoved_WithCacheData.xlsx b/sc/qa/unit/data/xlsx/PivotTable_CachedDefinitionAndDataNotInSync_SheetColumnsRemoved_WithCacheData.xlsx
new file mode 100644
index 0000000000..0cb21cd325
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/PivotTable_CachedDefinitionAndDataNotInSync_SheetColumnsRemoved_WithCacheData.xlsx
diff --git a/sc/qa/unit/data/xlsx/PivotTable_CachedDefinitionAndDataNotInSync_SheetColumnsRemoved_WithoutCacheData.xlsx b/sc/qa/unit/data/xlsx/PivotTable_CachedDefinitionAndDataNotInSync_SheetColumnsRemoved_WithoutCacheData.xlsx
new file mode 100644
index 0000000000..91297320b9
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/PivotTable_CachedDefinitionAndDataNotInSync_SheetColumnsRemoved_WithoutCacheData.xlsx
diff --git a/sc/qa/unit/pivottable_filters_test.cxx b/sc/qa/unit/pivottable_filters_test.cxx
index 8d6b1ad5d3..31fb49351c 100644
--- a/sc/qa/unit/pivottable_filters_test.cxx
+++ b/sc/qa/unit/pivottable_filters_test.cxx
@@ -2647,6 +2647,69 @@ CPPUNIT_TEST_FIXTURE(ScPivotTableFiltersTest, testPivotTableCompactLayoutXLSX)
     testThis(*getScDoc());
 }
 
+CPPUNIT_TEST_FIXTURE(ScPivotTableFiltersTest,
+                     testPivotTableXLSX_OutOfSyncPivotTableCachedDefinitionImport)
+{
+    // This tests that a out-of-sync sheet data and pivot table cached definitions
+    // still get imported correctly as expected.
+
+    // It is perfectly valid that the sheet data and pivot table are out-of-sync,
+    // but even if the sheet data is heavily modified, the pivot table should still
+    // be imported.
+
+    // The test document has columns named A-K where only A and K are used in the
+    // pivot table. The columns B-J were removed in the sheet data, but the pivot table
+    // was not updated, so the cached data still has those and the pivot table
+    // description still relies on those columns to be present.
+
+    auto testThis = [](ScDocument& rDocument) {
+        ScDPCollection* pDPs = rDocument.GetDPCollection();
+        CPPUNIT_ASSERT_MESSAGE("Failed to get a live ScDPCollection instance.", pDPs);
+        CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be exactly one pivot table instance.", size_t(1),
+                                     pDPs->GetCount());
+
+        const ScDPObject* pDPObj = &(*pDPs)[0];
+        CPPUNIT_ASSERT(pDPObj);
+        ScDPSaveData* pSaveData = pDPObj->GetSaveData();
+        CPPUNIT_ASSERT(pSaveData);
+
+        // Do we have a dim named "A"
+        ScDPSaveDimension* pSaveDimA = pSaveData->GetExistingDimensionByName(u"A");
+        CPPUNIT_ASSERT(pSaveDimA);
+
+        // Do we have a dim named "K"
+        ScDPSaveDimension* pSaveDimK = pSaveData->GetExistingDimensionByName(u"K");
+        CPPUNIT_ASSERT(pSaveDimK);
+
+        // Check the headers
+        CPPUNIT_ASSERT_EQUAL(OUString("K"), rDocument.GetString(ScAddress(0, 2, 0))); // A3
+        CPPUNIT_ASSERT_EQUAL(OUString("Sum of A"), rDocument.GetString(ScAddress(1, 2, 0))); //B3
+
+        // Check the values
+        CPPUNIT_ASSERT_EQUAL(OUString("1"), rDocument.GetString(ScAddress(0, 3, 0))); //A4
+        CPPUNIT_ASSERT_EQUAL(OUString("2"), rDocument.GetString(ScAddress(0, 4, 0))); //A5
+        CPPUNIT_ASSERT_EQUAL(OUString("5"), rDocument.GetString(ScAddress(1, 3, 0))); //B4
+        CPPUNIT_ASSERT_EQUAL(OUString("5"), rDocument.GetString(ScAddress(1, 4, 0))); //B5
+    };
+
+    // test document with sheet data and pivot table in sync
+    createScDoc("xlsx/PivotTable_CachedDefinitionAndDataInSync.xlsx");
+    testThis(*getScDoc());
+
+    // test document with sheet data and pivot table in out-of-sync - B-J columns removed,
+    // but the pivot table cache still hass all the data
+    createScDoc(
+        "xlsx/PivotTable_CachedDefinitionAndDataNotInSync_SheetColumnsRemoved_WithCacheData.xlsx");
+    testThis(*getScDoc());
+
+    // test document with sheet data and pivot table in out-of-sync - B-J columns removed,
+    // but the pivot table cache is not saved, only the cached definitions are available
+    createScDoc("xlsx/"
+                "PivotTable_CachedDefinitionAndDataNotInSync_SheetColumnsRemoved_WithoutCacheData."
+                "xlsx");
+    testThis(*getScDoc());
+}
+
 CPPUNIT_PLUGIN_IMPLEMENT();
 
 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/qa/unit/scshapetest.cxx b/sc/qa/unit/scshapetest.cxx
index b5083544f1..5e48270053 100644
--- a/sc/qa/unit/scshapetest.cxx
+++ b/sc/qa/unit/scshapetest.cxx
@@ -1207,6 +1207,30 @@ CPPUNIT_TEST_FIXTURE(ScShapeTest, testTdf154821_shape_in_group)
     CPPUNIT_ASSERT_RECTANGLE_EQUAL_WITH_TOLERANCE(aRectOrig, aRectReload, 1);
 }
 
+CPPUNIT_TEST_FIXTURE(ScShapeTest, testTdf160003_copy_page_anchored)
+{
+    // Load a document, which has a chart anchored to page on sheet2. Copy&paste to other document
+    // had lost the chart object.
+    createScDoc("ods/tdf160003_page_anchored_object.ods");
+
+    // copy range with chart
+    goToCell("$Sheet2.$A$1:$L$24");
+    dispatchCommand(mxComponent, ".uno:Copy", {});
+
+    // close document and create new one
+    createScDoc();
+
+    // paste clipboard
+    goToCell("$Sheet1.$A$1");
+    dispatchCommand(mxComponent, ".uno:Paste", {});
+
+    // Make sure the chart object exists.
+    ScDocument* pDoc = getScDoc();
+    ScDrawLayer* pDrawLayer = pDoc->GetDrawLayer();
+    const SdrPage* pPage = pDrawLayer->GetPage(0);
+    CPPUNIT_ASSERT_EQUAL(size_t(1), pPage->GetObjCount());
+}
+
 CPPUNIT_PLUGIN_IMPLEMENT();
 
 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/qa/unit/ucalc_solver.cxx b/sc/qa/unit/ucalc_solver.cxx
index 47770ec0c0..7834597e9c 100644
--- a/sc/qa/unit/ucalc_solver.cxx
+++ b/sc/qa/unit/ucalc_solver.cxx
@@ -163,4 +163,33 @@ CPPUNIT_TEST_FIXTURE(SolverTest, tdf156815)
     CPPUNIT_ASSERT_EQUAL(OUString("$NewName.$B$2"), aConstraints[0].aRightStr);
 }
 
+// Tests if settings for the DEPS and SCO solvers are kept in the file
+CPPUNIT_TEST_FIXTURE(SolverTest, tdf158735)
+{
+    createScDoc("ods/tdf158735.ods");
+    ScDocument* pDoc = getScDoc();
+
+    // Test the non-default values of the DEPS model
+    ScTable* pTable = pDoc->FetchTable(0);
+    std::shared_ptr<sc::SolverSettings> pSettings = pTable->GetSolverSettings();
+    CPPUNIT_ASSERT(pSettings);
+    CPPUNIT_ASSERT_EQUAL(OUString("com.sun.star.comp.Calc.NLPSolver.DEPSSolverImpl"),
+                         pSettings->GetParameter(SP_LO_ENGINE));
+    CPPUNIT_ASSERT_EQUAL(OUString("0.45"), pSettings->GetParameter(SP_AGENT_SWITCH_RATE));
+    CPPUNIT_ASSERT_EQUAL(OUString("0.85"), pSettings->GetParameter(SP_CROSSOVER_PROB));
+    CPPUNIT_ASSERT_EQUAL(OUString("1500"), pSettings->GetParameter(SP_LEARNING_CYCLES));
+    CPPUNIT_ASSERT_EQUAL(OUString("0"), pSettings->GetParameter(SP_ENHANCED_STATUS));
+
+    // Test the non-default values of the SCO model
+    pTable = pDoc->FetchTable(1);
+    pSettings = pTable->GetSolverSettings();
+    CPPUNIT_ASSERT(pSettings);
+    CPPUNIT_ASSERT_EQUAL(OUString("com.sun.star.comp.Calc.NLPSolver.SCOSolverImpl"),
+                         pSettings->GetParameter(SP_LO_ENGINE));
+    CPPUNIT_ASSERT_EQUAL(OUString("180"), pSettings->GetParameter(SP_LIBRARY_SIZE));
+    CPPUNIT_ASSERT_EQUAL(OUString("0.00055"), pSettings->GetParameter(SP_STAGNATION_TOLERANCE));
+    CPPUNIT_ASSERT_EQUAL(OUString("1"), pSettings->GetParameter(SP_RND_STARTING_POINT));
+    CPPUNIT_ASSERT_EQUAL(OUString("80"), pSettings->GetParameter(SP_STAGNATION_LIMIT));
+}
+
 CPPUNIT_PLUGIN_IMPLEMENT();