none
How to read a value from Excel by Script Task in SSIS? RRS feed

  • 问题

  • I try to read a value from Excel by Script Task in SSIS, but write is OK. Why?

    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.Office.Interop
    Imports Microsoft.Office.Interop.Excel
    Imports Microsoft.SqlServer.Dts.Runtime

    Public Class ScriptMain

    Public Sub Main()

            Dim oExcel As New Excel.Application
            Dim oBook As Excel.Workbook = oExcel.Workbooks.Open("C:\a.xls")
            Dim objSheet As Excel.Worksheet = CType(oBook.Worksheets(1), Excel.Worksheet)
            Dim a As Object

            objSheet.Cells(10, 10) = "ccc"         'it runs OK
               a = objSheet.Cells(1, 1).ToString     'it runs wrong

               a = objSheet.Cells(1, 1)                      'it runs also wrong

            oBook.Save()
            oBook.Close()

            oExcel.Quit()
            Dts.TaskResult = Dts.Results.Success
        End Sub

    End Class

    2011年7月1日 7:04

答案

  • 你好 gu0guest,

     

    看过你的代码之后,发现变量a被定义为Object,但是你的操作只是读取Excel文件中的一个单元格的数据,我建议你将a定义为String类型,然后通过下面的方法进行读取:

    Dim a As String

    a = objSheet.Range("A1").Value.ToString()

     

    objSheet.Range()中的A1表示Excel中的第一行第一列的单元格,以此类推,例如B2代表第二列第二行的单元格,字母代表列,数字代表行。

     

    Thanks,
    Weilin Qiao


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
    2011年7月6日 6:22
    版主