Can i add pivot table using exceljs

[redleifz]

I would like to add a pivot table into .xlsx file that i create using result for query datable , is it possible with this library

[zurmokeeper]

@redleifz #261 #1392 From the discussion and source code of these two issues, it should not yet support the pivot table function, but you also try it yourself to confirm. Or find out if there is someone else folk out of the project.

[pietersv]

It is possible to add a simple pivot table using ExcelJS. See this Gist for a complete example
https://gist.github.com/pietersv/6b43e0ee677530cbb72033e035ccd17c

The approach here works by modifying methods of the workbook.xlsx object to write the pivot table data.
Most of the complexity of pivot tables is all the internally cached representation of the table data. This method sidesteps all of that by setting the pivot table to refresh every time the workbook is opened.

Example:

  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
  };

  monkeyPatchPivotTable(workbook.xlsx); // modify xlsx object to write pivot table
  workbook.xlsx.writeFile(filepath).then(() => console.log('done'));

[zurmokeeper]

@pietersv That's fantastic, why don't you consider mentioning a PR?

[pietersv]

My only concern is that getting to PR requires time to research the general case beyond my immediate project, create unit tests, consider edge cases, and reply to follow-on bug reports and enhancement requests. If someone has the time and interest to take that on, I would be willing to post a bounty of say $1,000 for an accepted PR.

[lopun]

How is this going? It looks really great. Finallyyy

[HaiAlison]

hey mr @pietersv, can you show me how to load the pivot table when readFile?

[HaiAlison]

i want to clone the pivot table to new file from existed file