python - pandas 和 openpyxl 在添加工作表时创建损坏的 excel 文件

Question

我正在尝试使用创建一个 excel 文件pandas.DataFrame.to_excel，向其中添加一张或多张工作表并更改单元格的格式。我找到了一种方法来做到这一点，但最终总是有一个损坏的 excel 文件。我想我以某种错误的方式使用了 writer 对象。

import os
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Alignment

def excelcellwitdh(wb, sheet, cols, exclude, align):
    # function that adjusts cell width and centers cells
    ws = wb[sheet]
    count = -1
    for column in ws.columns:
        count +=1
        if cols[count] not in exclude:
            length = max(len(str(cell.value)) for cell in column)
            ws.column_dimensions[column[0].column_letter].width = length*1.15
            
            if align == "center":
                for cell in column:
                    cell.alignment = Alignment(horizontal='center') 

directory = "Path"
# provide directory here which will contain the data for my DataFrames
outputfile = os.path.join(directory, "Output_"+directory.split("\\")[-1]+".xlsx")
# this file does not exist yet

df1 = pd.DataFrame([['a', 'b'], ['c', 'd']],
                   index=['row 1', 'row 2'],
                   columns=['col 1', 'col 2'])
df2 = pd.DataFrame(columns= ["D", "E", "F"])
# create df1 and df2 (For simplicity I created two dummy DataFrames)

df1.to_excel(outputfile, sheet_name='1', index = None)
# create excel file with df1

wb = load_workbook(outputfile)
writer = pd.ExcelWriter(outputfile, engine='openpyxl') 
# create writer object
writer.book = wb

df2.to_excel(writer, sheet_name = '2', index = None)
# add df2 to excel sheet 

excelcellwitdh(wb, "1", df1.columns, exclude = ['col 2'], align = "center")
excelcellwitdh(wb, "2", df2.columns, exclude = ["D"], align = "left")
# format cells

writer.save()
writer.close() 

使用时

with pd.ExcelWriter(outputfile) as writer:  
    df1.to_excel(writer, sheet_name='1')
    df2.to_excel(writer, sheet_name='2')

我得到一个未损坏的文件，但我不能再使用我的功能了。我在这里想念什么？

标签： pythonpandasopenpyxl