C#实现将Excel数据导入到数据库

前面在C#专题里发了不少关于excel的文章,今天我们还要发一篇关于如何将excel里的数据导入到数据库的方法,下面将一下基本原理:

1.先把Excel导入DataSet里

2.再把其中的值区出来，利用sql语句插入到数据库里。

 /// <summary>     /// 实现SQL语句的构造！     /// </summary>     /// <param name="ds"></param>     /// <param name="i"></param>     /// <returns>返回SQL语句！</returns>     public static string sqlinsert(DataSet ds,string strName, int i)     {         String strInsert = "insert into "+strName+" values(";

        for (int j = 0; j < ds.Tables[0].Columns.Count; j++)         {             SqlConnection myConnection = new SqlConnection();

            strInsert = strInsert + " '" + ds.Tables[0].Rows[i].ItemArray[j].ToString() + " ',";         }         return strInsert;     }     /// <summary>     /// 实现excel导入DataSet内     /// </summary>     /// <param name="Pathm"></param>     /// <returns>DataSet</returns>     ///     public static DataSet ExcelToDS(string Pathm)     {         DataSet ds = new DataSet();         try         {             string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Pathm + ";" + "Extended Properties=Excel 8.0;";

            OleDbConnection conn = new OleDbConnection(strConn);

            conn.Open();

            string strExcel = "";

            OleDbDataAdapter myCommand = null;

            strExcel = "select * from [sheet1$]";

            myCommand = new OleDbDataAdapter(strExcel, strConn);

            myCommand.Fill(ds);         }         catch (System.Threading.ThreadAbortException)         {

        }         return ds;     }     protected void BtnLoad_Click(object sender, EventArgs e)     {

        string sqlStr = ConfigurationManager.AppSettings["DBConnectionString"];

        SqlConnection sqlCon = new SqlConnection(sqlStr);

        SqlCommand sqlCmd = sqlCon.CreateCommand();

        sqlCon.Open();

        if (fileExcel.PostedFile.FileName.ToString() != null && fileExcel.PostedFile.FileName.ToString() != "" && fileExcel.PostedFile.FileName.EndsWith(".xls"))         {             DataSet ds = null;

            try             {                 string path = fileExcel.PostedFile.FileName;

                path = path.Substring(path.LastIndexOf("\\") + 1);

                string strFilePath = MapPath("excel") + "\\" + path;

                fileExcel.PostedFile.SaveAs(strFilePath);

                ds = ExcelToDS(strFilePath);             }             catch (Exception)             {                 Response.Write(" <script >'导入数据发生错误!' </script >");                 return;             }

            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)             {

                SqlCommand cmd = sqlCon.CreateCommand();

                cmd.CommandText = "select TableName from Temp_Table_Name";

                string strName=cmd.ExecuteScalar().ToString();

                cmd.CommandText = "select TimeNameTable from "+strName+"";

                strName = cmd.ExecuteScalar().ToString();

                string strInsert = sqlinsert(ds,strName,i);

                strInsert = strInsert.Substring(0, strInsert.Length - 1);

                strInsert += ")";

                Response.Write("<Script Language=JavaScript>alert(\"数据导入了数据库！\")</Script>");

                sqlCmd.CommandText = strInsert;

                sqlCmd.ExecuteNonQuery();             }         }