protected void Page_Load(object sender, EventArgs e) { SqlConnection con = new SqlConnection(strConnString); SqlCommand command = new SqlCommand("BenReport", con) { CommandType = System.Data.CommandType.StoredProcedure }; SqlDataAdapter sda = new SqlDataAdapter(); command.Connection = con; sda.SelectCommand = command; DataSet ds = new DataSet(); sda = new SqlDataAdapter("BenReport", con); sda.Fill(ds); GRDBencount.DataSource = ds.Tables[0]; GRDBencount.DataBind(); con.Close(); } protected void Button1_Click(object sender, EventArgs e) { ExtractBen(); } public void ExtractBen() { SqlConnection connex = new SqlConnection(strConnString); SqlCommand command = new SqlCommand("spTest", connex) { CommandType = System.Data.CommandType.StoredProcedure }; SqlDataAdapter sda = new SqlDataAdapter(); command.Connection = connex; sda.SelectCommand = command; command.CommandTimeout = 600; DataSet ds = new DataSet(); sda = new SqlDataAdapter("spTest", connex); sda.Fill(ds); if (ds.Tables.Count > 0) { MemoryStream ms = new MemoryStream(); int i = 1; using (ExcelPackage package = new ExcelPackage(ms)) { foreach (DataTable table in ds.Tables) { ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("sheet" + i++); if (worksheet.Name == "sheet1") { worksheet.Name = "Bencount"; } if (worksheet.Name == "sheet2") { worksheet.Name = "validateWages"; } if (worksheet.Name == "sheet3") { worksheet.Name = "BeneficiariestobeExctracted"; } if (worksheet.Name == "sheet4") { worksheet.Name = "Beneficiaries_with_0_Wdays"; } if (worksheet.Name == "sheet5") { worksheet.Name = "beneficiaries_with_wages_but_no_account_numbers"; } if (worksheet.Name == "sheet6") { worksheet.Name = "account_number>11_characters/wages<100"; } if (worksheet.Name == "sheet7") { worksheet.Name = " beneficiary_appear on more than project for the same month"; } if(worksheet.Name == "sheet8") { worksheet.Name = "account_number_shared_by_more_than_one_beneficiary"; } if (worksheet.Name == "sheet9") { worksheet.Name = "beneficiary_appear_on_more_than_project_for_the_same_month"; } if (worksheet.Name == "sheet10") { worksheet.Name = "Missing_Beneficiaries"; } worksheet.Cells["A1"].LoadFromDataTable(table, true); worksheet.Cells["A1:Z1"].AutoFitColumns(); worksheet.Cells["A1:Z1"].Style.Font.Bold = true; } Response.Clear(); package.SaveAs(Response.OutputStream); Response.AddHeader("content-disposition", "attachchment; filename=BeneficiaryDetails.xls"); Response.Charset = ""; Response.ContentType = "application/vnd.xls"; Response.End(); } } }
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)