Pandas Dataframe to Excel Sheet

by

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

More Related Contents:

  1. Get the current URL with JavaScript?
  2. What is the difference between an interface and abstract class?
  3. Generic htaccess redirect www to non-www
  4. How can i list has same id data with while loop in PHP? [closed]
  5. Why should I use core.autocrlf=true in Git?
  6. entity object cannot be referenced by multiple instances of IEntityChangeTracker. while adding related objects to entity in Entity Framework 4.1
  7. Why is a shell script giving syntax errors when the same code works elsewhere? [duplicate]
  8. Why is a shell script giving syntax errors when the same code works elsewhere? [duplicate]
  9. Parsing huge logfiles in Node.js – read in line-by-line
  10. Avoid resending forms on php pages
  11. Match two strings in one line with grep
  12. iPhone: Hide UITableView search bar by default
  13. How to POST a django form with AJAX & jQuery
  14. Warnings Your Apk Is Using Permissions That Require A Privacy Policy: (android.permission.READ_PHONE_STATE)
  15. Haversine formula with php
  16. Android. Scrolling 2 listviews together
  17. printable char in java
  18. java.lang.IllegalStateException: Fragment not attached to Activity
  19. Return a list of imported Python modules used in a script?
  20. Tensorflow: ImportError: libcusolver.so.8.0: cannot open shared object file: No such file or directory
  21. How do I generate thread-safe uniform random numbers?
  22. Html.DropDownListFor() with custom parameter
  23. When is EOF needed in ANTLR 4?
  24. jQuery AJAX and JSON format
  25. javascript window.location in new tab
  26. Font Awesome 5 with Angular
  27. When should I recycle a bitmap using LRUCache?
  28. Pandas Timedelta in months
  29. CORS request with IE11
  30. How do I create then use long Windows paths from Perl?

Leave a Comment