Bug 59141 - Unable to write to embedded excel file in powerpoint bar chart
Summary: Unable to write to embedded excel file in powerpoint bar chart
Alias: None
Product: POI
Classification: Unclassified
Component: XSLF (show other bugs)
Version: 3.13-FINAL
Hardware: Macintosh All
: P2 blocker (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2016-03-08 07:16 UTC by Arif Shaikh
Modified: 2018-02-10 14:39 UTC (History)
1 user (show)

Powerpoint containing bar chart to update (83.54 KB, application/vnd.openxmlformats-officedocument.presentationml.presentation)
2016-03-08 07:16 UTC, Arif Shaikh
Testcase for bug (5.27 KB, text/plain)
2016-03-08 09:05 UTC, Arif Shaikh

Note You need to log in before you can comment on or make changes to this bug.
Description Arif Shaikh 2016-03-08 07:16:58 UTC
Created attachment 33647 [details]
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) {

	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();
				String titleRef = new CellReference(sheet.getSheetName(), 0, 1, true, true).formatAsString();

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

					CTStrVal sVal = strData.addNewPt();

					XSSFRow row = sheet.createRow(rownum++);

				String numDataRange = new CellRangeAddress(1, rownum - 1, 1, 1).formatAsString(sheet.getSheetName(), true);
				String axisDataRange = new CellRangeAddress(1, rownum - 1, 0, 0).formatAsString(sheet.getSheetName(), true);

				// updated the embedded workbook with the data
				xlsOut = xlsPart.getPackagePart().getOutputStream();
				try {
				} finally {
				FileOutputStream fos = new FileOutputStream(outputPath);
				try {
				} finally {
			} finally {
		} 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)) {
					} else {
						chart = null;
			return chart;
		} finally {
			if (pptx != null) pptx.close();
Comment 1 Arif Shaikh 2016-03-08 09:05:54 UTC
Created attachment 33648 [details]
Testcase for bug

Test case for the bug
Comment 2 Alain Fagot Bearez 2017-09-13 17:43:56 UTC
In your code, you open twice the same file but you try to change the chart after its slideshow has been closed.  This is the cause of the Rule M2.4 exception.

Would you mind trying to adapt your code in order to avoid this?

You might also be interested in trying the code available in the pull request #68 which could make it easier to define new values for your chart.


In that branch you would find a BarChartDemo under the examples, which shows how to access the charts without closing the presentation.
Comment 3 Alain Fagot Bearez 2018-02-10 14:39:29 UTC
A working example has been made available in r1816383
as org.apache.poi.xslf.usermodel.BarChartDemo