Python - Reading and Writing Excel file using openpyxl library

By xngo on June 8, 2019

Overview

openpyxl is a Python library to read/write Excel 2010 xlsx/xlsm files.

Installation

pip install openpyxl

Writing Excel file

#!/usr/bin/python3
 
import datetime
 
# Writing Excel
# #####################
# Import package.
from openpyxl import Workbook
 
# Create a workbook.
workbook = Workbook()
 
# Create a worksheet at position 0.
worksheet = workbook.create_sheet("My Fruits Sheet", 0)
 
# Assign data directly to specific cell.
worksheet['A1'] = 42
 
# Add row with data.
worksheet.append( ['apple', 2.99, 'banana', 4.99] )
 
# Python types will automatically be converted.
worksheet['A3'] = datetime.datetime.now()
 
# Save the file
workbook.save("sample.xlsx")

Reading Excel file

# Reading Excel
# #####################
from openpyxl import load_workbook
 
# Load Excel file.
workbook = load_workbook('sample.xlsx')
 
# List all sheet names.
print(workbook.sheetnames)
 
# Select sheet.
worksheet = workbook['My Fruits Sheet']
 
# Print value of cell.
print(worksheet['A3'].value)
print(worksheet.cell(row=2, column=3).value)

Reference

  • https://openpyxl.readthedocs.io/en/stable/tutorial.html#create-a-workbook
  • https://openpyxl.readthedocs.io/en/stable/usage.html
  • http://www.python-excel.org/

About the author

Xuan Ngo is the founder of OpenWritings.net. He currently lives in Montreal, Canada. He loves to write about programming and open source subjects.