This project has moved. For the latest updates, please go here.
1

Closed

system.memoryout of exception

description

Hi

I exporting around 50,000 rows to excel. I am using row grouping. I have lot of groupings. It works well for 20k rows but memory spikes a lot to atleast 1 gb. Its happening on the windows 2008 r2 servers with 24 gb ram

My data is lot of processed data before passing it to worksheet. This is the reason i am unable to provide data. Please let me know if there are any corrections i need to make to get it work

Thanks
                    using (var workbook = new ClosedXML.Excel.XLWorkbook())
                    {
                        var ws = workbook.Worksheets.Add(dt);
if (indentLevel == 2)
                                {

                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Group();
                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Style.Font.FontColor = XLColor.Red;
                                }
                                else if (indentLevel == 3)
                                {
                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Group();
                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Group();
                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Style.Font.FontColor = XLColor.Blue;
                                }
                                else if (indentLevel == 4)
                                {
                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Group();
                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Group();
                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Group();
                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Style.Font.FontColor = XLColor.ShockingPink;
                                }
                                else if (indentLevel == 5)
                                {
                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Group();
                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Group();
                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Group();
                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Group();
                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Style.Font.FontColor = XLColor.Green;
                                }
                                else if (indentLevel == 6)
                                {
                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Group();
                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Group();
                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Group();
                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Group();
                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Group();
                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Style.Font.FontColor = XLColor.Maroon;
                                }
                                else if (indentLevel == 7)
                                {
                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Group();
                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Group();
                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Group();
                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Group();
                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Group();
                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Group();
                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Style.Font.FontColor = XLColor.Purple;
                                }
                                else if (indentLevel == 8)
                                {
                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Group();
                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Group();
                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Group();
                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Group();
                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Group();
                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Group();
                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Group();
                                    ws.Rows(pointer_string.ToString() + ":" + moving_string.ToString()).Style.Font.FontColor = XLColor.Orange;
                                }
                            }
                            catch (Exception ex)
                            {
                                string script = "<script type=\"text/javascript\">alert('No results to export to excel');</script>";
                                ClientScript.RegisterClientScriptBlock(this.GetType(), "Alert", script);
                                break;
                            }
                        }

                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            ws.Cell(i + 2, 17).Style.Font.FontColor = XLColor.Blue;
                            ws.Cell(i + 2, 17).Style.Font.Underline = XLFontUnderlineValues.Single;
                            ws.Cell(i + 2, 17).Hyperlink = new XLHyperlink(ConfigurationSettings.AppSettings["url"].ToString() + "displaybss.aspx?partnumber=" + dt.Rows[i]["Prt No"].ToString() + "&partrev=" + dt.Rows[i]["Rev"].ToString());
                        }

                        //ws.Columns().AdjustToContents();
                        ws.Columns(maxlevel + 1, 15).Hide();
                        ws.Outline.SummaryHLocation = XLOutlineSummaryHLocation.Left;
                        ws.Outline.SummaryVLocation = XLOutlineSummaryVLocation.Top;


                        HttpResponse response = Response;
                        response.Clear();
                        response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml";
                        response.AddHeader("content-disposition", "attachment;filename=Results.xlsx");

                        //MemoryStream MyMemoryStream = new MemoryStream();
                        using (MemoryStream MyMemoryStream = new MemoryStream())
                        {

                            workbook.SaveAs(MyMemoryStream);
                            response.Charset = "";
                            response.AddHeader("Content-Length", MyMemoryStream.Length.ToString());
                            response.BinaryWrite(MyMemoryStream.GetBuffer());
                        }

                        response.Flush();
                        response.OutputStream.Close();
                        response.End();
                        workbook.Dispose();
                        ws.Dispose();
                        ws = null;
                        GC.Collect();
                        GC.SuppressFinalize(workbook);
                        HttpContext.Current.ApplicationInstance.CompleteRequest();
                    }

                   }
Closed Apr 25 at 8:39 PM by igitur
Please try the latest version of ClosedXML (v0.87.1 at this stage). If you still experience the issue, log it on the new GitHub page.

comments

duncans wrote Mar 25, 2014 at 5:38 PM

Don't save the workbook to a MemoryStream because this - as the name suggests - loads the entire thing into memory, which is one of the things causing your OutOfMemoryException.

I suggest saving it to a temporary file on the file system then use Response.TransmitFile to send it. You will need to have some housekeeping task to tidy up the temporary files.