From baed960b8ce55370a040d0c0f49bd3fdc16bdf84 Mon Sep 17 00:00:00 2001 From: Bartosz Kosiorek Date: Tue, 15 Aug 2017 07:35:35 +0200 Subject: tdf#89139 Fix exporting of DateTime to CacheDefinition according to ISO 8601 With this commit, the date is saved in Excel format, like: "2009-07-06T10:53:02" We are now exporting attributes: "minDate" "maxDate" "containsDate" This is necessary to export properly .xlsx file. Without that MS Office 365 is displaying error, and it is not even trying displaying .xslx file content Change-Id: I1239f5582173afe99bf9178fd4edd1dc5ca28e8e Reviewed-on: https://gerrit.libreoffice.org/41162 Tested-by: Jenkins Reviewed-by: Bartosz Kosiorek --- sc/source/filter/excel/xepivotxml.cxx | 64 +++++++++++++++++++++++++++++------ 1 file changed, 53 insertions(+), 11 deletions(-) (limited to 'sc/source') diff --git a/sc/source/filter/excel/xepivotxml.cxx b/sc/source/filter/excel/xepivotxml.cxx index e3283ef87c2d..b72e24bf19c5 100644 --- a/sc/source/filter/excel/xepivotxml.cxx +++ b/sc/source/filter/excel/xepivotxml.cxx @@ -17,6 +17,7 @@ #include #include +#include #include #include @@ -51,6 +52,9 @@ void savePivotCacheRecordsXml( XclExpXmlStream& rStrm, const ScDPCache& rCache ) const ScDPCache::IndexArrayType* pArray = rCache.GetFieldIndexArray(nField); assert(pArray); assert(static_cast(i) < pArray->size()); + + // We are using XML_x reference (like: ), instead of values here (eg: ). + // That's why in SavePivotCacheXml method, we need to list all items. pRecStrm->singleElement(XML_x, XML_v, OString::number((*pArray)[i]), FSEND); } pRecStrm->endElement(XML_r); @@ -171,6 +175,27 @@ const XclExpXmlPivotCaches::Entry* XclExpXmlPivotCaches::GetCache( sal_Int32 nCa return &maCaches[nPos]; } +namespace { +/** + * Create combined date and time string according the requirements of Excel. + * A single point in time can be represented by concatenating a complete date expression, + * the letter T as a delimiter, and a valid time expression. For example, "2007-04-05T14:30". + * + * fSerialDateTime - a number representing the number of days since 1900-Jan-0 (integer portion of the number), + * plus a fractional portion of a 24 hour day (fractional portion of the number). + */ +OUString GetExcelFormattedDate( double fSerialDateTime, SvNumberFormatter& rFormatter ) +{ + //::sax::Converter::convertDateTime(sBuf, (DateTime(rFormatter.GetNullDate()) + fSerialDateTime).GetUNODateTime(), 0, true); + css::util::DateTime aUDateTime = (DateTime(rFormatter.GetNullDate()) + fSerialDateTime).GetUNODateTime(); + // We need to reset nanoseconds, to avoid string like: "1982-02-18T16:04:47.999999849" + aUDateTime.NanoSeconds = 0; + OUStringBuffer sBuf; + ::sax::Converter::convertDateTime(sBuf, aUDateTime, nullptr, true); + return sBuf.makeStringAndClear(); +} +} + void XclExpXmlPivotCaches::SavePivotCacheXml( XclExpXmlStream& rStrm, const Entry& rEntry, sal_Int32 nCounter ) { assert(rEntry.mpCache); @@ -235,6 +260,7 @@ void XclExpXmlPivotCaches::SavePivotCacheXml( XclExpXmlStream& rStrm, const Entr std::set aDPTypes; double fMin = std::numeric_limits::infinity(), fMax = -std::numeric_limits::infinity(); bool isValueInteger = true; + bool isContainsDate = rCache.IsDateDimension(i); double intpart; for (; it != itEnd; ++it) { @@ -245,6 +271,7 @@ void XclExpXmlPivotCaches::SavePivotCacheXml( XclExpXmlStream& rStrm, const Entr double fVal = it->GetValue(); fMin = std::min(fMin, fVal); fMax = std::max(fMax, fVal); + // Check if all values are integers if (isValueInteger && (modf(fVal, &intpart) != 0.0)) { @@ -256,7 +283,8 @@ void XclExpXmlPivotCaches::SavePivotCacheXml( XclExpXmlStream& rStrm, const Entr auto aDPTypeEnd = aDPTypes.cend(); auto pAttList = sax_fastparser::FastSerializerHelper::createAttrList(); - + // TODO In same cases, disable listing of items, as it is done in MS Excel. + // Exporting savePivotCacheRecordsXml method needs to be updated accordingly bool bListItems = true; std::set aDPTypesWithoutBlank = aDPTypes; @@ -276,6 +304,11 @@ void XclExpXmlPivotCaches::SavePivotCacheXml( XclExpXmlStream& rStrm, const Entr if (!(isContainsString || (aDPTypes.size() > 1))) pAttList->add(XML_containsSemiMixedTypes, ToPsz10(false)); + // OOXTODO: XML_containsNonDate + + if (isContainsDate) + pAttList->add(XML_containsDate, ToPsz10(true)); + // default for containsString field is true, so we are writing only when is false if (!isContainsString) pAttList->add(XML_containsString, ToPsz10(false)); @@ -284,7 +317,12 @@ void XclExpXmlPivotCaches::SavePivotCacheXml( XclExpXmlStream& rStrm, const Entr if (isContainsBlank) pAttList->add(XML_containsBlank, ToPsz10(true)); - bool isContainsNumber = aDPTypesWithoutBlank.find(ScDPItemData::Value) != aDPTypesWithoutBlank.end(); + // If field contain mixed types (Date and Numbers), MS Excel is saving only "minDate" and "maxDate" and not "minValue" and "maxValue" + // Example how Excel is saving mixed Date and Numbers: + // + // Example how Excel is saving Dates only: + // + bool isContainsNumber = !isContainsDate && aDPTypesWithoutBlank.find(ScDPItemData::Value) != aDPTypesWithoutBlank.end(); if (isContainsNumber) pAttList->add(XML_containsNumber, ToPsz10(true)); @@ -301,15 +339,12 @@ void XclExpXmlPivotCaches::SavePivotCacheXml( XclExpXmlStream& rStrm, const Entr { pAttList->add(XML_minValue, OString::number(fMin)); pAttList->add(XML_maxValue, OString::number(fMax)); - // If there is only numeric types, then we shouldn't list all items - if (aDPTypesWithoutBlank.size() == 1) - bListItems = false; } - if (isContainsBlank && (aDPTypes.size() == 1)) + if (isContainsDate) { - // If all items are blank, then we shouldn't list all items - bListItems = false; + pAttList->add(XML_minDate, XclXmlUtils::ToOString(GetExcelFormattedDate(fMin, GetFormatter()))); + pAttList->add(XML_maxDate, XclXmlUtils::ToOString(GetExcelFormattedDate(fMax, GetFormatter()))); } if (bListItems) @@ -334,9 +369,16 @@ void XclExpXmlPivotCaches::SavePivotCacheXml( XclExpXmlStream& rStrm, const Entr FSEND); break; case ScDPItemData::Value: - pDefStrm->singleElement(XML_n, - XML_v, OString::number(rItem.GetValue()), - FSEND); + if (isContainsDate) + { + pDefStrm->singleElement(XML_d, + XML_v, XclXmlUtils::ToOString(GetExcelFormattedDate(rItem.GetValue(), GetFormatter())), + FSEND); + } + else + pDefStrm->singleElement(XML_n, + XML_v, OString::number(rItem.GetValue()), + FSEND); break; case ScDPItemData::Empty: pDefStrm->singleElement(XML_m, FSEND); -- cgit