Python操作Excel自动插入图片

#Python#Excel#图片

工作以来，测试繁多，测试后图片的机械性插入更是让我痛不欲生。正好之前有一些python编程经验，便自我摸索着写了个程序，通过索引数字的方式将图片一一插入Excel。效果甚好，工作效率明显提升。此处将代码贴出，大家一起学习。

#!/usr/bin/env python 
# -*- coding: utf-8 -*- 
import os,sys
import win32com.client
import win32process
import win32api
import win32con
from time import sleep

SheetNumber = 1                 
PictureNewName = []          
DataBuffer = []                 
RowNumber = 1                  
ColumNumber = 1               
TargetColor = 0              
TempColor = 0                 
PicturePositionLeft = 0        
PicturePositionTop = 0         
PictureHeight = 0            
PictureWidth = 0             

def FindExcelName(file_dir,Suffix):                
         L=[]   
         for root, dirs, files in os.walk(file_dir):  
             for file in files:  
                 if os.path.splitext(file)[1] == Suffix:
                     L.append(os.path.join(root, file))  
         return L

print("请注意：操作时请关闭其他Excel,否则该程序在运行时会自动关闭其他文件。")
print("WPS不受该程序影响")
FileRoot = os.getcwd()                       
print(FileRoot)

FileName = FindExcelName(FileRoot,'.xls') or  FindExcelName(FileRoot,'.xlsx')       
print(str(FileName[0]))
HugBuffer = input("输入Excel最大搜索范围（默认50,按Enter键跳过）:") or 50
TargetColorRow,TargetColorColum = input("在Sheet1中指定位置（行，列），用于图片的插入和查询,默认(1,10),按Enter键跳过。例:1,1:") or 1,10
PictureRoot = FileRoot + '\picture'
PictureFileName = FindExcelName(PictureRoot,'.png')

for PictureNumber in range(len(PictureFileName)):
    newstr = PictureFileName[PictureNumber].replace(PictureRoot,'')
    newstr1 = newstr.replace('\\tek','')
    newstr2 = newstr1.replace('.png','')
    PictureNewName.append(int(newstr2))
print('已获取picture文件夹中所有图片名')

xlApp = win32com.client.Dispatch('Excel.Application')   
xlBook = xlApp.Workbooks.Open(FileName[0])                
sht = xlBook.Sheets(1)
TargetColor = sht.Cells(int(TargetColorRow), int(TargetColorColum)).Font.Color
print("sheet的总个数：%d" %(xlBook.Sheets.Count))                

while SheetNumber <= xlBook.Sheets.Count:                   
    sht = xlBook.Sheets(SheetNumber)
    xlBook.RefreshAll
    for RowNumber in range(int(HugBuffer)):
        for ColumNumber in range(int(HugBuffer)):
            TempBuffer = sht.Cells(RowNumber+1, ColumNumber+1).Value
            TempColor = sht.Cells(RowNumber+1, ColumNumber+1).Font.Color      
            if TempBuffer != None and TempColor == TargetColor:
                for PictureNumber in range(len(PictureNewName)):
                    try:
                        if PictureNewName[PictureNumber] == int(TempBuffer):
                            PicturePositionLeft = sht.Cells(RowNumber+1, ColumNumber+1).Left
                            PicturePositionTop = sht.Cells(RowNumber+1, ColumNumber+1).Top
                            PictureHeight = sht.Cells(RowNumber+1, ColumNumber+1).Height - 2
                            PictureWidth  = sht.Cells(RowNumber+1, ColumNumber+1).Width - 2
                            sht.Shapes.AddPicture(PictureFileName[PictureNumber],1,1,PicturePositionLeft,PicturePositionTop,PictureWidth,PictureHeight)
                    except:
                        print('中文已跳过')
    print("已完成 [%s]" %(xlBook.Sheets(SheetNumber).Name))
    SheetNumber = SheetNumber + 1

print("图片插入已完成，即将关闭程序")
sleep(2)

xlBook.Save()
xlBook.Close()

目前程序还有一点小BUG，文件保存有时会出现问题，后面慢慢解决。