Dear Code Masters,
Good afternoon and hope this mail meet you in good health.
Please, i am generating an excel file with data's from sql server using Epplus. The data's are read from the sql server into the EPPLUS generated excel file into the memory it didn't download so that my client can see the data's in the excel.
below is my code for the downloading.
- ExcelPackage pck = new ExcelPackage();
- pck.Workbook.Worksheets.Add("RADET_SHEET");
- ExcelWorksheet ws = pck.Workbook.Worksheets[1];
- object msValue = System.Reflection.Missing.Value;
- ExcelRange ChartRange = ws.Cells["A3:R3"];
- //======== Create XLS Folder for storage =============
- string directoryPath = Server.MapPath(string.Format("~/{0}/", "XLS"));
- if (!Directory.Exists(directoryPath))
- {
- Directory.CreateDirectory(directoryPath);
- }
- //ws.TabColor = ConsoleColor.Blue;
- ws.Cells[1, 1].Value = "State : " + DrpState.SelectedItem.Text.Trim();
- ws.Cells[2, 1].Value = "Facility Name :" + DrpFacName.SelectedItem.Text.Trim();
- ws.Cells[3, 1].Value = "Period From :" + TextBox1.Text.Trim() + " " +
- "TO :" + TextBox2.Text.Trim();
- using (var Range = ws.Cells[5, 2, 5, 19])
- {
- Range.Style.Font.Bold = true;
- Range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
- //Range.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
- Range.Style.Font.Color.SetColor(System.Drawing.Color.WhiteSmoke);
- Range.Style.ShrinkToFit = false;
- Range.Style.WrapText = true;
- //Range.AutoFitColumns();
- }
- for(int ii = 2;ii <= 18; ii++)
- {
- ws.Cells[5,ii].Style.Border.Top.Style = ExcelBorderStyle.Thin;
- ws.Cells[5, ii].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
- ws.Cells[5, ii].Style.Border.Left.Style = ExcelBorderStyle.Thin;
- ws.Cells[5, ii].Style.Border.Right.Style = ExcelBorderStyle.Thin;
- }
- ws.Cells[5, 2].Value = "S/N";
- ws.Cells[5, 3].Value = "Patient ID";
- ws.Cells[5, 4].Value = "Patient Hospital No";
- ws.Cells[5, 5].Value = "Sex";
- ws.Cells[5, 6].Value = "Age at Start of ART (Year)";
- ws.Cells[5, 7].Value = "Age at Start of ART (Month)";
- ws.Cells[5, 8].Value = "ART Start Date";
- ws.Cells[5, 9].Value = "Last Pickup Date";
- ws.Cells[5, 10].Value = "Month of ARV Refill";
- ws.Cells[5, 11].Value = "Regimen Line at ART Start";
- ws.Cells[5, 12].Value = "Regimen at ART Start";
- ws.Cells[5, 13].Value = "Current Regimen Line";
- ws.Cells[5, 14].Value = "Current ART Regimen";
- ws.Cells[5, 15].Value = "Pregnancy Status";
- ws.Cells[5, 16].Value = "Current Viral Load (c/ml)";
- ws.Cells[5, 17].Value = "Date of Current Viral Load";
- ws.Cells[5, 18].Value = "Viral Load Indication";
- ws.Column(19).AutoFit();
- ws.Cells[5, 19].Value = "Current ART Status";
- int rownum = 5;
- int i = 1;
- int j = 1;
- var DAT = new DataTable();
- string SQLD = "SELECT * FROM new_RADET";
- using (SqlConnection con = new SqlConnection(ConnectAll()))
- {
- con.Open();
- SqlDataAdapter DA = new SqlDataAdapter(SQLD, con);
- DA.Fill(DAT);
- foreach(DataRow r1 in DAT.Rows)
- {
- ws.Cells[rownum + 1, 2].Value = i;
- ws.Cells[rownum + 1, 3].Value = r1["Enrol_id"].ToString();
- ws.Cells[rownum + 1, 4].Value = r1["Hopital_id"].ToString();
- ws.Cells[rownum + 1, 5].Value = r1["Sex"].ToString();
- ws.Cells[rownum + 1, 6].Value = r1["Age_Art"].ToString();
- ws.Cells[rownum + 1, 7].Value = r1["AgeLW5_Art"].ToString();
- ws.Cells[rownum + 1, 8].Value = r1["ArtStart_Date"].ToString();
- ws.Cells[rownum + 1, 9].Value = r1["LPickup_Date"].ToString();
- ws.Cells[rownum + 1, 10].Value = r1["Refill_Mnth"].ToString();
- ws.Cells[rownum + 1, 11].Value = r1["Reg_Line1"].ToString();
- ws.Cells[rownum + 1, 12].Value = r1["Reg_ART1"].ToString();
- ws.Cells[rownum + 1, 13].Value = r1["Reg_Line2"].ToString();
- ws.Cells[rownum + 1, 14].Value = r1["Reg_ART2"].ToString();
- ws.Cells[rownum + 1, 15].Value = r1["Preg_Status"].ToString();
- ws.Cells[rownum + 1, 16].Value = r1["cur_vl"].ToString();
- ws.Cells[rownum + 1, 17].Value = Convert.ToDateTime(r1["vlDate"].ToString().Substring(0,10));
- ws.Cells[rownum + 1, 18].Value = r1["VL_Ind"].ToString();
- ws.Cells[rownum + 1, 19].Value = r1["Cur_ART_Status"].ToString();
- i++;
- rownum++;
- }
- // con.Close();
- }
- ws.Column(1).AutoFit();
- ws.Column(2).AutoFit();
- ws.Column(3).AutoFit();
- ws.Column(4).AutoFit();
- ws.Column(5).AutoFit();
- ws.Column(6).AutoFit();
- ws.Column(7).AutoFit();
- ws.Column(8).AutoFit();
- ws.Column(9).AutoFit();
- ws.Column(10).AutoFit();
- ws.Column(11).AutoFit();
- ws.Column(12).AutoFit();
- ws.Column(13).AutoFit();
- ws.Column(14).AutoFit();
- ws.Column(15).AutoFit();
- ws.Column(16).AutoFit();
- ws.Column(17).AutoFit();
- ws.Column(18).AutoFit();
- ws.Column(19).AutoFit();
- string excelName = "\\CIHP_RADET";
- string PathsTo = directoryPath + excelName + ".xlsx";
- if (PathsTo != null) { File.Delete(PathsTo); }
- pck.Workbook.Properties.Title = "RADET";
- this.Response.ClearContent();
- this.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", PathsTo));
- Response.AddHeader("Content-Type", "application/vnd.ms-excel");
- this.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
- this.Response.BinaryWrite(pck.GetAsByteArray());
- Response.Flush();
- Response.End();
Sagar Pandurang KapPosted Feb 21, 2018, 10:49 PM
Abraham OlatubosunPosted Feb 22, 2018, 1:32 AM