OpenPyXL – Writing To an Excel Workbook Using Python

OpenPyXL – Writing To an Excel Workbook Using Python

Prep

First let’s make sure you have the OpenPyXL library installed. If you’re already in Python and don’t want to needlessly exit the interpreter, then type the following:

dir(openpyxl)

This command should list out all the methods of OpenPyXL if it is in fact installed on your machine. Else, we’ll have to turn to pip. Assuming your machine returned, ‘name ‘openpyxl’ is not defined’ then make sure to exit the Python interpreter. IE you should no longer see ‘>>>’ in your terminal. Now, execute the following command to install the library.

pip install openpyxl

Writing to an .xlsx Doc with OpenPyXL

We should now be ready to manipulate workbooks programatically. Enter your python interpreter again. This example will deal solely with writing to an Excel doc that already exists so if you don’t already have a target doc, make one. In your interpreter assign the path to that already existing workbook to a variable of name path. If trying to manipulate a live document then I recommend you make a copy in case the file becomes corrupt. I unfortunately learned this the hard way… a few times.

path = 'Path/To/Your/Workbook/TestWorkBook.xlsx'

Open the file using OpenPyXL and create a reference to a worksheet where ‘Sheet1’ is the target sheet name.

import openpyxl
wb = openpyxl.load_workbook(path)
ws = wb['Sheet1']

Now we can refer to specific cells on our worksheet by executing the following command.

ws['A1'] = 'Test Write' #Where A1 refers to the cell

Changes will not be reflected until you save which requires that the Excel document be closed. The following will save the changes you have made.

wb.save(path)

Changes should now be visible in cell A1 if you open the doc. We could also check in Python by doing the following.

ws['A1'].value

I personally prefer referring to cells by number instead of A1, B4, etc. In my opinion it makes looping through columns and rows much easier. We can do this with cell(). The first input refers to the row number and the second to column number so:

ws.cell(row=1, column=1).value

Happy coding!

Leave a Comment