.NET export to excel - child rows not displayed

Question

In my asp.net application, I let the user export some data from the view on a button click. The code bellow exports an excel file. The problem is that I can't figure it out how to display the models list of objects.

        var grid = new GridView();
        grid.DataSource = exportModels;
        grid.DataBind();

        Response.ClearContent();
        Response.AddHeader("content-disposition", "attachment; filename=Exported_Orders.xls");
        Response.ContentType = "application/excel";
        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);

        grid.RenderControl(htw);

        Response.Write(sw.ToString());

        Response.End();

The data-source model is this:

public class ExportOrdersViewModel
    {
        public int Id { get; set; }
        public string CustomerFirstName { get; set; }
        public string CustomerLastName { get; set; }
        public string CustomerEmail { get; set; }
        public string CustomerMobile { get; set; }
        public string ShippingStreet { get; set; }
        public string ShippingCity { get; set; }
        public string ShippingCountry { get; set; }
        public string ShippingPostalCode { get; set; }
        public string ShippingTo { get; set; }
        public bool IsShipped { get; set; }
        public ICollection Items { get; set; }

    }

    public class ExportOrdersItemViewModel
    {
        public string TicketName { get; set; }
        public string EventName { get; set; }
        public int Quantity { get; set; }
        public string CurrencyId { get; set; }
        public string PaymentCurrencyId { get; set; }
        public string UnitPrice { get; set; }
        public string PaymentUnitPrice { get; set; }
    }

The ExportOrderViewModel contains a list of ExportOrderItemViewModel and when the file is exported, the item rows of each order are missing form that file. I really need some help on how to add those fields when exporting.

Here is the caption with the exported file:

This is what the grid object contains right before the excel file is created:

user_4592209 · Answer

As @Crowcoder suggested, I used the CLoseXML library and finally succeeded to find a solution for my problem.

This is the code that generates the Excel that I wanted

using (var workBook = new XLWorkbook())
            {
                var workSheet = workBook.Worksheets.Add("Guests");

                var guestTable = new DataTable();
                guestTable.Columns.Add(Resources.Strings.FirstName, typeof(string));
                guestTable.Columns.Add(Resources.Strings.LastName, typeof(string));
                guestTable.Columns.Add(Resources.Strings.TicketName, typeof(string));
                guestTable.Columns.Add(Resources.Strings.TicketType, typeof(string));
                guestTable.Columns.Add(Resources.Strings.ShortCode, typeof(string));
                guestTable.Columns.Add(Resources.Strings.HasVisitorEnteredEvent, typeof(string));
                foreach (var entry in guestList)
                {
                    guestTable.Rows.Add(
                        entry.FirstName,
                        entry.LastName,
                        entry.TicketName,
                        entry.TicketType,
                        entry.ShortCode,
                        entry.HasEntered);
                }
                workSheet.Cell(1, 1).InsertTable(guestTable);
                workSheet.Tables.ForEach(t => t.ShowAutoFilter = false);
                workSheet.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                workSheet.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                workSheet.Tables.ForEach(t => t.Theme = XLTableTheme.TableStyleLight13);
                workSheet.Columns().AdjustToContents();
                workBook.SaveAs(outputStream);
            }

.NET export to excel - child rows not displayed

1 Answers