python - Pandas Dataframe to Excel Sheet

Question

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.

Answer 1

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

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

With VBA: copy and paste source to destination ranges.

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:pathTopython.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.

import os
import win32com.client
from pandas import ExcelWriter

if os.path.exists("C:Full LocationToexcelsheet.xlsm"):
  xlApp=win32com.client.Dispatch("Excel.Application")
  wb = xlApp.Workbooks.Open(Filename="C:Full LocationToexcelsheet.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 LocationToexcelsheet.xlsm')
  yourdf.to_excel(writer,'Sheet5')
  writer.save()