python 使用 openpyxl 操作 excel
文章目录
- python 使用 openpyxl 操作 excel
- 前言
- 1.环境
- 2.读取数据
- 3.openpyxl
前言
二、使用 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.html3.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