locked
Closed Xml: OutOfMemory Exception when adding datatable to workbook RRS feed

  • Question

  • User-73514677 posted

     Hi. I am trying to use Closed XML in a C# ASP.NET Web application. I have a dataset which contains more than 30 data tables. I want to pass each of the data table as a separate worksheet and then create the excel.

    Each data table has 10000 rows and 255 columns. I am getting the error :

        {System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.
       at ClosedXML.Excel.XLRangeBase.Cell(XLAddress cellAddressInRange)
       at ClosedXML.Excel.XLRangeBase.Cell(Int32 row, Int32 column)
       at ClosedXML.Excel.XLCell.SetValue[T](T value, Int32 ro, Int32 co)
       at ClosedXML.Excel.XLCell.InsertTable[T](IEnumerable`1 data, String tableName, Boolean createTable)
       at ClosedXML.Excel.XLCell.InsertTable(DataTable data, String tableName, Boolean createTable)
       at ClosedXML.Excel.XLCell.InsertTable(DataTable data)
       at ClosedXML.Excel.XLWorksheets.Add(DataTable dataTable, String sheetName)
       at ClosedXML.Excel.XLWorksheets.Add(DataTable dataTable)

    I have tried to use the below code

    XLWorkbook wb = new XLWorkbook();
    foreach (DataTable dt in ds.Tables)
    {
       wb.Worksheets.Add(dt);
    }
    
    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();
    }
    wb.Dispose();

    The error is happening in the line

    wb.Worksheets.Add(dt);

    The first datatable is added and then the loop moves to the second datatable. In the second instance, I am getting the error.

    Is there a limit on the number of rows and columns which can be used in Closed Xml ? How to fix this?

    Thanks

    Tuesday, July 9, 2019 10:39 AM

All replies

  • User753101303 posted

    Hi,

    More likely you just keep too much in memory ie you have each of your 30 DataTables in memory plus the Excel file to which you copy the same data. My first move would be to read/copy each table one by one so that I keep only one DataTable in memory rather than 30.

    Edit: or as a quick fix you could perhaps try something such as :

    while (ds.Tables.Count>0)
    {
        wb.Worksheets.Add(ds.Tables[0]);
        ds.Tables.RemoveAt(0);
    }

    to remove each table you don't need any more. You are at the expected size or you'll process even more data later ? Especially when dealing with quite a bit of data always try to not keep everything in memory from the beginning to the end but rather try to load what you need right now.

    Tuesday, July 9, 2019 11:34 AM
  • User-73514677 posted

    Thanks for the quick reply. Tried the above code and suggestion and moved the adding items to workbook , once dataset contains 4 data tables. But again the same error 

    Getting error as : Exception of type 'System.OutOfMemoryException' was thrown.

    stack trace:    at System.Collections.Generic.Dictionary`2.Resize(Int32 newSize, Boolean forceNewHashCodes)
       at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)
       at System.Collections.Generic.Dictionary`2.Add(TKey key, TValue value)
       at ClosedXML.Excel.XLCellsCollection.Add(Int32 row, Int32 column, XLCell cell)
       at ClosedXML.Excel.XLRangeBase.Cell(XLAddress cellAddressInRange)
       at ClosedXML.Excel.XLRangeBase.Cell(Int32 row, Int32 column)
       at ClosedXML.Excel.XLCell.SetValue[T](T value, Int32 ro, Int32 co)
       at ClosedXML.Excel.XLCell.InsertTable[T](IEnumerable`1 data, String tableName, Boolean createTable)
       at ClosedXML.Excel.XLCell.InsertTable(DataTable data, String tableName, Boolean createTable)
       at ClosedXML.Excel.XLCell.InsertTable(DataTable data)
       at ClosedXML.Excel.XLWorksheets.Add(DataTable dataTable, String sheetName)
       at ClosedXML.Excel.XLWorksheets.Add(DataTable dataTable)

    Tuesday, July 9, 2019 12:11 PM
  • User665608656 posted

    Hi venkatzeus,

    According to the error messages you provided, the main reason for the error is that you have a large amount of data in each table.

    Closedxml is suitable for smaller data sets and can be exported to excel quickly, you can refer to : ClosedXML

    For more than few 10 thousand rows of data, I recommend that you use OpenXML library to export your large data sets, you can refer to: Export very large data to Excel file

    Here is an example based on your code using OpenXML :

    string filename1 = @"C:\Users\yongqy\Desktop\test.xlsx";
    ExportDataSet(ds, filename1);
    private void ExportDataSet(DataSet ds, string destination)
            {
                using (var workbook = SpreadsheetDocument.Create(destination, SpreadsheetDocumentType.Workbook))
                {
                    var workbookPart = workbook.AddWorkbookPart();
    
                    workbook.WorkbookPart.Workbook = new Workbook();
    
                    workbook.WorkbookPart.Workbook.Sheets = new Sheets();
    
                    foreach (DataTable table in ds.Tables)
                    {
    
                        var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
                        var sheetData = new SheetData();
                        sheetPart.Worksheet = new Worksheet(sheetData);
    
                        Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<Sheets>();
                        string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
    
                        uint sheetId = 1;
                        if (sheets.Elements<Sheet>().Count() > 0)
                        {
                            sheetId =
                                sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                        }
    
                        Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName };
                        sheets.Append(sheet);
    
                        Row headerRow = new Row();
    
                        List<string> columns = new List<string>();
                        foreach (DataColumn column in table.Columns)
                        {
                            columns.Add(column.ColumnName);
    
                            Cell cell = new Cell();
                            cell.DataType = CellValues.String;
                            cell.CellValue = new CellValue(column.ColumnName);
                            headerRow.AppendChild(cell);
                        }
    
    
                        sheetData.AppendChild(headerRow);
    
                        foreach (DataRow dsrow in table.Rows)
                        {
                            Row newRow = new Row();
                            foreach (string col in columns)
                            {
                                Cell cell = new Cell();
                                cell.DataType = CellValues.String;
                                cell.CellValue = new CellValue(dsrow[col].ToString());
                                newRow.AppendChild(cell);
                            }
    
                            sheetData.AppendChild(newRow);
                        }
    
                    }
    
                }
            }

    Best Regards,

    YongQing.

    Wednesday, July 10, 2019 3:00 AM
  • User-73514677 posted

    Thanks. Tried this approach provided by user - YongQing . However, getting same error.

    Error: {System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown

    StackTrace:    at System.Reflection.CustomAttributeData.GetCustomAttributeRecords(RuntimeModule module, Int32 targetToken)
       at System.Reflection.CustomAttribute.IsCustomAttributeDefined(RuntimeModule decoratedModule, Int32 decoratedMetadataToken, RuntimeType attributeFilterType, Int32 attributeCtorToken, Boolean mustBeInheritable)
       at System.Reflection.CustomAttribute.IsDefined(RuntimeType type, RuntimeType caType, Boolean inherit)
       at System.RuntimeType.IsDefined(Type attributeType, Boolean inherit)
       at System.Enum.InternalFormat(RuntimeType eT, Object value)
       at System.Enum.ToString()
       at DocumentFormat.OpenXml.EnumValue`1.ToString(T enumVal)
       at DocumentFormat.OpenXml.EnumValue`1.get_InnerText()
       at DocumentFormat.OpenXml.OpenXmlElement.WriteAttributesTo(XmlWriter xmlWriter)
       at DocumentFormat.OpenXml.OpenXmlElement.WriteTo(XmlWriter xmlWriter)
       at DocumentFormat.OpenXml.OpenXmlCompositeElement.WriteContentTo(XmlWriter w)
       at DocumentFormat.OpenXml.OpenXmlElement.WriteTo(XmlWriter xmlWriter)
       at DocumentFormat.OpenXml.OpenXmlCompositeElement.WriteContentTo(XmlWriter w)
       at DocumentFormat.OpenXml.OpenXmlElement.WriteTo(XmlWriter xmlWriter)
       at DocumentFormat.OpenXml.OpenXmlCompositeElement.WriteContentTo(XmlWriter w)
       at DocumentFormat.OpenXml.OpenXmlPartRootElement.WriteTo(XmlWriter xmlWriter)
       at DocumentFormat.OpenXml.OpenXmlPartRootElement.SaveToPart(OpenXmlPart openXmlPart)
       at DocumentFormat.OpenXml.OpenXmlPartRootElement.Save()
       at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.SavePartContent(OpenXmlPart part)
       at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.TrySavePartContent(OpenXmlPart part)
       at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.SavePartContents()
       at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.Dispose(Boolean disposing)
       at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.Dispose()

    Wednesday, July 10, 2019 6:56 AM
  • User753101303 posted

    Including having a single DataTable rather then 30 in memory ? For now this is the biggest/easiest memory saving I can see.

    Wednesday, July 10, 2019 7:25 AM
  • User-73514677 posted

    Hi.

    Thanks for the reply. After using OpenXML, I am getting error in the below loop for second datatable:

    foreach (DataRow dsrow in table.Rows)
                        {
                            Row newRow = new Row();
                            foreach (string col in columns)
                            {
                                Cell cell = new Cell();
                                cell.DataType = CellValues.String;
                                cell.CellValue = new CellValue(dsrow[col].ToString());
                                newRow.AppendChild(cell);
                            }
    
                            sheetData.AppendChild(newRow);
                        }
    

    Wednesday, July 10, 2019 8:06 AM
  • User753101303 posted

    OutOfMemory means your code consumes too much memory which is not that surprising if you have 30 datatables with 10000 rows x 255 columns as well as an Excel file with the same content in memory.

    So could you confirm that you already updated  your code to load just the DataTable you are currently exporting rather them all ?

    Wednesday, July 10, 2019 8:15 AM
  • User-73514677 posted

    Hi,. Thanks for the reply.

    Below is the updated code with OpenXml, where in I have removed the datatable which is added. Please review

     private void ExportDataSet(DataSet ds, string destination)
            {
                try
                {
                    using (var workbook = SpreadsheetDocument.Create(destination, SpreadsheetDocumentType.Workbook))
                    {
                        var workbookPart = workbook.AddWorkbookPart();
    
                        workbook.WorkbookPart.Workbook = new Workbook();
    
                        workbook.WorkbookPart.Workbook.Sheets = new Sheets();
                        while (ds.Tables.Count > 0)
                        {
                       
                            var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
                            var sheetData = new SheetData();
                            sheetPart.Worksheet = new Worksheet(sheetData);
    
                            Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<Sheets>();
                            string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
    
                            uint sheetId = 1;
                            if (sheets.Elements<Sheet>().Count() > 0)
                            {
                                sheetId =
                                    sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                            }
    
                            Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = ds.Tables[0].TableName };
                            sheets.Append(sheet);
    
                            Row headerRow = new Row();
    
                            List<string> columns = new List<string>();
                            foreach (DataColumn column in ds.Tables[0].Columns)
                            {
                                columns.Add(column.ColumnName);
    
                                Cell cell = new Cell();
                                cell.DataType = CellValues.String;
                                cell.CellValue = new CellValue(column.ColumnName);
                                headerRow.AppendChild(cell);
                            }
    
    
                            sheetData.AppendChild(headerRow);
    
                            foreach (DataRow dsrow in ds.Tables[0].Rows)
                            {
                                Row newRow = new Row();
                                foreach (string col in columns)
                                {
                                    Cell cell = new Cell();
                                    cell.DataType = CellValues.String;
                                    cell.CellValue = new CellValue(dsrow[col].ToString());
                                    newRow.AppendChild(cell);
                                }
    
                                sheetData.AppendChild(newRow);
                            }
    
                            ds.Tables.RemoveAt(0);
                        }
                        workbookPart.Workbook.Save();
    
                        workbook.Close();
    
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
    
            }

    Wednesday, July 10, 2019 8:27 AM
  • User753101303 posted

    As it is not enough, my next step would be to load each DataTable one after the other so I'll ever get a single DataTable rather than 30 (and so saving the space currrently used by 29 DataTables).

    Depending on your VS edition you should have tools to see which objects are found in memory and how much size they take (not sure if available in Community).

    With this amount of data I would have likely started with a proof of concept with dummy data to check which amount of memory is needed (in particular I'm afraid that the final Excel content takes quite a bit of memory in which case it might be a bigger problem, but for now try already to trim down those 29 extra datatables).

    Wednesday, July 10, 2019 9:20 AM
  • User-73514677 posted

    Hi PatriceSc.

    Found this - https://blogs.msdn.microsoft.com/brian_jones/2010/06/22/writing-large-excel-files-with-the-open-xml-sdk/ 

    Is there a way to use a similar approach, if yes, could you please help with the sample code block.

    Wednesday, July 10, 2019 10:05 AM
  • User665608656 posted

    Hi venkatzeus,

    Because your data is very large, we have to consider exporting data in batches.

    Because of the outofMemory error in your second loop, we need to export the first table in the dataset and create the excel file you need.

    At this point, the excel file already exists, and 10000 pieces of data from the first sheet table have been exported.

    Next, you need to judge the dataset by while, open the excel file in each loop, insert a second (third,forth...thirty) sheet table into the file, and destroy the current datatable each time until the number of datasets is zero.

    In order to prevent outofMemory error and code running properly, this is a good way.

    The disadvantage is that it takes a little time,but it can export data successfully.

    For more detailed code, you can refer to:

          string filename1 = @"C:\Users\yongqy\Desktop\testData.xlsx";
           ExportDataSet1(ds, filename1);  
           private void ExportDataSet1(DataSet ds, string destination)
            {
                uint num = 1;
                try
                {
                    using (var workbook = SpreadsheetDocument.Create(destination, SpreadsheetDocumentType.Workbook))
                    {
                        var workbookPart = workbook.AddWorkbookPart();
    
                        workbook.WorkbookPart.Workbook = new Workbook();
    
                        workbook.WorkbookPart.Workbook.Sheets = new Sheets();
    
                        var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
                        var sheetData = new SheetData();
                        sheetPart.Worksheet = new Worksheet(sheetData);
    
                        Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<Sheets>();
                        string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
    
                        uint sheetId = 1;
                        if (sheets.Elements<Sheet>().Count() > 0)
                        {
                            sheetId = num;
                        }
    
                        Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = ds.Tables[0].TableName };
                        sheets.Append(sheet);
    
                        Row headerRow = new Row();
    
                        List<string> columns = new List<string>();
                        foreach (DataColumn column in ds.Tables[0].Columns)
                        {
                            columns.Add(column.ColumnName);
    
                            Cell cell = new Cell();
                            cell.DataType = CellValues.String;
                            cell.CellValue = new CellValue(column.ColumnName);
                            headerRow.AppendChild(cell);
                        }
                        sheetData.AppendChild(headerRow);
                        foreach (DataRow dsrow in ds.Tables[0].Rows)
                        {
                            Row newRow = new Row();
                            foreach (string col in columns)
                            {
                                Cell cell = new Cell();
                                cell.DataType = CellValues.String;
                                cell.CellValue = new CellValue(dsrow[col].ToString());
                                newRow.AppendChild(cell);
                            }
    
                            sheetData.AppendChild(newRow);
                        }
    
                        ds.Tables.RemoveAt(0);
                        workbookPart.Workbook.Save();
                        workbook.Close();
                        num++;
                    }
    
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    InsertWorksheet(ds, destination, num);
                }
    
            }
    // insert the remaining table into the excel file public static void InsertWorksheet(DataSet ds, string destination, uint num) { try { while (ds.Tables.Count > 0) { // Open the document for editing. using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(destination, true)) { // Add a blank WorksheetPart. WorksheetPart newWorksheetPart = spreadSheet.WorkbookPart.AddNewPart<WorksheetPart>(); var sheetData = new SheetData(); newWorksheetPart.Worksheet = new Worksheet(sheetData); Sheets sheets = spreadSheet.WorkbookPart.Workbook.GetFirstChild<Sheets>(); string relationshipId = spreadSheet.WorkbookPart.GetIdOfPart(newWorksheetPart); // Get a unique ID for the new worksheet. uint sheetId = 1; if (sheets.Elements<Sheet>().Count() > 0) { sheetId = num; } // Append the new worksheet and associate it with the workbook. Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = ds.Tables[0].TableName }; sheets.Append(sheet); Row headerRow = new Row(); List<string> columns = new List<string>(); foreach (DataColumn column in ds.Tables[0].Columns) { columns.Add(column.ColumnName); Cell cell = new Cell(); cell.DataType = CellValues.String; cell.CellValue = new CellValue(column.ColumnName); headerRow.AppendChild(cell); } sheetData.AppendChild(headerRow); foreach (DataRow dsrow in ds.Tables[0].Rows) { Row newRow = new Row(); foreach (string col in columns) { Cell cell = new Cell(); cell.DataType = CellValues.String; cell.CellValue = new CellValue(dsrow[col].ToString()); newRow.AppendChild(cell); } sheetData.AppendChild(newRow); } ds.Tables.RemoveAt(0); spreadSheet.Close(); num++; } } } catch (Exception ex) { throw ex; } }

    Best Regards,

    YongQing.

    Thursday, July 11, 2019 9:30 AM
  • User753101303 posted

    Yes it looks like the kind of Excel side optimization I was thinking about.

    But before that and for the 5th time, it's unclear if you took into account loading/exporting/disposing each DataTable one after the other so that you have 1 DataTable in memory rather than 30 ?

    I expected something such as "it now fails at sheet 23 rather then 5" or "I don't see how to do that" etc... rather than just "what about trying something else ?"

    This is not because removing a DataTable doesn't work or the error happens on the Excel side that it is the thing you should absolutely change (it is just the point where you don't have enough memory left)

    Now I'll still likely give a try on my side at :
    - creating datatables (keeping) and exporting them to sheets and see where it fails
    - creating/exporting/disposing a datatable to sheets and see where it fails
    - creating/exporting/disposing an adding a single sheet each time and see where it fails
    as it would make a nice demo about basic memory optimization ie having in memory what you need right now and in particular :
    - to not load massive amount of data you don't need yet
    - and make sure to discard massive amount of data you don't need any more

    Thursday, July 11, 2019 12:11 PM
  • User-73514677 posted

    Hi YongQing.

    Thanks for the code. I tried the same and still getting out of Memory exception

    Stack trace:    at System.ModuleHandle.ResolveMethodHandleInternal(RuntimeModule module, Int32 methodToken, RuntimeTypeHandle[] typeInstantiationContext, RuntimeTypeHandle[] methodInstantiationContext)
       at System.Reflection.CustomAttribute.FilterCustomAttributeRecord(CustomAttributeRecord caRecord, MetadataImport scope, Assembly& lastAptcaOkAssembly, RuntimeModule decoratedModule, MetadataToken decoratedToken, RuntimeType attributeFilterType, Boolean mustBeInheritable, Object[] attributes, IList derivedAttributes, RuntimeType& attributeType, IRuntimeMethodInfo& ctor, Boolean& ctorHasParameters, Boolean& isVarArg)
       at System.Reflection.CustomAttribute.GetCustomAttributes(RuntimeModule decoratedModule, Int32 decoratedMetadataToken, Int32 pcaCount, RuntimeType attributeFilterType, Boolean mustBeInheritable, IList derivedAttributes, Boolean isDecoratedTargetSecurityTransparent)
       at System.Reflection.CustomAttribute.GetCustomAttributes(RuntimeFieldInfo field, RuntimeType caType)
       at System.Reflection.RuntimeFieldInfo.GetCustomAttributes(Type attributeType, Boolean inherit)
       at System.Attribute.GetCustomAttributes(MemberInfo element, Type type, Boolean inherit)
       at System.Attribute.GetCustomAttribute(MemberInfo element, Type attributeType, Boolean inherit)
       at System.Attribute.GetCustomAttribute(MemberInfo element, Type attributeType)
       at DocumentFormat.OpenXml.EnumValue`1.ToString(T enumVal)
       at DocumentFormat.OpenXml.EnumValue`1.get_InnerText()
       at DocumentFormat.OpenXml.OpenXmlElement.WriteAttributesTo(XmlWriter xmlWriter)
       at DocumentFormat.OpenXml.OpenXmlElement.WriteTo(XmlWriter xmlWriter)
       at DocumentFormat.OpenXml.OpenXmlCompositeElement.WriteContentTo(XmlWriter w)
       at DocumentFormat.OpenXml.OpenXmlElement.WriteTo(XmlWriter xmlWriter)
       at DocumentFormat.OpenXml.OpenXmlCompositeElement.WriteContentTo(XmlWriter w)
       at DocumentFormat.OpenXml.OpenXmlElement.WriteTo(XmlWriter xmlWriter)
       at DocumentFormat.OpenXml.OpenXmlCompositeElement.WriteContentTo(XmlWriter w)
       at DocumentFormat.OpenXml.OpenXmlPartRootElement.WriteTo(XmlWriter xmlWriter)
       at DocumentFormat.OpenXml.OpenXmlPartRootElement.SaveToPart(OpenXmlPart openXmlPart)
       at DocumentFormat.OpenXml.OpenXmlPartRootElement.Save()
       at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.SavePartContent(OpenXmlPart part)
       at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.TrySavePartContent(OpenXmlPart part)
       at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.SavePartContents()
       at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.Dispose(Boolean disposing)
       at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.Dispose()
       at InsertWorksheet(DataSet ds, String destination, UInt32 num)

    Friday, July 12, 2019 9:41 AM
  • User475983607 posted

    Perhaps it is not possible to export 30 SQL tables of unknown size to Excel using OpenXML.  How much data are you exporting?  Have you taken stock of the RAM in your web server?  

    IMHO, the requirements seems rather odd.  Why are you exporting 30 tables to Excel?  What problem are you trying to solve?  Perhaps there is a better solution?

    For example, build 30 text files.  Or use SSIS to move data.  Or use Backup and restore. 

    Friday, July 12, 2019 10:41 AM
  • User-73514677 posted

    Hi.

    I understood the point and made another change. The place I was adding the datatables to the list, at that point I called the Export function, so at a particular time - only one datatable is available.

    I can see that the file is getting created, but found a issue as - only the last sheet is saved, all the other sheets are missing. The loop is moving for each sheet, so was expecting around 30 sheets, but in the saved file only the last datatable - sheet was available.

    Seems a minor tweak issue, but not able to figure out. Below is the full code used. Could you please check:

        public void getDataSetExportToExcel()
            {
                string fname = txtfilename.Text.Trim();
                string partfname = fname.Substring(0, 4).ToUpper();
    
                var ClaimsdataTables = new List<DataTable>();
                var AddressdataTables = new List<DataTable>();
                var CitydataTables = new List<DataTable>();
                var CountrydataTables = new List<DataTable>();    
    
                try
                {
                    NamesdataTables = GetDataSet("Names", "SELECT * from V_" + partfname + "_Names ", "SELECT COUNT(*) FROM V_" + partfname + "_Names");
                    AddressdataTables = GetDataSet("Address", "select * from V_" + partfname + "_Address", "SELECT COUNT(*) FROM V_" + partfname + "_Address");
                    CitydataTables = GetDataSet("City", "select * from V_" + partfname + "_City", "SELECT COUNT(*) FROM V_" + partfname + "_City");
                    CountrydataTables = GetDataSet("Country", "select * from V_" + partfname + "_Country", "SELECT COUNT(*) FROM V_" + partfname + "_Country");
    
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
    
      public List<DataTable> GetDataSet(string tblName, string strQuery, string strCountQuery)
            {       
                var dataTables = new List<DataTable>();
                string filename1 = string.Empty;
    
                string[] values = txtfilename.Text.Split('.');
                for (int i = 0; i < values.Length; i++)
                {
                    values[i] = values[i].Trim();
                }
                filename1 = values[0].ToString() + ".xlsx";
                string destpath = @"C:\" + filename1;
    
                try
                {
                    var totalRecords = 0;
                    var tableIndex = 1;
                    string fname = txtfilename.Text.Trim();
                    string partfname = fname.Substring(0, 4).ToUpper();              
    
                    using (OracleConnection cn = new OracleConnection(oradb))
                    {
                        cn.Open();
                     
                        using (var cmd = new OracleCommand { Connection = cn })
                        {
                            var countStatement = strCountQuery; 
                            cmd.CommandText = countStatement;
    
                            totalRecords = Convert.ToInt32(cmd.ExecuteScalar());
                          
                            int totalindex = totalRecords / 10000;
                            int offsetvalue = 0;
                          
                            for (var index = 0; index <= totalindex; index++)
                            {                            
                                OracleCommand cmd1 = new OracleCommand();
                                cmd1.Connection = cn;
                                var selectStatement = strQuery + " OFFSET :Offset ROWS FETCH NEXT 10000 ROWS ONLY";
                                cmd1.CommandText = selectStatement;
                              
                                var numParam = new OracleParameter();
                                numParam.ParameterName = "Offset";
                                numParam.OracleDbType = OracleDbType.Int32;
                                numParam.Direction = ParameterDirection.Input;
                                numParam.Value = offsetvalue;
                              
                                cmd1.Parameters.Add(numParam);
                            
                                var dt = new DataTable()
                                {
                                    TableName = tblName + "_" + index
                                };
                                dt.Load(cmd1.ExecuteReader());
                                dataTables.Add(dt);
    
                                ExportDataSet1(dt, destpath);
    
                                tableIndex += 1;
                                offsetvalue += 10000;     
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                return dataTables;
            }
    
      private void ExportDataSet1(DataTable ds, string destination)    
            {
                uint num = 1;
                try
                {
                    using (var workbook = SpreadsheetDocument.Create(destination, SpreadsheetDocumentType.Workbook))
                    {
                        var workbookPart = workbook.AddWorkbookPart();
    
                        workbook.WorkbookPart.Workbook = new Workbook();
    
                        workbook.WorkbookPart.Workbook.Sheets = new Sheets();
    
                        var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
                        var sheetData = new SheetData();
                        sheetPart.Worksheet = new Worksheet(sheetData);
    
                        Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<Sheets>();
                        string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
    
                        uint sheetId = 1;
                        if (sheets.Elements<Sheet>().Count() > 0)
                        {
                            sheetId = num;
                        }
    
                        Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = ds.TableName };
                        sheets.Append(sheet);
    
                        Row headerRow = new Row();
    
                        List<string> columns = new List<string>();
                        foreach (DataColumn column in ds.Columns)
                        {
                            columns.Add(column.ColumnName);
    
                            Cell cell = new Cell();
                            cell.DataType = CellValues.String;
                            cell.CellValue = new CellValue(column.ColumnName);
                            headerRow.AppendChild(cell);
                        }
                        sheetData.AppendChild(headerRow);
                        foreach (DataRow dsrow in ds.Rows)
                        {
                            Row newRow = new Row();
                            foreach (string col in columns)
                            {
                                Cell cell = new Cell();
                                cell.DataType = CellValues.String;
                                cell.CellValue = new CellValue(dsrow[col].ToString());
                                newRow.AppendChild(cell);
                            }
    
                            sheetData.AppendChild(newRow);
                        }
    
                   //     ds.RemoveAt(0);
                        workbookPart.Workbook.Save();
                        workbook.Close();
                        num++;
                    }
    
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    InsertWorksheet(ds, destination, num);
                }
    
            }
    
            // insert the remaining table into the excel file
             public static void InsertWorksheet(DataTable ds, string destination, uint num)
           // public static void InsertWorksheet(DataSet ds, string destination, uint num)
            {
                try
                {
                    //while (ds.Tables.Count > 0)
                    //{
                        // Open the document for editing.
                        using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(destination, true))
                        {
                            // Add a blank WorksheetPart.
                            WorksheetPart newWorksheetPart = spreadSheet.WorkbookPart.AddNewPart<WorksheetPart>();
                            var sheetData = new SheetData();
                            newWorksheetPart.Worksheet = new Worksheet(sheetData);
    
                            Sheets sheets = spreadSheet.WorkbookPart.Workbook.GetFirstChild<Sheets>();
                            string relationshipId = spreadSheet.WorkbookPart.GetIdOfPart(newWorksheetPart);
    
                            // Get a unique ID for the new worksheet.
                            uint sheetId = 1;
                            if (sheets.Elements<Sheet>().Count() > 0)
                            {
                                sheetId = num;
                            }
    
                            // Append the new worksheet and associate it with the workbook.
                            Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = ds.TableName };
                            sheets.Append(sheet);
                            Row headerRow = new Row();
    
                            List<string> columns = new List<string>();
                            foreach (DataColumn column in ds.Columns)
                            {
                                columns.Add(column.ColumnName);
    
                                Cell cell = new Cell();
                                cell.DataType = CellValues.String;
                                cell.CellValue = new CellValue(column.ColumnName);
                                headerRow.AppendChild(cell);
                            }
                            sheetData.AppendChild(headerRow);
                            foreach (DataRow dsrow in ds.Rows)
                            {
                                Row newRow = new Row();
                                foreach (string col in columns)
                                {
                                    Cell cell = new Cell();
                                    cell.DataType = CellValues.String;
                                    cell.CellValue = new CellValue(dsrow[col].ToString());
                                    newRow.AppendChild(cell);
                                }
    
                                sheetData.AppendChild(newRow);
                            }
    
                         //   ds.Tables.RemoveAt(0);
                            spreadSheet.Close();
                            num++;
                        }
                    //}
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
    

    Thanks

    Friday, July 12, 2019 10:50 AM
  • User753101303 posted

    In ExportDataSet1, you create a new workbook each time.

    What I tried to tell is that updating too many things while others are trying to help is often a bad idea. On one side we are trying to guide you while on the other side you introduce new issues faster than we can solve previous ones.

    I don't mean loading each DataTable using its own variable but really loading each one inside a loop so that you ever have only a single DataTable in memory rather than 30. And so it would save 29*10000*255*AverageBytesPerCell with a simple change which could have been enough to solve your issue.

    Instead for some unkown reason you absolutely wanted to switch to another Excel library apparently without even bothering to see if a much simpler solution would be enough and just making harder to help you. Let me see what happens if It try to export 10000*255*20 characters to worksheets...

    For some reason it seems I can't install ClosedXML right now? For now I'm trying something which is just :

                var wb = new XLWorkbook();
                for (int i = 0; i < 30; i++)
                {
                    var dt = GetDataTable(i); // Load only DataTable i in mmemory right before we need it
                    wb.Worksheets.Add(dt);
                    dt.Dispose(); // Likely not really needed (discard it right after we don't need it any more
                }
    

    rarther than 

     var wb = new XLWorkbook();
     var ds=GetAllDataTables();
    foreach(var dt in ds.Tables) // we have all 30 datatables loaded in memory
    {
        wb.Worksheets.Add(dt);
    }

    i'm still wondering if you tried or what ???

    Friday, July 12, 2019 11:24 AM
  • User475983607 posted

    I'd like to know why you are exporting 30 tables to Excel considering the table exist in the Database and you can query the data anytime you like.  Keep in mind you are creating a copy of data that can easily become stale within a few moments after the Export.  What is the purpose of the Excel Workbook?  Are you moving the data to another data store?  It can't be a report.  Can you explain the high level requirement?

    Friday, July 12, 2019 12:52 PM
  • User-73514677 posted

    Hi.

    Creating a web based application for the end users, so that they don't have to connect to database and query results and get the data in excel for reviewing.

    I get multiple request from users to extract data from database and export to excel. With this the end users just have to input the filename in the Web application textbox and the result is downloaded to data in excel for their review and convenience, whenever required.

    Hope this clarifies.

    Friday, July 12, 2019 12:58 PM
  • User475983607 posted

    Hi.

    Creating a web based application for the end users, so that they don't have to connect to database and query results and get the data in excel for reviewing.

    I get multiple request from users to extract data from database and export to excel. With this the end users just have to input the filename in the Web application textbox and the result is downloaded to data in excel for their review and convenience, whenever required.

    Hope this clarifies.

    Yeah, this is a very very very bad idea.  When you export data, you export data at a point in time.  The last thing you want is user running reports on old data and having a bunch of Excel Workbook floating around.

    I recommend rethinking the approach.  If you are using SQL then look into SSRS.  SQL Server Reporting Service solves this issue.

    Friday, July 12, 2019 1:16 PM
  • User1120430333 posted

    Hi. I am trying to use Closed XML in a C# ASP.NET Web application. I have a dataset which contains more than 30 data tables. I want to pass each of the data table as a separate worksheet and then create the excel.

    A datatable uses 6 times more memory than a collection of custom types/objects that is faster too, becuase of the boxing and unboxing used by a datatable. 

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

    https://dzone.com/articles/reasons-move-datatables

    Friday, July 12, 2019 8:38 PM
  • User-73514677 posted

    Hi Patrice.

    Thanks for the reply.

    I have tried the code , which you have asked to refer:

     public void ExportToExcel(string TableName)
            {
                try 
                {
                    DataSet ds = GetDataTable(TableName, "SELECT * from TableName", "SELECT COUNT(*) FROM TableName");
                    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();
                }
                catch (Exception ex)
                {
                    throw ex;
                }           
            }

    I am getting the same error - Out of Memory Exception

    Monday, July 15, 2019 2:02 PM
  • User665608656 posted

    Hi venkatzeus,

    According to your description, what the type of data do you need to export, string or int?

    I suggest you take a screenshot and show me your data format.

    Best Regards,

    YongQing.

    Tuesday, July 16, 2019 9:59 AM
  • User-73514677 posted

    Hi.

    The data contains int, float, string, date

    Tuesday, July 16, 2019 10:30 AM
  • User665608656 posted

    Hi venkatzeus,

    Could you tell us how much memory you have and how many data you need to operate?

    If you can, I suggest that you provide us with the schema of your tables, and that we will try to implement the functions you want by calculation if it is possible.

    Best Regards,

    YongQing.

    Wednesday, July 17, 2019 9:53 AM
  • User-73514677 posted

    Hi Yongqing Yu.

    The maximum data which needs to be moved would be 3 lakhs. ( This 3 lakh data, I have already split in 10000 rows of data and kept in datatables) . I have 8 GB of memory.

    I am fine with keeping all data as string format.

    Thanks

    Wednesday, July 17, 2019 10:22 AM
  • User1120430333 posted

    Hi. I am trying to use Closed XML in a C# ASP.NET Web application. I have a dataset which contains more than 30 data tables. I want to pass each of the data table as a separate worksheet and then create the excel.

    Each data table has 10000 rows and 255 columns. I am getting the error :

    Wow! That's a lot of memory being used in datatables, becuase a datatable is a memory hog that uses 6 times more memory than a collection using a custom type/object.


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

    Wednesday, July 17, 2019 8:44 PM
  • User665608656 posted

    Hi venkatzeus,

    According to your description, I suggest you do three things as follow:

    1: Do not download the entire database

    2: Add more memory

    3: Change the entire design of extract data to excel, which is very complex.

    Best Regards,

    YongQing.

    Thursday, July 18, 2019 5:18 AM
  • User475983607 posted

    I agree with Yongqing Yu.  You should use a standard approach rather than building your own solution.  Again, SSRS solves this problem.

    https://docs.microsoft.com/en-us/sql/reporting-services/tools/reporting-services-tools?view=sql-server-2017

    Thursday, July 18, 2019 11:55 AM