openpyxl使用总结

设置表头单元格的颜色

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)