df保为excel
pandas的dataframe保存为excel,设置列宽,行高和自动换行。
from openpyxl.styles import Alignment, Font
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.workbook import Workbook
def save_excel(df, filename, col_width=50, row_height=40):
wb = Workbook()
ws = wb.active
# 设置表头字体为加粗
bold_font = Font(bold=True)
# 将DataFrame写入Excel工作表
for r_idx, row in enumerate(dataframe_to_rows(df, index=False, header=True)):
for c_idx, value in enumerate(row, 1):
cell = ws.cell(row=r_idx + 1, column=c_idx, value=value)
# 设置表头加粗
if r_idx == 0:
cell.font = bold_font
# 设置单元格的换行功能
cell.alignment = Alignment(wrap_text=True)
# 调整列宽
for col in ws.columns:
column = col[0].column_letter
ws.column_dimensions[column].width = col_width
# 调整行高
for row in ws.iter_rows():
for cell in row:
if cell.row > 1: # 不调整第一行(表头)
ws.row_dimensions[cell.row].height = row_height
# 保存Excel文件
wb.save(filename)