protected void btnDownloadSummary_Click(object sender, EventArgs e) { String ConnString = ConfigurationManager.ConnectionStrings["ConcordConnectionString_Report"].ConnectionString; using (SqlConnection conn = new SqlConnection(ConnString)) { DataTable dt = new DataTable(); using (SqlDataAdapter adapter = new SqlDataAdapter()) { using (SqlCommand command = new SqlCommand("DownloadSummaryReport", conn)) { command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@region", ddlRegion.SelectedValue); if (!string.IsNullOrEmpty(ddlMarket.SelectedValue) && ddlMarket.SelectedValue != "-- Select --") { command.Parameters.AddWithValue("@market", ddlMarket.SelectedValue); } adapter.SelectCommand = command; conn.Open(); adapter.Fill(dt); } } conn.Close(); using (MemoryStream xlFileStream = new MemoryStream()) { using (OfficeOpenXml.ExcelPackage xlPkg = new OfficeOpenXml.ExcelPackage()) { int xlRowCnt = 1; int xlColCnt = 1; string sheetName = "Download Intake Summary"; OfficeOpenXml.ExcelWorksheet xlWrkSht1 = xlPkg.Workbook.Worksheets.Add(sheetName); xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Region"; xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Market"; xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Requestor "; xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Site_ID "; xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Status"; xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Desktop_Analysis_Request_Date"; xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Los_Upload_Date"; xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Prelim_Design_Date"; xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Final_Design_Requested_Date"; xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Final_Design_Completed_Date"; xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "PCN_Filed_date"; xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "PCN_Cleared_Date"; xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "BOM_Sent_to_Market_Date"; xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "FCC_601_Filed_Date"; xlWrkSht1.Select("A1:N1"); xlWrkSht1.SelectedRange.Style.Font.Bold = true; xlWrkSht1.SelectedRange.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; xlWrkSht1.SelectedRange.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#B4C6E7")); if (dt != null) { for (int i = 0; i < dt.Rows.Count; i++) { xlRowCnt++; xlWrkSht1.Cells[xlRowCnt, 1].Value = Convert.ToString(dt.Rows[i]["Region"]); xlWrkSht1.Cells[xlRowCnt, 2].Value = Convert.ToString(dt.Rows[i]["Market"]); xlWrkSht1.Cells[xlRowCnt, 3].Value = Convert.ToString(dt.Rows[i]["Requester"]); xlWrkSht1.Cells[xlRowCnt, 4].Value = Convert.ToString(dt.Rows[i]["Site_ID"]); xlWrkSht1.Cells[xlRowCnt, 5].Value = Convert.ToString(dt.Rows[i]["Status"]); xlWrkSht1.Cells[xlRowCnt, 6].Value = ((Convert.ToString(dt.Rows[i]["Desktop_Analysis_Request_Date"]) != null && Convert.ToString(dt.Rows[i]["Desktop_Analysis_Request_Date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["Desktop_Analysis_Request_Date"]).ToString("MM/dd/yyyy") : string.Empty); xlWrkSht1.Cells[xlRowCnt, 7].Value = ((Convert.ToString(dt.Rows[i]["Los_Upload_Date"]) != null && Convert.ToString(dt.Rows[i]["Los_Upload_Date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["Los_Upload_Date"]).ToString("MM/dd/yyyy") : string.Empty); xlWrkSht1.Cells[xlRowCnt, 8].Value = ((Convert.ToString(dt.Rows[i]["Prelim_Design_Date"]) != null && Convert.ToString(dt.Rows[i]["Prelim_Design_Date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["Prelim_Design_Date"]).ToString("MM/dd/yyyy") : string.Empty); xlWrkSht1.Cells[xlRowCnt, 9].Value = ((Convert.ToString(dt.Rows[i]["Final_Design_Requested_Date"]) != null && Convert.ToString(dt.Rows[i]["Final_Design_Requested_Date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["Final_Design_Requested_Date"]).ToString("MM/dd/yyyy") : string.Empty); xlWrkSht1.Cells[xlRowCnt, 10].Value = ((Convert.ToString(dt.Rows[i]["Final_Design_Completed_Date"]) != null && Convert.ToString(dt.Rows[i]["Final_Design_Completed_Date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["Final_Design_Completed_Date"]).ToString("MM/dd/yyyy") : string.Empty); xlWrkSht1.Cells[xlRowCnt, 11].Value = ((Convert.ToString(dt.Rows[i]["PCN_Filed_date"]) != null && Convert.ToString(dt.Rows[i]["PCN_Filed_date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["PCN_Filed_date"]).ToString("MM/dd/yyyy") : string.Empty); xlWrkSht1.Cells[xlRowCnt, 12].Value = ((Convert.ToString(dt.Rows[i]["PCN_Cleared_Date"]) != null && Convert.ToString(dt.Rows[i]["PCN_Cleared_Date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["PCN_Cleared_Date"]).ToString("MM/dd/yyyy") : string.Empty); xlWrkSht1.Cells[xlRowCnt, 13].Value = ((Convert.ToString(dt.Rows[i]["BOM_Sent_to_Market_Date"]) != null && Convert.ToString(dt.Rows[i]["BOM_Sent_to_Market_Date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["BOM_Sent_to_Market_Date"]).ToString("MM/dd/yyyy") : string.Empty); xlWrkSht1.Cells[xlRowCnt, 14].Value = ((Convert.ToString(dt.Rows[i]["FCC_601_Filed_Date"]) != null && Convert.ToString(dt.Rows[i]["FCC_601_Filed_Date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["FCC_601_Filed_Date"]).ToString("MM/dd/yyyy") : string.Empty); } } for (int colCnt = 1; colCnt <= xlColCnt; colCnt++) { xlWrkSht1.Column(colCnt).AutoFit(); } xlWrkSht1.Column(xlColCnt).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; xlWrkSht1.Select("A1:N" + xlRowCnt); xlWrkSht1.SelectedRange.Style.Border.Top.Style = ExcelBorderStyle.Thin; xlWrkSht1.SelectedRange.Style.Border.Left.Style = ExcelBorderStyle.Thin; xlWrkSht1.SelectedRange.Style.Border.Right.Style = ExcelBorderStyle.Thin; xlWrkSht1.SelectedRange.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; xlPkg.SaveAs(xlFileStream); xlWrkSht1.Dispose(); } Response.Clear(); Response.Buffer = true; Response.Charset = ""; Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("content-disposition", "attachment;filename=SqlExport.xlsx"); xlFileStream.WriteTo(Response.OutputStream); Response.Flush(); Response.End(); } } }