How to set the width of columns of a spreadsheet (xls/xlsx file) in Python

Question

I am using xlsxwriter to create a spreadsheet. Some of columns of the spreadsheet store a large number of bytes.xlsxwriter sets the default width for all columns. How can I increase the width of columns?

Answer 1

You have to use set_column() function to change the default properties for one or more columns.

set_column(first_col, last_col, width, cell_format, options)

Parameters:

• first_col (int) – First column (zero-indexed).
• last_col (int) – Last column (zero-indexed). Can be same as first_col.
• width (float) – The width of the column(s).
• cell_format (Format) – Optional Format object.
• options (dict) – Optional parameters: hidden, level, collapsed.

Examples:

worksheet.set_column(0, 0, 20)   # Column  A   width set to 20.

worksheet.set_column(1, 3, 30)   # Columns B-D width set to 30.

worksheet.set_column('E:E', 20)  # Column  E   width set to 20.

worksheet.set_column('F:H', 30)  # Columns F-H width set to 30.

Sample code:

import xlsxwriter

def main():

    xlsfile = 'test.xlsx'

    uid = (['AB',11], ['BC',12], ['CD',13], ['DE',14], ['EF',15])

    workbook = xlsxwriter.Workbook(xlsfile)

    worksheet = workbook.add_worksheet()

    worksheet.set_column(0,0,10) #set column A width to 10

    r = 0 #start row

    c = 0 #start column

    for name, id in (uid):

        worksheet.write(r, c, name)

        worksheet.write(r, c+1, id)

        r+=1

    workbook.close()

if __name__ == '__main__':

    main()

Output:

AB	11
BC	12
CD	13
DE	14
EF	15