public void ProcessRequest(HttpContext context) { string strFileName = Convert.ToString(context.Request.QueryString["filename"]); string strSQL = HttpUtility.UrlDecode(context.Request.QueryString["query"]); clsDBEngine objDB = new clsDBEngine(); objDB.blnOpenDataset(strSQL); DataTable objDT = objDB.dsData.Tables[0]; //FileInfo newFile = new FileInfo(strFileName); using (ExcelPackage package = new ExcelPackage()) { int intRowCount = objDT.Rows.Count; ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Report"); //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1 worksheet.Cells["A1"].LoadFromDataTable(objDT, true, TableStyles.None); var dateColumns = from DataColumn d in objDT.Columns where d.DataType == typeof(DateTime) || d.ColumnName.Contains("Date") select d.Ordinal + 1; foreach (var dc in dateColumns) { worksheet.Cells[2, dc, intRowCount + 1, dc].Style.Numberformat.Format = "d-MMM-yyyy H:m:s"; } ////Format the header for column 1-3 //using (ExcelRange rng = worksheet.Cells["A1:C1"]) //{ // rng.Style.Font.Bold = true; // rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid // rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189)); //Set color to dark blue // rng.Style.Font.Color.SetColor(Color.White); //} ////Example how to Format Column 1 as numeric //using (ExcelRange col = ws.Cells[2, 1, 2 + tbl.Rows.Count, 1]) //{ // col.Style.Numberformat.Format = "#,##0.00"; // col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; //} //Write it back to the client context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; context.Response.AddHeader("content-disposition", "attachment; filename= " + strFileName + ".xlsx"); context.Response.BinaryWrite(package.GetAsByteArray()); } //context.Response.Clear(); //context.Response.Charset = ""; //context.Response.Buffer = true; //context.Response.ContentType = "application/vnd.ms-excel"; //context.Response.AddHeader("content-disposition", "attachment;filename=" + strFileName + ".xlsx"); //System.IO.StringWriter swWriter = new System.IO.StringWriter(); //System.Web.UI.HtmlTextWriter hwHtmlWriter = new System.Web.UI.HtmlTextWriter(swWriter); //System.Web.UI.WebControls.GridView dgData = new System.Web.UI.WebControls.GridView(); //dgData.DataSource = objDB.dsData; //dgData.DataBind(); //dgData.RenderControl(hwHtmlWriter); //context.Response.Write(swWriter.ToString()); //context.Response.Flush(); //context.Response.End(); }