title: Optimized Infragistics Excel
body: <p>Recently we have encounter this issue about exporting reports to excel file using infragistics library.</p>
  
  <p>Our report table contains more or less 1.26 million records as of now. We are trying to create an excel(2007) file for this, so approximately this will be 2 worksheets because only about 1million rows is supported by excel 2007 per sheet.</p>
  
  <p>We have no problem during the creation of worksheets and during the filling of data into the worksheet, memory is manageable at this point.</p>
  
  <p><strong>Our problem is when writing the excel data into the stream(file, memory).
  The memory consumption is just too high and when it reaches 1.8GB it will no longer continue.</strong></p>
  
  <p><em>P.S. This is a summary report, the client wants this in a single excel file.</em></p>
  
  <p><strong>Your recommendations are much appreciated!!!</strong></p>
  
  <p>Below is the code that i have pulled out from the actual source but i have replaced the data retrieval part and instead use a dummy data.</p>
  
  <pre><code>using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Text;
  using Microsoft.Win32;
  using System.Runtime;
  using System.Diagnostics;
  using Infragistics.Excel;
  
  namespace ConsoleApplication1
  {
      class Program
      {
          static bool tip = true;
  
          static void Main(string[] args)
          {
              var xls = Workbook.MaxExcelRowCount;
              var xlsx =Workbook.MaxExcel2007RowCount;
  
              var wb = new Workbook(WorkbookFormat.Excel2007, WorkbookPaletteMode.StandardPalette);
              var ws = wb.Worksheets.Add("Sheet1");
              var sheetCtr = 1;
  
              var limit = 1256898;
              var rr = 0;
              for (var row = 0; row &lt; limit; row++)
              {
                  if (rr &gt;= xlsx - 1)
                  {
                      //create new worksheet if row exceeded the limit
                      ws = wb.Worksheets.Add("Sheet" + sheetCtr.ToString());
                      rr = 0;
                      ClearMemory();
                  }
                  for (var col = 0; col &lt; 10; col++)
                  {
                      ws.Rows[rr].Cells[col].Value = string.Format("data{0}-{1}", row, col);
                  }
                  Console.Write("Row=");
                  Console.Write(row.ToString());
                  Console.WriteLine();
                  rr++;
              }
  
  
              Console.Write("Saving worksheet...");
  
              //this part uses too much memory
              wb.Save("wb2.xlsx");
  
              Console.WriteLine("Workbook saved.");
  
              Console.WriteLine("Done");
              Console.Read();
          }
  
          static void ClearMemory()
          {
              try
              {
                  System.Diagnostics.Process proc = System.Diagnostics.Process.GetCurrentProcess();
                  if (tip == true)
                  {
                      proc.MaxWorkingSet = (IntPtr)((int)proc.MaxWorkingSet - 1);
                      proc.MinWorkingSet = (IntPtr)((int)proc.MinWorkingSet - 1);
                  }
                  else
                  {
                      proc.MaxWorkingSet = (IntPtr)((int)proc.MaxWorkingSet + 1);
                      proc.MinWorkingSet = (IntPtr)((int)proc.MinWorkingSet + 1);
                  }
                  tip = !tip;
              }
              catch (System.Exception ex)
              {
                  Console.WriteLine(ex.Message);
              }
          }
      }
  }
  </code></pre>