Creating multiple header rows in an excel file with EPPlus.Core

Question

I will be using EPPlus.Core to generate excel reports in my .Net Core project. So after digging Google to find some sample code snippets I found the following blog:

Create Excel Files in C#

I can create an excel file with a single header with the following code:

using (ExcelPackage excel = new ExcelPackage())
{
  excel.Workbook.Worksheets.Add("Worksheet1");
  excel.Workbook.Worksheets.Add("Worksheet2");
  excel.Workbook.Worksheets.Add("Worksheet3");

  var headerRow = new List<string[]>()
  {
    new string[] { "ID", "First Name", "Last Name", "DOB" }
  };

  // Determine the header range (e.g. A1:D1)
  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);

  FileInfo excelFile = new FileInfo(@"C:\Users\amir\Desktop\test.xlsx");
  excel.SaveAs(excelFile);
}

But I couldn't find out how to add multiple header rows on a single worksheet. For instance, the first table has ID, Name, Price headers and the second table has Notes and Price headers. Is it possible to add multiple tables into a worksheet with EPPlus.Core? The code snippet above adds only a single header row from A1 to D1. Let's say I wanna add another header row from A4 to J4. Any suggestions?

Seems like you'd need to copy and adjust the existing lines which add the headers on Row1 - did you try that? — Tim Williams
– Tim Williams, Commented Feb 11, 2020 at 23:35
Yes I did and it did crash. It error is like "the array index was outside.." This component can only add one header row to each worksheet or I couldn't find the magic method and/or usage on the SDK document yet. — thus
– thus, Commented Feb 11, 2020 at 23:57
If you show the code you tried and include the error you got, you're more likely to get help here. — Tim Williams
– Tim Williams, Commented Feb 12, 2020 at 0:00

thus · Accepted Answer · 2020-02-12 00:11:38Z

3

And yet I found the answer. Hope this will help others to save their time. It is not the best of best approach but this is what I need. I defined the coordinates on the Cells array by adding "A1:J1" and "A3:E3".

 var worksheet = excel.Workbook.Worksheets["Worksheet1"];
 worksheet.Cells["A1:J1"].LoadFromArrays(header1Data);
 worksheet.Cells["A1:J1"].Style.Font.Bold = true;
 worksheet.Cells[2, 1].LoadFromArrays(array1Data);

 worksheet.Cells["A3:E3"].LoadFromArrays(header2Data);
 worksheet.Cells["A3:E3"].Style.Font.Bold = true; 
 worksheet.Cells[4, 1].LoadFromArrays(array2Data);

answered Feb 12, 2020 at 0:11

thus

1,5951 gold badge16 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating multiple header rows in an excel file with EPPlus.Core

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related