How to add the customer header to the Excel Sheet -closed xml package-CodePudding

I need to add customer headers to the excel sheet which are programmatically created in c# using closed xml package. I want go generate the excel sheet like this below.

 --------------------------------------- -----------
|Generated till : current month         |
 ----------- ------------- ------------- -----------
|reported generate time: current time
 --------------------------------------- ----------- 
|           | current     | renewal     |  Total    |
 --------------------------------------------------- 
| Month     | Emp.Count   |   Emp.Count | Emp.Count |
 ----------- ------------- ------------- ----------- 
| Dec 22    | 10          |    12       | 22        |
| Nov 22    | 8           |     2       | 10        |
| Oct 22    | 3           |     3       |6          |
 --------------------------------------------------- 
|Total      | 21          |  17         |38         |
 ---------------------------------------------------

But, i unable to get like that. My coding like below,

           DataTable dt = new DataTable("Grid");
           dt.Columns.AddRange(new DataColumn[8] { new DataColumn("Billing Description"),
                                              new DataColumn("Month"),
                                              new DataColumn("Emp.Count New"),
                                              new DataColumn("Emp.Count Renewal"),
                                              new DataColumn("Emp.Count Total")});
            List<student> details = new StudentRepository().GetCount();

            for(int i=0; i< details.Count; i  )
            {
                dt.Rows.Add((i 1)   "Month  Billing",
                    details[i].Month,
                    details[i].new, 
                    details[i].renewl,
                   
                    details[i].total,
                   
                    );
            }

            using (XLWorkbook wb = new XLWorkbook())
            {
                wb.Worksheets.Add(dt);
                using (MemoryStream stream = new MemoryStream())
                {
                    wb.SaveAs(stream);
                    return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Summary.xlsx");
                }
            }

I have tried to add the row but its over-writing on the columns header. Please help me to figure it out.

CodePudding user response：

You can add the headers to a new worksheet and then insert the datatable to an specific cell. for example:

      using (XLWorkbook wb = new XLWorkbook())
      {
        var ws = wb.Worksheets.Add();
        ws.Cell(1, 1).Value = "Generated till : current month";
        ws.Cell(2, 1).Value = "reported generate time: current time";
        ws.Cell(3, 1).InsertTable(dt);
        using (MemoryStream stream = new MemoryStream())
        {
          wb.SaveAs(stream);
          return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Summary.xlsx");
        }
      }