Python Language Python and Excel OpenPyXL


OpenPyXL is a module for manipulating and creating xlsx/xlsm/xltx/xltm workbooks in memory.

Manipulating and reading an existing workbook:

import openpyxl as opx
#To change an existing wookbook we located it by referencing its path
workbook = opx.load_workbook(workbook_path)

load_workbook() contains the parameter read_only, setting this to True will load the workbook as read_only, this is helpful when reading larger xlsx files:

workbook = opx.load_workbook(workbook_path, read_only=True)

Once you have loaded the workbook into memory, you can access the individual sheets using workbook.sheets

first_sheet = workbook.worksheets[0]

If you want to specify the name of an available sheets, you can use workbook.get_sheet_names().

sheet = workbook.get_sheet_by_name('Sheet Name')

Finally, the rows of the sheet can be accessed using sheet.rows. To iterate over the rows in a sheet, use:

for row in sheet.rows:
    print row[0].value

Since each row in rows is a list of Cells, use Cell.value to get the contents of the Cell.

Creating a new Workbook in memory:

#Calling the Workbook() function creates a new book in memory
wb = opx.Workbook()

#We can then create a new sheet in the wb
ws = wb.create_sheet('Sheet Name', 0) #0 refers to the index of the sheet order in the wb

Several tab properties may be changed through openpyxl, for example the tabColor:

ws.sheet_properties.tabColor = 'FFC0CB'

To save our created workbook we finish with:'filename.xlsx')