[WIP] Excel table output
State: closed

- [x] closes #24862
- [ ] tests added / passed
- [ ] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [ ] whatsnew entry

This PR should allow writing excel table objects. Proof of concept implemented for XlsxWriter and OpenPyXL. Exactly which options of the `to_excel` api should be supported is up for discussion, see #24862.
 
```python
import pandas as pd
data = {'B' : dict(col1=1), 'A' : dict(col1=2), 'C' :dict(col1=3, col2=4.1)}
df = pd.DataFrame.from_dict(data, orient='index')

df.to_excel('test1.xlsx', engine='xlsxwriter', as_table=True, index=True)
df.to_excel('test2.xlsx', engine='openpyxl', as_table=True, index=False)
```



Labels:
name: IO Excel
description: read_excel, to_excel

Comments:
@WillAyd, can you offer some guidance on how to test this functionality? I presume there is no suitable way to involve MS Excel in the process, what should be tested? If the function runs without errors? If the data can be loaded from the excel file? As far as I know there is not a simple way to load an excel table in python referenced by the e.g. tablename.

Sure - check pandas/tests/io/data you will see Excel files created which explicitly show the intended layout. The corresponding writing test will compare a generated file against what was expected. 

Sent from my iPhone

> On Jan 24, 2019, at 5:28 AM, Thijs Damsma <notifications@github.com> wrote:
> 
> @WillAyd, can you offer some guidance on how to test this functionality? I presume there is no suitable way to involve MS Excel in the process, what should be tested? If the function runs without errors? If the data can be loaded from the excel file? As far as I know there is not a simple way to load an excel table in python referenced by the e.g. tablename.
> 
> —
> You are receiving this because you were mentioned.
> Reply to this email directly, view it on GitHub, or mute the thread.

# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/24899?src=pr&el=h1) Report
> Merging [#24899](https://codecov.io/gh/pandas-dev/pandas/pull/24899?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/2b16e2e6c5a298396727fc2e66a60edf1eb13bf9?src=pr&el=desc) will **decrease** coverage by `49.49%`.
> The diff coverage is `12.5%`.

[![Impacted file tree graph](https://codecov.io/gh/pandas-dev/pandas/pull/24899/graphs/tree.svg?width=650&token=eZ4WkYLtcO&height=150&src=pr)](https://codecov.io/gh/pandas-dev/pandas/pull/24899?src=pr&el=tree)

```diff
@@            Coverage Diff             @@
##           master   #24899      +/-   ##
==========================================
- Coverage   92.38%   42.88%   -49.5%     
==========================================
  Files         166      166              
  Lines       52398    52418      +20     
==========================================
- Hits        48406    22480   -25926     
- Misses       3992    29938   +25946
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `?` | |
| #single | `42.88% <12.5%> (-0.01%)` | :arrow_down: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/24899?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/core/generic.py](https://codecov.io/gh/pandas-dev/pandas/pull/24899/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZ2VuZXJpYy5weQ==) | `39.86% <ø> (-56.77%)` | :arrow_down: |
| [pandas/io/formats/excel.py](https://codecov.io/gh/pandas-dev/pandas/pull/24899/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2Zvcm1hdHMvZXhjZWwucHk=) | `14.69% <12.5%> (-82.71%)` | :arrow_down: |
| [pandas/io/formats/latex.py](https://codecov.io/gh/pandas-dev/pandas/pull/24899/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2Zvcm1hdHMvbGF0ZXgucHk=) | `0% <0%> (-100%)` | :arrow_down: |
| [pandas/core/categorical.py](https://codecov.io/gh/pandas-dev/pandas/pull/24899/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvY2F0ZWdvcmljYWwucHk=) | `0% <0%> (-100%)` | :arrow_down: |
| [pandas/io/sas/sas\_constants.py](https://codecov.io/gh/pandas-dev/pandas/pull/24899/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL3Nhcy9zYXNfY29uc3RhbnRzLnB5) | `0% <0%> (-100%)` | :arrow_down: |
| [pandas/tseries/plotting.py](https://codecov.io/gh/pandas-dev/pandas/pull/24899/diff?src=pr&el=tree#diff-cGFuZGFzL3RzZXJpZXMvcGxvdHRpbmcucHk=) | `0% <0%> (-100%)` | :arrow_down: |
| [pandas/tseries/converter.py](https://codecov.io/gh/pandas-dev/pandas/pull/24899/diff?src=pr&el=tree#diff-cGFuZGFzL3RzZXJpZXMvY29udmVydGVyLnB5) | `0% <0%> (-100%)` | :arrow_down: |
| [pandas/io/formats/html.py](https://codecov.io/gh/pandas-dev/pandas/pull/24899/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2Zvcm1hdHMvaHRtbC5weQ==) | `0% <0%> (-99.35%)` | :arrow_down: |
| [pandas/core/groupby/categorical.py](https://codecov.io/gh/pandas-dev/pandas/pull/24899/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZ3JvdXBieS9jYXRlZ29yaWNhbC5weQ==) | `0% <0%> (-95.46%)` | :arrow_down: |
| [pandas/io/sas/sas7bdat.py](https://codecov.io/gh/pandas-dev/pandas/pull/24899/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL3Nhcy9zYXM3YmRhdC5weQ==) | `0% <0%> (-91.17%)` | :arrow_down: |
| ... and [125 more](https://codecov.io/gh/pandas-dev/pandas/pull/24899/diff?src=pr&el=tree-more) | |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/24899?src=pr&el=continue).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/24899?src=pr&el=footer). Last update [2b16e2e...ca9c36e](https://codecov.io/gh/pandas-dev/pandas/pull/24899?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/24899?src=pr&el=h1) Report
> Merging [#24899](https://codecov.io/gh/pandas-dev/pandas/pull/24899?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/c64c9cb44222a42f7b02d4d6007919cd0645f1be?src=pr&el=desc) will **decrease** coverage by `0.44%`.
> The diff coverage is `87.5%`.

[![Impacted file tree graph](https://codecov.io/gh/pandas-dev/pandas/pull/24899/graphs/tree.svg?width=650&token=eZ4WkYLtcO&height=150&src=pr)](https://codecov.io/gh/pandas-dev/pandas/pull/24899?src=pr&el=tree)

```diff
@@            Coverage Diff             @@
##           master   #24899      +/-   ##
==========================================
- Coverage   92.83%   92.38%   -0.45%     
==========================================
  Files         182      166      -16     
  Lines       50430    52403    +1973     
==========================================
+ Hits        46815    48411    +1596     
- Misses       3615     3992     +377
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `90.8% <87.5%> (-0.7%)` | :arrow_down: |
| #single | `42.88% <12.5%> (+0.43%)` | :arrow_up: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/24899?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/core/generic.py](https://codecov.io/gh/pandas-dev/pandas/pull/24899/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZ2VuZXJpYy5weQ==) | `96.63% <ø> (+2.81%)` | :arrow_up: |
| [pandas/io/formats/excel.py](https://codecov.io/gh/pandas-dev/pandas/pull/24899/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2Zvcm1hdHMvZXhjZWwucHk=) | `97.12% <87.5%> (-0.34%)` | :arrow_down: |
| [pandas/io/gbq.py](https://codecov.io/gh/pandas-dev/pandas/pull/24899/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2dicS5weQ==) | `25% <0%> (-75%)` | :arrow_down: |
| [pandas/compat/\_\_init\_\_.py](https://codecov.io/gh/pandas-dev/pandas/pull/24899/diff?src=pr&el=tree#diff-cGFuZGFzL2NvbXBhdC9fX2luaXRfXy5weQ==) | `57.91% <0%> (-34.09%)` | :arrow_down: |
| [pandas/plotting/\_misc.py](https://codecov.io/gh/pandas-dev/pandas/pull/24899/diff?src=pr&el=tree#diff-cGFuZGFzL3Bsb3R0aW5nL19taXNjLnB5) | `38.68% <0%> (-26.18%)` | :arrow_down: |
| [pandas/io/common.py](https://codecov.io/gh/pandas-dev/pandas/pull/24899/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2NvbW1vbi5weQ==) | `72.86% <0%> (-21.19%)` | :arrow_down: |
| [pandas/io/gcs.py](https://codecov.io/gh/pandas-dev/pandas/pull/24899/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2djcy5weQ==) | `80% <0%> (-20%)` | :arrow_down: |
| [pandas/io/s3.py](https://codecov.io/gh/pandas-dev/pandas/pull/24899/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL3MzLnB5) | `86.36% <0%> (-13.64%)` | :arrow_down: |
| [pandas/core/computation/expr.py](https://codecov.io/gh/pandas-dev/pandas/pull/24899/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvY29tcHV0YXRpb24vZXhwci5weQ==) | `88.68% <0%> (-9.12%)` | :arrow_down: |
| [pandas/core/groupby/base.py](https://codecov.io/gh/pandas-dev/pandas/pull/24899/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZ3JvdXBieS9iYXNlLnB5) | `91.83% <0%> (-8.17%)` | :arrow_down: |
| ... and [180 more](https://codecov.io/gh/pandas-dev/pandas/pull/24899/diff?src=pr&el=tree-more) | |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/24899?src=pr&el=continue).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/24899?src=pr&el=footer). Last update [c64c9cb...d0a6391](https://codecov.io/gh/pandas-dev/pandas/pull/24899?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

Hello @tdamsma! Thanks for updating this PR. We checked the lines you've touched for [PEP 8](https://www.python.org/dev/peps/pep-0008) issues, and found:













There are currently no PEP 8 issues detected in this Pull Request. Cheers! :beers: 

##### Comment last updated at 2019-07-15 09:50:42 UTC
@WillAyd, I added a simple round-trip test. I tried to look for a example on how to check formatting, but the only test for formatting is completely commented out (`test_to_excel_header_styling_xls`). As far as I can tell the example sheets are only used in read tests, and not for comparison with write tests. I addressed your other comments, can you give some feedback if the general approach fits with pandas? I know there are still many tests to add, but I would like to know if I am on the right track for having this merged eventually. Thanks!
@tdamsma yea this might be tricky; not sure of best approach off the top of my head, but are there any testing facilities you know of with say openpyxl that would allow us to make stronger assertions about what is being written out? As is now there's just no way that the tests ensure this is actually working
@WillAyd, I wasn't aware of any options, but I just had a better look at openpyxl and this is indeed possible. In essence we need a function that can read an excel table into a dataframe, which would also be quite useful for pandas in general. 
@tdamsma given conversation in associated issue number and some of the other pre-cursors going to close this one for now to keep our queue minimal. If you plan on addressing soon let me know, otherwise can reopen as we get back around to this
I think this has gone a little stale. Our PR queue has grown quite large so closing for now to keep things clean but ping if you'd like to pick back up!