autofit column width using xlswriter || Django

Question

I am generating excel in my Django views.py but since column names are a bit long, I am having a hard time to set auto column fit width manually every time I/user downloads the excel.

Below is my working code snippet for excel generation using xlswriter.

def excel(request):
    ans = request.POST.getlist('ans[]')
    ans_final=[]
    rows = request.POST.get('rows')
    for each_ele in ans:
        each_ele = each_ele.split('.')
        each_ele[0] = each_ele[0][:-2]
        each_ele[1] = each_ele[1][:-2]
    fin = each_ele[0]+' - '+each_ele[1]
    ans_final.append(fin)
    workbook = xlsxwriter.Workbook('/home/Desktop/status.xlsx')
    worksheet = workbook.add_worksheet('Test_Data')
    bold = workbook.add_format({'bold': True})
    for i in range(len(ans_final)):
        worksheet.write(0, i,ans_final[i],bold)

    row_index=1
    row_count = int(rows)
    while(row_count):
        col_index=0
        for each_ele in ans:
            worksheet.write(row_index, col_index, eval(each_ele))
            col_index += 1
        row_index += 1
        row_count -= 1


   workbook.close() 
   return JsonResponse({'ok':'ok'})

Please suggest a work around for setting Auto-fit column width in the above code.

Please see if the below link provide solution to your query. https://stackoverflow.com/questions/33665865/adjust-cell-width-in-excel — Anupam Chaplot, Feb 25 '20 at 17:03

score 0 · Answer 1 · answered Feb 24 '20 at 10:44

You could try to use the worksheet.set_column(<start_col>, <end_col>, <width>) function?

Alternatively, if you want it to properly auto fit automatically, I think you'd need the win32com library?

You'd have to run something like this after you've added and saved your data into the spreadsheet:

import win32com.client as win32_client
excel_application = win32_client.gencache.EnsureDispatch('Excel.Application')
workbook = excel_application.Workbooks.Open(`<path_to_file>`)
worksheet = workbook.Worksheets("Test_Data")
worksheet.Columns.AutoFit()
workbook.Save()
excel_application.Application.Quit()

score 0 · Answer 2 · answered Feb 25 '20 at 12:33

Unfortunately, there is no way to specify “AutoFit” for a column in the Excel file format. This feature is only available at runtime from within Excel. It is possible to simulate “AutoFit” in your application by tracking the maximum width of the data in the column as your write it and then adjusting the column width at the end. From FAQ in documentation.

You can update your code and track the column data lenght while writing data inside column. This will come inside your while loop.

def excel(request):
    ans = request.POST.getlist('ans[]')
    ans_final=[]
    rows = request.POST.get('rows')
    for each_ele in ans:
        each_ele = each_ele.split('.')
        each_ele[0] = each_ele[0][:-2]
        each_ele[1] = each_ele[1][:-2]
    fin = each_ele[0]+' - '+each_ele[1]
    ans_final.append(fin)
    workbook = xlsxwriter.Workbook('/home/Desktop/status.xlsx')
    worksheet = workbook.add_worksheet('Test_Data')
    bold = workbook.add_format({'bold': True})
    for i in range(len(ans_final)):
        worksheet.write(0, i,ans_final[i],bold)

    row_index=1
    row_count = int(rows)
    cell_width = {}
    while(row_count):
        col_index=0
        for each_ele in ans:
            cell = worksheet.cell(row_index, col_index)
            cell.value = eval(each_ele)
            if cell.column_letter in cell_width:
                cell_width[cell.column_letter].append(len(eval(each_ele)))
            else:
                cell_width[cell.column_letter] = [len(cell_value[0])]
            col_index += 1
        row_index += 1
        row_count -= 1

    for key in cell_width.keys():
        width  = max(cell_width[key])
        worksheet.column_dimensions[key].width = width + 5

   workbook.close() 
   return JsonResponse({'ok':'ok'})

Here I am keeping length of every value in list and then getting the maximum number and then setting it as column width. I am adding extra 5 to add some extra space after content. I hope this will help you. :)

autofit column width using xlswriter || Django

2 Answers2