What name space to use in VB.net

问题:

Writing a vb.net script(as a part of SSIS ETL) to convert xls to tsv file.I was trying to use the name space Imports Microsoft.Excel to include the below codes .But ,it show there is no such name space! What name space to be included to use the Excel open close and save as functionality as a part of the vb.net oExcel.Workbooks.Open oBook.SaveAs(sTsvPath, -4158)

The vb.net code is

Public Sub Main()

        Dim oExcel As Object
        Dim oBook As Object

        Dim sFileName As String
        Dim sFileNameOnly As String


        Dim sXlsPath As String
        Dim sTsvPath As String



        sFileName = CStr(Dts.Variables("User::Xls_File_Name").Value)


        sXlsPath = "H:\Xls_Files\" + sFileName

        sFileNameOnly = Path.GetFileNameWithoutExtension(sFileName)

        sTsvPath = "H:\Xls_Files\" + sFileNameOnly + ".Txt"


        oExcel = CreateObject("Excel.Application")


        oBook = oExcel.Workbooks.Open(sXlsPath)

        oBook.SaveAs(sTsvPath, -4158)

        oBook.Close(False)

        oExcel.Quit()

        Dts.TaskResult = ScriptResults.Success
    End Sub

回答1:

First you need to add a reference to the Microsoft Excel 15.0 Object Library in the Solution Explorer pane. It appears in the COM objects tab when you choose "Add reference..." - your version number (e.g. 15.0) may be different.

Then in the code you have to add Imports Microsoft.Office.Interop.Excel, like this:

Option Infer On
Option Strict On

Imports System.IO
Imports Microsoft.Office.Interop.Excel

Module Module1

    Sub Main()

        Dim srcDir = "C:\temp"
        Dim srcFilename = "somefile.xls"
        Dim destFile = Path.Combine(srcDir, Path.GetFileNameWithoutExtension(srcFilename) & ".txt")

        File.Delete(destFile)

        Dim excel As Application = Nothing
        Dim wb As Workbook = Nothing

        Try
            excel = New Application
            wb = excel.Workbooks.Open(Path.Combine(srcDir, srcFilename))
            wb.SaveAs(destFile, XlFileFormat.xlCurrentPlatformText)

        Finally
            If wb IsNot Nothing Then
                wb.Close()
            End If
            If excel IsNot Nothing Then
                excel.Quit()
            End If

            ' see "The proper way to dispose Excel com object using VB.NET?"
            ' http://stackoverflow.com/a/38111107/1115360 for an explanation of the following:
            GC.Collect()
            GC.WaitForPendingFinalizers()
            GC.Collect()
            GC.WaitForPendingFinalizers()

        End Try

    End Sub

End Module

You will have to add in the DTS-related parts.

I used Path.Combine(srcDir, Path.GetFileNameWithoutExtension(srcFilename) & ".txt") for brevity rather than using Path.GetExtension and Path.ChangeExtension, which you would do in better-quality code. Also, you should wrap the File.Delete in a Try..Catch with an appropriate action in the Catch just in case something goes wrong there.