Repository: ToucanToco/fastexcel (language: python)

Issue:
Cannot handle special symbols
Hi team,
I am facing one issue on reading excel sheet through Polars. It says **calamine cell error: #VALUE!**
The sheet in interest does not get read through standard api.

How to handle such special symbols through fastexcel?

Thank you.

I am using fastexcel==0.10.2.



Discussion / hints:
Hi @durgeksh could you please provide the entire stack trace ? and maybe a file allowing to reproduce the issue ? thanks!
```py
Traceback (most recent call last):
  File "/Users/neo/Desktop/workspace/pocs/polarsdemo.py", line 84, in <module>
    df = pl.read_excel("sample_data.xlsx", engine='calamine')
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/neo/Desktop/workspace/pocs/.venv/lib/python3.11/site-packages/polars/_utils/deprecation.py", line 134, in wrapper
    return function(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/neo/Desktop/workspace/pocs/.venv/lib/python3.11/site-packages/polars/_utils/deprecation.py", line 134, in wrapper
    return function(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/neo/Desktop/workspace/pocs/.venv/lib/python3.11/site-packages/polars/io/spreadsheet/functions.py", line 253, in read_excel
    return _read_spreadsheet(
           ^^^^^^^^^^^^^^^^^^
  File "/Users/neo/Desktop/workspace/pocs/.venv/lib/python3.11/site-packages/polars/io/spreadsheet/functions.py", line 475, in _read_spreadsheet
    parsed_sheets = {
                    ^
  File "/Users/neo/Desktop/workspace/pocs/.venv/lib/python3.11/site-packages/polars/io/spreadsheet/functions.py", line 476, in <dictcomp>
    name: reader_fn(
          ^^^^^^^^^^
  File "/Users/neo/Desktop/workspace/pocs/.venv/lib/python3.11/site-packages/polars/io/spreadsheet/functions.py", line 821, in _read_spreadsheet_calamine
    ws = parser.load_sheet_by_name(sheet_name, **read_options)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/neo/Desktop/workspace/pocs/.venv/lib/python3.11/site-packages/fastexcel/__init__.py", line 184, in load_sheet_by_name
    self._reader.load_sheet(
_fastexcel.CalamineCellError: calamine cell error: #VALUE!
Context:
    0: could not determine dtype for column Amount


Process finished with exit code 1
```

Sample file: 
[sample_data.xlsx](https://github.com/ToucanToco/fastexcel/files/14880107/sample_data.xlsx)

Fix patch:
diff --git a/src/types/dtype.rs b/src/types/dtype.rs
index 90e7576..fb544ba 100644
--- a/src/types/dtype.rs
+++ b/src/types/dtype.rs
@@ -136,7 +136,7 @@ fn get_cell_dtype(data: &Range<CalData>, row: usize, col: usize) -> FastExcelRes
         CalData::DurationIso(_) => Ok(DType::Duration),
         // Errors and nulls
         CalData::Error(err) => match err {
-            CellErrorType::NA => Ok(DType::Null),
+            CellErrorType::NA | CellErrorType::Value | CellErrorType::Null => Ok(DType::Null),
             _ => Err(FastExcelErrorKind::CalamineCellError(err.to_owned()).into()),
         },
         CalData::Empty => Ok(DType::Null),


Test patch:
diff --git a/python/tests/fixtures/fixture-invalid-cell-value.xlsx b/python/tests/fixtures/fixture-invalid-cell-value.xlsx
new file mode 100644
index 0000000..1f013db
Binary files /dev/null and b/python/tests/fixtures/fixture-invalid-cell-value.xlsx differ
diff --git a/python/tests/test_fastexcel.py b/python/tests/test_fastexcel.py
index 4c1a9a1..69660a9 100644
--- a/python/tests/test_fastexcel.py
+++ b/python/tests/test_fastexcel.py
@@ -548,3 +548,21 @@ def test_null_strings(excel_file: str):
         pl.col("TIMESTAMPS_AND_NULLS").dt.cast_time_unit("ms"),
     )
     pl_assert_frame_equal(sheet.to_polars(), pl_df)
+
+
+def test_null_values_in_cells() -> None:
+    excel_reader = fastexcel.read_excel(path_for_fixture("fixture-invalid-cell-value.xlsx"))
+    sheet = excel_reader.load_sheet(0)
+
+    expected = {
+        "Title": ["A", "B", "C", "D"],
+        "Date": [None, None, datetime(2021, 1, 1), datetime(2021, 5, 5)],
+    }
+
+    pl_assert_frame_equal(
+        sheet.to_polars(),
+        pl.DataFrame(expected).with_columns(pl.col("Date").dt.cast_time_unit("ms")),
+    )
+    pd_expected = pd.DataFrame(expected)
+    pd_expected["Date"] = pd_expected["Date"].dt.as_unit("ms")
+    pd_assert_frame_equal(sheet.to_pandas(), pd_expected)