How to Add Multiple OLE Objects as Links to the Slide from Excel Worksheets in C#?

Aspose.Total Product Family
, ,
1

expected_out_net.zip (96.8 KB)

Hello everyone,

I’ve checked all the documentation and the forums but couldn’t find any solution for my case.

I have an excel workbook that contains 2 worksheets.
I want to create a slide with an OLE object linking to excel with specified range.
First worksheet has one table and one chart. I want to add first slide as one OLE object from excel first sheet.

Second sheet contains one large table but I want to show part of this table in the second slide. I don’t want to show whole table.
I’ve attached the expected output pptx file and sample excel file.
And here is the code that I tried.

Thanks in advance.

string filePath = "C:\\Users\\y\\Documents\\";
Workbook wb = new Workbook(filePath + "out_net.xlsx");


// Save the Excel workbook to a memory stream
MemoryStream ms = new MemoryStream();
wb.Save(ms, SaveFormat.Xlsx);
ms.Position = 0;
wb.Worksheets.ActiveSheetIndex = 0;
wb.Worksheets.SetOleSize(0, 50, 0, 10);
wb.Worksheets[0].PageSetup.PrintArea = "A1:C16";

// Create a new PowerPoint presentation
Presentation presentation = new Presentation();

// Add a slide to the presentation
ISlide slide = presentation.Slides[0];

ILayoutSlide layout = presentation.LayoutSlides[0];
ISlide slide2 = presentation.Slides.AddEmptySlide(layout);
// Creates a data object for embedding
IOleEmbeddedDataInfo dataInfo = new OleEmbeddedDataInfo(ms.ToArray(), "xlsx");

wb.Worksheets.ActiveSheetIndex = 1;
IOleEmbeddedDataInfo dataInfo2 = new OleEmbeddedDataInfo(ms.ToArray(), "xlsx");

// Adds an Ole Object Frame shape
IOleObjectFrame oleObjectFrame = slide.Shapes.AddOleObjectFrame(0, 0, presentation.SlideSize.Size.Width,
    presentation.SlideSize.Size.Height, dataInfo);
IOleObjectFrame oleObjectFrame2 = slide2.Shapes.AddOleObjectFrame(0, 0, presentation.SlideSize.Size.Width,
    presentation.SlideSize.Size.Height, dataInfo);
// Embed the Excel file as an OLE object on the slide
//IOleObjectFrame oleObjectFrame = slide.Shapes.AddOleObjectFrame(20, 20, 200, 150, "Charts", "out_net.xlsx");
oleObjectFrame.LinkPathLong = "out_net.xlsx";
oleObjectFrame.LinkPathLong = "out_net.xlsx";


presentation.Save(filePath + "out_net.pptx", Aspose.Slides.Export.SaveFormat.Pptx);
2

@yunusbayrak

To add multiple OLE objects as links to slides from Excel worksheets using Aspose.Slides for .NET, you can follow these steps:

  1. Load the Excel Workbook: Use the Workbook class from Aspose.Cells to load your Excel file.

  2. Create a Memory Stream: Save the workbook to a memory stream, which allows you to embed the data without saving it to disk.

  3. Set the Print Area: For the specific range you want to link, set the print area in the Excel worksheet.

  4. Create a PowerPoint Presentation: Instantiate a new Presentation object.

  5. Add Slides: Create slides in the presentation where you want to add the OLE objects.

  6. Add OLE Object Frames: Use the AddOleObjectFrame method to add OLE objects to the slides. You can specify the dimensions and the data to be linked.

  7. Set Link Path: If you want to link to the Excel file, set the LinkPathLong property of the OleObjectFrame.

Here is a code example that demonstrates how to achieve this:

string filePath = "C:\\Users\\y\\Documents\\";
Workbook wb = new Workbook(filePath + "out_net.xlsx");

// Save the Excel workbook to a memory stream
MemoryStream ms = new MemoryStream();
wb.Save(ms, SaveFormat.Xlsx);
ms.Position = 0;

// Create a new PowerPoint presentation
Presentation presentation = new Presentation();

// Add a slide for the first worksheet (table and chart)
ISlide slide1 = presentation.Slides[0];
wb.Worksheets.ActiveSheetIndex = 0; // First worksheet
wb.Worksheets.SetOleSize(0, 50, 0, 10);
wb.Worksheets[0].PageSetup.PrintArea = "A1:C16"; // Set the print area

// Create a data object for embedding
IOleEmbeddedDataInfo dataInfo1 = new OleEmbeddedDataInfo(ms.ToArray(), "xlsx");

// Add an Ole Object Frame shape for the first slide
IOleObjectFrame oleObjectFrame1 = slide1.Shapes.AddOleObjectFrame(0, 0, presentation.SlideSize.Size.Width,
    presentation.SlideSize.Size.Height, dataInfo1);
oleObjectFrame1.LinkPathLong = "out_net.xlsx"; // Set the link path

// Add a second slide for the second worksheet (part of the large table)
ISlide slide2 = presentation.Slides.AddEmptySlide(presentation.LayoutSlides[0]);
wb.Worksheets.ActiveSheetIndex = 1; // Second worksheet
wb.Worksheets.SetOleSize(0, 50, 0, 10);
wb.Worksheets[1].PageSetup.PrintArea = "A1:C10"; // Set the print area for the second slide

// Create a data object for the second worksheet
IOleEmbeddedDataInfo dataInfo2 = new OleEmbeddedDataInfo(ms.ToArray(), "xlsx");

// Add an Ole Object Frame shape for the second slide
IOleObjectFrame oleObjectFrame2 = slide2.Shapes.AddOleObjectFrame(0, 0, presentation.SlideSize.Size.Width,
    presentation.SlideSize.Size.Height, dataInfo2);
oleObjectFrame2.LinkPathLong = "out_net.xlsx"; // Set the link path

// Save the presentation
presentation.Save(filePath + "out_net.pptx", Aspose.Slides.Export.SaveFormat.Pptx);

In this example, the first slide contains an OLE object linked to the first worksheet, while the second slide links to a specified range of the second worksheet. Adjust the PrintArea as needed to show only the desired parts of your Excel data.

For more detailed information on managing OLE objects in PowerPoint presentations, you can refer to the Aspose documentation on Managing OLE.

Sources:
[1]: Manage OLE|Aspose.Slides Documentation

3

This adds the first worksheet to both slides. But I need to add second worksheet to second slide.
It looks like changing ActiveSheetIndex won’t help.
And I need to add sheets within specified range, which is also not possible as far as I see.

4

@yunusbayrak,
I am working on the issue and will get back to you soon.

1 Like
5

@yunusbayrak,
Thank you for your patience.

I have some difficulties creating images from your Excel worksheets, so I’ve moved this thread to Aspose.Total forum. My colleagues from Aspose.Cells team will assist you shortly.
@amjad.sahi FYI

As for adding an OLE object linked to the Excel file in a presentation, this can be done as below:

string fileName = "out_net.xlsx";
string className = "Excel.Sheet.12";

IOleObjectFrame oleFrame = slide.Shapes.AddOleObjectFrame(
    20, 20, 200, 150, className, fileName);

IPPImage oleImage = presentation.Images.AddImage(image);
oleFrame.SubstitutePictureFormat.Picture.Image = oleImage;
oleFrame.IsObjectIcon = true;

Unfortunately, it is not possible to specify a specific worksheet that will open when you double-click on the OLE object image.

1 Like
6

@yunusbayrak,
Please try using the following code snippets:

string excelFileName = "out_net.xlsx";
string className = "Excel.Sheet.12";
string pptFileName = "output.pptx";
int worksheetIndex = 1;
string rangeAddress = "A1:C10";
int slideIndex = 0;

float marginLeft = 20;
float marginTop = 20;
float marginRight = 20;
float marginBottom = 20;

using (Workbook workbook = new Workbook(excelFileName))
using (Presentation presentation = new Presentation())
{
    Worksheet worksheet = workbook.Worksheets[worksheetIndex];
    using (Stream imageStream = CreateRangeImage(worksheet, rangeAddress))
    {
        IPPImage oleImage = presentation.Images.AddImage(imageStream);
        SizeF slideSize = presentation.SlideSize.Size;

        RectangleF imageArea = GetImageLocation(
            slideSize, marginLeft, marginTop, marginRight, marginBottom,
            oleImage.Width, oleImage.Height);

        ISlide slide = presentation.Slides[slideIndex];

        IOleObjectFrame oleFrame = slide.Shapes.AddOleObjectFrame(
            imageArea.X, imageArea.Y, imageArea.Width, imageArea.Height, className, excelFileName);

        oleFrame.IsObjectIcon = false;
        oleFrame.SubstitutePictureFormat.Picture.Image = oleImage;
        oleFrame.SubstitutePictureFormat.PictureFillMode = PictureFillMode.Stretch;
                    
        presentation.Save(pptFileName, Aspose.Slides.Export.SaveFormat.Pptx);
    }
}

static Stream CreateRangeImage(Worksheet worksheet, string rangeAddress)
{
    worksheet.PageSetup.PrintArea = rangeAddress;
    worksheet.PageSetup.LeftMargin = 0;
    worksheet.PageSetup.RightMargin = 0;
    worksheet.PageSetup.TopMargin = 0;
    worksheet.PageSetup.BottomMargin = 0;
    worksheet.PageSetup.ClearHeaderFooter();

    ImageOrPrintOptions imageOptions = new ImageOrPrintOptions
    {
        ImageType = Aspose.Cells.Drawing.ImageType.OfficeCompatibleEmf,
        VerticalResolution = 300,
        HorizontalResolution = 300,
        OnePagePerSheet = true
    };

    SheetRender sheetRender = new SheetRender(worksheet, imageOptions);
    MemoryStream imageStream = new MemoryStream();

    sheetRender.ToImage(0, imageStream);
    imageStream.Seek(0, SeekOrigin.Begin);
    return imageStream;
}

static RectangleF GetImageLocation(
    SizeF slideSize, float marginLeft, float marginTop, float marginRight,
    float marginBottom, int imageWidth, int imageHeight)
{
    float maxWidth = slideSize.Width - marginLeft - marginRight;
    float maxHeight = slideSize.Height - marginTop - marginBottom;

    if (imageWidth < maxWidth && imageHeight < maxHeight)
        return new RectangleF(marginLeft, marginTop, imageWidth, imageHeight);

    float xRatio = maxWidth / imageWidth;
    float yRatio = maxHeight / imageHeight;

    float width, height;

    if (xRatio < yRatio) // The image is stretched more in width than in height compared to the slide dimensions.
    {
        width = maxWidth;
        height = imageHeight * xRatio;
    }
    else
    {
        width = imageWidth * yRatio;
        height = maxHeight;
    }

    return new RectangleF(marginLeft, marginTop, width, height);
}
1 Like
7

@andrey.potapov
Thanks for your answer and time. There is a simple issue left. The placement and size.
image.png (31.7 KB)

8

@yunusbayrak,
It looks like this happened after you clicked the “Update Links” button in PowerPoint. Could you please confirm this?

9

@andrey.potapov Correct, When I open powerpoint without click “Update Links” prompt then it’s ok.
But I guess we need to be able to update links because of the nature of the OLE linking, right?

10

@yunusbayrak,
Yes, sometimes you need to update links after inserting an OLE object into a PowerPoint presentation.

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): SLIDESNET-44686

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.

1 Like
11

@yunusbayrak,
Our developers have investigated the case. The issue is that when PowerPoint updates the data for a linked object, it updates the object’s image and always uses the first sheet of the linked Excel file entirely. This happens because:

  1. Updating Linked Objects: When PowerPoint updates the data for a linked OLE object, it refreshes the object preview, often regenerating it based on the first sheet or view of the linked Excel file, not necessarily the specific range initially used for the object’s preview.

  2. Entire First Page: PowerPoint tends to take the entire first sheet of the Excel file when regenerating the image during updates. This behavior often results in discrepancies in the size and content of the displayed image if the first sheet contains more data than initially captured in the OLE object’s preview. This limitation can affect the appearance and size of the object when refreshed, particularly with linked Excel files.

To prevent the PowerPoint dialog prompting to update the object’s data, set the UpdateAutomatic property of the OleObjectFrame to false:

oleFrame.UpdateAutomatic = false;