ENH: Adding XlsxWriter as an ExcelWriter() option

[jmcnamara]

related/closes #4517

Hi,

I am the author of the XlsxWriter module, a Python module for writing Excel XLSX files.

I am interested in submitting a PR to add this as a option when writing Excel xlsx files via to_excel().

It has a similar interface to the xlwt module but creates xlsx files. For large data files it is ~10x faster than OpenPyXL while maintaining low and constant memory usage. I occasionally see the current xlsx writer's speed and memory usage mentioned as a bug/limitation so it could be useful to have a fallback module option in those cases.

XlsxWriter also supports features such as charts and auto-filters that could be exposed at a later stage. See https://xlsxwriter.readthedocs.org/ for more details.

It uses standard libraries only and has an extensive test suite for Python 2.6, 2.7, 3.1, 3.2 and 3.3.

If you are interested I can work on a prototype and send a PR for you to evaluate. I have read CONTRIBUTING.md and I think I should be able to meet the criteria.

Regards,

John.

[jreback]

gr8!

could certainly be added as an optional deps see http://pandas.pydata.org/pandas-docs/dev/install.html#optional-dependencies; and could try in preference (if installed) over other writer libraries, then fallback to others (if installed)

you may need to rework the infrastructure a bit to do this,

e.g. at the top of the file various imports should be tried (see io/html.py for how this is done).

then the writers should take a list of writers? to try in sequence (else will finally raise if nothing is installed)

lmk if you need help

[jreback]

pls hook up travis as well; you will also need to add your library to ci/requirements-??? for testing (you can add to all if you want), with a version specified (if you have multiple versions then you can do that too)

[jreback]

as a side issue; people have been thinking about how to pass formatting info to excel via these writers; either via some kind of dict or a Style type of object (generally per column, though could in theory be per cell), any thoughts on this?

[jmcnamara]

you may need to rework the infrastructure a bit to do this,

I'll try to make the changes as noninvasive as possible.

pls hook up travis as well; you will also need to add your library to ci/requirements-???

Will do. XlsxWriter already uses Travis so it shouldn't be too painful. I normally test locally with a range of Python 2/3 versions as well.

John

[jreback]

gr8!

[jtratner]

@jmcnamara how's this going? Definitely interested in being able to hook up other writers to the core...Does ExcelWriter have the same API as openpyxl for saving excel files?

[jmcnamara]

@jtratner, @jreback

how's this going?

I made some initial progress getting XlsxWriter integrated and working, the code is on the enh_xlsxwriter_dev branch of my fork. For example the following now works using XlsxWriter:

    from pandas import ExcelWriter
    from pandas import DataFrame

    l1 = [1,2,3,4]
    l2 = [1,2,3,4]
    df1 = DataFrame({'Stimulus Time': l1, 'Reaction Time': l2})

    writer = ExcelWriter('path_to_file2.xlsx', writer_options={'use_xlsxwriter': True})
    df1.to_excel(writer, sheet_name='Sheet1', index=False)
    writer.save()

Dropping the optional writer_options parameter defaults back to OpenPyXL with similar results.

Definitely interested in being able to hook up other writers to the core

I think it should be reasonably easy to plug in other writers as well. The required API changes would be minor and backward compatible: basically the additional dict of options for the underlying Excel writer (shown above).

I'll try to get it to a more complete state in the next week, with tests, so you review it properly and let me know if you think it will fit in with the current code.

Does ExcelWriter have the same API as openpyxl for saving excel files?

It's API and scope is closer to Xlwt, i.e., it only creates new files and doesn't it doesn't rewrite files (although that could be simulated via the existing Pandas Excel reading functionality). In terms of functionality and features XlsxWriter goes beyond Xlwt or OpenPyXL and opens the possibility for adding charts, images, autofilters and conditional formatting as Pandas Excel writer options.

However, I need to do a bit more integration work first. :-)

It should also be about 10x faster than OpenPyXL for large files. However, to get that performance the cells would have to be written in row order. Currently in format.py they are handled in column order via chain:

    def get_formatted_cells(self):
        for cell in itertools.chain(self._format_header(), self._format_body()
                                    ):
            cell.val = self._format_value(cell.val)
            yield cell

Would it be acceptable to convert this, and associated header handling functions, to row order via izip_longest() or some similar iterator?

Without it XlsxWriter should still be 5x faster than OpenPyXL but would consume a similar (large) amount of memory for large files. There in some background on this, if you are interested, in the Working with Memory and Performance section of the XlsxsWriter docs.

Anyway, I'll try push some more complete code for you and the others to look at in the next week.

John.

[jreback]

@jmcnamara I would like to see an API more like this

df.to_excel(path_or_buf, sheet_name, engine='openpyxl|xlsxwriter'...., .....)
additional options can be passed directly to the engine if needed (as kwargs)

and can default the engine (and possible use get_option('io.excel.default_engine=....')

the last bit is actually easy to set up see: https://github.com/pydata/pandas/blob/master/pandas/io/pytables.py#L170

[jtratner]

@jmcnamara sounds good - it's definitely not a problem to go in row order vs. column order - I wouldn't know (without looking back at the history of the code) whether that was an explicit decision or not anyways. 5x speedup is absolutely non-trivial, so making a change to enable that is definitely a good thing.

[jmcnamara]

@jreback

I changed the API to engine=, added a default via the config handler and wrote some tests. The initial code is on my fork/branch.

In terms of the config it will probably need two items since the user may not be using the same engine for reading and writing. How about something like:

get_option('io.excel.writer_engine=....')
get_option('io.excel.reader_engine=....')

John

[jreback]

@jmcnamara pls make sure to hook up Travis
and you will need to update the ci/requirements file to make sure that the xlsxwriter is downloaded

[jtratner]

@jmcnamara one thing that would be nice would be to, instead of hard coding the engine, just create the format like: '_init_%s' % engine and then check whether the engine exists on the class (so it's simple to bind new methods to the class and hook into the writer). The init method should take the path, so it could raise an error about getting an .xls file. (in fact, you could call like _init_blah(path, **engine_kwargs)). I think it shouldn't require too much effort to run it that way (basically just extending the setup you already have).

You could do something like:

class _UnsupportedFileTypeError(Exception):
    pass

# __init__ method for ExcelWriter/

def __init__(self, path, engine=None, **engine_kwargs):

    if engine is None:
        engine = config.get_option('io.excel.engine')

    try:
        method = getattr(self, "_init_%s" % engine)
    except AttributeError:
        raise ValueError("No engine: %s" % engine)
    try:
        method(path, **engine_kwargs)
    except _UnsupportedFileTypeError:
        self._init_standard(path, **engine_kwargs)

and then the individual engines handle the rest of the setup. (like setting up the workbook, etc.). If this seems too complicated, then we can always do it later after you've added in xlsxwriter and worked through the various issues with adding a new engine.

[jmcnamara]

@jreback

make sure to hook up Travis and you will need to update the ci/requirements file to make sure that the xlsxwriter is downloaded

Already done in both cases. :-)

[jreback]

@jmcnamara did you send this as a PR? (or its just on your branch atm?)

[jmcnamara]

@jreback

I haven't sent a PR. I am a few days work away from that as yet.