Repository: ToucanToco/fastexcel (language: python)

Issue:
When coercing columns to strings, boolean cells turn into null
In our project, we have Excel files with mixed data types. Because of this, we need to coerce all columns to strings, and interpret the data downstream. We're currently blocked from adopting `fastexcel` because it does not coerce booleans as expected.

## How to reproduce

Suppose you have an Excel file with the following data:

| Header |
|--------|
| `=TRUE` |
| `=FALSE` |
| `"some string"` | 

Now lets read this Excel file into a Polars dataframe, while coercing the column to strings:
`excel_reader.load_sheet(0, header_row=1, dtypes={"Header": "string"}).to_polars()`

This produces the following data frame:

| Header |
|--------|
| `null` |
| `null` |
| `"some string"` | 

## Expected behavior

What I would have expected fastexcel to coerce the boolean values to `"0"` and `"1"` instead of losing them. That is:

| Header |
|--------|
| `"1"` |
| `"0"` |
| `"some string"` | 

## Test case

I've cloned the fastexcel repo and wrote a new unit test for this, which currently fails.

Excel sheet: [sheet-bool.xlsx](https://github.com/user-attachments/files/16260997/sheet-bool.xlsx)

```python
def test_bool_casting_to_string_for_polars() -> None:
    excel_reader = fastexcel.read_excel(path_for_fixture("sheet-bool.xlsx"))

    actual_polars_df = excel_reader.load_sheet(
        0, header_row=None, dtypes={0: "string"}, column_names=["0"]
    ).to_polars()
    expected_polars_df = pl.DataFrame(
        {
            "0": ["1", "0", "some string"],
        }
    )

    pl_assert_frame_equal(actual_polars_df, expected_polars_df)
```

Unfortunately, I'm not experienced with Rust, so I did not yet figure out where/how to make the change in the Rust code to make the test pass.

## Closing words

First of all, big thanks for building `fastexcel`! I'm eager to migrate to fastexcel due to its ability to directly output Polars, which our downstream pipelines are based on, which will give us a massive performance boost.

It would be much appreciated if you either gave me some pointers on where/how to make this change in Rust code (so I can open a PR), or made the fix yourself.


Discussion / hints:
Hi @severinh and thanks for your kind words
I'm on my phone right now so hard to help more but I would start with [this PR](https://github.com/ToucanToco/fastexcel/pull/245)
You probably want to update `STRING_TYPES_CELL` to support booleans
If that doesn't work we can make a quick fix soon. I'm planning on working on fastexcel this weekend and release a new version v0.11.0 soon, mostly for @lukapeschke amazing work on https://github.com/ToucanToco/fastexcel/pull/147
Btw @lukapeschke and myself tend to prefer the `"true"` / `"false"` representation (the classic `to_string()` for a boolean)
Would that be ok for you @severinh ?
I'll let you work on it if you want. If you prefer I can open a fix at lunch. You tell me
@PrettyWood Thanks for the quick response!.

> Btw @lukapeschke and myself tend to prefer the "true" / "false" representation (the classic to_string() for a boolean)
Would that be ok for you @severinh ?

Sure! Makes sense to be more explicit. The main thing I care about is that we don't lose legitimate data.

> You probably want to update STRING_TYPES_CELL to support booleans

Tried that, and unfortunately that did not seem to be enough yet. The output was still `null`. Since I won't have time this afternoon to look further into it, I would appreciate you going ahead and making the change.

PS: Another problem we ran into: A cell like `=DATE(2024, 7, 17)` is coerced into `'2024-07-01 00:00:00'`. I would have expected `'2024-07-01'` for dates. For now, we're trimming `' 00:00:00'` in post-processing, which is not ideal. Is this behavior expected and intentional? If not, I can file a separate ticket for this.

Fix patch:
diff --git a/src/types/dtype.rs b/src/types/dtype.rs
index e264949..9af844a 100644
--- a/src/types/dtype.rs
+++ b/src/types/dtype.rs
@@ -224,6 +224,7 @@ fn int_types() -> &'static HashSet<DType> {
 fn string_types() -> &'static HashSet<DType> {
     STRING_TYPES_CELL.get_or_init(|| {
         HashSet::from([
+            DType::Bool,
             DType::Int,
             DType::Float,
             DType::String,
diff --git a/src/types/python/excelsheet/sheet_data.rs b/src/types/python/excelsheet/sheet_data.rs
index 5a4b337..88b68d2 100644
--- a/src/types/python/excelsheet/sheet_data.rs
+++ b/src/types/python/excelsheet/sheet_data.rs
@@ -134,6 +134,8 @@ mod array_impls {
                         .map(|dt| dt.to_string())
                 } else if cell.is_datetime_iso() {
                     cell.get_datetime_iso().map(str::to_string)
+                } else if cell.is_bool() {
+                    cell.get_bool().map(|v| v.to_string())
                 } else {
                     cell.as_string()
                 }


Test patch:
diff --git a/python/tests/fixtures/fixture-multi-dtypes-columns.xlsx b/python/tests/fixtures/fixture-multi-dtypes-columns.xlsx
index 4e7211d..384af6d 100644
Binary files a/python/tests/fixtures/fixture-multi-dtypes-columns.xlsx and b/python/tests/fixtures/fixture-multi-dtypes-columns.xlsx differ
diff --git a/python/tests/test_dtypes.py b/python/tests/test_dtypes.py
index 7dc974f..f5c01b5 100644
--- a/python/tests/test_dtypes.py
+++ b/python/tests/test_dtypes.py
@@ -42,6 +42,7 @@ def expected_data() -> dict[str, list[Any]]:
         "Details": ["Healthcare"] * 7 + ["Something"] * 2,
         "Asset ID": ["84444"] * 7 + ["ABC123"] * 2,
         "Mixed dates": ["2023-07-21 00:00:00"] * 6 + ["July 23rd"] * 3,
+        "Mixed bools": ["true"] * 5 + ["false"] * 3 + ["other"],
     }
 
 
@@ -92,6 +93,7 @@ def test_sheet_with_mixed_dtypes_and_sample_rows(expected_data: dict[str, list[A
     ]
     expected_data["Asset ID"] = [84444.0] * 7 + [None] * 2
     expected_data["Mixed dates"] = [datetime(2023, 7, 21)] * 6 + [None] * 3
+    expected_data["Mixed bools"] = [True] * 5 + [False] * 3 + [None]
 
     pd_df = sheet.to_pandas()
     pd_assert_frame_equal(