ENH: Added ability to freeze panes from DataFrame.to_excel() (#15160)
State: closed

 - [X] closes #15160
 - [x] tests passed- Existing tests passed. New test added and passed.
 - [X] passes ``git diff upstream/master | flake8 --diff``
 - [x] whatsnew entry- Added to 0.20.

The ability to freeze panes has been added to the to_excel() method on data frames. The parameter is a tuple of 2 integers, representing the bottom-most row and right-most column to freeze, respectively. (This is the same format xlsxwriter uses). The default value is None, resulting in no freezing.

Example usage: 
``df.to_excel(writer, sheet_name='Sheet1', freeze_panes=(1,1))``

The following engines are supported:
xlsxwriter
xlwt
openpyxl22

The parameter exists for openpyxl20 and openpyxl1 but it has no effect, in anticipation of support for these versions being dropped (#15184).

Can one of the maintainers confirm this approach for engine support is adequate, or suggest an alternative? Once that is settled I can write a test to create a workbook using each engine and then try reading it back in using openpyxl.


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

Comments:
# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/15291?src=pr&el=h1) Report
> Merging [#15291](https://codecov.io/gh/pandas-dev/pandas/pull/15291?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/d6f8b460325fd79faa90858e2743878a7cc74dec?src=pr&el=desc) will **increase** coverage by `<.01%`.
> The diff coverage is `95%`.


```diff
@@            Coverage Diff             @@
##           master   #15291      +/-   ##
==========================================
+ Coverage   90.36%   90.37%   +<.01%     
==========================================
  Files         135      135              
  Lines       49438    49452      +14     
==========================================
+ Hits        44675    44690      +15     
+ Misses       4763     4762       -1
```


| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/15291?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/core/generic.py](https://codecov.io/gh/pandas-dev/pandas/compare/d6f8b460325fd79faa90858e2743878a7cc74dec...cef8fce3bf65e18a6813c8847e2de2e09221a54b?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZ2VuZXJpYy5weQ==) | `96.33% <ø> (ø)` | :white_check_mark: |
| [pandas/io/excel.py](https://codecov.io/gh/pandas-dev/pandas/compare/d6f8b460325fd79faa90858e2743878a7cc74dec...cef8fce3bf65e18a6813c8847e2de2e09221a54b?src=pr&el=tree#diff-cGFuZGFzL2lvL2V4Y2VsLnB5) | `79.64% <100%> (+0.24%)` | :white_check_mark: |
| [pandas/core/frame.py](https://codecov.io/gh/pandas-dev/pandas/compare/d6f8b460325fd79faa90858e2743878a7cc74dec...cef8fce3bf65e18a6813c8847e2de2e09221a54b?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZnJhbWUucHk=) | `97.82% <83.33%> (-0.05%)` | :x: |
| [pandas/util/testing.py](https://codecov.io/gh/pandas-dev/pandas/compare/d6f8b460325fd79faa90858e2743878a7cc74dec...cef8fce3bf65e18a6813c8847e2de2e09221a54b?src=pr&el=tree#diff-cGFuZGFzL3V0aWwvdGVzdGluZy5weQ==) | `81.97% <ø> (+0.18%)` | :white_check_mark: |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/15291?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/15291?src=pr&el=footer). Last update [d6f8b46...cef8fce](https://codecov.io/gh/pandas-dev/pandas/compare/d6f8b460325fd79faa90858e2743878a7cc74dec...cef8fce3bf65e18a6813c8847e2de2e09221a54b?el=footer&src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).
seems reasonable to me. yeah I wouldn't worry about supporting older version of openpyxl.

not sure how to test this (except for a smoke test I think).
pls add a whatsnew note, update docs in io.rst (maybe put a small section on 'Style'), IOW things that you can do. (we don't really support much readl styling yet (other issues about that), though things like float_format could be included).
@jreback @chris-b1 Requested changes made, please review. 

Also, I can look at supporting autofilter as well, as was mentioned in the original issue. Shall I open a separate issue for that? Sounds cleaner to me.
@jeffcarey that's fine to open a new issue.

One thing to thing about is do we just keep adding kwargs to ``.to_excel``? or a better API?

@chris-b1 
can you add a smoke test to write & then read back a simple frame.
@jreback - not sure on the API, for now probably easiest as a kwarg.

I guess could see having some kind of template / styler like api?

```
df.to_excel('tmp.xlsx') # default, template=ExcelTemplate

my_template =  pd.ExcelTemplate.freeze_panes().autofilter()

df.to_excel('tmp2.xlsx', template=my_template)
```
         

@chris-b1 yeah, I guess if only a couple of other options then kwargs is the way to go.
minor comments, some flake issues (use ``git diff master | flake8 --diff`` to see).

lgtm. ping on green.
@jreback Checks are green. Do you want me to fix the conflict that came up in the whatsnew or is that something you can do?
@jorisvandenbossche comments?

@jeffcarey can fix it on merge.
can you rebase
@jeffcarey can you rebase and push one more time. sorry had asome issues with the ci.
thanks!

keem em coming!
@jeffcarey I just realized that https://github.com/pandas-dev/pandas/blame/master/pandas/core/frame.py#L1441 was defined in ``core/frame.py``. This should *only* be in ``io/excel.py``. We want to make code ``core/frame.py`` as simple as possible, and this is completely extraneous.

Can you do a PR to remove (and simply put it in ``.write_cells``, where I think it is already validated anyhow).