ENH: Excel writer takes locale setting for date and datetime into account
State: closed

closes #4133

Based on the locale setting the date and datetime values are formatted in
excel exports. Moreover the this behaviour can be overruled by keyword
arguments. Old behavior can be enforced by
ExcelWriter(file, date_format='YYYY-MM-DD', datetime_format='YYYY-MM-DD HH:MM:SS')


Labels:

Comments:
@jtratner ?

I have officially added it to my todo list - I'll take a look soon - but
@jaydyou we can't change the default behavior without a good reason (i.e.
previous behavior was wrong).

this is okay, but I don't want to merge yet...I'm not sure how the defaults work with the writers and I'd rather match them. Allowing users to specify date format is a good thing though (but I'd imagine there must be engine-specific ways to do that?)

@jmcnamara - do you have thoughts on this?

A couple of comments on this:
1. It is a good idea to allow user configuration of the datetime and date formats. This is also the subject of issue #4133.
2. This isn't the best way to get locale specific date formats using Excel.

Excel allows a limited number of date formats which adjust automatically to regional settings. For example in the number format dialog in Excel you will see a message like this:

> Displays date and time serial numbers as date values, according to the type and locale (location) that you specify. Date formats that begin with an asterisk (*) respond to changes in regional date and time settings that are specified in Control Panel. Formats without an asterisk are not affected by Control Panel settings.

Xlsxwriter allows the regional date formats to be set. I'm not sure about `xlwt` and `openpyxl`. I'll look into that to see if there is a consistent format that can be used across all the Excel writer engines.

I would suggest that the localisation part of this PR is dropped and that the keyword `date_format`, `datetime_format` parts are kept to close #4133. That way if the user wants to set a custom date format to match their locale, then they can.

I have made a version following the suggestion of jmcnamara. You can set date_format and datetime_format arguments, but if you don't the behavior does not change in contrast to previous versions. 

@Jaydyou that looks good.

You should also update the release note, the `to_excel()` docstring and add a test case.

Have a look at some of the other Pandas PRs to see what is involved. For example [this](https://github.com/pydata/pandas/pull/5237/files).

@jmcnamara I hope it fulfils all requirements now?

@Jaydyou Apart from the couple of minor issues above that looks good. I'll pull down your fork and do some local testing.

There is a typo in the new testcase name so the test doesn't get run. Also, `datetime` isn't imported in the test suite, just `datetime.datetime`. So the test should be something like this:

``` python

    # At the start add date to the datetime import:
    from datetime import datetime, date

   ...

    def test_excel_date_datetime_format(self):
        df = DataFrame([[date(2014, 1, 31), 
                         date(1999, 9, 24)],
                        [datetime(1998, 5, 26, 23, 33, 4),
                         datetime(2014, 2, 28, 13, 5, 13)]],
                       index=['DATE', 'DATETIME'], columns=['X', 'Y'])

   ...
```

However, after that the tests fail. Try running the new test on its own:

```
py.test pandas/io/tests/test_excel.py -v -k datetime_format
```

I have improved my test setup and double checked if everything is working as expected now. Please try the latest version.

@Jaydyou can you rebase, remove the tracking branch commit https://github.com/Jaydyou/pandas/commit/d0963b2cc062f9ace51681e3f90a7b2b0300365f
and squash to a smaller num of commits?

thanks

The travis fail was spurious, restarted and now green.

does this close #4133? or is it just the comments near the end?

@jreback Yes. This would close #4133 as well.

@Jaydyou I think we need to test this on all engines to see if it works

@jmcnamara right?

> I think we need to test this on all engines to see if it works

@jreback The way the excel.py tests are structured now each test gets run with each engine.

@jmcnamara ok great

@Jaydyou just need those small doc updates and can get this in

merged via https://github.com/pydata/pandas/commit/19ae1dca9af9ca922b14ad2ec010f4b849c98f77

thanks!

I think this also closes #976.