PyExcel XLS

0.7.1 · active · verified Sat Apr 11

pyexcel-xls is a wrapper library designed to read, manipulate, and write data in the older Microsoft Excel XLS (Binary Interchange File Format) format. It functions primarily as a plugin for the `pyexcel` library, offering a unified API for spreadsheet data handling. The library is currently at version 0.7.1 and maintains an active, albeit sporadic, release cadence, typically tied to essential maintenance or updates of its core dependencies.

Warnings

breaking Starting with `v0.7.0`, `pyexcel-xls` removed its hard pin on `xlrd < 2.0`. If `xlrd` version 2.0.0 or greater is installed, `pyexcel-xls` will NO LONGER support reading `.xlsx` or `.xlsm` files. `v0.7.1` explicitly removed `.xlsm` support due to `xlrd>=2` changes.
Fix: For `.xlsx` or `.xlsm` files, install `pyexcel-xlsx` (`pip install pyexcel-xlsx`). `pyexcel-xlsx` uses `openpyxl` which properly handles these modern formats. If you specifically need `pyexcel-xls` to read `.xlsx` (though not recommended), you would have to pin `xlrd < 2.0.0` in your environment (e.g., `pip install 'xlrd<2.0.0' pyexcel-xls`).
breaking Version `0.6.0` dropped support for Python 2.x, requiring Python 3.6 or newer.
Fix: Ensure your project runs on Python 3.6 or a more recent version.
gotcha The `.xls` (Excel 97-2003) file format has a hard limit of 65,536 rows and 256 columns per sheet. Attempting to write or read files exceeding these limits will result in errors or data loss.
Fix: For datasets larger than 65,536 rows, use the `.xlsx` format, which is supported by the `pyexcel-xlsx` library.
gotcha `pyexcel-xls` (and `pyexcel` in general) focuses solely on data content. It does not preserve or handle any formatting, styles, fonts, colors, charts, or formulas within the Excel file.
Fix: Do not use `pyexcel-xls` if the preservation of visual formatting, styles, or formulas is a requirement. If only data extraction/insertion is needed, be aware that all such information will be lost upon saving.
gotcha When reading `.xls` files, zero values typed into DATE formatted fields are parsed as '01/01/1900', and zero values in TIME formatted fields are parsed as '00:00:00'.
Fix: Implement post-processing logic to detect and handle these specific date/time interpretations if zero values in these fields are expected in your source data.

Install

pip install pyexcel-xls Install pyexcel-xls

Imports

pyexcel.get_book
```
import pyexcel
book = pyexcel.get_book(file_name='your_file.xls')
```
When used as a pyexcel plugin, explicit imports from pyexcel_xls are not needed; pyexcel auto-loads it.
pyexcel.get_sheet
```
import pyexcel
sheet = pyexcel.get_sheet(file_name='your_file.xls')
```
The primary way to interact with .xls files via pyexcel's unified API.
get_data
```
from pyexcel_xls import get_data
```
This is for standalone usage of pyexcel-xls without the main pyexcel library, but direct use of pyexcel is generally recommended.
save_data
```
from pyexcel_xls import save_data
```
This is for standalone usage of pyexcel-xls without the main pyexcel library, but direct use of pyexcel is generally recommended.

Quickstart

This quickstart demonstrates how to use `pyexcel` (which leverages `pyexcel-xls` automatically) to write data to an `.xls` file and then read it back. It uses `OrderedDict` for structured sheet data and then saves and loads using `pyexcel.save_as` and `pyexcel.get_book` respectively.

import pyexcel
from collections import OrderedDict
import os

# Create some data
data = OrderedDict()
data.update({"Sheet 1": [[1, 2, 3], [4, 5, 6]]})
data.update({"Sheet 2": [["row 1", "row 2", "row 3"]]})

# Define a filename
file_name = "my_data.xls"

# Write data to an XLS file
pyexcel.save_as(dest_file_name=file_name, adict=data)
print(f"Data written to {file_name}")

# Read data from the XLS file
book = pyexcel.get_book(file_name=file_name)
for sheet in book:
    print(f"Sheet Name: {sheet.name}")
    for row in sheet:
        print(row)

# Clean up the created file
os.remove(file_name)
print(f"Cleaned up {file_name}")

view raw JSON →