数据框到Excel工作表 [英] Dataframe to Excel sheet

问题描述

我有一个5页的Excel文件(.xls格式)，我想用我的熊猫数据框的内容替换5页的内容.

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.

推荐答案

根据上述需求，您将需要同时使用Python(导出熊猫数据框)和VBA(删除现有工作表内容并复制/粘贴外部数据) ).

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:使用 to_csv 或 to_excel 方法.我建议使用to_csv方法，该方法在较大的数据集上表现更好.

With Python: use the to_csv or to_excel methods. I recommend the to_csv method which performs better with larger datasets.

# 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=',')

使用VBA:副本和

With VBA: copy and paste source to destination ranges.

幸运的是，在VBA中，您可以使用Shell调用Python脚本(假设您的操作系统是Windows).

Fortunately, in VBA you can call Python scripts using Shell (assuming your OS is 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

或者，您也可以反之亦然:使用Python运行宏(ClearExistingContent).确保您的Excel文件是启用了宏的(.xlsm)文件，并且具有已保存的宏，只能删除工作表5的内容.注意:宏不能与csv文件一起保存.

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.

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() 

这篇关于数据框到Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！