POI PPT 更新柱状图数据，清除缓存，更新＜c:extLst＞标签数据，更新＜c15:dlblRangeCache＞标签

本文链接：https://blog.csdn.net/dicklong91/article/details/125618673

POI PPT 更新柱状图数据，清除缓存，更新<c:extLst>标签数据，更新c15:dlblRangeCache标签

关键代码片段

    /**
     * 更新图表的关联 excel
     *
     * @param seriesDatas
     * @param workbook
     * @param sheet
     */
    public static void updateChartExcelV(List<SeriesData> seriesDatas, XSSFWorkbook workbook, XSSFSheet sheet) {
        XSSFRow title = sheet.getRow(0);
        for (int i = 0; i < seriesDatas.size(); i++) {
            SeriesData data = seriesDatas.get(i);
            if (data.name != null && !data.name.isEmpty()) {
                // 系列名称，不能修改，修改后无法打开 excel
                //                title.getCell(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));
                }
            }
        }
    }


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

        // 更新系列标题
        //        serTitle.getStrRef().setF(serTitle.getStrRef().getF()); //
        //        serTitle.getStrRef().getStrCache().getPtArray(0).setV(data.name);

        // TODO cat 也可能是 numRef

        long ptCatCnt = catDataSource.getStrRef().getStrCache().getPtCount().getVal();
        long ptNumCnt = numDataSource.getNumRef().getNumCache().getPtCount().getVal();

        int dataSize = data.value.size();
        for (int i = 0; i < dataSize; i++) {
            NameDouble cellValu = data.value.get(i);
            CTStrVal cat = ptCatCnt > i ? catDataSource.getStrRef().getStrCache().getPtArray(i)
                    : catDataSource.getStrRef().getStrCache().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.getStrRef().setF(
                replaceRowEnd(catDataSource.getStrRef().getF(),
                        ptCatCnt,
                        dataSize));
        numDataSource.getNumRef().setF(
                replaceRowEnd(numDataSource.getNumRef().getF(),
                        ptNumCnt,
                        dataSize));

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

 /**
     * 更新<c:extLst>标签数据
     * @param ctExtension
     * @param dataLabelsRangeSource
     * @param dataLabelsRangeSourceStr
     * @param i
     */
    static void updateFilteredBarSeries(CTExtension ctExtension,XDDFDataSource dataLabelsRangeSource,XDDFDataSource dataLabelsRangeSourceStr,int i) {

        XmlObject[] filteredBarSeries = ctExtension.selectChildren(new QName("http://schemas.microsoft.com/office/drawing/2012/chart", "filteredBarSeries", "c15"));
        XmlObject[] filteredBarSeriesers = filteredBarSeries[i].selectChildren(new QName("http://schemas.microsoft.com/office/drawing/2012/chart", "ser", "c15"));
        //setStr
        XmlObject[] filteredBarSerieCats0 =  filteredBarSeriesers[0].selectChildren(new QName("http://schemas.openxmlformats.org/drawingml/2006/chart", "cat"));
        XmlObject[] strRefs = filteredBarSerieCats0[0].selectChildren(new QName("http://schemas.openxmlformats.org/drawingml/2006/chart", "strRef"));
        XmlObject[] strCaches = strRefs[0].selectChildren(new QName("http://schemas.openxmlformats.org/drawingml/2006/chart", "strCache","c"));
        XmlObject strCache = strCaches[0];
        // empty the cache
        strCache.newCursor().removeXmlContents();
        // create new cache from dataLabelsRangeSource
        CTStrData newcache = CTStrData.Factory.newInstance();
        dataLabelsRangeSourceStr.fillStringCache(newcache);
        // set new cache
        strCache.set(newcache);

       //setVal
        XmlObject[] filteredBarSerievals0 =  filteredBarSeriesers[0].selectChildren(new QName("http://schemas.openxmlformats.org/drawingml/2006/chart", "val"));
        XmlObject[] numRef = filteredBarSerievals0[0].selectChildren(new QName("http://schemas.openxmlformats.org/drawingml/2006/chart", "numRef"));
        XmlObject[] numCaches = numRef[0].selectChildren(new QName("http://schemas.openxmlformats.org/drawingml/2006/chart", "numCache","c"));
        XmlObject numCache = numCaches[0];
        // empty the cache
        numCache.newCursor().removeXmlContents();
        // create new cache from dataLabelsRangeSource
        CTNumData cache = CTNumData.Factory.newInstance();
        //设置数据格式为百分比
        cache.setFormatCode("0%");
        dataLabelsRangeSource.fillNumericalCache(cache);
        // set new cache
        numCache.set(cache);
    }

/**
     * 更新<c15:dlblRangeCache>标签
     * @param ctExtension
     * @param dataLabelsRangeSource
     * @param i
     */
    static void updateDataLabelsRange(CTExtension ctExtension,XDDFDataSource dataLabelsRangeSource,int i) {
        XmlObject[] datalabelsRanges = ctExtension.selectChildren(new QName("http://schemas.microsoft.com/office/drawing/2012/chart", "datalabelsRange", "c15"));
        XmlObject dataLabelsRange = datalabelsRanges[0];
        XmlObject[] dlblRangeCaches = dataLabelsRange.selectChildren(new QName("http://schemas.microsoft.com/office/drawing/2012/chart", "dlblRangeCache", "c15"));
        XmlObject dlblRangeCache = dlblRangeCaches[0];
        // empty the cache
        dlblRangeCache.newCursor().removeXmlContents();
        // create new cache from dataLabelsRangeSource
        CTStrData cache = CTStrData.Factory.newInstance();
        //这里进行了方法重写 将数值转为为了百分数
        dataLabelsRangeSource.fillPersentStringCache(cache);
        // set new cache
        dlblRangeCache.set(cache);
    }