ASP.NET - How to create Excel file with grouping in memory using OpenXml

Watch this example on YouTube


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.IO;
using System.IO.Packaging;


            MemoryStream ms = new MemoryStream();
            SpreadsheetDocument sd = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);
            WorkbookPart wbp = sd.AddWorkbookPart();
            WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
            Workbook wb = new Workbook();
            FileVersion fv = new FileVersion();
            fv.ApplicationName = "Microsoft Office Excel";
            Worksheet ws = new Worksheet();

            SheetData sdt = new SheetData();
            for (int i = 1; i < 50; i++)
            {
                Row row1;
                if (i % 5 == 0)
                {
                    row1 = new Row()
                    {
                        //HERE CREATE GROUP
                        RowIndex = (UInt32)i,
                        Spans = new ListValue<StringValue>() { InnerText= "1:5"},
                        Height = 5,
                        DyDescent = 1.50D,
                        Hidden = false,
                        Collapsed = false
                    };
                }
                else
                {
                    row1 = new Row()
                    {
                        RowIndex = (UInt32)i,
                        Spans = new ListValue<StringValue>() { InnerText="1:5"},
                        Height = 5,
                        DyDescent = 1.50D,
                        OutlineLevel = 1,
                        Collapsed = false,
                        Hidden = true
                    };
                }
                Cell cell1 = new Cell();
                cell1.DataType = CellValues.String;
                cell1.CellValue = new CellValue("DataValue " + i.ToString());
                row1.Append(cell1);
                sdt.Append(row1);
            }
            ws.Append(sdt);
            wsp.Worksheet = ws;
            wsp.Worksheet.Save();

            Sheets sheets = new Sheets();
            Sheet sheet = new Sheet();
            sheet.Name = "mySheet";
            sheet.SheetId = 1;
            sheet.Id = wbp.GetIdOfPart(wsp);
            sheets.Append(sheet);
            wb.Append(fv);
            wb.Append(sheets);

            sd.WorkbookPart.Workbook = wb;
            sd.WorkbookPart.Workbook.Save();
            sd.Close();
            string FileName = "SomeFile.xlsx";

            Response.Clear();
            byte[] dt = ms.ToArray();

            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheet.sheet";
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", FileName));
            Response.BinaryWrite(dt);
            Response.End();