Rotate cell's text when using pd.DataFrame.to_excel?

Question

I'm working on a program and one of the needed features is to take a pandas dataframe and exports it to excel. This part works fine. When exporting, though, I need some of the cells to have their text rotated to be vertical rather than the default horizontal arrangement.

Here's what I have so far:

output = BytesIO()
writer = pd.ExcelWriter(output, engine='xlsxwriter')
filename = "Data for {} at {} on {}".format(item_no, loc, date)
columns = [('A:A', 18),
           ('B:B', 4),
           ('C:C', 18),
           ('D:Y', 20),
           ('Z:Z', 18),
           ('AA:AA', 12),
           ('AB:AB', 18),
           ('AC:AC', 12),
           ('AD:AD', 4),
           ('AE:AF', 18),
           ('AG:AG', 11),
           ('AH:AK', 16),
           ('AL:AL', 10),
           ('AM:AN', 14.5)]
df = fetch_data(item_no, loc, date)
df.to_excel(writer, index=False, sheet_name='Sheet1')

for col, width in columns:
    writer.sheets['Sheet1'].set_column(col, width)

writer.save()    
writer.close()
output.seek(0)

I know that the xlsxwriter library has format.set_rotation() but from what I can tell, this is only used when you use worksheet.write(..., format) rather than after pandas has written each cell to the sheet.

How would I go about getting certain of the cells to orient text vertically (90 degree rotation) rather than horizontally? In particular, I'm interested in rotating the text in cells D1:Y1.

You will have to post-process your generated file - probably with xlsxwriter itself, in order to add cell specific options to to it. — jsbueno, Mar 08 '18 at 21:43
I agree. The best way to do this is to dump the dataframe to Excel and then overwriting the data in cells `D1:Y1` with the frame data and the formatting that you want. — jmcnamara, Mar 09 '18 at 09:50

patrickjlong1 · Accepted Answer · 2018-03-09T14:15:16.907

You have the xlsxwriter objects from the dataframe writer object. So you could add a format and set it's properties. Then you can use it loop through writer.sheets['Sheet1'].set_column(col, width). If on the other hand you only want the column names rotated, you can opt to set header=False in df.to_excel() and then write out the column names, one-by-one in a loop.

I've provided an example that illustrates this below. You could condition on the 'D1:Y1' if those are the only ones you want to rotate on

import pandas as pd

df = pd.DataFrame({"Name": ["A", "B", "C"], "Status": [1, 2, 3]})

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

columns = [('A:A', 18),
           ('B:B', 4),
           ('C:C', 18),
           ('D:Y', 20),
           ('Z:Z', 18),
           ('AA:AA', 12),
           ('AB:AB', 18),
           ('AC:AC', 12),
           ('AD:AD', 4),
           ('AE:AF', 18),
           ('AG:AG', 11),
           ('AH:AK', 16),
           ('AL:AL', 10),
           ('AM:AN', 14.5)]

header_list = list(df.columns.values)

df.to_excel(writer, index=False, header=False, startrow=1, sheet_name='Sheet1')

workbook  = writer.book

format = workbook.add_format({})
format.set_rotation(90) 

for col, width in columns:
    writer.sheets['Sheet1'].set_column(col, width)

for i in range (0, len(header_list)):
    writer.sheets['Sheet1'].write(0, i, header_list[i], format)

workbook.close()

Expected Output:

This would apply the format to the whole column rather than just the first row, right? I'm just looking for `D1:Y1` rather than `D:Y`. — RagingRoosevelt, Mar 09 '18 at 05:33
@RagingRoosevelt I've modified the answer to rotate only the column name. It involved a few changes in `df.to_excel()` and creating a list of the column names. — patrickjlong1, Mar 09 '18 at 14:17
Thanks! I amended your solution a bit by setting `header_list = [(i, v) for i,v in enumerate(df.columns.values) if ]` and then looping over `for i, v in header_list:`. I was hoping that there might be a less dirty way to do this but it seems like there isn't :( — RagingRoosevelt, Mar 09 '18 at 16:36

Rotate cell's text when using pd.DataFrame.to_excel?

1 Answers1