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();
// }
// }
//}
}
