python 使用 openpyxl 操作 excel

python 使用 openpyxl 操作 excel

---

前言

二、使用 zipfile、openpyxl、flask 批量导出excel zip

---

1.环境

openyxl：3.0.6

python：3.7.6

pandas：1.3.5

2.读取数据

#使用pandas读取数据
#https://pandas.pydata.org/docs/reference/api/pandas.read_excel.html#pandas.read_excel
pd.read_excel(path, sheet_name=None)

或

#flask form
pd.read_excel(request.files['file'], sheet_name=None)

具体操作

https://pandas.pydata.org/docs/reference/frame.html

3.openpyxl

加载excel

#加载excel
#https://pandas.pydata.org/docs/reference/api/pandas.read_excel.html#pandas.read_excel
wb = load_workbook('./src/statics/clean_production_tmp.xlsx')

创建excel

wb = Workbook()

		e方法	参数	作用	返回
Workbook	get	wb.active		获取当前的sheet页	WorkSheet
Workbook	get	wb.get_sheet_by_name()	str	根据sheet名称获取sheet页	WorkSheet
Workbook	get	wb.copy_worksheet()	worksheet	根据参数 worksheet 复制worksheet	WorkSheet
Workbook	set	wb.remove(worksheet)	worksheet	根据参数 worksheet 复制worksheet	WorkSheet
WorkSheet	set	ws.column_dimensions[‘A’].width = 10		设置列宽
WorkSheet	set	ws.title = ‘123’	str	设置sheet标题
WorkSheet	set	ws.merge_cells(“A1:A4”)	set	合并单元格

保存excel

wb = Workbook()
wb.save('balances.xlsx')

通过接口返回

output = BytesIO()
wb_tmp = load_workbook('./src/statics/clean_production_tmp.xlsx')
wb_tmp.save(output)
output.seek(0)
resp = make_response(output.getvalue())
basename = '2022-3-7.xlsx'

# 转码，支持中文名称
resp.headers["Content-Disposition"] = "attachment; filename*=UTF-8''{utf_filename}".format(utf_filename=basename)
resp.headers['Content-Type'] = 'application/x-xlsx'
return resp