问尝试使用会话变量将网格视图导出到Excel

EN

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            SqlConnection sqlconnectionStatus = new SqlConnection(str);
            string DDL_Value = Convert.ToString(Request.QueryString["DDL_Val"]);
            string Val_Value = Convert.ToString(Request.QueryString["Val_Val"]);
            string Trk_Value = Convert.ToString(Request.QueryString["Trk_Val"]);
            string Acct_Value = Convert.ToString(Request.QueryString["Acct_Val"]);
            //Use the ClassTesting class to determine if the dates are real, and fill in today's date if they're blank
            string StDt_Value = ClassTesting.checkFields(Request.Form["txtStartDate"], "Date");
            string EnDt_Value = ClassTesting.checkFields(Request.Form["txtEndDate"], "Date");

            //string StDt_Value = Convert.ToString(Request.QueryString["StDt_Val"]);
            //string EnDt_Value = Convert.ToString(Request.QueryString["EnDt_Val"]);



            string BTN_Value;
            // Because the date is stored as an INT, you have to request the string and then
            //   convert it to an INT
            string StDT_Vals = Request.QueryString["StDt_Val"].ToString();
            string EnDT_Vals = Request.QueryString["EnDt_Val"].ToString();



            string sqlquery;

            if (String.IsNullOrEmpty(Acct_Value))
            {
                if (String.IsNullOrEmpty(DDL_Value))
                {
                    BTN_Value = "2";
                }
                else
                {
                    BTN_Value = "1";
                }
            }
            else
            {
                BTN_Value = "3";
            }
            // Check to see if a specific Agent is being requested
            if (BTN_Value == "1")
            {
                // int StDT_Value = Convert.ToInt32(StDT_Vals);
                // int EnDT_Value = Convert.ToInt32(EnDT_Vals);

                sqlquery = "Select DISTINCT PL.PROC_NM as Agent_Name, CCM.UNIQUE_CLAIM_ID as Issue_Number, CCM.CLAIM_ID as Claim_Number, ";
                sqlquery = sqlquery + "CCM.SOCSEC as Employee_Last_Digit, CONVERT(VARCHAR(10), CCM.DATE_IMPORTED, 101) AS Import_Date, CONVERT(VARCHAR(10), CCM.Orig_Open_Date, 101) as Original_Review_Date, ";
                sqlquery = sqlquery + "AGL.ACCT_GRP as Account_Name, AL.ACCT_NUM as Account_Number, CCM.CDBBEN as Benefit_Option, BT1.StatusText as BenType1, ";
                sqlquery = sqlquery + "BT2.StatusText as BenType2, BT3.StatusText as BenType3, CCM.Cmplt as Review_Validated, CCM.Vldtn_Cmmnts as Validation_Comments, ";
                sqlquery = sqlquery + "CCM.Gtkpr_Cmmnts as Gatekeeper_Comments, TS.StatusText as Tracking_Status ";
                sqlquery = sqlquery + "from ClosedClaims_MERGE CCM ";
                sqlquery = sqlquery + "LEFT JOIN PROC_LIST PL ON CCM.Spare = PL.LOGIN ";
                sqlquery = sqlquery + "LEFT JOIN ACCT_LIST AL ON AL.ACCT_NUM = CCM.CDBACC ";
                sqlquery = sqlquery + "LEFT JOIN ACCT_GRP_LIST AGL ON AGL.ACCT_GRP_PK = AL.ACCT_GRP_FK ";
                sqlquery = sqlquery + "LEFT JOIN TrackingStatus TS ON TS.StatusCode = CCM.TrackingStatus ";
                sqlquery = sqlquery + "LEFT JOIN BenefitType_v3 BT1 ON BT1.StatusCode = CCM.BENEFIT_TYPE1 ";
                sqlquery = sqlquery + "LEFT JOIN BenefitType_v3 BT2 ON BT2.StatusCode = CCM.BENEFIT_TYPE2 ";
                sqlquery = sqlquery + "LEFT JOIN BenefitType_v3 BT3 ON BT3.StatusCode = CCM.BENEFIT_TYPE3  ";
                sqlquery = sqlquery + "WHERE CCM.Spare " + (DDL_Value == "" ? "IS NULL" : "LIKE '" + DDL_Value + "'") + " AND CCM.Cmplt " + (Val_Value == "" ? "IS NULL" : "LIKE '" + Val_Value + "'") + " AND CCM.TrackingStatus IN (" + Trk_Value + ") AND CCM.SpareFinished >= '" + StDt_Value + "' AND CCM.SpareFinished <= '" + EnDt_Value + "'";
            }
            else
            {
                if (BTN_Value == "2")
                {
                    sqlquery = "Select DISTINCT PL.PROC_NM as Agent_Name, CCM.UNIQUE_CLAIM_ID as Issue_Number, CCM.CLAIM_ID as Claim_Number, ";
                    sqlquery = sqlquery + "CCM.SOCSEC as Employee_Last_Digit, CONVERT(VARCHAR(10), CCM.DATE_IMPORTED, 101) AS Import_Date, CONVERT(VARCHAR(10), CCM.Orig_Open_Date, 101) AS Original_Review_Date, ";
                    sqlquery = sqlquery + "AGL.ACCT_GRP as Account_Name, AL.ACCT_NUM as Account_Number, CCM.CDBBEN as Benefit_Option, BT1.StatusText as BenType1, ";
                    sqlquery = sqlquery + "BT2.StatusText as BenType2, BT3.StatusText as BenType3, CCM.Cmplt as Review_Validated, CCM.Vldtn_Cmmnts as Validation_Comments, ";
                    sqlquery = sqlquery + "CCM.Gtkpr_Cmmnts as Gatekeeper_Comments, TS.StatusText as Tracking_Status ";
                    sqlquery = sqlquery + "from ClosedClaims_MERGE CCM ";
                    sqlquery = sqlquery + "LEFT JOIN PROC_LIST PL ON CCM.Spare = PL.LOGIN ";
                    sqlquery = sqlquery + "LEFT JOIN ACCT_LIST AL ON AL.ACCT_NUM = CCM.CDBACC ";
                    sqlquery = sqlquery + "LEFT JOIN ACCT_GRP_LIST AGL ON AGL.ACCT_GRP_PK = AL.ACCT_GRP_FK ";
                    sqlquery = sqlquery + "LEFT JOIN TrackingStatus TS ON TS.StatusCode = CCM.TrackingStatus ";
                    sqlquery = sqlquery + "LEFT JOIN BenefitType_v3 BT1 ON BT1.StatusCode = CCM.BENEFIT_TYPE1 ";
                    sqlquery = sqlquery + "LEFT JOIN BenefitType_v3 BT2 ON BT2.StatusCode = CCM.BENEFIT_TYPE2 ";
                    sqlquery = sqlquery + "LEFT JOIN BenefitType_v3 BT3 ON BT3.StatusCode = CCM.BENEFIT_TYPE3  ";
                    sqlquery = sqlquery + "WHERE CCM.Cmplt " + (Val_Value == "" ? "IS NULL" : "LIKE '" + Val_Value + "'") + " AND CCM.TrackingStatus IN (" + Trk_Value + ") AND CCM.SpareFinished >= '" + StDt_Value + "' AND CCM.SpareFinished <= '" + EnDt_Value + "'";
                }
                else
                {
                    sqlquery = "Select DISTINCT PL.PROC_NM as Agent_Name, CCM.UNIQUE_CLAIM_ID as Issue_Number, CCM.CLAIM_ID as Claim_Number, ";
                    sqlquery = sqlquery + "CCM.SOCSEC as Employee_Last_Digit, CONVERT(VARCHAR(10), CCM.DATE_IMPORTED, 101) AS Import_Date, CONVERT(VARCHAR(10), CCM.Orig_Open_Date, 101) AS Original_Review_Date, ";
                    sqlquery = sqlquery + "AGL.ACCT_GRP as Account_Name, AL.ACCT_NUM as Account_Number, CCM.CDBBEN as Benefit_Option, BT1.StatusText as BenType1, ";
                    sqlquery = sqlquery + "BT2.StatusText as BenType2, BT3.StatusText as BenType3, CCM.Cmplt as Review_Validated, CCM.Vldtn_Cmmnts as Validation_Comments, ";
                    sqlquery = sqlquery + "CCM.Gtkpr_Cmmnts as Gatekeeper_Comments, TS.StatusText as Tracking_Status ";
                    sqlquery = sqlquery + "from ClosedClaims_MERGE CCM ";
                    sqlquery = sqlquery + "LEFT JOIN PROC_LIST PL ON CCM.Spare = PL.LOGIN ";
                    sqlquery = sqlquery + "LEFT JOIN ACCT_LIST AL ON AL.ACCT_NUM = CCM.CDBACC ";
                    sqlquery = sqlquery + "LEFT JOIN ACCT_GRP_LIST AGL ON AGL.ACCT_GRP_PK = AL.ACCT_GRP_FK ";
                    sqlquery = sqlquery + "LEFT JOIN TrackingStatus TS ON TS.StatusCode = CCM.TrackingStatus ";
                    sqlquery = sqlquery + "LEFT JOIN BenefitType_v3 BT1 ON BT1.StatusCode = CCM.BENEFIT_TYPE1 ";
                    sqlquery = sqlquery + "LEFT JOIN BenefitType_v3 BT2 ON BT2.StatusCode = CCM.BENEFIT_TYPE2 ";
                    sqlquery = sqlquery + "LEFT JOIN BenefitType_v3 BT3 ON BT3.StatusCode = CCM.BENEFIT_TYPE3  ";
                    sqlquery = sqlquery + "WHERE AL.ACCT_GRP_FK " + (Acct_Value == "" ? "IS NULL" : "LIKE '" + Acct_Value + "'") + " AND CCM.Cmplt " + (Val_Value == "" ? "IS NULL" : "LIKE '" + Val_Value + "'") + " AND CCM.TrackingStatus IN (" + Trk_Value + ") AND CCM.SpareFinished >= '" + StDt_Value + "' AND CCM.SpareFinished <= '" + EnDt_Value + "'";
                }
            }

            SqlConnection con = new SqlConnection(str);
            SqlCommand cmd = new SqlCommand(sqlquery, con);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);

            // Fill the DataSet.
            DataSet ds = new DataSet();
            adapter.Fill(ds, "dailyview");

            // Store the dataset in a session variable
            Session["SSQualOfSub"] = ds;

            // Perform the binding.
            GVQualOfSub.DataSource = ds;
            GVQualOfSub.DataBind();
        }

    }

    private void ExportGridView()
    {
        // Exports the data in the GridView to Excel
        // First call the session variable to refill the gridview
        DataTable gridDataSource = (DataTable)Session["SSQualOfSub"];

        GVQualOfSub.Visible = true;
        GVQualOfSub.DataSource = gridDataSource;
        GVQualOfSub.DataBind();

        string attachment = "attachment; filename=Qual_Of_Subs.xls";
        Response.ClearContent();
        Response.AddHeader("content-disposition", attachment);
        Response.ContentType = "application/ms-excel";
        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);
        GVQualOfSub.RenderControl(htw);
        Response.Write(sw.ToString());
        Response.End();

    }