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;
    }