将pandas数据框导出到Excel并设置列宽和文本换行

import pandas as pd
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.styles import Alignment

# 创建一个pandas数据框
data = {'Name': ['John', 'Emma', 'Mike'],
        'Age': [25, 28, 30],
        'City': ['New York', 'London', 'Paris']}
df = pd.DataFrame(data)

# 创建一个Excel工作簿和工作表
wb = Workbook()
ws = wb.active

# 将数据框的数据写入工作表
for r in dataframe_to_rows(df, index=False, header=True):
    ws.append(r)

# 设置列宽和文本换行
for column in ws.columns:
    max_length = 0
    column = [cell for cell in column]
    for cell in column:
        try:
            if len(str(cell.value)) > max_length:
                max_length = len(cell.value)
        except:
            pass
    adjusted_width = (max_length + 2) * 1.2
    ws.column_dimensions[get_column_letter(column[0].column)].width = adjusted_width
    for cell in column:
        cell.alignment = Alignment(wrap_text=True)

# 保存Excel文件
wb.save('output.xlsx')