1 所依赖库
xlrd , openpyxl ,win32com
2 打开excel获取sheet 及 sheet中单元格信息
from openpyxl import load_workbook
from openpyxl import Workbook
# 获取excel文件句柄
workbook = load_workbook(fileAbsPath)
# 获取sheet页
worksheet = workbook['采购表']
# 获取B3单元格信息
name = worksheet['B3'].value
workbook.close() |
3 追加写入信息到excel文件中
# 将信息追加到excel表中
def insertRes2outputXlxs(self, outputXlxsAbsPath, res):
workbook = openpyxl.load_workbook(outputXlxsAbsPath) # 读取xlsx文件
sheetnames = workbook.sheetnames # 获取所有sheet的名称
worksheet = workbook[sheetnames[0]] # 获取第一张sheet
nrows = worksheet.max_row
ncols = worksheet.max_column
print("nrows {}, ncols {}".format(nrows, ncols))
# 初始化列名称
colName = ["colName1", "colName2", "colName3", "colName4", "colName5"]
if nrows == 1:
for i in range(1, len(colName)+1):
worksheet.cell(1, i).value = colName[i - 1]
# 填入每行的序号
worksheet.cell(nrows + 1, 1).value = nrows
# 以追加的形式填入信息
for i in range(1, len(self.res) + 1):
worksheet.cell(nrows + 1, i+1).value = self.res[i - 1]
workbook.save(outputXlxsAbsPath)
workbook.close()
|
4 获取excel中图片
from PIL import ImageGrab
import win32com.client as win32
from win32 import win32clipboard
from win32com.client import Dispatch
# 获得excel中图片
def generatePic(self, fileName):
excel = win32.Dispatch("Excel.Application")
#excel.visible = False # 避免打开excel程序
excel.DisplayAlerts = 0 # 不显示警告信息
workbook = excel.Workbooks.Open(fileName, False)
# 以sheet名称形式获取sheet句柄
worksheet = workbook.Worksheets("sheet1")
shape = worksheet.Shapes[0]
# 图片名称
#print(shape.Name)
#print(worksheet.Shapes.Count) # 查看当前工作表的图形个数
shape.Copy()
image = ImageGrab.grabclipboard()
# 以jpg形式保存图片
image.convert("RGB").save("hello.jpg", 'jpeg')
workbook.Close(False)
excel.Quit()
|