Question Trying to pull a row from an xls document into an array

[-=EQ=-]

My first time back here in ages. This site has gotten far bigger (and better) since I was last here. Couldn't remember my old username so I made a new one.

Here's my situation:

I'm a Sys Admin, currently making an app that will create large amounts of users. The lists come in on xls files in 3 different formats but the data is the same.

I need an example on how to do this:

From what I can tell, I need to first open the xls file. Then count the rows (or do X until cell 1 = nothing. Then pull the first row in as an array. Process my code on the data in that array, empty the array and grab the next row.

Thanks in advance guys!

 

[cjard]

xls.. but what kind of xls? Open it with a hex editor and have a look

If it's XML, that would be nice as you dont need office installed. If its excel binary, you'll need to use COM Interop to create an instance of excel in memory and then pull the data out of it, unless you are able to purchase a library that can read/write xls binary files without

 

[MattP]

In situations like this I use the Jet.OLEDB engine for 2003 & previous files or ACE.OLEDB for 2007 to load the data into a dataset.

 

[-=EQ=-]

Does it really need to be this complicated? I just want to load a row into an array, not marry it. I dont think I want the commitment involved in such a small part of the overall program.

I'm trying to base it off this code...

         Dim xlApp As Excel.Application = New Excel.ApplicationClass
        Dim xlWorkBook As Excel.Workbook = xlApp.Workbooks.Open("c:\test1.xls")
        Dim xlWorkSheet As Excel.Worksheet = xlWorkBook.Worksheets("sheet1")

        'display the cells value B2
        MsgBox(xlWorkSheet.Cells(2, 2).Value)
        'edit the cell with new value
        xlWorkSheet.Cells(2, 2) = "http://vb.net-informations.com"
        xlWorkBook.Close()
        xlApp.Quit()

if I can read one cell, I should be able to read a row. Right?

 

[MattP]

Just trying to make your life easier.

If you insist on doing in with Interop you'll want to look up Excel.Range and Range.Offset.

 

[cjard]

I second MattP's suggestion; I clean forgot about Jet being able to read excel files. You should be able to do this:

    Dim x As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\hl.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'")
    Dim y As New OleDb.OleDbDataAdapter("SELECT * FROM [Sheet1$]", x)
    Dim z As New DataSet()
    y.Fill(z)