Memory problems while creating large excelfile RSS

• • Reply
  

  Fredrik87

  0 Points

  2 Posts

  Memory problems while creating large excelfile

  Dec 25, 2011 08:26 PM|LINK

  Hello,

  My problem turns up when creating an excel spreadsheet from a datatable of approximately 500 rows. I need to be able to do over 2000 rows!
  The problem: The creation process eats all memory(4GB).

  Allright, so here is the code I am using for creating the excel document.
  If you think the problem might be somewhere else outside this code provided below... I am more than willing to provide more code to the discussion.

  using DocumentFormat.OpenXml.Spreadsheet;
  using DocumentFormat.OpenXml.Packaging;
  
  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();
          }

   

  • Edited by XIII on Dec 26, 2011 08:03 AM