How to Supercharge Excel With Python

How to integrate Python and Excel with xlwings


Why integrate Python with Excel VBA?


Getting Set Up to Use xlwings

pip install xlwings
xlwings addin install
xlwings 0.17.0
[Errno 2] No such file or directory: 'C:\\Users\\costa\\AppData\\Roaming\\Microsoft\\Excel\\XLSTART\\xlwings.xlam'
mkdir C:\\Users\\costa\\AppData\\Roaming\\Microsoft\\Excel\\XLSTART

Enabling User Defined Functions for xlwings


Photo by Pakata Goh on Unsplash

Getting Started with xlwings

xlwings quickstart ProjectName

VBA to Python

VBA editor with xlwings

Example 1: Operate Outside of Excel, and return the Output

import xlwings as xw
import pandas as pd
def main():
wb = xw.Book.caller()
df = pd.read_csv(r'C:\temp\TestData.csv')
df['total_length'] = df['sepal_length_(cm)'] + df['petal_length_(cm)']
wb.sheets[0].range('A1').value = df
xlwings in action

Example 2: Use Excel Inputs to Drive the Operations

import xlwings as xw
import random
def random_line(afile):
line = next(afile)
for num, aline in enumerate(afile, 2):
if random.randrange(num): continue
line = aline
return line
'Function from: stackoverflow
def main():
wb = xw.Book.caller()
listloc = str(wb.sheets[0].range('B3').value)
fhandle = open(listloc, encoding = 'utf-8')
wb.sheets[0].range('A5').value = wb.sheets[0].range('B2').value + ' ' + wb.sheets[0].range('B1').value + ' here is a joke for you'
wb.sheets[0].range('A6').value = random_line(fhandle)

User-Defined Functions with xlwings

import xlwings as xw@xw.func
def joke(x):
wb = xw.Book.caller()
fhandle = open(r'C:\Temp\list.csv')
for i, line in enumerate(fhandle):
if i == x:
return(line)

Conclusion

Towards Data Science

A Medium publication sharing concepts, ideas, and codes.

Costas Andreou

Written by

Figuring out how the world works through sharing knowledge, techniques and code!

Towards Data Science

A Medium publication sharing concepts, ideas, and codes.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade