Click here to Skip to main content
15,912,329 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I need to be able to do over 200000 rows!
The problem: The creation process eats all memory(4GB). says ou of memeory exception.


C#
public static void InsertDataToExcelFile(SpreadsheetDocument sheet, DataTable table) 
        { 
            sheet.AddWorkbookPart(); 
            sheet.WorkbookPart.Workbook = new Workbook(); 
            sheet.WorkbookPart.Workbook.AppendChild(new Sheets()); 
       WorksheetPart newWorksheetPart = sheet.WorkbookPart.AddNewPart<WorksheetPart>(); 
            newWorksheetPart.Worksheet = new Worksheet(); 
        uint worksheetNumber = 1; 
 
SheetData sheetData = newWorksheetPart.Worksheet.AppendChild(new SheetData()); 
 
int numberOfColumns = table.Columns.Count; 
bool[] IsNumericColumn = new bool[numberOfColumns]; 
Row newRow = sheetData.AppendChild(new Row()); 
Cell newCell; 
foreach (DataRow dr in table.Rows) 
            { 
                newRow = sheetData.AppendChild(new Row()); 
          for (int colInx = 0; colInx < numberOfColumns; colInx++) 
                { 
         string cellValue = dr.ItemArray[colInx].ToString(); 
 
         if (IsNumericColumn[colInx] && !string.IsNullOrEmpty(cellValue))    
               {             
                 newCell = CreateNumericCell(cellValue); 
                } 
                else 
               { 
                        newCell = CreateTextCell(cellValue); 
                       }   
 
                  newRow.AppendChild(newCell); 
                } 
            } 
            newWorksheetPart.Worksheet.Save(); 
           sheet.WorkbookPart.Workbook.GetFirstChild<Sheets>().AppendChild(new Sheet() 
            { 
                Id = sheet.WorkbookPart.GetIdOfPart(newWorksheetPart), 
                SheetId = worksheetNumber++, 
                Name = table.TableName 
            }); 
            sheet.WorkbookPart.Workbook.Save(); 
        }
Posted

1 solution

You have not said how many columns there are, but 200,000 rows will take up a lot of space, so start multiplying by columns and you will eat memory very quickly, especially as you have to run part of excel at the same time.
Also the meth you are using to put in the data seems rather clunky too.
 
Share this answer
 
Comments
senthil sennu 26-Dec-11 8:38am    
Thanks for the reply. Column count is 22. Is there any other way to write the data to excel quickly.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900