//初始化成绩表格
protected void InitData()
{
Scoresmr score = new Scoresmr(); //创建Scoresmr对象
DataSet ds = score.QueryScore(); //调用QueryScore方法查询成绩并将查询结果放到DataSet数据集中
GridView1.DataSource = ds; //为GridView控件指名数据源
GridView1.DataBind(); //绑定数据
}
protected void ScoreInitData() //初始化成绩
{
Scoresmr score = new Scoresmr(); //创建Scoresmr对象
DataSet ds = score.QueryUserScore(Session["userID"].ToString());
if (ds.Tables[0].Rows.Count > 0)
{
GridView1.DataSource = ds; //为GridView控件指名数据源
GridView1.DataBind(); //绑定数据
}
else
{
lblScore.Text = "没有成绩!";
}
}
//GridView控件RowDeleting事件
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
Scoresmr score = new Scoresmr(); //创建Scoresmr对象
int ID = int.Parse(GridView1.DataKeys[e.RowIndex].Values[0].ToString()); //取出要删除记录的主键值
if (score.DeleteByStr(ID))
{
Response.Write("<script language=javascript>alert('成功删除！')</script>");
}
else
{
Response.Write("<script language=javascript>alert('删除失败！')</script>");
}
GridView1.EditIndex = -1;
InitData();
}
protected void imgBtnSave_Click(object sender, ImageClickEventArgs e)
{
Scoresmr insertExamScore = new Scoresmr(); //创建Scoresmr类对象
insertExamScore.UserID = Request.QueryString["UserID"].ToString();
insertExamScore.ExamTime = Convert.ToDateTime(lblExamtime.Text);
insertExamScore.PaperID = int.Parse(Request.QueryString["PaperID"].ToString());
if (queScore.Text.Trim() != "")
{
insertExamScore.Score = Convert.ToInt32(sumScore.Text);
}
insertExamScore.PingYu = tbxPingyu.Text;
//实例化公共类Papermr
Papermr mypaper = new Papermr();
mypaper.UserID = Request.QueryString["UserID"].ToString();
mypaper.PaperID = int.Parse(Request.QueryString["PaperID"].ToString());
mypaper.state = "已评阅";
//使用CheckScore方法验证成绩是否存在
if (!insertExamScore.CheckScore(insertExamScore.UserID, insertExamScore.PaperID))
{
//调用InsertByProc方法向数据库中插入成绩
if (insertExamScore.InsertByProc())
{
mypaper.UpdateByProc(mypaper.UserID, mypaper.PaperID, mypaper.state);
//给出成功提示信息
lblMessage.Text = "考生成绩保存成功!";
}
else
{
lblMessage.Text = "考生成绩保存失败!";
}
}
else
{
lblMessage.Text = "该用户的成绩已存在,请先删除成绩再评阅!";
}
}
//到出到Excel事件
protected void ImageButton2_Click(object sender, ImageClickEventArgs e)
{
Scoresmr score = new Scoresmr(); //创建Scoresmr对象
DataSet ds = score.QueryScore(); //调用QueryScore方法查询成绩并将查询结果放到DataSet数据集中
DataTable DT = ds.Tables[0];
//生成将要存放结果的Excel文件的名称
string NewFileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
//转换为物理路径
NewFileName = Server.MapPath("Temp/" + NewFileName);
//根据模板正式生成该Excel文件
File.Copy(Server.MapPath("../Modulemr.xls"), NewFileName, true);
//建立指向该Excel文件的数据库连接
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + NewFileName + ";Extended Properties='Excel 8.0;'";
OleDbConnection Conn = new OleDbConnection(strConn);
//打开连接，为操作该文件做准备
Conn.Open();
OleDbCommand Cmd = new OleDbCommand("", Conn);
foreach (DataRow DR in DT.Rows)
{
string XSqlString = "insert into [Sheet1$]";
XSqlString += "([用户姓名],[试卷],[成绩],[考试时间]) values(";
XSqlString += "'" + DR["UserName"] + "',";
XSqlString += "'" + DR["PaperName"] + "',";
XSqlString += "'" + DR["Score"] + "',";
XSqlString += "'" + DR["ExamTime"] + "')";
Cmd.CommandText = XSqlString;
Cmd.ExecuteNonQuery();
}
//操作结束，关闭连接
Conn.Close();
//打开要下载的文件，并把该文件存放在FileStream中
System.IO.FileStream Reader = System.IO.File.OpenRead(NewFileName);
//文件传送的剩余字节数：初始值为文件的总大小
long Length = Reader.Length;
Response.Buffer = false;
Response.AddHeader("Connection", "Keep-Alive");
Response.ContentType = "application/octet-stream";
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode("学生成绩.xls"));
Response.AddHeader("Content-Length", Length.ToString());
byte[] Buffer = new Byte[10000]; //存放欲发送数据的缓冲区
int ByteToRead; //每次实际读取的字节数
while (Length > 0)
{
//剩余字节数不为零，继续传送
if (Response.IsClientConnected)
{
//客户端浏览器还打开着，继续传送
ByteToRead = Reader.Read(Buffer, 0, 10000); //往缓冲区读入数据
Response.OutputStream.Write(Buffer, 0, ByteToRead); //把缓冲区的数据写入客户端浏览器
Response.Flush(); //立即写入客户端
Length -= ByteToRead; //剩余字节数减少
}
else
{
//客户端浏览器已经断开，阻止继续循环
Length = -1;
}
}
//关闭该文件
Reader.Close();
//删除该Excel文件
File.Delete(NewFileName);
}
