BUG: ExcelWriter with xlsxwriter adds default format to cells preventing column formats · Issue #9167 · pandas-dev/pandas (original) (raw)

The xlsxwriter engine (which I helped implement) erroneously adds a default General format to cells that don't require it.

This doesn't affect spreadsheets created with to_excel(). However, it does prevent the user from overwriting the cell format with a column format.

For example consider the following program:

import pandas as pd from pandas import ExcelWriter

df = pd.DataFrame([[123456, 123456], [987654, 987654]], columns=['First', 'Second'])

writer = ExcelWriter('test.xlsx', engine='xlsxwriter')

df.to_excel(writer)

workbook = writer.book worksheet = writer.book.worksheets()[0]

Add a column format.

myformat = workbook.add_format({'num_format': '#,##0'}) worksheet.set_column(1, 1, None, myformat)

writer.save()

The gives the following output:

screenshot 2

However, it should look like this (note the format in the B column cells):

screenshot

I'll submit a PR to fix this as soon as I get a working test.

Versions:

INSTALLED VERSIONS
------------------
commit: 4aa0e0a674a0823b5a81944f087e744f27c4e21d
python: 2.7.2.final.0
python-bits: 64
OS: Darwin
OS-release: 14.0.0
machine: x86_64
processor: i386
byteorder: little
LC_ALL: None
LANG: en_IE.UTF-8

pandas: 0.15.2-50-g4aa0e0a
nose: 1.3.0
Cython: 0.19.1
numpy: 1.7.1
scipy: None
statsmodels: None
IPython: 1.1.0
sphinx: 1.2b3
patsy: None
dateutil: 2.2
pytz: 2013b
bottleneck: None
tables: None
numexpr: None
matplotlib: 1.4.x
openpyxl: 2.0.2
xlrd: 0.9.3
xlwt: 0.7.5
xlsxwriter: 0.6.4
lxml: 3.2.3
bs4: None
html5lib: 1.0b3
httplib2: None
apiclient: None
rpy2: None
sqlalchemy: None
pymysql: None
psycopg2: None