ENH: Optimize nrows in read_excel
State: closed

- [ ] closes #32727
- [ ] tests added / passed
- [ ] passes `black pandas`
- [ ] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [ ] whatsnew entry

based on #33281

----

output of asv benchmarks:

```
(pandas-dev) marco@marco-Predator-PH315-52:~/pandas-dev/asv_bench$ asv continuous -f 1.1 upstream/master optimise-nrows-excel -b excel.ReadExcel
· Creating environments..................................................................................................................................
· Discovering benchmarks
·· Uninstalling from conda-py3.8-Cython0.29.16-jinja2-matplotlib-numba-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt
·· Building d0a8a687 <optimise-nrows-excel> for conda-py3.8-Cython0.29.16-jinja2-matplotlib-numba-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt....................................
·· Installing d0a8a687 <optimise-nrows-excel> into conda-py3.8-Cython0.29.16-jinja2-matplotlib-numba-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt..
· Running 4 total benchmarks (2 commits * 1 environments * 2 benchmarks)
[  0.00%] · For pandas commit c413df6d <master> (round 1/2):
[  0.00%] ·· Building for conda-py3.8-Cython0.29.16-jinja2-matplotlib-numba-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt....................................
[  0.00%] ·· Benchmarking conda-py3.8-Cython0.29.16-jinja2-matplotlib-numba-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt
[ 12.50%] ··· Setting up io.excel:62                                                                                                                                                                               ok
[ 12.50%] ··· Running (io.excel.ReadExcel.time_read_excel--)..
[ 25.00%] · For pandas commit d0a8a687 <optimise-nrows-excel> (round 1/2):
[ 25.00%] ·· Building for conda-py3.8-Cython0.29.16-jinja2-matplotlib-numba-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt..
[ 25.00%] ·· Benchmarking conda-py3.8-Cython0.29.16-jinja2-matplotlib-numba-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt
[ 37.50%] ··· Setting up io.excel:62                                                                                                                                                                               ok
[ 37.50%] ··· Running (io.excel.ReadExcel.time_read_excel--)..
[ 50.00%] · For pandas commit d0a8a687 <optimise-nrows-excel> (round 2/2):
[ 50.00%] ·· Benchmarking conda-py3.8-Cython0.29.16-jinja2-matplotlib-numba-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt
[ 62.50%] ··· Setting up io.excel:62                                                                                                                                                                               ok
[ 62.50%] ··· io.excel.ReadExcel.time_read_excel                                                                                                                                                                   ok
[ 62.50%] ··· ========== ============
                engine               
              ---------- ------------
                 xlrd      953±6ms   
               openpyxl   1.66±0.03s 
                 odf      6.02±0.02s 
              ========== ============

[ 75.00%] ··· io.excel.ReadExcel.time_read_excel_nrows                                                                                                                                                             ok
[ 75.00%] ··· ========== ============
                engine               
              ---------- ------------
                 xlrd      878±20ms  
               openpyxl   1.67±0.02s 
                 odf      4.58±0.04s 
              ========== ============

[ 75.00%] · For pandas commit c413df6d <master> (round 2/2):
[ 75.00%] ·· Building for conda-py3.8-Cython0.29.16-jinja2-matplotlib-numba-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt..
[ 75.00%] ·· Benchmarking conda-py3.8-Cython0.29.16-jinja2-matplotlib-numba-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt
[ 87.50%] ··· Setting up io.excel:62                                                                                                                                                                               ok
[ 87.50%] ··· io.excel.ReadExcel.time_read_excel                                                                                                                                                                   ok
[ 87.50%] ··· ========== ============
                engine               
              ---------- ------------
                 xlrd      941±5ms   
               openpyxl   1.69±0.02s 
                 odf      6.15±0.04s 
              ========== ============

[100.00%] ··· io.excel.ReadExcel.time_read_excel_nrows                                                                                                                                                             ok
[100.00%] ··· ========== ============
                engine               
              ---------- ------------
                 xlrd      971±20ms  
               openpyxl   1.69±0.01s 
                 odf      6.07±0.03s 
              ========== ============

       before           after         ratio
     [c413df6d]       [d0a8a687]
     <master>         <optimise-nrows-excel>
-        971±20ms         878±20ms     0.90  io.excel.ReadExcel.time_read_excel_nrows('xlrd')

SOME BENCHMARKS HAVE CHANGED SIGNIFICANTLY.
PERFORMANCE INCREASED.
```

Labels:
name: Performance
description: Memory or execution speed performance
name: IO Excel
description: read_excel, to_excel

Comments:
Performance benchmarks:

```
(pandas-dev) marco@marco-Predator-PH315-52:~/pandas-dev/asv_bench$ asv continuous -f 1.1 upstream/master optimise-nrows-excel -b excel.ReadExcel
· Creating environments
· Discovering benchmarks
·· Uninstalling from conda-py3.8-Cython0.29.16-jinja2-matplotlib-numba-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt
·· Building 2d9ee8de <optimise-nrows-excel> for conda-py3.8-Cython0.29.16-jinja2-matplotlib-numba-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt.................................
·· Installing 2d9ee8de <optimise-nrows-excel> into conda-py3.8-Cython0.29.16-jinja2-matplotlib-numba-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt.
· Running 4 total benchmarks (2 commits * 1 environments * 2 benchmarks)
[  0.00%] · For pandas commit b53dc8f8 <optimise-nrows-excel^2> (round 1/2):
[  0.00%] ·· Building for conda-py3.8-Cython0.29.16-jinja2-matplotlib-numba-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt.....................................
[  0.00%] ·· Benchmarking conda-py3.8-Cython0.29.16-jinja2-matplotlib-numba-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt
[ 12.50%] ··· Setting up io.excel:62                                                                                                                                                                    ok
[ 12.50%] ··· Running (io.excel.ReadExcel.time_read_excel--).
[ 25.00%] ··· Running (io.excel.ReadExcel.time_read_excel_nrows--).
[ 25.00%] · For pandas commit 2d9ee8de <optimise-nrows-excel> (round 1/2):
[ 25.00%] ·· Building for conda-py3.8-Cython0.29.16-jinja2-matplotlib-numba-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt..
[ 25.00%] ·· Benchmarking conda-py3.8-Cython0.29.16-jinja2-matplotlib-numba-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt
[ 37.50%] ··· Setting up io.excel:62                                                                                                                                                                    ok
[ 37.50%] ··· Running (io.excel.ReadExcel.time_read_excel--).
[ 50.00%] ··· Running (io.excel.ReadExcel.time_read_excel_nrows--).
[ 50.00%] · For pandas commit 2d9ee8de <optimise-nrows-excel> (round 2/2):
[ 50.00%] ·· Benchmarking conda-py3.8-Cython0.29.16-jinja2-matplotlib-numba-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt
[ 62.50%] ··· Setting up io.excel:62                                                                                                                                                                    ok
[ 62.50%] ··· io.excel.ReadExcel.time_read_excel                                                                                                                                                        ok
[ 62.50%] ··· ========== ============
                engine               
              ---------- ------------
                 xlrd     1.01±0.02s 
               openpyxl   1.85±0.02s 
                 odf      6.04±0.03s 
              ========== ============

[ 75.00%] ··· io.excel.ReadExcel.time_read_excel_nrows                                                                                                                                                  ok
[ 75.00%] ··· ========== ============
                engine               
              ---------- ------------
                 xlrd      877±8ms   
               openpyxl   1.83±0.01s 
                 odf      4.57±0.04s 
              ========== ============

[ 75.00%] · For pandas commit b53dc8f8 <optimise-nrows-excel^2> (round 2/2):
[ 75.00%] ·· Building for conda-py3.8-Cython0.29.16-jinja2-matplotlib-numba-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt..
[ 75.00%] ·· Benchmarking conda-py3.8-Cython0.29.16-jinja2-matplotlib-numba-numexpr-numpy-odfpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt
[ 87.50%] ··· Setting up io.excel:62                                                                                                                                                                    ok
[ 87.50%] ··· io.excel.ReadExcel.time_read_excel                                                                                                                                                        ok
[ 87.50%] ··· ========== ============
                engine               
              ---------- ------------
                 xlrd      974±20ms  
               openpyxl   1.82±0.04s 
                 odf      6.21±0.1s  
              ========== ============

[100.00%] ··· io.excel.ReadExcel.time_read_excel_nrows                                                                                                                                                  ok
[100.00%] ··· ========== ============
                engine               
              ---------- ------------
                 xlrd      960±9ms   
               openpyxl    1.79±0s   
                 odf      5.87±0.01s 
              ========== ============

       before           after         ratio
     [b53dc8f8]       [2d9ee8de]
     <optimise-nrows-excel^2>       <optimise-nrows-excel>
-      5.87±0.01s       4.57±0.04s     0.78  io.excel.ReadExcel.time_read_excel_nrows('odf')

SOME BENCHMARKS HAVE CHANGED SIGNIFICANTLY.
PERFORMANCE INCREASED.
```

The OP mentioned a memory leak from skipping rows in openpyxl, which I experienced too, so I've skipped optimising those files for now
looks good, i thought this would make a much bigger difference, but ok.

cc @WillAyd 
Thanks @MarcoGorelli 
@MarcoGorelli we are reverting this as something is broken in the evaluation here. If you can resubmit when you can.
Sure - I'm really sorry for the breakage caused, but glad this was caught early!