I just implemented a wrapper class that tracks the widths of items as you enter them. It seems to work pretty well.
import arial10
class FitSheetWrapper(object):
"""Try to fit columns to max size of any entry.
To use, wrap this around a worksheet returned from the
workbook's add_sheet method, like follows:
sheet = FitSheetWrapper(book.add_sheet(sheet_name))
The worksheet interface remains the same: this is a drop-in wrapper
for auto-sizing columns.
"""
def __init__(self, sheet):
self.sheet = sheet
self.widths = dict()
def write(self, r, c, label="", *args, **kwargs):
self.sheet.write(r, c, label, *args, **kwargs)
width = arial10.fitwidth(label)
if width > self.widths.get(c, 0):
self.widths[c] = width
self.sheet.col(c).width = width
def __getattr__(self, attr):
return getattr(self.sheet, attr)
All the magic is in John Yeung’s arial10
module. This has good widths for Arial 10, which is the default Excel font. If you want to write worksheets using other fonts, you’ll need to change the fitwidth function, ideally taking into account the style
argument passed to FitSheetWrapper.write
.