Creating Excel Chart and Embedding it in Presentation as OLE Object

Skip to end of metadata
Go to start of metadata
A very common question asked by Aspose.Slides for Java customers is about charts support. Using Aspose components, charts are created in MS Excel format using Aspose.Cells for Java and then embedded as OLE Object using Aspose.Slides for Java.

Steps for adding charts in presentation

Following sequence of steps is required to achieve this:

  • Create an excel chart using Aspose.Cells.
  • Set the OLE size of the chart. using Aspose.Cells.
  • Get the image of the chart with Aspose.Cells.
  • Embed the chart as an OLE object inside .ppt presentation using Aspose.Slides.
  • Replace the object changed image with the image obtained in step 3 to cater Object Changed Issue
  • Write the output presentation on disk

The implementation of the above steps in Java is as under:

Example

[Java]


public static void Run() {
try
{
    //Create a workbook
    Workbook wb = new Workbook();
    //Add an excel chart
    int chartRows = 55;
    int chartCols = 25;
    int chartSheetIndex = AddExcelChartInWorkbook(wb, chartRows, chartCols);	 
    //Set chart ole size
    wb.setOleSize(0, chartRows, 0, chartCols);
    //Get the chart image and save to stream
    ByteArrayOutputStream imageStream=new ByteArrayOutputStream();

    Worksheet work=wb.getWorksheets().get(chartSheetIndex);
    //Getting first chart of sheet
    Chart chart=work.getCharts().get(0);
    chart.toImage(imageStream, new ImageOrPrintOptions());  
	
    //Save the workbook to stream
    ByteArrayOutputStream bout=new ByteArrayOutputStream();
    wb.save(bout,FileFormatType.EXCEL_97_TO_2003);
    //Create a presentation
    Presentation pres = new Presentation();
    Slide sld = pres.getSlideByPosition(1);
    //Add the workbook on slide
    AddExcelChartInPresentation(pres, sld, bout.toByteArray(), imageStream.toByteArray());	
    //Write the presentation to disk
    pres.write(new FileOutputStream("d:\\outputJ.ppt"));           
}catch(Exception e){
      e.printStackTrace();
     }
}

static int AddExcelChartInWorkbook(Workbook wb, int chartRows, int chartCols)
{
    //Array of cell names 
    String[] cellsName = new String[] 
    { 
	"A1", "A2", "A3", "A4",
	"B1", "B2", "B3", "B4",
	"C1", "C2", "C3", "C4",
	"D1", "D2", "D3", "D4",
	"E1", "E2", "E3", "E4"
    };
    //Array of cell data
    int[] cellsValue = new int[] 
    {
	67,86,68,91,
	44,64,89,48,
	46,97,78,60,
	43,29,69,26,
	24,40,38,25
    };
    //Add a new worksheet to populate cells with data
    int  dataSheetIndex =wb.getWorksheets().add();//addSheet();
    Worksheet dataSheet= wb.getWorksheets().get(dataSheetIndex);
    String sheetName = "DataSheet";
    dataSheet.setName(sheetName);
    //Populate DataSheet with data
    int size=java.lang.reflect.Array.getLength(cellsName);  
	
	
    for (int i = 0; i < size; i++)
    {
	String cellName = cellsName[i];
	int cellValue = cellsValue[i];
	dataSheet.getCells().get(cellName).setValue(cellValue);
    }
    //Add a chart sheet
    int chartSheetIndex = wb.getWorksheets().add(SheetType.CHART);
    Worksheet chartSheet = wb.getWorksheets().get(chartSheetIndex);
    chartSheet.setName("ChartSheet");
    int chartSheetIdx = chartSheet.getIndex();	        
    //Add a chart in ChartSheet with data series from DataSheet
    int chartIndex = chartSheet.getCharts().add(ChartType.COLUMN, 0, chartRows, 0, chartCols);
    
    Chart chart = chartSheet.getCharts().get(chartIndex);
	
    chart.getNSeries().add(sheetName + "!A1:E1", false);
    chart.getNSeries().add(sheetName + "!A2:E2", false);
    chart.getNSeries().add(sheetName + "!A3:E3", false);
    chart.getNSeries().add(sheetName + "!A4:E4", false);
    //Set ChartSheet as active sheet
    wb.getWorksheets().setActiveSheetIndex(chartSheetIdx);
    return chartSheetIdx;
}
	
static void AddExcelChartInPresentation(Presentation pres, Slide sld, byte[] wbArray,    byte[] imgChart) throws Exception
{
    com.aspose.slides.Picture pic=new com.aspose.slides.Picture(pres,new ByteArrayInputStream(imgChart));
    int picId = pres.getPictures().add(pic);
    int slideWidth = (int)pres.getSlideSize().getX() - 1500;
    int slideHeight = (int)pres.getSlideSize().getY();
    int x = 1500 / 2;
    OleObjectFrame oof = sld.getShapes().addOleObjectFrame(x, 0, slideWidth, slideHeight, "Excel.Sheet.8", wbArray);
    oof.setPictureId(picId);
}

The presentation created through above method, will carry the Excel chart as OLE Object that can be activated by double clicking the OLE Frame.

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.