Image for OLE is not proper inside PPT

Aspose.Cells Product Family
,
1

Hi! Aspose team,
I have the requirement to display ole object inside PPT.
OLE Object is fine.
But the image which I am creating for the worksheet is not coming proper.
The Text in the image for the cell is getting cut off.
And the cell content in Excel are Vertically Top alligned.
PPT Screenshot image.png (49.3 KB)
Excel Screenshot for which I am creating image
image.png (11.3 KB)
I tried with autofit for dataSheet but still I am not able to get the image properly.
Could you please help me with this issue of image.
Attached sample code and Excel used for the POC.

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

    String dataDir = "D://AsposeSlides//";
    // Create a workbook
    Workbook workbook = new Workbook(dataDir+"Example.xlsx");

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

    // Get the chart worksheet as an image
    ImageOrPrintOptions imageOptions = new ImageOrPrintOptions();
    imageOptions.setImageType(ImageType.EMF);
    imageOptions.setOnlyArea(true);
    imageOptions.setOnePagePerSheet(true);
    imageOptions.setAllColumnsInOnePagePerSheet(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
    Presentation pres = new Presentation();
    ISlide sld = pres.getSlides().get_Item(0);
    

    // Add the workbook to the slide
    IOleEmbeddedDataInfo dataInfo = new OleEmbeddedDataInfo(bout.toByteArray(), "xlsx");
    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-dataSummary.pptx", com.aspose.slides.SaveFormat.Pptx);
    System.err.println("File Generated");
  }

Used Excel:
Example.zip (11.6 KB)

2

@Rohan_Wankar,

Thanks for the sample file and screenshots.

After an initial testing, we reproduced the issue as you mentioned via screenshots using your template XLSX file. We found output image for Excel OLE object is not proper inside the PPT. Even, if we don’t involve Aspose.Slides to insert the OLE object and paste output picture into PPT, the image itself has an issue where LPA column data is shown as “#######”. We need to investigate your issue in details.

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): CELLSJAVA-45482

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.

3

Hi! @amjad.sahi
Thanks! for the quick response.
And String content of the cell as well has an issue.
Border is coming just in bottom of the cell content.
It should be according to the excel which we are passing as an input.
Let me know if you need more input on the same.
thanks!
Rohan

4

@Rohan_Wankar,

Alright, we will also look into it as well.

Once we have an update on your issue, we will let you know.

1 Like
5

@Rohan_Wankar

For the issue that LPA column data is shown as "#######" , please comment the code line:

dataSheet.autoFitColumns();

For the text vertical alignment issue, please set system property to disable EmfPlus at the start, or you can set it at jvm start.

System.setProperty("Aspose.Cells.Disable", "EmfPlus");

So the code will be:

System.setProperty("Aspose.Cells.Disable", "EmfPlus");

String dataDir = "D://AsposeSlides//";
// Create a workbook
Workbook workbook = new Workbook(dataDir+"Example.xlsx");

// set visible rows and columns count
int chartRows = 25;
int chartCols = 11;
Worksheet dataSheet = workbook.getWorksheets().get(0);
// dataSheet.autoFitColumns();
// Set chart print area
dataSheet.getPageSetup().setPrintArea(dataSheet.getCells().get(0, 0).getName() + ":"
    + dataSheet.getCells().get(chartRows + 1, chartCols).getName());

...

If you still have issues, please share us the highlights and the generated pptx file.

6

Hi! @peyton.xu
I tried with the approach you specified.
But still I am not able to achieve my requirements.
And I want my cells to be auto fit according to the content of the cell.
Because for the web version of Powerpoint, Users will not be able to edit the Image in PPT.
Please could you let me know, if there is any other approach of handling it.
thanks!
Rohan

7

@Rohan_Wankar

Is the text vertical alignment issue fixed after setting System.setProperty("Aspose.Cells.Disable", "EmfPlus");?

Do you mean that you need the code dataSheet.autoFitColumns(); to autofit column width according to cell content? If yes, we will open a ticket to trace the issue that column data is shown as "#######" after autofitting column width.

8

hi! @peyton.xu
No TextVerticalAlignment is not fixed by setting this property as well.
And I want to use the property autoFit columns as well for the datasheet

9

@Rohan_Wankar,

Please try latest version/fix: Aspose.Cells for Java v23.6.
If you still find the text alignment issue with Aspose.Cells for Java v23.6, kindly share your sample (runnable) code and sample files that you are using, we will check it soon.

10

Hi! @amjad.sahi
I tried updating the version and tried setting system property as well.
But it is not working.
I am adding the snippet code for the issue.
Please try to generate the excel with this snippet code to replicate the issue.
thanks!

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

System.setProperty("Aspose.Cells.Disable", "EmfPlus");
String dataDir = "D://AsposeSlides//";
// Create a workbook
Workbook workbook = new Workbook();

// set visible rows and columns count
int chartRows = 25;
int chartCols = 11;
Worksheet dataSheet = workbook.getWorksheets().get(0);
com.aspose.cells.Cells cells = dataSheet.getCells();

// Adding a string value to the cell
com.aspose.cells.Cell cell = cells.get("A1");
cell.setValue("EMP ID");
Style style = cell.getStyle();
style.setVerticalAlignment(TextAlignmentType.TOP);
style.setHorizontalAlignment(TextAlignmentType.LEFT);
style.getFont().setSize(13);
style.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, Color.getBlack());
// Setting the line style of the bottom border
style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, Color.getBlack());
// Setting the line style of the left border
style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, Color.getBlack());
// Setting the line style of the right border
style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, Color.getBlack());
cell.setStyle(style);

cell = cells.get("B1");
cell.setValue("CITY");
style = cell.getStyle();
style.setVerticalAlignment(TextAlignmentType.TOP);
style.setHorizontalAlignment(TextAlignmentType.LEFT);
style.getFont().setSize(13);
style.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, Color.getBlack());
// Setting the line style of the bottom border
style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, Color.getBlack());
// Setting the line style of the left border
style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, Color.getBlack());
// Setting the line style of the right border
style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, Color.getBlack());
cell.setStyle(style);

cell = cells.get("C1");
cell.setValue("NAME");
style = cell.getStyle();
style.setVerticalAlignment(TextAlignmentType.TOP);
style.setHorizontalAlignment(TextAlignmentType.LEFT);
style.getFont().setSize(13);
style.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, Color.getBlack());
// Setting the line style of the bottom border
style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, Color.getBlack());
// Setting the line style of the left border
style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, Color.getBlack());
// Setting the line style of the right border
style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, Color.getBlack());
cell.setStyle(style);

cell = cells.get("D1");
cell.setValue("LPA");
style = cell.getStyle();
style.setVerticalAlignment(TextAlignmentType.TOP);
style.setHorizontalAlignment(TextAlignmentType.RIGHT);
style.getFont().setSize(13);
style.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, Color.getBlack());
// Setting the line style of the bottom border
style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, Color.getBlack());
// Setting the line style of the left border
style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, Color.getBlack());
// Setting the line style of the right border
style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, Color.getBlack());
cell.setStyle(style);
dataSheet.autoFitColumns();
// Set chart print area
dataSheet.getPageSetup().setPrintArea(dataSheet.getCells().get(0, 0).getName() + ":"
    + dataSheet.getCells().get(chartRows + 1, chartCols).getName());
// Set chart ole size
workbook.getWorksheets().setOleSize(0, chartRows, 0, chartCols);

// Get the chart worksheet as an image
ImageOrPrintOptions imageOptions = new ImageOrPrintOptions();
imageOptions.setImageType(ImageType.EMF);
imageOptions.setOnlyArea(true);
imageOptions.setOnePagePerSheet(true);
imageOptions.setAllColumnsInOnePagePerSheet(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
Presentation pres = new Presentation();
ISlide sld = pres.getSlides().get_Item(0);


// Add the workbook to the slide
IOleEmbeddedDataInfo dataInfo = new OleEmbeddedDataInfo(bout.toByteArray(), "xlsx");
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-dataSummary7.pptx", com.aspose.slides.SaveFormat.Pptx);

}

11

@Rohan_Wankar,
Please change the following code:

dataSheet.autoFitColumns();

To:

dataSheet.autoFitColumns();
dataSheet.autoFitRows(true);

After changing the code, we can obtain the correct results through testing. Please check the attachment. (31.8 KB).

12

Hi! @John.He
AutoFitRows resolved the issue for the row height.
But I have one more issue with respect to autofitColumns for the cell content.
Why we are seeing ####### for the cell content even if we do autoFitColumns()?
Could you please help me with this as well?
thanks!
Rohan

13

@Rohan_Wankar,

Regarding the data shown as "#######", please do not use the following line of code::

dataSheet.autoFitColumns();
14

Hi! @amjad.sahi
I tried removing dataSheet.autoFitColumns() as well
But still I am seeing the ##### symbol for the fact values.
Could you please help me with this issue?
thanks!
Rohan

15

@Rohan_Wankar,

Please share complete sample code and sample files to reproduce the issue, we will check it soon.

16

Hi! @amjad.sahi
I have used the snippet code which I have added early in the chat.
I just remove dataSheet.autoFitColumns() from the snippet code.
Please take a look at the same snippet code and let me know whether it is achievable.
thanks!
Rohan

17

@Rohan_Wankar,

I tested the following sample code and it works fine.
e.g.
Sample code:

         
        System.setProperty("Aspose.Cells.Disable", "EmfPlus");
        String dataDir1 = "f:\\files\\";
        // Create a workbook
        Workbook workbook = new Workbook();

        // set visible rows and columns count
        int chartRows = 25;
        int chartCols = 11;
        Worksheet dataSheet = workbook.getWorksheets().get(0);
        com.aspose.cells.Cells cells = dataSheet.getCells();

        // Adding a string value to the cell
        com.aspose.cells.Cell cell = cells.get("A1");
        cell.setValue("EMP ID");
        Style style = cell.getStyle();
        style.setVerticalAlignment(TextAlignmentType.TOP);
        style.setHorizontalAlignment(TextAlignmentType.LEFT);
        style.getFont().setSize(13);
        style.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
        // Setting the line style of the bottom border
        style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
        // Setting the line style of the left border
        style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
        // Setting the line style of the right border
        style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
        cell.setStyle(style);

        cell = cells.get("B1");
        cell.setValue("CITY");
        style = cell.getStyle();
        style.setVerticalAlignment(TextAlignmentType.TOP);
        style.setHorizontalAlignment(TextAlignmentType.LEFT);
        style.getFont().setSize(13);
        style.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
        // Setting the line style of the bottom border
        style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
        // Setting the line style of the left border
        style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
        // Setting the line style of the right border
        style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
        cell.setStyle(style);

        cell = cells.get("C1");
        cell.setValue("NAME");
        style = cell.getStyle();
        style.setVerticalAlignment(TextAlignmentType.TOP);
        style.setHorizontalAlignment(TextAlignmentType.LEFT);
        style.getFont().setSize(13);
        style.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
        // Setting the line style of the bottom border
        style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
        // Setting the line style of the left border
        style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
        // Setting the line style of the right border
        style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
        cell.setStyle(style);

        cell = cells.get("D1");
        cell.setValue("LPA");
        style = cell.getStyle();
        style.setVerticalAlignment(TextAlignmentType.TOP);
        style.setHorizontalAlignment(TextAlignmentType.RIGHT);
        style.getFont().setSize(13);
        style.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
        // Setting the line style of the bottom border
        style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
        // Setting the line style of the left border
        style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
        // Setting the line style of the right border
        style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
        cell.setStyle(style);

       dataSheet.autoFitColumns();
       dataSheet.autoFitRows(true);

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

        // Get the chart worksheet as an image
        ImageOrPrintOptions imageOptions = new ImageOrPrintOptions();
        imageOptions.setImageType(ImageType.EMF);
        imageOptions.setOnlyArea(true);
        imageOptions.setOnePagePerSheet(true);
        imageOptions.setAllColumnsInOnePagePerSheet(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(dataDir1 + "Example-out-dataSummary7.pptx", com.aspose.slides.SaveFormat.Pptx);

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

If you still find the issue, kindly paste exact code and output PPTX file for reference, we will check it soon.

18

Hi! @amjad.sahi
I have attached the used sample excel and downloaded PPTX from the sample code mentioned below.
I have commented dataSheet.autoFitColumns() as well, but still seeing #### for the bigger currency values.
Please take a look.
thanks!

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

String dataDir = "D://AsposeSlides//";
// Create a workbook
Workbook workbook = new Workbook(dataDir+"Example.xlsx");

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

// Get the chart worksheet as an image
ImageOrPrintOptions imageOptions = new ImageOrPrintOptions();
imageOptions.setImageType(ImageType.EMF);
imageOptions.setOnlyArea(true);
imageOptions.setOnePagePerSheet(true);
imageOptions.setAllColumnsInOnePagePerSheet(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
Presentation pres = new Presentation();
ISlide sld = pres.getSlides().get_Item(0);


// Add the workbook to the slide
IOleEmbeddedDataInfo dataInfo = new OleEmbeddedDataInfo(bout.toByteArray(), "xlsx");
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+"OLETest2.pptx", com.aspose.slides.SaveFormat.Pptx);
System.err.println("File Generated");

}OLESampleOutput.zip (58.6 KB)

19

@Rohan_Wankar
By using sample code and file for testing, we can reproduce the issue. I found that the bigger currency values were displayed as a string of # characters.

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): CELLSJAVA-45635

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.

20

@Rohan_Wankar,

When you use/enble the option imageOptions.setOnlyArea(true);, the result is closer to what you see in Excel Normal view with windows display 100% setting(see screentshot: Normal_view_vs_Print_view.png (77.0 KB)), the text in Cell E5 shows as #. When disable the option imageOptions.setOnlyArea(false);, the result is closer to what you see at Excel Printview.

So, please disable the option imageOptions.setOnlyArea(false); if you want to get the result as Excel Printview.