gistfile1.txt

// --------------------------------------------------
// This enables the generation of a XLSX pivot table
// with several restrictions
//
// Last updated: 2023-07-18-13:18
// --------------------------------------------------



// -------------------------------------------------------------------------------------
// monkey-patched ExcelJS libary implementation below (don't modify)
// -------------------------------------------------------------------------------------

function monkeyPatchPivotTable(xlsx) {
  const ContentTypesXform = require(`${EXCELJS_LIB}/xlsx/xform/core/content-types-xform`);
  const RelationshipsXform = require(`${EXCELJS_LIB}/xlsx/xform/core/relationships-xform`);
  const WorkbookXform = require(`${EXCELJS_LIB}/xlsx/xform/book/workbook-xform`);
  const XLSX = require(`${EXCELJS_LIB}/xlsx/xlsx`);
  const XmlStream = require(`${EXCELJS_LIB}/utils/xml-stream`);
  const ZipStream = require(`${EXCELJS_LIB}/utils/zip-stream`);

  // TK(2023-07-18): belongs into here 'xlsx/rel-type.js'
  const REL_TYPE_PIVOT_CACHE_DEFINITION =
    'http://schemas.openxmlformats.org/officeDocument/2006/relationships/pivotCacheDefinition';
  const REL_TYPE_PIVOT_CACHE_RECORDS =
    'http://schemas.openxmlformats.org/officeDocument/2006/relationships/pivotCacheRecords';
  const REL_TYPE_PIVOT_TABLE =
    'http://schemas.openxmlformats.org/officeDocument/2006/relationships/pivotTable';

  xlsx.write = async function (stream, options) {
    options = options || {};
    const { model } = this.workbook;
    const zip = new ZipStream.ZipWriter(options.zip);
    zip.pipe(stream);

    this.prepareModel(model, options);
    // - - - snip start - - -
    model.pivotTables = this.workbook.pivotTables;
    // - - - snip end - - -

    // render
    await this.addContentTypes(zip, model);
    await this.addOfficeRels(zip, model);
    await this.addWorkbookRels(zip, model);
    await this.addWorksheets(zip, model);
    await this.addSharedStrings(zip, model); // always after worksheets
    await this.addDrawings(zip, model);
    await this.addTables(zip, model);
    // - - - snip start - - -
    await this.addWorksheetsRels(zip, model);
    await this.addPivotCacheDefinitions(zip, model);
    await this.addPivotCacheRecords(zip, model);
    await this.addPivotCacheRels(zip, model);
    await this.addPivotTables(zip, model);
    await this.addPivotTablesRels(zip, model);
    // - - - snip end - - -
    // TK styles.xml: add `cellXfs` styles 'pivotButton' etc.
    debugger;
    await this.addStyles(zip, model);
    await Promise.all([this.addThemes(zip, model), this.addStyles(zip, model)]);
    await this.addMedia(zip, model);
    await Promise.all([this.addApp(zip, model), this.addCore(zip, model)]);
    await this.addWorkbook(zip, model); // **** TK workbook.xml: pivotCaches
    return this._finalize(zip);
  };

  xlsx.addContentTypes = async function (zip, model) {
    const xform = new ContentTypesXform();
    monkeyPatchContentTypesXform(xform);
    const xml = xform.toXml(model);
    zip.append(xml, { name: '[Content_Types].xml' });
  };

  xlsx.addWorkbookRels = async function (zip, model) {
    let count = 1;
    // - - - snip start - - -
    model.pivotTables.rId = `rId${count++}`;
    // - - - snip end - - -

    const relationships = [
      // - - - snip start - - -
      {
        Id: model.pivotTables.rId,
        Type: REL_TYPE_PIVOT_CACHE_DEFINITION,
        Target: 'pivotCache/pivotCacheDefinition1.xml',
      },
      // - - - snip end - - -
      { Id: `rId${count++}`, Type: XLSX.RelType.Styles, Target: 'styles.xml' },
      { Id: `rId${count++}`, Type: XLSX.RelType.Theme, Target: 'theme/theme1.xml' },
    ];
    if (model.sharedStrings.count) {
      relationships.push({
        Id: `rId${count++}`,
        Type: XLSX.RelType.SharedStrings,
        Target: 'sharedStrings.xml',
      });
    }
    model.worksheets.forEach(worksheet => {
      worksheet.rId = `rId${count++}`;
      relationships.push({
        Id: worksheet.rId,
        Type: XLSX.RelType.Worksheet,
        Target: `worksheets/sheet${worksheet.id}.xml`,
      });
    });
    const xform = new RelationshipsXform();
    const xml = xform.toXml(relationships);
    zip.append(xml, { name: 'xl/_rels/workbook.xml.rels' });
  };

  xlsx.addWorksheetsRels = async function (zip, model) {
    const relationships = [
      {
        Id: `rId1`,
        Type: REL_TYPE_PIVOT_TABLE,
        Target: '../pivotTables/pivotTable1.xml',
      },
    ];
    const xform = new RelationshipsXform();
    const xml = xform.toXml(relationships);
    // TK(2023-07-17): instead of `sheet1.xml`, find a more general solution
    zip.append(xml, { name: 'xl/worksheets/_rels/sheet1.xml.rels' });
  };

  xlsx.addPivotCacheDefinitions = async function (zip, model) {
    const sourceSheet = model.pivotTables.sourceSheet;
    const cacheFields = model.pivotTables.cacheFields;

    // TK(2023-07-18): using the proposed String.dedent would have been
    // useful here; akin to Python:
    // https://github.com/tc39/proposal-string-dedent
    const xml = `<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<pivotCacheDefinition
  xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
  xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
  xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
  mc:Ignorable="xr"
  xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision"
  r:id="rId1"
  refreshedBy="Author"
  refreshedDate="45125.026046874998"
  createdVersion="8"
  refreshedVersion="8"
  minRefreshableVersion="3"
  recordCount="${cacheFields.length + 1}"
>
  <cacheSource type="worksheet">
    <worksheetSource
      ref="${sourceSheet.dimensions.shortRange}"
      sheet="${sourceSheet.name}"
    />
  </cacheSource>
  <cacheFields count="${cacheFields.length}">
    ${cacheFields
      .map(cacheField => new PivotTableCacheDefinitionField(cacheField).render())
      .join('\n    ')}
  </cacheFields>
</pivotCacheDefinition>
`;

    zip.append(xml, { name: 'xl/pivotCache/pivotCacheDefinition1.xml' });
  };

  xlsx.addPivotCacheRecords = async function (zip, model) {
    const sourceData = model.pivotTables.sourceData;
    const cacheFields = model.pivotTables.cacheFields;

    // TK(2023-07-18): use String.dedent for readability; see earlier comment
    const xml = `<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<pivotCacheRecords
  xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
  xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
  xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
  mc:Ignorable="xr"
  xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision"
  count="${sourceData.length - 1}"
>${renderTable(sourceData)}
</pivotCacheRecords>
`;

    zip.append(xml, { name: 'xl/pivotCache/pivotCacheRecords1.xml' });

    // Helpers

    function renderTable(sourceData) {
      const body = sourceData.slice(1);
      return body.map(row => [...renderRowLines(row)].join('')).join('');
    }

    function* renderRowLines(row) {
      yield '\n  <r>';
      for (const [index, cellValue] of row.entries()) {
        yield '\n    ';
        yield renderCell(cellValue, cacheFields[index].sharedItems);
      }
      yield '\n  </r>';
    }

    function renderCell(value, sharedItems) {
      // integer
      if (Number.isFinite(value)) {
        return `<n v="${value}" />`;
      }
      // string
      else {
        const sharedItemsIndex = sharedItems.indexOf(value);
        if (sharedItemsIndex < 0) {
          throw new Error(
            `${JSON.stringify(value)} not in sharedItems ${JSON.stringify(sharedItems)}`
          );
        }
        return `<x v="${sharedItemsIndex}" />`;
      }
    }
  };

  xlsx.addPivotCacheRels = async function (zip, model) {
    const relationships = [
      {
        Id: `rId1`,
        Type: REL_TYPE_PIVOT_CACHE_RECORDS,
        Target: 'pivotCacheRecords1.xml',
      },
    ];
    const xform = new RelationshipsXform();
    const xml = xform.toXml(relationships);
    zip.append(xml, { name: 'xl/pivotCache/_rels/pivotCacheDefinition1.xml.rels' });
  };

  xlsx.addPivotTables = async function (zip, model) {
    // TK(2023-07-18): use String.dedent for readability; see earlier comment

    const { cacheFields, rows, columns, values, metric } = model.pivotTables;

    // Examples
    // --------
    // rows: [0, 1], // only 2 items possible for now
    // columns: [2], // only 1 item possible for now
    // values: [4], // only 1 item possible for now
    // metric: 'sum', // only 'sum' possible for now

    // How many types of rows[0] items are there? (e.g. 'a1', 'a2', 'a3' === 3)
    const row0Count = cacheFields[rows[0]].sharedItems.length;
    // How many types of rows[1] items are there? (e.g. 'b1', 'b2' === 2)
    const row1Count = cacheFields[rows[1]].sharedItems.length;

    // total rows (including subtotals and grand total)
    const rowItemsCount = row0Count * (row1Count + 1) + 1;

    const xml = `<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<pivotTableDefinition
  xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
  xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
  mc:Ignorable="xr"
  xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision"
  xr:uid="{267EE50F-B116-784D-8DC2-BA77DE3F4F4A}"
  name="PivotTable2"
  cacheId="10"
  applyNumberFormats="0"
  applyBorderFormats="0"
  applyFontFormats="0"
  applyPatternFormats="0"
  applyAlignmentFormats="0"
  applyWidthHeightFormats="1"
  dataCaption="Values"
  updatedVersion="8"
  minRefreshableVersion="3"
  useAutoFormatting="1"
  itemPrintTitles="1"
  createdVersion="8"
  indent="0"
  outline="1"
  outlineData="1"
  multipleFieldFilters="0"
>
  <location ref="A3:E15" firstHeaderRow="1" firstDataRow="2" firstDataCol="1" />
  <pivotFields count="${cacheFields.length}">
    ${renderPivotFields(model.pivotTables)}
  </pivotFields>
  <rowFields count="${rows.length}">
    ${rows.map(rowIndex => `<field x="${rowIndex}" />`).join('\n    ')}
  </rowFields>
  <rowItems count="${rowItemsCount}">
    ${[...range(0, row0Count)]
      .map(
        groupIndex => `
          <i><x v="${groupIndex}" /></i>
          ${[...range(0, row1Count)]
            .map(subGroupIndex => `<i><x v="${subGroupIndex}" /></i>`)
            .join('')}
        `
      )
      .join('\n    ')}
    <i t="grand"><x /></i>
  </rowItems>
  <colFields count="${columns.length}">
    ${columns.map(columnIndex => `<field x="${columnIndex}" />`).join('\n    ')}
  </colFields>
  <colItems count="${columns.length + 1}">
    ${[...range(0, columns.length)]
      .map(index => `<i><x v="${index}" /></i>`)
      .join('\n    ')}
    <i t="grand"><x /></i>
  </colItems>
  <dataFields count="${values.length}">
    <dataField
      name="Sum of ${cacheFields[values[0]].name}"
      fld="${values[0]}"
      baseField="0"
      baseItem="0"
    />
  </dataFields>
  <pivotTableStyleInfo name="PivotStyleLight16" showRowHeaders="1" showColHeaders="1" showRowStripes="0" showColStripes="0" showLastColumn="1" />
  <extLst>
    <ext uri="{962EF5D1-5CA2-4c93-8EF4-DBF5C05439D2}" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main">
      <x14:pivotTableDefinition hideValuesRow="1" xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main" />
    </ext>
    <ext uri="{747A6164-185A-40DC-8AA5-F01512510D54}" xmlns:xpdl="http://schemas.microsoft.com/office/spreadsheetml/2016/pivotdefaultlayout">
      <xpdl:pivotTableDefinition16 />
    </ext>
  </extLst>
</pivotTableDefinition>
`;
    zip.append(xml, { name: 'xl/pivotTables/pivotTable1.xml' });

    // Helpers

    function renderPivotFields(pivotTable) {
      return pivotTable.cacheFields
        .map((cacheField, fieldIndex) => {
          const fieldType =
            pivotTable.rows.indexOf(fieldIndex) >= 0
              ? 'row'
              : pivotTable.columns.indexOf(fieldIndex) >= 0
              ? 'column'
              : pivotTable.values.indexOf(fieldIndex) >= 0
              ? 'value'
              : null;
          return renderPivotField(fieldType, cacheField.sharedItems);
        })
        .join('');
    }

    function renderPivotField(fieldType, sharedItems) {
      // fieldType: 'row', 'column', 'value', null
      if (fieldType === 'row' || fieldType === 'column') {
        const axis = fieldType === 'row' ? 'axisRow' : 'axisCol';
        return `
          <pivotField axis="${axis}" showAll="0">
            <items count="${sharedItems.length + 1}">
              ${[...range(0, sharedItems.length)]
                .map(index => `<item x="${index}" />`)
                .join('\n              ')}
              <item t="default" />
            </items>
          </pivotField>
        `;
      } else if (fieldType === 'value') {
        return '<pivotField dataField="1" showAll="0" />';
      } else {
        return '<pivotField showAll="0" />';
      }
    }
  };

  xlsx.addPivotTablesRels = async function (zip, model) {
    const relationships = [
      {
        Id: `rId1`,
        Type: REL_TYPE_PIVOT_CACHE_DEFINITION,
        Target: '../pivotCache/pivotCacheDefinition1.xml',
      },
    ];
    const xform = new RelationshipsXform();
    const xml = xform.toXml(relationships);
    zip.append(xml, { name: 'xl/pivotTables/_rels/pivotTable1.xml.rels' });
  };

  // xlsx.addStyles = async function (zip, model) {};

  xlsx.addWorkbook = async function (zip, model) {
    const xform = new WorkbookXform();
    monkeyPatchWorkbookXform(xform);
    zip.append(xform.toXml(model), { name: 'xl/workbook.xml' });
  };
}

function monkeyPatchContentTypesXform(xform) {
  const ContentTypesXform = require(`${EXCELJS_LIB}/xlsx/xform/core/content-types-xform`);
  const XmlStream = require(`${EXCELJS_LIB}/utils/xml-stream`);

  xform.render = function (xmlStream, model) {
    xmlStream.openXml(XmlStream.StdDocAttributes);

    xmlStream.openNode('Types', ContentTypesXform.PROPERTY_ATTRIBUTES);

    const mediaHash = {};
    (model.media || []).forEach(medium => {
      if (medium.type === 'image') {
        const imageType = medium.extension;
        if (!mediaHash[imageType]) {
          mediaHash[imageType] = true;
          xmlStream.leafNode('Default', {
            Extension: imageType,
            ContentType: `image/${imageType}`,
          });
        }
      }
    });

    xmlStream.leafNode('Default', {
      Extension: 'rels',
      ContentType: 'application/vnd.openxmlformats-package.relationships+xml',
    });
    xmlStream.leafNode('Default', { Extension: 'xml', ContentType: 'application/xml' });

    xmlStream.leafNode('Override', {
      PartName: '/xl/workbook.xml',
      ContentType:
        'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml',
    });

    model.worksheets.forEach(worksheet => {
      const name = `/xl/worksheets/sheet${worksheet.id}.xml`;
      xmlStream.leafNode('Override', {
        PartName: name,
        ContentType:
          'application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml',
      });
    });

    // - - - snip start - - -
    xmlStream.leafNode('Override', {
      PartName: '/xl/pivotCache/pivotCacheDefinition1.xml',
      ContentType:
        'application/vnd.openxmlformats-officedocument.spreadsheetml.pivotCacheDefinition+xml',
    });
    xmlStream.leafNode('Override', {
      PartName: '/xl/pivotCache/pivotCacheRecords1.xml',
      ContentType:
        'application/vnd.openxmlformats-officedocument.spreadsheetml.pivotCacheRecords+xml',
    });
    xmlStream.leafNode('Override', {
      PartName: '/xl/pivotTables/pivotTable1.xml',
      ContentType:
        'application/vnd.openxmlformats-officedocument.spreadsheetml.pivotTable+xml',
    });
    // - - - snip end - - -

    xmlStream.leafNode('Override', {
      PartName: '/xl/theme/theme1.xml',
      ContentType: 'application/vnd.openxmlformats-officedocument.theme+xml',
    });
    xmlStream.leafNode('Override', {
      PartName: '/xl/styles.xml',
      ContentType:
        'application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml',
    });

    const hasSharedStrings = model.sharedStrings && model.sharedStrings.count;
    if (hasSharedStrings) {
      xmlStream.leafNode('Override', {
        PartName: '/xl/sharedStrings.xml',
        ContentType:
          'application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml',
      });
    }

    if (model.tables) {
      model.tables.forEach(table => {
        xmlStream.leafNode('Override', {
          PartName: `/xl/tables/${table.target}`,
          ContentType:
            'application/vnd.openxmlformats-officedocument.spreadsheetml.table+xml',
        });
      });
    }

    if (model.drawings) {
      model.drawings.forEach(drawing => {
        xmlStream.leafNode('Override', {
          PartName: `/xl/drawings/${drawing.name}.xml`,
          ContentType: 'application/vnd.openxmlformats-officedocument.drawing+xml',
        });
      });
    }

    if (model.commentRefs) {
      xmlStream.leafNode('Default', {
        Extension: 'vml',
        ContentType: 'application/vnd.openxmlformats-officedocument.vmlDrawing',
      });

      model.commentRefs.forEach(({ commentName }) => {
        xmlStream.leafNode('Override', {
          PartName: `/xl/${commentName}.xml`,
          ContentType:
            'application/vnd.openxmlformats-officedocument.spreadsheetml.comments+xml',
        });
      });
    }

    xmlStream.leafNode('Override', {
      PartName: '/docProps/core.xml',
      ContentType: 'application/vnd.openxmlformats-package.core-properties+xml',
    });
    xmlStream.leafNode('Override', {
      PartName: '/docProps/app.xml',
      ContentType:
        'application/vnd.openxmlformats-officedocument.extended-properties+xml',
    });

    xmlStream.closeNode();
  };
}

function monkeyPatchWorkbookXform(xform) {
  const WorkbookXform = require(`${EXCELJS_LIB}/xlsx/xform/book/workbook-xform`);
  const XmlStream = require(`${EXCELJS_LIB}/utils/xml-stream`);

  xform.render = function (xmlStream, model) {
    xmlStream.openXml(XmlStream.StdDocAttributes);
    xmlStream.openNode('workbook', WorkbookXform.WORKBOOK_ATTRIBUTES);

    this.map.fileVersion.render(xmlStream);
    this.map.workbookPr.render(xmlStream, model.properties);
    this.map.bookViews.render(xmlStream, model.views);
    this.map.sheets.render(xmlStream, model.sheets);
    this.map.definedNames.render(xmlStream, model.definedNames);
    this.map.calcPr.render(xmlStream, model.calcProperties);

    // - - - snip start - - -
    xmlStream.openNode('pivotCaches');
    xmlStream.leafNode('pivotCache', {
      'cacheId': 10, // defined in <pivotTableDefinition>
      'r:id': model.pivotTables.rId,
    });
    xmlStream.closeNode();
    // - - - snip end - - -

    xmlStream.closeNode();
  };
}

// helper classes and functions

class PivotTableCacheDefinitionField {
  constructor({ name, sharedItems }) {
    // string type
    //
    // {
    //   'name': 'A',
    //   'sharedItems': ['a1', 'a2', 'a3']
    // }
    //
    // or
    //
    // integer type
    //
    // {
    //   'name': 'D',
    //   'sharedItems': null
    // }
    this.name = name;
    this.sharedItems = sharedItems;
  }

  render() {
    // integer types
    if (this.sharedItems === null) {
      // TK(2023-07-18): left out attributes... minValue="5" maxValue="45"
      return `<cacheField name="${this.name}" numFmtId="0">
      <sharedItems containsSemiMixedTypes="0" containsString="0" containsNumber="1" containsInteger="1" />
    </cacheField>`;
    }

    // string types
    return `<cacheField name="${this.name}" numFmtId="0">
      <sharedItems count="${this.sharedItems.length}">
        ${this.sharedItems.map(item => `<s v="${item}" />`).join('')}
      </sharedItems>
    </cacheField>`;
  }
}

function* range(start, stop, step = 1) {
  const compare = step > 0 ? (a, b) => a < b : (a, b) => a > b;
  for (let value = start; compare(value, stop); value += step) {
    yield value;
  }
}


// --------------------------------------------------
// Example use
// --------------------------------------------------

function main() {
  const ExcelJS = require('exceljs');

  const data = [
    ['A', ' B', 'C', 'D', 'E'],
    ['a1', 'b1', 'c1', 4, 5],
    ['a1', 'b2', 'c1', 4, 5],
    ['a2', 'b1', 'c2', 14, 24],
    ['a2', 'b2', 'c2', 24, 35],
    ['a3', 'b1', 'c3', 34, 45],
    ['a3', 'b2', 'c3', 44, 45],
  ];

  const workbook = new ExcelJS.Workbook();

  // left empty for now
  const worksheet2 = workbook.addWorksheet('Sheet2');

  const worksheet1 = workbook.addWorksheet('Sheet1');
  worksheet1.addRows(data);

  // add pivot table
  workbook.pivotTables = {
    sourceData: data,
    sourceSheet: worksheet1,
    // TK(2023-07-18): compute the below instead of having to generate manually
    cacheFields: [
      { name: 'A', sharedItems: ['a1', 'a2', 'a3'] },
      { name: 'B', sharedItems: ['b1', 'b2'] },
      { name: 'C', sharedItems: ['c1', 'c2', 'c3'] },
      { name: 'D', sharedItems: null },
      { name: 'E', sharedItems: null },
    ],
    // for now, all pivot table "rows" and "columns" need to be strings;
    // all others need to be integers;
    // number indicates column index (0 = A, 1 = B, ...)
    rows: [0, 1], // only 2 items possible for now
    columns: [2], // only 1 item possible for now
    values: [4], // only 1 item possible for now
    metric: 'sum', // only 'sum' possible for now
  };

  let filepath = 'example.xlsx'
  monkeyPatchXlsx(workbook.xlsx);
  workbook.xlsx.writeFile(filepath).then(() => console.log('done'));
}


// Note: insert path to exceljs source files here; make sure to also `npm install`
// within exceljs to get all required third-party packages such as `saxes`.
const EXCELJS_LIB = '../exceljs/lib';

main();