Automate Excel with Python xlwings

Sharing is caring!

Last Updated on July 14, 2022 by Jay

In this tutorial, I will show you how to automate Excel with Python xlwings library. Excel is an awesome software with a simple and intuitive user interface, and Python is a powerful programming language that is very efficient at data analysis. xlwings is the glue that allows us to have the best of both worlds.

The author of xlwings says the library “Make Excel Fly!”, and I couldn’t agree more. You can use xlwings + Python for the following tasks:

1. Automate Excel with Python, such as producing reports
2. Write macros in Python, and run from Excel by clicking on a button
3. Write user-defined functions in Python, and call those function from Excel just like any Excel function

Sounds exciting? Let’s get started!

Part 1. Installing xlwings

There are two parts to install xlwings: Python library and Excel add-in. Let’s start with getting the Python library first:

pip install xlwings

Then download the Excel add-in from xlwings’ official Github repository. It’s the xlwings.xlam file on the page, and feel free to grab the latest version. The link here is to v0.18.0.

Put the xlwings.xlam file into your Excel add-in folder, which is:

C:\Users\xxxx\AppData\Roaming\Microsoft\AddIns

The xxxx is your own user name on your computer.

Then, open Excel, File -> Options -> Add-ins. Click on the “Go” button besides Manage: Excel Add-ins.

When the Add-ins window pops up, click on “Browse”.

This will take you directly to the Addins folder, simply select the xlwings.xlam file from the folder, and hit “OK”. xlwings addin is now added to your Excel, this add-in will be automatically enabled whenever you launch the Excel app.

Now, the xlwings tab should appear on your Excel ribbon.

The set up is done at this point, and now let’s have fun with automating Excel with Python!

Part 2. Automating Excel

Run the following Python script, which will open a new Excel instance.

import xlwings as xw
wb = xw.Book()

Write data to Excel

Here, wb refers to the new (and open) Excel file, and it’s also a Python object at the same time, which means we can manipulate it (the Excel file) in Python! Try the following code, which will allow you to input values from Python into Excel.

sheet = wb.sheets['Sheet1']
sheet.range('A1').value = "Hello Excel from Python"

We can also use the .range((x,y)) notation to refer to individual cells in Excel. x refers to row, and y refers to column. So .range((3, 2)) means cell B3.

sheet.range((3,2)).value = 'x-axis'
sheet.range((3,3)).value = 'y-axis'
for i in range(5):
    sheet.range((i+4, 2)).value = i

It’s also possible to write formulas in Excel using Python. Basically we are writing a string into the cell. Here, we want to calculate the exponential values of the x-axis in another column. In the below code, we are using an “f-string”, which is an improved string formatting syntax starting from Python 3.6.

for i in range(5):
    sheet.range((i+4,3)).value = f'=exp(B{i+4})'

Reading data from Excel

Reading data from Excel is as easy, the following code reads Excel data into Python as a list.

data = sheet.range('B3:C8').value

Check out the following short code if you want to read Excel data into Python as a pandas Dataframe. .expand() automatically detects the dimension of the data, and .options() specifies that we want a pandas Dataframe. We reset the index at the end so the x-axis will be treated as a column instead of a dataframe index.

import pandas as pd
df = xw.Range('B3').expand().options(pd.DataFrame).value
df.reset_index(inplace=True)

Now we have a table, what are we missing?… Yes, a graph! Since the data is already read into Python, we can generate a graph then put it into Excel file. For plotting the graph, we’ll use the matplotlib library.

import matplotlib.pyplot as plt
fig = plt.figure()
plt.plot(df['x-axis'],df['y-axis'])
plt.xlabel('x-axis')
plt.ylabel('y-axis')
sheet.pictures.add(fig, name='MyPlot', update=True)   #add the graph back into Excel

Finally, as we do for every Excel spreadsheet, we gotta save our work and close the file! wb refers to the workbook here.

wb.save('automate_excel_with_python.xlsx')
wb.close()

Part 3. Write a macro in Python and run in Excel

Let me just clarify, the “macro” isn’t referring to the VBA Excel macros. These are Python programs, which can be executed from Excel. However, it requires a tiny bit of VBA to allow Excel to call a Python function.

Python Script

Let’s first write a simple Python function to generate 10 random numbers, and then place them in an Excel sheet cell A1. Note the xw.Book.caller() refers to the current workbook. Save the following script as “rand_10.py”.

import numpy as np
import xlwings as xw

def generate():
    wb = xw.Book.caller()
    wb.sheets[0].range('A1').value = np.random.rand(10)

Excel VBA & linking to the Python script

Once we have the Python script, open up the VBA Editor, you can do this by pressing Alt + F11 inside the Excel app.

In the VBA editor, go to Tools -> Reference, and check the “xlwings” checkbox. OK and save.

Next, in the VBAProject window, right click on your Excel file, and insert a module. In the new module window appears on the right-hand side, you can type the following VBA code.

Sub Rand_10()
    RunPython ("import rand_10; rand_10.generate()")
End Sub

Some notes here: rand_10 is the Python script file name. i.e. we just saved the script as “rand_10.py”. The VBA code RunPython("import rand_10; rand_10.generate()") basically says: import the Python script called “rand_10”, then run the function generate() using Python.

Next, save the VBA code, and now we want to create a button in Excel sheet. Going back into Excel, under the “Developer” tab. Insert -> Button. If you don’t have the Developer tab. Right click on your Excel ribbon, and “Customize the Ribbon”. Check the Developer checkbox, and then OK to save the change.

Once you have a button, right click on it, then Assign Macro to assign the button to the VBA macro named Rand_10(). Click on the button, and 10 random numbers will be populated from cells A1 to J1.

Part 3. Write a user-defined function in Python and call it within Excel

Advanced Excel users know that we can create user-defined functions in VBA. This feature is great, as not all built-in Excel functions fit our needs. However, VBA is 1) hard to use/learn, and 2) has very limited functionality. With xlwings, we can create our own user-defined functions in Python. All we need is a Python script, and a little bit setup in Excel to achieve this.

Python script

Let’s write a simple Python function that calculates the square of a number.

import xlwings as xw

@xw.func
def square(x):
    return x ** 2

Two things to note here:

• @xw.func is a decorator. It must be added right before the def to let xlwings know this is a user-defined function.
• The function must return something so the returned value can be passed into Excel.

Excel setup

The default settings expect that the Python code and the Excel file to be:

• in the same directory
• with the same name, but Python file ends with .py and the Excel file ends with .xlsm (or .xlsb)

To illustrate, I’m going to name my files as “square.py” and “square.xlsb”. Open up square.xlsb in Excel, go to the xlwings tab, then click on Import Functions.

In some cases, you might get an “Automation error 440” pop up.

We can fix this by taking the below steps:

1. In Excel, click on File -> Options -> Trust Center -> Trust Center Settings… -> Macro Settings. Check “Trust access to the VBA project object model” box, and enable macros. OK to save changes.
2. Go to VBA editor (press Alt + F11). Tools -> References, then check “xlwings” box. OK to save change.

Once you complete the above 2 steps, go back to Excel the xlwings tab, click on the Import Functions again. After a brief pause, if no error message pops up, it means the Python function was imported successfully, and we can start using the Python function sqaure() inside Excel sheets. Let’s now test it!

You can type =square(A1) inside any cell, and notice that as you type out the function, square actually shows up in the function list! That’s a neat feature. Yep, you saw that – we can use this Python function just like an Excel function, and we can pass cell references into the function. Make no mistake here – it appears we are using an Excel function, but under the hood, Python is doing all the calculation, then only the result is displayed to the user via Excel. This means that, we can create really complex functions thanks to Python’s power. For example, a dynamic function that extract financial data from a website and display data in Excel.

Debugging

Two common errors you might experience as a beginner are:

1. Automation error 404. We talked about how to fix this error, make sure the Excel Macro setting is correct.
2. When you type the user defined function, “Object Require” shows up in the cell. Make sure xlwings is checked in VBA Editor -> Tools -> References, and the change is saved for the appropriate Excel file. Sometimes, when we have multiple Excel sheets open, we might end up applying this change to another file unintentionally.