openpyxl read Excel in Python: rows, headers, and values

Select a timestamp to watch that moment on YouTube.

1. 00:00

   You have an Excel workbook with two columns. The search query asks how to read it with openpyxl, but the real job has two parts: obtain values from the .xlsx file and decide what each row means. Watch a local workbook get created in memory so every viewer sees the same fixture. Later, replace that buffer with a path to your own file. We will not pretend the browser lesson can access files on your computer. The question openpyxl read Excel in Python usually means: open an .xlsx file, pick a sheet, and obtain cell values as Python data. Install openpyxl in the same virtual environment that runs your script.

2. 00:42

   Then call load_workbook(path, read_only=True, data_only=True), choose a worksheet by name, and iterate with sheet.iter_rows(values_only=True). The workbook must be closed. This page shows the exact local code; its browser console works on a small list of already-read rows because a real local Excel path does not exist in a visitor's browser. An .xlsx workbook is a local file or a binary stream. In a local Python project, install openpyxl into the environment running your script, then use load_workbook on the file path. Read workbook.sheetnames first so a misspelled tab name is caught

3. 01:22

   deliberately. Choose read_only for a large input and close the workbook after iterating. The separate local example on this page creates its own workbook and demonstrates the entire process. The first run treats a row tuple as though it were a mapping with an item key. That raises an error, revealing what iter_rows with values_only actually returns. The library has given us values in column order; it has not assigned semantic names to each value. A workbook can also have multiple sheets. Before consuming records, identify the intended sheet and verify the header row.

4. 02:02

   values_only=True returns tuples of values instead of Cell objects. The first row can be treated as headers only when you have verified it. Blank rows and blank cells are common in manually edited workbooks. Do not silently coerce an empty price to zero or use a misspelled column. A function that converts rows into dictionaries should validate the expected headers, skip completely blank rows, and preserve missing values until a later business rule decides what to do with them. The browser practice panel does not see your computer's files. It begins at the next stage, with a list of row tuples as if

5. 02:42

   iter_rows(values_only=True) had returned them. This keeps the exercise reproducible while the local snippet remains the exact answer for opening a real Excel workbook. Replace its in-memory fixture with your own path only after testing on the fixture. The diagnosis prints sheetnames and row tuples. You can now see item and quantity as a header, Pencils and three as data, and a fully blank row. If a human renamed quantity to count, code that quietly zips the new header would still run but the downstream consumer might receive the wrong key. A deliberate header check makes that layout change visible.

6. 03:23

   Partially blank rows should not vanish merely because one cell is None. read_only=True streams large worksheets with lower memory use, but it limits editing features and requires closing the workbook. data_only=True returns the last cached value saved by a spreadsheet application for formula cells; it does not calculate formulas. If a formula has no cached result, you may receive None. For a small file you created yourself, the ordinary mode is convenient; for large input, read-only mode is usually a better first choice. The examples here do not assert that Excel formulas have been recalculated.

7. 04:04

   Use values_only=True when you need data rather than formatting or Cell objects. The first row often contains column names, but that convention is not guaranteed by Excel itself. Before mapping records, compare the tuple against the names your importer expects. This explicit check turns a changed spreadsheet layout into a clear error instead of silently producing incorrect dictionaries. The corrected local code saves a small .xlsx stream, reopens it with load_workbook in read-only mode, uses data_only for cached values, iterates values-only rows, validates headers, maps data, and closes the workbook.

8. 04:45

   This is the full answer for a real Excel file. For browser practice, we start after the read operation with the same tuples; the on-page task is the header and blank-row mapping. Keep those two stages separate so each is easy to test. The local example creates a two-row workbook in memory so the result is reproducible, then reopens it with load_workbook and reads rows. For your own file, replace the BytesIO object with an .xlsx path and use workbook.sheetnames to inspect available sheets. The browser task starts after row extraction: implement a strict header-to- record mapping and verify missing headers, blank rows, and empty values.

9. 05:29

   This separation makes the core data- cleaning step runnable without pretending that a browser has your spreadsheet or openpyxl installed. Blank values arrive as None. A completely blank row can be skipped, but a partially blank row should remain visible because the missing cell may need a business rule. Do not convert quantity=None to zero without a documented reason. In a real workbook, a formula with data_only=True may also return None if a spreadsheet application has not saved a cached result. The verification checks a partially blank row and a renamed header. We preserve None in the partial row and reject the changed header.

10. 06:11

    Remember that data_only does not evaluate formulas; it reads cached results saved by a spreadsheet application. A missing or stale cache needs a different workflow. Also close a read-only workbook after iteration. Once the structure is trustworthy, a separate domain validator can decide whether a missing quantity is acceptable for your project. Once the header is checked, convert each remaining row with dict(zip(header, row)). Before doing so, compare lengths. zip stops at the shorter input, so an unexpected cell count could discard a value or omit a key. Whether a completely blank row should be skipped or reported is an import policy;

11. 06:53

    this lesson skips it and tests that choice. The result is a list of Python dictionaries, still containing strings, numbers, and None exactly as the workbook supplied them. A later step can validate each domain field and report row numbers to the user. Keep workbook reading and record validation as separate functions; then you can test row logic in a browser without requiring a particular spreadsheet file.