如何使用Excel VBA实现粘贴HTML数据并转换为规范表格展示

前置操作步骤 #

• 打开目标Excel文件，按Alt+F11进入VBA编辑界面
• 在左侧工程资源管理器右键点击当前工作簿，依次选择「插入」-「用户窗体」
• 向窗体中添加2个控件：
  • 多行文本框：在属性面板将MultiLine参数设为True，调整大小适配HTML代码粘贴
  • 命令按钮：将Caption属性设为「解析生成表格」

核心解析代码 #

双击窗体上的命令按钮，粘贴以下代码即可：

Private Sub CommandButton1_Click()
    Dim htmlObj As Object
    Dim tableObj As Object
    Dim trObj As Object, tdObj As Object
    Dim i As Integer, j As Integer
    Dim targetSheet As Worksheet
    
    ' 初始化HTML解析对象
    Set htmlObj = CreateObject("htmlfile")
    ' 补全缺失的HTML根标签，解决系统输出不规范的问题
    htmlObj.write "<html><body>" & Me.TextBox1.Text & "</body></html>"
    
    ' 定位第一个table标签，如有多个表格可调整索引
    Set tableObj = htmlObj.getElementsByTagName("table")(0)
    
    ' 选择输出的工作表，这里默认输出到当前工作簿的Sheet1，可自行修改
    Set targetSheet = ThisWorkbook.Sheets("Sheet1")
    targetSheet.Cells.Clear ' 清空原有内容
    
    ' 遍历HTML表格的行和列，写入Excel
    i = 1
    For Each trObj In tableObj.getElementsByTagName("tr")
        j = 1
        For Each tdObj In trObj.getElementsByTagName("td")
            targetSheet.Cells(i, j) = tdObj.innerText
            ' 保留原表格合并单元格属性
            If tdObj.colSpan > 1 Then targetSheet.Cells(i, j).Resize(1, tdObj.colSpan).Merge
            If tdObj.rowSpan > 1 Then targetSheet.Cells(i, j).Resize(tdObj.rowSpan, 1).Merge
            j = j + tdObj.colSpan
        Next tdObj
        i = i + 1
    Next trObj
    
    ' 自动调整列宽适配内容
    targetSheet.UsedRange.EntireColumn.AutoFit
    
    ' 若需要在消息框输出，替换上面的写入逻辑为以下代码即可：
    ' Dim outputStr As String
    ' For Each trObj In tableObj.getElementsByTagName("tr")
    '     For Each tdObj In trObj.getElementsByTagName("td")
    '         outputStr = outputStr & tdObj.innerText & vbTab
    '     Next
    '     outputStr = outputStr & vbCrLf
    ' Next
    ' MsgBox outputStr, vbInformation, "解析结果"
    
    Unload Me ' 关闭窗体
    MsgBox "表格解析完成", vbInformation
End Sub

使用说明 #

• 按F5运行窗体，将系统导出的HTML表格代码粘贴到文本框中
• 点击「解析生成表格」按钮即可自动在指定工作表生成规范的可视化表格
• 如果不需要写入工作表，可注释掉工作表写入的代码，启用消息框输出的代码段即可

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