Python Excel β Accessing Worksheet Data

Part 2 of Working with Excel centers around accessing data from the Active Worksheet. This skill is one of the must-haves if you are interested in pursuing a career as a Data Scientist.

Background

After completing Part 1 of this series, you should be comfortable using Python to:

• create an Excel file,
• import a library,
• load an `xlsx` file,
• display a sheet title,
• understand the term Active Worksheet.

The  `j-greats.xlsx` should exist on your system. If you do not have this particular file, click here for instructions.

Add the Required Starter Code to the top of each code snippet. This snippet will allow the code to run error-free.

```from openpyxl import load_workbook

file_name = 'j-greats.xlsx'
w_sheet   = w_book.active```

How to Access the Value of a Single Worksheet Cell

The scenarios below are common ways to access and return a single value from the Active Worksheet. Running this code retrieves a value and outputs it to the terminal.

Scenario 1

To retrieve the value from a single cell, access this value by using a specific row/column combination. For example, running this code displays the Last name for Dizzy.

```x = w_sheet['B5'].value
print(x)```

Output

`Gillespie`

Scenario 2

Another way to retrieve the value from a single cell is to specify a `row=y`, `column=x` combination.

```x = w_sheet.cell(row=8, column=3).value
print(x)```

Running this code displays the Birth Year of Fats Waller.

Output

`1904`

How to Access a Range of Worksheet Values via Iteration

The code below outputs the values in cells A-B, rows 2-4, from the active Worksheet to the terminal.

Have a look at the following code snippet:

```cells = w_sheet['A2': 'B4']
for cA, cB in cells:
print(cA.value, cB.value)```
• Line [1] sets a cell range and assigns this to the cells object. If the `cells` object is output to the terminal, a Tuple of Tuples displays. We iterate over the tuples next and print them line by line to prettify the output.
• Line [2] initializes a for loop (iterator) to display two values (cell A, cell B) for each row in the range selected earlier (A2:B4).
• Line [3] outputs the values of cell A (cA) and cell B (cB) for each row iteration.

Output

``````Miles    Davis
Duke     Ellington
John     Coltrane``````

How to Read Values from Rows/Columns using iter_rows()

Use the method to display all or a small portion of the Active Worksheet data.

π‘Note: To display all values, omit all parameters except for `values_only=True`.

```for value in w_sheet.iter_rows(min_row=1, max_row=3, min_col=1, max_col=4,  values_only=True):
print(value)```

In the code, line [1] initializes a loop iteration and passes the following parameters inside the `iter_rows()` method:

Line [2] outputs the values to the terminal as Tuples.

Output

```('First', 'Last', 'Birth Year', 'Birth State')
('Miles', 'Davis', 1926, 'Illinois')
('Duke', 'Ellington', 1899, 'Washington')```

How to Access Values from Rows/Columns using iter_cols()

Accessing all values from a specific column is a frequent request. For this example, all cell values from the Est. Worth column display.

In the code below, line [1] initializes a loop iteration and passes the following parameters inside the `iter_rows()` method:

If `values_only` equals `True`, the values will display. Otherwise, an object that references the above displays.

Output

```(<Cell 'Sheet1'.G2>,  <Cell 'Sheet1'.G3>,  <Cell 'Sheet1'.G4>,  <Cell 'Sheet1'.G5>, <Cell 'Sheet1'.G6>,
<Cell 'Sheet1'.G7>,  <Cell 'Sheet1'.G8>,  <Cell 'Sheet1'.G9>, <Cell 'Sheet1'.G10>,  <Cell 'Sheet1'.G11>)```

Line [3] outputs the values to the terminal as a tuple.

```cells = w_sheet['G2': 'G11']
for value in w_sheet.iter_cols(min_row=2, max_row=11, min_col=7, max_col=7, values_only=True):
print(value)```

Output:

`(1343500, 1911400, 2575500, 3690300, 8275780, 5145800, 3250930, 7670945, 4375395, 1560454)`

How to Access All Values from the Active Worksheet

Another way to display all values in the Active Worksheet is iteration using the `w_sheet` object as shown below:

```for row in w_sheet.rows:
for cell in row:
print(cell.value)```
• Line [1] loops through each row from the Active Worksheet.
• Line [2] loops through each cell of that row.
• Line [3] outputs the value of each cell to the terminal.

The iteration continues until all values have been output.

Output (first two rows only):

```First
Last
Birth Year
Birth State
Birth Country
Instrument
Est.Worth
Miles
Davis
1926
Illinois
USA
Trumpet
1343500```

Summary

• Access a single value,
• Access a range of values
• Access values using `iter_rows()`,
• Access values using `iter_cols()`,
• Access all values.

Next Up

Part 3 in Working with Excel will focus on how to:

• Append Worksheet Row(s)
• Modify Worksheet Data
• Insert Worksheet Column(s)
• Delete Worksheet Row(s)
• Delete Worksheet Column(s)