none
Populate large data to Execl RRS feed

  • Question

  • Hi.

    I am trying to transfer multiple data tables ( 20 + data tables) to Excel. Each data table contains 10000 rows and 255 columns.

    I have used the below code in Visual studio 2010 , C#, web based application.

     
    XLWorkbook wb = new XLWorkbook();
    DataSet ds = new DataSet();
    string destpath = @"C:\test.xlsx";
    
    try
      {
        ds = GetDatafromDatabase();
    
        int TableCount = ds.Tables.Count;
        for (int i = 0; i < TableCount; i++)
        {
           DataTable dt = ds.Tables[i];
           wb.Worksheets.Add(dt);
           dt.Dispose(); 
        }
    ds.Dispose();
    wb.SaveAs(destpath);
    }
     catch (Exception ex)
     {
       throw ex;
     }

    Each data table should be added as separate sheet.

    I am getting the Out of Memory exception. How to transfer data to Excel from C# Web application ?

    Thanks

    Friday, July 26, 2019 10:02 AM

All replies

  • ( 20 + data tables) to Excel. Each data table contains 10000 rows and 255 columns.

    I am getting the Out of Memory exception.

    Are you running in a 32 bits environment? You could easily exceed the maximum size. Lets imagine that each of your columns contains 40 bytes. 20 tables x 10000 rows x 255 columns x 40 bytes yields 2 GB. And this only covers the raw data, not the internal structures needed to contain it. Excel itself will not handle it if it is a 32-bt version. And your C# program will also not
    Friday, July 26, 2019 4:15 PM
    Moderator