
Motivation:
We often conduct impressive Python analyses, but all our colleagues ask is to receive the final results in Excel. Many of our colleagues do not want to work with Jupyter Notebook, Python scripts, etc. They just want to stick to their beloved spreadsheet tool (like Microsoft Excel, to call it by name). In "Excel-Python App for Non-Pythonists," we learned how to build a complete Excel-Python App for those colleagues. In this post, we will inspect Xlwings in more depth. Xlwings allows us to develop interactive applications using Excel spreadsheets as the GUI (graphical user interface). At the same time, Python does the job in the back by clicking a button in Excel or a UDF (user-defined function). At the end of this story, you’ll have a beautiful Excel KPI Dashboard Template in your hands:

… which receives the data from a database connection and calculates Overall Equipment Efficiency (OEE) using Pandas Dataframes and Numpy arrays. We will also discuss why we stick to Excel for the visualization part instead of using Xlwings possibilities for charts and static pictures.
Solution:
As many of us have already experienced, Excel’s spreadsheet grid uses a logic similar to Numpy arrays and Pandas Dataframes. Thanks to that, one of Xlwing’s core features is to make reading and writing Excel data extremely convenient.
Import Package and Data
Before we can check this out, we must ensure we have installed Xlwings. In case you haven’t yet, please do so now, e.g., via pip install:
pip install xlwings
Now, we can import Xlwings and call Xlwings’ Book function to access our input.xlsx file. Then we use Xlwings’ view function to prove the above’s statement about its usability:
import xlwings as xw
import pandas as pd
df = pd.read_excel('input.xlsx')
xw.view(df)

If you wanted to import your data out of your SQL Server database, you could do this:
import pandas as pd
import pyodbc
sql_conn = pyodbc.connect('Driver={SQL Server};'
'Server=MsSqlServerName;'
'Database=DatabaseName;'
'Trusted_Connection=NO;UID=USER;PWD=PASSWORD)
query = "SELECT * from dbo.input"
df = pd.read_sql(query, sql_conn)
In contrast to Pandas, Xlwings opens this Excel file (.view) so we can look at the data frame directly in an Excel spreadsheet. All common Python objects (numbers, lists, dictionaries, strings, tuples, arrays, and data frames) are supported by this function. View opens a new workbook and pastes the object into cell A1 of the first sheet by default. Thanks to Excel’s autofit, even the column widths are adapted automatically. The View function can be convenient when inspecting a big data frame (more convenient than going through a big data frame directly in a Juypter Notebook cell, especially for cross-checking lots of calculations).
Some words about the objects Books and Sheets
The workbook, sheet, range, or a specific cell at the highest granularity are the most important components we are dealing with in Excel. Therefore, let us now do some basic tasks related to these concepts.
We access our input.xlsx file calling Xlwings’ Book function:
wb=xw.Book(r"C:usersinput.xlsx")
wb.name

wb.sheets

Xlwings automatically detects the Input tab as being the only filled in sheet. We could have also called this sheet from this book directly:
sht = wb.sheets['Input']
We could have also used the index instead of the sheet’s name. Python alike, Xlwings starts indices from zero:
sht= wb.sheets[0]
sht.name

Going the next step, we can use range explicitly as an attribute of the sheet object:
sht.range("A1")

Above’s range only activated cell A1. However, we want to see the complete table starting from cell A1, no matter how many rows and columns this table consists of. Therefore, we must provide the DataFrame class as the convert parameter in the options method. Furthermore, we use options like header 1 for yes and table expand. By default, the data frame is expected to have both a header and index:
sht.range("A1").options(pd.DataFrame, header=1, index=False, expand='table').value

The value attribute of a range object accepts and returns a scalar for a single cell and a nested list for two-dimensional ranges. Above’s expand table option is much more convenient and flexible than hard defining range objects in tuples style:
sht.range("A1:AI253").value

We can also range objects by indexing and slicing the sheet object. Doing this with A1 notation feels more intuitive, and taking integer indices makes Excel feel a little bit like Numpy arrays:
sht["A1:AI253"]

sht["A1:AI256"] [:, 10]

Calculate OEE
Coming back from our little excursion about Excel objects, let’s now return to our Input data on which we want to calculate an OEE:
dfInput = sht.range('A1').options(pd.DataFrame, header=1, index=False, expand='table').value
For training purpose, we add this easy OEE calculation to our data frame:
dfInput['OEECalc']=(dfInput['Availability']+dfInput['Performance'] + dfInput['Quality']) /3
In the next step, we are opening our Kpi Dashboard Template into which we want to transfer the data from the other workbook:
wbNew=xw.Book(r"C:usersKPIDashboard_Xlwings.xlsx")
To be more precise, we want to copy the table’s content into the tab InputNew, starting from cell B4 (why B4 you might ask? Just for learning purpose):
shtout = wbNew.sheets['InputNew']
shtout.range('B4').options(pd.DataFrame, index=False).value = dfInput
Some thoughts about Plotting with Xlwings
Thanks to Xlwings, we have copied data from one Excel file into another, including one OEE calculation. Xlwings also supports plotting with Matplotlib, which we can now use to plot our KPIs. However, we decided to stick to Excel’s dashboarding functionalities to display our KPI input data. The reason for this is that at the current state, Excel heavily outperforms Xlwings charting and image functionalities. Thanks to Excel functions, we also made this OPS KPI Excel Template very dynamic and easy to adapt, especially for our colleagues who cannot work with Python:






Xlwings Excel Add-In
We can start the reading (from Excel), calculating, and writing (back to Excel) processes in a Jupyter Notebook. So far, nothing spectacular. But thanks to Xlwings, we can now add a VBA macro into Excel to start the Jupyter Notebook out of Excel. The basic concept is simply to write your Jupyter Notebook code into a main function, export that as a Python file (.py), and call that Python script out of Excel’s VBA Editor. Okay, now we’re talking.
Firstly, let’s make sure that we have installed the Add-In. If not, you must install xlwings addin in your terminal:
xlwings addin install

Now when you open Excel you will see the new Add-in:

Then you have to activate the Xlwing Add-in in Excel via going to your Add-in settings:

Afterward, click the search button and go to your Add-in folder, e.g.: C:UsersAppDataRoamingMicrosoftAddIns. Select the Xlwings.xlam, and you’re all set:

From now on you can call any Python script directly out of Excel. You just have to add this sub routine into your VBA module:
Sub SampleCall()
mymodule = Left(ThisWorkbook.Name, (InStrRev(ThisWorkbook.Name, ".", -1, vbTextCompare) - 1))
runpython ("import " & mymodule & ";" & mymodule & ".main()")
End Sub

Please modify your Jupyter Notebook to be in one def main:
import Pandas as pd
import xlwings as xw
def main():
wb=xw.Book(r"C:usersinput.xlsx")
wbNew=xw.Book(r"C:usersKPIDashboard_Xlwings.xlsm")
# more specifically, we want to work with the sheet Input
sht = wb.sheets['Input']
# and to be even more precise, we are only using the cells which belong to the table's range starting with cell A1
dfInput = sht.range('A1').options(pd.DataFrame, header=1, index=False, expand='table').value
# for training purpose, we add an easy OEE calculation to our dataframe
dfInput['OEECalc']=(dfInput['Availability']+dfInput['Performance'] + dfInput['Quality']) /3
shtout = wbNew.sheets['InputNew']
shtout.range('B4').options(pd.DataFrame, index=False).value = dfInput
@xw.func
def hello(name):
return f"Hello {name}!"
if __name__ == "__main__":
xw.Book("input.xlsx").set_mock_caller()
main()
..and download this Jupyter Notebook as a Python (just click on "file" in the Jupyter Notebook menu and choose "download as py") file. Please give it the same name as your Excel file and make sure the Excel file is located in the same folder as your Jupyter Notebook to work well:

Congratulations!
We have a lovely Excel-Python OPS KPI template, which ingests data from a database, calculates OEE using Pandas, and uses dynamic Excel charting functionality. All out of Excel directly. We will look at the definitions of Manufacturing KPIs in more detail in another story that is yet to come (the link will be added). The Jupyter Notebook and Python, Excel Add-In, and KPI Dashboard are found on my Git Hub.
Many thanks for reading! I hope this article is helpful for you. Feel free to connect with me on LinkedIn, Twitter or Workrooms.
Originally published on my website DAR-Analytics.