Write Data to Excel using Python

Last Updated on March 12, 2022 by Jay

We can write both raw data and formulas into an Excel file using Python. The openpyxl library gives us lots of flexibility for creating Excel files.

Navigate Excel File using Python

We discussed how to access Sheets and Cells in this guide.

Let’s first create an Excel workbook using openpyxl. Note a Workbook is always created with at least one Worksheet, wb.active returns the first Worksheet inside the Workbook:

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

In Excel, two common ways to reference a cell location is by 1) the column-row notation and 2) the (row, col) notation. For example, the below refer to the same cell at B3:

ws['C2']
<Cell 'Sheet'.C2>

ws.cell(2,3)
<Cell 'Sheet'.C2>

Although it’s easy for humans to read the column-row notation like “C2”, when we interact with cells programmatically, it’s easier to use the (row, col) notation because they are just index/numbers. The only thing to pay attention to is that the row and col index starts from 1 as opposed to 0.

Cell Coordinate Utility Methods

openpyxl provides several handy utility methods for coordinate conversion. From column-row to (row, col) and vice versa:

from openpyxl.utils.cell import (cols_from_range,
                                 rows_from_range,
                                 column_index_from_string,
                                 get_column_letter, 
                                 coordinate_to_tuple)

column_index_from_string("A")
1

get_column_letter(26)
'Z'

coordinate_to_tuple('B2')
(2, 2)

c = (2,3)
get_column_letter(c[1]) + str(c[0])
'C2'

We can also get all cells addresses individually from a range. Note the difference below:

cols_from_range() produces one column at a time
rows_from_range() produces one row at a time

for c in cols_from_range("A1:F6"):
    print(c)
('A1', 'A2', 'A3', 'A4', 'A5', 'A6')
('B1', 'B2', 'B3', 'B4', 'B5', 'B6')
('C1', 'C2', 'C3', 'C4', 'C5', 'C6')
('D1', 'D2', 'D3', 'D4', 'D5', 'D6')
('E1', 'E2', 'E3', 'E4', 'E5', 'E6')
('F1', 'F2', 'F3', 'F4', 'F5', 'F6')

for c in rows_from_range("A1:F6"):
    print(c)
('A1', 'B1', 'C1', 'D1', 'E1', 'F1')
('A2', 'B2', 'C2', 'D2', 'E2', 'F2')
('A3', 'B3', 'C3', 'D3', 'E3', 'F3')
('A4', 'B4', 'C4', 'D4', 'E4', 'F4')
('A5', 'B5', 'C5', 'D5', 'E5', 'F5')
('A6', 'B6', 'C6', 'D6', 'E6', 'F6')

Write Values to Excel using Python

There are two ways to write values into a cell. One is by setting the cell.value attribute, the other is by calling the cell(row, col, value) method. Note below the subtle difference between lines #2 and #3.

ws['C2'].value = 'Pi' 
ws.cell(2,4,value = 'Radius') #cell D2
ws.cell(2,5).value = 'Area'   #cell E2

The (row, col) notation makes looping through cells easy:

for i in range(1, 6):
    # starts from cell D3, ends at cell D7
    ws.cell(2+i, 4).value = i

We can also use the cell.offset(row, col) method to return a cell location relative to an active cell.

curr_cell = ws['D8']
for i in range(0,5):
    curr_cell.offset(row=i, column = 0).value = i+6

Write Formulas to Excel using Python

Writing formulas is as easy as writing values into Excel. All we need is to treat the Excel formulas as String values and assign them to the .value attribute. Inside the Python String value, we want to start with the equal sign = since that’s how we start an Excel formula.

ws['C3'].value = '=pi()'

Want to use absolute reference by “fixing” a cell coordinate in your formula? No problem! We can get the coordinate from a Cell object from its .coordinate attribute which returns a String value representing the cell address. Of course, in this example, we already know it’s “C3” since that’s what we entered into the code. This is to show how to get the coordinate of a cell programmatically.

ws['C3'].coordinate
'C3'

To make an absolute reference, we can use the absolute_coordinate() method in the utility module. It basically just adds the $ signs to fix a cell.

from openpyxl.utils.cell import absolute_coordinate
absolute_coordinate(ws['C3'].coordinate)
'$C$3'

Let’s calculate the Area column by writing Excel formulas to the cells, then save the Excel file to disk.

pi_cell = absolute_coordinate('C3')
for i in range(1,11):
    ws.cell(i+2,5).value = f'={pi_cell} * {ws.cell(i+2,4).coordinate} ^ 2'

wb.save('pi_eg.xlsx')