Function makePivotTable

lib/doc/pivot-table.js:5–51

(worksheet, model)

// TK(2023-10-10): turn this into a class constructor.

function makePivotTable(worksheet, model) {
  // Example `model`:
  // {
  //   // Source of data: the entire sheet range is taken,
  //   // akin to `worksheet1.getSheetValues()`.
  //   sourceSheet: worksheet1,
  //
  //   // Pivot table fields: values indicate field names;
  //   // they come from the first row in `worksheet1`.
  //   rows: ['A', 'B'],
  //   columns: ['C'],
  //   values: ['E'], // only 1 item possible for now
  //   metric: 'sum', // only 'sum' possible for now
  // }

  validate(worksheet, model);

  const {sourceSheet} = model;
  let {rows, columns, values} = model;

  const cacheFields = makeCacheFields(sourceSheet, [...rows, ...columns]);

  // let {rows, columns, values} use indices instead of names;
  // names can then be accessed via `pivotTable.cacheFields[index].name`.
  // *Note*: Using `reduce` as `Object.fromEntries` requires Node 12+;
  // ExcelJS is >=8.3.0 (as of 2023-10-08).
  const nameToIndex = cacheFields.reduce((result, cacheField, index) => {
    result[cacheField.name] = index;
    return result;
  }, {});
  rows = rows.map(row => nameToIndex[row]);
  columns = columns.map(column => nameToIndex[column]);
  values = values.map(value => nameToIndex[value]);

  // form pivot table object
  return {
    sourceSheet,
    rows,
    columns,
    values,
    metric: 'sum',
    cacheFields,
    // defined in <pivotTableDefinition> of xl/pivotTables/pivotTable1.xml;
    // also used in xl/workbook.xml
    cacheId: '10',
  };
}

function validate(worksheet, model) {
  if (worksheet.workbook.pivotTables.length === 1) {