Widths & Heights with xlwt + Python

这是http://down.vvipblog.com/itprogram/python/pybeginner/3933.html的英文版本的内容。

This article about using xlwt to generate Excel in Python reminded me I needed to see exactly how to set column widths (the xlwt documentation doesn’t cover it).

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 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.

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.

Talking of widths and heights, have you heard “Widths & Heights” by Magic Arm? Is good.