将excel数据导入sql server数据库表并在gridview中显示 [英] import excel data to sql server database table and display in gridview

问题描述

如何将excel数据导入sql server数据库表并在asp.net的gridview中显示

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

推荐答案

这将对你有所帮助。

将MS Excel数据导入SQL Server表使用C＃ [ ^ ]

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

查看此链接的朋友..



使用C＃将MS Excel数据导入SQL Server表 [ ^ ]



HTTP：//www.asp snippets.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 [ ^ ]







添加链接文字以反映文章/质量保证标题。

[/编辑]

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[^]



Link text added to reflect the Article/QA title.
[/Edit]

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>

然后你可以将数据插入你的SQL数据库。

Then you can insert Data into your SQL Database.

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)
                               {

                               }
                           }
                       }

在SQL中使用您自己的列名。希望你能得到答案。

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

这篇关于将excel数据导入sql server数据库表并在gridview中显示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！