如何在SSIS数据流源VB脚本中检测Excel空白单元格？

问题根源 #

你遇到的Object reference not set to an instance of an object错误，核心原因是空白Excel单元格的Value属性返回Nothing（VB的空引用），而非空字符串。直接调用.ToString()时，因为对象本身不存在，自然触发空引用异常。String.IsNullOrEmpty无效的原因是：它只能判断字符串是否为空，但你还没把Value转成字符串就已经报错了——这个方法无法处理空引用本身。

解决方案 #

最可靠的处理方式是先判断单元格Value是否为Nothing，再决定返回空字符串或调用.ToString()。推荐封装一个辅助函数，避免重复写判断逻辑，提升代码可读性。

修改后的完整脚本 #

#Region "Imports"
Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
#End Region

Public Class ScriptMain
    Inherits UserComponent

    ' 辅助函数：安全读取Excel单元格值为字符串
    Private Function GetCellValueAsString(rng As Excel.Range) As String
        If rng.Value Is Nothing Then
            Return String.Empty
        Else
            ' 可选添加Trim()去除单元格前后空格
            Return rng.Value.ToString().Trim()
        End If
    End Function

    Public Overrides Sub CreateNewOutputRows()
        Dim XL As Excel.Application = Nothing
        Dim WorkBooks As Excel.Workbooks = Nothing
        Dim CreditNote As Excel._Workbook = Nothing
        Dim Sheets As Excel.Sheets = Nothing
        Dim Sheet As Excel._Worksheet = Nothing
        Dim CreditNoteFile As FileInfo = Nothing

        Dim CreditNotesFolder As New DirectoryInfo(Variables.CreditNotesFolder)
        Dim ListOfCreditNotes As FileInfo() = CreditNotesFolder.GetFiles("*.XL*")

        XL = New Excel.Application
        ' 隐藏Excel界面，提升运行效率
        XL.Visible = False
        XL.DisplayAlerts = False
        WorkBooks = XL.Workbooks

        For Each CreditNoteFile In ListOfCreditNotes
            If CreditNoteFile.Name.StartsWith("~$") Then
                Continue For
            End If

            CreditNote = WorkBooks.Open(CreditNoteFile.FullName)
            Sheets = CreditNote.Worksheets
            Sheet = CType(Sheets(1), Excel.Worksheet)

            OutputBuffer.AddRow()
            OutputBuffer.Filename = CreditNoteFile.Name
            ' 使用辅助函数安全读取单元格
            OutputBuffer.AccountName = GetCellValueAsString(Sheet.Range("C6"))
            OutputBuffer.CreditReason = GetCellValueAsString(Sheet.Range("C12"))
            OutputBuffer.CreditReasonOther = GetCellValueAsString(Sheet.Range("C13"))

            ' 关闭工作簿时不保存
            CreditNote.Close(SaveChanges:=False)
            ' 释放当前文件的COM资源
            System.Runtime.InteropServices.Marshal.ReleaseComObject(Sheet)
            System.Runtime.InteropServices.Marshal.ReleaseComObject(Sheets)
            System.Runtime.InteropServices.Marshal.ReleaseComObject(CreditNote)
        Next CreditNoteFile

        OutputBuffer.SetEndOfRowset()

        ' 彻底释放Excel资源，避免进程残留
        WorkBooks.Close()
        System.Runtime.InteropServices.Marshal.ReleaseComObject(WorkBooks)
        XL.Quit()
        System.Runtime.InteropServices.Marshal.ReleaseComObject(XL)
        GC.Collect()
        GC.WaitForPendingFinalizers()
    End Sub
End Class

额外优化说明 #

1. 添加了XL.Visible = False和XL.DisplayAlerts = False，避免运行时弹出Excel窗口和提示框；
2. 增加了COM资源的释放逻辑，防止Excel进程在后台残留；
3. 关闭工作簿时指定SaveChanges:=False，避免误修改源文件。

替代方案（内联判断） #

如果不想封装函数，也可以直接用VB的短路If运算符（VB2015及以上支持）：

OutputBuffer.AccountName = If(Sheet.Range("C6").Value Is Nothing, String.Empty, Sheet.Range("C6").Value.ToString().Trim())

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