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) { } }