Working with Excel files in Python using Xlwings

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)

Output:

Excel Screenshot: