How to write to an open Excel file using Python?

Question

I am using openpyxl to write to a workbook. But that workbook needs to be closed in order to edit it. Is there a way to write to an open Excel sheet? I want to have a button that runs a Python code using the commandline and fills in the cells.

The current process that I have built is using VBA to close the file and then Python writes it and opens it again. But that is inefficient. That is why I need a way to write to open files.

Answer 1

If you're a Windows user there is a very easy way to do this. If we use the Win32 Library we can leverage the built-in Excel Object VBA model.

Now, I am not sure exactly how your data looks or where you want it in the workbook but I'll just assume you want it on the sheet that appears when you open the workbook.

For example, let's imagine I have a Panda's DataFrame that I want to write to an open Excel Workbook. It would like the following:

import win32com.client
import pandas as pd

# Create an instance of the Excel Application & make it visible.
ExcelApp = win32com.client.GetActiveObject("Excel.Application")
ExcelApp.Visible = True

# Open the desired workbook
workbook = ExcelApp.Workbooks.Open(r"<FILE_PATH>")

# Take the data frame object and convert it to a recordset array
rec_array = data_frame.to_records()

# Convert the Recordset Array to a list. This is because Excel doesn't recognize 
# Numpy datatypes.
rec_array = rec_array.tolist()

# It will look something like this now.
# [(1, 'Apple', Decimal('2'), 4.0), (2, 'Orange', Decimal('3'), 5.0), (3, 'Peach', 
# Decimal('5'), 5.0), (4, 'Pear', Decimal('6'), 5.0)]

# set the value property equal to the record array.
ExcelApp.Range("F2:I5").Value = rec_array

Again, there are a lot of things we have to keep in mind as to where we want it pasted, how the data is formatted and a whole host of other issues. However, at the end of the day, it is possible to write to an open Excel file using Python if you're a Windows' user.