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 or value etc) Excel files in Python with simple and practical examples I will suggest you to see this simple and to the point
Python 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.
Automate your Excel Tasks and save Time and Effort. You can save dozens or hundreds of hours by Python Excel Automation with just a single click get your tasks done with in seconds which used to take hours of Manual Excel and Data Entry Work.
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]