功能：
1、获取excel所有sheet页名称
2、获取指定sheet页数据,返回[[行数据],...],未指定读取首个sheet页数据
3、获取指定sheet页数据,返回[[列数据],...],未指定读取首个sheet页数据
4、获取指定sheet页的指定列数据,如B列数据,未指定读取首个sheet页数据
5、保存excel指定sheet页某单元格的图片，如G1或G12中的图片
6、保存excel指定sheet页某列中图片，如M或G列中的图片,以G1、G2、G3...命名图片
7、将picpath嵌入单元格cellname中（未完成，目前只能加载到excel）
8、excel写入数据：新增excel、新增sheet页写入
9、删除指定sheet页
# !/usr/bin/env python
# -*-coding:utf-8 -*-
import os,time
import openpyxl
from openpyxl_image_loader import SheetImageLoader
from openpyxl.drawing.image import Image
class HandleExcel:
def __init__(self, filepath):
"""获取文件路径和表单名"""
self.filepath = filepath
# Workbook(): 创建新的excel文件;load_workbook：加载已经存在的文件；
def open(self):
# 加载excel文件
try:
if not os.path.exists(self.filepath):
self.wb = openpyxl.Workbook()
else:
self.wb = openpyxl.load_workbook(self.filepath)
except ValueError:
print(f"{self.filepath},文件打开异常")
def get_sheetnames(self):
self.open()
return self.wb.sheetnames
def read_sheet(self, sheetname=None):
"""获取指定sheet页数据,返回[[行数据],...]"""
# 打开文件，选择表单
self.open()
try:
sheet = self.wb[sheetname] if sheetname is not None else self.wb.worksheets[0]
# print(self.sheet.max_row, self.sheet.max_column) # 获取sheet数据的总行数/总列数
sheetdata = [[cell.value for cell in row ] for row in sheet.rows]
return sheetdata
except:
print(f"*******Worksheet {sheetname} does not exist.")
def read_sheetcol(self, sheetname=None):
"""获取指定sheet页数据,返回[[列数据],...]"""
# 打开文件，选择表单
self.open()
try:
sheet = self.wb[sheetname] if sheetname is not None else self.wb.worksheets[0]
sheetdata = [[cell.value for cell in column ] for column in sheet.columns]
return sheetdata
except:
print(f"Worksheet {sheetname} does not exist.")
def get_coldata(self, sheetname=None,column='A'):
"""
获取指定sheet页的指定列数据,如B列数据
:param sheetname:
:param column: 列，如A、B、...
:return:
"""
self.open()
try:
sheet = self.wb[sheetname] if sheetname is not None else self.wb.worksheets[0]
return [cell.value for cell in sheet[column]]
except:
print(f"Worksheet {sheetname} does not exist.")
def downlload_excel_pic(self, sheetname,cellname, picpath):
"""
保存excel指定sheet页某单元格的图片，如G1或G12中的图片
cellname,单元格，如G1、G12、M1...
picpath,图片本地存储地址
"""
# 若存储目录不存在，新建
picdir = os.path.dirname(picpath)
if not os.path.exists(picdir):
os.makedirs(picdir)
# 打开文件，选择表单
self.open()
sheet = self.wb[sheetname]
image_loader = SheetImageLoader(sheet)
try:
# 保存图片
image = image_loader.get(cellname) # E1的图片
if os.path.exists(picpath):
os.remove(picpath)
image.save(picpath) # 保存路径
return picpath
except ValueError:
# print(f"{colname},单元格没有图片或图片超出单元格")
pass
self.wb.close()
def batch_downlload_excel_pic(self, sheetname,colname, savedir):
"""
保存excel指定sheet页某列中图片，如M或G列中的图片,以G1、G2、G3...命名图片
colname，图片所在列名,如M、G、I...
savedir，图片本地存储地址
"""
if not os.path.exists(savedir):
os.makedirs(savedir)
# 打开文件，选择表单
self.open()
sheet = self.wb[sheetname]
image_loader = SheetImageLoader(sheet)
L = sheet.max_row
start = time.perf_counter()
for i in range(1, L + 1):
try:
# 保存图片
picname = f'{colname}{str(i)}.png'
picpath = os.path.join(savedir, picname)
image = image_loader.get(colname + str(i)) # E列的图片
if not os.path.exists(picpath):
image.save(picpath) # 保存路径
except ValueError:
# print(f"{'E'+str(i)},这一行没有图片或图片超出单元格")
pass
a, b = int(i / L * 50), int((L - i) / L * 50)
print(
"\r图片下载进度：{:^3.0f}%<{}>{}{:.2f}s".format((i / L) * 100, "▋" * a, "-" * b, time.perf_counter() - start),
end="")
def insert_pic(self,sheetname, cellname, picpath):
"""将picpath嵌入单元格cellname中"""
try:
img = Image(picpath)
self.open()
sheet = self.wb[sheetname]
sheet.add_image(img, cellname)
self.wb.save(self.filepath)
self.wb.close()
except ValueError:
print(f"{cellname},单元格嵌入图片失败")
pass
def write_sheet(self, sheetname,row, column, value):
"""excel写入数据"""
# 打开文件，选择表单
self.open()
# sheet页是否存在
if sheetname not in self.wb.sheetnames:
# sheet = self.wb.create_sheet(sheetname,0) # 新增sheet页，插在首位
sheet = self.wb.create_sheet(sheetname) # 新增sheet页，默认插在末尾
else:
sheet = self.wb[sheetname]
# 写入数据
sheet.cell(row=row, column=column, value=value)
self.wb.save(self.filepath)
self.wb.close()
def remove_sheet(self,sheetname):
"""删除指定sheet页"""
self.open()
sheet = self.wb[sheetname]
self.wb.remove(sheet)
self.wb.save(self.filepath)
self.wb.close()
if __name__ == '__main__':
excelpath = r"C:\Users\ychen\Downloads\\test_0623.xlsx"
sheetname = "交通灯"
savedir = r'E:\Attachments' # 保存目录
# 获取所有sheetname
excelfile = HandleExcel(excelpath)
sheetnames = excelfile.get_sheetnames()
print(sheetnames)
# # 读取指定sheet页数据
# alldata = HandleExcel(excelpath).read_sheet()
# print(alldata)
# # alldata = HandleExcel(excelpath).read_sheetcol(sheetname)
# alldata = HandleExcel(excelpath).read_sheet(sheetname)
# print(alldata)
# coldata = HandleExcel(excelpath).get_coldata(sheetname,'B')
# print(coldata)
# # 批量保存指定列的图片
# excelfile = HandleExcel(excelpath)
# excelfile.batch_downlload_excel_pic(sheetname,"E", savedir)
# # 批量单元格图片
# picpath = r'E:\Attachments\test-E4.png'
# excelfile = HandleExcel(excelpath)
# excelfile.downlload_excel_pic(sheetname,"E4", picpath)
# # # 写入sheet页数据
# excelfile = HandleExcel(excelpath)
# excelfile.write_sheet('sheetname',1,2,'test')
#
# # # 删除指定sheet页数据
# excelfile = HandleExcel(excelpath)
# sheets = ['锥桶','路面箭头', '交通牌','交通灯','斑马线']
# for s in sheetnames:
# if s not in sheets:
# excelfile.remove_sheet(s)本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
