Python pandas 自动自动调整列宽 和加边框

注意openpyxl-3.0.10版本

代码

import numpy as np
import pandas as pd
from openpyxl.styles import Border, Side
from openpyxl.utils import get_column_letter
import pandas as pd
import openpyxl

if __name__ == '__main__':

    excel_file = pd.ExcelFile('source.xlsx')
    thin_border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin'),
    )

    with pd.ExcelWriter('output.xlsx', engine='openpyxl') as writer:
        for sheet_name in excel_file.sheet_names:
            df = excel_file.parse(sheet_name)
            df.to_excel(writer, sheet_name=sheet_name, index=False)

            column_widths = df.columns.str.encode('gbk').str.len().values
            max_widths = df.astype(str).applymap(lambda x: len(str(x).encode('gbk'))).max().values

            widths = np.max([column_widths, max_widths],axis=0)

            worksheet = writer.sheets[sheet_name]
            for i, width in enumerate(widths, 1):
                worksheet.column_dimensions[get_column_letter(i)].width = width+2

            for row in worksheet.iter_rows():
                for cell in row:
                    cell.border = thin_border