ClosedXML转iTextSharp PDF时单元格文本适配异常问题排查

我用ClosedXML读取Excel、iTextSharp生成PDF时遇到文本字体适配问题：明明文本能放进单元格，但禁用缩放后仍被错误调整字号。相关代码如下：

Dim colWidthsExcel As New List(Of Double)
For c As Integer = firstColNum To lastColNum
    Dim widthCol As Double = worksheet.Column(c).Width
    If widthCol <= 0 Then widthCol = 1
    colWidthsExcel.Add(widthCol)
Next

Dim sumWidths As Double = colWidthsExcel.Sum()
Dim availablePageWidth As Single = document.PageSize.Width - document.LeftMargin - document.RightMargin

Dim colWidthsPoints(colCount - 1) As Single
For i As Integer = 0 To colCount - 1
    colWidthsPoints(i) = CSng((colWidthsExcel(i) / sumWidths) * availablePageWidth)
Next

For row As Integer = firstRowNum To lastRowNum
    For col As Integer = firstColNum To lastColNum
        Dim cell = worksheet.Cell(row, col)
        If cell Is Nothing Then Continue For

        Dim idx = col - firstColNum
        Dim cellWidth As Single = 40.0F

        If idx >= 0 AndAlso idx < colWidthsPoints.Length Then
            If cell.IsMerged() Then
                Dim mRange As IXLRange = cell.MergedRange()
                Dim spanCols As Integer = Math.Max(1, mRange.ColumnCount())
                cellWidth = 0
                For k As Integer = idx To Math.Min(idx + spanCols - 1, colWidthsPoints.Length - 1)
                    cellWidth += colWidthsPoints(k)
                Next
            Else
                cellWidth = colWidthsPoints(idx)
            End If
        End If

        Dim cellPadding As Single = 4.0F ' Padding interno della cella PDF (2 punti per lato)
        Dim availableWidth As Single = cellWidth - cellPadding

        Dim text As String = cell.GetFormattedString().Trim()

        Dim fx = cell.Style.Font
        Dim cellsize As Single = cell.Style.Font.FontSize * scaleFactor
        Dim pdfFont As iTextSharp.text.Font = getFont(cell, cellsize)
        Dim baseFont As iTextSharp.text.pdf.BaseFont = pdfFont.BaseFont
        If baseFont Is Nothing Then
            baseFont = iTextSharp.text.pdf.BaseFont.CreateFont(iTextSharp.text.pdf.BaseFont.HELVETICA, iTextSharp.text.pdf.BaseFont.CP1252, iTextSharp.text.pdf.BaseFont.NOT_EMBEDDED)
        End If

        Dim size As Single = GetFittingFontSize_SingleLine(baseFont:=baseFont, text:=text, cellSize:=cellsize, availableWidth:=availableWidth, isWrapCell:=cell.Style.Alignment.WrapText)
Next
Next

Private Function GetFittingFontSize_SingleLine(baseFont As iTextSharp.text.pdf.BaseFont, text As String, cellSize As Single, availableWidth As Single, isWrapCell As Boolean, Optional minSize As Single = 2.0F, Optional stepSize As Single = 0.5F) As Single
    If String.IsNullOrEmpty(text) OrElse isWrapCell Then
        Return cellSize
    End If
    Dim size As Single = cellSize
    While size > minSize
        getMinValuelStep05(size)
        Dim textWidth As Single = baseFont.GetWidthPoint(text, size)
        If textWidth <= availableWidth Then Exit While
        size -= stepSize
    End While
    If size < minSize Then size = minSize
    Return size
End Function

核心问题排查 #

1. 列宽转换逻辑误差 #

• Excel列宽是字符宽度单位（默认基于10号Arial字符宽度），并非直接对应PDF的磅值，你当前用的比例分配法会导致PDF单元格宽度和Excel实际显示宽度不匹配，进而误判文本是否超出。
• 合并单元格宽度计算存在边界风险：若合并列超出你处理的lastColNum范围，Math.Min的截断会导致宽度计算不足，让文本看起来“超出”单元格。

2. 字体尺寸计算的逻辑漏洞 #

• getMinValuelStep05(size)函数未定义，直接打乱了字号递减的循环逻辑，导致字号计算完全错误。
• 初始字号用cell.Style.Font.FontSize * scaleFactor，但scaleFactor来源不明确，若不是基于Excel到PDF的精准缩放比例，会导致初始字号偏差。
• text = cell.GetFormattedString().Trim()会移除文本前后空格，而Excel中这些空格可能是有意保留的，会导致文本实际宽度计算失真。

3. 换行判断的潜在问题 #

• 若ClosedXML读取的cell.Style.Alignment.WrapText值不准确（比如合并单元格的换行设置未取到正确值），会导致本该换行的文本被错误缩放。

修复方案 #

1. 修正Excel列宽到PDF磅值的转换 #

用ClosedXML官方推荐的列宽转磅值公式，先把Excel列宽转成实际磅值，再按比例分配页面宽度：

' Excel列宽转PDF磅值（适配默认字体逻辑）
Function ExcelColumnWidthToPoints(width As Double, fontSize As Single) As Single
    Return CSng((width * fontSize) / 10 * 8.43)
End Function

在收集colWidthsExcel时，直接转换为磅值：

Dim widthCol As Double = worksheet.Column(c).Width
If widthCol <= 0 Then widthCol = 1
' 用单元格默认字号做换算，或取Excel默认字号10
colWidthsExcel.Add(ExcelColumnWidthToPoints(widthCol, 10))

2. 修复字号计算逻辑 #

• 移除未定义的getMinValuelStep05(size)调用，保留原始的字号递减逻辑。
• 验证scaleFactor：若无需全局缩放，直接设为1，保证初始字号和Excel一致。
• 去掉Trim()，保留文本原始空格：Dim text As String = cell.GetFormattedString()

3. 完善合并单元格宽度计算 #

确保只计算处理范围内的合并列：

If cell.IsMerged() Then
    Dim mRange As IXLRange = cell.MergedRange()
    Dim startCol = mRange.FirstColumn().ColumnNumber()
    Dim endCol = mRange.LastColumn().ColumnNumber()
    ' 仅统计当前处理列范围内的合并列数
    Dim spanCols = Math.Max(1, Math.Min(endCol, lastColNum) - Math.Max(startCol, firstColNum) + 1)
    cellWidth = 0
    For k As Integer = idx To idx + spanCols - 1
        cellWidth += colWidthsPoints(k)
    Next
End If

4. 验证WrapText读取准确性 #

合并单元格的换行设置需取合并范围的第一个单元格：

Dim isWrapCell As Boolean = cell.Style.Alignment.WrapText
If cell.IsMerged() Then
    isWrapCell = cell.MergedRange().FirstCell().Style.Alignment.WrapText
End If

内容的提问来源于stack exchange，提问作者gianluca23