pyexcel-xls 0.5.9

Write to an xls file

Here’s the sample code to write a dictionary to an xls file:

>>> from pyexcel_xls import save_data
>>> data = OrderedDict() # from collections import OrderedDict
>>> data.update({"Sheet 1": [[1, 2, 3], [4, 5, 6]]})
>>> data.update({"Sheet 2": [["row 1", "row 2", "row 3"]]})
>>> save_data("your_file.xls", data)

Read from an xls file

Here’s the sample code:

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

Write an xls to memory

Here’s the sample code to write a dictionary to an xls file:

>>> from pyexcel_xls import save_data
>>> data = OrderedDict()
>>> data.update({"Sheet 1": [[1, 2, 3], [4, 5, 6]]})
>>> data.update({"Sheet 2": [[7, 8, 9], [10, 11, 12]]})
>>> io = StringIO()
>>> save_data(io, data)
>>> # do something with the io
>>> # In reality, you might give it to your http response
>>> # object for downloading

Read from an xls from memory

Continue from previous example:

>>> # This is just an illustration
>>> # In reality, you might deal with xls file upload
>>> # where you will read from requests.FILES['YOUR_XLS_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 xls 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.xls", sheetx)

And let’s pretend to read partial data:

>>> partial_data = get_data("huge_file.xls", 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.xls", 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.xls",
...     start_row=2, row_limit=3,
...     start_column=1, column_limit=2)
>>> print(json.dumps(partial_data))
{"huge": [[23, 33], [24, 34], [25, 35]]}

Reading from an xls file

Here is the sample code:

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

Writing to an xls file

Here is the sample code:

>>> sheet.save_as("another_file.xls")

Reading from a IO instance

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

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

Writing to a StringIO instance

You need to pass a StringIO instance to Writer:

>>> data = [
...     [1, 2, 3],
...     [4, 5, 6]
... ]
>>> io = StringIO()
>>> sheet = pe.Sheet(data)
>>> io = sheet.save_to_memory("xls", io)
>>> # then do something with io
>>> # In reality, you might give it to your http response
>>> # object for downloading