Python 使用 openpyxl 将 Pandas 数据框复制到 excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36657288/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Copy pandas dataframe to excel using openpyxl
提问by blitz009
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
我在模板文件中保存了一些复杂的格式，我需要将数据从熊猫数据框中保存到其中。问题是当我使用 pd.to_excel 保存到这个工作表时，pandas 会覆盖格式。有没有办法以某种方式将 df 的值“粘贴”到工作表中？我正在使用熊猫 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()
回答by Charlie Clark
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:
openpyxl 2.4 附带了一个实用程序，用于将 Pandas 数据帧转换为 openpyxl 可以直接使用的内容。代码看起来有点像这样：
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.
您可以调整枚举的开始以将单元格放置在您需要的位置。
See openpyxl documentationfor more information.
有关更多信息，请参阅openpyxl 文档。
回答by Abbas
Here is the solution for you using clipboard:
这是您使用的解决方案clipboard：
import openpyxl
import pandas as pd
import clipboard as clp
#Copy dataframe to clipboard
df.to_clipboard()
#paste the clipboard to a valirable
cells = clp.paste()
#split text in varialble as rows and columns
cells = [x.split() for x in cells.split('\n')]
#Open the work book
wb= openpyxl.load_workbook('H:/template.xlsx')
#Get the Sheet
sheet = wb.get_sheet_by_name('spam')
sheet.title = 'df data'
#Paste clipboard values to the sheet
for i, r in zip(range(1,len(cells)), cells):
for j, c in zip(range(1,len(r)), r):
sheet.cell(row = i, column = j).value = c
#Save the workbook
wb.save('H:/df_out.xlsx')
