How to Create & Embed Excel Chart as OLE Object in PPTX Presentation Using Java

This technical tip shows how java developers can create Excel chart and embed it in presentation as OLE object using Aspose.Slides for Java.  In PowerPoint Slides, the use of editable chats for graphical display of the data is a common activity. Aspose provides the support of creating the Excel Charts with the use of Aspose.Cells for Java and further these charts can be embedded as an OLE Object in the PowerPoint Slide through Aspose.Slides for Java. This article covers the required steps along with the implementation in Java to create and embed an MS Excel Chart as an OLE Object in PowerPoint presentation by using Aspose.Cells for Java and Aspose.Slides for Java.  Following sequence of steps is required to create and embed an Excel Chart as an OLE Object in the PowerPoint Slide:

• Create an Excel Chart using Aspose.Cells for Java.
• Set the OLE size of the Excel Chart. using Aspose.Cells for Java.
• Get the image of the Excel Chart with Aspose.Cells for Java.
• Embed the Excel Chart as an OLE Object inside PPTX presentation using Aspose.Slides for Java.
• Replace the object changed image with the image obtained in step 3 to cater Object Changed Issue
• Save the output presentation to disk in PPTX format

public static void Run()
{
      //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.getWorksheets().setOleSize(0, chartRows, 0, chartCols);
   //Get the chart image and save to stream
   com.aspose.cells.ImageOrPrintOptions opts= new com.aspose.cells.ImageOrPrintOptions();
   opts.setImageFormat(com.aspose.cells.ImageFormat.getPng());
   ByteArrayOutputStream imageStream=new ByteArrayOutputStream();
   wb.getWorksheets().get(chartSheetIndex).getCharts().get(0).toImage(imageStream, opts);
   //Save the workbook to stream
   ByteArrayOutputStream bout=new ByteArrayOutputStream();

   wb.save(bout,com.aspose.cells.SaveFormat.EXCEL_97_TO_2003);
   //Create a presentation
   Presentation pres = new Presentation();
   ISlide sld = pres.getSlides().get_Item(0);
   //Add the workbook on slide
   AddExcelChartInPresentation(pres, sld, bout.toByteArray(), imageStream.toByteArray());
   //Write the presentation to disk
   pres.save(path + "outputJ.pptx", SaveFormat.Pptx);
}


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();
    Worksheet dataSheet =wb.getWorksheets().get(dataSheetIndex);
    String sheetName = "DataSheet";
    dataSheet.setName(sheetName);
    //Populate DataSheet with data
    int size= 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 WorksheetIndex = wb.getWorksheets().add(SheetType.CHART);
    Worksheet chartSheet = wb.getWorksheets().get(WorksheetIndex);
    chartSheet.setName("ChartSheet");
    int chartSheetIdx = chartSheet.getIndex();
    //Add a chart in ChartSheet with data series from DataSheet
    int chIndex = chartSheet.getCharts().add(ChartType.COLUMN, 0, chartRows, 0, chartCols);
    Chart chart=chartSheet.getCharts().get(chIndex);

    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, ISlide sld, byte[] wbArray,    byte[] imgChart) throws Exception
{
    double oleHeight = pres.getSlideSize().getSize().getHeight();
    double oleWidth = pres.getSlideSize().getSize().getWidth();
    Workbook wb=new Workbook();

    wb.open(new ByteArrayInputStream(wbArray),com.aspose.cells.SaveFormat.EXCEL_97_TO_2003);

    IOleObjectFrame oof = sld.getShapes().addOleObjectFrame(0f, 0f, (float)oleWidth, (float)oleHeight, "Excel.Sheet.8", wbArray);
    oof.getSubstitutePictureFormat().getPicture().setImage(pres.getImages().addImage(new ByteArrayInputStream(imgChart)));

}