Data Science

Tools for Working with Excel and Python

Posted onAuthorCharles Durfee

Author: Vincent Granville

Guest blog by Tony Roberts.

Microsoft Excel is widely used in almost every industry. Its intuitive interface and ease of use for organising data, performing calculations, and analysis of data sets has led to it being commonly used in countless different fields globally.

Whether you’re a fan of Excel or not, at some point you will have to deal with it! For many applications you won’t want to do complex calculations or manage large data sets in Excel itself, but you may need to take values from Excel as inputs, produce reports in an Excel format, or provide tools to Excel users. Python can be a better choice for complex tasks and fortunately there are many tools for the Python developer to work with so Excel and Python can be used together.

This post gives an overview of some of the most popular and useful tools out there to help you choose which is the right one for your specific application.

Below there’s a feature matrix outlining the different features of the packages for calling Python from Excel.

Content

Building Interactive Python tools with Excel as a front-end

  • PyXLL – The Python Excel Add-In
  • pywin32, win32com and comtypes
  • xlwings
  • DataNitro

Reading and writing Excel workbooks

  • OpenPyXL
  • XlsxWriter
  • XLTable
  • Pandas
  • xlrd and xlw

Building Interactive Python Tools with Excel as a Front-End

Excel is a well known and really good user interface for many tasks. When you get into more complex tasks and processing larger datasets however you can soon reach the limits of what can sensibly be achieved in Excel. Python is a popular choice for data science and other disciplines as it can handle these complex cases far better than Excel alone. By using both together and recognising the strengths of each it’s possible to build really powerful interactive tools using Excel as a user-friendly front end, with all the heavy lifting done in Python.

Python is an extremely powerful language with an extensive ecosystem of 3rd party libraries. Leveraging Python in Excel spreadsheets can be a fantastic way to enhance productivity and remove the need for importing and exporting data into and out of Excel. Interactive worksheets can be developed using Python code in the same way as you might use VBA, but with all of the advantages of Python.

There are a few tools available that can be used to bring Python to Excel, and it can be difficult to know which one is right for different situations. Below is an overview of each, which I hope will highlight the differences between them and help you decide which ones are right for what you need to achieve.

PyXLL – The Python Excel Add-In

PyXLL is currently the only package that enables developers to write fully featured Excel addins in Python. It embeds the Python interpreter into Excel so that it can be used as a complete VBA replacement. You can think of it conceptually to being similar to something like Excel-DNA for C#, except that it is dynamic and imports your Python code while Excel is running – so there’s no add-in to build and no need to restart Excel when modifying your Python code.

Using PyXLL, Python code can be used to write:

Read the full tutorial, with sample code, here. For more articles about Python, click here. 

DSC Resources

Go to Source