如何用Python openpyxl将Pandas一维DataFrame横向写入Excel？能否配置写入方向？

Great question! Unfortunately, pandas' to_excel method doesn’t have a built-in parameter to control writing direction (vertical vs horizontal) directly. But the fix is simple—you just need to transpose your DataFrame before writing it to Excel.

Why This Works #

Your 1D DataFrame is structured as a single column of values. Transposing it converts that column into a single row, which will then be written horizontally across columns instead of vertically down rows.

Modified Code #

Here’s how to adjust your existing code to get the horizontal output you want:

writer = pd.ExcelWriter(fn, engine='openpyxl')
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

# Transpose the DataFrame before writing to switch to horizontal direction
df_2.T.to_excel(writer, sheet_name='Sheet3', header=None, index=False, startcol=1, startrow=1)

writer.save()

Note for Series Data #

If your df_2 is actually a pandas Series (instead of a single-column DataFrame), you’ll need to convert it to a DataFrame first before transposing:

df_2.to_frame().T.to_excel(writer, sheet_name='Sheet3', header=None, index=False, startcol=1, startrow=1)

What This Does #

With this change, your values [10, 9, 8, 7, 6] will be written starting at cell B2 (since startcol=1 and startrow=1 are 0-indexed) and spread horizontally across B2 → C2 → D2 → E2 → F2, instead of stacking vertically down B2 → B3 → B4 → B5 → B6.

This approach keeps using the startrow and startcol parameters you’re already comfortable with, so you can control the starting position exactly as before.

内容的提问来源于stack exchange，提问作者Jacky