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.
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();
}
}
}
