1

I am trying the following code and it is working well. But I am wondering if there is a short approach to deal with the add format feature which is there in the package of xlsxwriter. Here's the code to more clarification

import pandas as pd
import xlsxwriter

writer = pd.ExcelWriter('pandas_excel.xlsx', engine='xlsxwriter')
new_df.to_excel(writer, sheet_name='Sheet1')

workbook  = writer.book

format_right_to_left = workbook.add_format()
format_right_to_left.set_reading_order(2)

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

worksheet = writer.sheets['Sheet1']
worksheet.right_to_left()

worksheet.set_column('B:G', 12, myformat)
worksheet.set_column('A:A', 16, myformat)
writer.save()
writer.close()

In the code I have those lines of adding format

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

My question how to shorten such lines so as to be more flexible and so as to be able to add more and more formats in an easy way?

YasserKhalil
  • 9,138
  • 7
  • 36
  • 95

1 Answers1

2

You can pass properties as a dict to add_format().

my_format = workbook.add_format({'reading_order':2, 'align':'center', 'valign':'vcenter'})

You can read more here

EDIT: full working example, with border

import pandas as pd
import xlsxwriter

data = {'col1': [1, 2], 'col2': [3, 4]}
df = pd.DataFrame(data=data)

with pd.ExcelWriter('pandas_excel.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, sheet_name='Sheet1')
    workbook  = writer.book
    my_format = workbook.add_format({'reading_order':2, 'align':'center', 'valign':'vcenter', 'border':1})
    worksheet = writer.sheets['Sheet1']
    worksheet.set_column('B:G', 12, my_format)
    worksheet.set_column('A:A', 16, my_format)
buran
  • 13,682
  • 10
  • 36
  • 61
  • Question: How can I put borders for the current region of A1 ( the number of rows would be dynamic but the columns would be 7)? – YasserKhalil Dec 01 '20 at 14:09
  • And as for these two lines `writer.save() writer.close()` Is that right or using `workbook` variable is the right ?? – YasserKhalil Dec 01 '20 at 14:20
  • 1
    It would be better to use `with` context manager as shown in the docs, that will take care to close the file for you. Note the note: __None of the methods and properties are considered public.__ – buran Dec 01 '20 at 14:25
  • Thanks a lot. I tried this after some search `formater = workbook.add_format({'border': 1}) worksheet.set_column('A:G', 15, formater)` but doesn't seem to work for me!! – YasserKhalil Dec 01 '20 at 14:49
  • Amazing. Thanks a lot. But the borders are on the whole column. How can I set the borders for specific range (A1:C3) for example? – YasserKhalil Dec 01 '20 at 15:11
  • I have searched a lot of how to set range reference but didn't find a clue till now. I mean I need to apply the format on specific range not the whole columns. – YasserKhalil Dec 01 '20 at 15:13
  • 1
    [it's not possible to set format to multiple cells at once](https://xlsxwriter.readthedocs.io/faq.html#q-can-i-apply-a-format-to-a-range-of-cells-in-one-go). You either format column, row or single cell. You need to loop over single cells and set format one by one. See also https://stackoverflow.com/questions/34026362/xlswriter-formatting-a-range – buran Dec 01 '20 at 15:18
  • Can you show me a simple example of how to loop over cells for a specific range? – YasserKhalil Dec 01 '20 at 16:26