Export dataframe to excel file using xlsxwriter

Question

I have dataframes as output and I need to export to excel file. I can use pandas for the task but I need the output to be the worksheet from right to left direction. I have searched and didn't find any clue regarding using the pandas to change the direction .. I have found the package xlsxwriter do that

import xlsxwriter

workbook = xlsxwriter.Workbook('output.xlsx')
worksheet1 = workbook.add_worksheet()
format_right_to_left = workbook.add_format({'reading_order': 2})
worksheet1.set_column('A:A', 20)
worksheet1.right_to_left()
worksheet1.write(new_df)
workbook.close()

But I don't know how to export the dataframe using this approach ..

snapshot to clarify the directions:

** I have used multiple lines as for format point

myformat = workbook.add_format()
myformat.set_reading_order(2)
myformat.set_align('center')
myformat.set_align('vcenter')

Is it possible to make such lines shorter using dictionary ..for example?

What do you mean by worksheet from right to left direction? Your expected output is not particularly clear to me. — Mayank Porwal
– Mayank Porwal, Commented Nov 30, 2020 at 21:01
If you know excel and VBA there is a property DisplayRightToLeft and it is assigned to True or False. I have found it in xlsxwriter package worksheet1.right_to_left() ..It is responsible for displaying the worksheet from left to rigth (this is the default) but as for Arabic, we are accustomed the direction from right to left. — YasserKhalil
– YasserKhalil, Commented Nov 30, 2020 at 21:06

Mayank Porwal · Accepted Answer · 2020-11-30 21:15:48Z

3

You can do this:

import xlsxwriter

writer = pd.ExcelWriter('pandas_excel.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1') # Assuming you already have a `df`

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

format_right_to_left = workbook.add_format({'reading_order': 2})
worksheet.right_to_left()
writer.save()

answered Nov 30, 2020 at 21:15

Mayank Porwal

34.2k9 gold badges45 silver badges65 bronze badges

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

3 Comments

YasserKhalil Over a year ago

Thank you very much. As for such a line in VBA ws.Cells.ReadingOrder = xlRTL makes the Arabic numbers appear correctly. What is the equivalent here?

YasserKhalil Over a year ago

I have searched and could do that worksheet.set_column('A:G', 11, format_right_to_left). How can I add more formats? such as this cell_format.set_align('center'). How can I merge the formats ??

YasserKhalil Over a year ago

I have updated my question so as to make it clear as for the format point.

Collectives™ on Stack Overflow

Export dataframe to excel file using xlsxwriter

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related