fitsheet

arial10.py

'''
Character width dictionary and convenience functions for column sizing
with xlwt when Arial 10 is the standard font.  Widths were determined
experimentally using Excel 2000 on Windows XP.  I have no idea how well
these will work on other setups.  For example, I don't know if system
video settings will affect the results.  I do know for sure that this
module won't be applicable to other fonts in general.

//John Yeung  2009-09-02
'''

charwidths = {
    '0': 262.637,
    '1': 262.637,
    '2': 262.637,
    '3': 262.637,
    '4': 262.637,
    '5': 262.637,
    '6': 262.637,
    '7': 262.637,
    '8': 262.637,
    '9': 262.637,
    'a': 262.637,
    'b': 262.637,
    'c': 262.637,
    'd': 262.637,
    'e': 262.637,
    'f': 146.015,
    'g': 262.637,
    'h': 262.637,
    'i': 117.096,
    'j': 88.178,
    'k': 233.244,
    'l': 88.178,
    'm': 379.259,
    'n': 262.637,
    'o': 262.637,
    'p': 262.637,
    'q': 262.637,
    'r': 175.407,
    's': 233.244,
    't': 117.096,
    'u': 262.637,
    'v': 203.852,
    'w': 321.422,
    'x': 203.852,
    'y': 262.637,
    'z': 233.244,
    'A': 321.422,
    'B': 321.422,
    'C': 350.341,
    'D': 350.341,
    'E': 321.422,
    'F': 291.556,
    'G': 350.341,
    'H': 321.422,
    'I': 146.015,
    'J': 262.637,
    'K': 321.422,
    'L': 262.637,
    'M': 379.259,
    'N': 321.422,
    'O': 350.341,
    'P': 321.422,
    'Q': 350.341,
    'R': 321.422,
    'S': 321.422,
    'T': 262.637,
    'U': 321.422,
    'V': 321.422,
    'W': 496.356,
    'X': 321.422,
    'Y': 321.422,
    'Z': 262.637,
    ' ': 146.015,
    '!': 146.015,
    '"': 175.407,
    '#': 262.637,
    '$': 262.637,
    '%': 438.044,
    '&': 321.422,
    '\'': 88.178,
    '(': 175.407,
    ')': 175.407,
    '*': 203.852,
    '+': 291.556,
    ',': 146.015,
    '-': 175.407,
    '.': 146.015,
    '/': 146.015,
    ':': 146.015,
    ';': 146.015,
    '<': 291.556,
    '=': 291.556,
    '>': 291.556,
    '?': 262.637,
    '@': 496.356,
    '[': 146.015,
    '\\': 146.015,
    ']': 146.015,
    '^': 203.852,
    '_': 262.637,
    '`': 175.407,
    '{': 175.407,
    '|': 146.015,
    '}': 175.407,
    '~': 291.556}

# By default, Excel displays column widths in units equal to the width
# of '0' (the zero character) in the standard font.  For me, this is
# Arial 10, but it can be changed by the user.  The BIFF file format
# stores widths in units 1/256th that size.
#
# Within Excel, the smallest incrementable amount for column width
# is the pixel.  However many pixels it takes to draw '0' is how many
# increments there are between a width of 1 and a width of 2.  A
# request for a finer increment will be rounded to the nearest pixel.
# For Arial 10, this is 9 pixels, but different fonts will of course
# require different numbers of pixels, and thus have different column
# width granularity.
#
# So far so good, but there is a wrinkle.  Excel pads the first unit
# of column width by 7 pixels.  At least this is the padding when the
# standard font is Arial 10 or Courier New 10, the two fonts I've tried.
# It don't know if it's different for different fonts.  For Arial 10,
# with a padding of 7 pixels and a 9-pixel-wide '0', this results in 16
# increments to get from width 0 (hidden) to width 1.  Ten columns of
# width 1 are 160 pixels wide while five columns of width 2 are 125
# pixels wide.  A single column of width 10 is only 97 pixels wide.
#
# The punch line is that pixels are the true measure of width, and
# what Excel reports as the column width is wonky between 0 and 1.
# The only way I know to find out the padding for a desired font is
# to set that font as the standard font in Excel and count pixels.


def colwidth(n):
    '''Translate human-readable units to BIFF column width units'''
    if n <= 0:
        return 0
    if n <= 1:
        return n * 456
    return 200 + n * 256


def fitwidth(data, bold=False):
    '''Try to autofit Arial 10'''
    maxunits = 0
    for ndata in data.split("\n"):
        units = 220
        for char in ndata:
            if char in charwidths:
                units += charwidths[char]
            else:
                units += charwidths['0']
        if maxunits < units:
            maxunits = units
    if bold:
        maxunits *= 1.1
    return max(maxunits, 700)  # Don't go smaller than a reported width of 2


def fitheight(data, bold=False):
    '''Try to autofit Arial 10'''
    rowlen = len(data.split("\n"))
    if rowlen > 1:
        units = 230 * rowlen
    else:
        units = 290
    if bold:
        units *= 1.1
    return int(units)

fitsheet.py

import locale

locale.setlocale(locale.LC_ALL, locale.getdefaultlocale())

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)
        if type(label) == Decimal:
            label = locale.currency(label, grouping=True)
        else:
            try:
                label = str(label)
            except Exception:
                label = smart_text(label)
                label = label.encode('ascii', 'replace')
        width = int(arial10.fitwidth(str(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)

gistfile1.txt

workbook = xlwt.Workbook(encoding='utf8')
sheet = workbook.add_sheet(nameSheet, cell_overwrite_ok=True)
sheet = FitSheetWrapper(sheet)