I am evaluating Aspose.Cells control for .NET and I am facing out of memory issue. Our requirement is to pull around 500,000 records from database (mix of various data types) with about 100 columns and create spreadsheet compatible with Excel 2003 in the process. All data has to be contained in one file and we can't break it up into multiple files. I have been monitoring the process and at around 350000 rows, memory usage is around 1.7 gigs and system blows up with out of memory exception. We are able to open excel file that contains that amount of data within Excel and our client currently creates it by using VBA code to pull data from MS Access db into Excel. They want to move away from that I was hoping to use your control set. Here is the code that I use to populate worksheets:
Workbook wb = new Workbook();
wb.Worksheets.Clear();
for (int i = 1; i < 500000; i += 65536)
{
Worksheet ws = wb.Worksheets.Add("Sheet"+i.ToString());
Console.WriteLine("Inserting sheet named {0} ", "Sheet" + sheetCount.ToString());
Console.WriteLine("Currently added rows - {0}", i);
cmd.Parameters.Clear();
SqlParameter param = cmd.CreateParameter();
param.ParameterName = "@minRow";
param.Value = i;
param.SqlDbType = SqlDbType.Int;
param.Direction = ParameterDirection.Input;
cmd.Parameters.Add(param);
SqlParameter param1 = cmd.CreateParameter();
param1.ParameterName = "@maxRow";
param1.Value = i + 65536;
param1.SqlDbType = SqlDbType.Int;
param1.Direction = ParameterDirection.Input;
cmd.Parameters.Add(param1);
using (SqlDataReader rd = cmd.ExecuteReader())
{
Cells cells = ws.Cells;
cells.ImportDataReader(rd, true, 0, 0, false);
}
sheetCount++;
}
wb.Save(String.Format("c:\\test.xls", sheetCount));
Do you have any suggestions regarding how I can overcome OutOfMemory exception?
Live Chat