https://bz.apache.org/bugzilla/show_bug.cgi?id=59141
Bug ID: 59141
Summary: Unable to write to embedded excel file in powerpoint
bar chart
Product: POI
Version: 3.13-FINAL
Hardware: Macintosh
Status: NEW
Severity: blocker
Priority: P2
Component: XSLF
Assignee: dev@poi.apache.org
Reporter: arif.shaikh...@gmail.com
Created attachment 33647
--> https://bz.apache.org/bugzilla/attachment.cgi?id=33647&action=edit
Powerpoint containing bar chart to update
I have a powerpoint (.pptx) file containing a bar chart template that I want to
update programatically. Apache POI throws run time exception while trying to
get the outputStream object of the embedded excel sheet file in bar chart.
Exception stacktrace:
Exception in thread "main"
org.apache.poi.openxml4j.exceptions.OpenXML4JRuntimeException: Rule M2.4
exception : this error should NEVER happen! Please raise a bug at
https://bz.apache.org/bugzilla/enter_bug.cgi?product=POI and attach a file that
triggers it, thanks!
at
org.apache.poi.openxml4j.opc.internal.ContentTypeManager.getContentType(ContentTypeManager.java:343)
at
org.apache.poi.openxml4j.opc.internal.ContentTypeManager.removeContentType(ContentTypeManager.java:256)
at org.apache.poi.openxml4j.opc.OPCPackage.removePart(OPCPackage.java:943)
at
org.apache.poi.openxml4j.opc.PackagePart.getOutputStream(PackagePart.java:522)
at com.company.Main.updateBarChartTemplate(Main.java:114)
at com.company.Main.main(Main.java:37)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:140)
Couldn't attach the .java file as there is only one file attachment option,
hence pasting the code below just in case you need it.
package com.company;
import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xslf.usermodel.XMLSlideShow;
import org.apache.poi.xslf.usermodel.XSLFChart;
import org.apache.poi.xslf.usermodel.XSLFSlide;
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 org.openxmlformats.schemas.drawingml.x2006.main.CTTextBody;
import org.openxmlformats.schemas.drawingml.x2006.main.CTTextParagraph;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.Map;
public class Main {
public static void main(String[] args) {
String inputPath = "excel_writing_bug.pptx";
String outputPath = "excel_writing_bug_output.pptx";
int slideNumber = 1;
String templateChartTitle = "Title";
String newChartTitle = "Title updated";
Map<String, Double> chartValues = new HashMap<>();
chartValues.put("Windows", 5d);
chartValues.put("Solaris", 2d);
chartValues.put("Unix", 8d);
chartValues.put("Linux", 5d);
try {
updateBarChartTemplate(inputPath, outputPath, slideNumber,
templateChartTitle, newChartTitle, chartValues);
} catch (IOException e) {
e.printStackTrace();
}
}
public static void updateBarChartTemplate(String inputPath, String
outputPath, int slideNumber, String templateChartTitle, String newChartTitle,
Map<String, Double> chartValues)
throws IllegalStateException, IOException{
XMLSlideShow pptx = null;
try {
pptx = new XMLSlideShow(new FileInputStream(inputPath));
XSLFChart chart = getChartFromTitle(inputPath, slideNumber,
templateChartTitle);
if (chart == null) throw new IllegalStateException("chart not found
in the template");
// embedded Excel workbook that holds the chart data
POIXMLDocumentPart xlsPart = chart.getRelations().get(0);
XSSFWorkbook wb = new XSSFWorkbook();
OutputStream xlsOut = null;
try {
XSSFSheet sheet = wb.createSheet();
CTChart ctChart = chart.getCTChart();
CTPlotArea plotArea = ctChart.getPlotArea();
CTBarChart barChart = plotArea.getBarChartArray(0);
//Bar Chart Series
CTBarSer ser = barChart.getSerArray(0);
// Series Text
CTSerTx tx = ser.getTx();
tx.getStrRef().getStrCache().getPtArray(0).setV(newChartTitle);
sheet.createRow(0).createCell(1).setCellValue(newChartTitle);
String titleRef = new CellReference(sheet.getSheetName(), 0, 1,
true, true).formatAsString();
tx.getStrRef().setF(titleRef);
// Category Axis Data
CTAxDataSource cat = ser.getCat();
CTStrData strData = cat.getStrRef().getStrCache();
// Values
CTNumDataSource val = ser.getVal();
CTNumData numData = val.getNumRef().getNumCache();
strData.setPtArray(null); // unset old axis text
numData.setPtArray(null); // unset old values
// set model
int idx = 0;
int rownum = 1;
for (Map.Entry<String, Double> entry : chartValues.entrySet())
{
String key = entry.getKey();
String value = String.valueOf(entry.getValue());
CTNumVal numVal = numData.addNewPt();
numVal.setIdx(idx);
numVal.setV(value);
CTStrVal sVal = strData.addNewPt();
sVal.setIdx(idx);
sVal.setV(key);
idx++;
XSSFRow row = sheet.createRow(rownum++);
row.createCell(0).setCellValue(key);
row.createCell(1).setCellValue(Double.valueOf(value));
}
numData.getPtCount().setVal(idx);
strData.getPtCount().setVal(idx);
String numDataRange = new CellRangeAddress(1, rownum - 1, 1,
1).formatAsString(sheet.getSheetName(), true);
val.getNumRef().setF(numDataRange);
String axisDataRange = new CellRangeAddress(1, rownum - 1, 0,
0).formatAsString(sheet.getSheetName(), true);
cat.getStrRef().setF(axisDataRange);
// updated the embedded workbook with the data
xlsOut = xlsPart.getPackagePart().getOutputStream();
try {
wb.write(xlsOut);
} finally {
xlsOut.close();
}
FileOutputStream fos = new FileOutputStream(outputPath);
try {
pptx.write(fos);
} finally {
fos.close();
}
} finally {
wb.close();
}
} finally {
if (pptx != null) pptx.close();
}
}
public static XSLFChart getChartFromTitle(String inputPath, int
slideNumber, String templateChartTitle) throws IOException {
XMLSlideShow pptx = null;
try {
pptx = new XMLSlideShow(new FileInputStream(inputPath));
XSLFSlide slide = pptx.getSlides().get(slideNumber - 1);
// find chart in the slide
XSLFChart chart = null;
for (POIXMLDocumentPart part : slide.getRelations()) {
if (part instanceof XSLFChart) {
chart = (XSLFChart) part;
CTChart ctChart = chart.getCTChart();
CTTitle ctTitle = ctChart.getTitle();
CTTx titleTx = ctTitle.getTx();
CTTextBody body = titleTx.getRich();
CTTextParagraph paragraph = body.getPArray(0);
String chartTitle = paragraph.getRArray(0).getT();
if (chartTitle.equalsIgnoreCase(templateChartTitle)) {
break;
} else {
chart = null;
}
}
}
return chart;
} finally {
if (pptx != null) pptx.close();
}
}
}
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscr...@poi.apache.org
For additional commands, e-mail: dev-h...@poi.apache.org
