[F] Summarise with Pivot Table

[iuioiua]

🚀 Feature Proposal

The "Summarise with Pivot Table" functionality for a table.

Motivation

Pivot tables are a great way of summarising data for tables.

Example

Inspired by exceljs/exceljs#2551. Notice the sourceTable property for Worksheet.addPivotTable().

import { Workbook } from "@cj-tech-master/excelts";

const workbook = new Workbook();
const worksheet = workbook.addWorksheet();

const table = worksheet.addTable({
  name: "table",
  ref: "A1",
  headerRow: true,
  columns: [
    { name: "A" },
    { name: "B" },
    { name: "C" },
  ],
  rows: [
    ["a1", "b1", 5],
    ["a1", "b2", 5],
    ["a2", "b1", 24],
    ["a2", "b2", 35],
    ["a3", "b1", 45],
    ["a3", "b2", 45],
  ],
});

const worksheet2 = workbook.addWorksheet("Sheet2");
worksheet2.addPivotTable({
  sourceTable: table,
  rows: ["A"],
  columns: ["B"],
  values: ["C"], // Exactly 1 field
  metric: "sum", // Metric: 'sum' only
});

workbook.xlsx.writeFile("./test.xlsx");

[cjnoname]

Please try this version:
1.6.1

To be honest, we’re not currently using this feature, and I built it without any testing. Please try it out and let me know if you run into any issues.

[iuioiua]

Thank you so much for your work. The file, including the table and worksheets, is being created correctly. But it seems the PivotTable isn't. I've reduced the original snippet of this issue to reproduce the issue. The same configuration works correctly when I create the PivotTable in Excel.

These are the results:

Notice:

1. The PivotTable is not displaying correctly.
2. The PivotTable fields are correct.
3. The error banner.

I click "Refresh" on the PivotTable and get the following error:

I click "OK" and get the following error, twice, and nothing else changes except the error banner disappears:

[cjnoname]

@iuioiua Thanks for reporting the issue. Could you please upload the broken file?

[iuioiua]

test.xlsx

Can you please reopen this issue?

[cjnoname]

Please try 2.0.1 and let me know if there's any issues

[iuioiua]

Still having issues. This is the file that gets generated:

test.xlsx

On top of what I previously reported, I get this when opening with Excel:

I click "Yes" then get the following error popup:

The following file is generated:

Repair Result to test1.xml

[cjnoname]

Please try 3.0.0

[iuioiua]

The original snippet now works! Great work! However, my exact use case, which is a little more complicated doesn't work. I'll reduce my use case to be minimally reproducible and open a new issue. Thank you!