Repository: pandas-dev/pandas
Pull request: ENH: Add check for character limmit
- [x] closes #56954  (Replace xxxx with the GitHub issue number)
- [x] [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
- [ ] All [code checks passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#pre-commit).
- [ ] Added [type annotations](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#type-hints) to new arguments/methods/functions.
- [ ] Added an entry in the latest `doc/source/whatsnew/vX.X.X.rst` file if fixing a bug or adding a new feature.


Problem statement:
ENH: show warning when exporting dataframe and hitting excel truncating limit
### Feature Type

- [X] Adding new functionality to pandas

- [ ] Changing existing functionality in pandas

- [ ] Removing existing functionality in pandas


### Problem Description

I wish I can see a warning when I save a pandas dataframe to excel, and one of the columns contain super long strings, and these strings are truncated, possibly due to a limit on the Excel side. 

### Feature Description

detect string length and Show a warning when the some contents of a cell exceed Excel length/size limit, to let the users know that the data won't be saved fully if choose to export to excel. (csv is probably better)

### Alternative Solutions

show warning all the time or add this to pd.to_excel documentation

### Additional Context

_No response_

Hints from issue discussion:
Hi @SophieGarden , could you let us know the length of strings you're dealing with? I've personally not yet encountered an issue of strings being erased in a .xlsx file (_not_ to say it can't happen), and I've dealt with exporting some pretty long strings in converting to .xlsx. Is it possible that you're running into the [visibility limit ](https://excel.tips.net/T003163_Character_Limits_for_Cells.html) for Excel, rather than the character limit?

This might be helpful to add into the pd.to_excel() documentation if this is what you're encountering. I expect others will run into the same, and if we can divert the questions to the Excel side this would probably be the best approach.
Seems like it is 32767 characters. See https://support.microsoft.com/en-au/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3. 

Reproducer:

```py
import pandas as pd
from io import BytesIO
df = pd.DataFrame({'col1': ['a'*35000, 'b']})
fobj = BytesIO()
df.to_excel(fobj, index=False)
fobj.seek(0)
df2 = pd.read_excel(fobj, engine='openpyxl')
assert len(df2['col1'].iloc[0]) == 35000 # raises (returns 32767 instead)
```
I think it makes sense to check the number of rows and columns (if we don't do so already). I would be opposed to checking every limitation @asishm linked to, and it does seem to me that the number of characters in a string is too detailed. That said, it appears there is a decent place for such a check in `ExcelWriter._value_with_fmt`, so I'd be okay with adding one there.

I think it would be good to add something like

> pandas will check the number of rows, columns, and cell character count does not exceed Excel's limitations. All other limitations must be checked by the user.

to the documentation
take

Commit message: Fix test ingnore extra warings

Patch to review:
diff --git a/pandas/core/generic.py b/pandas/core/generic.py
index 91a4271509421..a49597d1a0a3c 100644
--- a/pandas/core/generic.py
+++ b/pandas/core/generic.py
@@ -2363,6 +2363,10 @@ def to_excel(
         Once a workbook has been saved it is not possible to write further
         data without rewriting the whole workbook.
 
+        Pandas will check the number of rows, columns,
+        and cell character count does not exceed Excel's limitations.
+        All other limitations must be checked by the user.
+
         Examples
         --------
 
diff --git a/pandas/io/excel/_base.py b/pandas/io/excel/_base.py
index c7642236d4b2a..d3b228855d8f8 100644
--- a/pandas/io/excel/_base.py
+++ b/pandas/io/excel/_base.py
@@ -1326,6 +1326,13 @@ def _value_with_fmt(
             fmt = "0"
         else:
             val = str(val)
+            # GH#56954
+            if len(val) > 32767:
+                warnings.warn(
+                    "String value too long, truncated to 32767 characters",
+                    UserWarning,
+                    stacklevel=find_stack_level(),
+                )
 
         return val, fmt
 
diff --git a/pandas/tests/io/excel/test_writers.py b/pandas/tests/io/excel/test_writers.py
index 6aea7464ee8dc..4609f3946eb73 100644
--- a/pandas/tests/io/excel/test_writers.py
+++ b/pandas/tests/io/excel/test_writers.py
@@ -1385,6 +1385,17 @@ def test_to_excel_empty_frame(self, engine, ext):
             expected = DataFrame()
             tm.assert_frame_equal(result, expected)
 
+    def test_to_excel_raising_warning_when_cell_character_exceed_limit(
+        self, path, engine
+    ):
+        # GH#56954
+        df = DataFrame({"A": ["a" * 32768]})
+        msg = "String value too long, truncated to 32767 characters"
+        with tm.assert_produces_warning(
+            UserWarning, match=msg, raise_on_extra_warnings=False
+        ):
+            df.to_excel(path, engine=engine)
+
 
 class TestExcelWriterEngineTests:
     @pytest.mark.parametrize(


Code review comments:
diff_hunk: @@ -1326,6 +1326,13 @@ def _value_with_fmt(
             fmt = "0"
         else:
             val = str(val)
+            # GH#56954
+            if len(val) > 32767:
+                warnings.warn(
path: pandas/io/excel/_base.py
text: @user1:
The issue mentioned warning, but we currently raise an error if the data has too many rows/columns. I think we should raise here too - I don't believe users would be okay with the data loss in certain cases.

diff_hunk: @@ -1326,6 +1326,13 @@ def _value_with_fmt(
             fmt = "0"
         else:
             val = str(val)
+            # GH#56954
+            if len(val) > 32767:
+                warnings.warn(
+                    "String value too long, truncated to 32767 characters",
path: pandas/io/excel/_base.py
text: @user1:
This can potentially come from other data types, "String" may end up being confusing if that's the case. I think a more generic "Cell contents too long" is better. 

@author:
I have a quick question regarding this: should we also explore validating other data types, like float or int? The current code seems to focus solely on validating the length for specific data types. Do we need to extend these checks to other types mentioned above? Alternatively, have we conducted similar checks elsewhere, or is there a best practice that should be adhered to in this context?

@user1:
> should we also explore validating other data types, like float or int?

I think Excel's limitation is specifically for strings.

> Alternatively, have we conducted similar checks elsewhere, or is there a best practice that should be adhered to in this context?

When you say elsewhere, are you thinking of outside the excel code? I do not know of any formal best practice.


@author:
I believe I grasp your perspective. I want to ensure we deliberate on whether we should also account for the length of other data types. For now, let's concentrate on checking the length specifically for strings in this pull request; that should suffice.