Technology Sharing

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

Excel is a tool mainly used to process tables and data. We can also insert, edit or manage pictures in it to add visual effects to the worksheet and enhance the appeal of the report. This article will introduce in detail how to use Python to operate pictures in Excel, including the following 4 basic examples:

First you need to install a third-party Python library - Spire.XLS for PythonYou can download the product and then install it from a local path, or you can directly use the following pip command to install it:

pip install Spire.XLS

Python inserts pictures into Excel

We can use the Spire.XLS for Python library to insert a picture into a specified cell and set the picture size, etc. The operation is as follows:

1. Create a workbookWorkbookobject and get the specified worksheet;
2. useWorksheet.Pictures.Add(int topRow, int leftColumn, Image image)The method inserts a picture into the specified cell of the worksheet;
3. passExcelPictureThe attributes under the class set the image's width, height, distance from the cell border, etc.;
4. useSaveToFile()Method to save the Excel file.

Sample code:

from spire.xls import *
from spire.xls.common import *
     
# 创建Workbook对象
workbook = Workbook()

# 获取第一张工作表
sheet = workbook.Worksheets[0]

# 在指定单元格中插入图片（此处为第一行第二列，即B1单元格）
picture = sheet.Pictures.Add(1, 2, "示例.png")

# 设置图片宽高度
picture.Width = 120
picture.Height = 120

# 调整图片所在位置的列宽和行高
sheet.Columns[1].ColumnWidth = 20
sheet.Rows[0].RowHeight = 110

# 设置单元格边框与图片之间的距离
picture.LeftColumnOffset = 90
picture.TopRowOffset = 20

# 保存文件
workbook.SaveToFile("插入图片.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Python replaces pictures in Excel

To replace the original picture in Excel with a new picture, you can refer to the following steps:

1. Load the Excel file and get the specified worksheet;
2. passWorksheet.Pictures[]Attributes get the specified image;
3. Load a new graph and passExcelPicture.PictureThe property replaces the specified image with a new image;
4. useSaveToFile()Method to save the Excel file.

Sample code:

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

# 加载Excel文档
workbook = Workbook()
workbook.LoadFromFile ("插入图片.xlsx")

# 获取第一张工作表
sheet = workbook.Worksheets[0]

# 获取工作表中第一张图片
excelPicture = sheet.Pictures[0]
            
# 替换图片
excelPicture.Picture = Image.FromFile("pic.jpg")

# 保存文档
workbook.SaveToFile("替换图片.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Python extracts pictures from Excel

Spire.XLS for Python library can also read images in Excel documents and save them to a specified file path. The steps are as follows:

1. Load the Excel file and get the specified worksheet;
2. Traverse all pictures in the worksheet;
3. transferExcelPicture.Picture.Save()Method to extract pictures from Excel.

Example code:

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

# 加载Excel文档
workbook = Workbook()
workbook.LoadFromFile("图标.xlsx")

# 获取第一张工作表
sheet = workbook.Worksheets[0]

# 获取工作表中所有图片
for i in range(sheet.Pictures.Count - 1, -1, -1):
    pic = sheet.Pictures[i]

    # 保存图片
    pic.Picture.Save("提取图片\图片-{0:d}.png".format(i), ImageFormat.get_Png())

workbook.Dispose()

Python deletes pictures in Excel

Worksheet.Pictures[imgIndex].Remove() The method allows us to delete a specified picture by index. To delete all pictures in Excel, you can traverse each picture and then delete it. The sample code is as follows:

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

# 加载Excel文档
workbook = Workbook()
workbook.LoadFromFile("图标.xlsx")

# 获取第一张工作表
sheet = workbook.Worksheets[0]

# 删除工作表中所有图片
for i in range(sheet.Pictures.Count - 1, -1, -1):
    sheet.Pictures[i].Remove()

# 删除指定图片
# sheet.Pictures[imgIndex].Remove()

# 保存文档
workbook.SaveToFile("删除图片.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

The above examples can help you master the skills of inserting, replacing, and deleting pictures in Excel using Python, and perform some basic picture settings. If you have any questions, please go toforumcomminicate.