1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66

public static MemoryStream WriteMultipleListsToExcel_OpenXml(params (string sheetName, IEnumerable<object> list)[] sections)
{
   
    var memoryStream = new MemoryStream();

    using (var spreadsheetDocument = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook))
    {
        var workbookPart = spreadsheetDocument.AddWorkbookPart();
        workbookPart.Workbook = new Workbook();

        var workbookProps = new WorkbookProperties() { DefaultThemeVersion = 124226 }; // Ensure workbook properties
        workbookPart.Workbook.AppendChild(workbookProps);

        var sheets = workbookPart.Workbook.AppendChild(new Sheets());

        uint sheetId = 1;

        foreach (var (sheetName, list) in sections)
        {
            var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet(new SheetData());

            var sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = sheetId++, Name = sheetName };
            sheets.Append(sheet);

            var sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
            var type = list.GetType().GetGenericArguments()[0];
            var properties = type.GetProperties();

            // Add header row
            var headerRow = new Row();
            foreach (var prop in properties)
            {
                headerRow.Append(CreateTextCell(prop.Name));
            }
            sheetData.Append(headerRow);

            // Add data rows
            foreach (var item in list)
            {
                var dataRow = new Row();
                foreach (var prop in properties)
                {
                    var value = prop.GetValue(item)?.ToString() ?? string.Empty;
                    dataRow.Append(CreateTextCell(value));
                }
                sheetData.Append(dataRow);
            }

            worksheetPart.Worksheet.Save();
        }

        workbookPart.Workbook.Save();
    }

    memoryStream.Position = 0;
    return memoryStream;
}
private static Cell CreateTextCell(string value)
{
    return new Cell
    {
        CellValue = new CellValue(value),
        DataType = CellValues.String
    };
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

public class Person
{
    public string Name { get; set; }
    public int Age { get; set; }
}

public class Product
{
    public string Name { get; set; }
    public decimal Price { get; set; }
}

public class Order
{
    public int OrderId { get; set; }
    public decimal Amount { get; set; }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

var people = new List<Person>
{
    new Person { Name = "John", Age = 30 },
    new Person { Name = "Jane", Age = 25 }
};

var products = new List<Product>
{
    new Product { Name = "Laptop", Price = 1200 },
    new Product { Name = "Phone", Price = 800 }
};

var orders = new List<Order>
{
};

var memoryStream = WriteMultipleListsToExcel_OpenXml(("People", people), ("Products", products), ("Orders", orders));

File.WriteAllBytes("combined_data_openxml.xlsx", memoryStream.ToArray());
Console.WriteLine("Excel file created successfully using OpenXML!");