XLWings Python For Excel Tutorial

What is xlWings and Why Use it?

XLWings is a python framework that allows you to combine the power of Excel, VBA and Python.

It allows you to make Python massively useful for your workflow, especially if you’re new to Python.

This is because you can use Excel to display the results of your Python programs in the manner that you’re most comfortable with, i.e. Spreadsheets and other Microsoft applications.

Because Python has a less intensive resource footprint than Excel, it can open and process bigger files than Excel can handle, and process them faster.

So for example you may want to pass a lot of the heavy lifting to Python via XLWings, then have Excel do some additional work on your Python processed subset of the data, or just display your Python result, which ever sails your boat!

Getting XlWings

xlWings is free if..

• You use the Open Source version
• You use the professional version for a Non Commercial use.

So basically if you’re a hedge fund, pay up brother, you can afford it.

Pricing for commercial purposes (if you want the pro non opensource version) is on their website.

Specific Examples

Excel spreadsheets have a limit 1,048,576 rows. Good look using applications that open files that even come near to that limit, One way of handling situations like that would be Power Query however using Python and XLWings you can write functions that query text files (such as database csv extracts) that run to multiple millions of rows without straining Excel in the slightest.

What you need to get started

First you need to install Python, check out this video for that, then come back here.

So you’ve now installed Python and then you’ve issued the following commands which will result in Pandas, Numpy, XlWings and OpenPyXL being installed.

After issuing each of the following commands in turn, you’ll see a load of code run as the install happens, that’s normal, don’t be afraid, you’re not going to brick your PC ;-).

c:\users\YouUserName>pip install pandas
c:\users\YouUserName>pip install xlwings
c:\users\YouUserName>pip install numpy
c:\users\YouUserName>pip install openpyxl

We then enter the free non commercial license into the command prompt as follows.

c:\users\YouUserName>xlwings license update -k noncommercial

xlWings will return a message to let you know everything is good to go!

For this getting started tutorial, because there’s many ways to get started, I’ll show you the easiest way from the get go.

You can experiment later but let’s get a solid foundation first.

Setup the XlWings Addin to work with Excel

c:\users\YouUserName>xlwings addin install

' Whatever file path works for you ...    
c:\users\YouUserName>D:
D:>cd D:\LocalData\Programming\Python

Now time to create your first XlWings project

D:\LocalData\Programming\Python>xlwings quickstart myTestProject

After executing the above command the following two files should appear your macro-enabled workbook and an initial Python file.

For the files below the command would have been

D:\LocalData\Programming\Python>xlwings quickstart YouTubeDemo

Now, open your Excel macro file "YouTubeDemo" or what ever file name you chose and open your Python file in a code text editor such as SublimeText (Unlimited non time bound free trial) or Notepad++ (Free), I’ve got both on my PC.

Your Python file should look something like this..

01  import xlwings as xw
02  
03  def main():
04      wb = xw.Book.caller()
05      sheet = wb.sheets[0]
06      if sheet["A1"].value == "Hello xlwings!":
07          sheet["A1"].value = "Bye xlwings!"
08      else:
09          sheet["A1"].value = "Hello xlwings!"
10  
11  @xw.func   # This line makes hello() visible to Excel and VBA
12  def hello(name):
13      return f"Hello {name}!"
14  
15  if __name__ == "__main__":
16      xw.Book("YouTubeDemo.xlsm").set_mock_caller()
17      main()

This is just boiler plate code to get you started with some examples.

Note Python works on indentation, every indent is a sub element of the area above, also note the file name is the same as module name in Python and XlWings parlance.

On opening your Excel file you’ll notice a new xlwings tab on your ribbon

If you click the "Play" button (i.e. Run Main) on the left side of the ribbon menu the code in the Python main() function will run i.e. def main():.

The other function "hello(name)" can be called from either vba or the spreadsheet.

PYTHONPATH (yellow highlight) this is the locaton of any extra python files that you created (in a different location) that you want to be accessable from this project.

UDF Modules (yellow highlight) is where you enter a list of any python modules (file name minus extension so "GetData.py" becomes "GetData").

The green debug UDF’s user defined functions (hello(name) above is a udf because it has the @xw.func "decorator")

So you can call it from the spreadsheet…

Or you can call it from VBA, your choice, personally I like functions because they give me both options, and you can write some seriously big programs into a function that returns a result to your spreadsheet or VBA code.

So in the trite example above vba passes the string "Bill" to the Python function which returns "Bill" it could just have as easily passed back "Hello Bill what’s up?", by changing Line 13 above to the code below.

13      return f"Hello {name}, what's up?"    

If you’ve tried all of the above go through the video below, this starts off with some examples of Python functions for Excel being used in the spreadsheet.

It then shows you all the code behind them, it’s well worth your time to get you up to speed.

This article is a bit of a work in progress, so leave some comments below if you’ve got any questions.

I plan to continue adding to this.