1
Vote

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();
                    }

                   }

comments

duncans wrote Mar 25 at 6: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.