用EXCEL导入试题

//导入试题
    protected void Button1_Click(object sender, EventArgs e)
    {
        SqlConnection conn = questionConnection.getConnection();
        conn.Open();
        //上传文件
        bool b = Upload(FileUpload1);
        if (!b)
        {
            return;
        }
        string name = DateTime.Now.ToString("yyyymmddhhMMss") + FileUpload1.FileName;  //取得上传文件的名称
        string filepath = Server.MapPath("~/upload/") + name;
        DataSet ds = ExecleDs(filepath, name);

        DataRow[] dr = ds.Tables[0].Select();            //定义一个DataRow数组
        int rowsnum = ds.Tables[0].Rows.Count;
        if (rowsnum == 0)
        {
            Response.Write("<script>alert('Excel表为空表,无数据!')</script>");   //当Excel表为空时,对用户进行提示
        }
        else
        {
            for (int i = 0; i < dr.Length; i++)
            {
                int P_int_ID;
                string excel_type = dr[i]["试题类型"].ToString();
                int excel_ansNum =Convert.ToInt32(dr[i]["答案数量"]);
                string contents = dr[i]["题目"].ToString();
                string ansContents1 = dr[i]["答案1"].ToString();
                string ansContents2 = dr[i]["答案2"].ToString();
                string ansContents3 = dr[i]["答案3"].ToString();
                string ansContents4 = dr[i]["答案4"].ToString();
                string ansContents5 = dr[i]["答案5"].ToString();
                string ansContents6 = dr[i]["答案6"].ToString();
                int TrueAns =Convert.ToInt32(dr[i]["正确答案"]);
                //检查类型是否存在
               
                SqlCommand checkTypeCmd = new SqlCommand("Proc_GetCountQueType", conn);
                checkTypeCmd.CommandType = CommandType.StoredProcedure;

                checkTypeCmd.Parameters.Add("@type", SqlDbType.Char, 2);
                checkTypeCmd.Parameters["@type"].Value = excel_type;
                int count =Convert.ToInt32(checkTypeCmd.ExecuteScalar());

                if (count != 1)
                {
                    Response.Write("<script>alert('有不存在的考题类型，请检查');location='question.aspx'</script></script> ");
                    continue;
                }
                else
                {
                    //添加考题
                    SqlCommand InsQuestionCmd = new SqlCommand("Proc_InsQuestion", conn);
                    InsQuestionCmd.CommandType = CommandType.StoredProcedure;

                    InsQuestionCmd.Parameters.Add("@type", SqlDbType.Char, 2);
                    InsQuestionCmd.Parameters["@type"].Value = excel_type;

                    InsQuestionCmd.Parameters.Add("@contents", SqlDbType.Char, 300);
                    InsQuestionCmd.Parameters["@contents"].Value = contents;

                    InsQuestionCmd.Parameters.Add("@ansNum", SqlDbType.Int, 4);
                    InsQuestionCmd.Parameters["@ansNum"].Value = excel_ansNum;

                    InsQuestionCmd.Parameters.Add("@checked", SqlDbType.Bit, 1);
                    InsQuestionCmd.Parameters["@checked"].Value = false;

                    //添加考题并返回考题编号
                    P_int_ID = Convert.ToInt32(InsQuestionCmd.ExecuteScalar());
                    InsQuestionCmd.Dispose();

                    for (int j = 1; j <= excel_ansNum; j++)
                    {
                        SqlCommand insAnsCmd = new SqlCommand();

                        insAnsCmd.Connection = conn;

                        if ((contents != "") && (contents != null))
                        {
                            insAnsCmd.CommandText = "insert into answer(ansCode,questionCode,ansContents,truth) values(@ansCode,@questionCode,@ansContents,@truth)";
                            insAnsCmd.Parameters.Add("@ansCode", SqlDbType.Char, 1);
                            insAnsCmd.Parameters["@ansCode"].Value = j.ToString();
                            insAnsCmd.Parameters.Add("@questionCode", SqlDbType.Char, 5);
                            insAnsCmd.Parameters["@questionCode"].Value = P_int_ID;
                            insAnsCmd.Parameters.Add("@ansContents", SqlDbType.Char, 200);
                            insAnsCmd.Parameters.Add("@truth", SqlDbType.Bit, 1);
                            if (j == 1)
                            {
                                insAnsCmd.Parameters["@ansContents"].Value = ansContents1;
                                if (TrueAns == 1)
                                    insAnsCmd.Parameters["@truth"].Value = true;
                                else
                                    insAnsCmd.Parameters["@truth"].Value = false;
                            }
                            else if (j == 2)
                            {
                                insAnsCmd.Parameters["@ansContents"].Value = ansContents2;
                                if (TrueAns == 2)
                                    insAnsCmd.Parameters["@truth"].Value = true;
                                else
                                    insAnsCmd.Parameters["@truth"].Value = false;
                            }
                            else if (j == 3)
                            {
                                insAnsCmd.Parameters["@ansContents"].Value = ansContents3;
                                if (TrueAns == 3)
                                    insAnsCmd.Parameters["@truth"].Value = true;
                                else
                                    insAnsCmd.Parameters["@truth"].Value = false;
                            }
                            else if (j == 4)
                            {
                                insAnsCmd.Parameters["@ansContents"].Value = ansContents4;
                                if (TrueAns == 4)
                                    insAnsCmd.Parameters["@truth"].Value = true;
                                else
                                    insAnsCmd.Parameters["@truth"].Value = false;
                            }
                            else if (j == 5)
                            {
                                insAnsCmd.Parameters["@ansContents"].Value = ansContents5;
                                if (TrueAns == 5)
                                    insAnsCmd.Parameters["@truth"].Value = true;
                                else
                                    insAnsCmd.Parameters["@truth"].Value = false;
                            }
                            else if (j == 6)
                            {
                                insAnsCmd.Parameters["@ansContents"].Value = ansContents6;
                                if (TrueAns == 6)
                                    insAnsCmd.Parameters["@truth"].Value = true;
                                else
                                    insAnsCmd.Parameters["@truth"].Value = false;
                            }
                            insAnsCmd.ExecuteNonQuery();
                            insAnsCmd.Dispose();
                        }
                    }                   
                }
            }
        }
    }


  
   
   
   
    //上传文件方法 
    private bool Upload(FileUpload myFileUpload)
    {
        bool flag = false;
        //是否允许上载 
        bool fileAllow = false;
        //设定允许上载的扩展文件名类型 
        string[] allowExtensions = { ".xls" };

        //取得网站根目录路径 
        string path = HttpContext.Current.Request.MapPath("~/upload/");
        //检查是否有文件案 
        if (myFileUpload.HasFile)
        {
            //取得上传文件之扩展文件名，并转换成小写字母 
            string fileExtension = System.IO.Path.GetExtension(myFileUpload.FileName).ToLower();
            //检查扩展文件名是否符合限定类型 
            for (int i = 0; i < allowExtensions.Length; i++)
            {
                if (fileExtension == allowExtensions[i])
                {
                    fileAllow = true;
                }
            }

            if (fileAllow)
            {
                try
                {
                    //存储文件到文件夹 
                    myFileUpload.SaveAs(path +  DateTime.Now.ToString("yyyymmddhhMMss") + myFileUpload.FileName);
                    lblMes.Text = "文件导入成功";
                    flag = true;
                }
                catch (Exception ex)
                {
                    lblMes.Text += ex.Message;
                    flag = false;
                }
            }
            else
            {
                lblMes.Text = "不允许上载：" + myFileUpload.PostedFile.FileName + "，只能上传xls的文件，请检查！";
                flag = false;
            }
        }
        else
        {
            lblMes.Text = "请选择要导入的excel文件!";
            flag = false;
        }
        return flag;
    }


    /// <summary>
    /// 查询EXCEL电子表格添加到DATASET
    /// </summary>
    /// <param name="filenameurl">服务器路径</param>
    /// <param name="table">表名</param>
    ///

    public DataSet ExecleDs(string filenameurl, string table)
    {
        string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
        OleDbConnection conn = new OleDbConnection(strConn);
        conn.Open();
        DataSet ds = new DataSet();
        OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
        odda.Fill(ds, table);
        return ds;
    }