Rebased ref, commits from common ancestor:
commit d223775e011ac1d8253b4488ec2d941b2dd3e8a9
Author: Karthik Godha <[email protected]>
AuthorDate: Mon Feb 2 18:15:01 2026 +0530
Commit: Michael Stahl <[email protected]>
CommitDate: Wed Feb 4 20:18:53 2026 +0100
XLSX: Handle invalid Dates in PivotTable
In Excel, Date can't be later than 9999-12-31.
bug-document: forum-mso-en4-213374.xls
Change-Id: Ice06439386abd64384e2b5255054b08d610e42a5
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/198546
Tested-by: Jenkins CollaboraOffice <[email protected]>
Reviewed-by: Michael Stahl <[email protected]>
diff --git a/sc/source/filter/excel/xepivotxml.cxx
b/sc/source/filter/excel/xepivotxml.cxx
index bf426177feb1..363e8446d947 100644
--- a/sc/source/filter/excel/xepivotxml.cxx
+++ b/sc/source/filter/excel/xepivotxml.cxx
@@ -295,7 +295,21 @@ void XclExpXmlPivotCaches::SavePivotCacheXml(
XclExpXmlStream& rStrm, const Entr
pDefStrm->startElement(XML_cacheFields,
XML_count, OString::number(static_cast<tools::Long>(nCount +
nGroupFieldCount)));
- auto WriteFieldGroup = [this, &rCache, pDefStrm](size_t i, size_t base) {
+ SvNumberFormatter& rFormatter = GetFormatter();
+
+ // In Excel, DATE can't be later than 9999-12-31.
+ // Number of days from 1900-01-01 to 9999-12-31 is 2958465.
+ constexpr double MAX_DATE = 2.958465e6;
+ auto GetClampedDate = [&](double fVal, double fSub)
+ {
+ if (fVal > MAX_DATE)
+ return GetExcelFormattedDate(fSub, rFormatter);
+ else
+ return GetExcelFormattedDate(fVal, rFormatter);
+ };
+
+ auto WriteFieldGroup = [&rCache, pDefStrm, &GetClampedDate](size_t i,
size_t base)
+ {
const sal_Int32 nDatePart = rCache.GetGroupType(i);
if (!nDatePart)
return;
@@ -328,15 +342,13 @@ void XclExpXmlPivotCaches::SavePivotCacheXml(
XclExpXmlStream& rStrm, const Entr
// fieldGroup element
pDefStrm->startElement(XML_fieldGroup, XML_base,
OString::number(base));
- SvNumberFormatter& rFormatter = GetFormatter();
-
// rangePr element
const ScDPNumGroupInfo* pGI = rCache.GetNumGroupInfo(i);
auto pGroupAttList =
sax_fastparser::FastSerializerHelper::createAttrList();
pGroupAttList->add(XML_groupBy, sGroupBy);
// Possible TODO: find out when to write autoStart attribute for years
grouping
- pGroupAttList->add(XML_startDate, GetExcelFormattedDate(pGI->mfStart,
rFormatter).toUtf8());
- pGroupAttList->add(XML_endDate, GetExcelFormattedDate(pGI->mfEnd,
rFormatter).toUtf8());
+ pGroupAttList->add(XML_startDate, GetClampedDate(pGI->mfStart,
0).toUtf8());
+ pGroupAttList->add(XML_endDate, GetClampedDate(pGI->mfEnd,
MAX_DATE).toUtf8());
if (pGI->mfStep)
pGroupAttList->add(XML_groupInterval,
OString::number(pGI->mfStep));
pDefStrm->singleElement(XML_rangePr, pGroupAttList);
@@ -457,8 +469,8 @@ void XclExpXmlPivotCaches::SavePivotCacheXml(
XclExpXmlStream& rStrm, const Entr
if (isContainsDate)
{
- pAttList->add(XML_minDate, GetExcelFormattedDate(fMin,
GetFormatter()).toUtf8());
- pAttList->add(XML_maxDate, GetExcelFormattedDate(fMax,
GetFormatter()).toUtf8());
+ pAttList->add(XML_minDate, GetClampedDate(fMin, 0).toUtf8());
+ pAttList->add(XML_maxDate, GetClampedDate(fMax,
MAX_DATE).toUtf8());
}
//if (bListItems) // see TODO above
@@ -485,8 +497,13 @@ void XclExpXmlPivotCaches::SavePivotCacheXml(
XclExpXmlStream& rStrm, const Entr
case ScDPItemData::Value:
if (isContainsDate)
{
- pDefStrm->singleElement(XML_d,
- XML_v, GetExcelFormattedDate(rItem.GetValue(),
GetFormatter()).toUtf8());
+ double fDate = rItem.GetValue();
+ if (fDate > MAX_DATE)
+ pDefStrm->singleElement(XML_n, XML_v,
OString::number(fDate));
+ else
+ pDefStrm->singleElement(
+ XML_d, XML_v,
+ GetExcelFormattedDate(fDate,
GetFormatter()).toUtf8());
}
else
pDefStrm->singleElement(XML_n,
commit fb138143df479e2f742ed194fdeb55606e032c77
Author: Karthik Godha <[email protected]>
AuthorDate: Mon Feb 2 10:25:43 2026 +0530
Commit: Michael Stahl <[email protected]>
CommitDate: Wed Feb 4 19:18:20 2026 +0100
XLSX: Use Excel compatible errors in PivotTable
Excel doesn't support all LO internal errors, replace them with Excel
compatible errors
Ex: #ADDIN?
bug-document: forum-mso-en3-16864.xls
Change-Id: Id6d4f9e2d36e16f859c08a9bf2a0f96fea87d3b3
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/198509
Tested-by: Jenkins CollaboraOffice <[email protected]>
Reviewed-by: Michael Stahl <[email protected]>
diff --git a/sc/inc/global.hxx b/sc/inc/global.hxx
index e37b2dbaef58..6e986267c8a6 100644
--- a/sc/inc/global.hxx
+++ b/sc/inc/global.hxx
@@ -627,6 +627,8 @@ public:
const SfxItemSet& rOldAttrs,
const sal_uInt16 nWhich );
+ static bool IsValidOOXMLError(FormulaError nErrNumber);
+
static ScUnitConverter* GetUnitConverter();
/// strchr() functionality on unicode, as long as we need it for
FormulaToken etc.
diff --git a/sc/source/core/data/dpcache.cxx b/sc/source/core/data/dpcache.cxx
index 969bd5c01c4f..5819c0a8d566 100644
--- a/sc/source/core/data/dpcache.cxx
+++ b/sc/source/core/data/dpcache.cxx
@@ -149,7 +149,12 @@ void initFromCell(
if (rCell.hasError())
{
- rData.SetErrorStringInterned(internString(rStrPool,
rDoc.GetString(rPos.Col(), rPos.Row(), rPos.Tab())));
+ if (ScGlobal::IsValidOOXMLError(rCell.getFormula()->GetErrCode()))
+ rData.SetErrorStringInterned(
+ internString(rStrPool, rDoc.GetString(rPos.Col(), rPos.Row(),
rPos.Tab())));
+ else
+ rData.SetErrorStringInterned(
+ internString(rStrPool,
ScGlobal::GetErrorString(FormulaError::NotAvailable)));
}
else if (rCell.hasNumeric())
{
diff --git a/sc/source/core/data/global.cxx b/sc/source/core/data/global.cxx
index 50017bd40553..56f19267f47f 100644
--- a/sc/source/core/data/global.cxx
+++ b/sc/source/core/data/global.cxx
@@ -444,6 +444,23 @@ OUString ScGlobal::GetLongErrorString(FormulaError nErr)
return ScResId(pErrNumber);
}
+bool ScGlobal::IsValidOOXMLError(FormulaError nErrNumber)
+{
+ switch (nErrNumber)
+ {
+ case FormulaError::DivisionByZero:
+ case FormulaError::NotAvailable:
+ case FormulaError::NoName:
+ case FormulaError::NoCode:
+ case FormulaError::IllegalFPOperation:
+ case FormulaError::NoRef:
+ case FormulaError::NoValue:
+ return true;
+ default:
+ return false;
+ }
+}
+
SvxBrushItem* ScGlobal::GetButtonBrushItem()
{
assert(!bThreadedGroupCalcInProgress);
