最近更新于 2025-01-31 10:58
测试环境 Testing Environment
- office 2024
- Python 3.13.1
- openpyxl 3.1.5
描述 Description
下面的代码，会将 1 到 10 列的列宽依次设置为 10, 10.62, 10.7, 15, 20.8, 30, 35.34, 39.57, 40.20, 57.3，将 1 到 10 行的行高也依次设置为这些数值大小，最终将文件保存到 test.xlsx 文件中
The following code sets the column widths of columns 1 to 10 to 10, 10.62, 10.7, 15, 20.8, 30, 35.34, 39.57, 40.20, and 57.3, respectively. It also sets the row heights of rows 1 to 10 to these values. Finally, it saves the file as test.xlsx.
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
workbook = Workbook()
sheet = workbook.active
widthList = [10, 10.62, 10.7, 15, 20.8, 30, 35.34, 39.57, 40.20, 57.3]
heightList = widthList
for i in range(1, len(widthList) + 1):
sheet.column_dimensions[get_column_letter(i)].width = widthList[i - 1]
for i in range(1, len(heightList) + 1):
sheet.row_dimensions[i].height = heightList[i - 1]
workbook.save('test.xlsx')下面是 Excel 中查看的结果
Here are the results viewed in Excel.
| 设置的大小 | Excel中列宽width | Excel中行高height |
|---|---|---|
| 10 | 9.38 | 10 |
| 10.62 | 10 | 10.75 |
| 10.7 | 10.13 | 10.75 |
| 15 | 14.38 | 15 |
| 20.8 | 20.13 | 20.75 |
| 30 | 29.38 | 30 |
| 35.34 | 34.75 | 35.25 |
| 39.57 | 39 | 39.5 |
| 40.20 | 39.63 | 40.25 |
| 57.3 | 56.63 | 57.5 |
最开始我是估测后单元格写指定内容要多大，但是用 openpyxl 写表格却出现不够的情况，然后就多找了一些例子发现确实际和设置的不一致。
用中文搜只找到 CSDN 有位提到使用 openpyxl 发现实际列宽比设置的小 0.62，但上面的例子其实就能推翻。我试过设置整数大小，确实实际都比设置的小 0.62，但是也是有不同的。不是所有都是 0.62 的差值。
我用英语描述去谷歌搜，和托管这个项目的 issues 去搜，都没找到提同样问题的。可能是我描述问题，或者有没有可能和特殊的语言相关（单元格大小或许是按照特定的字体进行计算的），在使用英语的设备上，或许设置的大小就和实际匹配了。
Initially, I estimated the size needed for the content in each cell and set the column widths and row heights accordingly using openpyxl. However, I found that the actual sizes in Excel did not match the settings, and the content often did not fit properly. After searching online, I found a post on CSDN mentioning that the actual column width in Excel is 0.62 units smaller than the set value when using openpyxl. However, this example actually disproves that statement. I tested with integer values and found that the actual width is indeed 0.62 units smaller in some cases, but this difference is not consistent across all settings. The discrepancy is not always 0.62 units.
When I searched in English on Google and checked the issues on the project’s repository, I couldn’t find anyone else reporting the same problem. Perhaps my description of the issue was not clear enough, or maybe there is a language-specific factor involved. It’s possible that the cell size calculations are based on specific fonts, and on devices using English settings, the set sizes might match the actual sizes in Excel.
研究 Research
Test 1
下面的例子，设置 5000 列单元格的宽度和 5000 行单元格的高度，相邻单元格大小差异 0.01，设置完成后保存到文件 test.xlsx。然后使用 openpyxl 读取保存的文件，依次去读取宽度和高度值，最后和设置值一起打印出来进行对比。
The following example sets the widths of 5000 columns and the heights of 5000 rows, with a size difference of 0.01 between adjacent cells. After setting these values, the file is saved as test.xlsx. Then, using openpyxl, the saved file is read to retrieve the width and height values. Finally, these retrieved values are printed alongside the set values for comparison.
from openpyxl import Workbook, load_workbook
from openpyxl.utils import get_column_letter
workbook = Workbook()
sheet = workbook.active
sizeList = [ round(0.01 * i, 2) for i in range(1, 5000)]
for i in range(1, len(sizeList) + 1):
sheet.column_dimensions[get_column_letter(i)].width = sizeList[i - 1]
for i in range(1, len(sizeList) + 1):
sheet.row_dimensions[i].height = sizeList[i - 1]
workbook.save('test.xlsx')
workbook = load_workbook('test.xlsx')
sheet = workbook.active
widthList = []
heightList = []
for i in range(1, len(sizeList) + 1):
widthList.append(
sheet.column_dimensions[get_column_letter(i)].width
)
for i in range(1, len(sizeList) + 1):
heightList.append(
sheet.row_dimensions[i].height
)
for size, width, height in zip(sizeList, widthList, heightList):
print(f'{size} {width} {height}')从打印的结果看，用 openpyxl 读取出来和设置的值是一致的，但是打开 Excel 查看就不一致了，现在推测 openpyxl 里计算行高和列宽的方式可能和 Excel 不同。
From the printed results, the values read using openpyxl are consistent with the set values. However, when opening the file in Excel, the values do not match. It is now speculated that the way openpyxl calculates row heights and column widths might be different from that of Excel.
Test 2
上一个例子用 openpyxl 设置单元格保存了文件，这里就改成用 Excel 的 COM 接口读取 Excel 文件进行对比，结果保存到 test.txt 文件中。
In the previous example, the file was saved using openpyxl to set the cell dimensions. Here, we will use Excel’s COM interface to read the Excel file for comparison, and the results will be saved to a test.txt file.
import win32com.client as wc
import os
app = wc.Dispatch('Excel.Application')
app.Visible = False
app.DisplayAlerts = False
filePath = os.path.join(
os.path.dirname(os.path.abspath(__file__)),
'test.xlsx'
)
workbook = app.Workbooks.Open(filePath)
sheet = workbook.Worksheets('Sheet')
sizeList = [ round(0.01 * i, 2) for i in range(1, 5000)]
widthList = []
heightList = []
for i in range(1, len(sizeList) + 1):
widthList.append(
sheet.Columns(i).ColumnWidth
)
heightList.append(
sheet.Rows(i).RowHeight
)
workbook.Close(False)
app.Quit()
with open('test.txt', 'w', encoding='utf-8') as f:
for size, width, height in zip(sizeList, widthList, heightList):
f.write(f'{size} {width} {height}\n')下面就是保存的文件，第一列是设置的，第二列是实际列宽，第三列是实际行高，确实 openpyxl 和 Excel 的不一致
Here is the saved file, where the first column represents the set values, the second column represents the actual column widths, and the third column represents the actual row heights. It is evident that there is inconsistency between the values set by openpyxl and those displayed in Excel.
https://pan.baidu.com/s/1yDcLKBy61uVXu0W8R2ERnQ?pwd=i868
规律 Pattern
上面的 5000 个行高和列宽的例子，我看了一下。
Excel 中实际的行高必然是 0.25 的整数倍，但是没看出数值舍入的具体规律。
列宽的小数部分必然是 .0、.13、.25、.38、.5、.63、.75、.88 中的一种。
After examining the example with 5,000 row heights and column widths, I noticed the following:
The actual row heights in Excel are always multiples of 0.25. However, I couldn’t discern a specific rounding pattern for the values.
The decimal parts of the column widths are always one of the following: .0, .13, .25, .38, .5, .63, .75, .88.