1. Products
  2.   Spreadsheet
  3.   Python
  4.   Xlwings
 
  

Open Source Python API for Google Sheets

Python library that makes it easy to call Python from Excel and vice versa.

Xlwings is an open source python API for manipulating Excel file format. Using the API you can automate excel from python to produce reports, write UDFs(user-defined functions), write macros, and control Excel remotely. Furthermore, the API allows manipulating data structures like cells, lists, ranges, NumPy arrays, panda data frames, and panda series.

Xlwings requires the installtion of Microsoft Excel, so everything works on Windows and macOS except that the UDFs only works on Windows.

Previous Next

Getting Started with Xlwings

You need to have Python 3.5 or higher installed on your system to run xlwings smoothly. The recommended way to install via PIP. Please use the following command.

Install Xlwings via PIP Command

pip install xlwings

Install Xlwings via Conda

conda install xlwings

Interact with Excel from Python

Xlwings API allows manipulating Microsoft Spreadsheets using Python. Using the API you can connect to an exisitng workbook or create a new one using xlwings.Book() method. You can read/write values to & from ranges, expand ranges, convert datatypes easily. Furthermore, you can add Matplotlib & Plotly chart as pictures in your Excel Workbooks.

Get Dimensions of Excel Ranges Dynamically via Python

sheet = xw.Book().sheets[0]
sheet['A1'].value = [[1,2], [3,4]]
range1 = sheet['A1'].expand('table')  # or just .expand()
range2 = sheet['A1'].options(expand='table')
range1.value
[[1.0, 2.0], [3.0, 4.0]]
range2.value
[[1.0, 2.0], [3.0, 4.0]]
sheet['A3'].value = [5, 6]
range1.value
[[1.0, 2.0], [3.0, 4.0]]
range2.value

Call Python from Excel

The Open Source spreadsheet library Xlwings allows software developers to communicated with Python via Excel. You can call python funcitons inside your excel either by using Run button of Xlwings Excel Add-In or by using the RunPython VBA funciton. The good thing about using Excel Add-In is that you dont need your workbooks to be macro-enabled, and you can save it as xlsx.

Use Python Script to insert QR codes in Microsoft Excel

import tempfile
import segno
import xlwings as xw

# Update this with the name of your workbook
book = xw.Book('qr.xlsx')
sheet = xw.sheets[0]

# Update this with the starting cell of your URLs
start_cell = sheet['A1']
urls = start_cell.options(expand='down', ndim=1).value

# Loop through each URL and generate the QR code
for ix, url in enumerate(urls):
    # Generate the QR code
    qr = segno.make(url)
    with tempfile.TemporaryDirectory() as td:
        # Save the QR code as a temporary svg file. If you are on macOS, use pdf
        # instead and if you don't have Microsoft 365, you may have to use png
        filepath = f'{td}/qr.svg'
        qr.save(filepath, scale=5, border=0, finder_dark='#15a43a')
        # Insert the QR code to the right of the URL
        destination_cell = start_cell.offset(row_offset=ix, column_offset=1)
        sheet.pictures.add(filepath,
                           left=destination_cell.left,
                           top=destination_cell.top)

User Defined Functions (UDFs) in Excel using Python

Xlwings library gives developers the capability to write User Defined Funcitons (UDFs) inside Microsoft Excel fileformat programatically. Currently, UDFs are only available on windows. The API allows using simple funcitons, number of array dimensions, dynamic array formulas, arrya formulas with NumPy and more.

How to Call Macros via RunPython in Microsoft Excel

import xlwings as xw

@xw.sub
def my_macro():
    """Writes the name of the Workbook into Range("A1") of Sheet 1"""
    wb = xw.Book.caller()
    wb.sheets[0].range('A1').value = wb.name
 English