openpyxl reads and writes Excel .xlsx files from Python. Excel itself does not have to be installed. Use it to copy rows, rename sheets, add or delete worksheets, style cells, or build a file for someone else to open in Excel.
Install it once, from a terminal:
pip install openpyxl
.xlsx extension.Create testfile.xlsx in the same folder as your script and type this table into Sheet1. The later lessons use the same idea: a small file you can throw away.
| Date | Region | Name | Item | Quantity | Rate | Total |
|---|---|---|---|---|---|---|
| 7/6/14 4:50 AM | AB | Connor | Pencil | 15 | 1.99 | 29.85 |
| 4/23/14 2:25 PM | DG | Shane | Binder | 20 | 19.99 | 399.80 |
| 5/9/14 4:45 AM | PQ | Thatcher | Pencil | 25 | 4.99 | 124.75 |
| 3/15/14 6:00 AM | TX | James | Pencil | 35 | 2.99 | 104.65 |
openpyxl looks for a bare filename in the current working directory. Check that folder before you load a file:
import os print(os.getcwd()) os.chdir(r"C:\data\myfiles")
import openpyxl
wb = openpyxl.load_workbook("testfile.xlsx")
print(type(wb))
print(wb.sheetnames)
load_workbook returns a Workbook. sheetnames is the list of worksheet titles, for example ['Sheet1', 'Sheet2', 'Sheet3']. If you rename the tabs in Excel, save the file, and load it again, the list follows the new names.
Older openpyxl versions used get_sheet_names(). That method has been removed. Use wb.sheetnames.
sheet = wb["Sheet1"] print(sheet.title) print(type(sheet))
The result is a Worksheet. You can also take whatever tab was active when the file was saved:
sheet = wb.active
Older code called get_sheet_by_name("Sheet1") or get_active_sheet(). Both are gone. Index the workbook with the sheet title, or use wb.active.
print(sheet["B2"].value) print(sheet.cell(row=2, column=2).value)
Both lines read the same cell. "B2" is the Excel address. cell(row, column) is easier inside a loop because row and column are numbers. Row 1, column 1 is cell A1.
for row in sheet.iter_rows(min_row=2, max_col=4, values_only=True):
print(row)
Each row is a tuple of cell values. values_only=True skips the cell objects and returns the stored values.
Nothing is written to disk until you save. Pass a new filename when you want to keep the original:
wb.save("testfile-copy.xlsx")
Libraries covered: openpyxl, xlrd, xlsxwriter, xlwt, and xlutils.