(New in v4.5.0) Pivot Table

[mikez]

Pivot Table support is coming with ExcelJS 4.5.0.
(As of ExcelJS 4.4.1, experimental support is available.)

Try it out

const workbook = new Excel.Workbook();

const worksheet1 = workbook.addWorksheet('Sheet1');
worksheet1.addRows([
  ['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 worksheet2 = workbook.addWorksheet('Sheet2');
worksheet2.addPivotTable({
  // Source data: entire sheet range; header in first row
  sourceSheet: worksheet1,
  // Pivot table fields: via header row in `worksheet1`
  rows: ['A', 'B'],
  columns: ['C'],
  values: ['E'], // Exactly 1 field
  metric: 'sum', // Metric: 'sum' only
});

Note that the names rows, columns, and values are modeled after the Pivot Table Fields dialogue in Excel:

Limitations

The current experimental version has several limitations, including:

• No read support. You can't read xlsx documents with existing pivot tables. Writing is supported.
• One value only with "sum". Put as many rows and columns as you want, but you can only use one "value" item with the sum metric.
• One pivot table per document. Only one pivot table can be added for the entire document.

Feedback

Please try it out and leave feedback of how it goes. Also feel free to request features.

[Rablet]

Excellent timing as I needed this for a project I'm working on.
I submitted a PR for supporting multiple values (as long as custom columns are not used): #2578

[mikez]

Your PR handles cases like these, right?

{…, columns: [], values: ['D', 'E'], metric: 'sum'}

[Rablet]

Exactly! I should have added a sample to make that clearer.

For me I needed this as I have a Pivot Table where I have two different prices that I want to sum up and I am not interested in any additional columns. It works well for that.
I would have added multi value + multi column support but wasn't sure how to debug when that scenario kept breaking.

[vaynevayne]

Will 4.4.1 also include experimental support for nest Columns?

[mikez]

If I understand your linked commit correctly, it merges cells together to create the "appearance" of nested columns.
In Pivot tables (another concept), the columns are by design nested. Try it out! :)

[vaynevayne]

When will 4.4.1 be pushed? So I can try it

[mikez]

You can try it today if you do git clone https://github.com/exceljs/exceljs.

[vaynevayne]

There's no playground, and yarn add https://xxx doesn't work. It's tough
but pivot table api should link https://ant.design/components/table#components-table-demo-grouping-columns

columns[n].children,

[mikez]

node test/test-pivot-table.js yourfilename.xlsx

Have you understood the concept of pivot tables? It's used for data analysis. Grouping columns is another concept.

[shubhamgoswamiofficial]

From where shall i get version 4.4.1? As on npm latest is 4.4.0.
Bit urgent

[hung-payerprice]

+1 I'm also interested in this pivot table functionality. Is there any update on the timeline or ETA of 4.4.1 npm package release? Thank you

[mikez]

FYI @shubhamgoswamiofficial @hung-payerprice @Rablet
I'm not the maintainer of this library. I had implemented Pivot Tables elsewhere and contributed a first version of it to this library for the benefit of us all. The current maintainer (@Siemienik) asked me to post here so you could try it out and leave feedback. To reach the maintainer or contribute to a release, you may try Discord: https://discord.gg/siema

[Siemienik]

Discord or my email consulting@siemienik.com address is good place to catch me with urgent tickets.

I have got plans to release 4.4.1 with current progress soon (in march if everything would match the plan)

Please keep in mind, that's still experimental feature with limitations.

[RaphyPoo]

Hey, I am trying to implement the pivot tables into one of my projects but I see that it has not yet been released. Is there an ETA to when this will come out? I need it for a project that I am currently working on. I really appreciate the work you guys do!