import excel data to sql server database table and display in gridview

Question

0.00/5 (No votes)

See more:

C#

ASP.NET

How to import excel data to sql server database table and display in gridview in asp.net

Posted 9-Feb-14 22:23pm

udhayakumar10022014

Add a Solution

Comments

Modi Mayank 17-Mar-15 0:45am

I think Epplus.dll is the best way to import excel data to datatable. Refer: Import excelsheet data to sql server in C#

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Ni!E$H_WAGH · Answer 1 · 2014-02-09T22:40:00

Solution 1

This will be helpful to you.
Import MS Excel data to SQL Server table using C#[^]

Posted 9-Feb-14 22:40pm

Ni!E$H_WAGH

v2

Siva Hyderabad · Answer 2 · 2014-02-09T22:44:00

Solution 2

see this link friend..

Import MS Excel data to SQL Server table using C#[^]

http://www.aspsnippets.com/Articles/How-to-Import-Excel-Sheet-data-into-SQL-Server-using-ASPNet.aspx[^]

http://www.aspdotnet-suresh.com/2010/09/import-data-from-excel-to-sql-database.html[^]

[Edit member="Tadit"]
Link text added to reflect the Article/QA title.
[/Edit]

Posted 9-Feb-14 22:44pm

Siva Hyderabad

Updated 9-Feb-14 23:04pm

v4

Comments

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 10-Feb-14 5:00am

I corrected two links. Please correct the first one.

Md.Sajidur rahman · Answer 3 · 2014-02-09T22:49:00

C#

DataTable dt = null;
       if (FileUpload1.HasFile)
       {

           filename = FileUpload1.FileName;
           filename = AppDomain.CurrentDomain.BaseDirectory + @"" + filename;
           var fileExtension = Path.GetExtension(filename).Substring(1);
 if (fileExtension == "xlsx")
                    {
                        {

                            DataSet myDataset = new DataSet();
                            string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + @";Extended Properties=""Excel 12.0 Xml;HDR=YES""";
                            OleDbConnection myData = new OleDbConnection(strConn);
                            try
                            {
                                myData.Open();
                            }
                            catch (Exception ex)
                            {
                                try
                                {
                                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filename + ";" + "Extended Properties=Excel 8.0;HDR=YES;";
                                    myData = new OleDbConnection(strConn);
                                    myData.Open();
                                }
                                catch (Exception ex2)
                                {
                                    strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + @";Extended Properties=""HTML Import;HDR=YES;IMEX=1"";";
                                    myData = new OleDbConnection(strConn);
                                    try
                                    {
                                        myData.Open();
                                    }
                                    catch (Exception ex3)
                                    {
                                       
                                    }
                                }
                            }
 if (myData.State.ToString() != "Closed")
                            {
                                int i = 0;
                                DataSet ds = new DataSet();
                                List<string> queryList = new List<string>();
                                foreach (DataRow row in myData.GetSchema("tables").Rows)
                                {
                                    try
                                    {
                                        i++;
                                        string name = row[2].ToString().Replace("''", "'").TrimEnd('_');
                                        OleDbDataAdapter d = new OleDbDataAdapter("SELECT * from [" + name + "]", strConn);
                                        d.Fill(ds);
                                        dt = ds.Tables[0].Copy();                                     
                                      
                                        dt.Namespace = name;
                                        myDataset.Tables.Add(dt);

                                    }
                                    catch (Exception ex)
                                    {
                                    }
                                    break;
                                }

                            }


                        }
                    }

</string></string>

Then you can insert Data into your SQL Database.

C#

BU_UtilDB db = new BU_UtilDB();
                           if (existingrow)
                           {
                               try
                               {
                                   string query = "INSERT INTO [dbo].[Fr_Endkunde]([Anrede],[anrede_id]"
                                       + ",[Firma],[Geburtsdatum],[Name],[Ort],[PLZ] ,[Postfach],[Strasse],[Titel],[Vermittlernummer],[Vorname],[Land])"
                                       + "VALUES('" + dr["Anrede"] + "','" + dr["anrede_id"] + "','" + dr["Firma"] + "','" + datetime + "',"
                                       + "'" + dr["Nachname"] + "','" + dr["Ort"] + "','" + dr["PLZ"] + "','" + dr["Postfach"] + "','" + dr["Strasse"] + "','" + dr["Title"] + "',"
                                       + "'" + dr["Vermittlernummer"] + "','" + dr["Vorname"] + "','" + dr["Land"] + "')";
                                   BU_UtilDB.ExecuteReader(query);

                               }
                               catch (Exception ex)
                               {

                               }
                           }
                       }

In SQL You use your own Column Name. Hope you will got your answer.

TrushnaK · Answer 4 · 2014-02-09T22:50:00

Solution 4

refer below links:-
http://www.aspdotnet-suresh.com/2012/12/how-to-import-data-from-excel-to-aspnet.html[^]
http://www.c-sharpcorner.com/uploadfile/poonu.unique/import-excel-data-into-gridview-export-to-excel-sheet/[^]

Posted 9-Feb-14 22:50pm

TrushnaK