Hi! Aspose Team,
I have the requirement where I want to add the Excel as Embedded OLE object in ppt, according to the nos of columns in the middle of slides.
Image should not show the empty spaces, it should occupy size according to the no. of columns available in the worksheet & should be displayed the image in the center of the slides.
I am not sure how to achieve it.
Please could you help me with this requirement.
I have added the sample code.
thanks!
Rohan
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);
// 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, 2);
// Get the chart 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
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(50, 100,
600, 250, 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);
}
Example.zip (9.0 KB)
@Rohan_Wankar,
Thank you for contacting support.
Could you please share a presentation file containing the OLE object with the expected view?
HI! @Andrey_Potapov
I have attached the ppt with expected output, please take a look
thanks!
Rohan
Example-out-dataSummary4.zip (39.1 KB)
@Rohan_Wankar,
Thank you for the sample presentation. My colleagues from the Aspose.Cells team will soon show you how to create such an image from the Excel sheet.
@Rohan_Wankar,
While checking your PPTX file, I found your sample Excel XLSX file (you attached earlier in the first post) was different. So, kindly do share the underlying Excel XLSX file which you have embedded as OLE Object into PowerPoint presentation, this file has more fields/columns with data. Moreover, you would need to update/accommodate your sample code a bit based on your underlying Excel file. You may also share your updated code segment here, we can review it as well.
Hi! @Amjad_Sahi
I have attached the latest sample code for reference.
thanks!
Rohan
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.setGridlinesVisible(false);
// 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, 3);
// Get the chart worksheet as an image
ImageOrPrintOptions imageOptions = new ImageOrPrintOptions();
imageOptions.setImageType(ImageType.JPEG);
imageOptions.setOnlyArea(true);
imageOptions.setOnePagePerSheet(true);
int verticalResolution = imageOptions.getVerticalResolution();
int horizontalResolution = imageOptions.getHorizontalResolution();
System.err.println("verticalResolution :"+verticalResolution);
System.err.println("horizontalResolution :"+horizontalResolution);
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(50, 100,
600, 350, dataInfo);
oof.getSubstitutePictureFormat().getPicture()
.setImage(pres.getImages().addImage(imageByteArray));
// Write the presentation to disk
pres.save(dataDir+"Example-out-dataSummary4.pptx", com.aspose.slides.SaveFormat.Pptx);
}
Added Excel ReferenceDoclet_withSingleCell.zip (22 Bytes)
@Rohan_Wankar,
Your attachment (ReferenceDoclet_withSingleCell.zip) is empty and does not contain anything. Please zip the file(s) and attach the archive here. We will check your issue soon.
Hi! @Amjad_Sahi
I have reshared the excel file. please check it once.
Example123.zip (10.1 KB)
@Rohan_Wankar,
Thanks for the sample file.
Please try the updated code segment for your requirements:
e.g.
Sample code:
String dataDir = "f:\\files\\";
// Create a workbook
Workbook workbook = new Workbook(dataDir+"Example123.xlsx");
// set visible rows and columns count
int dataRows = 22;
int dataCols = 3;
Worksheet dataSheet = workbook.getWorksheets().get(0);
// Set your desired print area
dataSheet.getPageSetup().setPrintArea(dataSheet.getCells().get(0, 0).getName() + ":"
+ dataSheet.getCells().get(dataRows + 1, dataCols).getName());
//set margins
dataSheet.getPageSetup().setLeftMargin(0);
dataSheet.getPageSetup().setTopMargin(0);
dataSheet.getPageSetup().setRightMargin(0);
dataSheet.getPageSetup().setBottomMargin(0);
// Set chart ole size
workbook.getWorksheets().setOleSize(0, dataRows+1, 0, 2);
// Get the chart 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);
//sheetRender.toImage(0, "f:\\files\\out1.emf");
byte[] imageByteArray = imageStream.toByteArray();
// Save the workbook to stream
ByteArrayOutputStream bout = new ByteArrayOutputStream();
workbook.save(bout, com.aspose.cells.SaveFormat.XLSX);
.........
Hope, this helps a bit.
Hi! @Amjad_Sahi
I want to reduce the image size according to the worksheet populated columns & rows.
And I wanted to center the image in the slide.
Could you help me with this requirement.
thanks!
Rohan
You mean the output image size in KB/MB, etc.? If true, please note, Aspose.Cells is a spreadsheet management library rather than image processing or image compression API. We have another library i.e., Aspose.Imaging for the purpose which you may try to manipulate the output image produced by Aspose.Cells API. For your information, PNG and JPEG images are compressed images, they already have minimal size, so you may choose them. Moreover, you may try to minimize the Quality and horizontal and vertical resolutions using ImageOrPrintOptions. You may also set your desired custom size of the image. This will minimize the size of the output image produced by Aspose.Cells for Java.
Our colleague from Aspose.Slides team will assist you soon on it.
Hi! @Amjad_Sahi
I have added the screenshot.
I want to remove the blank space in the image.
And want to center the image.
image.png (101.4 KB)
@Rohan_Wankar,
Please try the updated sample code I shared in previous reply with latest version of Aspose.Cells for Java (e.g., v22.10 or v22.11). I tested with your sample file, it works fine and as expected.
@Andrey_Potapov will evaluate it and help you soon.
@Rohan_Wankar,
To center the image on the slide, you can get the slide size as shown below
var slideSize = presentation.getSlideSize().getSize();
and calculate the image position using the image size and slide size. I hope this helps.
Documents: Slide Size
API Reference: getSlideSize method
Hi! @Andrey_Potapov
How I can get the image size?
@Rohan_Wankar,
You have already used the image size (600x350) when adding the OLE object to the slide like this:
IOleObjectFrame oof = sld.getShapes().addOleObjectFrame(50, 100, 600, 350, dataInfo);
Hi! @Andrey_Potapov
thanks! for the quick response. I am able to remove the empty spaces in the image,
but now the ole size of Excel is getting reduced.
I have attached the snippet code for the reference.
How I can dynamically increase the size of Embedded OLE in PPT?
Please let me know.
thanks!
String dataDir = "D://AsposeSlides//";
// Create a workbook
Workbook workbook = new Workbook(dataDir+"Example.xlsx");
// set visible rows and columns count
int chartRows = 10;
int chartCols = 11;
Worksheet dataSheet = workbook.getWorksheets().get(0);
dataSheet.setGridlinesVisible(false);
// Set chart print area
dataSheet.getPageSetup().setPrintArea(dataSheet.getCells().get(0, 0).getName() + ":"
+ dataSheet.getCells().get(chartRows + 1, 3).getName());
dataSheet.getPageSetup().setLeftMargin(0);
dataSheet.getPageSetup().setTopMargin(0);
dataSheet.getPageSetup().setRightMargin(19);
dataSheet.getPageSetup().setBottomMargin(0);
// Set chart ole size
workbook.getWorksheets().setOleSize(0, chartRows, 0, 3);
// Get the chart worksheet as an image
ImageOrPrintOptions imageOptions = new ImageOrPrintOptions();
imageOptions.setImageType(ImageType.JPEG);
imageOptions.setOnlyArea(true);
imageOptions.setOnePagePerSheet(true);
int verticalResolution = imageOptions.getVerticalResolution();
int horizontalResolution = imageOptions.getHorizontalResolution();
System.err.println("verticalResolution :"+verticalResolution);
System.err.println("horizontalResolution :"+horizontalResolution);
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(70, 50,
400, 250, dataInfo);
pres.getImages().addImage(imageByteArray);
//IImageCollection imagesList = pres.getImages();
IPPImage ippImage = pres.getImages().addImage(imageByteArray);
oof.getSubstitutePictureFormat().getPicture()
.setImage(ippImage);
// Write the presentation to disk
pres.save(dataDir+"Example-out-dataSummary10.pptx", com.aspose.slides.SaveFormat.Pptx);
@Rohan_Wankar,
You should find some time to evaluate/understand your own code that you write and then sort it out by yourselves. The relevant API is setOleSize and you should specify the parameters accordingly. May be you could change the line of code:
// Set chart ole size
workbook.getWorksheets().setOleSize(0, chartRows, 0, 3);
to:
// Set chart ole size
workbook.getWorksheets().setOleSize(0, chartRows+1, 0, 3);
Hi! @Amjad_Sahi
I tried it, according to the no. of columns & rows.
But when I tried to set it dynamically according to the no. of rows.
The Embedded OLE size is getting exceeded w.r.t Slide size.
Is there any other way of achieving it?
thanks!
If it is exceeded, you may try to decrease the OLE object size accordingly. It might be due to PowerPoint behavior regarding OLE objects. @Andrey_Potapov, could you please look into it and suggest some way or workaround to cope with it.