Excel批量导入到数据库

using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using System.Data.OleDb;using System.Data; protected void Import()    {        try        {            string fileurl = Request.Form["homepath"].ToString();//用kindeditor编辑器上传excel文件            DataTable dt = GetDataFromExcelWithAppointSheetName(fileurl);            bool issuccess = false;            for (int i = 0; i < dt.Rows.Count; i++)            {                int userid = int.Parse(dt.Rows[i][0].ToString());                int reserveid = int.Parse(dt.Rows[i][1].ToString());                int typeid = int.Parse(dt.Rows[i][2].ToString());                int quantity = int.Parse(dt.Rows[i][3].ToString());                string name = dt.Rows[i][4].ToString();                int sex = int.Parse(dt.Rows[i][5].ToString());                string moblie = dt.Rows[i][6].ToString();                DateTime time = DateTime.Parse(dt.Rows[i][7].ToString());                int number = int.Parse(dt.Rows[i][8].ToString());                string verifycode =dt.Rows[i][9].ToString();                string str = dt.Rows[i][10].ToString();                string note1 = dt.Rows[i][11].ToString();                string note2 = dt.Rows[i][12].ToString();                string note3 = dt.Rows[i][13].ToString();                string note4 = dt.Rows[i][14].ToString();                int verify = int.Parse(dt.Rows[i][15].ToString());                int state = int.Parse(dt.Rows[i][16].ToString());                DateTime jointime = DateTime.Parse(dt.Rows[i][17].ToString());                codeQuery hq = new codeQuery();                //循环添加到                issuccess = hq.saveAddRes(userid, reserveid, typeid, Convert.ToInt32(quantity), name, Convert.ToInt32(sex), moblie, time, Convert.ToInt32(number), verifycode, str, note1, note2, note3, note4, verify, state, jointime);            }            if (issuccess)            {                adminBasic.setMsg("成功", "导入成功", "adm_reserve.aspx?sortid=" + ab.sortid + "&page=" + ab.pages);            }        }        catch (Exception e)        {            adminBasic.setMsg("失败", "导入失败", "adm_reserve.aspx?sortid=" + ab.sortid + "&page=" + ab.pages);        }    }    /// <summary>    /// 根据excel的文件的路径提取其中表的数据    /// </summary>    /// <param name="Path">Excel文件的路径</param>    private DataTable GetDataFromExcelWithAppointSheetName(string Path)    {        //连接串        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + System.Web.HttpContext.Current.Server.MapPath(Path) + ";" + "Extended Properties=Excel 8.0;";        //string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + System.Web.HttpContext.Current.Server.MapPath(Path) + ";Extended Properties='Excel 8.0; HDR=Yes; IMEX=1'"; //此连接只能操作Excel2007之前(.xls)文件        //string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + System.Web.HttpContext.Current.Server.MapPath(Path) + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'"; //此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串)        //备注： "HDR=yes;"是说Excel文件的第一行是列名而不是数据，"HDR=No;"正好与前面的相反。        //      "IMEX=1 "如果列中的数据类型不一致，使用"IMEX=1"可必免数据类型冲突。         OleDbConnection conn = new OleDbConnection(strConn);        conn.Open();        //返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等          DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });        //包含excel中表名的字符串数组        string[] strTableNames = new string[dtSheetName.Rows.Count];        for (int k = 0; k < dtSheetName.Rows.Count; k++)        {            strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();        }        OleDbDataAdapter myCommand = null;        DataTable dt = new DataTable();        //从指定的表明查询数据,可先把所有表明列出来供用户选择        string strExcel = "select * from [" + strTableNames[0] + "]";        myCommand = new OleDbDataAdapter(strExcel, strConn);        dt = new DataTable();        myCommand.Fill(dt);        return dt;    }