0

I want to read an excel file with vb.net and get a list of lists of strings based on the data it has. The data will always be structured as a table with 5 columns and N number of rows with the first row being a header.

This will be done in a Windows forms app. I have seen examples of this being done assuming that the person has Excel installed. However, I must account for the fact that they might not have it.

How do I do this in vb.net? If I were using python I know I could just simply use Pandas and be done with it quite simple. Is there a simple way of doing this?

Thanks!

2
  • 1
    Use ADO.net and the OleDb provider with Excel extension. Commented Oct 17, 2018 at 18:15
  • C#:Using Excel OleDb to get sheet names IN SHEET ORDER stackoverflow.com/questions/1164698/… Commented Oct 18, 2018 at 16:33

1 Answer 1

0

I use the datatable to extract the sheetname, as in my application, users were uploading all sorts of sheets and I didn't know the sheetname. You probably can skip that step and start with the "dim cmd"

    Dim FileName As String = Application("WebRoot") & "\uploads\1.xlsx"
    Dim cn As OleDbConnection = New OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & FileName & "';Extended Properties=""Excel 8.0;HDR=NO;IMEX=1"";")
    cn.Open()
    Dim dt As DataTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
    cn.Close()
    cn = Nothing
    Dim Source As String = dt.Rows(0)!TABLE_NAME.ToString().Replace("'", "")
    dt.Dispose()
    dt = Nothing
   'all the above just to get the sheetname (source)

    Dim cmd As New OleDbCommand
    cmd.Connection = New OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & FileName & "';Extended Properties=""Excel 8.0;HDR=No"";")        
    cmd.CommandText = "select * from [" & Source & "]"
    cmd.Connection.Open()
    Dim sb As New StringBuilder
    Dim rd As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
    rd.Read 'skip the header row
    Do While rd.Read
       '0 is columnA, 1 is columnB, and so on
       'so below will append the text in columnB to the stringbuilder
       sb.Append(rd(1).toString & VbCrLf)
    Loop
    'Not sure what you want to do with the string?
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.