Image for OLE is not proper inside PPT

[jnachi16]

Hi @amjad.sahi ,
Please find attached herewith sample code for OLE object. As shown in below screenshot, numeric values are aligned to LEFT in last column, whereas text alignment given through code is RIGHT aligned.
image.png (109.7 KB)

{
         String dataDir = "C:\\\\Temp\\\\";
            // Create a workbook
            Workbook workbook = new Workbook();
            
            Cells cells = workbook.getWorksheets().get(0).getCells();
            
            
            
            cells.get("A4").putValue("Emp id");
            Cell cell1 = cells.get("A4");
            Style style1 = cell1.getStyle();
            style1.getFont().setBold(true);
            style1.getFont().setSize(16);
            style1.getFont().setName("Arial");
            Color c = Color.getGray();
            style1.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, c);
            style1.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, c);
            style1.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, c);
            style1.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, c);
            style1.setForegroundColor(c);
            cell1.setStyle(style1);
            
            cells.get("B4").putValue("City");
            Cell cell2 = cells.get("B4");
            Style style2 = cell2.getStyle();
            style2.getFont().setBold(true);
            style2.getFont().setSize(16);
            style2.getFont().setName("Arial");
            style2.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, c);
            style2.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, c);
            style2.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, c);
            style2.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, c);
            style2.setForegroundColor(c);
            cell2.setStyle(style2);
            
            cells.get("C4").putValue("Name");
            Cell cell3 = cells.get("C4");
            Style style3 = cell3.getStyle();
            style3.getFont().setBold(true);
            style3.getFont().setSize(16);
            style3.getFont().setName("Arial");
            style3.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, c);
            style3.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, c);
            style3.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, c);
            style3.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, c);
            style3.setForegroundColor(c);
            cell3.setStyle(style3);
            
            cells.get("D4").putValue("Last Name");
            Cell cell4 = cells.get("D4");
            Style style4 = cell4.getStyle();
            style4.getFont().setBold(true);
            style4.getFont().setSize(16);
            style4.getFont().setName("Arial");
            style4.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, c);
            style4.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, c);
            style4.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, c);
            style4.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, c);
            style4.setForegroundColor(c);
            cell4.setStyle(style4);
            
            
            cells.get("E4").putValue("$");
            Cell cell5 = cells.get("E4");
            Style style5 = cell5.getStyle();
            style5.getFont().setBold(true);
            style5.getFont().setSize(16);
            style5.getFont().setName("Arial");
            style5.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, c);
            style5.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, c);
            style5.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, c);
            style5.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, c);
            style5.setHorizontalAlignment(TextAlignmentType.RIGHT);
            style5.setForegroundColor(c);
            cell5.setStyle(style5);
            
            
            cells.get("A5").putValue("Long Text needed");
            cells.get("A6").putValue("Long Text needed");
            cells.get("A7").putValue("ong Text needed");
            cells.get("A8").putValue("Long Text needed");
            cells.get("A9").putValue("Long Text needed");
            cells.get("A10").putValue("Long Text needed");
            cells.get("A11").putValue("Long Text needed");
            cells.get("A12").putValue("Long Text needed");
            cells.get("A13").putValue("Long Text needed");
            cells.get("A14").putValue("Long Text needed");
            cells.get("A15").putValue("Long Text needed");
            cells.get("A16").putValue("Long Text needed");
            cells.get("A17").putValue("Long Text needed");
            cells.get("A18").putValue("Long Text needed");
            cells.get("A19").putValue("Long Text needed");
            cells.get("A20").putValue("Long Text needed");
            cells.get("A21").putValue("Long Text neede7");
            cells.get("A22").putValue("Long Text needed");
            cells.get("A23").putValue("Long Text needed");
            cells.get("A24").putValue("Long Text needed");
            cells.get("A25").putValue("Long Text needed");
            cells.get("A26").putValue("Long Text needed");
            cells.get("A27").putValue("Long Text needed");

            for(int i=1;i<=24;i++) {
              Cell cell = cells.get("A"+(i+4));
              Style style = cell.getStyle();
              style.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, c);
              style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, c);
              style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, c);
              style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, c);
              style.setHorizontalAlignment(TextAlignmentType.LEFT);
              style.getFont().setName("Arial");
              style.getFont().setSize(11);
              
              
              
              cell.setStyle(style);


            }
            
            
            cells.get("B5").putValue("Long Text needed");
            cells.get("B6").putValue("Long Text needed");
            cells.get("B7").putValue("Long Text needed");
            cells.get("B8").putValue("Long Text needed");
            cells.get("B9").putValue("Long Text needed");
            cells.get("B10").putValue("Long Text needed");
            cells.get("B11").putValue("Long Text needed");
            cells.get("B12").putValue("Long Text needed");
            cells.get("B13").putValue("Long Text needed");
            cells.get("B14").putValue("Long Text needed");
            cells.get("B15").putValue("Long Text needed");
            cells.get("B16").putValue("Long Text needed");
            cells.get("B17").putValue("Long Text needed");
            cells.get("B18").putValue("Long Text needed");
            cells.get("B19").putValue("Long Text needed");
            cells.get("B20").putValue("Long Text needed");
            cells.get("B21").putValue("Long Text needed");
            cells.get("B22").putValue("Long Text needed");
            cells.get("B23").putValue("Long Text needed");
            cells.get("B24").putValue("Long Text needed");
            cells.get("B25").putValue("Long Text needed");
            cells.get("B26").putValue("Long Text needed");
            cells.get("B27").putValue("Long Text needed");
            
            
            for(int i=1;i<=24;i++) {
              Cell cell = cells.get("B"+(i+4));
              Style style = cell.getStyle();
              style.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, c);
              style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, c);
              style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, c);
              style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, c);
              style.setHorizontalAlignment(TextAlignmentType.LEFT);
              style.getFont().setSize(11);
              style.getFont().setName("Arial");

              
              
              cell.setStyle(style);


            }
            
            cells.get("C5").putValue("Long Text needed");
            cells.get("C6").putValue("Long Text needed");
            cells.get("C7").putValue("c3");
            cells.get("C8").putValue("Long Text needed");
            cells.get("C9").putValue("e5");
            cells.get("C10").putValue("f6");
            cells.get("C11").putValue("Long Text needed");
            cells.get("C12").putValue("h8");
            cells.get("C13").putValue("i9");
            cells.get("C14").putValue("Long Text needed");
            cells.get("C15").putValue("k11");
            cells.get("C16").putValue("l12");
            cells.get("C17").putValue("Long Text needed");
            cells.get("C18").putValue("n14");
            cells.get("C19").putValue("o15");
            cells.get("C20").putValue("o15s");
            cells.get("C21").putValue("Long Text needed");
            cells.get("C22").putValue("o1ssss52");
            cells.get("C23").putValue("o1v545");
            cells.get("C24").putValue("Long Text needed");
            cells.get("C25").putValue("Long Text needed");
            cells.get("C26").putValue("ot15");
            cells.get("C27").putValue("Long Text needed");
            
            for(int i=1;i<=24;i++) {
              Cell cell = cells.get("C"+(i+4));
              Style style = cell.getStyle();
              style.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, c);
              style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, c);
              style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, c);
              style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, c);
              style.setHorizontalAlignment(TextAlignmentType.LEFT);
              style.getFont().setSize(11);
              style.getFont().setName("Arial");

              
              
              cell.setStyle(style);


            }
            
            cells.get("D5").putValue("Long Text needed");
            cells.get("D6").putValue("Long Text needed");
            cells.get("D7").putValue("Long Text needed");
            cells.get("D8").putValue("r");
            cells.get("D9").putValue("s");
            cells.get("D10").putValue("Long Text needed");
            cells.get("D11").putValue("u");
            cells.get("D12").putValue("v");
            cells.get("D13").putValue("Long Text needed");
            cells.get("D14").putValue("x");
            cells.get("D15").putValue("Long Text needed");
            cells.get("D16").putValue("z");
            cells.get("D17").putValue("ab");
            cells.get("D18").putValue("ac");
            cells.get("D19").putValue("a4d");
            cells.get("D20").putValue("adh");
            cells.get("D21").putValue("Long Text needed");
            cells.get("D22").putValue("ad6");
            cells.get("D23").putValue("ad4");
            cells.get("D24").putValue("ad3");
            cells.get("D25").putValue("ad2");
            cells.get("D26").putValue("Long Text needed");
            cells.get("D27").putValue("Long Text needed");

            for(int i=1;i<=24;i++) {
              Cell cell = cells.get("D"+(i+4));
              Style style = cell.getStyle();
              style.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, c);
              style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, c);
              style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, c);
              style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, c);
              style.setHorizontalAlignment(TextAlignmentType.LEFT);
              style.getFont().setSize(11);
              style.getFont().setName("Arial");

              
              
              cell.setStyle(style);


            }
            
            cells.get("E5").putValue(9678988800L);
            cells.get("E6").putValue(9678988800L);
            cells.get("E7").putValue(9678988800L);
            cells.get("E8").putValue(9678988800L);
            cells.get("E9").putValue(9678988800L);
            cells.get("E10").putValue(9678988800L);
            cells.get("E11").putValue(9678988800L);
            cells.get("E12").putValue(9678988800L);
            cells.get("E13").putValue(9678988800L);
            cells.get("E14").putValue(9678988800L);
            cells.get("E15").putValue(9678988800L);
            cells.get("E16").putValue(9678988800L);
            cells.get("E17").putValue(8678988800L);
            cells.get("E18").putValue(9678988800L);
            cells.get("E19").putValue(9678988800L);
            cells.get("E20").putValue(9678988800L);
            cells.get("E21").putValue(9678988800L);
            cells.get("E22").putValue(9678988800L);
            cells.get("E23").putValue(9678988800L);
            cells.get("E24").putValue(1000000000L);
            cells.get("E25").putValue(1000000000L);
            cells.get("E26").putValue(1000000000L);
            cells.get("E27").putValue(1000000000L);




            for(int i=1;i<=24;i++) {
              Cell cell = cells.get("E"+(i+4));
              Style style = cell.getStyle();
              style.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, c);
              style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, c);
              style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, c);
              style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, c);
              style.getFont().setSize(11);
              style.getFont().setName("Arial");
              style.setHorizontalAlignment(TextAlignmentType.RIGHT);
              style.setVerticalAlignment(TextAlignmentType.TOP);
              Shade shade = new Shade();
              shade.setColor("#FDFDFD");
              ColourProvider d1 = new ColourProvider();
              Color d = d1.getColorFromShade(shade);
              style.setForegroundColor(d);
              style.setPattern(BackgroundType.SOLID);
              style.setIndentLevel(0);
              

              style.setCustom("$#,##0;$(#,##0)");
              
              cell.setStyle(style);

            }



            // 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+"Issue2.pptx", com.aspose.slides.SaveFormat.Pptx);
            System.out.println("File Generated");
    }

OLEIssue.zip (47.1 KB)

[John.He]

@jnachi16
After calling dataSheet.autoFitColumns(), the text width and column width are similar, which is not conducive to observation. By adding the following sample code to expand the column width, and then running the sample code you provided, we can observe that the alignment of text and numbers is correct. Please refer to the attachment (209.1 KB).

ColumnCollection columns = dataSheet.getCells().getColumns();
int colCount = columns.getCount();
for (int i = 0; i < colCount;i++)
{
	Column col = columns.get(i);
	col.setWidth(col.getWidth() + 20);        	
}

[jnachi16]

Hi @John.He /@amjad.sahi ,
Thanks for the solution.
But this approach won’t work for my requirement .
I might have any number of columns out of which I need to increase width for numeric columns only. Further 20 is static addition, which won’t suit all the use cases I’m having.
I need a generic solution for the addition of column width.
Based on numeric content length, perhaps I can decide with for that column but that would require iterating over all the rows impacting performance.
Kindly suggest some generic approach for handling this use case.

Thanks!

[amjad.sahi]

@jnachi16 ,

Thanks for your feedback.

We will check if we could find any better alternative. We will get back to you soon.

[peyton.xu]

@jnachi16

@jnachi16

As shown in below screenshot, numeric values are aligned to LEFT in last column, whereas text alignment given through code is RIGHT aligned.

It seems that the EmfPlus records in the generated Emf image are not rendered well. Please use the following JVM argument to disable to generate EmfPlus records while generating Emf image.

-DAspose.Cells.Disable=EmfPlus

Here is the generated pptx file for your reference:
Issue2_withoutEmfPlus.zip (40.5 KB)