Java使用poi编辑ppt中的条形图 [英] Java edit bar chart in ppt by using poi
本文介绍了Java使用poi编辑ppt中的条形图的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我是在工作中使用POI的新手.现在我将使用java中的POI来读取PPT中的条形图.我提前添加了几个系列x，它们是列标题条形图所属的excel.但是我默认只能用POI读取前三列.三栏，打开PPT无法编辑条形图，说明条形图节点损坏.那么有没有高手可以帮忙说说如何使用POI正确的给条形图加颜色(加系列)?
I am a newbie to using POI at work.Now i'm going to use POI in java to read a bar chart in the PPT.I've added several series x to it in advance,which are the column headers of the excel to which the bar graph belongs. But i can only read the first three columns by default with the POI.In addition,once I modify the column header of the bar chart,or want to add a fourth column(thar is,add a color)to a bar chart with only three columns, the bar chart cannot be edited when I open the PPT,indicating that the node of the bar chart is damaged. So is there a master who can help talking how to use POI to prroperly add a color to the bar chart(add a series)?
例如:当我调试到long ptCatCnt = catDataSource.getStrRef().getStrCache().getPtCount().getVal();它显示nullpointerexecption，我不知道条形图ppt中的结构如何.所以我想知道如何更新条形图.
Eg: when I debug to the " long ptCatCnt = catDataSource.getStrRef().getStrCache().getPtCount().getVal(); It show nullpointerexecption, I don't know how structure in ppt the bar-chart is.So I want know how to update the bar-chart。
代码是:
公开课PPTDemo{
public class PPTDemo {
public void run() {
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 instanceof XSLFChart) {
XSLFChart chart = (XSLFChart) documentPart;
// 查看里面的图表数据，才能知道是什么图表
CTPlotArea plot = chart.getCTChart().getPlotArea();
// 测试数据
List<SeriesData> 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， 值是纵向的
*
* @param param
* @param workbook
* @param sheet
*/
protected 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 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);
}
/**
* 替换 形如： 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;
}
/**
* 一个系列的数据
*/
public static class SeriesData {
/**
* value 系列的名字
*/
public String name;
public List<NameDouble> value;
public SeriesData(java.util.List<NameDouble> value) {
this.value = value;
}
public SeriesData(String name, List<NameDouble> value) {
this.name = name;
this.value = value;
}
public SeriesData() {
}
}
/**
*
*/
public class NameDouble {
public String name;
/**
*/
public double value;
public NameDouble(String name, double value) {
this.name = name;
this.value = value;
}
@SuppressWarnings("unused")
public NameDouble() {
}
}
}
推荐答案
Using current apache poi 5.0.0 可以使用新的 更新 类.这避免了直接使用 PowerPoint 中的图表>XDDFooxml-schemas 类(CT... 类).直接使用CT 类很容易出错，并且需要非常了解Office Open XML 的内部XML 结构.
Using current apache poi 5.0.0 updating a chart in PowerPoint is possible using the new XDDF classes. That avoids using the ooxml-schemas classes (CT... classes) directly. Using CT classes directly is error prone and needs very good knowlegde about the internally XML structure of Office Open XML.
需要知道的是，图表数据存储在嵌入的 Excel 工作簿中.因此，在更新数据时，需要始终更新该工作簿中的数据并更新图表中的数据.
What one needs to know is that chart data is stored in an embedded Excel workbook. So while updating the data the need is always updating the data in that workbook and updating the data in the chart.
以下示例是用于说明如何执行此操作的最小可重现示例.
The following example is a minimal reproducible example for how to do this.
模板BarChartSample.pptx 包含一个只有一个系列和一个类别的条形图.它定义了图表格式.看起来像这样:
The template BarChartSample.pptx contained a bar chart having only one series an one category. It defines the chart format. It looks like so:
代码是这样的:
import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.xslf.usermodel.*;
import org.apache.poi.xddf.usermodel.chart.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.AreaReference;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumns;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumn;
public class PowerPointChangeChartData {
//patched version of XSSFTable.updateHeaders, see https://stackoverflow.com/questions/55532006/renaming-headers-of-xssftable-with-apache-poi-leads-to-corrupt-xlsx-file/55539181#55539181
static void updateHeaders(XSSFTable table) {
XSSFSheet sheet = (XSSFSheet)table.getParent();
CellReference ref = table.getStartCellReference();
if (ref == null) return;
int headerRow = ref.getRow();
int firstHeaderColumn = ref.getCol();
XSSFRow row = sheet.getRow(headerRow);
DataFormatter formatter = new DataFormatter();
if (row != null /*&& row.getCTRow().validate()*/) {
int cellnum = firstHeaderColumn;
CTTableColumns ctTableColumns = table.getCTTable().getTableColumns();
if(ctTableColumns != null) {
for (CTTableColumn col : ctTableColumns.getTableColumnList()) {
XSSFCell cell = row.getCell(cellnum);
if (cell != null) {
col.setName(formatter.formatCellValue(cell));
}
cellnum++;
}
}
}
}
static void updateChart(XSLFChart chart, Object[][] data) throws Exception {
// get chart's data source which is a Excel sheet
XSSFWorkbook chartDataWorkbook = chart.getWorkbook();
String sheetName = chartDataWorkbook.getSheetName(0);
XSSFSheet chartDataSheet = chartDataWorkbook.getSheet(sheetName);
// current Office uses a table as data source
// so get that table if present
XSSFTable chartDataTable = null;
if (chartDataSheet.getTables().size() > 0) {
chartDataTable = chartDataSheet.getTables().get(0);
}
if (chart.getChartSeries().size() == 1) { // we will process only one chart data
XDDFChartData chartData = chart.getChartSeries().get(0);
if (chartData.getSeriesCount() == 1) { // we will process only templates having one series
int rMin = 1; // first row (0) is headers row
int rMax = data.length - 1;
// set new category data
XDDFCategoryDataSource category = null;
int c = 0;
for (int r = rMin; r <= rMax; r++) {
XSSFRow row = chartDataSheet.getRow(r); if (row == null) row = chartDataSheet.createRow(r);
XSSFCell cell = row.getCell(c); if (cell == null) cell = row.createCell(c);
cell.setCellValue((String)data[r][c]); // in sheet
}
category = XDDFDataSourcesFactory.fromStringCellRange(chartDataSheet, new CellRangeAddress(rMin,rMax,c,c)); // in chart
// series 1, is present already
c = 1;
// set new values in sheet and in chart
XDDFNumericalDataSource<Double> values = null;
for (int r = rMin; r < rMax+1; r++) {
XSSFRow row = chartDataSheet.getRow(r); if (row == null) row = chartDataSheet.createRow(r);
XSSFCell cell = row.getCell(c); if (cell == null) cell = row.createCell(c);
cell.setCellValue((Double)data[r][c]); // in sheet
}
values = XDDFDataSourcesFactory.fromNumericCellRange(chartDataSheet, new CellRangeAddress(rMin,rMax,c,c));
XDDFChartData.Series series1 = chartData.getSeries(0);
series1.replaceData(category, values); // in chart
// set new title in sheet and in chart
String series1Title = (String)data[0][c];
chartDataSheet.getRow(0).getCell(c).setCellValue(series1Title); // in sheet
series1.setTitle(series1Title, new CellReference(sheetName, 0, c, true, true)); // in chart
series1.plot();
//further series, all new created
int seriesCount = data[0].length - 1;
for (int s = 2; s <= seriesCount; s++) {
c++;
// set new values
for (int r = rMin; r < rMax+1; r++) {
XSSFRow row = chartDataSheet.getRow(r); if (row == null) row = chartDataSheet.createRow(r);
XSSFCell cell = row.getCell(c); if (cell == null) cell = row.createCell(c);
cell.setCellValue((Double)data[r][c]); // in sheet
}
values = XDDFDataSourcesFactory.fromNumericCellRange(chartDataSheet, new CellRangeAddress(rMin,rMax,c,c));
XDDFChartData.Series series = chartData.addSeries(category, values); // in chart
// set new title
String seriesTitle = (String)data[0][c];
XSSFCell cell = chartDataSheet.getRow(0).getCell(c); if (cell == null) cell = chartDataSheet.getRow(0).createCell(c);
cell.setCellValue(seriesTitle); // in sheet
series.setTitle(seriesTitle, new CellReference(sheetName, 0, c, true, true)); // in chart
series.plot();
}
// update the table if present
if (chartDataTable != null) {
CellReference topLeft = new CellReference(chartDataSheet.getRow(0).getCell(0));
CellReference bottomRight = new CellReference(chartDataSheet.getRow(rMax).getCell(c));
AreaReference tableArea = chartDataWorkbook.getCreationHelper().createAreaReference(topLeft, bottomRight);
chartDataTable.setArea(tableArea);
updateHeaders(chartDataTable);
}
}
}
}
public static void main(String[] args) throws Exception {
String filePath = "BarChartSample.pptx"; // has template line chart
String filePathNew = "BarChartSample_New.pptx";
Object[][] data = new Object[][] { // new data 3 series, 5 categories
{"", "Amount", "Values", "Others"}, // series title
{"Jan", 321d, 456d, 222d}, // category 1
{"Feb", 543d, 567d, 111d}, // category 2
{"Mar", 432d, 123d, 333d}, // category 3
{"Apr", 210d, 234d, 444d}, // category 4
{"May", 198d, 345d, 444d} // category 5
};
XMLSlideShow slideShow = new XMLSlideShow(new FileInputStream(filePath));
XSLFChart chart = slideShow.getCharts().get(0);
updateChart(chart, data);
FileOutputStream out = new FileOutputStream(filePathNew);
slideShow.write(out);
out.close();
slideShow.close();
}
}
结果如下:
提示:该代码使用了 XSSFTable.updateHeaders 的修补版本，因为当前版本无法更新表头.请参阅 重命名带有 Apache Poi 的 XSSFTable 导致 XLSX 文件损坏.
Hint: The code uses a patched version of XSSFTable.updateHeaders as the current version fails updating the table headers. See Renaming headers of XSSFTable with Apache Poi leads to corrupt XLSX-file.
这篇关于Java使用poi编辑ppt中的条形图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
