?using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using MyOnLineExam.BusinessLogicLayer;
using System.IO;
using System.Data.OleDb;

public partial class Web_admin_UserScore : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            ImageButton1.Attributes.Add("OnClick", "javascript:return confirm('確實要刪除這些用戶嗎？')");
            InitData();  //綁定用戶信息        
        }
    }
    //初始化成績表格
    protected void InitData()
    {
        Scores score = new Scores();        //創建Scores對象       
        DataSet ds = score.QueryScore();     //調用QueryScore方法查詢成績并將查詢結果放到DataSet數據集中
        GridView1.DataSource = ds;          //為GridView控件指名數據源        
        GridView1.DataBind();               //綁定數據
    }
    //GridView控件RowDeleting事件
    protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        Scores score = new Scores();          //創建Scores對象
        int ID = int.Parse(GridView1.DataKeys[e.RowIndex].Values[0].ToString()); //取出要刪除記錄的主鍵值
        if (score.DeleteByProc(ID))
        {
            Response.Write("<script language=javascript>alert('成功刪除該用戶！')</script>");
        }
        else
        {
            Response.Write("<script language=javascript>alert('刪除該用戶失敗！')</script>");
        }
        GridView1.EditIndex = -1;
        InitData();
    }
    //批量刪除成績
    protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
    {
        Scores score = new Scores();//創建Scores對象
        foreach (GridViewRow dr in GridView1.Rows)//對GridView中的每一行進行判斷
        {
            if (((CheckBox)dr.FindControl("xuanze")).Checked)//如果選擇了進行刪除
            {
                int ID = int.Parse(((Label)dr.FindControl("Label1")).Text);
                score.ID = ID;
                score.DeleteByProc(ID);
            }
        }
    }
    //到出到Excel事件
    protected void ImageButton2_Click(object sender, ImageClickEventArgs e)
    {
        Scores score = new Scores();        //創建Scores對象       
        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("../Module01.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);
    }
}