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.

Answer 1

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: