Still having memory issues with Beta release

Mar 13, 2014 at 9:18 PM
Edited Mar 13, 2014 at 9:19 PM
I may have missed some documentation somewhere, however I'm still running into memory issues with the 4.0 Beta release.

I need to create a spreadsheet with close to 300k records. The process, while slow, completes successfully however I don't see any of the memory used during the process given back.

The process I'm using seems pretty basic. I pull records from a database into a List object (named 'results'). I then cycle through that list and write to the spreadsheet.
        //Construct new Excel package
        ExcelPackage pck = new ExcelPackage();

        //Instantiate workbook object
        var ws = pck.Workbook.Worksheets.Add("Query_" + DateTime.Now.ToString());

        //Header content
        ws.Cells["A1"].Value = "ChannelCode";
        ws.Cells["B1"].Value = "DrmTerrDesc";
        ws.Cells["C1"].Value = "IndDistrnId";
        ws.Cells["D1"].Value = "StateCode";
        ws.Cells["E1"].Value = "ZipCode";
        ws.Cells["F1"].Value = "EndDate";
        ws.Cells["G1"].Value = "EffectiveDate";
        ws.Cells["H1"].Value = "LastUpdateId";
        ws.Cells["I1"].Value = "ErrorCodes";
        ws.Cells["J1"].Value = "Status";
        ws.Cells["K1"].Value = "Id";

        //Set autofilter
        ws.Cells["A1:K1"].AutoFilter = true;

        //Center text
        ws.Cells["A1:K1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

        //Freeze first row
        ws.View.FreezePanes(2, 1);

        //Set background color for Id cells
        ws.Column(11).Style.Fill.PatternType = ExcelFillStyle.Solid;
        ws.Column(11).Style.Fill.BackgroundColor.SetColor(Color.Gainsboro);

        //Set background color for the rest
        ws.Cells["A1:K1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
        ws.Cells["A1:K1"].Style.Fill.BackgroundColor.SetColor(Color.Black);

        //Set header text color
        ws.Cells["A1:K1"].Style.Font.Color.SetColor(Color.WhiteSmoke);
        ws.Cells["A1:K1"].Style.Font.Bold = true;

        //Content
        int i = 2;
        foreach (var zip in results)
        {
            //Set cell values
            ws.Cells["A" + i.ToString()].Value = zip.ChannelCode;
            ws.Cells["B" + i.ToString()].Value = zip.DrmTerrDesc;
            ws.Cells["C" + i.ToString()].Value = zip.IndDistrnId;
            ws.Cells["D" + i.ToString()].Value = zip.StateCode;
            ws.Cells["E" + i.ToString()].Value = zip.ZipCode;
            ws.Cells["F" + i.ToString()].Value = zip.EndDate.ToShortDateString();
            ws.Cells["G" + i.ToString()].Value = zip.EffectiveDate.ToShortDateString();
            ws.Cells["H" + i.ToString()].Value = zip.LastUpdateId;
            ws.Cells["I" + i.ToString()].Value = zip.ErrorCodes;
            ws.Cells["J" + i.ToString()].Value = zip.Status;
            ws.Cells["K" + i.ToString()].Value = zip.Id;

            //Unlock non-Id fields
            ws.Cells["A" + i.ToString()].Style.Locked = false;
            ws.Cells["B" + i.ToString()].Style.Locked = false;
            ws.Cells["C" + i.ToString()].Style.Locked = false;
            ws.Cells["D" + i.ToString()].Style.Locked = false;
            ws.Cells["E" + i.ToString()].Style.Locked = false;
            ws.Cells["F" + i.ToString()].Style.Locked = false;
            ws.Cells["G" + i.ToString()].Style.Locked = false;
            ws.Cells["H" + i.ToString()].Style.Locked = false;
            ws.Cells["I" + i.ToString()].Style.Locked = false;
            ws.Cells["J" + i.ToString()].Style.Locked = false;

            i++;
        }

        ws.Cells.Dispose();

        ////Autofit cells
        //ws.Cells[ws.Dimension.Address].AutoFitColumns();

        return new ExcelResult
            {
                FileName = "ZipCodeSearch_" + DateTime.Now + ".xlsx",
                Package = pck
            };
This is an MVC app and I wanted the user to be able to download the finished product. As a results I override the 'ExecuteResult' method of the System.Web.Mvc class 'ActionResults'
    public string FileName { get; set; }
    public ExcelPackage Package { get; set; }

    public override void ExecuteResult(ControllerContext context)
    {
        context.HttpContext.Response.Buffer = true;
        context.HttpContext.Response.Clear();
        context.HttpContext.Response.AddHeader("content-disposition", "attachment; filename=" + FileName);
        context.HttpContext.Response.ContentType = "application/vnd.ms-excel";
        context.HttpContext.Response.BinaryWrite(Package.GetAsByteArray());

        Package.Stream.Close();
        Package.Dispose();
    }
Coordinator
Mar 18, 2014 at 7:01 PM
Call pck.Dispose() or wrap the package in an Using statment to release the memory (and optionally call GC.Collect() to see the change).
This will call dispose on any underlaying objects in the cellstore.
The Cells property is an ExcelRange object that is more of a "window" on top if the cell store.