注意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