how to add border to a range of cells using xlsxwriter?

Question

I want to add border around a range of cells like this image1, but my output file looks like this when I applied "set_column" function image2. instead of "set_column", is there any other functions to use?

writer=pd.ExcelWriter('merge_'+name+'.xlsx',
                      engine='xlsxwriter')
workbook  = writer.book
data.to_excel(writer, sheet_name=SheetName,index=False,na_rep=0)
worksheet = writer.sheets[SheetName]
border_format=workbook.add_format({
                            'border':1,
                            'align':'left',
                            'font_size':10
                           })
worksheet.set_column('A:D',12,border_format)

Thank you in advance,

Impressionism

Answer 1

I don't know how with xlsxwriter you can add format at range with set_column or set_row methods, but you can try do it with conditional formatting like this:

worksheet.conditional_format( 'A1:D12' , { 'type' : 'no_blanks' , 'format' : border_format} )

Singles

thank you, it works although formating font size and align don't work. I tried combination of "set_column" and "condition_format", and it perfectly works fine. – Impressionism

@Impressionism Can you share your code? As in I want to see how you combined "set_column" and "condition_format"? Thanks in Advance :) – Chthonian

Here I never mind it is blank or not i need to draw border for certain range any ideas for that. Thanks in advance – Kurtkurth

Answer 2

An improvement on the accepted answer to get the conditional formatting method working for both empty cells & non-empty cells.

workbook = xlsxwriter.Workbook('hello_.xlsx')
border = workbook.add_format ({ 'border':1})
worksheet = workbook.add_worksheet('results')

headers = ['Total', 'GST', 'Total(Inc GST)', 'Commission', 'Final Payment']
vals = [31514.47, 3151.44 , 34665.917, 2773.27, 31892.64]


start_row=9 
start_col=3
worksheet.write_column (start_row, start_col ,headers)
worksheet.write_column (start_row, start_col+4 ,vals)

# This method works for all  cell regardless of being empty or not
worksheet.conditional_format( start_row, start_col, start_row+5, start_col+6, { 'type' : 'cell' , 
                                'criteria': 'not equal to',
                                'value':    '"Text_with_0_probability"',
                                'format' : border} )
workbook.close()

Tachometer