protected void btn_Export_Click(object sender, EventArgs e)
{
try
{
Response.Clear();
Response.Buffer = true;
//string filename = "'" + ddl_PlantName.SelectedItem.Text + "' " + DateTime.Now.ToString() + ".xls";
Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
// Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
using (StringWriter sw = new StringWriter())
{
HtmlTextWriter hw = new HtmlTextWriter(sw);
//To Export all pages
AgentwiseLoan.AllowPaging = false;
Datefunc();
Get_AgentwiseLoan();
AgentwiseLoan.HeaderRow.BackColor = Color.White;
foreach (TableCell cell in AgentwiseLoan.HeaderRow.Cells)
{
cell.BackColor = AgentwiseLoan.HeaderStyle.BackColor;
}
foreach (GridViewRow row in AgentwiseLoan.Rows)
{
row.BackColor = Color.White;
foreach (TableCell cell in row.Cells)
{
if (row.RowIndex % 2 == 0)
{
cell.BackColor = AgentwiseLoan.AlternatingRowStyle.BackColor;
}
else
{
cell.BackColor = AgentwiseLoan.RowStyle.BackColor;
}
cell.CssClass = "textmode";
}
}
AgentwiseLoan.RenderControl(hw);
//style to format numbers to string
// string style = @"<style> td { mso-number-format:""" + "\\@" + @"""; }</style>";
string style = @"<style> .textmode { } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
}
catch (Exception ex)
{
WebMsgBox.Show(ex.ToString());
}
}
public void Get_AgentwiseLoan()
{
try
{
DataTable dt = new DataTable();
string cmd = "SELECT * FROM " +
" (SELECT agent_Id,CAST(SUM(loanamount) AS DECIMAL(18,2)) AS LAmount,CAST((SUM(loanamount)-SUM(balance)) AS DECIMAL(18,2)) AS LrecoveryAmount,CAST(SUM(balance) AS DECIMAL(18,2)) AS Lbalance FROM LoanDetails Where company_code='" + ccode + "' AND plant_code='" + pcode + "' AND LLoanFlag=1 GROUP BY agent_Id ) AS L " +
" LEFT JOIN " +
" (SELECT (CONVERT(nvarchar(15),Agent_Id)+'_'+ Agent_Name) AS Agent_Name,Agent_Id AS Aid FROM Agent_Master Where Company_code='" + ccode + "' AND Plant_code='" + pcode + "') AS A ON L.agent_Id=A.Aid ";
SqlDataAdapter adp = new SqlDataAdapter(cmd, connStr);
adp.Fill(dt);
if (dt.Rows.Count > 0)
{
AgentwiseLoan.DataSource = dt;
AgentwiseLoan.DataBind();
AgentwiseLoan.FooterRow.Cells[1].Text = "Total";
AgentwiseLoan.FooterRow.Cells[1].HorizontalAlign = HorizontalAlign.Center;
decimal LAmount = dt.AsEnumerable().Sum(row => row.Field <decimal>("LAmount"));
AgentwiseLoan.FooterRow.Cells[2].HorizontalAlign = HorizontalAlign.Right;
AgentwiseLoan.FooterRow.Cells[2].Text = LAmount.ToString("N2");
decimal LrecoveryAmount = dt.AsEnumerable().Sum(row => row.Field <decimal>("LrecoveryAmount"));
AgentwiseLoan.FooterRow.Cells[3].HorizontalAlign = HorizontalAlign.Right;
AgentwiseLoan.FooterRow.Cells[3].Text = LrecoveryAmount.ToString("N2");
decimal Lbalance = dt.AsEnumerable().Sum(row => row.Field <decimal>("Lbalance"));
AgentwiseLoan.FooterRow.Cells[4].HorizontalAlign = HorizontalAlign.Right;
AgentwiseLoan.FooterRow.Cells[4].Text = Lbalance.ToString("N2");
}
else
{
dt = null;
AgentwiseLoan.DataSource = dt;
AgentwiseLoan.DataBind();
}
}
catch (Exception ex)
{
}
}
