If there is one great truth about civil engineers, it is that we have never met an Excel spreadsheet we didn't like. Show another civil engineer one of your fancy Python programs and you often get a disinterested shrug... Show them a spreadsheet that breaks the laws of decency and physics and their eyes light up like a kid at Christmas.

While the Excel spreadsheet itself may be a source of (much deserved) ridicule by many programmers for all of its shortcomings, Excel is a vital tool for the technical professional. It is the lingua franca for most engineering professions, where wrapping the calculations in code is not an option.

For those of us who want to engage our technical peers with the ability to do amazing things with code, but who don't want the nightmare of actually running and managing VBA code, there is an alternative. And this alternative does not require every non-programmer who uses your spreadsheet to suddenly master "pip" and the command line or even have an installation of Python themselves. For all the engineer knows it could be elves running the calculations in the background. At the end of the day they click a button and the magic happens and everybody wins.

Some further background on Excel files... In 2010, Excel went to using the .xlsx file as their standard file format. This file format is a compressed XML open standard that any XML library can read/edit. When it comes to using Python to access Excel the libraries prior to 2010 were primarily forced to use the win32 api. Packages that are setup to read the older file format include xlrd (for reading data) and xlwt (for writing data). We don't want to focus on the older file format for our purposes, but you are welcome to check out those libraries as well.

The two main Python Excel libraries we will be focused on are openpyxl and xlwings. In my quest to use Python and Excel together I originally settled on openpyxl. It is primarily reading and writing using the underlying XML file format, and it has a very pythonic api with great documentation. You don't need an installation of Excel to use this library and it can also be installed on a linux operating system.

There is one big drawback to this approach that I discovered as I went down this path... If you are using this library to share Python inside of the spreadsheet and want others to be able to open the spreadsheet and update cells, openpyxl is going to be a problem. The open Excel file will be locked by the operating system, and won't allow you to make changes on the fly. This problem led me to abandon openpyxl (still a fantastic library for most use cases) and led me down the xlwings route. Xlwings, similar to the older xlrd and xlwt libraries, primarily uses the win32 api under the hood to get the job done on the newer file format, which does lock you into Windows or Mac operating systems.

Let's get started with installation. I found it to be very painless using 'pip'. Xlwings requires the pywin32 and comtypes libraries, but pip will install those libraries for you when you go to install xlwings if needed.

pip install xlwings

Before we go too far down the road of using the library, I also want to give an overview of how to use/distribute spreadsheets with Python. In my world of civil engineers, asking other engineers to use the command line and have an installation of Python is going to be a non-starter. You are going to lose them before you ever get started if you go that route. The nice thing about this approach is we don't have to do either of those things with xlwings.

The steps do require a tiny amount of VBA, and some of the steps I used are based on older documentatation from xlwings. The steps in the latest documentation work as well, but for my use case of having a centrally located exe that is referenced by many users/worksheets, the older approach to embedding Python worked better for me.

First, in your spreadsheet under your Excel Developer tab, open up VBA. Then under the VBA Editor "File --> Import Tab..." navigate to the folder where xlwings is installed in your Python installation. For me it was under "C:\Python37\Lib\site-packages\xlwings\xlwings.bas". Now you have the necessary VBA to make the connection in your spreadsheet to your Python code.

In order to distribute your script without requiring an installation of Python and its libraries you will need to turn your Python script into an executable. PyInstaller is my library of choice for converting scripts to an exe. If you don't have many dependencies in your script (a library like Pandas requires a large number of dependencies), I would recommend the "--onefile" option to make your exe file simple to distribute.

While you are testing/developing your script, you can run it without converting it into an exe each time, but for distribution I would recommend using a compiled executable. Open up a new Excel spreadsheet and load the xlwings .bas library as previously discussed. We are going to start by running our simple script directly from Python to interact with the Excel spreadsheet, and then later we will make it a standalone script that is easier to distribute.

I created a spreadsheet "Book1.xlsm" to test the script. There is a function called "set_mock_caller" that xlwings uses to simulate the main script/function call within your existing spreadsheet prior to embedding the call in your spreadsheet. You only need to specify the input spreadsheet. Later you can comment out the file reference and "set_mock_caller" lines once your script is complete.

import xlwings as xw

f = r'C:\dev\personal\KnickKnackCivil\draft\Book1.xlsm'
xw.Book(f).set_mock_caller()

Now we are going to define our workbook and sheet, as well as set a value for one of our cells to "Hello World!".

wb = xw.Book.caller()
ws1 = wb.sheets['Sheet1']
ws1.range('A1').value = 'Hello World!'
wb.save()

You should see the "Hello, World!" text show up in your spreadsheet now. The "set_mock_caller" function is great for initial testing of your scripts.

Now that we have a simple example that executes, lets take that one step further. We are going to connect that simple script to a button in our spreadsheet and also define the location of the exe file we create so it becomes a dynamic reference we can update later if we need to. Create the exe file of your script with pyinstaller or py2exe.

Open up VBA through the Developer tab, and insert a new module. Insert the following VBA code in the new module.

Sub RunPyExe_Click()
    Dim exe As String

    exe = ThisWorkbook.Sheets("Sheet1").Range("B9").Value

    RunFrozenPython (exe)

End Sub

In this example we are using the "B9" cell under "Sheet1" to reference the location of the exe file. The "RunFrozenPython" command is the critical command for connecting our Python executable to Excel. No matter how complex the referenced Python script is, that is all of the VBA code that is needed on your part.

In Excel, under the Developer tab, insert a button in "Sheet1". Once you have placed your button in the worksheet, assign the "RunPyExe_Click" macro to the button. Update cell "B9" in "Sheet1" to point to your exe file.

Here is my updated Python code that I then saved and converted to an exe using pyinstaller.

import xlwings as xw

wb = xw.Book.caller()
ws1 = wb.sheets['Sheet1']
ws1.range('A1').value = 'Hello, World!'
wb.save()

Now you should be able to click the button and see the "Hello, World!" text appear in cell "A1".

This is a simple example, but whatever your use case, from a simple spreadsheet to a highly sophisticated spreadsheet with a ton of tabs, tables and charts, the process is the same and the library is capable of producing anything you can produce in VBA. Writing the Python code was the easy part for me, and I was off and running deleting and creating sheets, inserting data series, reading and manipulating files and creating output charts once I had this initial process down. See the xlwings documentation for more information about the capabilities of the library. The charting capabilities are limited to a few stock types at this time, but detailed charts of all types can be created through the chart class "api" functions which reference the win32 Excel api.

As far as best practices, I would always recommend "showing your work" when using Python behind the scenes. Create detailed output that shows the steps in Python. I would also recommend keeping the simple calculations in Excel. To the extent you can, don't make it a black box for non-programmers, or as some might call it, a ...