vb.net traversing an xls / xlsx file?

Question

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?

Answer 1

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

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

Alternatively you could use com-interop and simply set the application object visible property to 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.

Answer 2

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.

Kinda round about though...

Answer 3

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.

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

Answer 4

Work with the Excel Object Model directly from .NET.