试图使用会话变量将网格视图导出到Excel
本文关键字:视图 Excel 网格 会话 变量 | 更新日期: 2023-09-27 17:52:58
我有一个通过SQL字符串传递几个变量生成的报告。问题是,当我试图将生成的网格视图导出到Excel时,网格视图本身就空白了。我试图使用会话变量来存储数据集,但我不太了解它们。有人能帮忙吗?
这是页面加载时运行的代码:
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();
}
当我尝试导出时,我得到一个错误。
Unable to cast object of type 'System.Data.DataSet' to type 'System.Data.DataTable'.
对不起,我对c#有点陌生。
您正在会话中存储DataSet
,后来您试图访问它作为DataTable
。
你应该这样做:
DataSet gridDataSource = (DataSet)Session["SSQualOfSub"];
代替
DataTable gridDataSource = (DataTable)Session["SSQualOfSub"];
我不确定您的特定场景,但通常在Session
中存储大量数据不是一个好主意,因为它是在服务器上每个用户维护的。
除了当前的错误,你应该使用SqlParameter
与SqlCommand
,你是连接查询和容易SQL注入