private void ExportToExcelNew() { //CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global(); //SqlConnection conn = null; string FromDate = txtFromDate.Text; string ToDate = txtToDate.Text; try { //conn = new SqlConnection(obj.GetConnectionString()); //conn.Open(); //bool b = ValidateInput(); //if (b == true) { //GridView gvvc = new GridView(); //CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global(); string Stateid = App_Code.Global.GetSelectState(ref lstState, false); string siteid = App_Code.Global.GetSelectState(ref lstSiteId, false); string CustGroup = App_Code.Global.GetSelectState(ref lstCustGroup, false); string BuList = App_Code.Global.GetSelectBUList(ref lstbu, false); string CustList = App_Code.Global.GetSelectCustMaster(ref lstCust, false); string query = "EXEC USP_GETVRSSPDISCOUNTREPORT '" + Stateid + "','" + siteid + "','" + BuList + "','" + CustGroup + "','" + CustList + "','" + Session["USERID"].ToString() + "','" + Session["LOGINTYPE"].ToString() + "','" + FromDate + "','" + ToDate + "' "; List <SqlParameter> sqlParameters = new List <SqlParameter>(); SqlParameter stateIdParam = new SqlParameter("@STATEID", SqlDbType.NVarChar, 5000); stateIdParam.Value = Stateid; sqlParameters.Add(stateIdParam); SqlParameter siteIdParam = new SqlParameter("@SITEID", SqlDbType.NVarChar, 5000); siteIdParam.Value = siteid; sqlParameters.Add(siteIdParam); SqlParameter buParam = new SqlParameter("@BU", SqlDbType.NVarChar, 5000); buParam.Value = BuList; sqlParameters.Add(buParam); SqlParameter custgroupParam = new SqlParameter("@CUSTGROUP", SqlDbType.NVarChar, 5000); custgroupParam.Value = CustGroup; sqlParameters.Add(custgroupParam); SqlParameter customerParam = new SqlParameter("@CUSTOMER", SqlDbType.NVarChar, 5000); customerParam.Value = CustList; sqlParameters.Add(customerParam); SqlParameter userCodeParam = new SqlParameter("@USERCODE", SqlDbType.NVarChar, 20); userCodeParam.Value = Session["USERID"].ToString(); sqlParameters.Add(userCodeParam); SqlParameter userTypeParam = new SqlParameter("@USERTYPE", SqlDbType.Int); userTypeParam.Value = Session["LOGINTYPE"].ToString(); sqlParameters.Add(userTypeParam); SqlParameter fromDateParam = new SqlParameter("@FROMDATE", SqlDbType.DateTime); fromDateParam.Value = FromDate; sqlParameters.Add(fromDateParam); SqlParameter toDateParam = new SqlParameter("@TODATE", SqlDbType.DateTime); toDateParam.Value = ToDate; sqlParameters.Add(toDateParam); DataSet ds = CreamBellFramework.GetDataSetFromStoredProcedure("USP_GETVRSSPDISCOUNTREPORT", sqlParameters.ToArray()); // string query = "EXEC USP_GETVRSSPDISCOUNTREPORT '" + lstState.SelectedValue + "','" + Session["SiteCode"].ToString() + "','" + lstbu.SelectedValue + "','" + lstCustGroup.SelectedValue + "','" + lstCust.SelectedItem.Value + "','" + Session["USERID"].ToString() + "','" + Session["LOGINTYPE"].ToString() + "','" + FromDate + "','" + ToDate + "' "; //using (SqlCommand cmd = new SqlCommand(query)) //{ // cmd.CommandTimeout = 600; // using (SqlDataAdapter sda = new SqlDataAdapter()) // { // cmd.Connection = obj.GetConnection(); // sda.SelectCommand = cmd; // using (DataSet ds = new DataSet()) // { //sda.Fill(ds); using (XLWorkbook wb = new XLWorkbook()) { wb.Worksheets.Add(ds.Tables[0], "Detail"); wb.Worksheets.Add(ds.Tables[1], "Summary"); Response.Clear(); Response.Buffer = true; Response.Charset = ""; Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("content-disposition", "attachment;filename=VRSSpecialDiscountReport.xlsx"); using (MemoryStream MyMemoryStream = new MemoryStream()) { wb.SaveAs(MyMemoryStream); MyMemoryStream.WriteTo(Response.OutputStream); Response.Flush(); Response.End(); } } // } // } //} } } catch (Exception ex) { ErrorSignal.FromCurrentContext().Raise(ex); if (ex.Message.ToString() != "Thread was being aborted.") { ScriptManager.RegisterStartupScript(this, typeof(Page), "Validation", "alert('" + ex.Message.ToString().Replace("'", "") + "');", true); } //LblMessage.Text = ex.Message.ToString(); } //finally //{ // if (conn != null) // { // if (conn.State == ConnectionState.Open) // { // conn.Close(); // conn.Dispose(); // } // } //} }