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')
```

## Additional Resources

How to Use Python to Read Excel Formula