openpyxl Python Library Basics

Download and install the latest version of python from Python Website.

Setting up virtual environment is not a must. But this is a best practice when working on python projects. Click virtual environment for more details.

Install openpyxl using the command: pip install openpyxl

Type command pip freeze
Should give an output like:

et-xmlfile==1.1.0
openpyxl==3.0.9

New excel files can be created using openpyxl. Import Workbook module from openpyxl library as below. Import os module also to work with the folders to save and access the file, if needed.

from openpyxl import Workbook

w = Workbook()
sh = w.active
w.save('NewWorkbook.xlsx')

To open an excel workbook, import load_workbook module and call load_workbook method with the file path as parameter as shown below. Worksheets can be accessed using zero based index.

from openpyxl import load_workbook

w = load_workbook('NewWorkBook.xlsx')
sh = w.worksheets[0]
print(sh)

To modify a workbook, open it and add or remove content as needed and save as shown in create work book section.

2 ways are there. Either add content to the specific cell or append content as new rows.

from openpyxl import Workbook

w = Workbook()
sh = w.active

sh['A1'] = 'First row first cell'
sh.append(["Second row first cell", 'cell 2', 3, 4, 'Cell 5'])

w.save('NewWorkbook.xlsx')

Shown below is an easy way to adjust the column width. Open the required sheet within the work book and using the column_dimensions property, we can set the width of any column.

from openpyxl import Workbook

w = Workbook()
sh = w.active

sh['A1'] = 'First row first cell'
colno = 1
sh.column_dimensions[str(chr(64 + colno))].width = 50

w.save('NewWorkbook.xlsx')

Cell content can be right, left or center aligned using the alignment property. To apply alignment, import Alignment module from openpyxl.styles. Alignment can be applied to columns as well.

from openpyxl import Workbook
from openpyxl.styles import Alignment

w = Workbook()
sh = w.active

sh['A1'] = 'First row first cell'
colno = 1
sh.column_dimensions[str(chr(64 + colno))].width = 50
sh['A1'].alignment = Alignment(horizontal = 'right')

w.save('NewWorkbook.xlsx')

Apply styles to a cell or an entire row as below. Import Font module from openpyxl.styles.

from openpyxl import Workbook
from openpyxl.styles import Font

w = Workbook()
sh = w.active

sh['A1'] = 'First row first cell'
sh['A1'].font = Font(color = '15cafb15', bold=True)
sh.append(["Second row first cell", 'cell 2', 3, 4, 'Cell 5'])
for row in sh["2:2"]: #For entire second row
    row.font = Font(bold=True)

w.save('NewWorkbook.xlsx')

To delete a row, use the built-in delete_rows method and provide row number starting from 1. Multi-row delete option is also available.

from openpyxl import Workbook
from openpyxl.styles import Font

w = Workbook()
sh = w.active

sh.delete_rows(1) #Delete first row
sh.delete_rows(2, sh.max_row+1) #Delete second to last row

w.save('NewWorkbook.xlsx')

To add a new column, just add some content to the required cells as shown below.

from openpyxl import Workbook
from openpyxl.styles import Font

w = Workbook()
sh = w.active

sh['C1'], sh['D1'] = 'Third column', 'Fourth column'

w.save('NewWorkbook.xlsx')

We can use iter_rows method to get all the rows with data. Use for loop to iterate through the rows.

from openpyxl import Workbook
from openpyxl.styles import Font

w = Workbook()
sh = w.active

sh['A1'], sh['B1'] = 20, 100
sh['A2'], sh['B2'] = 30, 50

for r in sh.iter_rows():
    print(r[0].value, r[1].value)

w.save('NewWorkbook.xlsx')

We have the option to apply formulas in excel sheet rows or columns. Same thing can be implemented using openpyxl as well. Access the required cells and apply the calculations as shown below

To apply same type of calculations to an entire column, use a for loop.

from openpyxl import Workbook
from openpyxl.styles import Font

w = Workbook()
sh = w.active

sh['A1'], sh['B1'] = 20, 100
sh['A2'], sh['B2'] = 30, 50

for i, r in enumerate(sh.iter_rows()):
    sh['C' + str(i + 1)] = r[0].value * r[1].value

w.save('NewWorkbook.xlsx')