How to set Header Title in top of the excel sheet in EPPlus

Question

I can export excel using EPPlus library. When I have added the Header and Footer it can only show when I click the Ctrl + P, I mean only in print preview. Now I want to set the title in the top row with marge the all cell and then write the column header. So how can I set the title text in the 1st row of the sheet and column Header of the table in the secound list.

 using (ExcelPackage excel = new ExcelPackage())
                    {
                        var sheet = excel.Workbook.Worksheets.Add("Worksheet1");
    
                        var headerRow = new List<string[]>()
                        {
                            new string[]
                            {
                                "Transaction Id", "Date", "Time", "Id", "Name", "Amount"
                            }
                        };
    
                        string headerRange = "A1:" + Char.ConvertFromUtf32(headerRow[0].Length + 64) + "1";
                        // Target a worksheet
                        var worksheet = excel.Workbook.Worksheets["Worksheet1"];   
                       // Popular header row data
                        worksheet.Cells[headerRange].LoadFromArrays(headerRow);    
                        var totalNoOfRows = depositList.Count() + 1;
    
                        //ExcelWorksheet ws = worksheet.Workbook.Worksheets.Add("Demo");
                        //ws.Cells["A1:G1"].Merge = true;
    
                        // Header Text Setup 
                        var header = sheet.HeaderFooter.OddHeader;
                        header.CenteredText = "&18&U&\"Times New Roman,Regular Bold\"&14& " + ClientName + " \n  Report \n";
                        worksheet.PrinterSettings.TopMargin = 1;
                        // Footer Text Setup 
                        ExcelHeaderFooterText footer = sheet.HeaderFooter.OddFooter;
                        header.RightAlignedText = "&10&P of &N";
                        footer.LeftAlignedText = "&16&\"Aril, Bold\"Download Date and Time " + DateTime.Now;            
   
    
                        using (MemoryStream stream = new MemoryStream())
                        {
                            excel.SaveAs(stream);
                            return File(stream.ToArray(),
                                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                                "Report(" + ClientName + ").xlsx");
                        }
                    }

I have added the the image and I am confused to set the Table Header row in the 2nd row.

ExcelWorksheet ws = worksheet.Workbook.Worksheets.Add("Demo");
ws.Cells["A1:J1"].Merge = true;
var headerRow = new List<string[]>()
                            {
                                new string[]
                                {
                                    "Transaction Id", "Date", "Time", "Id", "Name", "Amount"
                                }
                            };

Answer 1

I do not think what you are trying to do, is a possibility with EPPlus. EPPlus provides only that functionality that excel supports by itself and Excel itself does not support viewing the header and footer in edit mode. This is what the documentation about Headers and footers has to say regarding this. This is applicable to Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel Starter 2010.

Headers and footers are displayed only in Page Layout view, Print Preview, and on printed pages.

Further if you take a look at the source of the ExcelHeaderFooter class, there is no provision that deals with viewing in Page Layout view or anything related (you can set have printer settings set on the worksheet though).

Update

You can accommodate the Title in the first row with the below lines of code

// This is similar to the snippet that you have added.
// You can specify your custom range, content and required styling.
sheet.Cells["A1:J1"].Merge = true;
sheet.Cells["A1"].Value = "Title";

Further, the header row can be added below the title row as shown below

// Notice here, this is similar to what you were trying. However, we have A2
// as the beginning of our headerRange and not A1. Similarly, notice the modification
// done to the upper bound so as to have a valid excel range.
// You can play around with the range here. But key takeaway is the second row we are dealing with
string headerRange = "A2:" + Char.ConvertFromUtf32(headerRow[0].Length + 64) + "2";