ENH: Excel output in non-ascii encodings
State: closed

ENH/TST: Support for non-ascii encodings in DataFrame.to_excel

Closes #3710.

Notice: Despite (for my modest knowledge of python) it should be easier to just put encoding='ascii' in the declaration of to_excel in line 1352 of frame.py, I've declared it with encoding=None as jreback suggested, and then making it default to ascii later with the if clause.


Labels:
name: Enhancement
name: Unicode
description: Unicode strings
name: IO Excel
description: read_excel, to_excel

Comments:
@jtornero please replace all tabs with 4 spaces. Otherwise we won't be able to incorporate this.

Well, I've replaced the tabs by spaces and commited again. I got some complains from Travis IC, but I can't interpretate them very well, specially those about python2. . What should I do now? A new pull request?

Thank you very much

Jorge Tornero

you don't need a new pull request, let me take a look at Travis.

It's an issue with a new excel engine we added. Going to need the expert on this. @jmcnamara - how do you handle non-ascii encoding in xlsxwriter?

Xlsxwriter handles utf8 at the very least. I'll have have a look at the issue.

The xlsxwriter issue isn't with the encoding, it handles that correctly and passes the test. The issue is that the constructor doesn't handle arbitrary keywords like the new `encoding` keyword.

I should have spotted that and added some mapping of user keywords to keywords supported by xlsxwriter (an earlier version of my patch had that I think). 

Anyway, for now @jtornero can patch `excel.py` as follows to remove keyword handling from XlsxWriter  while I work on a cleaner solution.

```
$ git diff
diff --git a/pandas/io/excel.py b/pandas/io/excel.py
index 2c82261..8b49a40 100644
--- a/pandas/io/excel.py
+++ b/pandas/io/excel.py
@@ -668,7 +668,7 @@ class _XlsxWriter(ExcelWriter):

         super(_XlsxWriter, self).__init__(path, **engine_kwargs)

-        self.book = xlsxwriter.Workbook(path, **engine_kwargs)
+        self.book = xlsxwriter.Workbook(path)

     def save(self):
         """

```

 @jreback 

This patch is only required for `xlwt` since `openpyxl`, `xlsxwriter` and `pyexcelerate` (I'm getting around to it) don't need it for `utf8` and don't handle other encodings anyway (afaik).

So it seems like overkill to add a new keyword just for this and opening the flood gates for any other future patches that want to add configuration to specific engines.

So, instead  perhaps the new parameter should be something like `engine_kwargs` or even just `**kwargs` as a generic interface to passing options into the various engines. The engines can then have their own logic for handling or ignoring options.

Or perhaps that is the way it already works and I've missed it. I've only had a chance to have a quick look.

@jmcnamara okay, that's fine. Yes, the clear answer is to not pass encoding to other engines.

Easiest way to handle this is to add to the constructor: `encoding=None` and not pass it forwards for engines that don't need it.  Simple.  Plus add a warning that non-utf8 encodings aren't supported.

@jmcnamara @jtornero To be clear, to fix this all you need to do is change the `__init__` method of each ExcelWriter to the following (I agree we should just change it to `kwargs`):

```
def __init__(self, io, encoding=None, **kwargs):
```

I'm not convinced we need to validate kwargs or anything like that (yet).

One additional note - before this PR, you could pass encoding to xlwt without issue right? So this is really just a testing thing.  The other easy way to do this would be to have the _xlwt_ engine set a default for encoding, and then set read_excel to do:

``` python
if encoding:
    kwargs['encoding'] = encoding
```

And that way it only gets passed to the constructor if necessary. I might prefer this second option because it means that constructors don't need to deal with extra keyword arguments.

@jtornero: The Travis test that failed is being run under Python3.2. That version of Python does not accept the `u'...'` syntax for strs (or what's called unicode in Python2). The `u'...'` syntax was [added back in Python3](http://docs.python.org/dev/whatsnew/3.3.html), which is why the last Travis test passed.

To fix, add

```
from pandas.compat import u
```

to the top of `test_excel.py`, and replace `u'...'` with `u('...')`.

Guess it's all done

okay I'll take  look in the next few days. No major changes to Excel so should be fine.

@jtratner what's the state of this?

Well I did the PR and just waiting.

Please rebase this on current master and then we can make sure everything passes

pls rebase on master and can see where this is

@jtornero needs a rebase

@jreback @jtratner I'm so sorry but I am absolutely lost about this. I guess I have to clone my forked repo again, then rebase (don't know the steps clearly)? And then? I'm so sorry this is disappointing but my git skills are unfortunately too low!!

I rebased you to this commit: pls incorporate this and go from here

https://github.com/jreback/pandas/commit/147866070e00774ef12486a623cbe9e3e9199694

@jreback can you take a look at https://gist.github.com/jtornero/1324e77425715cdfe987

Best regards,

Jorge Tornero

@jtornero just post here what you need

https://github.com/pydata/pandas/wiki/Using-Git

@jreback Well... what it is supposed that I have to expect and what it is supposed from me to do? I mean: when I rebase, I should run the tests again? or just make a new PR? I will clone my forked repo again, because the "original" one (where I did the work that origined my PR) is not available anymore for me. Does it matter?

in this case just confirm that the version I out up looks ok 
if so I can just directly incorporate it

if not
then create a new branch
pull this commit in
then make changes and submit a new pr

So steps are

1) clone my forked repo
2) Because it is already rebased (you did it yesterday), run the tests
3) It tests are ok, post here

If not:
1) create branch
2) pull this commit in???
3) make whatever changes are neccessary to pass the tests and in that case, make PR

you don't need to fork again, just create a new branch

like this:

```
git checkout -b new_excel_branch upstream/master
git pull https://github.com/jreback/pandas.git excel_encoding
```

then you can work with the new branch

I am suggesting this as sort of an exercise as the fix is fine, but this is nice to know how to do

Well, I've cloned and installed and updated some libraries and run the tests. Some errors arise related to google and timeseries (see the nose output at https://gist.github.com/jtornero/9496276)

It is ok then?

hmm those are a bit odd
what version of numpy do u have?

@ jreback Well I my first attempts complainted with lots of messages sort of  "compiled against version 9 but your numpy version y 7" so I installed 1.8.0 with pip, so currently I'm using 1.8.0. I'll try to compile it from source and see what happens