上传Excel文件并导入到数据

上传文件： 

    /// <summary>
    /// 上传Excel文件
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnFileLoad_Click(object sender, EventArgs e)
    {
        if (this.FileUpload_Excel.HasFile)
        {
            try
            {
                //获取文件名
                string FileName = this.FileUpload_Excel.FileName.ToString();
                //获取文件后缀名
                string[] FileArray = FileName.Split('.');
                string FileType = FileArray[FileArray.Length - 1].ToString();

                //判断上传的文件是否为Excel格式
                if (FileType != "xls")
                {
                    JavaScript.Alert("请您上传Excel文件！", this.Page);
                    return;
                }

                this.FileUpload_Excel.SaveAs(Server.MapPath("~") + "/ExcelFile/" + "LevovoExcel" + "." + FileType);
                this.Label_Excel.Text = "恭喜您！文件上传成功";

            }
            catch (Exception ex)
            {
                Response.Write(ex.Message.ToString());
            }

        }
        else
        {
            this.Label_Excel.Text = "对不起！文件没有上传成功，请重试！";
        }
    }

 

导入文件：

    /// <summary>
    /// 将Excel中的文件导入到数据库
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnInsertData_Click(object sender, EventArgs e)
    {
        //先将EXCEL导入到数据库,一:先把EXCEL导入dateView,二:然后将dateView里的数据导入到数据库里面

        //EXCEL连接
        string conn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=WWW/ExcelFile/LevovoExcel.xls;" + "Extended Properties=Excel 8.0;";
        OleDbConnection thisconnection = new OleDbConnection(conn);
        thisconnection.Open();
        string Sql = "select * from [Sheet1$]";
        OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, thisconnection);
        DataSet ds = new DataSet();
        mycommand.Fill(ds, "[Sheet1]");
        thisconnection.Close();

        //Sql连接
        SqlConnection thisconnection1 = new SqlConnection(SqlHelper.conn_Default);
        thisconnection1.Open();
        int count = ds.Tables["[Sheet1]"].Rows.Count;

        for (int i = 0; i < count; i++)
        {
            string DataNum1 = ds.Tables["[Sheet1]"].Rows[i]["编号"].ToString();
            string DataNum2 = ds.Tables["[Sheet1]"].Rows[i]["批号"].ToString();
            string DataNum3 = ds.Tables["[Sheet1]"].Rows[i]["报名表类别"].ToString();
            string DataNum4 = ds.Tables["[Sheet1]"].Rows[i]["姓名"].ToString();
            string DataNum5 = ds.Tables["[Sheet1]"].Rows[i]["性别"].ToString();
            string DataNum6 = ds.Tables["[Sheet1]"].Rows[i]["有效证件号码"].ToString();
            string DataNum7 = ds.Tables["[Sheet1]"].Rows[i]["出生日期"].ToString();
            string DataNum8 = ds.Tables["[Sheet1]"].Rows[i]["年龄"].ToString();
            string DataNum9 = ds.Tables["[Sheet1]"].Rows[i]["职业"].ToString();
            string DataNum10 = ds.Tables["[Sheet1]"].Rows[i]["国籍"].ToString();
            string DataNum11 = ds.Tables["[Sheet1]"].Rows[i]["省份/直辖市"].ToString();
            string DataNum12 = ds.Tables["[Sheet1]"].Rows[i]["城市"].ToString();
            string DataNum13 = ds.Tables["[Sheet1]"].Rows[i]["地址"].ToString();
            string DataNum14 = ds.Tables["[Sheet1]"].Rows[i]["邮政编码"].ToString();
            string DataNum15 = ds.Tables["[Sheet1]"].Rows[i]["Email"].ToString();
            string DataNum16 = ds.Tables["[Sheet1]"].Rows[i]["区号"].ToString();
            string DataNum17 = ds.Tables["[Sheet1]"].Rows[i]["住宅电话"].ToString();
            string DataNum18 = ds.Tables["[Sheet1]"].Rows[i]["移动电话"].ToString();
            string DataNum19 = ds.Tables["[Sheet1]"].Rows[i]["办公电话"].ToString();

            string excelsql = "INSERT INTO [cnSheet_Lenovo] ([编号] ,[批号] ,[报名表类别] ,[姓名] ,[性别] ,[有效证件号码] ,[出生日期] ,[年龄] ,[职业] ,[国籍] ,[省份/直辖市] ,[城市] ,[地址] ,[邮政编码] ,[Email] ,[区号] ,[住宅电话] ,[移动电话] ,[办公电话] )  VALUES  ('" + DataNum1 + "','" + DataNum2 + "','" + DataNum3 + "','" + DataNum4 + "','" + DataNum5 + "','" + DataNum6 + "','" + DataNum7 + "','" + DataNum8 + "','" + DataNum9 + "','" + DataNum10 + "','" + DataNum11 + "','" + DataNum12 + "','" + DataNum13 + "','" + DataNum14 + "','" + DataNum15 + "','" + DataNum16 + "','" + DataNum17 + "','" + DataNum18 + "','" + DataNum19 + "')";
            SqlCommand mycommand1 = new SqlCommand(excelsql, thisconnection1);
            mycommand1.ExecuteNonQuery();
        }
        Response.Write("更新成功");
        thisconnection1.Close();

    }