poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Yegor Kozlov <yegor.koz...@dinom.ru>
Subject Re: Updating Charts in a PPTX template
Date Fri, 02 Dec 2011 09:16:28 GMT
We don't have a usermodel API to work with charts in PPTX. You took
the right approach because direct manipulation with low-level xml
beans is the only way to do that.

On Thu, Dec 1, 2011 at 8:50 AM, Kerwin <kerwin747@gmail.com> wrote:
> Hi All,
>
> I have a PPTX template with multiple charts as placeholders that need to be
> updated with data.
> I could not find documentation on how to update a given chart in a PPTX
> file directly. I have written some code below to be able to do this. Could
> you please let me know if this is the best way to update multiple charts in
> a PPTX file?.
> I essentially read the chart.xml and then read the related embedded Excel
> package. I am successful in reading the chart.xml but cannot read the
> embedded excel XML accurately as I could not find methods to differentiate
> the series and values using getsheetdata(). If I use XSSF I am able to
> update the excel but when I click on the sheet and return to the chart the
> additional series added is removed. Please suggest any better way to
> update the chart.
>

There can be many reasons why the added series is disappearing. Most
likely you don't update all the required xml bits.

Open the template with chart placeholders in PowerPoint and update the
data.  Then unzip the updated file  and compare it with the source
template and with a file updated by your code. This way you can figure
out what you missed .

Yegor

> Code:
>
> import java.io.FileInputStream;
> import java.io.FileOutputStream;
> import java.util.ArrayList;
> import java.util.List;
> import org.apache.poi.openxml4j.opc.OPCPackage;
> import org.apache.poi.openxml4j.opc.PackagePart;
> import org.apache.poi.openxml4j.opc.PackagePartName;
> import org.apache.poi.openxml4j.opc.PackageRelationship;
> import org.apache.poi.xslf.usermodel.XMLSlideShow;
> import org.apache.poi.xssf.model.SharedStringsTable;
> import org.apache.poi.xssf.usermodel.XSSFSheet;
> import org.apache.poi.xssf.usermodel.XSSFWorkbook;
> import org.openxmlformats.schemas.drawingml.x2006.chart.CTAxDataSource;
> import org.openxmlformats.schemas.drawingml.x2006.chart.CTChart;
> import org.openxmlformats.schemas.drawingml.x2006.chart.CTChartSpace;
> import org.openxmlformats.schemas.drawingml.x2006.chart.CTExternalData;
> import org.openxmlformats.schemas.drawingml.x2006.chart.CTLineChart;
> import org.openxmlformats.schemas.drawingml.x2006.chart.CTLineSer;
> import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumData;
> import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumDataSource;
> import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumRef;
> import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumVal;
> import org.openxmlformats.schemas.drawingml.x2006.chart.CTStrData;
> import org.openxmlformats.schemas.drawingml.x2006.chart.CTStrRef;
> import org.openxmlformats.schemas.drawingml.x2006.chart.CTStrVal;
> import org.openxmlformats.schemas.drawingml.x2006.chart.ChartSpaceDocument;
> import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
> import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCol;
> import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCols;
> import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRow;
> import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRst;
> import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet;
> import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetData;
> import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheets;
> import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbook;
> import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet;
> import org.openxmlformats.schemas.spreadsheetml.x2006.main.WorkbookDocument;
>
> public class POIReadXMLANDXLS {
>  public static void main(String args[]){
>  try {
>
>  OPCPackage ppt =OPCPackage.open("Presentation1.pptx");
>  ArrayList<PackagePart> packlst = ppt.getParts();
>  for(PackagePart part :packlst ){
>
>
> if(part.getContentType().equals("application/vnd.openxmlformats-officedocument.drawingml.chart+xml")){
>
>    //PROCESS CHART.XML
>    ChartSpaceDocument chartSpace =
> ChartSpaceDocument.Factory.parse(part.getInputStream());
>    CTChartSpace chartxml = chartSpace.getChartSpace();
>    CTChart chart = chartxml.getChart();
>    List<CTLineChart> linechartlist= chart.getPlotArea().getLineChartList();
>
>     for (CTLineChart lineChart : linechartlist){
>     List<CTLineSer> CTLineSerlst =lineChart.getSerList();
>     Integer i = new Integer(1);
>     for (CTLineSer ser : CTLineSerlst){
>      //Get Category and save
>      CTAxDataSource category =ser.getCat();
>      CTStrRef strref = category.getStrRef();
>      CTStrData strData =strref.getStrCache();
>      List<CTStrVal> strValuelst = strData.getPtList();
>      for (CTStrVal strValue :strValuelst ){
>       System.out.println(strValue.getV());
>       strValue.setV(strValue.getV() + i.toString());
>       i++;
>      }
>      //Get values and save
>      CTNumDataSource value= ser.getVal();
>      CTNumRef numref =value.getNumRef();
>      CTNumData numData =numref.getNumCache();
>      List<CTNumVal> numvalLst = numData.getPtList();
>      for (CTNumVal numval : numvalLst){
>       numval.setV("10000" );
>      }
>     }
>    }
>
>    ppt.removePart(part.getPartName());
>    PackagePart newpart = ppt.createPart(part.getPartName(),
> part.getContentType());
>    newpart.load(chartSpace.newInputStream());
>    //CHANGE THE EMBEDDED EXCEL
>    CTExternalData externalData = chartxml.getExternalData();
>    String id = externalData.getId();
>    PackageRelationship relation= part.getRelationship(id);
>    XMLSlideShow pptslideshow = new XMLSlideShow(new
> FileInputStream("Presentation1.pptx"));
>       List<PackagePart> embeds= pptslideshow.getAllEmbedds();
>       for ( PackagePart excelpart:embeds){
>    PackagePartName name= excelpart.getPartName();
>     if (name.toString().equals(relation.getTargetURI().toString())){
>      XSSFWorkbook workBook = new XSSFWorkbook(excelpart.getInputStream());
>      XSSFSheet sheet = workBook.getSheetAt(0);
>      SharedStringsTable sharedString= workBook.getSharedStringSource();
>      List<CTRst> rlst =sharedString.getItems();
>      Integer i = new Integer(1);
>      for (CTRst rst :rlst){
>      //Add some different logic in here to get rows
>       if (rst.getT().indexOf("Series")==-1){
>        rst.setT("203" + i.toString());
>        i++;
>       }
>      }
>      CTWorksheet worksheet = sheet.getCTWorksheet();
>      CTSheetData sheetdata = worksheet.getSheetData();
>
>      List<CTRow> rowlst =sheetdata.getRowList();
>      for (CTRow row : rowlst){
>       List<CTCell> celllst = row.getCList();
>       for (CTCell cell:celllst ){
>        //updating cells of type numbers(n)
>        if (!cell.getT().toString().equals("s")){
>         cell.setV("10000");
>        }
>       }
>      }
>      ppt.removePart(excelpart.getPartName());
>      PackagePart newExcelpart = ppt.createPart(excelpart.getPartName(),
> excelpart.getContentType());
>      workBook.write(newExcelpart.getOutputStream());
>     }
>   }
>    XMLSlideShow ppt2 = new XMLSlideShow(ppt);
>    ppt2.write(new FileOutputStream("NewPresentation.pptx"));
>   }
>  }
>  } catch (Exception e) {
>  // TODO Auto-generated catch block
>  e.printStackTrace();
>  }
> }
> }

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org


Mime
View raw message