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.
- 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.
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 : wb.sheets Out: Sheets([<Sheet [test.xlsx]Sheet1>])
We can select the desired sheet using index. Lets select the first sheet.
ws = wb.sheets
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: [['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...
I hope now you know how to use Python to manipulate data inside Microsoft Excel.