vb.net遍历xls / xlsx文件？

is there a simple way in vb.net to load an excel file and read it? perhaps there is a way to load the file but have it not be visible to the user?

在vb.net中有一个简单的方法来加载excel文件并读取它吗?也许有一种方法来加载文件,但让用户看不到它?

4 个解决方案

#1

Alex - here is some old code I dug up to query excel. Pretty basic scenario here. Don't pay attention to the poor error handling and lack of the 'Using' construct.

亚历克斯 - 这是我挖出来查询excel的一些旧代码。非常基本的场景。不要注意糟糕的错误处理和缺乏“使用”结构。

Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0" & _
                           ";Data Source=" & ExcelFile & _
                           ";Extended Properties=Excel 8.0;"

Dim conn As OleDbConnection = Nothing
Dim dt As System.Data.DataTable = Nothing
Dim excelDataSet As New DataSet()

Try

    conn = New OleDbConnection(connString)

    conn.Open()
    dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

    If dt Is Nothing Then
        Return Nothing
    End If

    Dim excelSheets(dt.Rows.Count) As String
    Dim i As Integer = 0
    For Each row As DataRow In dt.Rows
        excelSheets(i) = row("TABLE_NAME").ToString
        System.Math.Min(System.Threading.Interlocked.Increment(i), i - 1)
        If i = SheetNumber Then
            Exit For
        End If
    Next

    Using excelCommand As New OleDbCommand("Select * from [" & excelSheets(SheetNumber - 1) & "]", conn)
        Using excelAdapter As New OleDbDataAdapter(excelCommand)
            excelAdapter.Fill(excelDataSet)
        End Using
    End Using


Catch ex As OleDbException
    Throw
Catch ex As Exception
    Throw
Finally

    conn.Close()

    If conn IsNot Nothing Then
        conn.Dispose()
    End If

    If dt IsNot Nothing Then
        dt.Dispose()
    End If

End Try

Return excelDataSet

#2

Work with the Excel Object Model directly from .NET.

直接从.NET使用Excel对象模型。

#3

You can use ado.net to read from excel spreadsheets via the OLEDB JET 4 provider

您可以使用ado.net通过OLEDB JET 4提供程序从excel电子表格中读取

This just opens the excel file as a file stream instead of opening the actual excel spreadsheet.

这只是打开excel文件作为文件流而不是打开实际的Excel电子表格。

Alternatively you could use com-interop and simply set the application object visible property to false.

或者,您可以使用com-interop并简单地将应用程序对象visible属性设置为false。

See http://www.connectionstrings.com/excel

One gotcha to watch out for when using .net and com interop with any office application is that if you try opening the application as a user who is more of a windows service than an actual human user then you will need to login to windows as that user and open the relevant application as that user so that all the registry entries are correctly updated for certain features of the office application.

在使用.net和com与任何办公应用程序互操作时,需要注意的是,如果您尝试以比实际人类用户更多的Windows服务用户身份打开应用程序,那么您将需要登录到窗口,因为用户并以该用户身份打开相关应用程序,以便为办公应用程序的某些功能正确更新所有注册表项。

#4

If you have a more simple (ie small) excel spreadsheet that does not need to be dynamic, I think you could export it as a comma delimited file and then use a loop and streamreader object to parse each comma seperated value into an array.

如果你有一个更简单(即小)的excel电子表格,不需要是动态的,我认为你可以将它导出为逗号分隔文件,然后使用循环和streamreader对象将每个逗号分隔值解析为数组。

Kinda round about though...

有点围绕虽然......

#1