Django+ openpyxl 导出文件，设置表头／内容格式

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

之前使用xlrd、xlrt处理文件的导入导出，这两个主要用于excel2003格式的文件的读写，并且xlrt最多可以写入256行，大于256行会报错

找了新插件openpyxl，对它找到针对某一行设置大小和对齐方式的方法，使用了下面的笨办法
官网：https://openpyxl.readthedocs.io/en/stable/styles.html#cell-styles-and-named-styles

from openpyxl import Workbook
from openpyxl.styles import Alignment,Font,colors

def export(request,sql,sheet_name):
wb = Workbook() #optimized_write=True
# 创建一个sheet
w = wb.create_sheet(sheet_name, 0) # 创建连接
connection = pymysql.connect(**config)
try:
with connection.cursor() as cursor:
# 执行sql语句，插入记录
cursor.execute(sql);
results = cursor.fetchall()
# 没有设置默认自动提交，需要主动提交，以保存所执行的语句
connection.commit()
finally:
connection.close();
resultslist = list(results) i = 1
for k, v in resultslist[0].items():
w.cell(row=1, column=i).value = k.replace("t_", "").replace("F_", "")
s = chr(i + 64) + str(1)
w[s].alignment = Alignment(horizontal='center', vertical='center')
w[s].font = Font(size=18, bold=True)
w.column_dimensions[chr(i + 64)].width = 35
i = i + 1
excel_row = 2
# 写入数据
for i in range(0, len(resultslist)):
excel_col = 1
for k, v in resultslist[i].items():
w.cell(row=excel_row, column=excel_col).value = v
s=chr(excel_col+64)+str(excel_row)
w[s].alignment= Alignment(horizontal='center', vertical='center')
w[s].font = Font(size=16)
excel_col = excel_col +1
excel_row = excel_row + 1 exist_file = os.path.exists("%s.xlsx" %sheet_name)
if exist_file:
os.remove("%s.xlsx" %sheet_name) response = HttpResponse(content_type='application/msexcel')
response['Content-Disposition'] = 'attachment;filename=%s.xlsx' % sheet_name
wb.save(response)
return response