用VBA布局EXCEL报表的列宽问题

EXCEL的行高单位rowheight为磅值，列宽单位columnwidth为字宽，且字宽跟EXCEL的默认字体样式有关。

 

这个“正文字体”，又跟当前EXCEL文件的主题字体有关：

 

列宽不仅与每个字符的“标准宽度”有关，还有一个“调整宽度”，因为EXCEL默认总是要使单元格每一个字符“清晰可见”。

用以下代码，通过调整一系列columnwidth值，尝试分析列宽columnwidth与实际宽度width的关系：

Sub aa()

Dim bk As Workbook
Set bk = ThisWorkbook
Dim st As Worksheet, st1 As Worksheet
Set st1 = bk.Worksheets(1)
Set st2 = bk.Worksheets(2)
Dim cw As Double, w As Double, i As Integer

st2.Cells.Clear

For i = 1 To 100
    st1.Columns("A:A").ColumnWidth = i
    cw = st1.Cells(1, 1).ColumnWidth
    w = st1.Cells(1, 1).Width
    st2.Cells(i, 1).Value = cw
    st2.Cells(i, 2).Value = w'Debug.Print "cw" & cw & "  w" & w & "  w/cw" & w / cw
Next i

End Sub

 

得到以下结果：

1	12
2	18.75
3	25.5
4	32.25
5	39
6	45.75
7	52.5
8	59.25
9	66
10	72.75
11	79.5
12	86.25
...	...

 

观察其中关系，发现无论列宽columnwidth的“字符个数宽度”多少，“调整宽度”是固定的。

设每个字符对应的标准宽度为x，调整宽度为y，利用上述第1、2条数据有：

1 * x + y = 12

2 * x + y = 18.75

解之，x = 6.75    y = 5.25

 

故若每页报表列数不一样的时候，为了使每页的总体实际宽度一直，在设置列宽的时候应注意单元格的“调整宽度” 。

当然，先要用一个函数，以实验测试的方式，得到目标工作簿下的“标准字宽”和单元格“调整宽度”，比如这样：

'用于计算当前EXCEL设置下的单元格字符标准宽度和调整宽度（磅值）
Function getStandardCW()
    Dim c As Range
    Set c = ThisWorkbook.Worksheets(1).Cells(1, 1)
    Dim cw0 As Double, w As Double, w1 As Double
    cw0 = c.ColumnWidth
    c.ColumnWidth = 1
    w = c.Width
    c.ColumnWidth = 2
    w1 = c.Width
    c.ColumnWidth = cw0
    getStandardCW = Array(w1 - w, 2 * w - w1)
End Function

 

实际调整报表列宽的时候，获取当前环境的一个单元格调整宽度相当于的columnWidth：

Dim gw As Double
T = getStandardCW()
gw = T(1) / T(0)

 

假若报表总列数为4时，为“标准情况”，那么在调整其他列数有差别的报表时，再分别调整：

超过/不足4列后的每一列，应该调整减少/增加1个gw。