pandas 如何设置excel格式

Pandas可以使用`openpyxl`或`xlsxwriter`库来设置Excel文件的格式。以下是一些示例代码：

1. 使用`openpyxl`设置Excel格式:

import pandas as pd
from openpyxl.styles import Color, PatternFill, Font, Border
from openpyxl.styles import colors
from openpyxl.utils import get_column_letter

# 读取数据
df = pd.read_excel('data.xlsx')

# 创建工作簿和工作表对象
wb = openpyxl.Workbook()
ws = wb.active

# 写入数据
for r in dataframe_to_rows(df, index=False, header=True):
    ws.append(r)

# 设置表头格式
header_font = Font(name='Calibri', bold=True, color=colors.WHITE)
header_fill = PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid')
header_border = Border(left=Side(border_style='thin', color='FF000000'),
                       right=Side(border_style='thin', color='FF000000'),
                       top=Side(border_style='thin', color='FF000000'),
                       bottom=Side(border_style='thin', color='FF000000'))
for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill
    cell.border = header_border

# 自适应列宽
for col in ws.columns:
    max_length = 0
    column = col[0].column_letter  # Get the column name
    for cell in col:
        try:
            if len(str(cell.value)) > max_length:
                max_length = len(str(cell.value))
        except:
            pass
    adjusted_width = (max_length + 2)
    ws.column_dimensions[column].width = adjusted_width

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

2. 使用`xlsxwriter`设置Excel格式:

import pandas as pd
import xlsxwriter

# 读取数据
df = pd.read_excel('data.xlsx')

# 创建工作簿和工作表对象
workbook = xlsxwriter.Workbook('data_formatted.xlsx')
worksheet = workbook.add_worksheet()

# 设置表头格式
header_format = workbook.add_format({
    'bold': True,
    'text_wrap': True,
    'valign': 'top',
    'fg_color': '#D7E4BC',
    'border': 1})

# 写入数据
for col_num, col_name in enumerate(df.columns):
    worksheet.write(0, col_num, col_name, header_format)
for row_num, row_data in enumerate(df.values):
    for col_num, col_data in enumerate(row_data):
        worksheet.write(row_num+1, col_num, col_data)

# 自适应列宽
for i, col in enumerate(df.columns):
    column_len = df[col].astype(str).str.len().max()
    column_len = max(column_len, len(col))
    worksheet.set_column(i, i, column_len)

# 保存文件
workbook.close()

以上两种方法都可以设置Excel文件的格式，可以根据需要选择其中一种。