reading from excel sheet

[man_luck]

10.0.2614.0




I want to retrieve data from an excel sheet and display it in datagrid. I have the following code(not showing the declarations) :

con = New OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source=c:\CAD.xls;Extended Properties=""Excel 8.0""")
cmd = New OleDb.OleDbCommand("Select * from [delete2$]", con)
dataset.Clear()
Try
adap.SelectCommand = cmd
adap.Fill(dataset, "[delete2$]")
dg.DataSource = dataset.Tables(0).DefaultView
Catch exp As Exception
MsgBox(exp.Message)
End Try

the program is neither giving any error nor displaying the contents of the excel sheet.Please guide me how to display the contents of the excel sheet(File name is CAD.xls and the sheet name is delete2).

 

[i8088user]

The following works perfectly fine for me


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim DataSet As New DataSet

Dim con As OleDb.OleDbConnection = New OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source=c:\CAD.xls;Extended Properties=""Excel 8.0""")

Dim cmd = New OleDb.OleDbCommand("SELECT * FROM [Sheet1$]", con) 'Sheet1 = Wookbookname append "$"

Dim adap As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(cmd)

' DataSet.Clear()

Try

adap.SelectCommand = cmd

adap.Fill(DataSet)

DataGrid1.DataSource = DataSet.Tables(0).DefaultView

Catch exp As Exception

MsgBox(exp.Message)

End Try

End Sub

 

[man_luck]

insertion in excel sheet

ya now its working fine.thanx anyway

I would also like to insert data into a particular column but not able to do so.Kindly anyone assist me on this issue.

 

[i8088user]

youll need to write an Insert Command for the Data Adaptor... then use Table. NewRow adap.InsertCommand = New OleDb.OleDbCommand("INSERT * INTO [Sheet1$]")

With DataSet.Tables(0).NewRow()

'Remember Tables/Rows/Cols Start at offset 0

.Item("Col0") = "CAT"

.Item(1) = "DOG"

End With

Wit my luck i botched the SQL syntax however... the col names, are the values on row 1. A:1 is the name for col A...

 

[mattcya]

Putting spreadsheet column info into a label

The code you put down works beautifually but what would i have to do to put each column field into a label box

e.g: Label1 = Firstname info, label 2 =lastname info???

Cheers,

Mattcya