DEV Community

loading...

Embed Excel in PowerPoint as OLE Object in Java

eiceblue profile image E-iceblue Product Family ・3 min read

If you want to distribute a PowerPoint presentation that contains an Excel spreadsheet to your colleagues or customers, embedding the Excel data to the presentation is the best choice, because readers can easily open and view, or even make changes to the Excel data with no need to have access to the original Excel document.

In this article, I am going to introduce how to embed Excel in PowerPoint as OLE object by using Spire.Office for Java.

Installing Spire.Office.jar

If you create a Maven project, you can easily import the jar in your application using the following configurations. For non-Maven projects, download the jar file from this link and add it as a dependency in your application.

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>http://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId> e-iceblue </groupId>
        <artifactId>spire.office</artifactId>
        <version>4.2.0</version>
    </dependency>
</dependencies>
Enter fullscreen mode Exit fullscreen mode

Main steps involved

Step 1. Create an instance of Workbook class which is under the com.spire.xls.Workbook namespace, to load an Excel document. Call saveToImage(int firstRow, int firstColumn, int lastRow, int lastColumn) method to convert the selected cell range to an image. The image will be inserted to PowerPoint and displayed as an icon for the Excel to be embedded. You can also specify any other image as the icon.

Step 2. Create a Presentation object. You can use it either to create a new PowerPoint document or to load an existing PowerPoint file. And then, append the image to ImageCollection of the presentation.

Step 3. Convert the Excel document to byte[], which is one of the key parameters for creating OLE object.

Step 4. Append OLE object to slide, then specify the OLE object type and display image.

Using the code

import com.spire.presentation.FileFormat;
import com.spire.presentation.IOleObject;
import com.spire.presentation.Presentation;
import com.spire.presentation.SlideSizeType;
import com.spire.presentation.drawing.IImageData;
import com.spire.xls.Workbook;

import java.awt.geom.Rectangle2D;
import java.awt.image.BufferedImage;
import java.io.File;
import java.io.FileInputStream;

public class EmbedExcel {

    public static void main(String[] args) throws Exception {

        //Specify excel path
        String excelPath = "C:\\Users\\Administrator\\Desktop\\Product.xlsx";

        //Create a Workbook object to load excel file
        Workbook wb = new Workbook();
        wb.loadFromFile(excelPath);

        //Set the margins to 0 so that only the selected cell range data will display in slide
        wb.getWorksheets().get(0).getPageSetup().setBottomMargin(0);
        wb.getWorksheets().get(0).getPageSetup().setTopMargin(0);
        wb.getWorksheets().get(0).getPageSetup().setLeftMargin(0);
        wb.getWorksheets().get(0).getPageSetup().setRightMargin(0);

        //Convert the selected range to image
        BufferedImage image = wb.getWorksheets().get(0).saveToImage(1,1,5,5);

        //Create a Presentation object
        Presentation ppt = new Presentation();
        ppt.getSlideSize().setType(SlideSizeType.SCREEN_16_X_9);

        //Append image to ppt
        IImageData oleImage = ppt.getImages().append(image);

        //Load the excel file and convert it to byte[] object
        File excelFile = new File(excelPath);
        FileInputStream inputStream = new FileInputStream(excelFile);
        byte[] data = new byte[(int) excelFile.length()];
        inputStream.read(data, 0, data.length);

        //Create a Rectangle2D object
        Rectangle2D rect = new Rectangle2D.Float(60, 60, image.getWidth(), image.getHeight());

        //Insert the Excel file as an OLE object to the first slide
        IOleObject oleObject = ppt.getSlides().get(0).getShapes().appendOleObject("excel", data, rect);
        oleObject.getSubstituteImagePictureFillFormat().getPicture().setEmbedImage(oleImage);
        oleObject.setProgId("Excel.Sheet.12");

        //Save to another file
        ppt.saveToFile("InsertOle.pptx", FileFormat.PPTX_2013);
    }
}
Enter fullscreen mode Exit fullscreen mode

Alt Text

Discussion (0)

pic
Editor guide