//导入试题
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;
}
用EXCEL导入试题
最新推荐文章于 2022-05-02 15:08:02 发布