Python Excel

openpyxl load_workbook()

load_workbook() opens an Excel file that already exists on disk. It does not create a new file. After it returns, you can read cells, write cells, add a sheet, or delete one, then save.

How to use it

  1. Know the file name, including the .xlsx extension.
  2. Know the folder the file is in.
  3. Call load_workbook with that path. It returns a workbook object.
  4. Use the workbook to read, write, add, or delete sheets, then call save.

File in the current folder

If myfile.xlsx sits in the folder where you started Python, the name is enough:

import openpyxl

wb = openpyxl.load_workbook("myfile.xlsx")
print(wb.sheetnames)

File somewhere else

Pass the full path. A forward slash works on Windows and avoids escape-character mistakes:

path = "C:/files/pythonexcel/myfile.xlsx"
wb = openpyxl.load_workbook(path)

From here, wb is your handle on that file. Reading and writing cells is covered in the openpyxl tutorial and in write to a cell.

Options you will actually use

wb = openpyxl.load_workbook("myfile.xlsx", data_only=True)

data_only=True returns the last calculated value of a formula instead of the formula text. Excel must have opened and saved the file once, because openpyxl does not calculate formulas itself.

wb = openpyxl.load_workbook("myfile.xlsx", read_only=True)

read_only=True streams a large file instead of holding every cell in memory. You can iterate rows, but you cannot save that workbook object back. Open the file a second time without read_only when you need to write.

Create versus open

openpyxl.Workbook() makes a new, empty workbook in memory. load_workbook opens one that is already on disk. Mixing them up is the usual reason a script says the file does not exist: load_workbook will not create a missing file.