grouping excel rows with EPPLUS

I see this is already answered but figured I would provide an EPPlus way which you can certainly do but you do need to manually create the sum cells:

[TestMethod]
public void Row_Grouping_Test()
{
    //http://stackoverflow.com/questions/41636336/grouping-excel-rows-with-epplus

    //Throw in some data
    var datatable = new DataTable("tblData");
    datatable.Columns.AddRange(new[]
    {
        new DataColumn("Header", typeof (string)), new DataColumn("Col1", typeof (int)), new DataColumn("Col2", typeof (int)), new DataColumn("Col3", typeof (object))
    });

    for (var i = 0; i < 10; i++)
    {
        var row = datatable.NewRow();
        row[0] = $"Header {i}"; row[1] = i; row[2] = i * 10; row[3] = Path.GetRandomFileName(); datatable.Rows.Add(row);
    }

    //Create a test file
    var fi = new FileInfo(@"c:\temp\Row_Grouping_Test.xlsx");
    if (fi.Exists)
        fi.Delete();

    using (var pck = new ExcelPackage(fi))
    {
        var worksheet = pck.Workbook.Worksheets.Add("Sheet1");
        worksheet.Cells.LoadFromDataTable(datatable, false);

        worksheet.Cells["A11"].Value = "TOTAL";
        worksheet.Cells["B11"].Formula = "SUBTOTAL(9,B2:B10)";
        worksheet.Cells["C11"].Formula = "SUBTOTAL(9,C2:C10)";
        worksheet.Row(11).Style.Font.Bold = true;

        //Row Group 1 (start with 1 since row index is 1-based)
        for (var i = 1; i <= datatable.Rows.Count; i++)
            worksheet.Row(i).OutlineLevel = 1;

        pck.Save();
    }
}

Which looks like this:

enter image description here

Tags:

C#

Excel

Vba

Epplus