poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bugzi...@apache.org
Subject [Bug 59141] New: Unable to write to embedded excel file in powerpoint bar chart
Date Tue, 08 Mar 2016 07:16:58 GMT
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.shaikh786@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-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org


Mime
View raw message