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_book    = load_workbook(filename = file_name)
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

In this article, we learned how to:

• 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)