Python版本:3.11
# 测试版本为下。其他版本不一定OK 为了减少不必要麻烦 尽量保持一致
openpyxl==3.0.10
pillow==9.4.0
python excel单元格的一些操作方法
import openpyxl
# import re
wb = openpyxl.load_workbook(filename="../csv/FS_THOKS_Testreport.xlsx")
sheet = wb.get_sheet_by_name("Sheet1")
# 仅仅获取有颜色的单元格里面的值和16进制颜色
for row in sheet.rows:
for cell in row:
if cell.fill.start_color.index != "00000000": # 不等于白色背景
print(cell.value, cell.fill.start_color.index, end="\n")
"""
FFFFFF00 对应黄色。其他颜色进制百度网上很多 按照对应关系自行写代码额外展示不一样的效果
output:
评论 FFFFFF00
None FFFFFF00
"""
# 列出所有合并的单元格,格式A1:H1
for cell in sheet.merged_cells.ranges:
print(cell)
"""
output:
A3:A4
B1:C2
"""
# 查找替换单元格中的特定字符
for r in range(1, sheet.max_row + 1):
for c in range(1, sheet.max_column + 1):
s = sheet.cell(r, c)
"""
精确匹配 s==信息,模糊匹配xxx in s
或者正则表达式更高级的匹配例如
import re
...
if re.search("Happy", str(s.value), flags=re.I):
s.value = re.sub("Happy", "Sad", str(s.value), flags=re.I)
"""
# if type(s).__name__ == "MergedCell": # 这个可以判断是否是合并单元格
if s.value != None and "Happy" in str(s.value):
s.value = s.value.replace("Happy", "Sad")
wb.save("../csv/FS_THOKS_Testreport.xlsx")
提取excel中所有的图片类
import zipfile
from PIL import Image
import io
"""
如果要生成新的excel并且里面的内容和原有一摸一样
可以将下面注释的代码放开
"""
def redact_images(filename, FilePath):
# 输出一个新的 excel/doc文件
# outfile = filename.replace(".xlsx", "_redacted.xlsx")
with zipfile.ZipFile(filename) as inzip:
# with zipfile.ZipFile(outfile, "w") as outzip:
for info in inzip.infolist():
name = info.filename
content = inzip.read(info)
if name.endswith(('.png','.jpeg','.gif')):
# fmt = name.split('.')[-1]
Name = name.split('/')[-1]
img = Image.open(io.BytesIO(content))
img.save(FilePath + str(Name))
# outb = io.BytesIO()
# img.save(outb, fmt)
# content = outb.getvalue()
# info.file_size = len(content)
# info.CRC = zipfile.crc32(content)
# outzip.writestr(info, content)
redact_images("../csv/FS_THOKS_Testreport.xlsx", "D:\\images\\")
提取成功后的图片