|
|
ExcelHome LDY ExcelHome LDY
如何精确计算设置列宽，目前来说没有一个绝对正确的方案。
想要完全搞清楚列宽的转换，没有相关的技术文档，几乎是不可能的事情。
但影响列宽计算的三个重要因素
内部因素 “标准字体”及其字号 ExcelHome LDY
外部因素 显示器的DPI设置
外部因素 显示器分辨率设置要与显示器匹配（如果设置不当，看到的美女可能会是一个矮胖子）
这三个因素在绝大多数电脑中设置都是一样的，而且如非特殊需求是没有必要修改的。
笔者在这样的前提下，对列宽的换算进行了一番反推算研究，虽然结果只是几个简单的公式
以及几行VBA代码，但其中涉及到的知识确是非常基础实用的，将过程记录下来与同好分享之。
“标准字体”的字号如不是文中提及的12号，则结果可乘以相应比例来使用。
Excel中的度量单位大多数都是以磅为单位 ExcelHome LDY
唯独列宽是以字符宽度为单位进行设置，而字符宽度又是一个动态的单位
就像日常所说的有碗口粗细，这个饭碗和汤碗的尺寸差别可就大了。
自从Excel发行以来，列宽的计算方法从来没有被搞清楚过。
用通常的磅、像素、字符的转换方式计算，永远得不到正确的答案。
对于列宽来说，我们总是被动的接受Excel处理后的结果。
Columns(6).ColumnWidth = 8 '单位是字符 ExcelHome LDY
B = Columns(6).Width ' 'B 的单位是磅，只能读取，无法通过计算得出。
我们只知道一个原则，列宽是根据标准字体极其字号，按照像素数值取整来计算的。
这里所指的“标准字体”比较容易让人误会，这必须要搞清楚
在Excel当中有两处设置地方可以称之为标准字体ExcelHome LDY
1 是Application级的作用于整个Excel程序
影响范围是所有通过Excel新建的工作簿.
直观视觉效果就是行号列标中的字母和数字
需要重启Excel才能生效
手工设置方法
Excel菜单栏--》工具--》选项
在弹出的窗体中选测标准 标签 ，可以看到设置标准字体及字号的地方
VBA设置方法：
Application.StandardFont = "宋体"
Application.StandardFontSize = 12
ExcelHome LDY
2 是工作簿级的，只对本工作簿有效，这个才是关键因素
设置后不受Application.StandardFont改变的影响 ExcelHome LDY
手工设置方法
Excel菜单栏--》格式--》样式
在样式窗体中单击修改按钮
在单元格格式窗体中选择字体标签
可以设置 字体 字形 字号等属性
这些设置都会对列宽直接产生影响
直观视觉效果就是行号列标中的文字的变化
并且列宽也会立刻随之发生变化。
VBA设置方法：
ActiveWorkbook.Styles("Normal").Font.Name = "宋体"
ActiveWorkbook.Styles("Normal").Font.Size = 12
ActiveWorkbook.Styles("Normal").Font.Bold = False’设为加粗影响计算
ActiveWorkbook.Styles("Normal").Font.Italic = False'设为斜体影响计算
两个英文单词 Standard和Normal 都可以翻译为“标准”
不同版本的Excel默认安装的样式
简体版字体为 宋体 12 繁体版 是新明细体 12 英文版是 Times New Roman 12 （待验证）
这些都适用下面的公式
ExcelHome LDY
想要精确的计算列宽，必须先简单了解电脑系统中的度量单位
以下纯为个人推断总结，虽不中亦不远矣，也许某技术文档中提及过，错漏之处请指正。
现代工业从英国发展起步，他们的度量单位也随之普及。
电脑系统是由美国人发明的，而他们习惯的度量单位是英制的，
英制最小长度单位是英寸，而12英寸=1英尺
但对电脑显示屏来说中一英寸还是太大了，于是把英寸细分
这个细分过程既继承了12这个进制方法也混合了10进制
我杜撰了几个新名词 英分、英厘、英毫
一英寸=12英分=12*12英厘
当英厘也嫌大的时候，他们偷懒了，没按12进制走，而是直接用10进制了
1英厘=10英毫 ，1英寸=12*12*10=1440英毫
当然英分英厘英毫这些名称都没用，我杜撰的吗。
他们把英毫称之为缇，一英寸=1440缇
以下是MSDN中的资料
1 英寸 1440 缇
1 厘米 567 缇
1 垂直字符 240 缇
1 水平字符 120 缇
1 磅 20 缇
1 像素 15 缇
可以看出来除了厘米，其他度量单位都是可以和英寸取整的
1 英寸= 6 垂直字符
1 英寸= 12 水平字符
1 英寸= 72 磅
1 英寸= 96 像素
注： 像素换算是在显示器设置为正常尺寸96dpi情况下
这里指的字符应该是特指12号字，1平方英寸可以写12*6=72个字符
这符合英制以12为进位的习惯
ExcelHome LDY
Excel的帮助文档是这样描述的:
一个列宽单位等于“常规”样式中一个字符的宽度。对于比例字体，则使用字符“0”（零）的宽度。
再来看看对字体的解释
以磅 （磅：指打印的字符的高度的度量单位。1 磅等于 1/72 英寸，或大约等于 1 厘米的 1/28。）为单位返回或设置标准字体大小。Long 类型，可读写。
ExcelHome LDY
大多大数字体的“0”字符所占的宽度是其高度的一半
比如宋体12号字的0的宽度就是6磅，也就是8像素
我们可以用个简单的实验来验证一下
在常规样式中的字体以及单元格字体都为宋体12的时候
6磅*12=72磅 =1英寸
那么12个12号字的“0”连在一起就是一英寸。
在单元格中输入13个 "0"或=REPT("0",13)
然后画一个矩形，设置填充色为无，
切换到大小标签，中文版用户可以看到宽度框里的默认单位是 厘米
不用去算了，Excel很聪明的，直接清空，输入 1 英寸，确定
它接受了，并且再看其宽度已自动转换为2.54厘米
（这个小技巧也许之前有人发现过，但自己发现这么个小技巧，依然是很高兴的）
（输入 xx 磅 EXCEL也接受 ，其他office产品也是如此）
把这个矩形的左边和第一个0对齐，右边的边框刚好把第13个0隔离出去。
12个0刚刚好1英寸。这个实验成功。
然后再来设置列宽为12 ，也就是12个宋体“ 0” 的宽度
按道理这个列宽应该和矩形的宽度一样，应该也是1英寸（=96像素）
但实际并不是如此，第13个“0”犹有抱琵琶半遮面，并不是所希望的那样被隔离出去。
12列宽明显大于1英寸。
12列宽Excel的对应像素是101像素，不是计算得出的96像素，excel偷偷摸摸加了5个像素。
结合前文提到的“列宽是根据标准字体极其字号，按照像素数值取整来计算的。”
得到公式如下 Y=ROUND(ROUND(X*8,0)/8,2)
其中X是手工输入的列宽值，单位为字符，例如手工输入列宽为7.68
其中Y是Excel内部按照像素数值取整后返回的值 7.63
但是Y的单位依然是字符，既可显示7.63个“0”字符
要把字符换算为磅,不能简单的用水平字符*6来计算，别忘了Excel还+了5像素，要套用以下公式
K=ROUND(Y*8+5,0)*0.75
说明： 8 是12磅字的水平像素宽度，5 是excel添加的，X*8+5得到像素值，取整后乘以0.75得到磅值
以上公式反计算，可以根据希望得到的列宽磅值计算出最适合的字符值
Y=ROUND(ROUND(K/0.75-5,0)/8,2)
列宽小于1 的时候
Y=ROUND(ROUND(X*8,0)/8,2)
这个公式就不适用了
1字符的宽度Excel把它转换为13像素来显示
那么小于1字符列宽转换公式为
Y=ROUND(ROUND(X*13,0)/13,2)
这两个公式合并
Y=ROUND(ROUND(X*IF(X<1,13,8),0)/IF(X<1,13,8),2)
磅值和字符值的转换公式也要修正为
K=ROUND(X*IF(X<1,13,8)+IF(X<1,0,5),0)*0.75
Y=ROUND(ROUND(K/0.75-IF(K>9,5,0),0)/IF(K>9,8,13),2)
列宽各值转换
手工输入 --》自动修正 单位 字符
Y=ROUND(ROUND(X*IF(X<1,13,8),0)/IF(X<1,13,8),2)
列宽 字符 --》 磅
K=ROUND(X*IF(X<1,13,8)+IF(X<1,0,5),0)*0.75
列宽 字符 --》 像素
K=ROUND(X*IF(X<1,13,8)+IF(X<1,0,5),0)
列宽 磅 --》 字符
Y=ROUND(ROUND(K/0.75-IF(K>9,5,0),0)/IF(K>9,8,13),2)
按照公制厘米设置列宽行高
因为厘米不能和英寸取整，所以excel中不仅不能随意设置整数厘米的列宽
也不能随意设置整数厘米的其他属性，用户只能得到一个近似值（5的倍数除外）ExcelHome LDY
x = 2 '厘米
y = 2 * 567/20 '56.7 磅
z = Round(Round(y / 0.75 - IIf(y > 9, 5, 0), 0) / IIf(y > 9, 8, 13), 2)'8.88 字符
Columns(1).ColumnWidth = z
A列的列宽 近似于2厘米 = 2.01厘米
顺便说说行高。
帮助中的说明： 对于单个行，Height 属性的值等于 RowHeight 属性的值。然而，用 Height 属性还可返回整个单元格区域的高度。
这个解释有误。
Height 和 RowHeight 都称之为行高，和列宽一样都是按照像素取整进行自动调节的。
但是取整计算方式不同.由于差别不大，不易被发现，也许是一个BUG
用鼠标在工作表界面拖动调整行高，这两个属性值是一致的。
但用VBA代码去设置行高，或是调出行高设置框输入一个磅值，大多数情况下这两个属性值是不同的
如图输入10.1后，用VBA代码去读取这两个值
H = Rows(1).Height
RH = Rows(1).RowHeight
H = 9.75 ,RH = 10.25
ExcelHome LDY
行高的值可以通过下面的公式模拟计算
这样 Rows(1).Height 和 Rows(1).RowHeight的值一样
RH=INT((手工输入+0.07)/0.75)*0.75
Rows(1).RowHeight = RH
根据修正后的值可以依此设置正方形单元格。
附件中提供两个例子
精确设置列宽与行高.rar
(59.71 KB, 下载次数: 2116)
让单元格适应图片
设置正方形单元格 |
评分
-
2
查看全部评分
-
|
[复制链接]
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2009-10-26 02:50
发表于 2009-10-26 08:49
，版主做的更加精确了，谢谢版主的分享
。
