使用openpyxl将pandas数据框复制到excel [英] Copy pandas dataframe to excel using openpyxl

问题描述

我在模板文件中保存了一些复杂的格式，需要将熊猫数据框中的数据保存到其中.问题是当我使用pd.to_excel保存到此工作表时，pandas覆盖了格式.是否有办法以某种方式将df的粘贴值"粘贴到工作表中?我正在使用熊猫0.17

I have some complicated formating saved in a template file into which I need to save data from a pandas dataframe. Problem is when I use pd.to_excel to save to this worksheet, pandas overwrites the formatting. Is there a way to somehow 'paste values' form the df into the worksheet? I am using pandas 0.17

import openpyxl
import pandas as pd
wb= openpyxl.load_workbook('H:/template.xlsx')
sheet = wb.get_sheet_by_name('spam')
sheet.title = 'df data'
wb.save('H:/df_out.xlsx')

xlr = pd.ExcelWriter('df_out.xlsx')
df.to_excel(xlr, 'df data')
xlr.save()

推荐答案

openpyxl 2.4附带了一个实用程序，可将Pandas Dataframe转换为openpyxl可直接使用的格式.代码看起来像这样:

openpyxl 2.4 comes with a utility for converting Pandas Dataframes into something that openpyxl can work with directly. Code would look a bit like this:

from openpyxl.utils.dataframe import dataframe_to_rows
rows = dataframe_to_rows(df)

for r_idx, row in enumerate(rows, 1):
    for c_idx, value in enumerate(row, 1):
         ws.cell(row=r_idx, column=c_idx, value=value)

您可以调整枚举的开始位置，以将单元格放置在所需的位置.

You can adjust the start of the enumeration to place the cells where you need them.

有关更多信息，请参见 openpyxl文档.

See openpyxl documentation for more information.

这篇关于使用openpyxl将pandas数据框复制到excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！