How to Export multiple headers to excel using asp.net

Question

User1016820894 posted

I am exporting data from sql to an excel spreadsheet in asp.net code behind (code is below). It is working fine but I need to add 2 header rows at the top before the normal header rows and data. One of the header rows needs to be merged and centered across several columns. How do I do this?

Thanks.

           try
            {
 
                SQLCmd = "SELECT Name, DOB, Address, City, State, Zip " +
                         "FROM dbo.tbldata; ";
                sheetname = "sheet1;
                filename = "Excel Report";
                
                using (SqlCommand cmd = new SqlCommand(SQLCmd))
                {
                    using (SqlDataAdapter sda = new SqlDataAdapter())
                    {
                        cmd.Connection = connsql;
                        sda.SelectCommand = cmd;

                        using (DataTable dt = new DataTable())
                        {                           
                            sda.Fill(dt);
                            
                            using (XLWorkbook wb = new XLWorkbook())
                            {
                                wb.Worksheets.Add(dt, sheetname);
                               
                                Response.Clear();
                                Response.Buffer = true;
                                Response.Charset = "";
                                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                                Response.AddHeader("content-disposition", "attachment;filename=" + filename + ".xlsx");

                                using (MemoryStream MyMemoryStream = new MemoryStream())
                                {
                                    wb.SaveAs(MyMemoryStream);

                                    MyMemoryStream.WriteTo(Response.OutputStream);

                                    Response.Flush();

                                }
                            }
                        }
                    }
                }

            }
            catch (Exception Ex)
            {
                ClientScript.RegisterStartupScript(GetType(), "showError", "alert('" + Ex.Message + "');", true);
                return "error";
            }
            finally
            {
                Response.End();
            }

Answer 1

User2103319870 posted

I need to add 2 header rows at the top before the normal header rows and data. One of the header rows needs to be merged and centered across several columns. How do I do this?

Try with below code

try
			{

				SQLCmd = "SELECT Name, DOB, Address, City, State, Zip " +
						 "FROM dbo.tbldata; ";
				sheetname = "sheet1;
				filename = "Excel Report";
				using (SqlCommand cmd = new SqlCommand(SQLCmd))
				{
					using (SqlDataAdapter sda = new SqlDataAdapter())
					{
						cmd.Connection = connsql;
						sda.SelectCommand = cmd;

						using (DataTable dt = new DataTable())
						{
							sda.Fill(dt);

							using (XLWorkbook wb = new XLWorkbook())
							{
								//Add only worksheets name here
								var ws = wb.Worksheets.Add(sheetname);
								//Adding extra header
								//Update the range values as per your need
								var headerrange1 = ws.Range("A1:H1");
								//Merge the header across multiple column
								headerrange1.Merge().Style.Font.SetBold().Font.FontSize = 10;
								ws.Cell(1, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
								ws.Cell(1, 1).Value = "Extra Header1";


								var headerrange2 = ws.Range("A2:H2");
								headerrange2.Merge().Style.Font.SetBold().Font.FontSize = 10;
								ws.Cell(2, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
								ws.Cell(2, 1).Value = "Extra Header2";
								//Starts excel data from 3rd row after first two headers
								ws.Cell(3, 1).InsertTable(dt);

								Response.Clear();
								Response.Buffer = true;
								Response.Charset = "";
								Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
								Response.AddHeader("content-disposition", "attachment;filename=" + filename + ".xlsx");


								using (MemoryStream MyMemoryStream = new MemoryStream())
								{
									wb.SaveAs(MyMemoryStream);
									MyMemoryStream.WriteTo(Response.OutputStream);
									Response.Flush();
								}
							}
						}
					}
				}

			}