pandas ExcelWriter write sheet direction right to left

Question

I'm creating in python Django dynamic Excel file that is downloaded (not stored locally), and I need to make the sheet display from right to left and not from left to right.

Here is the code that I used:

import pandas
from io import BytesIO, StringIO

sio = BytesIO()

PandasDataFrame = pandas.DataFrame([['t1', 't2'], ['t3', 't4']], index = ['t1', 't2'], columns = ['t11', 't1']  )

PandasWriter = pandas.ExcelWriter(sio, engine='xlsxwriter')



PandasDataFrame.to_excel(PandasWriter, sheet_name='Sheet1')

PandasWriter.book.add_format({'reading_order': 2})

PandasWriter.save()

PandasWriter.close()

sio.seek(0)

workbook = sio.read()

response = HttpResponse(workbook,content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')

response['Content-Disposition'] = 'attachment; filename=wrong_data.xlsx'

return (response)

Answer 1

Here is a simple Pandas example that demonstrates how to change the text, and also the worksheet direction. You can convert it to a Django example yourself.

# _*_ coding: utf-8

import pandas as pd

# Create a Pandas dataframe from some data.
df = pd.DataFrame({'Data': [u'نص عربي / English text'] * 6})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Add the cell formats.
format_right_to_left = workbook.add_format({'reading_order': 2})

# Change the direction for the worksheet.
worksheet.right_to_left()

# Make the column wider for visibility and add the reading order format.
worksheet.set_column('B:B', 30, format_right_to_left)

# Close the Pandas Excel writer and output the Excel file.
writer.save()

Output:

See the XlsxWriter docs on the worksheet right_to_left() method for more details.