Python: Insert or Extract OLE Objects in Excel

OLE enables users to incorporate diverse file types-such as images, charts, documents, and multimedia-directly into Excel workbooks, fostering a more dynamic and comprehensive representation of information. By inserting OLE objects, users can create interactive and engaging spreadsheets that integrate a variety of data formats to simplify analyses and presentations in a single Excel environment. In this article, you will learn how to insert linked or embedded OLE objects to Excel in Python as well as how to extract OLE objects from Excel in Python using Spire.XLS for Python.

• Insert a Linked OLE Object to Excel in Python
• Insert an Embedded Object to Excel in Python
• Extract OLE Objects from Excel in Python

Install Spire.XLS for Python

This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.

pip install Spire.XLS

If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows

Insert a Linked OLE Object to Excel in Python

To insert an OLE object to a worksheet, you use the Worksheet.OleObjects.Add(fileName, image, linkType) method, in which:

• the fileName parameter specifies the path of an external file to be inserted,
• the image parameter specifies a thumbnail of the first page or a document icon that the OLE object will be displayed as,
• the linkType parameter determines whether the OLE object is inserted to the document as an embedded source or a linked source.

The following are the steps to insert a linked OEL object to Excel using Spire.XLS for Python.

• Create a Workbook object.
• Get the first worksheet through Workbook.Worksheet[index] property.
• Load an image using Image.FromFile() method.
• Insert an OLE object to the worksheet using Worksheet.OleObjects.Add() method, and specify the link type as OleLinkType.Link.
• Specify the OLE object location through IOleObject.Location property.
• Specify the OLE object type through IOleObject.ObjectType property.
• Save the workbook to an Excel file using Workbook.SaveToFile() method.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Add text to A1
sheet.Range["A1"].Text = "Here is an OLE Object."

# Load an image to be displayed as an icon of ole object
image = Image.FromFile("C:/Users/Administrator/Desktop/word_icon.png") 
with Stream() as stream:
    image.Save(stream,ImageFormat.get_Png())

    # Add an ole object to the worksheet that links to an external file
    oleObject = sheet.OleObjects.Add("C:/Users/Administrator/Desktop/invoice.docx", stream, OleLinkType.Link)

# Specify ole object location
oleObject.Location = sheet.Range["B3"]

# Specify ole object type
oleObject.ObjectType = OleObjectType.WordDocument

# Save to file
workbook.SaveToFile("output/OleObject.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Insert an Embedded OLE Object to Excel in Python

To insert an embedded OEL object to Excel, you specify the link type as OleLinkType.Embed while invoking the Worksheet.OleObjects.Add() method. The detailed steps are as follows.

• Create a Workbook object.
• Get the first worksheet through Workbook.Worksheet[index] property.
• Load an image using Image.FromFile() method.
• Insert an OLE object to the worksheet using Worksheet.OleObjects.Add() method, and specify the link type as OleLinkType.Embed.
• Specify the OLE object location through IOleObject.Location property.
• Specify the OLE object type through IOleObject.ObjectType property.
• Save the workbook to an Excel file using Workbook.SaveToFile() method.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Add text to A1
sheet.Range["A1"].Text = "Here is an OLE Object."

# Load an image that represents ole object
image = Image.FromFile("C:/Users/Administrator/Desktop/screenshot.png") 
with Stream() as stream:
    image.Save(stream,ImageFormat.get_Png())

    # Add an ole object to the worksheet as embedded source
    oleObject = sheet.OleObjects.Add("C:/Users/Administrator/Desktop/invoice.docx", stream, OleLinkType.Embed)

# Specify ole object location
oleObject.Location = sheet.Range["B3"]

# Specify ole object type
oleObject.ObjectType = OleObjectType.WordDocument

# Save to file
workbook.SaveToFile("output/OleObject.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Extract OLE Objects from Excel in Python

Spire.XLS for Python provides the Worksheet.HasOleObjects property to determine whether a worksheet has OLE objects. If it does, get all the objects through the Worksheet.OleObjects property. Then, determine the type of a particular OEL object and save the OEL as a file of the appropriate document type. The following are the steps to extract OLE objects from Excel using Spire.XLS for Python.

• Create a Workbook object.
• Get a specific worksheet through Workbook.Worksheet[index] property.
• Determine if the worksheet contains OLE objects through Worksheet.HasOleObjects property.
• Get all the OLE objects from the worksheet through Worksheet.OleObjects property.
• Determine the type of a particular OEL object and save the OEL as a file of the appropriate document type.

from spire.xls import *
from spire.xls.common import *

# Write data to file
def WriteAllBytes(fname:str,data):
    fp = open(fname,"wb")
    for d in data:
        fp.write(d)
    fp.close()

# Create a Workbook object
workbook = Workbook()

# Load an Excel document
workbook.LoadFromFile("C:\\Users\\Administrator\\Desktop\\OleObjects.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Determine if the worksheet has ole objects
if sheet.HasOleObjects:

    # Iterate through the found objects
    for obj in sheet.OleObjects:

        # If the object type is a Word document, save it to a .docx file
        type = obj.ObjectType
        if type is OleObjectType.WordDocument:
            WriteAllBytes("output/ExtractedFiles/Word-Extracted.docx", obj.OleData)

        # If the object type is an Adobe Acrobat document, save it to a .pdf file
        if type is OleObjectType.AdobeAcrobatDocument:
            WriteAllBytes("output/ExtractedFiles/PDF-Extracted.pdf", obj.OleData)

        # If the object type is a PowerPoint document, save it to a .pptx file
        if type is OleObjectType.PowerPointPresentation:
            WriteAllBytes("output/ExtractedFiles/PPT-Extracted.pptx", obj.OleData)
workbook.Dispose()

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.