reading from excel sheet

man_luck

New member
Joined
Jul 14, 2004
Messages
3
Programming Experience
Beginner
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

Member
Joined
Sep 18, 2004
Messages
7
Programming Experience
10+
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

New member
Joined
Jul 14, 2004
Messages
3
Programming Experience
Beginner
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

Member
Joined
Sep 18, 2004
Messages
7
Programming Experience
10+
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

Member
Joined
Jun 18, 2004
Messages
16
Programming Experience
Beginner
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
 
Top Bottom