在VB.net中使用什么名称空间 [英] What name space to use in VB.net

问题描述

编写一个vb.net脚本(作为SSIS ETL的一部分)将xls转换为tsv文件.我试图使用该名称 空格Imports Microsoft.Excel包含以下代码.但是，它表明没有 这样的名字空间！使用Excel将要包含的名称空间打开关闭并另存为 功能作为vb.net的一部分 oExcel.Workbooks.Open oBook.SaveAs(sTsvPath，-4158)

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)

vb.net代码是

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

推荐答案

首先，您需要在解决方案资源管理器"窗格中添加对Microsoft Excel 15.0对象库的引用.当您选择添加参考..."时，它会显示在COM对象标签中-您的版本号(例如15.0)可能会有所不同.

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.

然后在代码中必须添加Imports Microsoft.Office.Interop.Excel，如下所示:

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

您将不得不添加与DTS相关的部分.

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

为了简洁起见，我使用Path.Combine(srcDir, Path.GetFileNameWithoutExtension(srcFilename) & ".txt")而不是使用

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.

这篇关于在VB.net中使用什么名称空间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！