Support for Worksheet Preview in PowerPoint Presentation in Java

RAVI_DEVIREDDY · July 3, 2023, 1:20pm

Hi Team - we are currently using the Aspose slides 19.8 and cells-197. for java to prepare the PPT and worksheet individually.

But as per latest requirements from the clients , now we have to insert the worksheet preview ( when we open the slide the worksheet should show first few rows say 25 ) into slide and when we click on the preview it has to open the full worksheet.

Please let us know how can this is doable .

andrey.potapov · July 3, 2023, 3:01pm

@RAVI_DEVIREDDY,
Thank you for posting your requirements.

If I understand you correctly, you need to create OLE data and an image from the workbook. Then you will be able to add an OLE object frame to a presentation and set the image to the frame. The following code example shows you how to do this using Aspose.Slides:

var presentation = new Presentation();
var slide = presentation.getSlides().get_Item(0);

var dataInfo = new OleEmbeddedDataInfo(oleStream.toByteArray(), "xlsx");
var oleFrame = slide.getShapes().addOleObjectFrame(20, 20, 300, 700, dataInfo);

var pptImage = presentation.getImages().addImage(imageStream.toByteArray());
oleFrame.getSubstitutePictureFormat().getPicture().setImage(pptImage);

presentation.save("output.pptx", com.aspose.slides.SaveFormat.Pptx);

My colleagues from Aspose.Cells team will help you generate OLE data (oleStream variable) and the image (imageStream variable) using Aspose.Cells for Java.
@amjad.sahi FYI

amjad.sahi · July 3, 2023, 7:33pm

@RAVI_DEVIREDDY,

Here is the complete example code to accomplish your task for your reference.
e.g.
Sample code:

String dataDir = "f:\\files\\";
// Open an existing Excel workbook
Workbook workbook = new Workbook(dataDir+"Example.xlsx");

// set visible rows and columns count
int cellsRows = 25;
int cellsCols = 11;
Worksheet dataSheet = workbook.getWorksheets().get(0);

// Set print area
dataSheet.getPageSetup().setPrintArea(dataSheet.getCells().get(0, 0).getName() + ":"
+ dataSheet.getCells().get(cellsRows + 1, cellsCols).getName());
// Set ole size
workbook.getWorksheets().setOleSize(0, cellsRows, 0, cellsCols);

// Get the worksheet as an image  for preview in Powerpoint presentation
ImageOrPrintOptions imageOptions = new ImageOrPrintOptions();
imageOptions.setImageType(ImageType.EMF);
imageOptions.setOnlyArea(true);
imageOptions.setOnePagePerSheet(true);
SheetRender sheetRender = new SheetRender(dataSheet, imageOptions);
ByteArrayOutputStream imageStream = new ByteArrayOutputStream();
sheetRender.toImage(0, imageStream);
byte[] imageByteArray = imageStream.toByteArray();

// Save the workbook to stream
ByteArrayOutputStream bout = new ByteArrayOutputStream();
workbook.save(bout, com.aspose.cells.SaveFormat.XLSX);

// Create a presentation
com.aspose.slides.Presentation pres = new com.aspose.slides.Presentation();
com.aspose.slides.ISlide sld = pres.getSlides().get_Item(0);

// Add the workbook to the slide
com.aspose.slides.IOleEmbeddedDataInfo dataInfo = new com.aspose.slides.OleEmbeddedDataInfo(bout.toByteArray(), "xlsx");
com.aspose.slides.IOleObjectFrame oof = sld.getShapes().addOleObjectFrame(1f, 1f,
(float) pres.getSlideSize().getSize().getWidth() - 2, (float) pres.getSlideSize().getSize().getHeight() - 2, dataInfo);
oof.getSubstitutePictureFormat().getPicture().setImage(pres.getImages().addImage(imageByteArray));

// Write the presentation to disk
pres.save(dataDir+"Example-out.pptx", com.aspose.slides.SaveFormat.Pptx);

Also, see the document/article with sample for your further reference.
https://docs.aspose.com/slides/java/creating-excel-chart-and-embedding-it-in-presentation-as-ole-object/

RAVI_DEVIREDDY · July 5, 2023, 11:26am

thanks @amjad.sahifor quick repose , I tried your example and its working but the data of the worksheet/excel is not visible properly .PFA.

Please let me know if we need to do anything for this to set it properly.
Screenshot from 2023-07-05 12-26-01.png (90.9 KB)

amjad.sahi · July 5, 2023, 2:04pm

@RAVI_DEVIREDDY,

Thanks for the screenshot.

Could you please zip and attach your Excel file. We will evaluate your issue and assist you to figure it out for your requirements.

RAVI_DEVIREDDY · July 5, 2023, 2:27pm

dummy-data.zip (7.3 KB)
PFA .

amjad.sahi · July 5, 2023, 2:58pm

@RAVI_DEVIREDDY,

Please try latest version of Aspose.Cells for Java v23.6:

Aspose.Cells for Java (Releases | Maven)

I tested using newer versions of both Aspose.Cells for Java and Aspose.Slides for Java and it works fine. Here is my complete sample (runnable) code for your rerference.
e.g.
Sample code:


        System.setProperty("Aspose.Cells.Disable", "EmfPlus");
        String dataDir = "f:\\files\\";
        // Create a workbook        
        Workbook workbook = new Workbook(dataDir+"dummy-data.xlsx");

        // set visible rows and columns count
        int cellsRows = 25;
        int cellsCols = 11;
        Worksheet dataSheet = workbook.getWorksheets().get(0);
        // Set print area
        dataSheet.getPageSetup().setPrintArea(dataSheet.getCells().get(0, 0).getName() + ":"
                + dataSheet.getCells().get(cellsRows + 1, cellsCols).getName());
        // Set ole size
        workbook.getWorksheets().setOleSize(0, cellsRows, 0, cellsCols);

        // Get the worksheet as an image
        ImageOrPrintOptions imageOptions = new ImageOrPrintOptions();
        imageOptions.setImageType(ImageType.EMF);
        imageOptions.setOnlyArea(true);
        imageOptions.setOnePagePerSheet(true);
        SheetRender sheetRender = new SheetRender(dataSheet, imageOptions);
        ByteArrayOutputStream imageStream = new ByteArrayOutputStream();
        sheetRender.toImage(0, imageStream);
        byte[] imageByteArray = imageStream.toByteArray();

        // Save the workbook to stream
        ByteArrayOutputStream bout = new ByteArrayOutputStream();
        workbook.save(bout, com.aspose.cells.SaveFormat.XLSX);

        // Create a presentation
        com.aspose.slides.Presentation pres = new com.aspose.slides.Presentation();
        com.aspose.slides.ISlide sld = pres.getSlides().get_Item(0);

        // Add the workbook to the slide
        com.aspose.slides.IOleEmbeddedDataInfo dataInfo = new com.aspose.slides.OleEmbeddedDataInfo(bout.toByteArray(), "xlsx");
        com.aspose.slides.IOleObjectFrame oof = sld.getShapes().addOleObjectFrame(1f, 1f,
                (float) pres.getSlideSize().getSize().getWidth() - 2,
                (float) pres.getSlideSize().getSize().getHeight() - 2, dataInfo);
        oof.getSubstitutePictureFormat().getPicture()
                .setImage(pres.getImages().addImage(imageByteArray));

        // Write the presentation to disk
        pres.save(dataDir+"Example-out-dataSummary1.pptx", com.aspose.slides.SaveFormat.Pptx);

Please find attached the output PPTX file for your reference.
Example-out-dataSummary1.zip (50.1 KB)

If you still find the issue with latest version of Aspose.Cells for Java v23.6, please share your sample (runnable) same as above and attach your sample files, we will check it soon.

RAVI_DEVIREDDY · July 5, 2023, 3:59pm

thanks can you please let me know both slides and cells versions ?
is slides 19.8 is ok for this ? #and I have couple of oberservations on this , when you click on the preview its opening but when you try click outside (i.e. want to close the preview and go back to slide) , its going beyond the slide width but now its showing the correct font and data clearly . PFAafter-preview-closed.png (368.3 KB)

RAVI_DEVIREDDY · July 5, 2023, 4:19pm

still am getting the same issue by using the cells-23.6 and slides-19.8 .

package com.mintel.pptworker;

import com.aspose.cells.*;
import com.mintel.pptworker.utils.PPTConstants;

import java.io.ByteArrayOutputStream;

public class AsposeFormCode {

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

    // Open an existing Excel workbook
    com.aspose.slides.License slidesLicense = new com.aspose.slides.License();
    slidesLicense.setLicense(PPTConstants.LICENSE_PATH);
    Workbook workbook = new Workbook(PPTConstants.EXCEL_PATH);

    // set visible rows and columns count
    int cellsRows = 25;
    int cellsCols = 11;
    Worksheet dataSheet = workbook.getWorksheets().get(0);

    // Set print area
    dataSheet.getPageSetup().setPrintArea(dataSheet.getCells().get(0, 0).getName() + ":"
            + dataSheet.getCells().get(cellsRows + 1, cellsCols).getName());
    // Set ole size
    workbook.getWorksheets().setOleSize(0, cellsRows, 0, cellsCols);

    // Get the worksheet as an image  for preview in Powerpoint presentation
    ImageOrPrintOptions imageOptions = new ImageOrPrintOptions();
    imageOptions.setImageType(ImageType.EMF);
    imageOptions.setOnlyArea(true);
    imageOptions.setOnePagePerSheet(true);
    SheetRender sheetRender = new SheetRender(dataSheet, imageOptions);
    ByteArrayOutputStream imageStream = new ByteArrayOutputStream();
    sheetRender.toImage(0, imageStream);
    byte[] imageByteArray = imageStream.toByteArray();

    // Save the workbook to stream
    ByteArrayOutputStream bout = new ByteArrayOutputStream();
    workbook.save(bout, com.aspose.cells.SaveFormat.XLSX);

    // Create a presentation
    com.aspose.slides.Presentation pres = new com.aspose.slides.Presentation();
    com.aspose.slides.ISlide sld = pres.getSlides().get_Item(0);

    // Add the workbook to the slide
    com.aspose.slides.IOleEmbeddedDataInfo dataInfo = new com.aspose.slides.OleEmbeddedDataInfo(bout.toByteArray(), "xlsx");
    com.aspose.slides.IOleObjectFrame oof = sld.getShapes().addOleObjectFrame(1f, 1f,
            (float) pres.getSlideSize().getSize().getWidth() - 2, (float) pres.getSlideSize().getSize().getHeight() - 2, dataInfo);
    oof.getSubstitutePictureFormat().getPicture().setImage(pres.getImages().addImage(imageByteArray));

    // Write the presentation to disk
    pres.save(  "Example-out.pptx", com.aspose.slides.SaveFormat.Pptx);
    System.out.println("Done!!!");
}

}
Example-out.zip (56.5 KB)

RAVI_DEVIREDDY · July 5, 2023, 4:26pm

now am using both versions to 23.6 but still I don’t see proper data rendering to slide , code is same as above .
Example-out.zip (51.3 KB)

amjad.sahi · July 5, 2023, 6:20pm

@RAVI_DEVIREDDY,

Thanks for the sample files.

It is strange that you are getting such an output where data is not visible. I again tested using an updated/enhanced code segment and it works absolutely fine. I even added a line (see the line below) to also render the image to file path, the output preview image is fine and as expected.

sheetRender.toImage(0, "f:\\files\\outimage1.png");

Here is my complete sample (enhanced) code for your reference.
e.g.
Sample code:

        String dataDir = "f:\\files\\";
        // Create a workbook
        Workbook workbook = new Workbook(dataDir+"dummy-data.xlsx");

        // set visible rows and columns count
        int cellsRows = 19;
        int cellsCols = 9;
        Worksheet dataSheet = workbook.getWorksheets().get(0);

        //Autofit rows to fit the size.
        dataSheet.autoFitColumns();

        // Set print area
        dataSheet.getPageSetup().setPrintArea(dataSheet.getCells().get(0, 0).getName() + ":"
                + dataSheet.getCells().get(cellsRows + 1, cellsCols).getName());
        // Set ole size
        workbook.getWorksheets().setOleSize(0, cellsRows, 0, cellsCols);

        // Get the worksheet as an image
        ImageOrPrintOptions imageOptions = new ImageOrPrintOptions();
        imageOptions.setImageType(ImageType.PNG);
        imageOptions.setOnlyArea(true);
        imageOptions.setOnePagePerSheet(true);
        imageOptions.setAllColumnsInOnePagePerSheet(true);
        SheetRender sheetRender = new SheetRender(dataSheet, imageOptions);
        ByteArrayOutputStream imageStream = new ByteArrayOutputStream();
        sheetRender.toImage(0, "f:\\files\\outimage1.png");
        sheetRender.toImage(0, imageStream);

        byte[] imageByteArray = imageStream.toByteArray();

        // Save the workbook to stream
        ByteArrayOutputStream bout = new ByteArrayOutputStream();
        workbook.save(bout, com.aspose.cells.SaveFormat.XLSX);

        // Create a presentation
        com.aspose.slides.Presentation pres = new com.aspose.slides.Presentation();
        com.aspose.slides.ISlide sld = pres.getSlides().get_Item(0);

        // Add the workbook to the slide
        com.aspose.slides.IOleEmbeddedDataInfo dataInfo = new com.aspose.slides.OleEmbeddedDataInfo(bout.toByteArray(), "xlsx");
        com.aspose.slides.IOleObjectFrame oof = sld.getShapes().addOleObjectFrame(1f, 1f,
                (float) pres.getSlideSize().getSize().getWidth() - 2,
                (float) pres.getSlideSize().getSize().getHeight() - 2, dataInfo);
        oof.getSubstitutePictureFormat().getPicture()
                .setImage(pres.getImages().addImage(imageByteArray));

        // Write the presentation to disk
        pres.save(dataDir+"Example-out-dataSummary1.pptx", com.aspose.slides.SaveFormat.Pptx);

Please find attached the zipped archive having rendered (preview) image and output PPTX file.
files1.zip (74.2 KB)

By the way, I am not an expert of Aspose.Slides, so if the OLE object frame in the Powerpoint slide is not placed at your desired location, you may seek help from @andrey.potapov.

andrey.potapov · July 6, 2023, 5:49am

@RAVI_DEVIREDDY,
I get the same result as @amjad.sahi using Aspose.Slides for Java 23.6 and Aspose.Cells for Java 23.6. We need more information to reproduce the problem on our end and help you.

RAVI_DEVIREDDY · July 6, 2023, 7:31am

Hi @andrey.potapov - I tried to generate it on windows machine and it looks better , I can see the attached worksheet data correctly but there are some issues with this as mentioned earlier .

Earlier I generated on Ubuntu , do we have any issues with OS ?
can’t to make the image unmovable on the slide ? now I can drag the image any where it doesn’t look good.
is there any possibility add the scroll bar in the image to scroll through the data without open the worksheet by double click ?
as I said earlier , when I open the slide for the first time I can see the table/image properly but when I double click on the sheet to open the full worksheet and close it(by click outside), the image/table is re-sizing to different and it doesn’t look good. after-worksheet-close.png (139.0 KB)
first-time.jpg (600.4 KB)
PFA.

amjad.sahi · July 6, 2023, 8:18am

For the rendered image which does not show data properly on Ubuntu, please make sure the following and do the needful:

The display settings of OS should be 100% (scaling). Aspose.Cells does not give good results regarding image rendering with display settings other than 100%.
Make sure the underlying fonts (e.g., Arial, Calibri, etc.) used in the Excel workbook are installed on your linux machine. The fonts folder should be accessible (you should have read/write permissions) to your application. See the document on configuring fonts for your reference.

Regarding other issues, my fellow colleague from Aspose.Slides team will get back to you soon.

andrey.potapov · July 6, 2023, 1:01pm

@RAVI_DEVIREDDY,
Thank you for the details. I reproduced the change of the OLE image and its preview after opening data from the Excel file by double-clicking on the OLE frame.

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): SLIDESJAVA-39240

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

You can use the IGraphicalObjectLock interface as follows:

oof.getGraphicalObjectLock().setPositionLocked(true);

As far as I know, PowerPoint documents do not allow to do this.

RAVI_DEVIREDDY · July 7, 2023, 8:21am

thanks for the replay , can you please let me know on OS , is there any issues while generating this on Ubuntu systems ?

andrey.potapov · July 7, 2023, 10:54am

@RAVI_DEVIREDDY,
We will also check the issues on Ubuntu. Unfortunately, I don’t have any additional information yet.