4

I am having trouble aligning the columns of an Excel Worksheet using xlsxWriter. For further clarity here is the data-frame

   Name        Employee ID      Year
John Miller    2014108          2014
Sarah Jones    2011548          2011
Jake Kenedy    2010546          2010

I am trying to align the Name column so that the values are on the left instead of being centred

I tried this

workbook = writer.book
cell_format = workbook.add_format()
cell_format.set_align('left')

However, nothing happened. Any suggestions would be greatly appreciated.

Improve this question
0

2 Answers 2

Reset to default
3

You need to actually apply the formatting the name column. 

df = pd.DataFrame ({'Name': ['J Miller', 'S Jones', 'J Kenedy'],
                    'Employee ID': [1,2,3],
                    'Year': [2014, 2011, 2010]})

writer = pd.ExcelWriter('left_aligned_file.xlsx', engine='xlsxwriter')

# Add your dataframe to the writer
df.to_excel(writer, sheet_name='Sheet1')

workbook = writer.book
worksheet = writer.sheets['Sheet1']

new_format = workbook.add_format()
new_format.set_align('left')

# Apply new format to name column, which will be column C.
worksheet.set_column('C:C', 10, new_format)
writer.save()

Might be worth a look: http://xlsxwriter.readthedocs.io/example_pandas_column_formats.html

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I tried the suggestion you provided. However, nothing happened except the cells got wider. The original data frame is much larger and has over 40000 entries. Furthermore, I am trying to align the index, is that causing the problem by any chance.
1

I had the same issue related to the index. My dataframe had the first column of type string as the index. It appears the index cannot be reformatted using xlsxwriter. To get around this I tried the following in this order:

df.reset_index(inplace=True)
df.to_excel(writer, sheet_name='Sheet1',index=False)

Then the formatting functions worked.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.