Click here to Skip to main content
15,943,148 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi all,

I am working on Desktop Application. I want to upload Excel sheet to Database(sql server),And Display data in Gridview.
Is there any IO property for that?

Please give me solution if You have.

Thanks in Advance.

first you have to store excel data in datatable,
you can store data in datatable as

string constring="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MySpreadsheet.XLS;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"""
datatable dt=new datatable();
Oledbconnection con=new oledbconnection(constring);
oledbdataadapter da=new oledbdataadapter("select * from sheet1",con)
store datatable data into sql using for/while loop.

see these links you may get some idea[^][^]
Share this answer
MacParekh 21-May-12 3:08am    
Is there any way to avoid "select * from sheet1 ??
can i get by IO.File.ReadAllLines(file)
I have done for CSV file. but confuse about xls file

because i don't want to add more connection string for Excel sheet
MacParekh 29-May-12 1:01am    
You can directly show data from excel:[^]

Import data from excel to sql server:

Import Data from Excel to SQL Server[^]

Display data from sql server in gridview:[^]
Share this answer
 Private Sub browseXLfile_Click(sender As Object, e As EventArgs) Handles browseXLfile.Click
        Dim ofd As New OpenFileDialog

        If ofd.ShowDialog() <> System.Windows.Forms.DialogResult.OK Then Exit Sub

        Dim nme As String = ofd.FileName
        Dim safename As String = ofd.SafeFileName
        safename = safename.Substring(0, safename.LastIndexOf("."))
        Import(nme, dgv, safename)
    End Sub

Public Shared Function Import(ByVal FileName As String, ByVal dgv As DataGridView, ByVal safefilename As String) As Boolean

            Dim MyConnection As System.Data.OleDb.OleDbConnection
            Dim DtSet As System.Data.DataSet
            Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
            MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=Excel 8.0;")
            'MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\';Extended Properties=Excel 8.0;")
            MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)
            MyCommand.TableMappings.Add("Table", safefilename)
            DtSet = New System.Data.DataSet
            dgv.DataSource = DtSet.Tables(0)

            Dim expr As String = "SELECT * FROM [Sheet1$]"

            Dim SQLconn As New SqlConnection()
            Dim ConnString As String = "Data Source=SUN-50\SQLEXPRESS;Initial Catalog=SunEducation;Persist Security Info=True;User ID=yourusername;Password=yourpassword"
            Dim objCmdSelect As OleDbCommand = New OleDbCommand(expr, MyConnection)
            Dim objDR As OleDbDataReader

            SQLconn.ConnectionString = ConnString

            Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(ConnString)
                bulkCopy.DestinationTableName = safefilename


                    objDR = objCmdSelect.ExecuteReader

                Catch ex As Exception
                End Try
            End Using

            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function
Share this answer
Share this answer
MacParekh 21-May-12 8:02am    
Is there any way to avoid "select * from sheet1 ??
can i get by IO.File.ReadAllLines(file)
I have done for CSV file. but confuse about xls file

because i don't want to add more connection string for Excel sheet
Maciej Los 23-May-12 12:22pm    
Yes, it is! You can read data cell by cell, but it's not effective.
Member 9326869 27-Aug-12 1:27am    
Hai all, Please Help Me...
How to load the data from excel into datagridview using vb .net with sql server connection.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900