Repository: ToucanToco/fastexcel (language: python)

Issue:
Fastexcel fails to load an excel file with polars
```python
dfs = pl.read_excel('input.xlsx', engine='calamine', sheet_id=0)
for key in dfs.keys():
    print(dfs[key].head())
```

Error:
```
  Traceback (most recent call last):
  File "/Users/Desktop/workspace/pocs/demo.py", line 27, in <module>
    dfs = pl.read_excel('compass_input.xlsx', engine='calamine', sheet_id=0)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/Desktop/workspace/pocs/.venv/lib/python3.11/site-packages/polars/utils/deprecation.py", line 136, in wrapper
    return function(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/Desktop/workspace/pocs/.venv/lib/python3.11/site-packages/polars/utils/deprecation.py", line 136, in wrapper
    return function(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/Desktop/workspace/pocs/.venv/lib/python3.11/site-packages/polars/io/spreadsheet/functions.py", line 259, in read_excel
    return _read_spreadsheet(
           ^^^^^^^^^^^^^^^^^^
  File "/Users/Desktop/workspace/pocs/.venv/lib/python3.11/site-packages/polars/io/spreadsheet/functions.py", line 487, in _read_spreadsheet
    parsed_sheets = {
                    ^
  File "/Users/Desktop/workspace/pocs/.venv/lib/python3.11/site-packages/polars/io/spreadsheet/functions.py", line 488, in <dictcomp>
    name: reader_fn(
          ^^^^^^^^^^
  File "/Users/Desktop/workspace/pocs/.venv/lib/python3.11/site-packages/polars/io/spreadsheet/functions.py", line 834, in _read_spreadsheet_calamine
    df = ws.to_polars()
         ^^^^^^^^^^^^^^
  File "/Users/Desktop/workspace/pocs/.venv/lib/python3.11/site-packages/fastexcel/__init__.py", line 64, in to_polars
    df = pl.from_arrow(data=self.to_arrow())
                            ^^^^^^^^^^^^^^^
  File "/Users/Desktop/workspace/pocs/.venv/lib/python3.11/site-packages/fastexcel/__init__.py", line 47, in to_arrow
    return self._sheet.to_arrow()
           ^^^^^^^^^^^^^^^^^^^^^^
RuntimeError: Could not create RecordBatch from sheet Hybrid_Productivity

Caused by:
    0: Could not build schema for sheet Hybrid_Productivity
    1: Error in calamine cell: NA
```

Installed versions:
```
--------Version info---------
Polars:               0.20.7
Index type:           UInt32
Platform:             macOS-12.7.3-arm64-arm-64bit
Python:               3.11.2 (v3.11.2:878ead1ac1, Feb  7 2023, 10:02:41) [Clang 13.0.0 (clang-1300.0.29.30)]

----Optional dependencies----
adbc_driver_manager:  <not installed>
cloudpickle:          <not installed>
connectorx:           <not installed>
deltalake:            <not installed>
fsspec:               <not installed>
gevent:               <not installed>
hvplot:               <not installed>
matplotlib:           3.8.2
numpy:                1.26.4
openpyxl:             <not installed>
pandas:               2.2.0
pyarrow:              15.0.0
pydantic:             <not installed>
pyiceberg:            <not installed>
pyxlsb:               <not installed>
sqlalchemy:           <not installed>
xlsx2csv:             <not installed>
xlsxwriter:           <not installed>
None

Process finished with exit code 0
```


Discussion / hints:
Hello, could you please provide an excel file allowing to reproduce the bug ? Without this, it will be hard to investigate the issue
Hi @lukapeschke, I found that if a cell contains the value "#N/A" then it is failing to load the data. I am attaching a sample below.
Thank you.
[sample_data.xlsx](https://github.com/ToucanToco/fastexcel/files/14277136/sample_data.xlsx)

```python
df = pl.read_excel('sample_data.xlsx', engine='calamine')
```


Fix patch:
diff --git a/src/types/excelsheet.rs b/src/types/excelsheet.rs
index e7ec2eb..bb55775 100644
--- a/src/types/excelsheet.rs
+++ b/src/types/excelsheet.rs
@@ -246,17 +246,17 @@ fn create_duration_array(
 impl TryFrom<&ExcelSheet> for Schema {
     type Error = anyhow::Error;
 
-    fn try_from(value: &ExcelSheet) -> Result<Self, Self::Error> {
+    fn try_from(sheet: &ExcelSheet) -> Result<Self, Self::Error> {
         // Checking how many rows we want to use to determine the dtype for a column. If sample_rows is
         // not provided, we sample limit rows, i.e on the entire column
-        let sample_rows = value.offset() + value.schema_sample_rows().unwrap_or(value.limit());
+        let sample_rows = sheet.offset() + sheet.schema_sample_rows().unwrap_or(sheet.limit());
 
         arrow_schema_from_column_names_and_range(
-            value.data(),
-            &value.column_names(),
-            value.offset(),
+            sheet.data(),
+            &sheet.column_names(),
+            sheet.offset(),
             // If sample_rows is higher than the sheet's limit, use the limit instead
-            std::cmp::min(sample_rows, value.limit()),
+            std::cmp::min(sample_rows, sheet.limit()),
         )
     }
 }
@@ -264,11 +264,11 @@ impl TryFrom<&ExcelSheet> for Schema {
 impl TryFrom<&ExcelSheet> for RecordBatch {
     type Error = anyhow::Error;
 
-    fn try_from(value: &ExcelSheet) -> Result<Self, Self::Error> {
-        let offset = value.offset();
-        let limit = value.limit();
-        let schema = Schema::try_from(value)
-            .with_context(|| format!("Could not build schema for sheet {}", value.name))?;
+    fn try_from(sheet: &ExcelSheet) -> Result<Self, Self::Error> {
+        let offset = sheet.offset();
+        let limit = sheet.limit();
+        let schema = Schema::try_from(sheet)
+            .with_context(|| format!("Could not build schema for sheet {}", sheet.name))?;
         let mut iter = schema
             .fields()
             .iter()
@@ -278,25 +278,25 @@ impl TryFrom<&ExcelSheet> for RecordBatch {
                     field.name(),
                     match field.data_type() {
                         ArrowDataType::Boolean => {
-                            create_boolean_array(value.data(), col_idx, offset, limit)
+                            create_boolean_array(sheet.data(), col_idx, offset, limit)
                         }
                         ArrowDataType::Int64 => {
-                            create_int_array(value.data(), col_idx, offset, limit)
+                            create_int_array(sheet.data(), col_idx, offset, limit)
                         }
                         ArrowDataType::Float64 => {
-                            create_float_array(value.data(), col_idx, offset, limit)
+                            create_float_array(sheet.data(), col_idx, offset, limit)
                         }
                         ArrowDataType::Utf8 => {
-                            create_string_array(value.data(), col_idx, offset, limit)
+                            create_string_array(sheet.data(), col_idx, offset, limit)
                         }
                         ArrowDataType::Timestamp(TimeUnit::Millisecond, None) => {
-                            create_datetime_array(value.data(), col_idx, offset, limit)
+                            create_datetime_array(sheet.data(), col_idx, offset, limit)
                         }
                         ArrowDataType::Date32 => {
-                            create_date_array(value.data(), col_idx, offset, limit)
+                            create_date_array(sheet.data(), col_idx, offset, limit)
                         }
                         ArrowDataType::Duration(TimeUnit::Millisecond) => {
-                            create_duration_array(value.data(), col_idx, offset, limit)
+                            create_duration_array(sheet.data(), col_idx, offset, limit)
                         }
                         ArrowDataType::Null => Arc::new(NullArray::new(limit - offset)),
                         _ => unreachable!(),
@@ -309,7 +309,7 @@ impl TryFrom<&ExcelSheet> for RecordBatch {
             Ok(RecordBatch::new_empty(Arc::new(schema)))
         } else {
             RecordBatch::try_from_iter(iter)
-                .with_context(|| format!("Could not convert sheet {} to RecordBatch", value.name))
+                .with_context(|| format!("Could not convert sheet {} to RecordBatch", sheet.name))
         }
     }
 }
diff --git a/src/utils/arrow.rs b/src/utils/arrow.rs
index 12b2df9..3523e03 100644
--- a/src/utils/arrow.rs
+++ b/src/utils/arrow.rs
@@ -2,7 +2,7 @@ use std::{collections::HashSet, sync::OnceLock};
 
 use anyhow::{anyhow, Context, Result};
 use arrow::datatypes::{DataType as ArrowDataType, Field, Schema, TimeUnit};
-use calamine::{Data as CalData, DataType, Range};
+use calamine::{CellErrorType, Data as CalData, DataType, Range};
 
 fn get_cell_type(data: &Range<CalData>, row: usize, col: usize) -> Result<ArrowDataType> {
     let cell = data
@@ -31,7 +31,10 @@ fn get_cell_type(data: &Range<CalData>, row: usize, col: usize) -> Result<ArrowD
         // A simple duration
         CalData::DurationIso(_) => Ok(ArrowDataType::Duration(TimeUnit::Millisecond)),
         // Errors and nulls
-        CalData::Error(err) => Err(anyhow!("Error in calamine cell: {err:?}")),
+        CalData::Error(err) => match err {
+            CellErrorType::NA => Ok(ArrowDataType::Null),
+            _ => Err(anyhow!("Error in calamine cell: {err:?}")),
+        },
         CalData::Empty => Ok(ArrowDataType::Null),
     }
 }


Test patch:
diff --git a/python/tests/fixtures/sheet-with-na.xlsx b/python/tests/fixtures/sheet-with-na.xlsx
new file mode 100644
index 0000000..e098fc9
Binary files /dev/null and b/python/tests/fixtures/sheet-with-na.xlsx differ
diff --git a/python/tests/test_fastexcel.py b/python/tests/test_fastexcel.py
index a97e5e8..d033394 100644
--- a/python/tests/test_fastexcel.py
+++ b/python/tests/test_fastexcel.py
@@ -431,3 +431,20 @@ def test_sheet_with_pagination_out_of_bound():
             pl.col("Amazing").str.strptime(pl.Datetime, "%F %T").dt.cast_time_unit("ms")
         ),
     )
+
+
+def test_sheet_with_na():
+    """Test reading a sheet with #N/A cells. For now, we consider them as null"""
+    excel_reader = fastexcel.read_excel(path_for_fixture("sheet-with-na.xlsx"))
+    sheet = excel_reader.load_sheet(0)
+
+    assert sheet.name == "Sheet1"
+    assert sheet.height == sheet.total_height == 2
+    assert sheet.width == 2
+
+    expected = {
+        "Title": ["A", "B"],
+        "Amount": [None, 100.0],
+    }
+    pd_assert_frame_equal(sheet.to_pandas(), pd.DataFrame(expected))
+    pl_assert_frame_equal(sheet.to_polars(), pl.DataFrame(expected))