Excel Question

[SIMIN]

Hello all,

I just found a great sample code in MSDN which allows me to read data from Excel files.

But however, this is the 1st time I am going to do this, and cannot customize it exactly.

I want to open an Excel file, loop through all sheets and read all cells, rows and columns of each sheet one by one.

 

I have 2 samples here, taken from MSDN, but I cannot customize them to go through and show all cells/rows/columns of all sheets!

Please help me if you can :) Thanks.

 

Dim conn1 As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\ExcelData1.xls;Extended Properties=""Excel 8.0;HDR=YES""")
conn1.Open()
Dim cmd1 As New System.Data.OleDb.OleDbCommand("Select * From [EmployeeData$]", conn1)
Dim rdr As OleDbDataReader = cmd1.ExecuteReader
Do While rdr.Read()
   Debug.WriteLine(System.String.Format("{0,-10}{1, -15}{2}", _
      rdr.GetString(0), rdr.GetString(1), _
      rdr.GetDateTime(2).ToString("d")))
Loop
rdr.Close()
conn1.Close()

 

Dim conn2 As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\ExcelData2.xls;Extended Properties=""Excel 8.0;HDR=YES""")
Dim da As New OleDbDataAdapter("Select * From [inventoryData$]", conn2)
Dim ds As DataSet = New DataSet()
da.Fill(ds)
Dim dr As DataRow
For Each dr In ds.Tables(0).Rows 'Show results in output window
   Debug.WriteLine(System.String.Format("{0,-15}{1, -6}{2}", _
      dr("Product"), dr("Qty"), dr("Price")))
Next
conn2.Close()

[mandelbrot]

Hi Simin,

 

 

I think this is what you're looking for.

 

Oh, and while I think on, by changing what would be the table name in the SQL string, you're actually changing your worksheet in excel:

SELECT * FROM [myExcelWorksheetName]

Then it's simply a case of moving through each of the rows (see the link that I've put in above).

 

 

Paul.

[SIMIN]

Hi,

The problem is that I don't know what are the name of sheets/worksheets!

[mandelbrot]

Maybe this will help you - it's not entirely related to what you're doing, but it may point you in an alternate direction...

Excel COM object

 

Kind regards,

Paul.