excel - pandas DataFrame.to_excel 保持excel文件格式

Question

当我使用df.to_excel将数据保存在文件上时，pandas中有一个选项可以保留文件的格式吗？
我发现的唯一解决方法是:

from openpyxl import load_workbook
import pandas as pd

# df_data is a pd.DataFrame

wb = load_workbook(fout_file)
sheet = wb.active
for r, row in enumerate(dataframe_to_rows(df_data, index=False, header=False), 2):
    for c in range(0, df_columns):
        sheet.cell(row=r, column=c + 1).value = row[c]
wb.save(fout_file)

有更好的方法让我不必逐个单元格复制吗？

谢谢

斯特凡诺·G.

@DSteman 感谢您的想法，我只是按照您的建议尝试使用 StyleForm。

def main ():
    ...
    ...
    ...
    # df_new_data = pd.DataFrame(columns=self.df_values.columns)
    df_new_data = StyleFrame.read_excel(self.template_fout, read_style=True)
    ...
    ...
    ...
        cr_dfnewdata = 0
        for j, row_data in data.iterrows():
            original_row = row_data.copy(deep=True)
            # df_new_data = df_new_data.append(original_row)
            cr_dfnewdata += 1
            df_new_data[cr_dfnewdata] = original_row
            ...
            ...
            ...
            compensa_row = row_data.copy(deep=True)
            compensa_row[self.importo_col] = importo * -1
            # compensa_row[self.qta_col] = qta * -1
            compensa_row[self.cod_ribal_col] = f"{cod_ribal}-{j}"
            # df_new_data = df_new_data.append(compensa_row)
            cr_dfnewdata += 1
            df_new_data[cr_dfnewdata] = compensa_row
            ...
            ...
            ...


def save_working_data(self, cod_ribalt: str, df_data):
    fout_working_name = f"{self.working_dir}/working_{cod_ribalt}.xlsx"
    df_data.to_excel(fout_working_name).save()

但是我收到了这个错误:

export_df.index = [row_index.value for row_index in export_df.index]AttributeError: 'int' object has no attribute 'value'

Answer 1

您可以使用 df.to_clipboard(index=False) 来执行此操作

from win32com.client import Dispatch
import pandas as pd

xlApp = Dispatch("Excel.Application")
xlApp.Visible = 1
xlApp.Workbooks.Open(r'c:\Chadee\test.xlsx')
xlApp.ActiveSheet.Cells(1,1).Select

d = {'col1': [1, 2], 'col2': [3, 4]}
df = pd.DataFrame(data=d)
df.to_clipboard(index=False)

xlApp.ActiveWorkbook.ActiveSheet.PasteSpecial()

输出:

请注意，单元格颜色仍然相同

希望有帮助! :-)