A spreadsheet is a two-dimensional grid for storing and manipulating tabular data. Cells may contain numbers, strings, blanks, or formulas, making spreadsheets useful for ad hoc data work but more complex to process than CSV files. section 14.2.9 section 21.3
Practical rules for working with spreadsheets include:
- Treat blank cells used in numerical calculations as zero when that matches the spreadsheet’s intended semantics. section 14.2.9
- Represent nonnumeric strings as invalid values when calculations should reject them, and propagate that invalid state through arithmetic. section 14.2.9
- Read an Excel file by loading its workbook, selecting a worksheet, iterating over rows, and extracting cell values. section 21.3
- Prefer direct Excel processing for many files, but convert to CSV when a simple, one-time extraction is more convenient. section 21.3
Reading a worksheet with openpyxl
A simple extraction loads the workbook, selects the first worksheet, and collects each row as a list of cell values. The same temperature dataset can then be processed as ordinary Python data. section 21.3
from openpyxl import load_workbook
wb = load_workbook('temp_data_01.xlsx')
results = []
ws = wb.worksheets[0]
for row in ws.iter_rows():
results.append([cell.value for cell in row])
print(results)
[['Notes', 'State', 'State Code', 'Month Day, Year', 'Month Day, Year
Code', 'Avg Daily Max Air Temperature (F)', 'Record Count for Daily Max Air
Temp (F)', 'Min Temp for Daily Max Air Temp (F)', 'Max Temp for Daily Max
Air Temp (F)', 'Avg Daily Max Heat Index (F)', 'Record Count for Daily Max
Heat Index (F)', 'Min for Daily Max Heat Index (F)', 'Max for Daily Max
Heat Index (F)', 'Daily Max Heat Index (F) % Coverage'], [None, 'Illinois',
17, 'Jan 01, 1979', '1979/01/01', 17.48, 994, 6, 30.5, 'Missing', 0,
'Missing', 'Missing', '0.00%'], [None, 'Illinois', 17, 'Jan 02, 1979',
'1979/01/02', 4.64, 994, -6.4, 15.8, 'Missing', 0, 'Missing', 'Missing',
'0.00%'], [None, 'Illinois', 17, 'Jan 03, 1979', '1979/01/03', 11.05, 994,
-0.7, 24.7, 'Missing', 0, 'Missing', 'Missing', '0.00%'], [None,
'Illinois', 17, 'Jan 04, 1979', '1979/01/04', 9.51, 994, 0.2, 27.6,
'Missing', 0, 'Missing', 'Missing', '0.00%'], [None, 'Illinois', 17, 'May
15, 1979', '1979/05/15', 68.42, 994, 61, 75.1, 'Missing', 0, 'Missing',
'Missing', '0.00%'], [None, 'Illinois', 17, 'May 16, 1979', '1979/05/16',
70.29, 994, 63.4, 73.5, 'Missing', 0, 'Missing', 'Missing', '0.00%'],
[None, 'Illinois', 17, 'May 17, 1979', '1979/05/17', 75.34, 994, 64,
80.5,82.6, 2, 82.4, 82.8, '0.20%'], [None, 'Illinois', 17, 'May 18, 1979',
'1979/05/18', 79.13, 994, 75.5, 82.1, 81.42, 349, 80.2, 83.4,
'35.11%'],[None, 'Illinois', 17, 'May 19, 1979', '1979/05/19', 74.94,
994,66.9, 83.1, 82.87, 78, 81.6, 85.2, '7.85%']]
Use the worked listing to inspect the complete workbook-loading, worksheet-selection, row-iteration, and cell-value extraction pattern. section 21.3
