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

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

Answer 2

Or you can do like this:

your_df.to_excel( r'C:\Users\full_path\excel_name.xlsx',                   sheet_name= 'your_sheet_name'                 )