const ExcelJS = require('exceljs');

const createWorkbook = async () => {
    const workbook = new ExcelJS.Workbook();
    workbook.calcProperties.fullCalcOnLoad = true;

    const worksheet1 = workbook.addWorksheet('Sheet1');
    worksheet1.addRows([
        ['Description', 'Initial Cost', 'VAT', 'Total',],
        ['Potatoes', 10, 2, 12],
        ['Potatoes', 10, 2, 12],
        ['Potatoes', 10, 2, 12],
        ['Grapes', 20, 4, 24],
        ['Grapes', 20, 4, 24],
        ['Strawberries', 15, 3, 18],
    ]);

    const worksheet2 = workbook.addWorksheet('Sheet2');
    worksheet2.addPivotTable({
        sourceSheet: worksheet1,
        rows: ['Description'],
        columns: [],
        values: ['Initial Cost', 'VAT', 'Total'],
        metric: 'sum',
    });

    await workbook.xlsx.writeFile("PivotWithMultipleValues.xlsx");
}
createWorkbook();

 worksheet2.addPivotTable({
        sourceSheet: worksheet1,
        rows: ['Description'],
        columns: ['Description', 'Initial Cost'],
        values: ['Initial Cost', 'VAT', 'Total'],
        metric: 'sum',
    });

Warning: Pivot Table support is experimental. 
Please leave feedback at https://github.com/exceljs/exceljs/discussions/2575
/Users/robin/dev/exceljs/lib/doc/pivot-table.js:81
    throw new Error(
          ^

Error: It is currently not possible to have multiple values when columns are specified. Please either supply an empty array for columns or a single value.
    at validate (/Users/robin/dev/exceljs/lib/doc/pivot-table.js:81:11)
    at makePivotTable (/Users/robin/dev/exceljs/lib/doc/pivot-table.js:20:3)
    at Worksheet.addPivotTable (/Users/robin/dev/exceljs/lib/doc/worksheet.js:821:24)
    at createWorkbook (/Users/robin/dev/nodeexceldemo/index.js:21:16)
    at Object.<anonymous> (/Users/robin/dev/nodeexceldemo/index.js:31:1)
    at Module._compile (node:internal/modules/cjs/loader:1101:14)
    at Object.Module._extensions..js (node:internal/modules/cjs/loader:1153:10)
    at Module.load (node:internal/modules/cjs/loader:981:32)
    at Function.Module._load (node:internal/modules/cjs/loader:822:12)
    at Function.executeUserEntryPoint [as runMain] (node:internal/modules/run_main:81:12)
    at node:internal/main/run_main_module:17:47

const Excel = require('exceljs');

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

workbook.xlsx.writeFile("RegressionTest.xlsx");