.. image:: https://raw.githubusercontent.com/pyexcel/pyexcel.github.io/master/images/patreon.png :target: https://www.patreon.com/chfw
.. image:: https://raw.githubusercontent.com/pyexcel/pyexcel-mobans/master/images/awesome-badge.svg :target: https://awesome-python.com/#specific-formats-processing
.. image:: https://codecov.io/gh/pyexcel/pyexcel-xlsxr/branch/master/graph/badge.svg :target: https://codecov.io/gh/pyexcel/pyexcel-xlsxr
.. image:: https://badge.fury.io/py/pyexcel-xlsxr.svg :target: https://pypi.org/project/pyexcel-xlsxr
.. image:: https://img.shields.io/gitter/room/gitterHQ/gitter.svg :target: https://gitter.im/pyexcel/Lobby
.. image:: https://img.shields.io/static/v1?label=continuous%20templating&message=%E6%A8%A1%E7%89%88%E6%9B%B4%E6%96%B0&color=blue&style=flat-square :target: https://moban.readthedocs.io/en/latest/#at-scale-continous-templating-for-open-source-projects
.. image:: https://img.shields.io/static/v1?label=coding%20style&message=black&color=black&style=flat-square :target: https://github.com/psf/black
pyexcel-xlsxr is a specialized xlsx reader using lxml. It does partial reading, meaning it wont load all content into memory.
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.
If your company has embedded pyexcel and its components into a revenue generating
product, please support me on github, patreon <https://www.patreon.com/bePatron?u=5537627>_
or bounty source <https://salt.bountysource.com/teams/chfw-pyexcel>_ to maintain
the project and develop it further.
If you are an individual, you are welcome to support me too and for however long
you feel like. As my backer, you will receive
early access to pyexcel related contents <https://www.patreon.com/pyexcel/posts>_.
And your issues will get prioritized if you would like to become my patreon as pyexcel pro user.
With your financial support, I will be able to invest a little bit more time in coding, documentation and writing interesting posts.
Fonts, colors and charts are not supported.
Nor to read password protected xls, xlsx and ods files.
You can install pyexcel-xlsxr via pip:
.. code-block:: bash
$ pip install pyexcel-xlsxr
or clone it and install it:
.. code-block:: bash
$ git clone https://github.com/pyexcel/pyexcel-xlsxr.git
$ cd pyexcel-xlsxr
$ python setup.py install
Read from an xlsx file
Here's the sample code:
.. code-block:: python
>>> 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:
.. code-block:: python
>>> # 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:
.. code-block:: python
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:
.. code-block:: python
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:
.. code-block:: python
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:
.. code-block:: python
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]]}
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:
.. code-block:: python
>>> 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:
.. code-block:: python
>>> # 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 |
+-------+-------+-------+
New BSD License
Development steps for code changes
#. git clone https://github.com/pyexcel/pyexcel-xlsxr.git #. cd pyexcel-xlsxr
Upgrade your setup tools and pip. They are needed for development and testing only:
#. pip install --upgrade setuptools pip
Then install relevant development requirements:
#. pip install -r rnd_requirements.txt # if such a file exists #. pip install -r requirements.txt #. 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 dependecy is installed
(will be released), the future
version of the dependency in the requirements.txt will be valid.
Although nose and doctest are both used in code testing, it is adviable 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
Please run::
$ make format
so as to beautify your code otherwise your build may fail your unit test.
Updated
#. #9: Potential fix for incorrect reading of data with empty cells when used with pyexcel
Updated
#. New style xlsx plugins, promoted by pyexcel-io v0.6.2.
Fixed
#. #5 <https://github.com/pyexcel/pyexcel-xlsxr/issues/5>: AttributeError
when a cell text is None
#. #2 <https://github.com/pyexcel/pyexcel-xlsxr/issues/2>: unit test failed
in OpenSuSE
Updated
#. Fix python 3 compactibility
Updated
#. #1 <https://github.com/pyexcel/pyexcel-xlsxr/issues/1>_: fix xml parsing
problem when the microsoft spreadsheetml 2009 ac name space 'x14ac' made lxml
an idiot
Added
#. Initial release. In order align it with pyexcel 0.5.0 release, its version start from 0.5.0
FAQs
Read xlsx file using partial xml
We found that pyexcel-xlsxr demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
Research
The Socket Research Team breaks down a malicious wrapper package that uses obfuscation to harvest credentials and exfiltrate sensitive data.
Research
Security News
Attackers used a malicious npm package typosquatting a popular ESLint plugin to steal sensitive data, execute commands, and exploit developer systems.
Security News
The Ultralytics' PyPI Package was compromised four times in one weekend through GitHub Actions cache poisoning and failure to rotate previously compromised API tokens.