This [article about using xlwt to generate Excel][ylp] in [Python][python] reminded me I needed to see exactly how to set column widths (the [xlwt documentation][xlwtdocs] doesn’t cover it).
Let’s create a new [Excel][excel] workbook and add a sheet:
>>> import xlwt
>>> book = xlwt.Workbook(encoding=’utf-8′)
>>> sheet = book.add_sheet(‘sheeeeeet’)
We need to get a column in order to set its width. You do that by call `col()` on the sheet, passing the column’s index as the only argument (or `row()` for accessing rows):
>>> sheet.col(0) # First column
>>> sheet.row(2) # Third row
The index is zero-based. You can fetch a column even if you have not written to any cell in that column (this applies equally to rows).
Columns have a property for setting the width. The value is an integer specifying the size measured in 1/256 of the width of the character ‘0’ as it appears in the sheet’s default font. xlwt creates columns with a default width of 2962, roughly equivalent to 11 characters wide.
>>> first_col = sheet.col(0)
>>> first_col.width = 256 * 20 # 20 characters wide (-ish)
For rows, the height is determined by the style applied to the row or any cell in the row. *(In fact rows also have a property called `height` but it doesn’t do what you want.)* To set the height of the row itself, create a new style with a font height:
>>> tall_style = xlwt.easyxf(‘font:height 720;’) # 36pt
>>> first_row = sheet.row(0)
Setting the style on the row does not change the style of the cells in that row.
There is no obvious way to set a default width and height for all columns and rows. An instance of `xlwt.Worksheet.Worksheet` has properties for `col_default_width` and `row_default_height` but changing those does not actually change the defaults.
The problem is that new columns are always created with an explicit width, while rows take their height from the style information.
My first attempt at setting defaults set the width on every column and the height on every row. It works, but [creates 65,536 unnecessary empty row objects][xls].
A slightly better approach is to set the width on every column and to set the font height on the default style record in the workbook:
book = xlwt.Workbook(encoding=’utf-8′)
sheet = book.add_sheet(‘sheeeeeet’)
col_width = 256 * 20 # 20 characters wide
for i in itertools.count():
sheet.col(i).width = col_width
default_book_style = book.default_style
default_book_style.font.height = 20 * 36 # 36pt
Here I used `itertools.count()` and wrapped the loop in a try block so I can forget exactly how many columns are permitted. When the loop tries to access a bad index it will throw `ValueError` and the loop will exit.
You mustn’t replace the default style on an instance of xlwt.Workbook.Workbook, you have to update the property of the existing style (to ensure you are changing the *first* style record). Unfortunately there is no way to set a default column width (as of xlwt version 0.7.2) so the brute force method of setting every column will have to do – it isn’t so bad since there are only 256 columns.
Talking of widths and heights, have you heard [“Widths & Heights”][wah] by [Magic Arm][magicarm]? Is good.