windowsforms-405464-controls-and-libraries-spreadsheet-charts-and-graphics-ole-objects.md
OLE (Object Linking and Embedding) technology allows you to insert data (spreadsheets, images, presentations, charts, equations, and much more) from external applications into your document. Use the Spreadsheet control’s API to load, add, extract, or remove OLE objects. You can print documents with OLE objects and export them to PDF.
The Spreadsheet control supports OLE objects in OpenXML-based (XLSX, XLSM, XLTX, and XLTM) and binary (XLS and XLSB) formats.
Note
The Spreadsheet control does not contain UI elements to manage OLE objects.
The Spreadsheet control displays OLE objects as images. You cannot modify embedded data or open files associated with OLE objects.
The Worksheet.OleObjects property returns an OleObjectCollection that stores all OLE objects within the worksheet. Each individual OLE object within the collection is an OleObject interface.
using DevExpress.Spreadsheet;
Worksheet worksheet = spreadsheetControl1.Document.Worksheets[0];
// Get the collection of OLE objects:
OleObjectCollection oleObjects = worksheet.OleObjects;
Imports DevExpress.Spreadsheet
Dim worksheet As Worksheet = spreadsheetControl1.Document.Worksheets(0)
' Get the collection of OLE objects:
Dim oleObjects As OleObjectCollection = worksheet.OleObjects
You use the following OleObject members to obtain information about the corresponding OLE object:
InsertTypeReturns how the OLE object is stored within the worksheet. Possible options: Linked, Embedded.AsEmbeddedContent()
Allows you to access the content of an embedded OLE object. You can call the OleObjectEmbeddedContent.GetRawData method to obtain raw binary content or call the OleObjectEmbeddedContent.SaveAs method to save the embedded content to a file or stream.
var embeddedContent = worksheet.OleObjects[0].AsEmbeddedContent();
var contentData = embeddedContent.GetRawData();
Dim embeddedContent = worksheet.OleObjects(0).AsEmbeddedContent()
Dim contentData = embeddedContent.GetRawData()
Allows you to access the content of a linked OLE object. For linked OLE objects, you can use the OleObjectLinkedContent.FileName property to obtain the corresponding file name.
var linkedContent = worksheet.OleObjects[0].AsLinkedContent();
var path = linkedContent.FileName;
Dim linkedContent = worksheet.OleObjects(0).AsLinkedContent()
Dim path = linkedContent.FileName
Returns an ID that identifies the content type (for example, “ExcelWorksheet” or “AdobeAcrobatDocument”). See the OleObjectType structure for all possible options.
var typeID = worksheet.OleObjects[0].OleObjectTypeId;
Dim typeID = worksheet.OleObjects(0).OleObjectTypeId
RepresentationTypeReturns how the OLE object is displayed in the worksheet. Possible options: Content, Icon.
You can also call OleObjectCollection.GetOleObjectById and OleObjectCollection.GetOleObjectsByName methods to obtain a specific OLE object by its ID or name.
OLE objects can also be accessed in the Worksheet.Shapes collection:
foreach (var obj in worksheet.Shapes) {
if (obj.ShapeType is DevExpress.Spreadsheet.ShapeType.OleObject) {
// Do something
}
}
For Each obj In worksheet.Shapes
If TypeOf obj.ShapeType Is DevExpress.Spreadsheet.ShapeType.OleObject Then
' Do something
End If
Next
A worksheet stores linked objects as links to corresponding files.
Call the OleObjectCollection.AddLinkedOleObject method to add a linked OLE object to a worksheet:
Worksheet worksheet = workbook.Worksheets[0];
CellRange oleIconRange = worksheet.Range["B4:D6"];
SpreadsheetImageSource oleIcon = SpreadsheetImageSource.FromFile("oleIcon.png");
// Create a linked OLE object
OleObject oleObjectLinked = worksheet.OleObjects.AddLinkedOleObject(
oleIconRange, "package.pdf", OleObjectType.PackageWithExtension("pdf"), oleIcon);
Dim worksheet As Worksheet = workbook.Worksheets(0)
Dim oleIconRange As CellRange = worksheet.Range("B4:D6")
Dim oleIcon As SpreadsheetImageSource = SpreadsheetImageSource.FromFile("oleIcon.png")
' Create linked OLE object
Dim oleObjectLinked As OleObject = worksheet.OleObjects.AddLinkedOleObject(oleIconRange, "package.pdf", OleObjectType.PackageWithExtension("pdf"), oleIcon)
A worksheet stores embedded objects incorporated directly in the spreadsheet document in a binary format.
Call the OleObjectCollection.AddEmbeddedOleObject method to add an embedded OLE object to a worksheet:
Worksheet worksheet = workbook.Worksheets[0];
CellRange oleIconRange = worksheet.Range["B4:D6"];
SpreadsheetImageSource oleIcon = SpreadsheetImageSource.FromFile("oleIcon.png");
// Create an embedded OLE object from byte array
byte[] sourceData = File.ReadAllBytes("package.pdf");
OleObject oleObjectEmbedded1 = worksheet.OleObjects.AddEmbeddedOleObject(
oleIconRange, sourceData, OleObjectType.PackageWithExtension("pdf"), oleIcon);
// Create an embedded OLE object from file stream
using (var stream = File.OpenRead("package.pdf")) {
OleObject oleObjectEmbedded2 = worksheet.OleObjects.AddEmbeddedOleObject(
oleIconRange, stream, OleObjectType.PackageWithExtension("pdf"), oleIcon);
}
Dim worksheet As Worksheet = workbook.Worksheets(0)
Dim oleIconRange As CellRange = worksheet.Range("B4:D6")
Dim oleIcon As SpreadsheetImageSource = SpreadsheetImageSource.FromFile("oleIcon.png")
' Create an embedded OLE object from byte array
Dim sourceData As Byte() = File.ReadAllBytes("package.pdf")
Dim oleObjectEmbedded1 As OleObject = worksheet.OleObjects.AddEmbeddedOleObject(oleIconRange, sourceData, OleObjectType.PackageWithExtension("pdf"), oleIcon)
' Create an embedded OLE object from file stream
Using stream = File.OpenRead("package.pdf")
Dim oleObjectEmbedded2 As OleObject = worksheet.OleObjects.AddEmbeddedOleObject(oleIconRange, stream, OleObjectType.PackageWithExtension("pdf"), oleIcon)
Call the following methods to remove an OLE object from the document:
OleObject oleObject = worksheet.OleObjects[0];
// Remove the current OLE object
oleObject.Delete();
// or
worksheet.OleObjects.Remove(oleObject);
// or
worksheet.OleObjects.RemoveAt(0);
// Remove all OLE objects in the collection
worksheet.OleObjects.Clear();
Dim oleObject As OleObject = worksheet.OleObjects(0)
' Remove the current OLE object
oleObject.Delete()
' or
worksheet.OleObjects.Remove(oleObject)
' or
worksheet.OleObjects.RemoveAt(0)
' Remove all OLE objects in the collection
worksheet.OleObjects.Clear()