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!

rodrigocf
  • 1,951
  • 13
  • 39
  • 62
  • 1
    Use ADO.net and the OleDb provider with Excel extension. – Mary Oct 17 '18 at 18:15
  • C#:Using Excel OleDb to get sheet names IN SHEET ORDER https://stackoverflow.com/questions/1164698/using-excel-oledb-to-get-sheet-names-in-sheet-order – IvanH Oct 18 '18 at 16:33

1 Answers1

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?
TonyE
  • 319
  • 2
  • 4