Using OpenPyXl for XLSX files

OpenPyXl is a Python library for reading and writing Excel files. It is an open source project. A new workbook is created using the following command:

wb = Workbook()

We can access the currently active sheet by using the following command:

ws = wb.active()

To change the sheet name, use the title command:

ws.title = "Demo Name"

A single row can be added to the sheet using the append method:

ws.append()

A new sheet can be created using the create_sheet() method. An individual cell in the active sheet can be created using the column and row values:

# Assigns the cell corresponding to 
# column A and row 10 a value of 5
ws.['A10'] = 5  
#or
ws.cell(column=1, row=10, value=5)

A workbook can be saved using the save method. To load an existing workbook, we can use the load_workbook method. The names of the different sheets in an Excel workbook can be accessed using get_sheet_names(). 

The following code creates an Excel workbook with three sheets and saves it; later, it loads the sheet and accesses a cell. The code can be accessed from GitHub at OpenPyXl_example.ipynb:

# Creating and writing into xlsx file
from openpyxl import Workbook
from openpyxl.compat import range
from openpyxl.utils import get_column_letter
wb = Workbook()
dest_filename = 'empty_book.xlsx'
ws1 = wb.active
ws1.title = "range names"
for row in range(1, 40):
 ws1.append(range(0,100,5))
ws2 = wb.create_sheet(title="Pi")
ws2['F5'] = 2 * 3.14
ws2.cell(column=1, row=5, value= 3.14)
ws3 = wb.create_sheet(title="Data")
for row in range(1, 20):
 for col in range(1, 15):
 _ = ws3.cell(column=col, row=row, value="\
 {0}".format(get_column_letter(col)))
print(ws3['A10'].value)
wb.save(filename = dest_filename)

# Reading from xlsx file
from openpyxl import load_workbook
wb = load_workbook(filename = 'empty_book.xlsx')
sheet_ranges = wb['range names']
print(wb.get_sheet_names())
print(sheet_ranges['D18'].value)