Excel File Reader PRO#

This feature requires at least v0.28.0.

xlwings PRO comes with an ultra fast Excel file reader. Compared with pandas.read_excel(), you should be able to see speedups anywhere between 5 to 25 times when reading a single sheet. The exact speed will depend on your content, file format, and Python version. The following Excel file formats are supported:

xlsx / xlsm / xlam
xlsb
xls

Other advantages include:

Support for named ranges
Support for dynamic ranges via myrange.expand() or myrange.options(expand="table"), respectively.
Support for converters so you can read in ranges not just as pandas DataFrames, but also as NumPy arrays, lists, scalar values, dictionaries, etc.
You can read out cell errors like #DIV/0! or #N/A as strings instead of converting them all into NaN

Unlike the classic (“interactive”) use of xlwings that requires Excel to be installed, reading a file doesn’t depend on an installation of Excel and therefore works everywhere where Python runs. However, reading directly from a file requires the workbook to be saved before xlwings is able to pick up any changes.

Reading a specific range#

To open a file in read mode, provide the mode="r" argument: xw.Book("myfile.xlsx", mode="r"). You usually want to use Book as a context manager so that the file is automatically closed and resources cleaned up once the code leaves the body of the with statement:

import xlwings as xw

with xw.Book("myfile.xlsx", mode="r") as book:
    sheet1 = book.sheets[0]
    data = sheet1["A1:B2"].value

If you don’t use the with statement, make sure to close the book manually via book.close().

Reading an entire sheet#

To read an entire sheet, use the cells property:

with xw.Book("myfile.xlsx", mode="r") as book:
    sheet1 = book.sheets[0]
    data = sheet1.cells.value

Converters: DataFrames etc.#

You can use the usual converters, for example to read in a range as a DataFrame:

with xw.Book("myfile.xlsx", mode="r") as book:
    sheet1 = book.sheets[0]
    df = sheet1["A1:B2"].options("df").value
    # As usual, you can also provide more options
    df = sheet1["A1:B2"].options("df", index=False).value

For more details, see Converters and Options.

Named Ranges#

Named ranges are supported like so:

with xw.Book("myfile.xlsx", mode="r") as book:
    sheet1 = book.sheets[0]
    data = sheet1["myname"].value  # get values
    address = sheet1["myname"].address  # get address

Dynamic Ranges#

You can make use of the usual range expansion to read in a range of dynamic size:

with xw.Book("myfile.xlsx", mode="r") as book:
    sheet1 = book.sheets[0]
    data = sheet1["A1"].expand().value

Cell errors#

While xlwings reads in cell errors such as #N/A as None by default, you may want to read them in as strings if you’re specifically looking for these by using the err_to_str option:

with xw.Book("myfile.xlsx", mode="r") as book:
    sheet1 = book.sheets[0]
    data = sheet1["A1:B2"].option(err_to_str=True).value

Limitations#

The reader is currently only available via pip install xlwings. Installation via conda is not yet supported, but you can still use pip to install xlwings into a Conda environment!
Date cells: Excel cells with a Date/Time are currently only converted to a datetime object in Python for xlsx file formats. For xlsb format, pandas has the same restriction though (it uses pyxlsb under the hood).
Dynamic ranges: myrange.expand() is currently inefficient, so will slow down the reading considerably if the dynamic range is big.
Named ranges: Accessing named ranges is currently only supported via mysheet["mynamedrange"].value, but not via mybook.names or mysheet.names.
Excel tables: Accessing data via table names isn’t supported at the moment.
Options: except for err_to_str, non-default options are currently inefficient and will slow down the read operation. This includes dates, empty, and numbers.
Formulas: currently only the cell values are supported, but not the cell formulas.
This is only a file reader, writing files is currently not supported.