Iterate through dataset and pass it to same Excel for Export

Question

User-73514677 posted

Hi.

I am trying to export dataset to a Excel using ClosedXML option. I am using VS 2010 C# ASP.NET Web application.

I have tried the code as below:

 DataSet ds = getDataSetExportToExcel(con);
                    using (XLWorkbook wb = new XLWorkbook())
                    {
                        wb.Worksheets.Add(ds);

                        Response.Clear();
                        Response.Buffer = true;
                        Response.Charset = "";
                        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                        Response.AddHeader("content-disposition", "attachment;filename= " + filename1 + ".xlsx");
                        using (MemoryStream MyMemoryStream = new MemoryStream())
                        {
                            wb.SaveAs(MyMemoryStream);
                            MyMemoryStream.WriteTo(Response.OutputStream);

                            Response.Flush();
                            Response.End();
                        }
                    }

The dataset contains 4 data tables . With the above code, I am getting Memory out exception in the line:  

  wb.Worksheets.Add(ds);

How to iterate the dataset for each of the datatables and then pass it to the same excel as multiple sheets ?

Thanks

Answer 1

User1120430333 posted

You could use a List of DTO(s) as opposed to dataset with datatable, becuase a List of custom objects uses  6 times less memory than a datatable.

http://lauteikkehn.blogspot.com/2012/03/datatable-vs-list.html

The example DtoCache  holds several LIsts of DTO*s) in it.

https://www.codeproject.com/Articles/1050468/Data-Transfer-Object-Design-Pattern-in-Csharp

using System.Collections.Generic;

namespace Entities
{
    public class DtoCache
    {
        public List<DtoProjectType> ProjectTypes { get; set; } = new List<DtoProjectType>();
        public List<DtoStatus> Statuses { get; set; } = new List<DtoStatus>();
        public List<DtoResource> Resources { get; set; } = new List<DtoResource>();
        public List<DtoDuration> Durations { get; set; } = new List<DtoDuration>();
    }
}

Answer 2

User-73514677 posted

HI.  Thanks for the reply.

If using list, how to iterate it multiple times so that the data is passed to same excel and in multiple sheets.

Answer 3

User-73514677 posted

The issue is due to the row limit in excel.

Is there a way in which if the row count is more than 62000, then move the next set of data to the next sheet.

Answer 4

User1120430333 posted

HI.  Thanks for the reply.

If using list, how to iterate it multiple times so that the data is passed to same excel and in multiple sheets.

You would use a for each loop on the List of objects to iterate list/collection.