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();
}
}
}
