When applied as style in to_excel(), 'vertical-align: middle' works for OpenPyXL, but not for XlsxWriter engine (original) (raw)

Code sample, a copy-pastable example if possible

import pandas as pd

def align(data): return pd.DataFrame('text-align: center', index=data.index, columns=data.columns)

def valign(data): return pd.DataFrame('vertical-align: middle', index=data.index, columns=data.columns)

def whitespace(data): return pd.DataFrame('white-space: normal', index=data.index, columns=data.columns)

d = {'col1': ['first\nsecond', 'only one'], 'col2': ['only one', 'first\nsecond']} df = pd.DataFrame(data=d)

df = df.style.apply(align, axis=None).apply(valign, axis=None).apply(whitespace, axis=None)

with pd.ExcelWriter('test.xlsx', engine='xlsxwriter') as writer: df.to_excel(writer, sheet_name='Test sheet')

Problem description

When using the XlsxWriter engine, the vertical alignment is ignored, the text is aligned to the bottom. The horizontal alignment as well as the whitespace/wrapping are correctly applied.

xlsxwriter

Expected output

This doesn't occur, when the OpenPyXL engine is chosen, i.e. when using:

with pd.ExcelWriter('test.xlsx', engine='openpyxl') as writer: df.to_excel(writer, sheet_name='Test sheet')

The expected output is correctly generated:

openpyxl

Some observations

The problem doesn't occur resp. the expected output can be generated solely using the XlsxWriter engine without using pandas styling:

import pandas as pd

d = {'col1': ['first\nsecond', 'only one'], 'col2': ['only one', 'first\nsecond']} df = pd.DataFrame(data=d)

with pd.ExcelWriter('test.xlsx', engine='xlsxwriter') as writer: df.to_excel(writer, sheet_name='Test sheet') workbook = writer.book worksheet = writer.sheets['Test sheet'] fmt = workbook.add_format({'align': 'center', 'valign': 'vcenter', 'text_wrap': True}) worksheet.set_column('B:C', None, fmt)

From pandas/io/formats/excel.py, I assume, that the vertical alignment setting middle is mapped to center, such that 'vertical-align': 'center'. Furthermore, from pandas/io/excel/_xlsxwriter.py, I guess, that the corresponding settings from the OpenPyXL engine are used and (partially) mapped to the correct names used in the XlsxWriter engine, cf. the correct mapping to valign. Now, my assumption is, that the (OpenPyXL's) center setting isn't properly translated to the XlsxWriter's vcenter setting, because, for example, 'vertical-align: top' generates the proper output also for the XlsxWriter engine.

Output of pd.show_versions()

INSTALLED VERSIONS
------------------
commit           : None
python           : 3.7.1.final.0
python-bits      : 64
OS               : Windows
OS-release       : 10
machine          : AMD64
processor        : Intel64 Family 6 Model 60 Stepping 3, GenuineIntel
byteorder        : little
LC_ALL           : None
LANG             : None
LOCALE           : None.None

pandas           : 0.25.3
numpy            : 1.17.4
pytz             : 2019.3
dateutil         : 2.8.1
pip              : 19.3.1
setuptools       : 39.1.0
Cython           : None
pytest           : None
hypothesis       : None
sphinx           : None
blosc            : None
feather          : None
xlsxwriter       : None
lxml.etree       : None
html5lib         : None
pymysql          : None
psycopg2         : None
jinja2           : None
IPython          : None
pandas_datareader: None
bs4              : 4.8.1
bottleneck       : None
fastparquet      : None
gcsfs            : None
lxml.etree       : None
matplotlib       : 3.2.0rc1
numexpr          : None
odfpy            : None
openpyxl         : 3.0.0
pandas_gbq       : None
pyarrow          : None
pytables         : None
s3fs             : None
scipy            : 1.3.1
sqlalchemy       : None
tables           : None
xarray           : None
xlrd             : None
xlwt             : None
xlsxwriter       : None