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

[HansHirse]

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.

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:

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()

```lang-none 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 ```

[tehunter]

take