用java在excel表格中创建数据透视表

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>

Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("Sheet1");

// 假设有一些数据需要填充到表格中
List<String> headers = Arrays.asList("Name", "Age", "Gender");
List<List<Object>> data = Arrays.asList(
    Arrays.asList("John", 25, "Male"),
    Arrays.asList("Alice", 30, "Female"),
    Arrays.asList("Bob", 35, "Male")
);

// 创建表头行
Row headerRow = sheet.createRow(0);
for (int i = 0; i < headers.size(); i++) {
    Cell cell = headerRow.createCell(i);
    cell.setCellValue(headers.get(i));
}

// 填充数据行
for (int i = 0; i < data.size(); i++) {
    Row dataRow = sheet.createRow(i + 1);
    List<Object> rowData = data.get(i);
    for (int j = 0; j < rowData.size(); j++) {
        Cell cell = dataRow.createCell(j);
        cell.setCellValue(rowData.get(j).toString());
    }
}

// 定义数据透视表的起始位置和大小
CellReference topLeft = new CellReference("E1");
CellReference bottomRight = new CellReference("H10");

// 创建数据透视表
PivotTable pivotTable = sheet.createPivotTable(
    new AreaReference(topLeft, bottomRight),
    new CellReference("A1")
);

// 设置数据透视表的行、列和值字段
pivotTable.addRowLabel(0); // Name
pivotTable.addColumnLabel(DataConsolidateFunction.AVERAGE, 1); // Age
pivotTable.addColumnLabel(DataConsolidateFunction.COUNT, 2); // Gender

// 设置数据透视表的样式
pivotTable.getTableStyleInfo().setShowColumnStripes(false);
pivotTable.getTableStyleInfo().setShowRowStripes(true);

FileOutputStream fileOut = new FileOutputStream("path/to/output.xlsx");
workbook.write(fileOut);
fileOut.close();
workbook.close();