BUG: Inconsistent Boolean Cell format in Output Spreadsheet

[Kruegerrr]

Pandas version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import pandas as pd

df = pd.DataFrame({'Boolean': [True]*64})
# Data type is 'bool'. Good!
print(df['Boolean'].dtype)

# Export the DataFrame in write mode
with pd.ExcelWriter('./test.xlsx', engine='openpyxl', mode='w') as writer:
    df.to_excel(writer, sheet_name='Sheet1', index=False)

# Export the DataFrame in append mode
with pd.ExcelWriter('./test.xlsx', engine='openpyxl', mode='a', if_sheet_exists='replace') as writer:
    df.to_excel(writer, sheet_name='Sheet2', index=False)

Issue Description

An Excel workbook test.xlsx with two sheets should be created. The format of all cells should be 'boolean value'. However, the cell format for cells after row 33 in 'Sheet1' is 'general number',

and the cell format for cells after row 32 in 'Sheet2' is 'general number'.

Expected Behavior

If the data type of a column of pandas dataframe is 'bool', the associated column in the output file should consistently be 'boolean value', regardless of the number of rows.

Installed Versions

INSTALLED VERSIONS

commit : a60ad39
python : 3.11.6.final.0
python-bits : 64
OS : Linux
OS-release : 6.5.9-arch2-1
Version : #1 SMP PREEMPT_DYNAMIC Thu, 26 Oct 2023 00:52:20 +0000
machine : x86_64
processor :
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 2.1.2
numpy : 1.26.0
pytz : 2023.3.post1
dateutil : 2.8.2
setuptools : 68.2.2
pip : 23.3.1
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : 4.9.3
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : 3.1.2
IPython : 8.16.1
pandas_datareader : None
bs4 : 4.12.2
bottleneck : None
dataframe-api-compat: None
fastparquet : None
fsspec : None
gcsfs : None
matplotlib : 3.8.0
numba : None
numexpr : None
odfpy : None
openpyxl : 3.1.2
pandas_gbq : None
pyarrow : None
pyreadstat : None
pyxlsb : None
s3fs : None
scipy : None
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : 2.0.1
zstandard : 0.21.0
tzdata : 2023.3
qtpy : None
pyqt5 : None

[rhshadrach]

Thanks for the report! I can replicate this just using openpyxl:

from openpyxl import Workbook

book = Workbook()
wks = book.create_sheet()
startrow, startcol = 0, 0
for cell in range(30):
    xcell = wks.cell(
        row=startrow + cell + 1, column=startcol + 1
    )
    xcell.value = True
book.save("test_direct.xlsx")

For me this produces a 2nd sheet where the first few entries are True and the rest are 1. I recommend reporting this issue to openpyxl.

[Kruegerrr]

@rhshadrach issue opened for openpyxl.

[rhshadrach]

Thanks @Kruegerrr. As there is nothing pandas can do here, closing as an upstream issue.