在字符单位和像素(点)之间转换Excel列宽 [英] Convert Excel column width between characters unit and pixels (points)

问题描述

一个单位的列宽等于普通"样式中一个字符的宽度.对于比例字体，使用字符0(零)的宽度."

因此，Excel中的ColumnWidth被测量为适合列的"0"字符数.如何将该值转换为像素，反之亦然?

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?

推荐答案

如上所述，Excel中的ColumnWidth值取决于可以通过Workbook.Styles("Normal").Font获取的工作簿的默认字体.另外，它取决于当前屏幕的DPI.

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.

在Excel 2013中对不同字体和大小进行了一些研究之后，我发现我们有2个线性函数(因为Arial与Tahoma重叠，所以看不到Arial):

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

从图中可以看出，ColumnWidth < 1的功能与折线图的主要部分不同.它是根据一列中的像素数/一列中适合一个"0"字符所需的像素数来计算的.

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.

• A-常规样式中的字符宽度为"0"
• B-左右填充
• C-1px右边距

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

A Windows API函数GetTextExtentPoint32 ，但是字体大小应该大一些.通过实验，我选择了+ 0.3pt，它对我使用的基本字体为8-48pt的不同字体都有效. B使用四舍五入" 四舍五入为整数.此外，这里还需要屏幕DPI(请参见下面的Python 3实现)

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)

以下是用于字符像素转换的公式及其在Python 3中的实现:

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

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)

这篇关于在字符单位和像素(点)之间转换Excel列宽的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！