前言
偶然想起了这个号， 太久没有写东西了，还是写一写基础知识。今天整理了个django 对于excel的处理，写成了通用类的形式，这样更方便调用。当然，对于python所有的框架都是适用的。
code
# -*- coding: utf-8 -*-
import tempfile
from zipfile import ZipFile, ZIP_DEFLATED
from django.http import HttpResponse, StreamingHttpResponse
from openpyxl import Workbook
from openpyxl.styles import Alignment, Font, Side, Border, PatternFill, Protection
from openpyxl.writer.excel import ExcelWriter
class ExcelUtils:
"""excel处理类"""
def __init__(self, file_name, header: list = None, data: list = None, sheet_name="Sheet"):
"""
:param sheet_name: sheet_name
:param header: 表头
:param data: 数据
"""
self.file_name = file_name
self.sheet_name = sheet_name
self.header = header
self.data = data
def draw(self, header_styles=None, data_styles=None):
"""excel数据、样式设置并返回（导出）"""
if data_styles is None:
data_styles = {}
if header_styles is None:
header_styles = {}
wb, sheet = self.excel_data_deal()
self.excel_header_style(sheet, **header_styles)
self.excel_data_style(sheet, **data_styles)
# return self.excel_file_response(save_virtual_workbook(wb), self.file_name)
return self.excel_stream_response(wb, self.file_name)
def excel_data_deal(self):
"""创建excel并加入数据"""
wb = Workbook()
sheet = wb.create_sheet(self.sheet_name) if self.sheet_name != "Sheet" else wb.active
if self.header is not None:
sheet.append(self.header)
for i, r_data in enumerate(self.data):
for j, c_data in enumerate(r_data):
sheet.cell(row=i + 2, column=j + 1, value=c_data)
return wb, sheet
def excel_header_style(self, sheet, **kwargs):
"""
表头样式设置
rows_width示例：设置E列宽度为30 {"E": 30}
"""
sheet.row_dimensions[1].height = 20
for i, r in enumerate(sheet[1]):
width = len(r.value) * 3
# 获取列字母
column_letter = [chr(i + 65) if i <= 25 else 'A' + chr(i - 26 + 65)][0]
# 列宽, 最大为50
sheet.column_dimensions['{}'.format(column_letter)].width = width if width < 50 else 50
self.write_excel_cell(r, **kwargs)
if kwargs.get("rows_width"):
for k, v in kwargs["rows_width"].items():
sheet.column_dimensions[k].width = v
def excel_data_style(self, sheet, **kwargs):
"""数据样式设置"""
kwargs["fill"] = kwargs.get("fill", {})
data_len = len(self.data)
for i in range(data_len):
for j, _ in enumerate(sheet.column_dimensions):
kwargs["fill"]["fill_color"] = "00FFFF00" if j % 2 == 0 else "00FFFFFF"
self.write_excel_cell(sheet.cell(row=i + 2, column=j + 1), **kwargs)
@staticmethod
def write_excel_cell(cell_obj, value=None, **kwargs):
"""
单元格样式设置
:param cell_obj: 单元格
:param value: 单元格内容
:alignment horizontal: 水平位置 "general", "left", "center", "right",
:alignment vertical: 垂直位置 "top", "center", "bottom", "justify", "distributed"
:alignment wrap: 是否换行
:alignment shrink: 是否缩小填充
:font size: 字体大小
:font bold: 字体是否加粗，True/False
:border side_style: 边框样式 默认thin
:border side_color: 边框颜色 默认000000
:fill fill_type: 填充样式 默认solid
:fill fill_color: 填充颜色 默认无(白色)
"""
if value is not None:
cell_obj.value = value
alignment = kwargs.get("alignment", {})
font = kwargs.get("font", {})
border = kwargs.get("border", {})
fill = kwargs.get("fill", {})
number_format = kwargs.get("number_format", "General")
protection = kwargs.get("protection", {})
# 位置：对齐方式等, 默认居中
cell_obj.alignment = Alignment(horizontal=alignment.get("horizontal", "center"),
vertical=alignment.get("vertical", "center"),
wrapText=alignment.get("wrap", False),
shrinkToFit=alignment.get("shrink", False))
# 字体：字号、字体颜色、下划线等， 默认字体大小为10
cell_obj.font = Font(size=font.get("size", None),
bold=font.get("bold", False))
# 边框样式，默认黑色细线
side = Side(style=border.get("side_style", "thin"),
color=border.get("side_color", "000000"))
cell_obj.border = Border(left=side, right=side, top=side, bottom=side)
# 填充：填充色、填充类型等，默认白色填充
cell_obj.fill = PatternFill(fill_type=fill.get("fill_type", "solid"),
fgColor=fill.get("fill_color", "00FFFFFF"))
# 数据格式
cell_obj.number_format = number_format
# 写保护
cell_obj.protection = Protection(locked=protection.get("locked", True),
hidden=protection.get("hidden", False))
@staticmethod
def excel_file_response(content, file_name):
"""
导出已处理的excel文件
:param content: 文件内容
:param file_name: 文件名
"""
response = HttpResponse(content, content_type='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment;filename=%s' % file_name.encode().decode('latin-1')
return response
def excel_stream_response(self, wb, file_name):
"""返回流式数据"""
response = StreamingHttpResponse(self.wb_iterator(wb), content_type='application/octet-stream')
response['Content-Disposition'] = f'attachment; filename="{file_name}"'
return response
@staticmethod
def wb_iterator(workbook, chunk_size=512):
"""重写save_virtual_workbook方法，适配文件流"""
tmp = tempfile.TemporaryFile()
archive = ZipFile(tmp, 'w', ZIP_DEFLATED, allowZip64=True)
writer = ExcelWriter(workbook, archive)
writer.save()
tmp.seek(0)
while True:
virtual_workbook_content = tmp.read(chunk_size)
if virtual_workbook_content:
yield virtual_workbook_content
else:
tmp.close()
break
写这个类其实就是加深对openpyxl的理解以及使用，如果要实现excel的导出以及样式处理，直接使用draw方法就行，以我在项目的中的使用示例展示：
excel = ExcelUtils(file_name, header, data)
header_styles = dict(font={"size": 12, "bold": True}, fill={"fill_color": "00FF8080"}, rows_width={"E": 30})
data_styles = dict(alignment={"shrink": True})
return excel.draw(header_styles=header_styles, data_styles=data_styles)
只需要给表头和数据设置对应的样式即可，而样式设置在write_excel_cell这个函数中已经做了详细说明。
对于返回数据的问题这里我提供了两个函数excel_file_response和excel_stream_response，从函数名就能知道其意思，如果小文件，一般使用excel_file_response，如果是以文件流形式导出则使用excel_stream_response，这里我重写了save_virtual_workbook方法，通过生成器每次只返回512字节的内容。当然，如果文件比较大建议提高chunk_size。
- 另外，这是根据我个人理解以及体验来写的，有默认的样式，如果这个样式不符合你的需要也可以在单元格样式设置中改成你喜欢的样式即可。如果有不懂的或者错误之处可以在下面留言，copy代码请注明出处。
