BarChart is not refreshed in PPT after updating value in Data File

[Saravvij <sa...@gmail.com>]

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

Re: BarChart is not refreshed in PPT after updating value in Data File

[Nick Burch <ap...@gagravarr.org>]

On Fri, 26 Jul 2013, Saravvij wrote:
> 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.

Yup, that's expected. When you embed one office document in another, it 
puts in both the file and a pre-rendered version. That means that if you 
open the file on a computer that doesn't have the software to process the 
embedded file, just the outer one, it still looks fine. If memory serves, 
the pre-rendered version is normally an EMF

I *think* that if you zap the pre-rendered version, then when it opens 
it'll either give a nasty read cross (no software to process the embedded 
document) or re-render it for you (software can handle both outer and 
inner). Check the list archives for details, I'm fairly sure someone has 
been down this road before and fed back on it

Nick

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