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

I am working on VB.net 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.
Posted

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)
da.fill(dt);
then
store datatable data into sql using for/while loop.


or
see these links you may get some idea
http://www.daniweb.com/software-development/vbnet/threads/385368/how-to-import-exel-file-to-sql-server-using-vb.net[^]

http://www.aspsnippets.com/Articles/Read-and-Import-Excel-Sheet-into-SQL-Server-Database-in-ASP.Net.aspx[^]
 
Share this answer
 
Comments
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    
Thanks
You can directly show data from excel:

http://vb.net-informations.com/datagridview/vb.net_datagridview_import.htm[^]

Import data from excel to sql server:

Import Data from Excel to SQL Server[^]

Display data from sql server in gridview:

http://www.aspnettutorials.com/tutorials/database/db-grid-aspnet2-vb.aspx[^]
 
Share this answer
 
VB
 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
        Try


            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:\vb.net-informations.xls';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
            MyCommand.Fill(DtSet)
            dgv.DataSource = DtSet.Tables(0)
            MyConnection.Close()


            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

                Try

                    MyConnection.Open()
                    objDR = objCmdSelect.ExecuteReader
                    bulkCopy.WriteToServer(objDR)
                    objDR.Close()
                    SQLconn.Close()

                Catch ex As Exception
                    MsgBox(ex.ToString)
                End Try
            End Using



            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function
 
Share this answer
 
 
Share this answer
 
v2
Comments
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