c# - how can we give header and title to excel sheet in asp.net in following function

Question

my function which i want to format is as follows

private void GenerateXLSXFile(DataTable tbl)
{
ExcelPackage excelPackage = new ExcelPackage();
ExcelWorkSheet excelWorksheet = excelPackage.Workbook.Worksheets.Add("DemoPage");
excelWorksheet.Cells("A1").LoadFromDataTable(tbl, true);
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;  filename=ExcelDemo.xlsx");
MemoryStream stream = new MemoryStream(excelPackage.GetAsByteArray());
Response.OutputStream.Write(stream.ToArray(), 0, stream.ToArray().Length);
Response.Flush();
Response.Close();
}

Answer 1

Take a temp grid and then if you using datatable use that or use datas as follows...

DataSet ds = ViewState["Data"] as DataSet;
                DataView sortedView = (ds.Tables[0]).DefaultView;
                //DataTable dt = ViewState["Data"].ToString();

                //Create a dummy GridView
                GridView GridView1 = new GridView();
                GridView1.AllowPaging = false;
                GridView1.DataSource = sortedView;
                GridView1.DataBind();
                GridView1.Caption = "ABCD"; //// allows you to give caption to excel sheet
                Response.Clear();
                Response.Buffer = true;
                Response.AddHeader("content-disposition", "attachment;filename=RM.xls");
                Response.Charset = "";
                Response.ContentType = "application/vnd.ms-excel";
                StringWriter sw = new StringWriter();
                HtmlTextWriter hw = new HtmlTextWriter(sw);

                for (int i = 0; i < GridView1.Rows.Count; i++)
                {
                    //Apply text style to each Row
                    GridView1.Rows[i].Attributes.Add("class", "textmode");
                }
                GridView1.Caption = "RM Master";
                GridView1.RenderControl(hw);
                //style to format numbers to string
                string style = @"<style> .textmode { mso-number-format:@; } </style>";
                Response.Write(style);
                Response.Output.Write(sw.ToString());
                Response.Flush();
                Response.End();