Data Manipulation Using Python In Microsoft Excel On Windows

In this post, i will talk about Excel plugin Xlwings. Xlwings enables writing Python scripts. Therefore Xlwings make it possible to do data manipulation using Python's data science packages.

Required:

• Anaconda Installed
• Python 3.5+ Installed

How to install xlwings

Bring up your Anaconda Powershell Prompt (anaconda3) in Windows. Then activate your Python virtual env. On my machine, i have created a virtual env python37.

Activate it using following command...

conda activate python37

Once you are in your python virtual env. Installing xlwings is very easy. Just run following command...

xlwings addin install 

You should see something like this message after the successful installation of xlwings...

The add-in is not installed.
"xlwings addin install" will install it at: C:\Users\john\AppData\Roaming\Microsoft\Excel\XLSTART\xlwings.xlam

Lets now invoke ipyhton, You can do it in any Python editor you want.

ipython

Lets now create a new excel file test.xlsx

import xlwings as xw
wb = xw.Book("C:/Users/john/Downloads/test.xlsx")

You will see excel spreadsheet opened with name test.xlsx.

Lets add following data to our test.xlsx sheet.

wb is a handler to our spreadsheet test.xlsx. We can list the number of sheets.

In [39]: wb.sheets
Out[39]: Sheets([<Sheet [test.xlsx]Sheet1>])

We can select the desired sheet using index. Lets select the first sheet.

 ws = wb.sheets[0]

How to use regular expressions in Microsoft Excel

Lets now do a simple data manipulation. We will remove the digit and column from in front of letters in excel cells and put the result in column B using Python regex.

import re
newvalues = []
for val in values:
	newvalues.append([re.sub(r'[0-9]+:','',val)])

Lets print the values in our newvalues.

print(newvalues)
Out[59]: [['A'], ['B'], ['B']]

Now lets paste these values in our spreadsheet column B using following Python command...

ws.range('C1:C3').value = newvalues

You should see following output...

WrapUp!

I hope now you know how to use Python to manipulate data inside Microsoft Excel.