sc/source/filter/excel/xepivotxml.cxx

   1 /* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
   2 /*
   3  * This file is part of the LibreOffice project.
   4  *
   5  * This Source Code Form is subject to the terms of the Mozilla Public
   6  * License, v. 2.0. If a copy of the MPL was not distributed with this
   7  * file, You can obtain one at http://mozilla.org/MPL/2.0/.
   8  */
   9
  10 #include <xepivotxml.hxx>
  11 #include <dpcache.hxx>
  12 #include <dpitemdata.hxx>
  13 #include <dpobject.hxx>
  14 #include <dpsave.hxx>
  15 #include <dputil.hxx>
  16 #include <document.hxx>
  17 #include <generalfunction.hxx>
  18
  19 #include <o3tl/temporary.hxx>
  20 #include <oox/export/utils.hxx>
  21 #include <oox/token/namespaces.hxx>
  22 #include <sax/tools/converter.hxx>
  23 #include <sax/fastattribs.hxx>
  24
  25 #include <com/sun/star/sheet/DataPilotFieldOrientation.hpp>
  26 #include <com/sun/star/sheet/DataPilotFieldLayoutMode.hpp>
  27 #include <com/sun/star/sheet/DataPilotOutputRangeType.hpp>
  28
  29 #include <vector>
  30
  31 using namespace oox;
  32 using namespace com::sun::star;
  33
  34 namespace {
  35
  36 void savePivotCacheRecordsXml( XclExpXmlStream& rStrm, const ScDPCache& rCache )
  37 {
  38     SCROW nCount = rCache.GetDataSize();
  39     size_t nFieldCount = rCache.GetFieldCount();
  40
  41     sax_fastparser::FSHelperPtr& pRecStrm = rStrm.GetCurrentStream();
  42     pRecStrm->startElement(XML_pivotCacheRecords,
  43         XML_xmlns, rStrm.getNamespaceURL(OOX_NS(xls)).toUtf8(),
  44         FSNS(XML_xmlns, XML_r), rStrm.getNamespaceURL(OOX_NS(officeRel)).toUtf8(),
  45         XML_count, OString::number(static_cast<long>(nCount)).getStr(),
  46         FSEND);
  47
  48     for (SCROW i = 0; i < nCount; ++i)
  49     {
  50         pRecStrm->startElement(XML_r, FSEND);
  51         for (size_t nField = 0; nField < nFieldCount; ++nField)
  52         {
  53             const ScDPCache::IndexArrayType* pArray = rCache.GetFieldIndexArray(nField);
  54             assert(pArray);
  55             assert(static_cast<size_t>(i) < pArray->size());
  56
  57             // We are using XML_x reference (like: <x v="0"/>), instead of values here (eg: <s v="No Discount"/>).
  58             // That's why in SavePivotCacheXml method, we need to list all items.
  59             pRecStrm->singleElement(XML_x, XML_v, OString::number((*pArray)[i]), FSEND);
  60         }
  61         pRecStrm->endElement(XML_r);
  62     }
  63
  64     pRecStrm->endElement(XML_pivotCacheRecords);
  65 }
  66
  67 const char* toOOXMLAxisType( sheet::DataPilotFieldOrientation eOrient )
  68 {
  69     switch (eOrient)
  70     {
  71         case sheet::DataPilotFieldOrientation_COLUMN:
  72             return "axisCol";
  73         case sheet::DataPilotFieldOrientation_ROW:
  74             return "axisRow";
  75         case sheet::DataPilotFieldOrientation_PAGE:
  76             return "axisPage";
  77         case sheet::DataPilotFieldOrientation_DATA:
  78             return "axisValues";
  79         case sheet::DataPilotFieldOrientation_HIDDEN:
  80         default:
  81             ;
  82     }
  83
  84     return "";
  85 }
  86
  87 const char* toOOXMLSubtotalType(ScGeneralFunction eFunc)
  88 {
  89     switch (eFunc)
  90     {
  91         case ScGeneralFunction::SUM:
  92             return "sum";
  93         case ScGeneralFunction::COUNT:
  94             return "count";
  95         case ScGeneralFunction::AVERAGE:
  96             return "average";
  97         case ScGeneralFunction::MAX:
  98             return "max";
  99         case ScGeneralFunction::MIN:
 100             return "min";
 101         case ScGeneralFunction::PRODUCT:
 102             return "product";
 103         case ScGeneralFunction::COUNTNUMS:
 104             return "countNums";
 105         case ScGeneralFunction::STDEV:
 106             return "stdDev";
 107         case ScGeneralFunction::STDEVP:
 108             return "stdDevp";
 109         case ScGeneralFunction::VAR:
 110             return "var";
 111         case ScGeneralFunction::VARP:
 112             return "varp";
 113         default:
 114             ;
 115     }
 116     return nullptr;
 117 }
 118
 119 }
 120
 121 XclExpXmlPivotCaches::XclExpXmlPivotCaches( const XclExpRoot& rRoot ) :
 122     XclExpRoot(rRoot) {}
 123
 124 void XclExpXmlPivotCaches::SaveXml( XclExpXmlStream& rStrm )
 125 {
 126     sax_fastparser::FSHelperPtr& pWorkbookStrm = rStrm.GetCurrentStream();
 127     pWorkbookStrm->startElement(XML_pivotCaches, FSEND);
 128
 129     for (size_t i = 0, n = maCaches.size(); i < n; ++i)
 130     {
 131         const Entry& rEntry = maCaches[i];
 132
 133         sal_Int32 nCacheId = i + 1;
 134         OUString aRelId;
 135         sax_fastparser::FSHelperPtr pPCStrm = rStrm.CreateOutputStream(
 136             XclXmlUtils::GetStreamName("xl/pivotCache/", "pivotCacheDefinition", nCacheId),
 137             XclXmlUtils::GetStreamName(nullptr, "pivotCache/pivotCacheDefinition", nCacheId),
 138             rStrm.GetCurrentStream()->getOutputStream(),
 139             CREATE_XL_CONTENT_TYPE("pivotCacheDefinition"),
 140             CREATE_OFFICEDOC_RELATION_TYPE("pivotCacheDefinition"),
 141             &aRelId);
 142
 143         pWorkbookStrm->singleElement(XML_pivotCache,
 144             XML_cacheId, OString::number(nCacheId).getStr(),
 145             FSNS(XML_r, XML_id), aRelId.toUtf8(),
 146             FSEND);
 147
 148         rStrm.PushStream(pPCStrm);
 149         SavePivotCacheXml(rStrm, rEntry, nCacheId);
 150         rStrm.PopStream();
 151     }
 152
 153     pWorkbookStrm->endElement(XML_pivotCaches);
 154 }
 155
 156 void XclExpXmlPivotCaches::SetCaches( const std::vector<Entry>& rCaches )
 157 {
 158     maCaches = rCaches;
 159 }
 160
 161 bool XclExpXmlPivotCaches::HasCaches() const
 162 {
 163     return !maCaches.empty();
 164 }
 165
 166 const XclExpXmlPivotCaches::Entry* XclExpXmlPivotCaches::GetCache( sal_Int32 nCacheId ) const
 167 {
 168     if (nCacheId <= 0)
 169         // cache ID is 1-based.
 170         return nullptr;
 171
 172     size_t nPos = nCacheId - 1;
 173     if (nPos >= maCaches.size())
 174         return nullptr;
 175
 176     return &maCaches[nPos];
 177 }
 178
 179 namespace {
 180 /**
 181  * Create combined date and time string according the requirements of Excel.
 182  * A single point in time can be represented by concatenating a complete date expression,
 183  * the letter T as a delimiter, and a valid time expression. For example, "2007-04-05T14:30".
 184  *
 185  * fSerialDateTime - a number representing the number of days since 1900-Jan-0 (integer portion of the number),
 186  * plus a fractional portion of a 24 hour day (fractional portion of the number).
 187  */
 188 OUString GetExcelFormattedDate( double fSerialDateTime, const SvNumberFormatter& rFormatter )
 189 {
 190     //::sax::Converter::convertDateTime(sBuf, (DateTime(rFormatter.GetNullDate()) + fSerialDateTime).GetUNODateTime(), 0, true);
 191     css::util::DateTime aUDateTime = (DateTime(rFormatter.GetNullDate()) + fSerialDateTime).GetUNODateTime();
 192     // We need to reset nanoseconds, to avoid string like: "1982-02-18T16:04:47.999999849"
 193     aUDateTime.NanoSeconds = 0;
 194     OUStringBuffer sBuf;
 195     ::sax::Converter::convertDateTime(sBuf, aUDateTime, nullptr, true);
 196     return sBuf.makeStringAndClear();
 197 }
 198 }
 199
 200 void XclExpXmlPivotCaches::SavePivotCacheXml( XclExpXmlStream& rStrm, const Entry& rEntry, sal_Int32 nCounter )
 201 {
 202     assert(rEntry.mpCache);
 203     const ScDPCache& rCache = *rEntry.mpCache;
 204
 205     sax_fastparser::FSHelperPtr& pDefStrm = rStrm.GetCurrentStream();
 206
 207     OUString aRelId;
 208     sax_fastparser::FSHelperPtr pRecStrm = rStrm.CreateOutputStream(
 209         XclXmlUtils::GetStreamName("xl/pivotCache/", "pivotCacheRecords", nCounter),
 210         XclXmlUtils::GetStreamName(nullptr, "pivotCacheRecords", nCounter),
 211         pDefStrm->getOutputStream(),
 212         CREATE_XL_CONTENT_TYPE("pivotCacheRecords"),
 213         CREATE_OFFICEDOC_RELATION_TYPE("pivotCacheRecords"),
 214         &aRelId);
 215
 216     rStrm.PushStream(pRecStrm);
 217     savePivotCacheRecordsXml(rStrm, rCache);
 218     rStrm.PopStream();
 219
 220     pDefStrm->startElement(XML_pivotCacheDefinition,
 221         XML_xmlns, rStrm.getNamespaceURL(OOX_NS(xls)).toUtf8(),
 222         FSNS(XML_xmlns, XML_r), rStrm.getNamespaceURL(OOX_NS(officeRel)).toUtf8(),
 223         FSNS(XML_r, XML_id), aRelId.toUtf8(),
 224         XML_recordCount, OString::number(rEntry.mpCache->GetDataSize()).getStr(),
 225         XML_createdVersion, "3", // MS Excel 2007, tdf#112936: setting version number makes MSO to handle the pivot table differently
 226         FSEND);
 227
 228     pDefStrm->startElement(XML_cacheSource,
 229         XML_type, "worksheet",
 230         FSEND);
 231
 232     OUString aSheetName;
 233     GetDoc().GetName(rEntry.maSrcRange.aStart.Tab(), aSheetName);
 234     pDefStrm->singleElement(XML_worksheetSource,
 235         XML_ref, XclXmlUtils::ToOString(rEntry.maSrcRange).getStr(),
 236         XML_sheet, aSheetName.toUtf8(),
 237         FSEND);
 238
 239     pDefStrm->endElement(XML_cacheSource);
 240
 241     size_t nCount = rCache.GetFieldCount();
 242     pDefStrm->startElement(XML_cacheFields,
 243         XML_count, OString::number(static_cast<long>(nCount)).getStr(),
 244         FSEND);
 245
 246     for (size_t i = 0; i < nCount; ++i)
 247     {
 248         OUString aName = rCache.GetDimensionName(i);
 249
 250         pDefStrm->startElement(XML_cacheField,
 251             XML_name, aName.toUtf8(),
 252             XML_numFmtId, OString::number(0).getStr(),
 253             FSEND);
 254
 255         const ScDPCache::ScDPItemDataVec& rFieldItems = rCache.GetDimMemberValues(i);
 256
 257         std::set<ScDPItemData::Type> aDPTypes;
 258         double fMin = std::numeric_limits<double>::infinity(), fMax = -std::numeric_limits<double>::infinity();
 259         bool isValueInteger = true;
 260         bool isContainsDate = rCache.IsDateDimension(i);
 261         for (const auto& rFieldItem : rFieldItems)
 262         {
 263             ScDPItemData::Type eType = rFieldItem.GetType();
 264             // tdf#123939 : error and string are same for cache; if both are present, keep only one
 265             if (eType == ScDPItemData::Error)
 266                 eType = ScDPItemData::String;
 267             aDPTypes.insert(eType);
 268             if (eType == ScDPItemData::Value)
 269             {
 270                 double fVal = rFieldItem.GetValue();
 271                 fMin = std::min(fMin, fVal);
 272                 fMax = std::max(fMax, fVal);
 273
 274                 // Check if all values are integers
 275                 if (isValueInteger && (modf(fVal, &o3tl::temporary(double())) != 0.0))
 276                 {
 277                     isValueInteger = false;
 278                 }
 279             }
 280         }
 281
 282         auto pAttList = sax_fastparser::FastSerializerHelper::createAttrList();
 283         // TODO In same cases, disable listing of items, as it is done in MS Excel.
 284         // Exporting savePivotCacheRecordsXml method needs to be updated accordingly
 285         //bool bListItems = true;
 286
 287         std::set<ScDPItemData::Type> aDPTypesWithoutBlank = aDPTypes;
 288         aDPTypesWithoutBlank.erase(ScDPItemData::Empty);
 289
 290         const bool isContainsString = aDPTypesWithoutBlank.count(ScDPItemData::String) > 0;
 291         const bool isContainsBlank = aDPTypes.count(ScDPItemData::Empty) > 0;
 292         const bool isContainsNumber
 293             = !isContainsDate && aDPTypesWithoutBlank.count(ScDPItemData::Value) > 0;
 294         bool isContainsNonDate = !(isContainsDate && aDPTypesWithoutBlank.size() <= 1);
 295
 296         // XML_containsSemiMixedTypes possible values:
 297         // 1 - (Default) at least one text value, or can also contain a mix of other data types and blank values,
 298         //     or blank values only
 299         // 0 - the field does not have a mix of text and other values
 300         if (!(isContainsString || (aDPTypes.size() > 1) || (isContainsBlank && aDPTypesWithoutBlank.empty())))
 301             pAttList->add(XML_containsSemiMixedTypes, ToPsz10(false));
 302
 303         if (!isContainsNonDate)
 304             pAttList->add(XML_containsNonDate, ToPsz10(false));
 305
 306         if (isContainsDate)
 307             pAttList->add(XML_containsDate, ToPsz10(true));
 308
 309         // default for containsString field is true, so we are writing only when is false
 310         if (!isContainsString)
 311             pAttList->add(XML_containsString, ToPsz10(false));
 312
 313         if (isContainsBlank)
 314             pAttList->add(XML_containsBlank, ToPsz10(true));
 315
 316         // XML_containsMixedType possible values:
 317         // 1 - field contains more than one data type
 318         // 0 - (Default) only one data type. The field can still contain blank values (that's why we are using aDPTypesWithoutBlank)
 319         if (aDPTypesWithoutBlank.size() > 1)
 320             pAttList->add(XML_containsMixedTypes, ToPsz10(true));
 321
 322         // If field contain mixed types (Date and Numbers), MS Excel is saving only "minDate" and "maxDate" and not "minValue" and "maxValue"
 323         // Example how Excel is saving mixed Date and Numbers:
 324         // <sharedItems containsSemiMixedTypes="0" containsDate="1" containsString="0" containsMixedTypes="1" minDate="1900-01-03T22:26:04" maxDate="1900-01-07T14:02:04" />
 325         // Example how Excel is saving Dates only:
 326         // <sharedItems containsSemiMixedTypes="0" containsNonDate="0" containsDate="1" containsString="0" minDate="1903-08-24T07:40:48" maxDate="2024-05-23T07:12:00"/>
 327         if (isContainsNumber)
 328             pAttList->add(XML_containsNumber, ToPsz10(true));
 329
 330         if (isValueInteger && isContainsNumber)
 331             pAttList->add(XML_containsInteger, ToPsz10(true));
 332
 333
 334         // Number type fields could be mixed with blank types, and it shouldn't be treated as listed items.
 335         // Example:
 336         //    <cacheField name="employeeID" numFmtId="0">
 337         //        <sharedItems containsString="0" containsBlank="1" containsNumber="1" containsInteger="1" minValue="35" maxValue="89"/>
 338         //    </cacheField>
 339         if (isContainsNumber)
 340         {
 341             pAttList->add(XML_minValue, OString::number(fMin));
 342             pAttList->add(XML_maxValue, OString::number(fMax));
 343         }
 344
 345         if (isContainsDate)
 346         {
 347             pAttList->add(XML_minDate, GetExcelFormattedDate(fMin, GetFormatter()).toUtf8());
 348             pAttList->add(XML_maxDate, GetExcelFormattedDate(fMax, GetFormatter()).toUtf8());
 349         }
 350
 351         //if (bListItems) // see TODO above
 352         {
 353             pAttList->add(XML_count, OString::number(static_cast<long>(rFieldItems.size())));
 354         }
 355         sax_fastparser::XFastAttributeListRef xAttributeList(pAttList);
 356
 357         pDefStrm->startElement(XML_sharedItems, xAttributeList);
 358
 359         //if (bListItems) // see TODO above
 360         {
 361             for (const ScDPItemData& rItem : rFieldItems)
 362             {
 363                 switch (rItem.GetType())
 364                 {
 365                     case ScDPItemData::String:
 366                         pDefStrm->singleElement(XML_s,
 367                             XML_v, rItem.GetString().toUtf8(),
 368                             FSEND);
 369                     break;
 370                     case ScDPItemData::Value:
 371                         if (isContainsDate)
 372                         {
 373                             pDefStrm->singleElement(XML_d,
 374                                 XML_v, GetExcelFormattedDate(rItem.GetValue(), GetFormatter()).toUtf8(),
 375                                 FSEND);
 376                         }
 377                         else
 378                             pDefStrm->singleElement(XML_n,
 379                                 XML_v, OString::number(rItem.GetValue()),
 380                                 FSEND);
 381                     break;
 382                     case ScDPItemData::Empty:
 383                         pDefStrm->singleElement(XML_m, FSEND);
 384                     break;
 385                     case ScDPItemData::Error:
 386                         pDefStrm->singleElement(XML_e,
 387                             XML_v, rItem.GetString().toUtf8(),
 388                             FSEND);
 389                     break;
 390                     case ScDPItemData::GroupValue:
 391                     case ScDPItemData::RangeStart:
 392                         // TODO : What do we do with these types?
 393                         pDefStrm->singleElement(XML_m, FSEND);
 394                     break;
 395                     default:
 396                         ;
 397                 }
 398             }
 399         }
 400
 401         pDefStrm->endElement(XML_sharedItems);
 402         pDefStrm->endElement(XML_cacheField);
 403     }
 404
 405     pDefStrm->endElement(XML_cacheFields);
 406
 407     pDefStrm->endElement(XML_pivotCacheDefinition);
 408 }
 409
 410 XclExpXmlPivotTableManager::XclExpXmlPivotTableManager( const XclExpRoot& rRoot ) :
 411     XclExpRoot(rRoot), maCaches(rRoot) {}
 412
 413 void XclExpXmlPivotTableManager::Initialize()
 414 {
 415     ScDocument& rDoc = GetDoc();
 416     if (!rDoc.HasPivotTable())
 417         // No pivot table to export.
 418         return;
 419
 420     ScDPCollection* pDPColl = rDoc.GetDPCollection();
 421     if (!pDPColl)
 422         return;
 423
 424     // Update caches from DPObject
 425     for (size_t i = 0; i < pDPColl->GetCount(); ++i)
 426     {
 427         ScDPObject& rDPObj = (*pDPColl)[i];
 428         rDPObj.SyncAllDimensionMembers();
 429         (void)rDPObj.GetOutputRangeByType(sheet::DataPilotOutputRangeType::TABLE);
 430     }
 431
 432     // Go through the caches first.
 433
 434     std::vector<XclExpXmlPivotCaches::Entry> aCaches;
 435     const ScDPCollection::SheetCaches& rSheetCaches = pDPColl->GetSheetCaches();
 436     const std::vector<ScRange>& rRanges = rSheetCaches.getAllRanges();
 437     for (const auto & rRange : rRanges)
 438     {
 439         const ScDPCache* pCache = rSheetCaches.getExistingCache(rRange);
 440         if (!pCache)
 441             continue;
 442
 443         // Get all pivot objects that reference this cache, and set up an
 444         // object to cache ID mapping.
 445         const ScDPCache::ScDPObjectSet& rRefs = pCache->GetAllReferences();
 446         for (const auto& rRef : rRefs)
 447             maCacheIdMap.emplace(rRef, aCaches.size()+1);
 448
 449         XclExpXmlPivotCaches::Entry aEntry;
 450         aEntry.mpCache = pCache;
 451         aEntry.maSrcRange = rRange;
 452         aCaches.push_back(aEntry); // Cache ID equals position + 1.
 453     }
 454
 455     // TODO : Handle name and database caches as well.
 456
 457     for (size_t i = 0, n = pDPColl->GetCount(); i < n; ++i)
 458     {
 459         const ScDPObject& rDPObj = (*pDPColl)[i];
 460
 461         // Get the cache ID for this pivot table.
 462         CacheIdMapType::iterator itCache = maCacheIdMap.find(&rDPObj);
 463         if (itCache == maCacheIdMap.end())
 464             // No cache ID found.  Something is wrong here....
 465             continue;
 466
 467         sal_Int32 nCacheId = itCache->second;
 468         SCTAB nTab = rDPObj.GetOutRange().aStart.Tab();
 469
 470         TablesType::iterator it = m_Tables.find(nTab);
 471         if (it == m_Tables.end())
 472         {
 473             // Insert a new instance for this sheet index.
 474             std::pair<TablesType::iterator, bool> r =
 475                 m_Tables.insert(std::make_pair(nTab, std::make_unique<XclExpXmlPivotTables>(GetRoot(), maCaches)));
 476             it = r.first;
 477         }
 478
 479         XclExpXmlPivotTables *const p = it->second.get();
 480         p->AppendTable(&rDPObj, nCacheId, i+1);
 481     }
 482
 483     maCaches.SetCaches(aCaches);
 484 }
 485
 486 XclExpXmlPivotCaches& XclExpXmlPivotTableManager::GetCaches()
 487 {
 488     return maCaches;
 489 }
 490
 491 XclExpXmlPivotTables* XclExpXmlPivotTableManager::GetTablesBySheet( SCTAB nTab )
 492 {
 493     TablesType::iterator const it = m_Tables.find(nTab);
 494     return it == m_Tables.end() ? nullptr : it->second.get();
 495 }
 496
 497 XclExpXmlPivotTables::Entry::Entry( const ScDPObject* pTable, sal_Int32 nCacheId, sal_Int32 nPivotId ) :
 498     mpTable(pTable), mnCacheId(nCacheId), mnPivotId(nPivotId) {}
 499
 500 XclExpXmlPivotTables::XclExpXmlPivotTables( const XclExpRoot& rRoot, const XclExpXmlPivotCaches& rCaches ) :
 501     XclExpRoot(rRoot), mrCaches(rCaches) {}
 502
 503 void XclExpXmlPivotTables::SaveXml( XclExpXmlStream& rStrm )
 504 {
 505     sax_fastparser::FSHelperPtr& pWSStrm = rStrm.GetCurrentStream(); // worksheet stream
 506
 507     for (const auto& rTable : maTables)
 508     {
 509         const ScDPObject& rObj = *rTable.mpTable;
 510         sal_Int32 nCacheId = rTable.mnCacheId;
 511         sal_Int32 nPivotId = rTable.mnPivotId;
 512
 513         sax_fastparser::FSHelperPtr pPivotStrm = rStrm.CreateOutputStream(
 514             XclXmlUtils::GetStreamName("xl/pivotTables/", "pivotTable", nPivotId),
 515             XclXmlUtils::GetStreamName(nullptr, "../pivotTables/pivotTable", nPivotId),
 516             pWSStrm->getOutputStream(),
 517             CREATE_XL_CONTENT_TYPE("pivotTable"),
 518             CREATE_OFFICEDOC_RELATION_TYPE("pivotTable"));
 519
 520         rStrm.PushStream(pPivotStrm);
 521         SavePivotTableXml(rStrm, rObj, nCacheId);
 522         rStrm.PopStream();
 523     }
 524 }
 525
 526 namespace {
 527
 528 struct DataField
 529 {
 530     long const mnPos; // field index in pivot cache.
 531     const ScDPSaveDimension* mpDim;
 532
 533     DataField( long nPos, const ScDPSaveDimension* pDim ) : mnPos(nPos), mpDim(pDim) {}
 534 };
 535
 536 /** Returns a OOXML subtotal function name string. See ECMA-376-1:2016 18.18.43 */
 537 OString GetSubtotalFuncName(ScGeneralFunction eFunc)
 538 {
 539     switch (eFunc)
 540     {
 541         case ScGeneralFunction::SUM:       return "sum";
 542         case ScGeneralFunction::COUNT:     return "count";
 543         case ScGeneralFunction::AVERAGE:   return "avg";
 544         case ScGeneralFunction::MAX:       return "max";
 545         case ScGeneralFunction::MIN:       return "min";
 546         case ScGeneralFunction::PRODUCT:   return "product";
 547         case ScGeneralFunction::COUNTNUMS: return "countA";
 548         case ScGeneralFunction::STDEV:     return "stdDev";
 549         case ScGeneralFunction::STDEVP:    return "stdDevP";
 550         case ScGeneralFunction::VAR:       return "var";
 551         case ScGeneralFunction::VARP:      return "varP";
 552         default:;
 553     }
 554     return "default";
 555 }
 556
 557 sal_Int32 GetSubtotalAttrToken(ScGeneralFunction eFunc)
 558 {
 559     switch (eFunc)
 560     {
 561         case ScGeneralFunction::SUM:       return XML_sumSubtotal;
 562         case ScGeneralFunction::COUNT:     return XML_countSubtotal;
 563         case ScGeneralFunction::AVERAGE:   return XML_avgSubtotal;
 564         case ScGeneralFunction::MAX:       return XML_maxSubtotal;
 565         case ScGeneralFunction::MIN:       return XML_minSubtotal;
 566         case ScGeneralFunction::PRODUCT:   return XML_productSubtotal;
 567         case ScGeneralFunction::COUNTNUMS: return XML_countASubtotal;
 568         case ScGeneralFunction::STDEV:     return XML_stdDevSubtotal;
 569         case ScGeneralFunction::STDEVP:    return XML_stdDevPSubtotal;
 570         case ScGeneralFunction::VAR:       return XML_varSubtotal;
 571         case ScGeneralFunction::VARP:      return XML_varPSubtotal;
 572         default:;
 573     }
 574     return XML_defaultSubtotal;
 575 }
 576
 577 }
 578
 579 void XclExpXmlPivotTables::SavePivotTableXml( XclExpXmlStream& rStrm, const ScDPObject& rDPObj, sal_Int32 nCacheId )
 580 {
 581     typedef std::unordered_map<OUString, long> NameToIdMapType;
 582
 583     const XclExpXmlPivotCaches::Entry* pCacheEntry = mrCaches.GetCache(nCacheId);
 584     if (!pCacheEntry)
 585         // Something is horribly wrong.  Check your logic.
 586         return;
 587
 588     const ScDPCache& rCache = *pCacheEntry->mpCache;
 589
 590     const ScDPSaveData& rSaveData = *rDPObj.GetSaveData();
 591
 592     size_t nFieldCount = rCache.GetFieldCount();
 593     std::vector<const ScDPSaveDimension*> aCachedDims;
 594     NameToIdMapType aNameToIdMap;
 595
 596     aCachedDims.reserve(nFieldCount);
 597     for (size_t i = 0; i < nFieldCount; ++i)
 598     {
 599         OUString aName = rCache.GetDimensionName(i);
 600         aNameToIdMap.emplace(aName, aCachedDims.size());
 601         const ScDPSaveDimension* pDim = rSaveData.GetExistingDimensionByName(aName);
 602         aCachedDims.push_back(pDim);
 603     }
 604
 605     std::vector<long> aRowFields;
 606     std::vector<long> aColFields;
 607     std::vector<long> aPageFields;
 608     std::vector<DataField> aDataFields;
 609
 610     long nDataDimCount = rSaveData.GetDataDimensionCount();
 611     // Use dimensions in the save data to get their correct ordering.
 612     // Dimension order here is significant as they specify the order of
 613     // appearance in each axis.
 614     const ScDPSaveData::DimsType& rDims = rSaveData.GetDimensions();
 615     bool bTabularMode = false;
 616     for (const auto & i : rDims)
 617     {
 618         const ScDPSaveDimension& rDim = *i;
 619
 620         long nPos = -1; // position in cache
 621         if (rDim.IsDataLayout())
 622             nPos = -2; // Excel uses an index of -2 to indicate a data layout field.
 623         else
 624         {
 625             OUString aSrcName = ScDPUtil::getSourceDimensionName(rDim.GetName());
 626             NameToIdMapType::iterator it = aNameToIdMap.find(aSrcName);
 627             if (it != aNameToIdMap.end())
 628                 nPos = it->second;
 629
 630             if (nPos == -1)
 631                 continue;
 632
 633             if (!aCachedDims[nPos])
 634                 continue;
 635         }
 636
 637         sheet::DataPilotFieldOrientation eOrient = rDim.GetOrientation();
 638
 639         switch (eOrient)
 640         {
 641             case sheet::DataPilotFieldOrientation_COLUMN:
 642                 if (nPos == -2 && nDataDimCount <= 1)
 643                     break;
 644                 aColFields.push_back(nPos);
 645             break;
 646             case sheet::DataPilotFieldOrientation_ROW:
 647                 aRowFields.push_back(nPos);
 648             break;
 649             case sheet::DataPilotFieldOrientation_PAGE:
 650                 aPageFields.push_back(nPos);
 651             break;
 652             case sheet::DataPilotFieldOrientation_DATA:
 653                 aDataFields.emplace_back(nPos, &rDim);
 654             break;
 655             case sheet::DataPilotFieldOrientation_HIDDEN:
 656             default:
 657                 ;
 658         }
 659         if(rDim.GetLayoutInfo())
 660             bTabularMode |= (rDim.GetLayoutInfo()->LayoutMode == sheet::DataPilotFieldLayoutMode::TABULAR_LAYOUT);
 661     }
 662
 663     sax_fastparser::FSHelperPtr& pPivotStrm = rStrm.GetCurrentStream();
 664     pPivotStrm->startElement(XML_pivotTableDefinition,
 665         XML_xmlns, rStrm.getNamespaceURL(OOX_NS(xls)).toUtf8(),
 666         XML_name, rDPObj.GetName().toUtf8(),
 667         XML_cacheId, OString::number(nCacheId).getStr(),
 668         XML_applyNumberFormats, ToPsz10(false),
 669         XML_applyBorderFormats, ToPsz10(false),
 670         XML_applyFontFormats, ToPsz10(false),
 671         XML_applyPatternFormats, ToPsz10(false),
 672         XML_applyAlignmentFormats, ToPsz10(false),
 673         XML_applyWidthHeightFormats, ToPsz10(false),
 674         XML_dataCaption, "Values",
 675         XML_useAutoFormatting, ToPsz10(false),
 676         XML_itemPrintTitles, ToPsz10(true),
 677         XML_indent, ToPsz10(false),
 678         XML_outline, ToPsz10(!bTabularMode),
 679         XML_outlineData, ToPsz10(!bTabularMode),
 680         XML_compact, ToPsz10(false),
 681         XML_compactData, ToPsz10(false),
 682         FSEND);
 683
 684     // NB: Excel's range does not include page field area (if any).
 685     ScRange aOutRange = rDPObj.GetOutputRangeByType(sheet::DataPilotOutputRangeType::TABLE);
 686
 687     sal_Int32 nFirstHeaderRow = rDPObj.GetHeaderLayout() ? 2 : 1;
 688     sal_Int32 nFirstDataRow = 2;
 689     sal_Int32 nFirstDataCol = 1;
 690     ScRange aResRange = rDPObj.GetOutputRangeByType(sheet::DataPilotOutputRangeType::RESULT);
 691
 692     if (!aOutRange.IsValid())
 693         aOutRange = rDPObj.GetOutRange();
 694
 695     if (aOutRange.IsValid() && aResRange.IsValid())
 696     {
 697         nFirstDataRow = aResRange.aStart.Row() - aOutRange.aStart.Row();
 698         nFirstDataCol = aResRange.aStart.Col() - aOutRange.aStart.Col();
 699     }
 700
 701     pPivotStrm->write("<")->writeId(XML_location);
 702     rStrm.WriteAttributes(XML_ref,
 703         XclXmlUtils::ToOString(aOutRange),
 704         XML_firstHeaderRow, OString::number(nFirstHeaderRow).getStr(),
 705         XML_firstDataRow, OString::number(nFirstDataRow).getStr(),
 706         XML_firstDataCol, OString::number(nFirstDataCol).getStr(),
 707         FSEND);
 708
 709     if (!aPageFields.empty())
 710     {
 711         rStrm.WriteAttributes(XML_rowPageCount, OString::number(static_cast<long>(aPageFields.size())).getStr(), FSEND);
 712         rStrm.WriteAttributes(XML_colPageCount, OString::number(1).getStr(), FSEND);
 713     }
 714
 715     pPivotStrm->write("/>");
 716
 717     // <pivotFields> - It must contain all fields in the pivot cache even if
 718     // only some of them are used in the pivot table.  The order must be as
 719     // they appear in the cache.
 720
 721     pPivotStrm->startElement(XML_pivotFields,
 722         XML_count, OString::number(static_cast<long>(aCachedDims.size())).getStr(),
 723         FSEND);
 724
 725     for (size_t i = 0; i < nFieldCount; ++i)
 726     {
 727         const ScDPSaveDimension* pDim = aCachedDims[i];
 728         if (!pDim)
 729         {
 730             pPivotStrm->singleElement(XML_pivotField,
 731                 XML_showAll, ToPsz10(false),
 732                 XML_compact, ToPsz10(false),
 733                 FSEND);
 734             continue;
 735         }
 736
 737         bool bDimInTabularMode = false;
 738         if(pDim->GetLayoutInfo())
 739             bDimInTabularMode = (pDim->GetLayoutInfo()->LayoutMode == sheet::DataPilotFieldLayoutMode::TABULAR_LAYOUT);
 740
 741         sheet::DataPilotFieldOrientation eOrient = pDim->GetOrientation();
 742
 743         if (eOrient == sheet::DataPilotFieldOrientation_HIDDEN)
 744         {
 745             if(bDimInTabularMode)
 746             {
 747                 pPivotStrm->singleElement(XML_pivotField,
 748                     XML_showAll, ToPsz10(false),
 749                     XML_compact, ToPsz10(false),
 750                     XML_outline, ToPsz10(false),
 751                     FSEND);
 752             }
 753             else
 754             {
 755                 pPivotStrm->singleElement(XML_pivotField,
 756                     XML_showAll, ToPsz10(false),
 757                     XML_compact, ToPsz10(false),
 758                     FSEND);
 759             }
 760             continue;
 761         }
 762
 763         if (eOrient == sheet::DataPilotFieldOrientation_DATA)
 764         {
 765             if(bDimInTabularMode)
 766             {
 767                 pPivotStrm->singleElement(XML_pivotField,
 768                     XML_dataField, ToPsz10(true),
 769                     XML_showAll, ToPsz10(false),
 770                     XML_compact, ToPsz10(false),
 771                     XML_outline, ToPsz10(false),
 772                     FSEND);
 773             }
 774             else
 775             {
 776                 pPivotStrm->singleElement(XML_pivotField,
 777                     XML_dataField, ToPsz10(true),
 778                     XML_showAll, ToPsz10(false),
 779                     XML_compact, ToPsz10(false),
 780                     FSEND);
 781             }
 782             continue;
 783         }
 784
 785         // Dump field items.
 786         std::vector<ScDPLabelData::Member> aMembers;
 787         {
 788             // We need to get the members in actual order, getting which requires non-const reference here
 789             auto& dpo = const_cast<ScDPObject&>(rDPObj);
 790             dpo.GetMembers(i, dpo.GetUsedHierarchy(i), aMembers);
 791         }
 792
 793         const ScDPCache::ScDPItemDataVec& rCacheFieldItems = rCache.GetDimMemberValues(i);
 794         // The pair contains the member index in cache and if it is hidden
 795         std::vector< std::pair<size_t, bool> > aMemberSequence;
 796         std::set<size_t> aUsedCachePositions;
 797         for (const auto & rMember : aMembers)
 798         {
 799             auto it = std::find_if(rCacheFieldItems.begin(), rCacheFieldItems.end(),
 800                 [&rDPObj, &pDim, &rMember](const ScDPItemData& rItem) {
 801                     OUString sFormattedName;
 802                     if (rItem.HasStringData() || rItem.IsEmpty())
 803                         sFormattedName = rItem.GetString();
 804                     else
 805                         sFormattedName = const_cast<ScDPObject&>(rDPObj).GetFormattedString(pDim->GetName(), rItem.GetValue());
 806                     return sFormattedName == rMember.maName;
 807                 });
 808             if (it != rCacheFieldItems.end())
 809             {
 810                 size_t nCachePos = static_cast<size_t>(std::distance(rCacheFieldItems.begin(), it));
 811                 auto aInserted = aUsedCachePositions.insert(nCachePos);
 812                 if (aInserted.second)
 813                     aMemberSequence.emplace_back(std::make_pair(nCachePos, !rMember.mbVisible));
 814             }
 815         }
 816         // Now add all remaining cache items as hidden
 817         for (size_t nItem = 0; nItem < rCacheFieldItems.size(); ++nItem)
 818         {
 819             if (aUsedCachePositions.find(nItem) == aUsedCachePositions.end())
 820                 aMemberSequence.emplace_back(nItem, true);
 821         }
 822
 823         auto pAttList = sax_fastparser::FastSerializerHelper::createAttrList();
 824         pAttList->add(XML_axis, toOOXMLAxisType(eOrient));
 825         pAttList->add(XML_showAll, ToPsz10(false));
 826
 827         long nSubTotalCount = pDim->GetSubTotalsCount();
 828         std::vector<OString> aSubtotalSequence;
 829         bool bHasDefaultSubtotal = false;
 830         for (long nSubTotal = 0; nSubTotal < nSubTotalCount; ++nSubTotal)
 831         {
 832             ScGeneralFunction eFunc = pDim->GetSubTotalFunc(nSubTotal);
 833             aSubtotalSequence.push_back(GetSubtotalFuncName(eFunc));
 834             sal_Int32 nAttToken = GetSubtotalAttrToken(eFunc);
 835             if (nAttToken == XML_defaultSubtotal)
 836                 bHasDefaultSubtotal = true;
 837             else if (!pAttList->hasAttribute(nAttToken))
 838                 pAttList->add(nAttToken, ToPsz10(true));
 839         }
 840         // XML_defaultSubtotal is true by default; only write it if it's false
 841         if (!bHasDefaultSubtotal)
 842             pAttList->add(XML_defaultSubtotal, ToPsz10(false));
 843
 844         pAttList->add( XML_compact, ToPsz10(false));
 845         if(bDimInTabularMode)
 846             pAttList->add( XML_outline, ToPsz10(false));
 847         sax_fastparser::XFastAttributeListRef xAttributeList(pAttList);
 848         pPivotStrm->startElement(XML_pivotField, xAttributeList);
 849
 850         pPivotStrm->startElement(XML_items,
 851             XML_count, OString::number(static_cast<long>(aMemberSequence.size() + aSubtotalSequence.size())),
 852             FSEND);
 853
 854         for (const auto & nMember : aMemberSequence)
 855         {
 856             auto pItemAttList = sax_fastparser::FastSerializerHelper::createAttrList();
 857             if (nMember.second)
 858                 pItemAttList->add(XML_h, ToPsz10(true));
 859             pItemAttList->add(XML_x, OString::number(static_cast<long>(nMember.first)));
 860             sax_fastparser::XFastAttributeListRef xItemAttributeList(pItemAttList);
 861             pPivotStrm->singleElement(XML_item, xItemAttributeList);
 862         }
 863
 864         for (const OString& sSubtotal : aSubtotalSequence)
 865         {
 866             pPivotStrm->singleElement(XML_item,
 867                 XML_t, sSubtotal,
 868                 FSEND);
 869         }
 870
 871         pPivotStrm->endElement(XML_items);
 872         pPivotStrm->endElement(XML_pivotField);
 873     }
 874
 875     pPivotStrm->endElement(XML_pivotFields);
 876
 877     // <rowFields>
 878
 879     if (!aRowFields.empty())
 880     {
 881         pPivotStrm->startElement(XML_rowFields,
 882             XML_count, OString::number(static_cast<long>(aRowFields.size())),
 883             FSEND);
 884
 885         for (const auto& rRowField : aRowFields)
 886         {
 887             pPivotStrm->singleElement(XML_field,
 888                 XML_x, OString::number(rRowField),
 889                 FSEND);
 890         }
 891
 892         pPivotStrm->endElement(XML_rowFields);
 893     }
 894
 895     // <rowItems>
 896
 897     // <colFields>
 898
 899     if (!aColFields.empty())
 900     {
 901         pPivotStrm->startElement(XML_colFields,
 902             XML_count, OString::number(static_cast<long>(aColFields.size())),
 903             FSEND);
 904
 905         for (const auto& rColField : aColFields)
 906         {
 907             pPivotStrm->singleElement(XML_field,
 908                 XML_x, OString::number(rColField),
 909                 FSEND);
 910         }
 911
 912         pPivotStrm->endElement(XML_colFields);
 913     }
 914
 915     // <colItems>
 916
 917     // <pageFields>
 918
 919     if (!aPageFields.empty())
 920     {
 921         pPivotStrm->startElement(XML_pageFields,
 922             XML_count, OString::number(static_cast<long>(aPageFields.size())),
 923             FSEND);
 924
 925         for (const auto& rPageField : aPageFields)
 926         {
 927             pPivotStrm->singleElement(XML_pageField,
 928                 XML_fld, OString::number(rPageField),
 929                 XML_hier, OString::number(-1), // TODO : handle this correctly.
 930                 FSEND);
 931         }
 932
 933         pPivotStrm->endElement(XML_pageFields);
 934     }
 935
 936     // <dataFields>
 937
 938     if (!aDataFields.empty())
 939     {
 940         pPivotStrm->startElement(XML_dataFields,
 941             XML_count, OString::number(static_cast<long>(aDataFields.size())),
 942             FSEND);
 943
 944         for (const auto& rDataField : aDataFields)
 945         {
 946             long nDimIdx = rDataField.mnPos;
 947             assert(aCachedDims[nDimIdx]); // the loop above should have screened for NULL's.
 948             const ScDPSaveDimension& rDim = *rDataField.mpDim;
 949             const boost::optional<OUString> & pName = rDim.GetLayoutName();
 950             pPivotStrm->write("<")->writeId(XML_dataField);
 951             if (pName)
 952                 rStrm.WriteAttributes(XML_name, pName->toUtf8(), FSEND);
 953
 954             rStrm.WriteAttributes(XML_fld, OString::number(nDimIdx).getStr(), FSEND);
 955
 956             ScGeneralFunction eFunc = rDim.GetFunction();
 957             const char* pSubtotal = toOOXMLSubtotalType(eFunc);
 958             if (pSubtotal)
 959                 rStrm.WriteAttributes(XML_subtotal, pSubtotal, FSEND);
 960
 961             pPivotStrm->write("/>");
 962         }
 963
 964         pPivotStrm->endElement(XML_dataFields);
 965     }
 966
 967     OUStringBuffer aBuf("../pivotCache/pivotCacheDefinition");
 968     aBuf.append(nCacheId);
 969     aBuf.append(".xml");
 970
 971     rStrm.addRelation(
 972         pPivotStrm->getOutputStream(),
 973         CREATE_OFFICEDOC_RELATION_TYPE("pivotCacheDefinition"),
 974         aBuf.makeStringAndClear());
 975
 976     pPivotStrm->endElement(XML_pivotTableDefinition);
 977 }
 978
 979 void XclExpXmlPivotTables::AppendTable( const ScDPObject* pTable, sal_Int32 nCacheId, sal_Int32 nPivotId )
 980 {
 981     maTables.emplace_back(pTable, nCacheId, nPivotId);
 982 }
 983
 984 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */