Python Language Tutorial => OpenPyXL

Example

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:

wb.save('filename.xlsx')

PDF - Download Python Language for free

Previous Next

Python Language

Fastest Entity Framework Extensions

Example

Got any Python Language Question?

Python Language

Python Language Python and Excel OpenPyXL

Fastest Entity Framework Extensions

Example

Got any Python Language Question?