(New in v4.5.0) Pivot Table #2575

mikez · 2023-10-28T18:45:08Z

mikez
Oct 28, 2023

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 · 2023-11-01T12:49:06Z

Rablet
Nov 1, 2023

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

2 replies

mikez Nov 1, 2023
Author

Your PR handles cases like these, right?

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

Rablet Nov 1, 2023

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 · 2023-11-07T03:59:03Z

vaynevayne
Nov 7, 2023

Will 4.4.1 also include experimental support for nest Columns?

7 replies

mikez Nov 7, 2023
Author

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 Nov 8, 2023

When will 4.4.1 be pushed? So I can try it

mikez Nov 8, 2023
Author

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

vaynevayne Nov 10, 2023

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 Nov 10, 2023
Author

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 · 2024-02-07T07:17:38Z

shubhamgoswamiofficial
Feb 7, 2024

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

5 replies

hung-payerprice Feb 20, 2024

+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 Feb 20, 2024
Author

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 Feb 20, 2024
Maintainer

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 Mar 18, 2024

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!

gogromat Dec 4, 2024

you can install exceljs at specific commit: (npm|yarn|etc.): yarn add https://github.com/exceljs/exceljs#ddab279
add a "postinstall" to package.json "scripts" section: "cd node_modules/exceljs/ && yarn install && yarn run build"

OlegMuzych · 2024-09-24T15:40:24Z

OlegMuzych
Sep 24, 2024

Hello there! What is the difficulty in making pivot table reading support? Why when reading a file .xlsx and its saving are the pivot tables missing? In no library has npm found support for reading and saving linked tables.

0 replies

gogromat · 2024-12-04T22:37:35Z

gogromat
Dec 4, 2024

Does not take columns or values when name contains newline characters (\r\n).
Ex. "Time\r\nWorked".
Removing newline characters produces correct pivot table.

1 reply

mikez Dec 5, 2024
Author

@gogromat Thanks for reporting! Do you see an easy way to fix this in the code? Alas, the maintainer of this repository seems to have gone missing. But maybe he will return eventually.

dsilva01 · 2025-02-06T16:10:25Z

dsilva01
Feb 6, 2025

Hey everyone,

I’ve submitted a PR to add support for the Count metric in PivotTable, which allows users to aggregate data based on the number of occurrences in a given field. Currently, exceljs supports Sum metric, but Count was missing. This update brings it closer to native Excel functionality. #2885

0 replies

(New in v4.5.0) Pivot Table #2575

Uh oh!

Uh oh!

Try it out

Limitations

Feedback

Replies: 6 comments · 15 replies

Uh oh!

Uh oh!

mikez Nov 1, 2023 Author

Uh oh!

Uh oh!

Uh oh!

Uh oh!

mikez Nov 7, 2023 Author

Uh oh!

Uh oh!

mikez Nov 8, 2023 Author

Uh oh!

Uh oh!

Uh oh!

mikez Nov 10, 2023 Author

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

mikez Feb 20, 2024 Author

Uh oh!

Siemienik Feb 20, 2024 Maintainer

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

mikez Dec 5, 2024 Author

Uh oh!

Replies: 6 comments 15 replies

mikez Nov 1, 2023
Author

mikez Nov 7, 2023
Author

mikez Nov 8, 2023
Author

mikez Nov 10, 2023
Author

mikez Feb 20, 2024
Author

Siemienik Feb 20, 2024
Maintainer

mikez Dec 5, 2024
Author