Search code examples
excelunit-conversion

Convert Excel column width between characters unit and pixels (points)

"One unit of column width is equal to the width of one character in the Normal style. For proportional fonts, the width of the character 0 (zero) is used."

So ColumnWidth in Excel is measured as a number of "0" characters which fits in a column. How can this value be converted into pixels and vice versa?

Image from https://bettersolutions.com/excel/rows-columns/column-widths.htm

Solution

  • As already mentioned ColumnWidth value in Excel depends on default font of a Workbook which can be obtained via Workbook.Styles("Normal").Font. Also it depends on current screen DPI.

    After carrying out some research for different fonts and sizes in Excel 2013 I've found out that we have 2 linear functions (Arial cannot be seen because it overlaps with Tahoma.):

    enter image description here

    As it can be seen in the picture the function for ColumnWidth < 1 is different from the major part of the line chart. It's calculated as a number of pixels in a column / number of pixels needed to fit one "0" character in a column.

    Now let's see what a typical cell width consists of.

    enter image description here

    • A - "0" character width in the Normal Style
    • B - left and right padding
    • C - 1px right margin

    A can be calculated with GetTextExtentPoint32 Windows API function, but font size should be a little bit bigger. By experiment I chose +0.3pt which worked for me for different fonts with 8-48pt base size. B is (A + 1) / 4 rounded to integer using "round half up". Also screen DPI will be needed here (see Python 3 implementation below)

    Here are equations for character-pixel conversion and their implementation in Python 3:

    enter image description here

    enter image description here

    enter image description here

    import win32print, win32gui
from math import floor

def get_screen_dpi():
    dc = win32gui.GetDC(0)
    LOGPIXELSX, LOGPIXELSY = 88, 90
    dpi = [win32print.GetDeviceCaps(dc, i) for i in (LOGPIXELSX,
                                                        LOGPIXELSY)]
    win32gui.ReleaseDC(0, dc)
    return dpi

def get_text_metrics(fontname, fontsize):
    "Measures '0' char size for the specified font name and size in pt"
    dc = win32gui.GetDC(0)
    font = win32gui.LOGFONT()
    font.lfFaceName = fontname
    font.lfHeight = -fontsize * dpi[1] / 72
    hfont = win32gui.CreateFontIndirect(font)
    win32gui.SelectObject(dc, hfont)
    metrics = win32gui.GetTextExtentPoint32(dc, "0")
    win32gui.ReleaseDC(0, dc)
    return metrics

def ch_px(v, unit="ch"):
    """
    Convert between Excel character width and pixel width.
    `unit` - unit to convert from: 'ch' (default) or 'px'
    """
    rd = lambda x: floor(x + 0.5)  # round half up
    # pad = left cell padding + right cell padding + cell border(1)
    pad = rd((z + 1) / 4) * 2 + 1
    z_p = z + pad  # space (px) for "0" character with padding
    if unit == "ch":
        return v * z_p if v < 1 else v * z + pad
    else:
        return v / z_p if v < z_p else (v - pad) / z

font = "Calibri", 11
dpi = get_screen_dpi()
z = get_text_metrics(font[0], font[1] + 0.3)[0]  # "0" char width in px
px = ch_px(30, "ch")
ch = ch_px(px, "px")
print("Characters:", ch, "Pixels:", px, "for", font)