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.

Add the Required Starter Code (lines [1-4]) to the top of each code snippet. This snippet will allow the code to run error-free.

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

Required starter code:

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. Running this code displays the Last name for Dizzy.

Code

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. Running this code displays the Birth Year of Fats Waller.

Code

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

Output

1904

How to Access a Range of Worksheet Values via Iteration

The code below (lines [5-7]) output the values in cells A-B, rows 2-4 from the active Worksheet to the terminal.

  • Line [5] 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.
  • Line [6] provides the code needed to access these values.

Code

print(cells)

Output

((<Cell 'Sheet1'.A2>, <Cell 'Sheet1'.B2>), (<Cell 'Sheet1'.A3>, <Cell 'Sheet1'.B3>), (<Cell 'Sheet1'.A4>, <Cell 'Sheet1'.B4>))
  • Line [6] initializes a for loop (iterator) to display two values (cell A, cell B) for each row in the range selected earlier (A2:B4).
  • Line [7] outputs the values of cell A (cA) and cell B (cB) for each row iteration.
cells = w_sheet['A2': 'B4']
for cA, cB in cells:
    print(cA.value, cB.value)

Output

Miles    Davis   
Duke     Ellington
John     Coltrane

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

To display all or a small portion of the Active Worksheet data, use the iter_rows() method.

Note: ? To display all values, omit all parameters except for values_only=True.

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

min_row=1sets the start position of the row range to be Row 1
max_row=3sets the stop position of the row range to be Row 3
min_col=1sets the start position of the column range to be Column A
max_cols=4sets the stop position of the column range to be Column D

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

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

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 [5] initializes a loop iteration and passes the following parameters inside the iter_rows() method:

min_row=2sets the start position of the row range to be Row 2
max_row=11sets the stop position of the row range to be Row 11
min_col=7sets the start position of the column range to be Column G
max_cols=7sets the stop position of the column range to be Column G

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 [6] 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:

  • Line [5] loops through each row from the Active Worksheet.
  • Line [6] loops through each cell of that row.
  • Line [7] outputs the value of each cell to the terminal.

The iteration continues until all values have been output.

for row in w_sheet.rows:
    for cell in row:
        print(cell.value)

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)