Working with Excel files in Python using Xlwings

Last Updated : 21 Mar, 2024

Xlwings is a Python library that makes it easy to call Python from Excel and vice versa. It creates reading and writing to and from Excel using Python easily. It can also be modified to act as a Python Server for Excel to synchronously exchange data between Python and Excel. Xlwings makes automating Excel with Python easy and can be used for- generating an automatic report, creating Excel embedded functions, manipulating Excel or CSV databases etc.

Installation:
The virtual environment is used to separate a project environment (libraries, environment variables etc) etc from other project and from the global environment of the same machine. This step is optional as it isn’t always necessary to create a virtual environment for a project. We will be using a python package virtualenv for this purpose.

virtualenv env
.\env\scripts\activate

And the virtual environment is ready.

pip install xlwings

To start using Xlwings, there are certain basic steps which are to be done almost every time. This includes opening an Excel file, viewing the sheet available and then selecting a sheet.
Sheet 1 of data.xlsx file.

https://drive.google.com/file/d/1ZoT_y-SccAslpD6HWTgCn9N-iiKqw_pA/view?usp=sharing

Below are some examples which depict how to perform various operations using Xlwings library:

Example 1:

Python3

# Python program to 
# access Excel files 
  
# Import required library 
import xlwings as xw 
  
# Opening an excel file 
wb = xw.Book('data.xlsx') 
  
# Viewing available 
# sheets in it 
wks = xw.sheets 
print("Available sheets :\n", wks) 
  
# Selecting a sheet 
ws = wks[0] 
  
# Selecting a value 
# from the selected sheet 
val = ws.range("A1").value 
print("A value in sheet1 :", val) 

Output:

Values can be selected from an Excel sheet by specifying a cell, row, column, or area. Selecting the entire row or column is not recommended as an entire row or column in Excel is quite long, so it would result in a long list of trailing None. Selecting a range of 2D data will result in a list of lists of data.

Example 2:

Python3

# Python3 code to select 
# data from excel 
import xlwings as xw 
  
# Specifying a sheet 
ws = xw.Book("data.xlsx").sheets['Sheet1'] 
  
# Selecting data from 
# a single cell 
v1 = ws.range("A2").value 
v2 = ws.range("A3").value 
print("Result :", v1, v2) 
  
# Selecting entire 
# rows and columns 
r = ws.range("4:4").value 
print("Row :", r) 
  
c = ws.range("C:C").value 
print("Column :", c) 
  
# Selecting a 2D 
# range of data 
table = ws.range("A1:C4").value 
print("Table :", table) 
  
# Automatic table 
# detection from 
# a cell 
automatic = ws.range("A1").expand().value 
print("Automatic Table :", automatic) 

Output:

Xlwings can be used to insert data in an Excel file similarly that it reads from an Excel file. Data can be provided as a list or a single input to a certain cell or a selection of cells.

Example 3:

Python3

# Python3 code to write 
# data to Excel 
import xlwings as xw 
  
# Selecting a sheet in Excel 
ws = xw.Book('data.xlsx').sheets("Sheet2") 
  
# Writing one value to 
# one cell 
ws.range("A1").value = "geeks"
  
# Writing multiple values 
# to a cell for automatic 
# data placement 
ws.range("B1").value = ["for", "geeks"] 
  
# Writing 2D data to a cell 
# to automatically put data 
# into correct cells 
ws.range("A2").value = [[1, 2, 3], ['a', 'b', 'c']] 
  
# Writing multiple data to 
# multiple cells 
ws.range("A4:C4").value = ["This", "is", "awesome"] 
  
# Outputting entire table 
print("Table :\n", ws.range("A1").expand().value) 