Python automated office: Efficiently import and export all Excel pictures in one minute

In our day-to-day work, we often need to insert or export images in Excel sheets, especially when making product catalogs, reports, or presenting data. Now there is such a task, the product catalog needs to insert the actual picture of each product into the D column, there are hundreds of products, the traditional method needs to paste the pictures one by one into excel, and then resize the pictures, this process is time-consuming and laborious, and easy to make mistakes. Luckily, Python provides powerful libraries to help us automate these tasks.

一、Python导入图片到Excel

Using Python to import pictures into Excel sheets in bulk can greatly improve our work efficiency. Here's a simple example code that shows how to add pictures to an Excel sheet using the openpyxl library:

import os  
import glob  
from openpyxl import load_workbook  
from openpyxl.drawing.image import Image

def write_images_to_excel(image_paths, excel_file):
    wb = load_workbook(excel_file)  
    ws = wb.active 

    for path in image_paths:
    # 创建Image对象
        img = Image(path)

        # 调整图⽚⼤⼩，假设放在A列，1:1⽐例
        img.width, img.height = 150, 50

        # 添加图⽚到⼯作表，放在图⽚名对应的⾏
        ws.add_image(img, 'D' + str(image_paths.index(path) + 3))

    wb.save(excel_file)

# ⽰例图⽚路径和Excel⽂件名


def get_image_paths(folder_path):  
    image_paths = []  
    # 使用glob模块匹配所有jpg和png图片  
    for extension in ['*.jpg', '*.png']:  
        image_paths.extend(glob.glob(os.path.join(folder_path, extension)))  
    return image_paths  

# 使用你的文件夹路径替换下面的'your_folder_path'  
folder_path = 'extracted_images'  
image_paths = get_image_paths(folder_path)  
print(image_paths)

excel_file = 'test.xlsx'
write_images_to_excel(image_paths, excel_file)           

This code first opens Excel where you need to insert the picture and gets the active worksheet. It then iterates through a list of picture paths and creates an Image object for each path. Next, it sets the size of the picture and adds it to the specified location in the worksheet. Finally, save the workbook.

2. Python exports images from Excel

Sometimes, we may need to extract images from Excel files, for example for backup, further processing, or use in other applications. Although the openpyxl library itself does not provide the ability to extract images directly from Excel, we can combine other methods to achieve this need. Here's a sample code:

import os  
import zipfile  
from openpyxl import load_workbook  

# 加载包含图片的Excel工作簿  
wb = load_workbook('VFTLURE.xlsx')  
ws = wb.active  

# 创建输出目录来保存提取的图片  
output_dir = 'extracted_images'  
if not os.path.exists(output_dir):  
    os.makedirs(output_dir)  

# Excel文件作为ZIP归档处理  
with zipfile.ZipFile('VFTLURE.xlsx', 'r') as zip_ref:  
    # 遍历ZIP归档中的文件，查找图片文件  
    for zip_info in zip_ref.infolist():  
        if zip_info.filename.endswith(('.png', '.jpg', '.jpeg', '.gif', '.bmp')):  
            # 提取图片到输出目录  
            img_path = os.path.join(output_dir, os.path.basename(zip_info.filename))  
            with zip_ref.open(zip_info) as img_file, open(img_path, 'wb') as out_file:  
                out_file.write(img_file.read())  

print(f"Images extracted to {output_dir}")           

This code first loads the Excel workbook that contains the pictures. Then, it creates an output directory to save the extracted pictures. Next, it treats the Excel file as a ZIP archive and goes through all the files in it. For each file that ends in picture format, it extracts it into the output directory. Finally, it prints a message indicating that the picture has been successfully extracted to the specified directory. All the images in Excel above are extracted into the definition folder

summary

Through Python automation, we can easily import and export pictures in Excel in batches, which greatly improves work efficiency and accuracy. Whether it's creating a catalog, a report, or other scenario where you need to process a lot of images, these automated scripts can save us a lot of time and effort.