Let’s create a new 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) >>> first_col.width 5120
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) >>> first_row.set_style(tall_style)
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
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.
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:
import itertools import xlwt book = xlwt.Workbook(encoding='utf-8') sheet = book.add_sheet('sheeeeeet') col_width = 256 * 20 # 20 characters wide try: for i in itertools.count(): sheet.col(i).width = col_width except ValueError: pass default_book_style = book.default_style default_book_style.font.height = 20 * 36 # 36pt book.save('example.xls')
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.