ASP.NET - How to create Excel file with grouping and multiple outline levels - OpenXML

Watch this example on YouTube - step by step



Please refer to previous posts to see how to download OpenXML from Microsoft Website

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

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

            SheetData sd = new SheetData();
            int i = 1;
            int j = 1;
            for (int x = 1; x < 50; x++)
            {
                Row row1;
                Row row2;
                if (i % 5 == 0)
                {
                    row1 = new Row()
                    {
                        RowIndex = (UInt32)x,
                        Spans = new ListValue<StringValue>() { InnerText = "1:5" },
                        Hidden = false,
                        OutlineLevel = 2,
                        Collapsed = false
                    };
                }
                else
                {
                    row1 = new Row()
                    {
                        RowIndex = (UInt32)x,
                        Spans = new ListValue<StringValue>() { InnerText = "1:5" },
                        OutlineLevel = 3,
                        Collapsed = false,
                        Hidden = true
                    };
                }

                if (j % 10 == 0)
                {
                    x++;
                    row2 = new Row()
                    {
                        RowIndex = (UInt32)x,
                        Spans = new ListValue<StringValue>() { InnerText = "1:10" },
                        OutlineLevel = 1,
                        Hidden = false,
                        Collapsed = false
                    };
                    j = 0;
                    i = 0;
                }
                else
                {
                    row2 = null;
                }

                Cell cell1 = new Cell();
                cell1.DataType = CellValues.String;
                cell1.CellValue = new CellValue("Putin Khu***" + x.ToString().Trim());
                row1.Append(cell1);
                sd.Append(row1);
                if (row2 != null)
                {
                    sd.Append(row2);
                }
                i++;
                j++;
            }
            ws.Append(sd);
            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);

            xl.WorkbookPart.Workbook = wb;
            xl.WorkbookPart.Workbook.Save();
            xl.Close();
            string fileName = "test.xlsx";
            Response.Clear();
            byte[] data1 = fs1.ToArray();

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