OLE Objects

EPPlus supports adding OLE Objects by accessing ExcelWorksheet 's Drawings property. You can add OLE Objects by supplying a file path or a Stream.

Example 1 - Adding an Embedded OLE Object

To add an embedded OLE object you use the AddOleObject method on the worksheets Drawings property. You can use a file path as a string, a FileInfo object or a Stream. Using a Stream you will also need to supply a filename.

using ExcelPackage package = new ExcelPackage();
var worksheet = package.Workbook.Worksheets.Add("Sheet 1");
//Using a string
var oleObject = worksheet.Drawings.AddOleObject("MyOleObject", @"C:\MyFiles\MyPDF.pdf");

//Using FileInfo
FileInfo fileInfo = new FileInfo(@"C:\MyFiles\MyPDF.pdf");
var oleObject = worksheet.Drawings.AddOleObject("MyOleObject", fileInfo);

//Using a Stream
using (FileStream fileStream = new FileStream(@"C:\MyFiles\MyPDF.pdf", FileMode.Open, FileAccess.Read))
{
    var oleObject = worksheet.Drawings.AddOleObject("MyPdf", fileStream, "MyPDF.pdf");
}

Example 2 - Add a Linked OLE Object

Instead of embedding an object into the worksheet, EPPlus also supports linking to an object. Use AddOleObject method on the worksheets Drawing property. You need to supply a filepath as a string and true as the LinkToFile argument.

using ExcelPackage package = new ExcelPackage();
var worksheet = package.Workbook.Worksheets.Add("Sheet 1");
var oleObject = worksheet.Drawings.AddOleObject("MyLinkedObject", @"C:\MyFiles\MyPDF.pdf", true);

ExcelOleObjectParameters

• bool LinkToFile - Optional: If true the file will be linked.
• bool DisplayAsIcon - Optional: If true the drawings will be displayed as an icon.
• string ProgId - Set custom ProgId for the OLE Object

Read existing OLE Objects

You can access exsisting OLE Objects by using the Drawings property on the worksheet. You can use the name or the index to get the ExcelOleObject.

using ExcelPackage package = new ExcelPackage();
var worksheet = package.Workbook.Worksheets[0];
var MyTextFile = worksheet.Drawings["MyTextFile"];

Copy OLE Objects

As with any other Drawing you can make a copy of the OLE Object. Use the Copy method on the drawing and supply the target worksheet and position. Offsets in row and column are optional parameters.

var MyTextFile = worksheet.Drawings["MyTextFile"];
MyTextFile.Copy(AnotherWorksheet, 5, 1);

Copy arguments

Copy accepts the following arguments:

• ExcelWorksheet worksheet - The target worksheet to copy the object to.
• int row - The row position for the object copy.
• int col - The column position for the object copy.
• int rowOffset - Optional: The row offset.
• int colOffset - Optional: The column offset.

Remove an OLE Object

You can remove an OLE from a worksheet via the Drawings.Remove method. You can supply the OLE Object, name or its index in the Drawings collection.

//Using the object to remove
var MyTextFile = worksheet.Drawings["MyTextFile"];
worksheet.Drawings.Remove(MyTextFile);

//Using the index to remove
worksheet.Drawings.Remove(0);

//Using the name to remove
worksheet.Drawings.Remove("MyTextFile");

Custom Icon

You can use a custom icon for OLE Objects by passing an image to the Add method. You can use a string, FileInfo or Stream. Supported formats are .BMP and .EMF. Note that Microsoft Excel will override the icon. To stop this set the displayAsIcon property to true.

using ExcelPackage package = new ExcelPackage();
var worksheet = package.Workbook.Worksheets.Add("Sheet 1");
//Using a string
var oleObject = worksheet.Drawings.AddOleObject("MyOleObject", @"C:\MyFiles\MyPDF.pdf", false, true, @"C:\MyFiles\MyIcon.bmp");

//Using FileInfo
FileInfo fileInfo = new FileInfo(@"C:\MyFiles\MyPDF.pdf");
FileInfo iconImage = new FileInfo(@"C:\MyFiles\MyIcon.bmp");
var oleObject = worksheet.Drawings.AddOleObject("MyOleObject", fileInfo, false, true, iconImage);

//Using a Stream
using (FileStream fileStream = new FileStream(@"C:\MyFiles\MyPDF.pdf", FileMode.Open, FileAccess.Read))
{
    using (FileStream iconStream = new FileStream(@"C:\MyFiles\MyIcon.bmp", FileMode.Open, FileAccess.Read))
    {
        var oleObject = worksheet.Drawings.AddOleObject("MyPdf", fileStream, "MyPDF.pdf", true, iconStream);
    }
}

Extract file from OLE Object.

You can extract the contents of an OLE object using oleObject.GetEmbeddedObjectBytes() method. This method returns the embedded file as a byte array. This only works on embedded OLE objects and linked OLE objects will return null.

//Get the OLE Object
var myOleObject = worksheet.Drawings["0"] as ExcelOleObject;
//Get the byte array.
var oleBytes = myOleObject.GetEmbeddedObjectBytes();
//Save the byte array to a file.
File.WriteAllBytes("myFile.pdf", oleBytes);

Link to cell inside an embedded xlsx Ole Object.

While EPPlus won't use the formula for calculation, you can still add formulas that reference data inside an OLE Object. The object must be a valid .xlsx file else it might create a corrupt workbook. To add a formula that references a cell in the embedded xlsx file you must first prepare the file by adding a link to it. You can do it by calling CreateLinkToEmbeddedObject() method on the Ole Object. You can then add formulas using the following format: [1]!'!Sheet1!Object 1!OleSheet!R7C1' The [1]! is the index of the external link. !Sheet1 is the name of the sheet in the current workbook. !Object 1 is the name of the OLE Object. !OleSheet is the name of the worksheet inside !Object 1. !R7C1 is referecing the 7th row in the 1st Column.

var oleObject = ws.Drawings[0] as ExcelOleObject;
oleObject.CreateLinkToEmbeddedObject();
ws.Cells["A5"].CreateArrayFormula("[1]!'!Sheet1!Object 1!OleSheet!R7C1'");

Open an embedded xlsx document for editing.

EPPlus supports opening and editing embedded workbooks. You can use the var embeddedPackage = GetEmbeddedPackage() to get the embedded excel package that you can manipulate like any other workbook in EPPlus. To save the changes use SetEmbeddedPackage(embeddedPackage) method.

Notes

• When opening a workbook with an OLE object added with EPPlus in Excel and the DisplayAsIcon property is set to false. The Ole Object will be displayed as an icon until you open the OLE object inside Excel. This is due to limitations in EPPlus how we create the image representing the OLE Object.
• EPPlus has a max file size of 2GB for embedded OLE objects. Larger files will throw an exception.

See also

• Sample 5.6 - OLE Objects (C#)