ENH: Added xlsxwriter as an ExcelWriter option.
State: closed

Added xlsxwriter as an optional writer engine. closes #4542.


Labels:

Comments:
@jmcnamara please tell me when you'd like me to look this over. Definitely can leave settings to a separate PR as you requested.

I've lost the thread here a little bit. Are there some issue here that I need to fix?

need a release notes entry (doc/source/releast.rst), reference the issue, maybe in improvements?

possibly add a pros/cons of different writers in docs (or in docstring)?

@jmcnamara I think I was just waiting for you to tell me that you were finished. Your call if you want to add comparisons, it's also fine to just link back to xlsxwriter docs and let people find out for themselves.

I think this looks good - unless anyone else has objections to it, I'm going to merge tonight. Thank you @jmcnamara for bearing through this and being flexible about accommodating a refactor of the Excel writer engine while you were working on this :)

Sorry for the laggy response. I'll make the requested additions today and send a message when they are good to merge. Thanks for your help and patience.

@jtratner @jreback 

I made the changes above and I think that this is okay to merge.

If there are any other fixes required I send some more PRs before the 0.13 release. From here I would like to do the following:
- Once merged, post an update to a question about the Excel writer speed on the PyData mailing list to see if anyone is interested in trying out xlsxwriter.
- Add a similar sub-class for PyExcelerate and maybe do a speed comparison.
- Look at tackling the row/column refactoring.

-- John

@jmcnamara can you squash those last 2 commits?

otherwise look ok to me, @jtratner 

@jmcnamara maybe want to do show some perf numbers at the top of this PR?
(at some point can add a vbench if you'd like as well)

it would be great to come up with performance metrics. That said, memory could be an even better gain...I've found relatively small dataframes (maybe 100K cells) taking 4-6GB just to write.

this is a nice little ipython feature: `%memit`, like timeit,  https://gist.github.com/vene/3022718

This looks fine to me - definitely worth announcing to the PyData mailing list (and clearly it's great if you have metrics along with it). I'd be +1 on PyExcelerate too, that seems like an interesting alternative project.

is this tested/an issue with xlsxwriter? https://github.com/pydata/pandas/issues/3122

oh good point!

yep - xlsxwriter handles it fine. We can add those tests separately. Easy test case would be something like this like this:

``` python
def test_non_standard_dtypes(self):
      df = DataFrame(range(5))
      dtypes = ['float16', 'int16', 'int32', 'float32', 'uint64', 'uint16', 'uint32']
      with ensure_clean('_some_out.' + self.ext) as pth:
          writer = ExcelWriter(pth)
          for dtype in dtypes:
               df.astype(dtype).to_excel(writer, dtype)
          writer.save()
          reader = ExcelFile(pth)
          for dtype in dtypes:
              np.testing.assert_array_equal(reader.parse(dtype).values, df.astype(dtype).values)
```

Which xlsxwriter passes right now.

However, once those two commits are squashed, I want to merge this PR...that way it's in here and easier to reason about other changes to io/excel.

> can you squash those last 2 commits?

@jreback I can do a `git rebase -i HEAD~2` or `HEAD~3` to squash the commits but the push will be rejected without a `--force` or a `git pull` with merge. Will I do the force or is there a better/cleaner way to squash the commits?

@jmcnamara yes, git push --force is what you want.

The goal is to rewrite the history to be cleaner (which is why you have to force the rewrite). Since it's is a topic branch, so it's okay to rebase like this.

I'm always slightly reticent to use `--force` but the cleaner history is definitely better. It is done now.

@jmcnamara Thanks! Building on this, it would be great to look at memory and performance for xlsxwriter as well as the row-oriented vs. column-oriented discussion (like you mentioned).

Btw - I'm planning to resolve #3122 by moving the formatting on to the `ExcelWriter` class, so that `openpyxl`/`xlwt` can convert themselves first to deal with the dtypes comparison, so I think it should be pretty easy to handle that change in that way.