java appt_java 使用 apoi 更新 ppt 中图表的数据

packagezhongcy.demos;importorg.apache.poi.ooxml.POIXMLDocumentPart;importorg.apache.poi.openxml4j.exceptions.InvalidFormatException;importorg.apache.poi.sl.usermodel.SlideShow;importorg.apache.poi.sl.usermodel.SlideShowFactory;importorg.apache.poi.xslf.usermodel.XSLFChart;importorg.apache.poi.xslf.usermodel.XSLFSlide;importorg.apache.poi.xssf.usermodel.XSSFCell;importorg.apache.poi.xssf.usermodel.XSSFRow;importorg.apache.poi.xssf.usermodel.XSSFSheet;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;import org.openxmlformats.schemas.drawingml.x2006.chart.*;importjava.io.File;importjava.io.FileNotFoundException;importjava.io.FileOutputStream;importjava.io.IOException;importjava.util.Arrays;importjava.util.List;importjava.util.regex.Matcher;importjava.util.regex.Pattern;public classPPTDemo {public voidrun() {try{

SlideShow slideShow= SlideShowFactory.create(new File("./res/1.pptx"));for(Object o : slideShow.getSlides()) {

XSLFSlide slider=(XSLFSlide) o;//第一页

if (slider.getSlideNumber() == 1) {for(POIXMLDocumentPart.RelationPart part : slider.getRelationParts()) {

POIXMLDocumentPart documentPart=part.getDocumentPart();//是图表

if (documentPart instanceofXSLFChart) {

XSLFChart chart=(XSLFChart) documentPart;//查看里面的图表数据，才能知道是什么图表

CTPlotArea plot =chart.getCTChart().getPlotArea();//测试数据

List seriesDatas =Arrays.asList(new SeriesData("", Arrays.asList(new NameDouble("行1", Math.random() * 100),new NameDouble("行2", Math.random() * 100),new NameDouble("行3", Math.random() * 100),new NameDouble("行4", Math.random() * 100),new NameDouble("行5", Math.random() * 100)

)),new SeriesData("", Arrays.asList(new NameDouble("行1", Math.random() * 100),new NameDouble("行2", Math.random() * 100),new NameDouble("行3", Math.random() * 100),new NameDouble("行4", Math.random() * 100),new NameDouble("行5", Math.random() * 100)

))

);

XSSFWorkbook workbook=chart.getWorkbook();

XSSFSheet sheet= workbook.getSheetAt(0);//柱状图

if (!plot.getBarChartList().isEmpty()) {

CTBarChart barChart= plot.getBarChartArray(0);

updateChartExcelV(seriesDatas, workbook, sheet);

workbook.write(chart.getPackagePart().getOutputStream());int i = 0;for(CTBarSer ser : barChart.getSerList()) {

updateChartCatAndNum(seriesDatas.get(i), ser.getTx(), ser.getCat(), ser.getVal());++i;

}

}//饼图

else if (!plot.getPieChartList().isEmpty()) {//示例饼图只有一列数据

updateChartExcelV(Arrays.asList(seriesDatas.get(0)), workbook, 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());++i;

}

}

}

}

}

}try{try (FileOutputStream out = new FileOutputStream("./res/o1.pptx")) {

slideShow.write(out);

}

}catch(FileNotFoundException e1) {

e1.printStackTrace();

}catch(IOException e1) {

e1.printStackTrace();

}

}catch(IOException e) {

e.printStackTrace();

}catch(InvalidFormatException e) {

e.printStackTrace();

}

}/*** 更新图表的关联 excel， 值是纵向的

*

*@paramparam

*@paramworkbook

*@paramsheet*/

protected void updateChartExcelV(ListseriesDatas, 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 的缓存数据

*

*@paramdata 数据

*@paramserTitle 系列的标题缓存

*@paramcatDataSource 条目的数据缓存

*@paramnumDataSource 数据的缓存*/

protected voidupdateChartCatAndNum(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);

}/*** 替换 形如： Sheet1!$A$2:$A$4 的字符

*

*@paramrange

*@return

*/

public static String replaceRowEnd(String range, long oldSize, longnewSize) {

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

}returnrange;

}/*** 一个系列的数据*/

public static classSeriesData {/*** value 系列的名字*/

publicString name;public Listvalue;public SeriesData(java.util.Listvalue) {this.value =value;

}public SeriesData(String name, Listvalue) {this.name =name;this.value =value;

}publicSeriesData() {

}

}/****/

public classNameDouble {publicString name;/**

*/

public doublevalue;public NameDouble(String name, doublevalue) {this.name =name;this.value =value;

}

@SuppressWarnings("unused")publicNameDouble() {

}

}

}