include/svtools/htmlkywd.hxx | 1
include/svtools/htmltokn.h | 1
sc/qa/filter/html/data/numberformat.html | 8 +++++
sc/qa/filter/html/html.cxx | 27 +++++++++++++++++
sc/source/filter/html/htmlpars.cxx | 47 ++++++++++++++++++++++++++++++-
svtools/source/svhtml/htmlkywd.cxx | 1
6 files changed, 84 insertions(+), 1 deletion(-)
New commits:
commit c687ebfb0868840d321a44b420083f7619e181c6
Author: Miklos Vajna <vmik...@collabora.com>
AuthorDate: Fri Feb 9 12:01:39 2024 +0100
Commit: Caolán McNamara <caolan.mcnam...@collabora.com>
CommitDate: Mon Feb 12 09:48:06 2024 +0100
tdf#159483 sc HTML import: handle data-sheets-value attribute for the num
case
E.g. have "1,000.00" and "2,000.00" in two cells, paste that into calc,
and try to do a SUM() on them, which will fail because the cell content
is text.
Just data-sheets-value itself would not be good solution, because then
we would lose the number format, so the paste result would be like 1000,
which is bad for readability if you don't want further operations on the
value.
Fix the problem by also parsing the data-sheets-numberformat attribute,
so far what's clear is that the "2" JSON key there provides a number
format string which matches the syntax of Excel/Calc.
This gives the best of the two worlds: the output looks like the
original, but SUM() works on the cells as well.
(cherry picked from commit 789964785a61daab5f8065f006dd7aaf843c7236)
Change-Id: Ic7c09ba55a51852f285ad0c05ed42c6771b0f500
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/163193
Tested-by: Jenkins CollaboraOffice <jenkinscollaboraoff...@gmail.com>
Reviewed-by: Caolán McNamara <caolan.mcnam...@collabora.com>
diff --git a/include/svtools/htmlkywd.hxx b/include/svtools/htmlkywd.hxx
index 1c0360404ced..a515ec497e32 100644
--- a/include/svtools/htmlkywd.hxx
+++ b/include/svtools/htmlkywd.hxx
@@ -447,6 +447,7 @@
#define OOO_STRING_SVTOOLS_HTML_O_SDval "sdval"
#define OOO_STRING_SVTOOLS_HTML_O_DSval "data-sheets-value"
#define OOO_STRING_SVTOOLS_HTML_O_SDnum "sdnum"
+#define OOO_STRING_SVTOOLS_HTML_O_DSnum "data-sheets-numberformat"
#define OOO_STRING_SVTOOLS_HTML_O_sdlibrary "sdlibrary"
#define OOO_STRING_SVTOOLS_HTML_O_sdmodule "sdmodule"
#define OOO_STRING_SVTOOLS_HTML_O_sdevent "sdevent-"
diff --git a/include/svtools/htmltokn.h b/include/svtools/htmltokn.h
index b18acd6dcb87..bc8734ddd56d 100644
--- a/include/svtools/htmltokn.h
+++ b/include/svtools/htmltokn.h
@@ -346,6 +346,7 @@ STRING_START = BOOL_END,
SDVAL, // StarDiv NumberValue
DSVAL,
SDNUM, // StarDiv NumberFormat
+ DSNUM,
SDLIBRARY,
SDMODULE,
STRING_END,
diff --git a/sc/qa/filter/html/data/numberformat.html
b/sc/qa/filter/html/data/numberformat.html
new file mode 100644
index 000000000000..3f7b3f56d6bd
--- /dev/null
+++ b/sc/qa/filter/html/data/numberformat.html
@@ -0,0 +1,8 @@
+<table>
+ <tr>
+ <td data-sheets-value="{"1":3,"3":1000}"
data-sheets-numberformat="{"1":2,"2":"#,##0.00","3":1}">1,000.00</td>
+ </tr>
+ <tr>
+ <td data-sheets-value="{"1":3,"3":2000}"
data-sheets-numberformat="{"1":2,"2":"#,##0.00","3":1}">2,000.00</td>
+ </tr>
+</table>
diff --git a/sc/qa/filter/html/html.cxx b/sc/qa/filter/html/html.cxx
index 6ab2cc7fb0b7..b66976396f99 100644
--- a/sc/qa/filter/html/html.cxx
+++ b/sc/qa/filter/html/html.cxx
@@ -116,6 +116,33 @@ 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_PLUGIN_IMPLEMENT();
diff --git a/sc/source/filter/html/htmlpars.cxx
b/sc/source/filter/html/htmlpars.cxx
index ac48799549cd..e70252602af1 100644
--- a/sc/source/filter/html/htmlpars.cxx
+++ b/sc/source/filter/html/htmlpars.cxx
@@ -79,7 +79,6 @@ namespace
/// data-sheets-value from google sheets, value is a JSON.
void ParseDataSheetsValue(const OUString& rDataSheetsValue,
std::optional<OUString>& rVal, std::optional<OUString>& rNum)
{
- // data-sheets-value from google sheets, value is a JSON.
OString aEncodedOption = rDataSheetsValue.toUtf8();
const char* pEncodedOption = aEncodedOption.getStr();
std::stringstream aStream(pEncodedOption);
@@ -100,6 +99,16 @@ void ParseDataSheetsValue(const OUString& rDataSheetsValue,
std::optional<OUStri
rNum = ";;@";
break;
}
+ case 3:
+ {
+ // 3 is number.
+ it = aTree.find("3");
+ if (it != aTree.not_found())
+ {
+ rVal =
OUString::fromUtf8(it->second.get_value<std::string>());
+ }
+ break;
+ }
case 4:
{
// 4 is boolean.
@@ -114,6 +123,37 @@ void ParseDataSheetsValue(const OUString&
rDataSheetsValue, std::optional<OUStri
}
}
}
+
+/// data-sheets-numberformat from google sheets, value is a JSON.
+void ParseDataSheetsNumberformat(const OUString& rDataSheetsValue,
std::optional<OUString>& rNum)
+{
+ OString aEncodedOption = rDataSheetsValue.toUtf8();
+ const char* pEncodedOption = aEncodedOption.getStr();
+ std::stringstream aStream(pEncodedOption);
+ boost::property_tree::ptree aTree;
+ boost::property_tree::read_json(aStream, aTree);
+ // The "1" key describes the other keys.
+ auto it = aTree.find("1");
+ if (it != aTree.not_found())
+ {
+ int nType = std::stoi(it->second.get_value<std::string>());
+ switch (nType)
+ {
+ case 2:
+ {
+ // 2 is number format.
+ it = aTree.find("2");
+ if (it != aTree.not_found())
+ {
+ // Leave the parse and a number language unspecified.
+ OUString aNum = ";;" +
OUString::fromUtf8(it->second.get_value<std::string>());
+ rNum = aNum;
+ }
+ break;
+ }
+ }
+ }
+}
}
ScHTMLStyles::ScHTMLStyles() : maEmpty() {}
@@ -1029,6 +1069,11 @@ void ScHTMLLayoutParser::TableDataOn( HtmlImportInfo*
pInfo )
ParseDataSheetsValue(rOption.GetString(), mxActEntry->pValStr,
mxActEntry->pNumStr);
}
break;
+ case HtmlOptionId::DSNUM:
+ {
+ ParseDataSheetsNumberformat(rOption.GetString(),
mxActEntry->pNumStr);
+ }
+ break;
default: break;
}
}
diff --git a/svtools/source/svhtml/htmlkywd.cxx
b/svtools/source/svhtml/htmlkywd.cxx
index 2bdd3e897b30..47b2f7067f2b 100644
--- a/svtools/source/svhtml/htmlkywd.cxx
+++ b/svtools/source/svhtml/htmlkywd.cxx
@@ -526,6 +526,7 @@ static HTML_OptionEntry aHTMLOptionTab[] = {
{std::u16string_view(u"" OOO_STRING_SVTOOLS_HTML_O_SDval),
HtmlOptionId::SDVAL}, // StarDiv NumberValue
{std::u16string_view(u"" OOO_STRING_SVTOOLS_HTML_O_DSval),
HtmlOptionId::DSVAL},
{std::u16string_view(u"" OOO_STRING_SVTOOLS_HTML_O_SDnum),
HtmlOptionId::SDNUM}, // StarDiv NumberFormat
+ {std::u16string_view(u"" OOO_STRING_SVTOOLS_HTML_O_DSnum),
HtmlOptionId::DSNUM},
{std::u16string_view(u"" OOO_STRING_SVTOOLS_HTML_O_sdlibrary),
HtmlOptionId::SDLIBRARY},
{std::u16string_view(u"" OOO_STRING_SVTOOLS_HTML_O_sdmodule),
HtmlOptionId::SDMODULE},
