Openpyxl write to a Cell

In this article you will learn how to write to a workbook cell in Python using Openpyxl. There are three ways in Openpyxl to write to a cell, with sheet reference, with cell reference and with row and column number.

Writing to individual cell of Excel sheet in Python with Openpyxl module has become very easy with code continuously evolving and becoming simpler. When using python if you want to write to individual cell or cells with openpyxl module, there are technically three methods. However, before starting writing to a cell here are the steps that must be performed in order to successfuly write data to individual cells.

If you want to Read, Write and Manipulate(Copy, cut, paste, delete or search for an item etc) Excel files in Python with simple and practical examples I will suggest you to see this simple and to the point Excel Openpyxl Course with examples about how to deal with MS Excel files in Python. This video course teaches efficiently how to manipulate excel files and automate tasks.

Everything you do in Microsoft Excel, can be automated with Python. So why not use the power of Python and make your life easy. You can make intelligent and thinking Excel sheets, bringing the power of logic and thinking of Python to Excel which is usually static, hence bringing flexibility in Excel and a number of opportunities.

Steps to write data to a cell

1. For using openpyxl, its necessary to import it

>>>import openpyxl

2. Next step is to create a workbook object

>>>myworkbook=openpyxl.load_workbook(path)

3. Create a reference to the sheet on which you want to write. Lets say you want to write on Sheet 1

>>>worksheet= myworkbook.get_sheet_by_name('Sheet1')

Example: Openpyxl Write to Cell - 3 Ways

Now after creating a reference to sheet we can easily write to cells. As stated earlier there are three methods.

Write to a cell with sheet reference

First method is to use sheet reference and write the cell address i.e C5, D8, F16 etc.

>>>worksheet['B4']='We are writing to B4'

Write to a cell with cell reference

Second method is to create a reference to cell with the help of sheet reference and then use that reference to write to cell with cell address

>>>mycell=worksheet['B4']

>>>mycell.value='Writing with reference to cell'

Write to a cell with Row and Column Number

Third method is to use row and column number and use worksheet reference to create reference to cell and then write to that cell. This method is extremely helpful for writing to a large number of cells one by one as it can use loops, like for loop very efficiently. If you want to write to E4 then the row is 4 and column is 5th in Excel.

>>>mycell= mysheet.cell(row=4, column=5)    

>>>mycell='Writing data to E4'

Using a loop to write to a series of cells picking values from a list.

>>>for i in range(5,15):

            cellref=mysheet.cell(row=i, column=5)

            cellref.value=lista[i]