python 使用 openpyxl 操作 excel
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
|