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

User · Answer

Or you can do like this   your df to excel  r C  Users full path excel name xlsx                     sheet name   your sheet name

User · Answer

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

User · Answer

I tested the previous answers found here  Assuming that we want the other four sheets to remain  the previous answers here did not work  because the other four sheets were deleted  In case we want them to remain use xlwings  import xlwings as xw import pandas as pd  filename    quot test xlsx quot   df   pd DataFrame         quot a quot   1  8  3         quot b quot   1  2  5         quot c quot   3  4  6          columns   one    two    three    quot four quot     app   xw App visible False  wb   xw Book filename  ws   wb sheets  quot Sheet5 quot    ws clear   ws  quot A1 quot   options pd DataFrame  header 1  index False  expand  table   value   df    If formatting of column names and index is needed as xlsxwriter does it     the following lines will do it  if the dataframe is not multiindex   ws  quot A1 quot   expand  quot right quot   api Font Bold   True ws  quot A1 quot   expand  quot down quot   api Font Bold   True ws  quot A1 quot   expand  quot right quot   api Borders Weight   2 ws  quot A1 quot   expand  quot down quot   api Borders Weight   2  wb save filename  app quit

[python] Dataframe to Excel sheet

Examples related to python

Examples related to pandas