Repository: ToucanToco/fastexcel (language: python)

Issue:
29.02 cell cast to 29.020000000000003 string
## Steps to reproduce

See the Excel file [decimal-numbers.xlsx](https://github.com/user-attachments/files/17162995/decimal-numbers.xlsx). It contains a column with two decimals:

| Decimals |
|--------|
| 28.14 |
| 29.02 | 

Read the Excel file using fastexcel:

```python
read_excel("decimal-numbers.xlsx").load_sheet_by_name("Sheet1").to_polars()
```
```
shape: (2, 1)
┌──────────┐
│ Decimals │
│ ---      │
│ f64      │
╞══════════╡
│ 28.14    │
│ 29.02    │
└──────────┘
```

Looks fine.

Then read the Excel file while casting to strings:

```python
read_excel("decimal-numbers.xlsx").load_sheet_by_name("Sheet1", dtypes={0: 'string'}).to_polars()
```
```
shape: (2, 1)
┌────────────────────┐
│ Decimals           │
│ ---                │
│ str                │
╞════════════════════╡
│ 28.14              │
│ 29.020000000000003 │
└────────────────────┘
```

The expected result was that the strings would be the same as shown to the user in the Excel file:

```
shape: (2, 1)
┌──────────┐
│ Decimals │
│ ---      │
│ str      │
╞══════════╡
│ 28.14    │
│ 29.02    │
└──────────┘
```

# Wrap-up

I understand this looks like an issue due to floating point precision, and I'm not sure if this:

1. could be fixed in fastexcel
2. could be fixed in the underlying calamine.
3. cannot be fixed at all, since it's a fundamental property of the Excel file format or parsing process.

What's the motivation for filing this bug: In our system, we have highly heterogeneous data, so we have to read all Excel values as strings. However, if users see 29.02 in their Excel files, but `29.020000000000003` in our system, that's highly confusing and surprising to users.

What do you think?


Discussion / hints:
Hello @severinh 
Always great to have a clear bug issue with file and clear explanation thank you!
I checked quickly and indeed it feels hard to fix on `fastexcel` side

In `src/data.rs`, in `create_string_array` function we simply do
```rs
cell.as_string()
```

Even with
```rs
cell.get_float().map(|v| v.to_string())
```
or 
```rs
cell.as_f64()
```
we get the same behavior with this floating precision issue and AFAIK there is no way for us to get more info on the original input.
I'll try to dig on calamine side tonight

EDIT: quick check on calamine
```rs
    inner: [
        SharedString(
            "Decimals",
        ),
        Float(
            28.14,
        ),
        Float(
            29.020000000000003,
        ),
    ],
```
when reading the xml content we get
```rs
BytesText { content: Borrowed("29.020000000000003") }
```
so I guess it goes even further directly in the xml content 😞 
The floating point number in the Excel file is 29.020000000000003:


```xml
    <row r="2" spans="1:1" x14ac:dyDescent="0.2">
      <c r="A2" s="1">
        <v>28.14</v>
      </c>
    </row>
    <row r="3" spans="1:1" x14ac:dyDescent="0.2">
      <c r="A3" s="1">
        <v>29.020000000000003</v>
      </c>
    </row>
```

For the display (and storage) of floating point numbers Excel uses something very similar to the `printf()` format option `%.16G`:

```python
$ python
>>> print("%.16G" % 29.020000000000003)
29.02
```

That format option isn't supported by Rust in [std::fmt](https://doc.rust-lang.org/std/fmt/) however.

Fix patch:
diff --git a/src/data.rs b/src/data.rs
index 553d10d..8da9ada 100644
--- a/src/data.rs
+++ b/src/data.rs
@@ -81,6 +81,8 @@ mod array_impls {
     use calamine::{CellType, DataType, Range};
     use chrono::NaiveDate;
 
+    use crate::types::dtype::excel_float_to_string;
+
     pub(crate) fn create_boolean_array<DT: CellType + DataType>(
         data: &Range<DT>,
         col: usize,
@@ -142,6 +144,8 @@ mod array_impls {
                     cell.get_datetime_iso().map(str::to_string)
                 } else if cell.is_bool() {
                     cell.get_bool().map(|v| v.to_string())
+                } else if cell.is_float() {
+                    cell.get_float().map(excel_float_to_string)
                 } else {
                     cell.as_string()
                 }
diff --git a/src/types/dtype.rs b/src/types/dtype.rs
index 9af844a..32b3dd9 100644
--- a/src/types/dtype.rs
+++ b/src/types/dtype.rs
@@ -280,6 +280,31 @@ pub(crate) fn get_dtype_for_column<DT: CellType + Debug + DataType>(
     }
 }
 
+/// Convert a float to a nice string to mimic Excel behaviour.
+///
+/// Excel can store a float like 29.02 set by the user as "29.020000000000003" in the XML.
+/// But in fact, the user will see "29.02" in the cell.
+/// Excel indeed displays decimal numbers with 8 digits in a standard cell width
+/// and 10 digits in a wide cell. Like this:
+///
+/// Format = 0.000000000 |  Unformatted, wide cell  | Unformatted, standard width
+/// ---------------------|--------------------------|----------------------------
+///     1.123456789      |        1.123456789       |           1.123457
+///    12.123456789      |        12.12345679       |           12.12346
+///         ...          |            ...           |              ...
+///   123456.123456789   |        123456.1235       |           123456.1
+///
+/// Excel also trims trailing zeros and the decimal point if there is no fractional part.
+///
+/// We do not distinguish between wide cells and standard cells here, so we retain at most
+/// nine digits after the decimal point and trim any trailing zeros.
+pub(crate) fn excel_float_to_string(x: f64) -> String {
+    format!("{x:.9}")
+        .trim_end_matches('0')
+        .trim_end_matches('.')
+        .to_string()
+}
+
 #[cfg(test)]
 mod tests {
     use calamine::{Cell, Data as CalData};
@@ -394,4 +419,12 @@ mod tests {
             FastExcelErrorKind::UnsupportedColumnTypeCombination(_)
         ));
     }
+
+    #[rstest]
+    #[case(29.020000000000003, "29.02")]
+    #[case(10000_f64, "10000")]
+    #[case(23.0, "23")]
+    fn test_excel_float_to_string(#[case] x: f64, #[case] expected: &str) {
+        assert_eq!(excel_float_to_string(x), expected.to_string());
+    }
 }


Test patch:
diff --git a/python/tests/fixtures/decimal-numbers.xlsx b/python/tests/fixtures/decimal-numbers.xlsx
new file mode 100644
index 0000000..dc9fabd
Binary files /dev/null and b/python/tests/fixtures/decimal-numbers.xlsx differ
diff --git a/python/tests/test_fastexcel.py b/python/tests/test_fastexcel.py
index 555ae1c..2ab280a 100644
--- a/python/tests/test_fastexcel.py
+++ b/python/tests/test_fastexcel.py
@@ -536,3 +536,19 @@ def test_null_values_in_cells() -> None:
 def test_null_column_is_nullable() -> None:
     sheet = fastexcel.read_excel(path_for_fixture("null-column.xlsx")).load_sheet(0)
     assert sheet.to_arrow().schema.field("nullonly").nullable is True
+
+
+def test_sheet_with_decimal_numbers() -> None:
+    sheet = fastexcel.read_excel(path_for_fixture("decimal-numbers.xlsx")).load_sheet(0)
+    pl_assert_frame_equal(
+        sheet.to_polars(),
+        pl.DataFrame({"Decimals": [28.14, 29.02]}),
+    )
+
+    sheet2 = fastexcel.read_excel(path_for_fixture("decimal-numbers.xlsx")).load_sheet(
+        0, dtypes={0: "string"}
+    )
+    pl_assert_frame_equal(
+        sheet2.to_polars(),
+        pl.DataFrame({"Decimals": ["28.14", "29.02"]}),
+    )