private void ShowData_ForExcel()
{
CreamBell_DMS_WebApps.App_Code.Global obj = new CreamBell_DMS_WebApps.App_Code.Global();
string FilterQuery = string.Empty;
SqlConnection conn = null;
SqlCommand cmd = null;
string query = string.Empty;
bool endRequest = false;
try
{
conn = new SqlConnection(obj.GetConnectionString());
conn.Open();
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.Text;
if (rdWorking.Checked)
{
query = "GetDataForUseNonuseReport '" + txtFromDate.Text + "','" + txtToDate.Text + "','" + ddlstate.SelectedValue + "','" + rdWorking.Text + "'";
}
else if (rdNotWorking.Checked)
{
query = "GetDataForUseNonuseReport '" + txtFromDate.Text + "','" + txtToDate.Text + "','" + ddlstate.SelectedValue + "','" + rdNotWorking.Text + "'";
}
else
{
query = "GetDataForUseNonuseReport '" + txtFromDate.Text + "','" + txtToDate.Text + "','" + ddlstate.SelectedValue + "'";
}
cmd.CommandText = query;
//cmd.Parameters.AddWithValue("@SITEID", Session["SiteCode"].ToString());
//cmd.Parameters.AddWithValue("@DATAAREAID", Session["DATAAREAID"].ToString());
DataTable dt = new DataTable();
dt = new DataTable();
dt.Load(cmd.ExecuteReader());
//=================Create Excel==========
//var workbook = new ExcelFile();
//// Add new worksheet to Excel file.
//var worksheet = workbook.Worksheets.Add("New worksheet");
//// Set the value of the cell "A1".
//worksheet.Cells["A1"].Value = "Hello world!";
//// Save Excel file.
//workbook.Save("Workbook.xls");
string attachment = "attachment; filename=Usenousereport.xls";
Response.ClearContent();
StringWriter strwritter = new StringWriter();
HtmlTextWriter htmltextwrtter = new HtmlTextWriter(strwritter);
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("content-disposition", attachment);
string tab = "";
foreach (DataColumn dc in dt.Columns)
{
Response.Write(tab + dc.ColumnName);
tab = "\t";
}
Response.Write("\n");
int i;
foreach (DataRow dr in dt.Rows)
{
tab = "";
for (i = 0; i < dt.Columns.Count; i++)
{
Response.Write(tab + dr[i].ToString());
tab = "\t";
}
Response.Write("\n");
}
Response.End();
}
catch (Exception ex)
{
ErrorSignal.FromCurrentContext().Raise(ex);
}
finally
{
if (conn != null)
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
}
}
private void ExportToExcelNew()
{
SqlConnection conn = null;
SqlCommand cmd = null;
DataTable dtDataByfilter = null;
string query = string.Empty;
conn = new SqlConnection(obj.GetConnectionString());
conn.Open();
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandTimeout = 3600;
cmd.CommandType = CommandType.StoredProcedure;
query = "[dbo].[ACX_GETUSERDETAILS]";
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@State", ddlState.SelectedItem.Value);
if (ddlSiteId.SelectedItem.Value == "All")
{
cmd.Parameters.AddWithValue("@SITECODE", "");
}
else
{
cmd.Parameters.AddWithValue("@SITECODE", ddlSiteId.SelectedItem.Value);
}
if (rdoDistributor.Checked == true)
{
cmd.Parameters.AddWithValue("@USERTYPE", "DISTRIBUTOR");
}
else
{
cmd.Parameters.AddWithValue("@USERTYPE", "PSRVRS");
}
dtDataByfilter = new DataTable();
dtDataByfilter.Load(cmd.ExecuteReader());
DataTable dt = new DataTable();
dt = dtDataByfilter;
Global.ExportDataTable(dt, "UserLoginDetails.xls", "EXCEL", "UserLoginDetails");
//Response.Clear();
//Response.Buffer = true;
//Response.ClearContent();
//Response.ClearHeaders();
//Response.Charset = "";
//string FileName = "UserLoginDetails" + DateTime.Now + ".xls";
//StringWriter strwritter = new StringWriter();
//HtmlTextWriter htmltextwrtter = new HtmlTextWriter(strwritter);
//Response.Cache.SetCacheability(HttpCacheability.NoCache);
//Response.ContentType = "application/vnd.ms-excel";
//Response.AddHeader("Content-Disposition", "attachment;filename=" + FileName);
//gridLoginDetails.GridLines = GridLines.Both;
//gridLoginDetails.HeaderStyle.Font.Bold = true;
//gridLoginDetails.RenderControl(htmltextwrtter);
//Response.Write(strwritter.ToString());
//Response.End();
}
private DataTable GetData(string strProcName, string strReportName, CreamBell_DMS_WebApps.App_Code.Global obj, ref DataTable dtSetHeader)
{
string query1 = "Select NAME from ax.inventsite where SITEID='" + Session["SiteCode"].ToString() + "'";
dtSetHeader = new DataTable();
dtSetHeader = obj.GetData(query1);
SqlConnection conn = null;
SqlCommand cmd = null;
DataTable dtDataByfilter = null;
string query = string.Empty;
conn = new SqlConnection(obj.GetConnectionString());
conn.Open();
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandTimeout = 3600;
cmd.CommandType = CommandType.StoredProcedure;
query = strProcName;
cmd.CommandText = query;
DateTime now = Convert.ToDateTime(txtToDate.Text);
now = now.AddMonths(1);
cmd.Parameters.AddWithValue("@FromDate", Convert.ToDateTime(txtFromDate.Text));
cmd.Parameters.AddWithValue("@ToDate", Convert.ToDateTime(txtToDate.Text));
string SiteList = "";
foreach (ListItem item in chkListSite.Items)
{
if (item.Selected)
{
if (SiteList == "")
{
SiteList += "" + item.Value.ToString() + "";
}
else
{
SiteList += "," + item.Value.ToString() + "";
}
}
}
if (SiteList.Length > 0)
{
cmd.Parameters.AddWithValue("@SITEID", SiteList);
}
else
{
cmd.Parameters.AddWithValue("@SITEID", "");
}
string strStateCode = "";
foreach (ListItem item in chkListState.Items)
{
if (item.Selected)
{
if (strStateCode == "")
{
strStateCode += "" + item.Value.ToString() + "";
}
else
{
strStateCode += "," + item.Value.ToString() + "";
}
}
}
if (strStateCode.Length > 0)
{
cmd.Parameters.AddWithValue("@STATECODE", strStateCode);
}
else
{
cmd.Parameters.AddWithValue("@STATECODE", "");
}
cmd.Parameters.Add("@SupplyCode", SqlDbType.NVarChar, 40000).Direction = ParameterDirection.Output;
dtDataByfilter = new DataTable();
dtDataByfilter.Load(cmd.ExecuteReader());
DataTable dt = new DataTable();
dt = dtDataByfilter;
SupplierCode = Convert.ToString(cmd.Parameters["@SupplyCode"].Value);
return(dt);
}