XLWINGS - The Best Excel Python Plugin

This isn’t Excel…

If you are interested in Excel, you most likely are pursuing a career or hobby in regards to data. By now, you probably have realized you can do anything in Excel, and there are millions of resources out there to help you through it.

You now need to progress, which means leaving Excel behind and moving into Databases and code. I recommend starting with Python. While it is code, it is easier than most.

How do I learn this?

Again, millions of resources online to get you started. Python is very popular, and has a package feature which allows smart people to do the hard work, and you just reap the benefits. Packages are like formulas in Excel - you don’t need to understand the background code to use them.

This allows you do things like image recognition and machine learning. Relatively easily.

But before we get ahead of ourselves, let’s start small. I got bored of the online tutorials, as I learn by doing (particularly if it can be related to work or something I find useful). The best way I found to learn it, was get Python to work with Excel. This way, you can start to see the benefit of Python without being scared away from things like dataframes and CSV files.

Presenting XLWINGs

This allows you to talk to Excel very easily.

(I’m assuming you have already figured out the basics of things like Pip Install and have your packages all sorted out. If not, I understand this is a tricky bit and something I haven’t even fully figured out. The best solution is to download the fantastic free editor, Pycharm. This does all the hard work for you - no more annoying pip download. Find a tutorial on how to get started. )

Install XLWINGS using PIP or Pycharm.

import xlwings as xw
wb = xw.Book('forecast.xlsx')
sht = wb.sheets['data']
sht.range('A1').value = 'Excell-en.com'
wb.save()

Assuming you have a workbook called “forecast” in your root directory (or the directory with your script if using Pycharm) this will write in Cell A1.

Why not create a loop as well, to list data from a python list into Excel?

import xlwings as xw
wb = xw.Book('forecast.xlsx') #Workbook location
sht = wb.sheets['data'] #Sheet Name

list = [1,2,3,4,5,6,7,8,9,10] #Table
column = 'A' #Column add data to
row = 1 #Starting row

for i in list: #for each number in the list
    cell = column + str(row) #this just joins the A and 1 together. 1 is wrapped in STR, as you can't join an interger and string together
    sht.range(cell).value = i #in the data sheet, range A1, it will put in the first number in the list
    row = row + 1 #this increases the row number by 1
    #code now loops back to start of for loop, and keeps going until it reaches the end of the list

wb.save() #saves without prompting

What now

What I showed you above is very basic. However just like Excel, you can google and look at the XLWINGs website to figure out how do pretty much anything.

For example you can launch macros with Python, add in formulas, formatting charts etc. Why not store data in Excel from Python, sync excel to google sheets and create a live dashboard of data?

I will write more blogs in the future about useful Python tools for Excel.