设置表头单元格的颜色
fill = PatternFill("solid", fgColor='FF000000')
font = Font(color='00FFFFFF')
for index, v in enumerate(header):
d = self.ws.cell(row=1, column=index + 1)
d.value = v
d.fill = fill
d.font = font
设置表头单元格的长度
self.ws.column_dimensions[row[0]].width = row[1]
单元格保存图片
获取图片
img = Image(image_path)
设置图片宽高
img.width, img.height = 100, 100
保存图片
self.ws.add_image(img, f'F{index_row + 2}')
设置图片单元格高度；宽度由header设置
self.ws.row_dimensions[index_row + 2].height = 100
封装的脚本代码
from openpyxl.styles import PatternFill, Font
from openpyxl.drawing.image import Image
from openpyxl import Workbook
from typing import List
import os
from core.config import settings
class Excel:
def __init__(self, excel_name):
self.excel_name: str = excel_name
self.excel_path: str = f'{settings.TEMP_FILE}/{self.excel_name}'
self.wb = Workbook()
self.ws = self.wb.active
def write_header(self, header: List[str], column_width: List[list]):
"""
headers: 表头内容
column_dimensions：表头的间隔
"""
# 设置表头样式：黑色填充，白色字体
fill = PatternFill("solid", fgColor='FF000000')
font = Font(color='00FFFFFF')
for index, v in enumerate(header):
d = self.ws.cell(row=1, column=index + 1)
d.value = v
d.fill = fill
d.font = font
# 设置列间隔
for row in column_width:
self.ws.column_dimensions[row[0]].width = row[1]
def write_body(self, rows: List[dict]):
"""
rows: [[1,2,3],[1,2,3]]
"""
for index_row, row in enumerate(rows):
index = 1
for v in row.values():
self.ws.cell(row=index_row + 2, column=index).value = v
index += 1
return self.__save()
def write_body_with_image(self, rows: List[dict]):
"""
rows: [[1,2,3],[1,2,3]],
"""
for index_row, row in enumerate(rows):
index = 1
for v in row.values():
if index == 6:
# 单元格保存图片用add_image
image_path = f'{settings.BASE_PATH}/{v}'
img = Image(image_path)
# 设置宽高
img.width, img.height = 100, 100
self.ws.add_image(img, f'F{index_row + 2}')
# 设置图片单元格高度；宽度由header设置
self.ws.row_dimensions[index_row + 2].height = 100
else:
self.ws.cell(row=index_row + 2, column=index).value = v
index += 1
return self.__save()
def __save(self):
try:
self.wb.save(self.excel_path)
return True
except Exception as e:
print(f'[保存Excel报错] {e}')
return False
# def __del__(self):
# os.remove(self.excel_name)
Hole yor life get everything if you never give up.
Original: https://www.cnblogs.com/1fengchen1/p/16079830.html
Author: SonnyZhang
Title: openpyxl使用总结
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/643446/
转载文章受原作者版权保护。转载请注明原作者出处！