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
workbook = opx.load_workbook(workbook_path, read_only=True)
Once you have loaded the workbook into memory, you can access the individual sheets using
first_sheet = workbook.worksheets
If you want to specify the name of an available sheets, you can use
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.value
rows is a list of
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
ws.sheet_properties.tabColor = 'FFC0CB'
To save our created workbook we finish with: