poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Saravvij <sarav...@gmail.com>
Subject BarChart is not refreshed in PPT after updating value in Data File
Date Fri, 26 Jul 2013 20:17:55 GMT
Hi, I have a pptx template, it has just 1 slide for testing purpose. The
slide has a simple bar chart. I am able to edit the bar chart by double
clicking it on pptx file and I could change the values in Sheet1 (Data sheet
for Barchart), and, I able to see the changes immediately in BarChart.

Now, I am trying to do the same using POI API. I am doing the below steps
here

1. Read the template file
2. Have all shapes in Map
3. Read the BarChart shape by referring it's name
4. Read the excel file of the BarChart
5. Update a cell Value in Sheet1
6. Save everything and write into another file - "MyPresentation.pptx"

When I open the file - "MyPresentation.pptx", it does not show up the
updated cell value upfront in the Bar. I need to double click the chart to
change it to EDIT mode to get the latest value reflected. Why does BarChart
is not getting refreshed when it's underlying Data Sheet is updated using
POI?

Please advise me.


Here is the completed code, attached pptx template file as well.

package com.ppt;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.PackagePart;
import org.apache.poi.xslf.usermodel.XMLSlideShow;
import org.apache.poi.xslf.usermodel.XSLFGraphicFrame;
import org.apache.poi.xslf.usermodel.XSLFShape;
import org.apache.poi.xslf.usermodel.XSLFSheet;
import org.apache.poi.xslf.usermodel.XSLFSlide;

public class PPTChart {

	public static void main(String args[]) throws InvalidFormatException,
IOException{ 
		
		XMLSlideShow ppt;
	
		// Read pptx template
		ppt = new XMLSlideShow(new FileInputStream("MyTemplate.pptx"));

		// Get all slides
		XSLFSlide[] slide = ppt.getSlides();
		
		// Get working slide that is slide=0
		XSLFSlide slide0 = slide[0];
		XSLFShape[] shapes = slide0.getShapes();
		
		// Add all shapes into a Map
		Map <String, XSLFShape> shapesMap = new HashMap<String, XSLFShape>();
		for(XSLFShape shape : shapes)
		{
			shapesMap.put(shape.getShapeName(), shape);
			System.out.println(shape.getShapeName() + "  " + shape.getShapeId() + "  
" + shape);
		
		}
		
		// Read the bar chart
		XSLFGraphicFrame chart = (XSLFGraphicFrame) shapesMap.get("MyBarChart");
		
		// Get the chart sheet
		XSLFSheet sheet =  chart.getSheet();
		
		for(int i=0; i<sheet.getRelations().size(); i++)
		{
			System.out.println("Partname =" +
sheet.getRelations().get(i).getPackagePart().getPartName());
			
			

		
if(sheet.getRelations().get(i).getPackagePart().getPartName().toString().contains(".xls"))
			{
				
				System.out.println("Found the bar chart excel");
				
				// BarChart Excel package part
				PackagePart barChartExcel  =
sheet.getRelations().get(i).getPackagePart();
				
				// Reference the excel in workbook
				HSSFWorkbook wb = new HSSFWorkbook(barChartExcel.getInputStream());
				
				// Read sheet where Barchart data is available
				HSSFSheet mysheet =  wb.getSheetAt(1);
			
				// Read first
				HSSFRow row = mysheet.getRow(1);
				
				
				//Print first cell value for debugging
				System.out.println("Updating cell value from - " + row.getCell(1));
				
				// New value
				double insertValue = 7777777.0;
				
				
				wb.getSheetAt(1).getRow(1).getCell(1).setCellValue(insertValue);
		
				// Set first BarChart as active sheet
				HSSFSheet mysheet0 =  wb.getSheetAt(0);
				mysheet0.setActive(true);
				
				// Write the updated excel back to workbook
				OutputStream excelOut = barChartExcel.getOutputStream();
				excelOut.flush();
				wb.write(excelOut);
				excelOut.close();

				// Write workbook to file
				FileOutputStream o = new FileOutputStream("C:\\MyPresentation.pptx");
				ppt.write(o);
				o.close();
				System.out.println("new ppt is created....");
				
				break; // Exit
			}
	
		}
	}
}




--
View this message in context: http://apache-poi.1045710.n5.nabble.com/BarChart-is-not-refreshed-in-PPT-after-updating-value-in-Data-File-tp5713347.html
Sent from the POI - User mailing list archive at Nabble.com.

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


Mime
View raw message