Jeremy K.

Posted on Sep 7

How to Import Excel Data into Python

#programming #python

In routine data work, importing Excel files into Python for further analysis is a common need. This article shows how to use Free Spire.XLS for Python—a free library—to read, parse, and import Excel data.

1. Why Choose This Approach

Python offers many libraries for handling Excel files. Free Spire.XLS for Python stands out because it does not require Microsoft Office—it works with .xls and .xlsx files in a pure Python environment. It supports Python 3.6+ and runs on Windows, Linux, and macOS. The free version is more than adequate for most individual and small‑project data import tasks.

2. Environment Setup

Install the library via pip:

pip install Spire.XLS.Free

After installation, import the required modules:

from spire.xls import *
from spire.xls.common import *

3. Core Object Model

Two central objects form the foundation of this library:

Workbook – represents the entire Excel workbook, handling file loading and saving.
Worksheet – represents a single worksheet, providing cell access and data reading.

4. Basic Excel Data Reading

4.1 Loading an Excel File

workbook = Workbook()
workbook.LoadFromFile("data.xlsx")

4.2 Accessing a Worksheet

# First worksheet (index 0)
worksheet = workbook.Worksheets[0]
# Alternative:
worksheet = workbook.Worksheets.get_Item(0)

4.3 Reading Cell Values

Use Worksheet.Range to reference a cell, then access its properties: Value, Text, or NumberValue.

# By row and column (1‑based)
cell_value = worksheet.Range[1, 1].Value   # A1

# By address
cell_text = worksheet.Range["B2"].Text

# Numeric value
cell_number = worksheet.Range["C3"].NumberValue

4.4 Iterating Over All Data

row_count = worksheet.AllocatedRange.RowCount
col_count = worksheet.AllocatedRange.ColumnCount

for row in range(1, row_count + 1):
    row_data = []
    for col in range(1, col_count + 1):
        row_data.append(worksheet.Range[row, col].Value)
    print(row_data)

5. Practical Example: Import into a Python List

This function reads an entire worksheet into a two‑dimensional list:

from spire.xls import Workbook

def excel_to_list(file_path):
    """Load Excel data into a list of lists."""
    workbook = Workbook()
    workbook.LoadFromFile(file_path)
    sheet = workbook.Worksheets[0]

    row_count = sheet.AllocatedRange.RowCount
    col_count = sheet.AllocatedRange.ColumnCount

    data = []
    for row in range(1, row_count + 1):
        row_data = []
        for col in range(1, col_count + 1):
            row_data.append(sheet.Range[row, col].Value)
        data.append(row_data)

    workbook.Dispose()  # Release resources
    return data

# Usage
data = excel_to_list("sales_data.xlsx")
for row in data:
    print(row)

6. Practical Example: Import into a List of Dictionaries (with Headers)

For easier field‑based access, convert data to a list of dictionaries using the first row as keys:

from spire.xls import Workbook

def excel_to_dict_list(file_path):
    """Load Excel data into a list of dictionaries; first row becomes keys."""
    workbook = Workbook()
    workbook.LoadFromFile(file_path)
    sheet = workbook.Worksheets[0]

    row_count = sheet.AllocatedRange.RowCount
    col_count = sheet.AllocatedRange.ColumnCount

    # Read headers from the first row
    headers = [sheet.Range[1, col].Value for col in range(1, col_count + 1)]

    # Read data rows (starting at row 2)
    result = []
    for row in range(2, row_count + 1):
        record = {}
        for col in range(1, col_count + 1):
            record[headers[col - 1]] = sheet.Range[row, col].Value
        result.append(record)

    workbook.Dispose()
    return result

# Usage
records = excel_to_dict_list("employee_info.xlsx")
for emp in records:
    print(f"Name: {emp.get('Name')}, Department: {emp.get('Department')}")

Summary

This article walked through the full workflow for importing Excel data into Python, covering environment setup, basic reading, list conversion, and dictionary‑list conversion. The free library requires no Office dependencies, is easy to install, and offers an intuitive API—making it a practical choice for handling small to medium‑sized Excel import tasks.

DEV Community