pyexcel-xlsxr 0.6.2

Read xlsx file using partial xml

pyexcel-xlsxr is a specialized xlsx reader using lxml. It does partial reading, meaning it wont load all content into memory.

lxml installation

This library depends on lxml. Because its availablity, the use of this library is restricted.

for PyPy, lxml == 3.4.4 are tested to work well. But lxml above 3.4.4 is difficult to get installed.

for Python 3.7, please use lxml==4.1.1.

Otherwise, this library works OK with lxml 3.4.4 or above.

Support the project

If your company uses pyexcel and its components in a revenue-generating product, please consider supporting the project on GitHub or Patreon. Your financial support will enable me to dedicate more time to coding, improving documentation, and creating engaging content.

Known constraints

Fonts, colors and charts are not supported.

Nor to read password protected xls, xlsx and ods files.

Installation

You can install pyexcel-xlsxr via pip:

$ pip install pyexcel-xlsxr

or clone it and install it:

$ git clone https://github.com/pyexcel/pyexcel-xlsxr.git
$ cd pyexcel-xlsxr
$ python setup.py install

Usage

As a standalone library

Read from an xlsx file

Here’s the sample code:

>>> from pyexcel_xlsxr import get_data
>>> data = get_data("your_file.xlsx")
>>> import json
>>> print(json.dumps(data))
{"Sheet 1": [[1, 2, 3], [4, 5, 6]], "Sheet 2": [["row 1", "row 2", "row 3"]]}

Read from an xlsx from memory

Continue from previous example:

>>> # This is just an illustration
>>> # In reality, you might deal with xlsx file upload
>>> # where you will read from requests.FILES['YOUR_XLSX_FILE']
>>> data = get_data(io)
>>> print(json.dumps(data))
{"Sheet 1": [[1, 2, 3], [4, 5, 6]], "Sheet 2": [[7, 8, 9], [10, 11, 12]]}

Pagination feature

Let’s assume the following file is a huge xlsx file:

>>> huge_data = [
...     [1, 21, 31],
...     [2, 22, 32],
...     [3, 23, 33],
...     [4, 24, 34],
...     [5, 25, 35],
...     [6, 26, 36]
... ]
>>> sheetx = {
...     "huge": huge_data
... }
>>> save_data("huge_file.xlsx", sheetx)

And let’s pretend to read partial data:

>>> partial_data = get_data("huge_file.xlsx", start_row=2, row_limit=3)
>>> print(json.dumps(partial_data))
{"huge": [[3, 23, 33], [4, 24, 34], [5, 25, 35]]}

And you could as well do the same for columns:

>>> partial_data = get_data("huge_file.xlsx", start_column=1, column_limit=2)
>>> print(json.dumps(partial_data))
{"huge": [[21, 31], [22, 32], [23, 33], [24, 34], [25, 35], [26, 36]]}

Obvious, you could do both at the same time:

>>> partial_data = get_data("huge_file.xlsx",
...     start_row=2, row_limit=3,
...     start_column=1, column_limit=2)
>>> print(json.dumps(partial_data))
{"huge": [[23, 33], [24, 34], [25, 35]]}

As a pyexcel plugin

No longer, explicit import is needed since pyexcel version 0.2.2. Instead, this library is auto-loaded. So if you want to read data in xlsx format, installing it is enough.

Reading from an xlsx file

Here is the sample code:

>>> import pyexcel as pe
>>> sheet = pe.get_book(file_name="your_file.xlsx")
>>> sheet
Sheet 1:
+---+---+---+
| 1 | 2 | 3 |
+---+---+---+
| 4 | 5 | 6 |
+---+---+---+
Sheet 2:
+-------+-------+-------+
| row 1 | row 2 | row 3 |
+-------+-------+-------+

Reading from a IO instance

You got to wrap the binary content with stream to get xlsx working:

>>> # This is just an illustration
>>> # In reality, you might deal with xlsx file upload
>>> # where you will read from requests.FILES['YOUR_XLSX_FILE']
>>> xlsxfile = "another_file.xlsx"
>>> with open(xlsxfile, "rb") as f:
...     content = f.read()
...     r = pe.get_book(file_type="xlsx", file_content=content)
...     print(r)
...
Sheet 1:
+---+---+---+
| 1 | 2 | 3 |
+---+---+---+
| 4 | 5 | 6 |
+---+---+---+
Sheet 2:
+-------+-------+-------+
| row 1 | row 2 | row 3 |
+-------+-------+-------+

License

New BSD License

Developer guide

Development steps for code changes

1. git clone https://github.com/pyexcel/pyexcel-xlsxr.git

2. cd pyexcel-xlsxr

Upgrade your setup tools and pip. They are needed for development and testing only:

1. pip install –upgrade setuptools pip

Then install relevant development requirements:

1. pip install -r rnd_requirements.txt # if such a file exists

2. pip install -r requirements.txt

3. pip install -r tests/requirements.txt

Once you have finished your changes, please provide test case(s), relevant documentation and update changelog.yml

Note

As to rnd_requirements.txt, usually, it is created when a dependent library is not released. Once the dependency is installed (will be released), the future version of the dependency in the requirements.txt will be valid.

How to test your contribution

Although nose and doctest are both used in code testing, it is advisable that unit tests are put in tests. doctest is incorporated only to make sure the code examples in documentation remain valid across different development releases.

On Linux/Unix systems, please launch your tests like this:

$ make

On Windows, please issue this command:

> test.bat

Before you commit

Please run:

$ make format

so as to beautify your code otherwise your build may fail your unit test.

Change log

0.6.2 - 31.10.2025

Fixed

1. Fix freeze when parsing certain corrupt XLSX files

2. Fix reading of files with more than 26 columns

Updated

1. Migrated to pytest

0.6.1 - 11.11.2024

Updated

1. #9: Potential fix for incorrect reading of data with empty cells when used with pyexcel

0.6.0 - 10.10.2020

Updated

1. New style xlsx plugins, promoted by pyexcel-io v0.6.2.

0.5.3 - 23.06.2020

Fixed

1. #5: AttributeError when a cell text is None

2. #2: unit test failed in OpenSuSE

0.5.2 - 15.09.2018

Updated

1. Fix python 3 compactibility

0.5.1 - 14.07.2018

Updated

1. #1: fix xml parsing problem when the microsoft spreadsheetml 2009 ac name space ‘x14ac’ made lxml an idiot

0.5.0 - 24.11.2017

Added

1. Initial release. In order align it with pyexcel 0.5.0 release, its version start from 0.5.0