JAVA 基于poi xslf替换ppt占位符 和操作图表

一、注解类

        1.1图表行

package vip.xiaonuo.common.ppt.annotation;

import vip.xiaonuo.common.ppt.enums.ChartsTypesEnum;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * @Author: zxh
 * @Date: 2024/05/14/11:43
 * @Description:
 */
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface PPTChartCat {

    /**
     * 图表cat的名称
     * @return
     */
    String name();

    /**
     * 对应图表的顺序
     * @return
     */
    int index() default 0;

    /**
     * 图表的类型
     * @return
     */
    ChartsTypesEnum type();
}

        1.2图表列

package vip.xiaonuo.common.ppt.annotation;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * @Author: zxh
 * @Date: 2024/05/14/11:43
 * @Description:
 */
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface PPTChartCol {


}

        1.3文本元素

        

package vip.xiaonuo.common.ppt.annotation;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * ppt导出属性
 * @Author: zxh
 * @Date: 2024/05/09/16:49
 * @Description:
 */
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface PPTParam {
    String value() default "";
}

二操作类

        2.1图表操作类

package vip.xiaonuo.common.ppt;

import cn.hutool.core.collection.CollectionUtil;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.xslf.usermodel.XMLSlideShow;
import org.apache.poi.xslf.usermodel.XSLFChart;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.drawingml.x2006.chart.*;
import vip.xiaonuo.common.exception.CommonException;
import vip.xiaonuo.common.ppt.annotation.PPTChartCat;
import vip.xiaonuo.common.ppt.annotation.PPTChartCol;
import vip.xiaonuo.common.ppt.enums.ChartsTypesEnum;

import java.io.IOException;
import java.lang.reflect.Field;
import java.util.*;
import java.util.stream.Collectors;

/**
 * ppt图表操作工具类
 *
 * @Author: zxh
 * @Date: 2024/05/22/16:42
 * @Description:
 */
@Slf4j
public class PPTChartUtil {
    public static void main(String[] args) {
//        XMLSlideShow xmlSlideShow = PPTUtil.readPPT("D:\\ideaProject\\kitchen\\snowy\\snowy-plugin\\snowy-plugin-kitchen\\src\\main\\resources\\经营报告demo.pptx");
//        List<DemoEntity> demoEntities = new ArrayList<>();
//        demoEntities.add(new DemoEntity("2023-05-14", Math.random() * 100, Math.random() * 100, Math.random() * 100, Math.random() * 100));
//        demoEntities.add(new DemoEntity("2023-05-15", Math.random() * 100, Math.random() * 100, Math.random() * 100, Math.random() * 100));
//        demoEntities.add(new DemoEntity("2023-05-16", Math.random() * 100, Math.random() * 100, Math.random() * 100, Math.random() * 100));
//        demoEntities.add(new DemoEntity("2023-05-17", Math.random() * 100, Math.random() * 100, Math.random() * 100, Math.random() * 100));
//        demoEntities.add(new DemoEntity("2023-05-18", Math.random() * 100, Math.random() * 100, Math.random() * 100, Math.random() * 100));
//
//        updateChartDataByTitle(xmlSlideShow, "各餐别营业趋势", demoEntities);
//
//        //导出
//        PPTUtil.writePPT(xmlSlideShow, "D:\\demo" + System.currentTimeMillis() + ".pptx");
    }


    /**
     * @param xmlSlideShow
     * @param chartTitle
     * @param data
     * @param <T>
     */
    public static <T> void updateChartDataByTitle(XMLSlideShow xmlSlideShow, String chartTitle, List<T> data) {
        try {
            //从ppt中获取图表
            XSLFChart chart = getChartByTitle(xmlSlideShow, chartTitle);
            //更新图表
            updateChart(chart, data);
        } catch (Exception e) {
            log.error("图表数据更新失败，表名:{}",chartTitle);
        }
    }

    /**
     * 更新图表数据
     * 先查看图表类型
     *
     * @param chart
     */
    public static <T> void updateChart(XSLFChart chart, List<T> data) throws Exception {
        //处理数据
        List<SeriesData> seriesData = processChartFields(data);
        CTPlotArea plotArea = chart.getCTChart().getPlotArea();


        //todo 更新图表
        List<CTBarChart> barChartList = plotArea.getBarChartList();
        List<CTLineChart> lineChartList = plotArea.getLineChartList();
        List<CTPieChart> pieChartList = plotArea.getPieChartList();
        List<CTDoughnutChart> doughnutChartList = plotArea.getDoughnutChartList();

        if (CollectionUtil.isNotEmpty(barChartList) || CollectionUtil.isNotEmpty(lineChartList) || CollectionUtil.isNotEmpty(pieChartList) || CollectionUtil.isNotEmpty(doughnutChartList)) {
            //处理复合图形（一个图表既包含柱状图又包含折线图）
            //获取需要更新图表的类型顺序
            List<ChartsTypesEnum> chartsTypesEnumList = processChartCompound(data.get(0).getClass());
            //处理excel数据
            updateChartExcelDate(chart, seriesData);

            //将数据转化成更新图表需要的格式
            List<List<String>> chartDatas = new ArrayList<>();
            List<String> categories = seriesData.get(0).getValue().stream()
                    .map(NameDouble::getName)
                    .collect(Collectors.toList());
            chartDatas.add(categories);

            for (SeriesData seriesDatum : seriesData) {
                List<String> values = seriesDatum.getValue().stream()
                        //保留2位小数
                        .map(nameDouble -> Math.round(nameDouble.getValue() * 100) / 100.0 + "")
                        .collect(Collectors.toList());
                chartDatas.add(values);
            }

            List<String> txList = seriesData.stream().map(SeriesData::getName).collect(Collectors.toList());


            // 图表类型索引
            int barIndex = 0;
            int lineIndex = 0;
            int pieIndex = 0;
            int doughnutIndex = 0;

            for (int i = 0; i < chartsTypesEnumList.size(); i++) {
                ChartsTypesEnum chartsType = chartsTypesEnumList.get(i);
                switch (chartsType) {
                    case BAR:
                        updateBarCat(barChartList.get(0), barIndex, chartDatas);
                        updateBarDataCache(barChartList.get(0), barIndex, i, chartDatas.get(i + 1));
                        updateBarTx(barChartList.get(0), barIndex, i, txList);
                        barIndex++;
                        break;
                    case LINE:
                        updateLineCat(lineChartList.get(0), lineIndex, chartDatas);
                        updateLineDataCache(lineChartList.get(0), lineIndex, i, chartDatas.get(i + 1));
                        updateLineTx(lineChartList.get(0), lineIndex, i, txList);
                        lineIndex++;
                        break;
                    case DONUT:
                        updateDoughnutCat(doughnutChartList.get(0), doughnutIndex, chartDatas);
                        updateDoughnutDataCache(doughnutChartList.get(0), doughnutIndex, i, chartDatas.get(i + 1));
                        doughnutIndex++;
                        break;
                    case PIE:
                        updatePieCat(pieChartList.get(0), pieIndex, chartDatas);
                        updatePieDataCache(pieChartList.get(0), pieIndex, i, chartDatas.get(i + 1));
                        //饼图只有一列数据直接返回
                        return;
                    default:
                        break;
                }
            }
        }
    }

    private static void updateLineTx(CTLineChart lineChart, int lineIndex, int i, List<String> txList) {
        CTLineSer ctLineSer = lineChart.getSerList().get(lineIndex);
        CTSerTx tx = ctLineSer.getTx();

        replaceTx(tx, txList, i);
    }


    /**
     * 处理图表类型
     *
     * @param clazz
     * @param <T>
     * @return 图表类型根据 PPTChartCat注解的index排序的 ChartsTypesEnum list
     */
    private static <T> List<ChartsTypesEnum> processChartCompound(Class<T> clazz) {
        List<ChartsTypesEnum> chartsTypesEnumList = new ArrayList<>();
        Map<Integer, ChartsTypesEnum> indexToTypeMap = new HashMap<>();

        Field[] declaredFields = clazz.getDeclaredFields();
        for (Field field : declaredFields) {
            if (field.isAnnotationPresent(PPTChartCat.class)) {
                PPTChartCat annotation = field.getAnnotation(PPTChartCat.class);
                int index = annotation.index();
                ChartsTypesEnum type = annotation.type();
                indexToTypeMap.put(index, type);
            }
        }

        // 根据index排序生成图表类型list
        indexToTypeMap.entrySet().stream()
                .sorted(Map.Entry.comparingByKey())
                .forEachOrdered(entry -> chartsTypesEnumList.add(entry.getValue()));

        return chartsTypesEnumList;
    }


    /**
     * 更新图表关联的excel数据
     */
    public static void updateChartExcelDate(XSLFChart chart, List<SeriesData> datas) throws IOException, InvalidFormatException {
        XSSFWorkbook workbook = chart.getWorkbook();
        XSSFSheet sheet = workbook.getSheetAt(0);

        for (int i = 0; i < datas.size(); i++) {
            SeriesData data = datas.get(i);
            XSSFRow oneRow = sheet.getRow(0);
            if (oneRow == null) {
                oneRow = sheet.createRow(0);
            }
            oneRow.createCell(i + 1).setCellValue(data.name);
            int size = data.value.size();
            for (int j = 0; j < size; j++) {
                XSSFRow row = sheet.getRow(j + 1);
                if (row == null) {
                    row = sheet.createRow(j + 1);
                }
                NameDouble cellValu = data.value.get(j);
                XSSFCell cell = row.getCell(0);
                if (cell == null) {
                    cell = row.createCell(0);
                }
                cell.setCellValue(cellValu.name);

                cell = row.getCell(i + 1);
                if (cell == null) {
                    cell = row.createCell(i + 1);
                }
                cell.setCellValue(cellValu.value);
            }
            int lastRowNum = sheet.getLastRowNum();
            if (lastRowNum > size) {
                for (int idx = lastRowNum; idx > size; idx--) {
                    sheet.removeRow(sheet.getRow(idx));
                }
            }
        }
        workbook.write(chart.getPackagePart().getOutputStream());
    }

    /**
     * 更新饼图数据
     *
     * @param xmlSlideShow
     * @param chartName
     * @param data
     */
    public static void updatePieChart(XMLSlideShow xmlSlideShow, String chartName, List<SeriesData> data) {
        XSLFChart chart = getPieOrDonutChartByTitle(xmlSlideShow, chartName);
        CTDoughnutChart ctDoughnutChart = chart.getCTChart().getPlotArea().getDoughnutChartList().get(0);
        updateDoughnutCat(ctDoughnutChart, 0, null);
    }

    /**
     * 根据索引查询图表
     */
    public static XSLFChart getChartByIndex(XMLSlideShow xmlSlideShow, int index) {
        List<XSLFChart> charts = xmlSlideShow.getCharts();
        if (charts.size() > 0 && index < charts.size()) {
            return charts.get(index);
        } else {
            throw new CommonException("没有找到图表，index：%S", index);
        }
    }

    /**
     * 根据图表名称获取柱状图或折线图的数据
     */
    public static XSLFChart getChartByTitle(XMLSlideShow xmlSlideShow, String chartName) {
        List<XSLFChart> charts = xmlSlideShow.getCharts();
        for (XSLFChart chart : charts) {
            String titleText = chart.getTitleShape().getText();
            if (Objects.equals(titleText, chartName)) {
                return chart;
            }
            CTPlotArea plot = chart.getCTChart().getPlotArea();
            List<CTDoughnutChart> doughnutChartList = plot.getDoughnutChartList();
            if (doughnutChartList.size() > 0) {
                CTDoughnutChart ctDoughnutChart = doughnutChartList.get(0);
                CTPieSer serArray = ctDoughnutChart.getSerArray(0);
                String v = serArray.getTx().getStrRef().getStrCache().getPtArray(0).getV();
                if (Objects.equals(v, chartName)) {
                    return chart;
                }
            }
        }
        throw new CommonException(String.format("没有找到图表，title：%S", chartName));
    }


    /**
     * 根据图表名称获取饼图或圆环图的数据
     */
    public static XSLFChart getPieOrDonutChartByTitle(XMLSlideShow xmlSlideShow, String chartName) {
        List<XSLFChart> charts = xmlSlideShow.getCharts();
        for (XSLFChart chart : charts) {

        }
        throw new CommonException(String.format("没有找到图表，title：%S", chartName));
    }

    /**
     * 更新柱状图的 cat 缓存
     *
     * @param barChart
     * @param serIndex
     * @param data
     */
    public static void updateBarCat(CTBarChart barChart, int serIndex, List<List<String>> data) {
        CTBarSer ctBarSer = barChart.getSerList().get(serIndex);
        CTAxDataSource cat = ctBarSer.getCat();


        if (barChart.isSetExtLst()) {
            barChart.unsetExtLst();
        }


        replaceCat(cat, data);
    }

    /**
     * 更新柱状图的缓存数据
     *
     * @param barChart
     * @param serIndex
     * @param data
     */
    public static void updateBarDataCache(CTBarChart barChart, int serIndex, int index, List<String> data) {
        CTBarSer ctBarSer = barChart.getSerList().get(serIndex);
        CTNumRef numRef = ctBarSer.getVal().getNumRef();

        replaceVal(numRef, data, index);
    }

    private static void updateBarTx(CTBarChart ctBarChart, int barIndexint, int i, List<String> txList) {
        CTBarSer ctBarSer = ctBarChart.getSerList().get(barIndexint);
        CTSerTx tx = ctBarSer.getTx();

        replaceTx(tx, txList, i);
    }


    /**
     * 更新折线图的 cat 缓存
     *
     * @param lineChart
     * @param serIndex
     * @param data
     */
    public static void updateLineCat(CTLineChart lineChart, int serIndex, List<List<String>> data) {
        CTLineSer ctLineSer = lineChart.getSerList().get(serIndex);
        CTAxDataSource cat = ctLineSer.getCat();

        if (lineChart.isSetExtLst()) {
            lineChart.unsetExtLst();
        }

        replaceCat(cat, data);
    }

    /**
     * 更新折线图的缓存数据
     *
     * @param lineChart
     * @param serIndex
     * @param data
     */
    public static void updateLineDataCache(CTLineChart lineChart, int serIndex, int index, List<String> data) {
        CTLineSer ctLineSer = lineChart.getSerList().get(serIndex);
        CTNumRef numRef = ctLineSer.getVal().getNumRef();

        replaceVal(numRef, data, index);
    }


    /**
     * 更新甜甜圈图表数据
     *
     * @param pieChart
     * @param serIndex
     * @param data
     */
    public static void updateDoughnutCat(CTDoughnutChart pieChart, int serIndex, List<List<String>> data) {
        CTPieSer ctPieSer = pieChart.getSerList().get(serIndex);
        CTAxDataSource cat = ctPieSer.getCat();

        if (pieChart.isSetExtLst()) {
            pieChart.unsetExtLst();
        }

        replaceCat(cat, data);
    }

    /**
     * 更新甜甜圈图的缓存数据
     *
     * @param pieChart
     * @param serIndex
     * @param data
     */
    public static void updateDoughnutDataCache(CTDoughnutChart pieChart, int serIndex, int index, List<String> data) {
        CTPieSer ctPieSer = pieChart.getSerList().get(serIndex);
        CTNumRef numRef = ctPieSer.getVal().getNumRef();

        replaceVal(numRef, data, index);
    }


    /**
     * 更新饼图的 cat 缓存
     *
     * @param pieChart
     * @param serIndex
     * @param data
     */
    public static void updatePieCat(CTPieChart pieChart, int serIndex, List<List<String>> data) {
        CTPieSer ctPieSer = pieChart.getSerList().get(serIndex);
        CTAxDataSource cat = ctPieSer.getCat();

        if (pieChart.isSetExtLst()) {
            pieChart.unsetExtLst();
        }
    }


    /**
     * 更新饼图的缓存数据
     *
     * @param pieChart
     * @param serIndex
     */
    public static void updatePieDataCache(CTPieChart pieChart, int serIndex, int index, List<String> data) {
        CTPieSer ctPieSer = pieChart.getSerList().get(serIndex);
        CTNumRef numRef = ctPieSer.getVal().getNumRef();

        replaceVal(numRef, data, index);
    }

    /**
     * 替换 Cat 缓存
     */
    private static void replaceCat(CTAxDataSource cat, List<List<String>> data) {
        if (cat.isSetNumRef()) {
            updateCat(cat.getNumRef(), data);
        } else if (cat.isSetStrRef()) {
            updateCat(cat.getStrRef(), data);
        } else if (cat.isSetMultiLvlStrRef()) {
            updateCat(cat.getMultiLvlStrRef(), data);
        }
    }


    /**
     * 替换 val 缓存
     *
     * @param tx
     * @param txList
     * @param i
     */
    private static void replaceTx(CTSerTx tx, List<String> txList, int i) {
        // 重新设置 pt
        CTStrRef strRef = tx.getStrRef();

        // 清除现有的缓存并创建新的缓存
        strRef.unsetStrCache();
        strRef.addNewStrCache().addNewPtCount().setVal(1);

        // 设置新的 pt 值
        CTStrVal pt = strRef.getStrCache().addNewPt();
        pt.setIdx(0);
        pt.setV(txList.get(i));

        //设置对应ecel位置
        strRef.setF(String.format("Sheet1!$%S$%d", getExcelColumnName(i + 1), 1));
    }

    /**
     * 更新cat中多系列的缓存
     */
    private static void updateCat(CTMultiLvlStrRef multiLvlStrRef, List<List<String>> data) {
        multiLvlStrRef.unsetMultiLvlStrCache();

        CTMultiLvlStrData ctMultiLvlStrData = multiLvlStrRef.addNewMultiLvlStrCache();
        ctMultiLvlStrData.addNewPtCount().setVal(data.get(0).size());

        for (int i = 0; i < data.size(); i++) {
            CTLvl ctLvl = ctMultiLvlStrData.addNewLvl();
            for (int j = 0; j < data.get(i).size(); j++) {
                CTStrVal ctStrVal = ctLvl.addNewPt();
                ctStrVal.setV(data.get(i).get(j));
                ctStrVal.setIdx(j);
            }
        }
        multiLvlStrRef.setF(String.format("Sheet1!$A$2:$A$%d", data.get(0).size() + 1));
    }

    /**
     * 更新 strRef 类型的 cat 缓存
     *
     * @param strRef
     * @param data
     */
    private static void updateCat(CTStrRef strRef, List<List<String>> data) {

        // 重新设置 pt
        strRef.unsetStrCache();
        CTStrData ctStrData = strRef.addNewStrCache();
        ctStrData.addNewPtCount().setVal(data.get(0).size());
        for (int i = 0; i < data.get(0).size(); i++) {
            CTStrVal ctStrVal = ctStrData.addNewPt();
            ctStrVal.setV(data.get(0).get(i));
            ctStrVal.setIdx(i);
        }
        strRef.setF(String.format("Sheet1!$A$2:$A$%d", data.get(0).size() + 1));
    }

    /**
     * 更新 numRef 类型的 cat 缓存
     *
     * @param numRef
     * @param data
     */
    private static void updateCat(CTNumRef numRef, List<List<String>> data) {
        // 重新设置 pt
        numRef.unsetNumCache();
        CTNumData ctNumData = numRef.addNewNumCache();
        int size = data.get(0).size();
        ctNumData.addNewPtCount().setVal(size);

        for (int i = 0; i < data.get(0).size(); i++) {
            CTNumVal ctNumVal = ctNumData.addNewPt();
            ctNumVal.setV(data.get(0).get(i));
            ctNumVal.setIdx(i);
        }
        numRef.setF(String.format("Sheet1!$A$2:$A$%d", size + 1));
    }


    // 替换数据
    private static void replaceVal(CTNumRef numRef, List<String> data, int index) {
        numRef.unsetNumCache();

        CTNumData ctNumData = numRef.addNewNumCache();
        ctNumData.addNewPtCount().setVal(data.size());

        for (int i = 0; i < data.size(); i++) {
            CTNumVal ctNumVal = ctNumData.addNewPt();
            ctNumVal.setIdx(i);
            ctNumVal.setV(data.get(i));
        }
        numRef.setF(String.format("Sheet1!$%s$2:$%s$%d", getExcelColumnName(index + 1), getExcelColumnName(index + 1), data.size() + 1));
    }

    /**
     * 将原始实体数据转成SeriesData对象，方便操作和填充ppt
     *
     * @param dataList 待转换的数据列表
     * @return 转换后的 SeriesData 列表
     */
    public static <T> List<SeriesData> processChartFields(List<T> dataList) throws IllegalAccessException {
        List<SeriesData> seriesDataList = new ArrayList<>();
        if (dataList.isEmpty()) {
            return seriesDataList;
        }
        {
            //先根据实体类生成初始的 seriesDataList 此时的 value是空的
            T entity = dataList.get(0);
            Field[] fields = entity.getClass().getDeclaredFields();
            for (Field field : fields) {
                if (field.isAnnotationPresent(PPTChartCat.class)) {
                    field.setAccessible(true);
                    PPTChartCat annotation = field.getAnnotation(PPTChartCat.class);
                    String categoryName = annotation.name();
                    //todo 根据注解的 index排序
//                    int index = annotation.index();
                    SeriesData seriesData = new SeriesData();
                    seriesData.setName(categoryName);
                    seriesDataList.add(seriesData);
                }
            }
        }

        //遍历数据列表，填充 seriesDataList 的 value
        for (SeriesData seriesData : seriesDataList) {
            String catName = seriesData.getName();
            List<NameDouble> nameDoubles = new ArrayList<>();
            for (T entity : dataList) {
                Field[] fields = entity.getClass().getDeclaredFields();
                double value = 0;
                String colName = "";
                for (Field field : fields) {
                    //取出PPTChartCol注解的值
                    if (field.isAnnotationPresent(PPTChartCol.class)) {
                        try {
                            field.setAccessible(true);
                            colName = (String) field.get(entity);
                        } catch (IllegalAccessException e) {
                            e.printStackTrace();
                        }
                    }

                    //取出PPTChartCat注解的值
                    if (field.isAnnotationPresent(PPTChartCat.class)) {
                        field.setAccessible(true);
                        PPTChartCat annotation = field.getAnnotation(PPTChartCat.class);
                        String categoryName = annotation.name();
                        if (categoryName.equals(catName)) {
                            field.setAccessible(true);
                            value = field.getDouble(entity);
                        }
                    }
                }
                nameDoubles.add(new NameDouble(colName, value));
            }
            seriesData.setValue(nameDoubles);
        }
        return seriesDataList;
    }

    /**
     * 根据索引获取excel列
     */
    public static String getExcelColumnName(int index) {
        StringBuilder columnName = new StringBuilder();
        while (index >= 0) {
            int remainder = index % 26;
            columnName.insert(0, (char) ('A' + remainder));
            index = (index / 26) - 1;
        }
        return columnName.toString();
    }


    /**
     * 处理图表数据实体类
     */
    @AllArgsConstructor
    @NoArgsConstructor
    @Data
    public static class SeriesData {

        /**
         * value 系列的名字
         */
        public String name;

        public List<NameDouble> value;
    }


    /**
     *
     */
    @Data
    @AllArgsConstructor
    static class NameDouble {

        public String name;

        public double value;

        public NameDouble(String name) {
            this.name = name;
        }

    }

}

        2.2文本操作类

package vip.xiaonuo.common.ppt;

import cn.hutool.core.util.ReUtil;
import org.apache.poi.sl.usermodel.TextBox;
import org.apache.poi.xslf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.main.CTRegularTextRun;
import org.springframework.util.ObjectUtils;
import vip.xiaonuo.common.ppt.annotation.PPTParam;

import java.lang.reflect.Field;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * ppt文本和表格占位符替换工具类
 * @Author: zxh
 * @Date: 2024/05/22/16:41
 * @Description:
 */
public class PPTTextUtil {
    /**
     * 修改ppt模板中文本和表格的数据
     * @param pptx XMLSlideShow 需要修改的ppt对象
     * @param entity entity 需要替换的实体对象
     */
    public static <T> void setPPTPlaceholder(XMLSlideShow pptx , T entity){

        Map<String, String> stringStringMap = processField(entity);
        // 遍历幻灯片
        List<XSLFSlide> slideList = pptx.getSlides();
        for (XSLFSlide slide : slideList) {

            // 1. 替换段落占位符
            // 1.1 获取所有的shape，并解析为文本段落
            List<XSLFShape> shapes = slide.getShapes();
            List<XSLFTextParagraph> paragraphsFromSlide = new ArrayList<>();
            for (XSLFShape shape : shapes) {
                List<XSLFTextParagraph> textParagraphs = parseParagraph(shape);
                paragraphsFromSlide.addAll(textParagraphs);
            }
            // 1.2 替换文本段落中的占位符
            for (XSLFTextParagraph paragraph : paragraphsFromSlide) {
                replaceTagInParagraph(paragraph, stringStringMap, -1);
            }
            // 2. 替换表格内占位符
            // 2.1 循环获取到表格的单元格，并获取到文本段落
            List<XSLFTable> allTableFromSlide = getAllTableFromSlide(slide);
            for (XSLFTable xslfTableRows : allTableFromSlide) {
                List<XSLFTableRow> rows = xslfTableRows.getRows();
                for (XSLFTableRow row : rows) {
                    for (XSLFTableCell cell : row.getCells()) {
                        List<XSLFTextParagraph> textParagraphs = cell.getTextParagraphs();
                        for (XSLFTextParagraph textParagraph : textParagraphs) {
                            // 2.2 替换文本段落中的占位符
                            replaceTagInParagraph(textParagraph, stringStringMap, -1);
                        }
                    }
                }
            }
        }
    }

    /**
     * 处理实体类的属性，生成map
     */
    public static <T> Map<String, String> processField(T entity) {
        Map<String, String> resultMap = new HashMap<>();
        processFieldsRecursive(entity, "", resultMap);
        return resultMap;

    }


    /**
     * 生成 用户填充文本和表格中 用 ${}占位符的
     * @param entity
     * @param prefix
     * @param resultMap
     * @param <T>
     */
    private static <T> void processFieldsRecursive(T entity, String prefix, Map<String, String> resultMap) {
        Class<?> clazz = entity.getClass();
        Field[] fields = clazz.getDeclaredFields();

        for (Field field : fields) {
            field.setAccessible(true);
            PPTParam pptParamAnnotation = field.getAnnotation(PPTParam.class);
            if (pptParamAnnotation != null) {
                String fieldName = field.getName();
                String fullFieldName = prefix.isEmpty() ? fieldName : prefix + "." + fieldName;
                Object value;
                try {
                    value = field.get(entity);
                } catch (IllegalAccessException e) {
                    continue;
                }
                if (value != null) {
                    if (field.getType().isPrimitive() || field.getType().equals(String.class)) {
                        resultMap.put(fullFieldName, value.toString());
                    } else {
                        //递归一下，如果属性是引用数据类型，用 “.” 拼接key
                        processFieldsRecursive(value, fullFieldName, resultMap);
                    }
                }
            }
        }
    }

    /**
     * 替换段落内的标签文本
     */
    public static void replaceTagInParagraph(XSLFTextParagraph paragraph, Map<String, String> paramMap, int start) {
        String paraText = paragraph.getText();

        // 正则匹配，循环匹配替换
        // String regEx = "\\$\\{.+?\\}"; 替换成下面的正则 带上[]匹配起来愉快又轻松
        // 正则匹配，循环匹配替换
        String regEx = "\\$\\{([^}]+)\\}";
        Pattern pattern = Pattern.compile(regEx);
        Matcher matcher = pattern.matcher(paraText);
        while (matcher.find()) {
            StringBuilder keyWord = new StringBuilder();
            // 获取占位符起始位置所在run的索引
            int s = getRunIndex(paragraph, "${", start);
            if (s < start) {
                // 重复递归，直接返回
                return;
            }
            // 获取占位符结束位置所在run的索引
            int e = getRunIndex(paragraph, "}", start);
            // 这个是带占位符的 用来替换文本
            String rs = matcher.group(0);
            //这个是不带占位符的 用来去map里的数据
            String tag = matcher.group(1);
            // 存放 key
            keyWord.append(rs);
            // 获取标签所在 run 的全部文字
            String text = getRunsT(paragraph, s, e+1);
            // 如果没在 paramMap，则不做替换
            String v = nullToDefault(paramMap.get(tag), "-");
            // 没有找到这个标签所对应的值，那么就直接替换成标签的值（业务需求来着，找不到不替换）
            setText(paragraph.getTextRuns().get(Math.max(s,0)), text.replace(rs, v));
            // 存在 ${ 和 } 不在同一个CTRegularTextRun内的情况，将其他替换为空字符
            for (int i = s + 1; i < e + 1; i++) {
                setText(paragraph.getTextRuns().get(i), "");
            }
            start = e + 1;
        }
    }

    /**
     * 获取段落下特定索引的textRun的值
     * @param paragraph 段落
     * @param start 起始位置
     * @param end 终止位置
     * @return run值
     */
    private static  String getRunsT(XSLFTextParagraph paragraph, int start, int end) {
        List<XSLFTextRun> textRuns = paragraph.getTextRuns();
        StringBuilder t = new StringBuilder();
        for (int i = (Math.max(start, 0)); i < end; i++) {
            t.append(textRuns.get(i).getRawText());
        }
        return t.toString();
    }

    /**
     * toString方法，空则返回默认值
     * @param o 对象
     * @param defaultStr 默认值
     * @return toString
     */
    private static  String nullToDefault(Object o, String defaultStr) {
        if (ObjectUtils.isEmpty(o)) {
            return defaultStr;
        }
        return o.toString();
    }

    /**
     * 设置run的值
     * @param run run
     * @param t run值
     * @return void
     */
    private static  void setText(XSLFTextRun run, String t) {
            run.setText(t);
    }

    /**
     * 获取word在段落中出现第一次的run的索引
     * @param paragraph 段落
     * @param word 目标值
     * @param start 索引
     * @return void
     */
    private  static int getRunIndex(XSLFTextParagraph paragraph, String word, int start) {

        /**
         *  在pptx 的ooxml中 一串文字可能可能分割在多个run中 例如”${“就可能存在在两个相邻的run中，所以要拼接一下再确定索引
         *  还有个坑 ’\n‘ 会单独成run，在xslf中 换行符不能进行替换，所以写占位符的时候不能带换行  真的傻逼！！！！！！！！！！！！
         */
        List<XSLFTextRun> textRuns = paragraph.getTextRuns();
        for (int i = (Math.max(start, 0)); i < textRuns.size(); i++) {
            String text = textRuns.get(i).getRawText();
            if (i!=textRuns.size()-1){
                String rawText = textRuns.get(i + 1).getRawText();
                if (rawText.contains(word)){
                    return i+1;
                }
                text = text + rawText;
            }
            if (text.contains(word)) {
                return i;
            }
        }
        return -1;
    }

    /**
     * 从幻灯片中获取表格列表
     * @param slide 幻灯片
     * @return 表格列表
     */
    public static List<XSLFTable> getAllTableFromSlide(XSLFSlide slide) {
        List<XSLFTable> tables = new ArrayList<>();
        for (XSLFShape shape : slide.getShapes()) {
            if (shape instanceof XSLFTable) {
                tables.add((XSLFTable) shape);
            }
        }
        return tables;
    }

    /**
     * 解析一个shape内的所有段落
     * @param shape shape
     * @return 文本段落列表
     */
    public static List<XSLFTextParagraph> parseParagraph(XSLFShape shape) {
        if (shape instanceof XSLFAutoShape) {
            XSLFAutoShape autoShape = (XSLFAutoShape) shape;
            return autoShape.getTextParagraphs();
        } else if (shape instanceof XSLFTextShape) {
            XSLFTextShape textShape = (XSLFTextShape) shape;
            return textShape.getTextParagraphs();
        } else if (shape instanceof XSLFFreeformShape) {
            XSLFFreeformShape freeformShape = (XSLFFreeformShape) shape;
            return freeformShape.getTextParagraphs();
        } else if (shape instanceof TextBox) {
            TextBox textBox = (TextBox) shape;
            return textBox.getTextParagraphs();
        }
        return new ArrayList<>();
    }
}

        2.3 通用操作类

package vip.xiaonuo.common.ppt;

import cn.afterturn.easypoi.cache.manager.POICacheManager;
import cn.hutool.core.util.StrUtil;
import cn.hutool.http.HttpStatus;
import com.alibaba.excel.util.MapUtils;
import com.alibaba.fastjson.JSON;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.sl.usermodel.TableCell;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xslf.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.drawingml.x2006.chart.*;
import vip.xiaonuo.common.exception.CommonException;
import vip.xiaonuo.common.ppt.annotation.PPTChartCat;
import vip.xiaonuo.common.ppt.annotation.PPTChartCol;
import vip.xiaonuo.common.ppt.annotation.PPTTableColumn;
import vip.xiaonuo.common.util.HttpServletUtil;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.awt.*;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 基于poi的ppt操作工具类
 *
 * @Author: zxh
 * @Date: 2024/05/09/16:46
 * @Description:
 */
@Slf4j
public class PPTUtil {


    public static void main(String[] args) throws Exception {


//        List<ChartsTypesEnum> chartsTypesEnums = new ArrayList<ChartsTypesEnum>();
//        chartsTypesEnums.add(ChartsTypesEnum.BAR);
//        chartsTypesEnums.add(ChartsTypesEnum.LINE);
//        chartsTypesEnums.add(ChartsTypesEnum.LINE);
//        chartsTypesEnums.add(ChartsTypesEnum.LINE);
//
//
        System.out.println("0--->"+getExcelColumnName(0));
        System.out.println("25--->"+getExcelColumnName(25));
        System.out.println("26--->"+getExcelColumnName(26));
        System.out.println("888--->"+getExcelColumnName(888));
//
        ArrayList<DemoEntity> demoEntities = new ArrayList<>();
        demoEntities.add(new DemoEntity("2023-05-14", Math.random() * 100, Math.random() * 100, Math.random() * 100, Math.random() * 100));
        demoEntities.add(new DemoEntity("2023-05-15", Math.random() * 100, Math.random() * 100, Math.random() * 100, Math.random() * 100));
        demoEntities.add(new DemoEntity("2023-05-16", Math.random() * 100, Math.random() * 100, Math.random() * 100, Math.random() * 100));
        demoEntities.add(new DemoEntity("2023-05-17", Math.random() * 100, Math.random() * 100, Math.random() * 100, Math.random() * 100));
        demoEntities.add(new DemoEntity("2023-05-18", Math.random() * 100, Math.random() * 100, Math.random() * 100, Math.random() * 100));
//
//
//        XMLSlideShow xmlSlideShow = readPPT("D:\\ideaProject\\kitchen\\snowy\\snowy-plugin\\snowy-plugin-kitchen\\src\\main\\resources\\经营报告demo.pptx");
//        //处理图表数据
//        updateChartDate(xmlSlideShow, "顾客月消费频次", demoEntities);
//        try {
//            long l = System.currentTimeMillis();
//            try (FileOutputStream out = new FileOutputStream(StrUtil.format("D://demo{}.pptx", l))) {
//                xmlSlideShow.write(out);
//            }
//        } catch (IOException e1) {
//            e1.printStackTrace();
//        }


    }

    /**
     * 读取ppt
     */
    public static XMLSlideShow readPPT(String filePath) {
        try {
            InputStream file1 = POICacheManager.getFile(filePath);
            XMLSlideShow xmlSlideShow = new XMLSlideShow(file1);
            file1.close();
            return xmlSlideShow;
        } catch (Exception e) {
            e.printStackTrace();
            throw new CommonException("读取ppt失败", e);
        }
    }

    /**
     * 以流的方式导出
     */
    public static void exportPPT(XMLSlideShow xmlSlideShow, String filePath) {
        try (FileOutputStream out = new FileOutputStream(filePath)) {
            xmlSlideShow.write(out);
        } catch (IOException e1) {
            e1.printStackTrace();
        }
    }


    /**
     *     写入 ppt
     */
    public  static  void writePPT(XMLSlideShow slideShow,String exportPath) {
        try {
            File file = new File(exportPath);
            if (file.exists()) {
                file.delete();
            }
            slideShow.write(new FileOutputStream(new File(exportPath)));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出流
     */
    public static void exportPPTStream (XMLSlideShow slideShow,String fileName)  {
        try {
            HttpServletResponse response = HttpServletUtil.getResponse();
            try {

                response.reset();
                response.setHeader("Access-Control-Expose-Headers", "content-disposition");
                response.setHeader("content-disposition", "attachment; filename=\"" + fileName + "\"");
                //响应跨域问题
                response.setHeader("Access-Control-Allow-Origin", "*");
                response.setContentType("application/octet-stream;charset=UTF-8");
                //使用的easyExcel工具
                //自适应列宽度
                //自定义单元格样式
                ServletOutputStream out = response.getOutputStream();
                slideShow.write(out);
                out.flush();
            } catch (Exception e) {
                log.error("ppt导出失败 错误信息:", e);
                // 重置response
                response.reset();
                response.setContentType("application/json;UTF-8");
                Map<String, Object> map = MapUtils.newHashMap();
                map.put("status", HttpStatus.HTTP_INTERNAL_ERROR);
                map.put("message", "导出数据失败" + e.getMessage());
                response.getWriter().println(JSON.toJSONString(map));
            }
        } catch (IOException e) {
            log.error(">>> 导出数据异常：{}", e.getMessage());
        }
    }



    /**
     * 生成ppt表格并插入指定页
     */
    public static <T> void setPPTTable(XMLSlideShow pptx, String title, Integer pageNum, List<T> entity) throws Exception {
        XSLFSlide slide = pptx.createSlide();

        XSLFTextBox textBox = slide.createTextBox();
        textBox.setAnchor(new Rectangle(50, 50, 600, 50));
        textBox.setText(title);

        int numCols = 0; // 列数

        // 根据泛型T中带有PPTTableCom注解的字段数确定列数
        Field[] fields = entity.get(0).getClass().getDeclaredFields();
        for (Field field : fields) {
            if (field.isAnnotationPresent(PPTTableColumn.class)) {
                numCols++;
            }
        }

        int numRows = 1; // 行数（包括表头）
        int rowIndex = 0;
        XSLFTable table = null;

        for (T item : entity) {
            // 如果行数超过10，则创建新的幻灯片
            if (numRows % 11 == 0) {
                slide = pptx.createSlide();
                textBox = slide.createTextBox();
                textBox.setAnchor(new Rectangle(50, 50, 600, 50));
                textBox.setText(title);
                rowIndex = 0;
            }

            // 在第一次迭代或创建新的幻灯片后创建表格
            if (table == null || rowIndex == 0) {
                table = slide.createTable();
                XSLFTableRow headerRow = table.addRow();
                for (Field field : fields) {
                    if (field.isAnnotationPresent(PPTTableColumn.class)) {
                        PPTTableColumn annotation = field.getAnnotation(PPTTableColumn.class);
                        XSLFTableCell cell = headerRow.addCell();
                        cell.setText(annotation.name());
                    }
                }
                numRows++;
            }

            XSLFTableRow row = table.addRow();
            for (Field field : fields) {
                if (field.isAnnotationPresent(PPTTableColumn.class)) {
                    field.setAccessible(true);
                    Object value = field.get(item);
                    String cellValue = value != null ? value.toString() : "";
                    row.addCell().setText(cellValue);
                }
            }
            rowIndex++;
            numRows++;
        }

        // 设置表格边框
        for (int i = 0; i < numRows; i++) {
            for (int j = 0; j < numCols; j++) {
                XSLFTableCell cell = table.getCell(i, j);
                cell.setBorderColor(TableCell.BorderEdge.bottom, new Color(0, 0, 0));
                cell.setBorderColor(TableCell.BorderEdge.top, new Color(0, 0, 0));
                cell.setBorderColor(TableCell.BorderEdge.left, new Color(0, 0, 0));
                cell.setBorderColor(TableCell.BorderEdge.right, new Color(0, 0, 0));
            }
        }

        // 设置表格位置和大小
        table.setAnchor(new Rectangle(50, 150, 600, numRows * 20));

        // 设置幻灯片的页码
        pptx.setSlideOrder(slide, pageNum);
    }

    /**
     * 更新图表数据 柱状图 折线图 饼图
     * <br/>
     * 传入的参数data是需要修改的实体类 ，<br/>
     * 实体类的属性需要使用注解@PPTChartCol 来标记x轴坐标<br/>
     * 注解@PPTChartCat来标记图表category的
     * value为category名 属性为具体数据
     * 其中饼状图只有是长度为1的list
     *
     * @param slideShow XMLSlideShow 需要修改的ppt对象
     * @param title     匹配的图表title
     * @param data      需要修改的数据
     * @param <T>
     */
    public static <T> void updateChartDate(XMLSlideShow slideShow, String title, List<T> data) {
        List<XSLFChart> charts = slideShow.getCharts();

        for (XSLFChart chart : charts) {
            String text = chart.getTitleShape().getText();

            try {
                if (StrUtil.isNotEmpty(text) && text.equals(title)) {
                    //处理数据
                    List<SeriesData> seriesDatas = processChartFields(data);
                    //工作簿
                    XSSFWorkbook workbook = chart.getWorkbook();
                    //sheet
                    XSSFSheet sheet = workbook.getSheetAt(0);

                    // 查看里面的图表数据，才能知道是什么图表
                    CTPlotArea plot = chart.getCTChart().getPlotArea();

                    // 柱状图
                    if (!plot.getBarChartList().isEmpty()) {
                        CTBarChart barChart = plot.getBarChartArray(0);
//                        updateChartExcelV(seriesDatas, sheet);
                        workbook.write(chart.getPackagePart().getOutputStream());

                        int i = 0;
                        List<CTBarSer> serList = barChart.getSerList();
                        for (SeriesData seriesData : seriesDatas) {
                            CTBarSer ser;
                            if (i < serList.size()) {
                                ser = serList.get(i);
                            } else {
                                // 创建新的CTBarSer
                                ser = barChart.addNewSer();
                                ser.addNewTx();
                                ser.addNewCat();
                                ser.addNewVal();
                                serList.add(ser);
                            }
                            updateChartCatAndNum(seriesData, ser.getTx(), ser.getCat(), ser.getVal(), i);
                            ++i;
                        }

                        //折线图
                    } else if (!plot.getLineChartList().isEmpty()) {
                        CTLineChart lineChart = plot.getLineChartArray(0);
//                        updateChartExcelV(seriesDatas, sheet);
                        workbook.write(chart.getPackagePart().getOutputStream());

                        int i = 0;
                        List<CTLineSer> serList = lineChart.getSerList();
                        for (SeriesData seriesData : seriesDatas) {
                            CTLineSer ser;
                            if (i < serList.size()) {
                                ser = serList.get(i);
                            } else {
                                // 创建新的CTLineSer
                                ser = lineChart.addNewSer();
                                ser.addNewTx();
                                ser.addNewCat();
                                ser.addNewVal();
                                serList.add(ser);
                            }
                            updateChartCatAndNum(seriesData, ser.getTx(), ser.getCat(), ser.getVal(), i);
                            ++i;
                        }
                    }

                    // 饼图
                    else if (!plot.getPieChartList().isEmpty()) {
                        // 饼图只有一列数据
//                        updateChartExcelV(Collections.singletonList(seriesDatas.get(0)), sheet);
                        workbook.write(chart.getPackagePart().getOutputStream());

                        CTPieChart pieChart = plot.getPieChartArray(0);
                        int i = 0;
                        for (CTPieSer ser : pieChart.getSerList()) {
                            updateChartCatAndNum(seriesDatas.get(i), ser.getTx(), ser.getCat(), ser.getVal(), 0);
                            ++i;
                        }
                    }
                    //甜甜圈
                    else if (!plot.getDoughnutChartList().isEmpty()) {

                        // 饼图只有一列数据
//                        updateChartExcelV(Collections.singletonList(seriesDatas.get(0)), sheet);
                        workbook.write(chart.getPackagePart().getOutputStream());

                        CTDoughnutChart pieChart = plot.getDoughnutChartArray(0);
                        int i = 0;
                        for (CTPieSer ser : pieChart.getSerList()) {
                            updateChartCatAndNum(seriesDatas.get(i), ser.getTx(), ser.getCat(), ser.getVal(), 0);
                            ++i;
                        }
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }


    /**
     * 替换数据表中的占位符的数据
     *
     * @param sheet
     * @param dataMap
     */
    private static void replacePlaceholders(XSSFSheet sheet, Map<String, String> dataMap) {
        final String placeholderRegex = "\\$\\{.+?\\}";
        final Pattern pattern = Pattern.compile(placeholderRegex);
        for (Row row : sheet) {
            for (Cell cell : row) {
                String cellValue = cell.getStringCellValue();
                Matcher matcher = pattern.matcher(cellValue);
                if (matcher.find()) {
                    String placeholder = matcher.group();
                    String replacement = dataMap.get(placeholder);
                    if (replacement != null) {
                        cell.setCellValue(replacement);
                    }
                }
            }
        }
    }


    /**
     * 将原始实体数据转成SeriesData对象，方便操作和填充ppt
     *
     * @param dataList 待转换的数据列表
     * @return 转换后的 SeriesData 列表
     */
    public static <T> List<SeriesData> processChartFields(List<T> dataList) throws IllegalAccessException {
        List<SeriesData> seriesDataList = new ArrayList<>();
        if (dataList.isEmpty()) {
            return seriesDataList;
        }
        {
            //先根据实体类生成初始的 seriesDataList 此时的 value是空的
            T entity = dataList.get(0);
            Field[] fields = entity.getClass().getDeclaredFields();
            for (Field field : fields) {
                if (field.isAnnotationPresent(PPTChartCat.class)) {
                    field.setAccessible(true);
                    PPTChartCat annotation = field.getAnnotation(PPTChartCat.class);
                    String categoryName = annotation.name();
                    SeriesData seriesData = new SeriesData();
                    seriesData.setName(categoryName);
                    seriesDataList.add(seriesData);
                }
            }
        }

        //遍历数据列表，填充 seriesDataList 的 value
        for (SeriesData seriesData : seriesDataList) {
            String catName = seriesData.getName();
            List<NameDouble> nameDoubles = new ArrayList<>();
            for (T entity : dataList) {
                Field[] fields = entity.getClass().getDeclaredFields();
                double value = 0;
                String colName = "";
                for (Field field : fields) {
                    //取出PPTChartCol注解的值
                    if (field.isAnnotationPresent(PPTChartCol.class)) {
                        try {
                            field.setAccessible(true);
                            colName = (String) field.get(entity);
                        } catch (IllegalAccessException e) {
                            e.printStackTrace();
                        }
                    }

                    //取出PPTChartCat注解的值
                    if (field.isAnnotationPresent(PPTChartCat.class)) {
                        field.setAccessible(true);
                        PPTChartCat annotation = field.getAnnotation(PPTChartCat.class);
                        String categoryName = annotation.name();
                        if (categoryName.equals(catName)) {
                            field.setAccessible(true);
                            value = field.getDouble(entity);
                        }
                    }
                }
                nameDoubles.add(new NameDouble(colName, value));
            }
            seriesData.setValue(nameDoubles);
        }
        return seriesDataList;
    }


    /**
     * 更新 chart 的缓存数据
     *
     * @param data          数据
     * @param catDataSource 条目的数据缓存
     * @param numDataSource 数据的缓存
     */
    protected static void updateChartCatAndNum(SeriesData data, CTSerTx serTitle, CTAxDataSource catDataSource, CTNumDataSource numDataSource, int index) {

        //这里的数据先更新再新增 是为了不破坏模板的格式
        if (serTitle.getStrRef() == null) {
            serTitle.addNewStrRef().setF(String.format("Sheet1!$%s$%d", getExcelColumnName(index + 1), 1));
            serTitle.getStrRef().addNewStrCache().addNewPt().setV(data.name);
        } else {
            serTitle.getStrRef().setF(serTitle.getStrRef().getF()); //
            serTitle.getStrRef().getStrCache().getPtArray(0).setV(data.name);
        }


        long ptCatCnt = 0;
        long ptNumCnt = 0;
        //  cat 也可能是 numRef
        if (catDataSource.getNumRef() == null || numDataSource.getNumRef() == null) {
            catDataSource.addNewNumRef().addNewNumCache().addNewPtCount().setVal(data.value.size());
            numDataSource.addNewNumRef().addNewNumCache().addNewPtCount().setVal(data.value.size());
            catDataSource.getNumRef().setF(String.format("Sheet1!$%s$%d", getExcelColumnName(0), data.value.size()));
            numDataSource.getNumRef().setF(String.format("Sheet1!$%s$%d", getExcelColumnName(index), data.value.size()));
        } else {
            ptCatCnt = catDataSource.getNumRef().getNumCache().getPtCount().getVal();
            ptNumCnt = numDataSource.getNumRef().getNumCache().getPtCount().getVal();
        }
        int dataSize = data.value.size();
        for (int i = 0; i < dataSize; i++) {
            NameDouble cellValu = data.value.get(i);
            CTNumVal cat = ptCatCnt > i ? catDataSource.getNumRef().getNumCache().getPtArray(i) : catDataSource.getNumRef().getNumCache().addNewPt();
            cat.setIdx(i);
            cat.setV(cellValu.name);

            CTNumVal val = ptNumCnt > i ? numDataSource.getNumRef().getNumCache().getPtArray(i) : numDataSource.getNumRef().getNumCache().addNewPt();
            val.setIdx(i);
            val.setV(String.format("%.2f", cellValu.value));

        }

        // 更新对应 excel 的range
        catDataSource.getNumRef().setF(replaceRowEnd(catDataSource.getNumRef().getF(), ptCatCnt, dataSize));
        numDataSource.getNumRef().setF(replaceRowEnd(numDataSource.getNumRef().getF(), ptNumCnt, dataSize));

        // 删除多的
        if (ptNumCnt > dataSize) {
            for (int idx = dataSize; idx < ptNumCnt; idx++) {
                catDataSource.getNumRef().getNumCache().removePt(dataSize);
                numDataSource.getNumRef().getNumCache().removePt(dataSize);
            }
        }
        // 更新个数
        catDataSource.getNumRef().getNumCache().getPtCount().setVal(dataSize);
        numDataSource.getNumRef().getNumCache().getPtCount().setVal(dataSize);
    }


    /**
     * 替换 形如： Sheet1!$A$2:$A$4 的字符
     *
     * @param range
     * @return
     */
    public static String replaceRowEnd(String range, long oldSize, long newSize) {
        Pattern pattern = Pattern.compile("(:\\$[A-Z]+\\$)(\\d+)");
        Matcher matcher = pattern.matcher(range);
        if (matcher.find()) {
            long old = Long.parseLong(matcher.group(2));
            return range.replaceAll("(:\\$[A-Z]+\\$)(\\d+)", "$1" + Long.toString(old - oldSize + newSize));
        }
        return range;
    }


    /**
     * 根据索引获取excel列
     */
    public static String getExcelColumnName(int index) {
        StringBuilder columnName = new StringBuilder();
        while (index >= 0) {
            int remainder = index % 26;
            columnName.insert(0, (char) ('A' + remainder));
            index = (index / 26) - 1;
        }
        return columnName.toString();
    }


    /**
     * 处理图表数据实体类
     */
    @AllArgsConstructor
    @NoArgsConstructor
    @Data
    public static class SeriesData {

        /**
         * value 系列的名字
         */
        public String name;

        public List<NameDouble> value;
    }


    /**
     *
     */
    @Data
    @AllArgsConstructor
    static class NameDouble {

        public String name;

        public double value;

        public NameDouble(String name) {
            this.name = name;
        }

    }
}

 三，文本替换使用示例

    public static void main(String[] args) {
        XMLSlideShow xmlSlideShow = PPTUtil.readPPT("D:\\ideaProject\\kitchen\\snowy\\snowy-plugin\\snowy-plugin-kitchen\\src\\main\\resources\\经营报告demo.pptx");
        List<DemoEntity> demoEntities = new ArrayList<>();
        demoEntities.add(new DemoEntity("2023-05-14", Math.random() * 100, Math.random() * 100, Math.random() * 100, Math.random() * 100));
        demoEntities.add(new DemoEntity("2023-05-15", Math.random() * 100, Math.random() * 100, Math.random() * 100, Math.random() * 100));
        demoEntities.add(new DemoEntity("2023-05-16", Math.random() * 100, Math.random() * 100, Math.random() * 100, Math.random() * 100));
        demoEntities.add(new DemoEntity("2023-05-17", Math.random() * 100, Math.random() * 100, Math.random() * 100, Math.random() * 100));
        demoEntities.add(new DemoEntity("2023-05-18", Math.random() * 100, Math.random() * 100, Math.random() * 100, Math.random() * 100));

        updateChartDataByTitle(xmlSlideShow, "各餐别营业趋势", demoEntities);

        //导出
        PPTUtil.writePPT(xmlSlideShow, "D:\\demo" + System.currentTimeMillis() + ".pptx");
    }

        DemoEntity:

@Data
@AllArgsConstructor
public class DemoEntity {

    @PPTChartCol
    String date;

    @PPTChartCat(name = "总营业额",type= ChartsTypesEnum.BAR,index=0)
    double total;

    @PPTChartCat(name ="早餐",type= ChartsTypesEnum.LINE,index=1)
    double breakfast;

    @PPTChartCat(name = "午餐",type= ChartsTypesEnum.LINE,index=2)
    double lunch;

    @PPTChartCat(name = "22餐",type= ChartsTypesEnum.LINE,index=3)
    double asdf;

}