[Bug 59141] New: Unable to write to embedded excel file in powerpoint bar chart

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

[Bug 59141] New: Unable to write to embedded excel file in powerpoint bar chart

Bugzilla from bugzilla@apache.org
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: [hidden email]
          Reporter: [hidden email]

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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 59141] Unable to write to embedded excel file in powerpoint bar chart

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=59141

Arif Shaikh <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 OS|                            |All

--
You are receiving this mail because:
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 59141] Unable to write to embedded excel file in powerpoint bar chart

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=59141

Arif Shaikh <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |[hidden email]

--
You are receiving this mail because:
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 59141] Unable to write to embedded excel file in powerpoint bar chart

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=59141

--- Comment #1 from Arif Shaikh <[hidden email]> ---
Created attachment 33648
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=33648&action=edit
Testcase for bug

Test case for the bug

--
You are receiving this mail because:
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 59141] Unable to write to embedded excel file in powerpoint bar chart

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=59141

--- Comment #2 from Alain Fagot Bearez <[hidden email]> ---
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.

https://github.com/apache/poi/pull/68

In that branch you would find a BarChartDemo under the examples, which shows
how to access the charts without closing the presentation.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]