ENH: Add calamite engine to `read_excel`
State: closed

Adds excel reader from rust _calamite_ as an engine using library binding library _python-calamite_.
- [x] closes #50395 
- [ ] [Tests added and passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#writing-tests) if fixing a bug or adding a new feature
- [x] All [code checks passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#pre-commit).
- [x] Added [type annotations](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#type-hints) to new arguments/methods/functions.
- [x] Added an entry in the latest `doc/source/whatsnew/vX.X.X.rst` file if fixing a bug or adding a new feature.


Labels:
name: Enhancement
name: IO Excel
description: read_excel, to_excel
name: Dependencies
description: Required and optional dependencies
name: Stale

Comments:
Opened as a first stab. There are quite a few tests failing that we would need to investigate when adding the engine.
`python-calamite` is not even half-year old. There might be pushback.

`python-calamite` doesn't have a wheel for arm macOS. And the instance on CircleCI doesn't have Rust installed. You either need to get the author of `python-calamite` to produce a wheel for arm macOS or install the Rust toolchain on CircleCI.
> `python-calamite` is not even half-year old. There might be pushback.
> 
> 
> 
> `python-calamite` doesn't have a wheel for arm macOS. And the instance on CircleCI doesn't have Rust installed. You either need to get the author of `python-calamite` to produce a wheel for arm macOS or install the Rust toolchain on CircleCI.

That's a fair point. Can we have a discussion with the relevant people if we want to go ahead with this then?

Separately, I can open an issue to get a wheel for this library, if we want to go ahead with it. 
Yeah I think I would be -0.5 adding this new engine at the moment. As @fangchenli mentioned it appears relatively new package and doesn't seem to have a super active development community around the package yet
Yea I'm also hesitant to bring this in - both the python binding and the core library itself don't have that much development momentum. Something to watch as they have nice ideas, but I don't think they are at the point yet where pandas internally should change anything to interface with them
IMO, it's better to judge this by how many tests its able to pass. If it's able to pass most(say ~60-70%) of them for at least one of its formats, then it being new doesn't really matter.

Right now, we depend on two libraries, xlrd and odfpy, that are unmaintained, and pyxlsb seems semi-maintained,  so I would argue that the level of maintenance seems just about average in terms of the Excel parsers that we use.
(The only major concern I have is if python-calamine is unable to keep pace with calamine)

What does everyone else think?


Also cc @rhshadrach 

> IMO, it's better to judge this by how many tests its able to pass. If it's able to pass most(say ~60-70%) of them for at least one of its formats, then it being new doesn't really matter.

I agree. Let's see how far this PR could go.

One other thing: even if this is not something we want to merge right now, IMO better to get a working implementation that can always be maintained as a PR for the longer term than waiting until "the time is right" to actually implement.
Give the current state of development of calamite, if the standard features of pandas Excel reader are functional I would be okay adding this if it were tagged as experimental, maybe even going so far as to having the user specify it as `engine="calimite-experimental"`. I think this would communicate the risk involved to users.

> If it's able to pass most(say ~60-70%) of them for at least one of its formats, then it being new doesn't really matter.

This seems too low to me. Some exceptions for certain tests are okay; I'd approach on a case-by-case basis. But if users cannot reliably use an engine in its current state, then I would not add even as experimental.
Update: author has built a wheel for Mac OS arm 
Current tests: 168 failed, 354 passed (67%). I need some time to fix some fails. But for example I can't fix tests with date/datetime, because this is implemented in calamine only for xlsx (https://github.com/tafia/calamine/pull/198).
I think there are some other readers that can't read datetime and are excluded from the tests that include datetime. 

Perhaps we could do the same in this case. 
> Current tests: 168 failed, 354 passed (67%). I need some time to fix some fails. But for example I can't fix tests with date/datetime, because this is implemented in calamine only for xlsx ([tafia/calamine#198](https://github.com/tafia/calamine/pull/198)).

No worries let us know on your progress. I am happy to help out where necessary.
Hey, @dimastbk thank you for your updates. I merged them into my branch.

Tests are looking pretty good. There are only a handful more failing and a few mypy errors.

Would you be able to have a look at this? Happy to support if needed. Should be green ticks after that I hope.

Thanks. 
What are we thinking about the state of this ENH? @dimastbk has done some good work and it looks like we are at about 25 failing tests. Would be good to get some thoughts from the maintainers.
@kostyafarber hi! Can you merge https://github.com/kostyafarber/pandas/pull/4?
Failed Tests:
1. test_reader_special_dtypes[('.xlsx')] - Attributes of DataFrame.iloc[:, 5] (column name="DateCol") are different
2. test_reader_special_dtypes[('.xlsm')] - Attributes of DataFrame.iloc[:, 5] (column name="DateCol") are different
3. test_use_nullable_dtypes[('.ods')-True-pandas] - Ods error: Parse float error: invalid float literal
4. test_use_nullable_dtypes[('.ods')-True-pyarrow] - Ods error: Parse float error: invalid float literal
5. test_use_nullable_dtypes[('.ods')-False-pandas] - Ods error: Parse float error: invalid float literal
6. test_use_nullable_dtypes[('.ods')-False-pyarrow] - Ods error: Parse float error: invalid float literal
7. test_use_nullabla_dtypes_and_dtype[('.ods')] - Ods error: Parse float error: invalid float literal
8. test_use_nullable_dtypes_string[python-('.ods')] - Ods error: Parse float error: invalid float literal
9. test_use_nullable_dtypes_string[pyarrow-('.ods')] - Ods error: Parse float error: invalid float literal
10. test_reader_spaces[('.ods')] - DataFrame.iloc[:, 0] (column name="testcol") are different
11. test_date_conversion_overflow[('.xlsx')] - Attributes of DataFrame.iloc[:, 0] (column name="DateColWithBigInt") are different
12. test_date_conversion_overflow[('.xlsm')] - Attributes of DataFrame.iloc[:, 0] (column name="DateColWithBigInt") are different
13. test_reader_seconds[('.xlsx')] - DataFrame.iloc[:, 0] (column name="Time") are different
14. test_reader_seconds[('.xlsm')] - DataFrame.iloc[:, 0] (column name="Time") are different
15. test_read_excel_multiindex_header_only[('.ods')] - DataFrame are different
16. test_no_header_with_list_index_col[('.ods')] - DataFrame are different
17. test_multiheader_two_blank_lines[('.ods')] - DataFrame are different
18. test_excel_read_binary_via_read_excel[('.xls')] - Attributes of DataFrame.iloc[:, 0] (column name="Unnamed: 0") are different
19. test_excel_read_binary_via_read_excel[('.ods')] - Attributes of DataFrame.iloc[:, 0] (column name="Unnamed: 0") are different


Comments:
3-9. ODS: Non-number is not allowed in [office:value](http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part1.html#__RefHeading__1417678_253892949) by specification. In my opinion, it's correct, that calamine raised exception.
10. ODS: https://github.com/tafia/calamine/issues/225 and https://github.com/tafia/quick-xml/issues/461.
13-14. XLSX: Need to add a format with seconds.
15-17. ODS: Calamine doesn't support [number-rows-repeated](http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part1.html#__RefHeading__1418532_253892949).
18-19. XLS/ODS: Reading in binary mode with one engine (which support datetime), and default reading with calamine (which doesn't support datetime). It's strange, maybe bug in test?
> @kostyafarber hi! Can you merge [kostyafarber#4](https://github.com/kostyafarber/pandas/pull/4)?

done
This pull request is stale because it has been open for thirty days with no activity. Please [update](https://pandas.pydata.org/pandas-docs/stable/development/contributing.html#updating-your-pull-request) and respond to this comment if you're still interested in working on this.
Thanks for the PR but it appears to have gone stale. Additionally, I think with PDEP-9, I think this could be better supported by a separate library without being natively included in pandas so closing for now. https://github.com/pandas-dev/pandas/pull/51799