import xlwings as xw
import numpy as np
import matplotlib.pyplot as plt
%matplotlib inline
wb = xw.Book('data.xlsx')
sht = wb.sheets['my data']
ft_per_m = sht.range('B4').value
ft_per_m
3.28084
lbm_per_kg = 2.20462
sht.range('A6').color = (255,253,66)
sht.range('B6').color = (148,206,88)
sht.range('A6').value = "lbm_per_kg"
sht.range('B6').value = lbm_per_kg
t = sht.range('E2:E42').value # --> list
t = np.array( sht.range('E2:E42').value ) # --> numpy array
v = sht.range('F2:F42').options(np.array).value # --> numpy array alternative
# OR THIS
tv = np.array(sht.range('E2').expand().value) # expand() --> smart sizing --> matrix
t = tv[:,0]
v = tv[:,1]
# OR THIS
t = np.array(sht.range('E2').expand('vertical').value) # expand() --> smart sizing --> matrix
v = np.array(sht.range('F2').expand('vertical').value) # expand() --> smart sizing --> matrix
plt.plot(t,v);
data = np.array([1,2,3])
sht.range('a10').value = data # --> horizontal
sht.range('a12').options(transpose=True).value = data # --> vertical
sht.range('a16').value = data[:,np.newaxis] # --> vertial alternative
T = np.linspace(300,1000,len(t))
sht.range('J1').value = "t (s)"
sht.range('K1').value = "T (K)"
sht.range('J2').value = t[:,np.newaxis]
sht.range('K2').value = T[:,np.newaxis]
sht.range('H1').value = "sum(t)"
sht.range('H2').formula = "=sum(E:E)"
fig = plt.figure()
plt.rc("font", size=14)
plt.plot(t,T**3)
plt.xlabel('t (s)')
plt.ylabel('T (K)')
sht.pictures.add(fig, name="T_vs_t", update=True, left=720, top=150)
plt.close() # keeps the plot from showing right here
wb.sheets.add("New Sheet", after="my data")
for s in wb.sheets:
print(s.name)
wb.sheets[1].name
my data New Sheet
'New Sheet'
wb.save()
wb.close()
You can call Python functions from Excel
Here are initial, one-time setup instructions that are tested on a Mac:
xlwings runpython install
xlwings addin install
Then follow the instructions.env | grep PYTHONPATH
in a terminal (without the PYTHONPATH= part).Then, to create a workbook already setup for interfacing with Python, type in a terminal: xlwings quickstart some_project_name
. This will create a folder some_project_name with two files: some_project_name.xlsm, and some_project_name.py. You can edit both of these.
On Windows, (but not Mac, unfortunately), you can also setup user defined functions (UDFs) that allow you to write Python functions that can be called directly from within Excel code cells.