Python 数据框到 Excel 工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29459461/
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
Dataframe to Excel sheet
提问by gaurav gurnani
I have an Excel file (.xls format) with 5 sheets, I want to replace the contents of sheet 5 with contents of my pandas data frame.
我有一个包含 5 个工作表的 Excel 文件（.xls 格式），我想用我的 Pandas 数据框的内容替换工作表 5 的内容。
采纳答案by Parfait
From your above needs, you will need to use both Python (to export pandas data frame) and VBA (to delete existing worksheet content and copy/paste external data).
根据您的上述需求，您需要同时使用 Python（导出 Pandas 数据框）和 VBA（删除现有工作表内容和复制/粘贴外部数据）。
With Python: use the to_csvor to_excelmethods. I recommend the to_csv method which performs better with larger datasets.
使用 Python：使用to_csv或to_excel方法。我推荐 to_csv 方法，它在更大的数据集上表现更好。
# DF TO EXCEL
from pandas import ExcelWriter
writer = ExcelWriter('PythonExport.xlsx')
yourdf.to_excel(writer,'Sheet5')
writer.save()
# DF TO CSV
yourdf.to_csv('PythonExport.csv', sep=',')
With VBA: copyand pastesource to destination ranges.
Fortunately, in VBA you can call Python scripts using Shell (assuming your OS is Windows).
幸运的是，在 VBA 中，您可以使用 Shell 调用 Python 脚本（假设您的操作系统是 Windows）。
Sub DataFrameImport()
'RUN PYTHON TO EXPORT DATA FRAME
Shell "C:\pathTo\python.exe fullpathOfPythonScript.py", vbNormalFocus
'CLEAR EXISTING CONTENT
ThisWorkbook.Worksheets(5).Cells.Clear
'COPY AND PASTE TO WORKBOOK
Workbooks("PythonExport").Worksheets(1).Cells.Copy
ThisWorkbook.Worksheets(5).Range("A1").Select
ThisWorkbook.Worksheets(5).Paste
End Sub
Alternatively, you can do vice versa: run a macro (ClearExistingContent) with Python. Be sure your Excel file is a macro-enabled (.xlsm) one with a saved macro to delete Sheet 5 content only. Note: macros cannot be saved with csv files.
或者，您可以反之亦然：使用 Python 运行宏 (ClearExistingContent)。确保您的 Excel 文件是启用宏 (.xlsm) 的文件，其中保存的宏仅用于删除工作表 5 的内容。注意：宏不能与 csv 文件一起保存。
import os
import win32com.client
from pandas import ExcelWriter
if os.path.exists("C:\Full Location\To\excelsheet.xlsm"):
xlApp=win32com.client.Dispatch("Excel.Application")
wb = xlApp.Workbooks.Open(Filename="C:\Full Location\To\excelsheet.xlsm")
# MACRO TO CLEAR SHEET 5 CONTENT
xlApp.Run("ClearExistingContent")
wb.Save()
xlApp.Quit()
del xl
# WRITE IN DATA FRAME TO SHEET 5
writer = ExcelWriter('C:\Full Location\To\excelsheet.xlsm')
yourdf.to_excel(writer,'Sheet5')
writer.save()
回答by Christian
Or you can do like this:
或者你可以这样做：
your_df.to_excel( r'C:\Users\full_path\excel_name.xlsx',
sheet_name= 'your_sheet_name'
)
