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]