导出DataTable到Excel时出现0x800A03EC错误如何解决

核心问题（仅显示表头+第一个单元格值） #

导致该问题的错误有3处：

• 数组赋值代码末尾多余的(1)：ws.Range(xxx)(1).Value = arr等价于仅给选中范围的第一个单元格赋值，数组内容没有批量写入整个范围
• 数据写入范围计算错误：你要从第2行开始写入dt.Rows.Count行数据，结束行应为dt.Rows.Count + 1，原代码的结束行dt.Rows.Count会少写入最后一行数据
• 数组维度声明冗余：VB.NET中Dim arr(a,b)的索引范围是0_a、0b，存储dt的dt.Rows.Count行数据仅需要声明到dt.Rows.Count-1，原声明多了一行空元素

原SaveAs报错0x800A03EC的常见原因 #

• 保存路径不存在、无写入权限，或者目标文件已经被其他程序打开占用
• 未指定SaveAs的必填格式参数：后缀为xlsx时需要指定FileFormat:=Excel.XlFileFormat.xlOpenXMLWorkbook，避免版本不兼容报错
• 调用Excel COM时未使用1-based索引操作工作表/单元格（该问题你已修复）

修复后代码 #

主逻辑部分 #

Dim app As New Excel.Application
' 隐藏Excel窗口避免弹窗
app.Visible = False
Dim wb As Excel.Workbook = app.Workbooks.Add()
Dim ws As Excel.Worksheet
Dim strFN As String = "MyFileName.xlsx"   
Dim dt As New DataTable

Using da As New DataAdapter(dif)
    da.SetSelectCommand(SQL)
    da.Fill(dt)
End Using

ws = CType(wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count)), Excel.Worksheet)
DataTableToExcel(dt, ws, "TableName")

' 修复SaveAs调用，指定格式参数
wb.SaveAs(path, FileFormat:=Excel.XlFileFormat.xlOpenXMLWorkbook)
wb.Close(SaveChanges:=False)
app.Quit()
' 释放COM对象避免后台残留Excel进程
System.Runtime.InteropServices.Marshal.ReleaseComObject(ws)
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb)
System.Runtime.InteropServices.Marshal.ReleaseComObject(app)

DataTableToExcel方法修复 #

Private Sub DataTableToExcel(dt As DataTable, ws As Excel.Worksheet, TabName As String)
    ' 修复数组维度声明
    Dim arr(dt.Rows.Count - 1, dt.Columns.Count - 1) As Object
    Dim r As Int32, c As Int32

    For r = 0 To dt.Rows.Count - 1
        For c = 0 To dt.Columns.Count - 1
            arr(r, c) = dt.Rows(r).Item(c)
        Next
    Next

    ws.Name = TabName   
    c = 0

    For Each column As DataColumn In dt.Columns
        If column.ColumnName.Length > 4 AndAlso column.ColumnName.EndsWith("_ID") Then
            ws.Cells(1, c + 1) = column.ColumnName.Replace("_", " ").Substring(0, column.ColumnName.Length - 4)
        Else
            ws.Cells(1, c + 1) = column.ColumnName.Replace("_", " ")
        End If
        c += 1
    Next

    ' 修复范围计算，去掉多余的(1)，加空表判断避免报错
    If dt.Rows.Count > 0 Then
        ws.Range(ws.Cells(2, 1), ws.Cells(dt.Rows.Count + 1, dt.Columns.Count)).Value = arr
    End If
End Sub

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