Hi Andrey
I have been working with this as well, I realized if I right click on the image(after using the code you shared) and click on Update Link it actually refreshes the image.
So this is a viable solution for us, thank you.
I am good with the linking(instead of embedding) as long as I can link all objects using the OLE approach - charts, tables and text
Can you also please share some code embedding charts as OLE objects and linking(not embedding) them to an excel file. I have a chart in the ppt and excel file I shared with you on email, so you can use that as an example.
To clarify the problem statement, given a chart and data in Excel.
Add the chart as an OLE object in PowerPoint, such that PowerPoint shows the image of the chart, but double clicking the chart opens the Excel file. emulating the Ctrl + Alt + V behavior manually.
This is my code to accomplish this, but it renders the entire Excel as an image on the PPt instead of just the chart.
sheet_name = "Apple, Inc."
chart_index = 0 # Index of the chart in the worksheet
chart = workbook.worksheets.get(sheet_name).charts[chart_index]
img_stream = io.BytesIO()
chart.to_image(img_stream, ImageType.PNG)
img_stream.seek(0)
img = presentation.images.add_image(img_stream)
frame = slide.shapes.add_ole_object_frame(
20, 100, 144, 144,
"Excel.Sheet.12",
excel_file_name
)
frame.substitute_picture_format.picture.image.replace_image(img)
frame.update_automatic = True
@sukritisehgal,
Thank you for the code example. I’ll need a little time to investigate the issue and will get back to you promptly.
@sukritisehgal,
Thank you for your patience. I was trying to meet your requirements. It appears that PowerPoint does not provide such a feature, so Aspose.Slides cannot offer it either. If I have misunderstood anything, please provide step‑by‑step instructions on how to accomplish your goal manually in PowerPoint, and we will then help you do the same with Aspose.Slides. If you can perform this manually in PowerPoint, we can provide the corresponding code to achieve the same.
Hey Andrey,
Here is how I do it manually in Excel and PowerPoint,
- Assuming I have an Excel with a Chart
- Copy the Excel Chart using Ctrl + C
- Open a new PowerPoint Slide
- Press Ctrl + Alt + V, Paste Link
- This creates an OLE object in PowerPoint linked to the Excel chart.
Thanks for all your help!
@sukritisehgal,
Thank you very much for the instructions. I need some time to prepare a code example for you. I will get back to you as soon as possible.
Thanks, looking forward to working with you!
Hi Andrey,
It will be great if you can share this code in Java.
If that is an issue, Python is okay too!
@sukritisehgal,
Thank you for your request. I’ll need to dive into the API to meet your requirements, and I’ll get back to you as soon as I have an update.
@sukritisehgal,
Thank you for your patience. Unfortunately, I was unable to meet your requirements. I’ve forwarded your request to our developers.
Thank you. Please let us know as soon as possible.
@sukritisehgal,
Our developers have investigated the issue. Unfortunately, we cannot capture the chart object from Excel in the way you described, because this would require recreating the clipboard behavior between PowerPoint and Excel, and even Microsoft cannot achieve that consistently, as it varies across system settings.
In the provided example, the desired behavior is demonstrated. However, when you update the link, the image is also refreshed and displays the entire content of the specified cells. This, in turn, resizes the image and reveals parts of the cells that were not visible in the original image.
Please try using the following code example:
String excelFile = "linked_excel.xlsx";
Presentation presentation = new Presentation();
ISlide slide = presentation.getSlides().get_Item(0);
Workbook workbook = new Workbook(excelFile);
Worksheet worksheet = workbook.getWorksheets().get("sheet1");
com.aspose.cells.Chart chart = worksheet.getCharts().get(0);
ImageOrPrintOptions imageOptions = new ImageOrPrintOptions();
imageOptions.setImageType(com.aspose.cells.ImageType.EMF);
ByteArrayOutputStream imageStream = new ByteArrayOutputStream();
chart.toImage(imageStream, imageOptions);
Range cellRange = workbook.getWorksheets().get("sheet1").getCells().createRange("O199:S214");
IOleObjectFrame oleFrame = slide.getShapes().addOleObjectFrame(20, 300, 400, 200, "Excel.Sheet.12", excelFile);
oleFrame.setLinkPathLong(oleFrame.getLinkPathLong() + getAddressOnWorksheet(cellRange));
oleFrame.getSubstitutePictureFormat().getPicture().getImage().replaceImage(imageStream.toByteArray());
oleFrame.setUpdateAutomatic(false);
presentation.save("output.pptx", SaveFormat.Pptx);
presentation.dispose();
static String getAddressOnWorksheet(Range cellRange)
{
String worksheetName = "sheet1";
return "!"+worksheetName+"!R"+(cellRange.getFirstRow() + 1)+ "C" + (cellRange.getFirstColumn() + 1) +
":R"+(cellRange.getFirstRow() + cellRange.getRowCount())+"C" + (cellRange.getColumnCount() + cellRange.getFirstColumn());
}
Thank you Andrey. I can work with this.
I will find a solution for the resizing.
@sukritisehgal,
Thank you for using Aspose.Slides for Java.
Hey Andrey
I have an additional question here. This linking only works if I open the linked excel first.
If I open the powerpoint first, the links are broken and I have to manually go to File → Info and update them.
However if I open Excel first, the links work automatically.
What can I do to make this work even if the PPT opens first ?
@sukritisehgal,
Thank you for posting the question. I need some time to check the issue. I will get back to you as soon as possible.
Sure, adding more context.
If I open the ppt first after running the code, I get a message saying : Sorry, we couldn’t find all the linked files. you may be able to re-stablish the links by clicking the File tab, then clicking “Edit Links to Files” on the Info tab.
I don’t get the message if the liked Excel file is already open on my system before I open the ppt
@sukritisehgal,
Thank you for your patience. Unfortunately, I was unable to reproduce the issue you described. I used the code example provided above. File linking works fine. Could you please share step-by-step instructions to reproduce the issue on our end?
Hi Andrey,
Below is the code I am using, very similar to the code you shared above.
My Excel is stored on my computer, but the file is closed.
After running this script, here is what happens :
-
If I open the PowerPoint, I get a message saying this ppt contains Linked files, I click Update Links. I then get a message : Sorry, we couldn’t find all the linked files. you may be able to re-stablish the links by clicking the File tab, then clicking “Edit Links to Files” on the Info tab. If I go to File → Info, the type of all linked files is ???. All OLE objects are broken.
-
I open the Excel. After it loads, I open the PowerPoint. I get a message saying this ppt contains Linked files, I click Update Links. Now the Ppt is fine and all links are present. If I go to File-> Info, the type of all Links is Worksheet, not ???.
import com.aspose.cells.*;
import com.aspose.slides.Presentation;
import com.aspose.slides.IOleObjectFrame;
import com.aspose.slides.ISlide;
import com.aspose.slides.SaveFormat;
import org.springframework.core.io.ClassPathResource;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
public class Test {
public static void main(String[] args) throws Exception {
String excelFileName = "linked_excel.xlsx";
Presentation presentation = new Presentation();
ISlide slide = presentation.getSlides().get_Item(0);
Workbook workbook = new Workbook(excelFileName);
Range cellRange = workbook.getWorksheets().get("template").getCells().createRange("D2:J12");
IOleObjectFrame frame = slide.getShapes().addOleObjectFrame(442, 329.5f, 320.4f, 230.4f, "Excel.Sheet.12", excelFileName);
frame.setLinkPathLong(frame.getLinkPathLong() + getAddressOnWorksheet(cellRange));
frame.getSubstitutePictureFormat().getPicture().getImage().replaceImage(createOleImage(cellRange));
frame.setUpdateAutomatic(true);
presentation.save("output.pptx", SaveFormat.Pptx);
presentation.dispose();
}
static String getAddressOnWorksheet(Range cellRange)
{
String worksheetName = "template";
return "!"+worksheetName+"!R"+(cellRange.getFirstRow() + 1)+ "C" + (cellRange.getFirstColumn() + 1) +
":R"+(cellRange.getFirstRow() + cellRange.getRowCount())+"C" + (cellRange.getColumnCount() + cellRange.getFirstColumn());
}
static byte[] createOleImage(com.aspose.cells.Range cellRange) throws Exception
{
PageSetup pageSetup = cellRange.getWorksheet().getPageSetup();
pageSetup.setPrintArea(cellRange.getAddress());
pageSetup.setLeftMargin(0);
pageSetup.setRightMargin(0);
pageSetup.setTopMargin(0);
pageSetup.setBottomMargin(0);
pageSetup.clearHeaderFooter();
ByteArrayOutputStream imageStream = new ByteArrayOutputStream();
ImageOrPrintOptions imageOrPrintOptions = new ImageOrPrintOptions();
imageOrPrintOptions.setOnePagePerSheet(true);
imageOrPrintOptions.setOnlyArea(true);
imageOrPrintOptions.setImageType(com.aspose.cells.ImageType.EMF);
SheetRender sheetRender = new SheetRender(cellRange.getWorksheet(),
imageOrPrintOptions);
sheetRender.toImage(0, imageStream);
return imageStream.toByteArray();
}
}
@sukritisehgal,
Thank you for the details. I need some time to check the issue. I will get back to you as soon as possible.
I ran into this issue all day yesterday and this somehow started working on its own today!