Apache POI更新PPT饼图：保留样式及首个数据更新问题求助

尝试更新带有自定义颜色和样式的PPT饼图，期望更新数据的同时保留原有格式与样式，但目前存在以下问题：

• 更新数据后饼图丢失原有样式，新增切片与原有切片样式不一致
• 无法更新第一个数据值，旧的第一行数据未被移除，希望仅显示新数据（从Kate行开始）
• 关联的工作表行丢失原有格式

原实现代码：

static void updatePieChart(XSLFChart chart, List<User> users) throws Exception {

    XSSFWorkbook workbook = chart.getWorkbook();
    XSSFSheet sheet = workbook.getSheetAt(0);
    users.stream()
            .forEach(value -> addMonthDataToChart(sheet, chart, user.getName(), user.getSavings()));
}

static void addDataToChart(XSSFSheet sheet, XSLFChart chart, String month, Double value) {
    Row row;
    Cell cell;

    List<XDDFChartData> chartDataList = chart.getChartSeries();
    XDDFChartData chartData = chartDataList.get(0);

    List<XDDFChartData.Series> seriesList = chartData.getSeries();
    for (XDDFChartData.Series series : seriesList) {
        XDDFDataSource categoryData = series.getCategoryData();
        AreaReference catReference = new AreaReference(categoryData.getDataRangeReference(), SpreadsheetVersion.EXCEL2007);
        CellReference firstCatCell = catReference.getFirstCell();
        CellReference lastCatCell = catReference.getLastCell();
        if (firstCatCell.getCol() == lastCatCell.getCol()) {
            int col = firstCatCell.getCol();
            int lastRow = lastCatCell.getRow();

            row = sheet.getRow(lastRow + 1);
            if (row == null)
                row = sheet.createRow(lastRow + 1);

            cell = row.getCell(col);
            if (cell == null)
                cell = row.createCell(col);
            cell.setCellValue(month);

            XDDFDataSource<String> category = XDDFDataSourcesFactory.fromStringCellRange(
                    sheet,
                    new CellRangeAddress(firstCatCell.getRow(), lastRow+1, col, col));

            XDDFNumericalDataSource valuesData = series.getValuesData();
            AreaReference numReference = new AreaReference(valuesData.getDataRangeReference(), SpreadsheetVersion.EXCEL2007);
            CellReference firstNumCell = numReference.getFirstCell();
            CellReference lastNumCell = numReference.getLastCell();
            if (lastNumCell.getRow() == lastRow && firstNumCell.getCol() == lastNumCell.getCol()) {
                col = firstNumCell.getCol();
                row = sheet.getRow(lastRow+1);

                if (row == null)
                    row = sheet.createRow(lastRow + 1);

                cell = row.getCell(col);
                if (cell == null)
                    cell = row.createCell(col);

                cell.setCellValue(value);

                XDDFNumericalDataSource<Double> values = XDDFDataSourcesFactory.fromNumericCellRange(
                        sheet,
                        new CellRangeAddress(firstNumCell.getRow(), lastRow + 1, col, col));

                series.replaceData(category, values);
            }
        }
    }
    chart.plot(chartData);
}

核心问题分析 #

1. 原代码采用追加数据逻辑，而非替换原有数据，导致旧数据残留
2. series.replaceData()方法会重置图表样式，未保留原有切片的自定义格式
3. 未复制原有单元格样式，导致工作表格式丢失

修正后的代码 #

static void updatePieChart(XSLFChart chart, List<User> users) throws Exception {
    XSSFWorkbook workbook = chart.getWorkbook();
    XSSFSheet sheet = workbook.getSheetAt(0);
    XDDFChartData chartData = chart.getChartSeries().get(0);
    XDDFChartData.Series series = chartData.getSeries().get(0);

    // 获取原有数据范围参数
    XDDFDataSource categoryData = series.getCategoryData();
    AreaReference catArea = new AreaReference(categoryData.getDataRangeReference(), SpreadsheetVersion.EXCEL2007);
    int startRow = catArea.getFirstCell().getRow();
    int catCol = catArea.getFirstCell().getCol();
    int valCol = series.getValuesData().getDataRangeReference().getFirstCell().getCol();

    // 清空原有数据行（移除旧数据）
    int oldEndRow = catArea.getLastCell().getRow();
    for (int i = startRow; i <= oldEndRow; i++) {
        Row row = sheet.getRow(i);
        if (row != null) sheet.removeRow(row);
    }

    // 写入新数据并复制原有格式（假设表头行在startRow-1，可根据实际调整）
    CellStyle catCellStyle = sheet.getRow(startRow - 1).getCell(catCol).getCellStyle();
    CellStyle valCellStyle = sheet.getRow(startRow - 1).getCell(valCol).getCellStyle();
    int currentRow = startRow;

    for (User user : users) {
        // 处理分类单元格（姓名）
        Row row = sheet.getRow(currentRow) != null ? sheet.getRow(currentRow) : sheet.createRow(currentRow);
        Cell catCell = row.getCell(catCol) != null ? row.getCell(catCol) : row.createCell(catCol);
        catCell.setCellValue(user.getName());
        catCell.setCellStyle(catCellStyle);

        // 处理数值单元格（储蓄）
        Cell valCell = row.getCell(valCol) != null ? row.getCell(valCol) : row.createCell(valCol);
        valCell.setCellValue(user.getSavings());
        valCell.setCellStyle(valCellStyle);

        currentRow++;
    }

    // 保存原有图表切片样式
    List<XDDFFillProperties> fillStyles = new ArrayList<>();
    List<XDDFChartData.Series.SeriesTextProperties> legendStyles = new ArrayList<>();
    int oldPointCount = series.getPointCount();
    for (int i = 0; i < oldPointCount; i++) {
        fillStyles.add(series.getFillProperties(i));
        legendStyles.add(series.getSeriesTextProperties(i));
    }

    // 更新图表数据范围
    XDDFDataSource<String> newCategories = XDDFDataSourcesFactory.fromStringCellRange(
            sheet, new CellRangeAddress(startRow, currentRow - 1, catCol, catCol));
    XDDFNumericalDataSource<Double> newValues = XDDFDataSourcesFactory.fromNumericCellRange(
            sheet, new CellRangeAddress(startRow, currentRow - 1, valCol, valCol));

    series.replaceData(newCategories, newValues);
    chart.plot(chartData);

    // 恢复原有样式，新增切片复用最后一个原有样式
    int newPointCount = series.getPointCount();
    for (int i = 0; i < newPointCount; i++) {
        if (i < fillStyles.size()) {
            series.setFillProperties(i, fillStyles.get(i));
            series.setSeriesTextProperties(i, legendStyles.get(i));
        } else {
            // 新切片使用最后一个自定义样式，也可根据需求新增样式
            series.setFillProperties(i, fillStyles.get(fillStyles.size() - 1));
            series.setSeriesTextProperties(i, legendStyles.get(legendStyles.size() - 1));
        }
    }
}

关键修正说明 #

• 清空旧数据：直接移除原有数据行，确保图表只显示新数据
• 保留单元格格式：复制表头行的样式到新单元格，解决工作表格式丢失问题
• 样式备份与恢复：在更新数据前保存原有切片的填充样式和图例样式，更新后重新应用，避免自定义样式丢失
• 简化逻辑：去掉冗余的循环和追加逻辑，直接遍历新数据写入，代码更易维护

内容的提问来源于stack exchange，提问作者Adrian Duran