Forums Ultimate UI for ASP.NET Web Forms
Your Privacy Matters: We use our own and third-party cookies to improve your experience on our website. By continuing to use the website we understand that you accept their use. Cookie Policy
Close
Jerson
80
Workbook.Save() failed or sometimes takes more than day.
posted

Recently we have encountered this issue about exporting reports to excel file.

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 would be a 2 worksheet file because only about 1million rows is supported by excel 2007 per sheet.

We have no problem during the creation of worksheets and during the filling of data into the worksheet objects, memory is manageable at this point.

Our problem is when writing(Workbook.Save()) the excel data into the stream(file, memory). The memory consumption is just too high and when it reaches 1GB or higher it will no longer continue.

P.S. This is a summary report, the upper management wants this in a single excel file.

Is there any way we can at least lessen the memory usage? Or is there any alternative to generate excel files with this amount of data.

Below is a simple test, we pulled out from the actual code that replicates this issue:

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;//the number of rows to process
	var rr = 0;
	for (var row = 0; row < limit; row++)
	{
		if (rr >= xlsx - 1)
		{
			sheetCtr++;
			ws = wb.Worksheets.Add("Sheet" + sheetCtr.ToString());
			rr = 0;
			//ClearMemory();
		}
		for (var col = 0; col < 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 will no longer respond when memory gets 1.gb or higher
	//wb.Save("wb2.xlsx");           
	using (var stream = new System.IO.MemoryStream())
	{
		wb.Save(stream);
		stream.WriteTo(new System.IO.FileStream("test1.xlsx",System.IO.FileMode.Create));
	}
	Console.WriteLine("Workbook saved.");

	Console.WriteLine("Done");
	Console.Read();
}
Parents Reply Children
No Data