In Excel, adding images can make worksheets more visually appealing and easier to understand. For instance, when organizing a client list, you might want to associate each client with their company logo for quicker identification. On the other hand, when preparing an Excel file for internal data archiving, a simplified version without images may be more appropriate. This guide will show you how to insert or delete images in Excel using Python, enabling batch processing to save time and boost productivity.
Python Library to Insert or Delete Images in Excel
Many Python editors, like VS Code, allow you to install third-party libraries to simplify your workflow. Libraries such as OpenPyXL, Spire, XlsxWriter, Aspose, and xlrd are popular options. Among them, Spire stands out for simple and intuitive APIs and regular updates to support the latest Excel features and meet developers’ needs. For that, this article will demonstrate how to insert and delete pictures using Spire.XLS for Python (the full name of the Spire Excel library). Don't worry—if you use other libraries, the procedures are generally similar, differing mainly in the API.
How to Insert Images in Excel Files Easily
When it comes to inserting an image in Excel spreadsheets, the steps include loading or creating an Excel file, specifying the file path of images, and inserting the image into the Excel file using the Worksheet.Pictures.Add() method offered by Spire. Let’s take a closer look!
Steps to insert an image in a cell in Excel worksheets:
Create an instance of the Workbook class.
Get a worksheet using the Workbook.Worksheets[] property.
Insert an image into an Excel cell through the Worksheet.Pictures.Add() method.
Set the width and height of the image, the column width and row height, and the cell border.
Save the updated Excel workbook with the Workbook.SaveToFile() method.
Here is the code example of inserting an image in the 1st worksheet of an Excel file:
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Get the first worksheet.
sheet = workbook.Worksheets[0]
# Add a picture to a specific cell
imgPath = "/image.png"
picture = sheet.Pictures.Add(1, 3, imgPath)
# Set the picture width and height
picture.Width = 150
picture.Height = 150
# Adjust the column width and row height so that the cell can accommodate the picture
sheet.Columns[2].ColumnWidth = 25
sheet.Rows[0].RowHeight = 135
# Set the distance between cell border and image
picture.LeftColumnOffset = 90
picture.TopRowOffset = 20
# Save to file
workbook.SaveToFile("/InsertImage.xlsx", ExcelVersion.Version2013)
# Release instance
workbook.Dispose()
How to Delete All Images from Excel Workbook Batchly
For document storage or archiving, a clean Excel sheet without images is often a better option. It not only saves storage space but also ensures a more concise and streamlined presentation of data. With Spire.XLS for Python, you can use the Worksheet.Pictures.Remove() method to efficiently remove all pictures from an Excel sheet in one go, simplifying the process and saving time.
Steps to delete all images from Excel worksheets:
Create an object of the Workbook class, and use the Workbook.LoadFromFile() method to read an Excel spreadsheet with images.
Get a worksheet using the Workbook.Worksheets[] property.
Loop through all images in the worksheet.
Delete all images from the Excel worksheet using the Worksheet.Pictures.Remove() method.
Save the modified Excel file through the Workbook.SaveToFile() method.
The following code deletes all images from the first worksheet of an Excel file. Instructions for deleting specific pictures are provided as comments within the example.
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("/InsertImage.xlsx")
# Get the first worksheet
sheet = workbook.Worksheets[0]
# Delete all pictures from the worksheet
for i in range(sheet.Pictures.Count - 1, -1, -1):
sheet.Pictures[i].Remove()
# Delete a specific picture (commented out as an example)
# sheet.Pictures[imgIndex].Remove()
# Save to file
workbook.SaveToFile("/DeleteImage.xlsx", ExcelVersion.Version2013)
# Release the resource
workbook.Dispose()
The Conclusion
This page demonstrates how to easily insert or delete images in Excel files, complete with step-by-step instructions and code examples. By the end, you'll see that managing images in Excel is a breeze!
ALSO READ
How to Copy a Worksheet in Excel Files with Python: Step-by-step Tips
Hide and Unhide Rows and Columns in Excel Using Python [Quick and Effective]
How to Delete Rows and Columns from Excel in Python: Quick Guide
[A Complete Guide] How to Insert a Row and a Column in Excel with Python