Improve performance when generating excel spreadsheet

Question

I’m using this generated NPOI to create excel spreadsheets and am facing the following problem:

The generated spreadsheet has 300 lines and takes 1 minute to be generated and sometimes error Exception of type 'System.OutOfMemoryException' was thrown.".

What can I do to improve performance and eliminate error ?

Note: The consultation is very fast.

here is the relevant excerpt from the file generation:

public ActionResult ExportarExcel(ProcessamentoRegistrosDTO pProcessamentoRegistrosDTO)
        {
            MemoryStream stream = null;
            try
            {
                #region :: setar variáveis e instancia objetos ::
                string _nomeArquivo = string.Empty;
                _nomeArquivo = "Documento_Fiscal_" + DateTime.Now.ToString().Replace(" ", "_").Replace("/", "_").Replace(":", "_") + ".xlsx";


                IWorkbook workbook = new XSSFWorkbook();
                ISheet sheet = workbook.CreateSheet("Plan 1");
                #endregion

                #region :: Obter dados ::

                #endregion

                #region :: Filtrar dados ::

                #endregion

                #region :: Criar estilos ::
                int rowNumer = 0;
                IRow row = sheet.CreateRow(rowNumer);
                ICell cell;

                var estiloHeader = workbook.CreateCellStyle();
                estiloHeader.BorderTop = BorderStyle.Thin;
                estiloHeader.BorderBottom = BorderStyle.Thin;
                estiloHeader.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.SeaGreen.Index;
                estiloHeader.FillPattern = FillPattern.SolidForeground;
                var estiloColuna = workbook.CreateCellStyle();
                estiloColuna.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
                estiloColuna.FillPattern = FillPattern.SolidForeground;
                var detailSubtotalFont = workbook.CreateFont();
                detailSubtotalFont.Boldweight = (short)FontBoldWeight.Bold;
                estiloHeader.SetFont(detailSubtotalFont);

                ICellStyle style = workbook.CreateCellStyle();
                cell = row.CreateCell(0);
                cell.SetCellValue("PARÂMETRO");
                cell.CellStyle = estiloHeader;
                cell = row.CreateCell(1);
                cell.SetCellValue("SERVIÇO");
                cell.CellStyle = estiloHeader;
                cell = row.CreateCell(2);
                cell.SetCellValue("RAZÃO SOCIAL");
                cell.CellStyle = estiloHeader;
                cell = row.CreateCell(3);
                cell.SetCellValue("SITUAÇÃO CADASTRAL");
                cell.CellStyle = estiloHeader; //sheet.SetDefaultColumnStyle(3, estiloColuna);
                cell = row.CreateCell(4);
                cell.SetCellValue("CÓDIGO SITUAÇÃO");
                cell.CellStyle = estiloHeader;
                cell = row.CreateCell(5);
                cell.SetCellValue("DATA DA INCLUSÃO");
                cell.CellStyle = estiloHeader;
                cell = row.CreateCell(6);
                cell.SetCellValue("DATA PROCESSAMENTO");
                cell.CellStyle = estiloHeader;
                cell = row.CreateCell(7);
                cell.SetCellValue("ID REGISTRO");
                cell.CellStyle = estiloHeader;
                #endregion

                #region :: Preencher planilha ::
                foreach (var item in _clienteServico)
                {
                    rowNumer++;
                    row = sheet.CreateRow(rowNumer);
                    row.CreateCell(0).SetCellValue(item.Parametro);
                    row.CreateCell(1).SetCellValue(item.Descricao);
                    row.CreateCell(2).SetCellValue(item.NomeEmpresa);
                    row.CreateCell(3).SetCellValue(item.Situacao);
                    row.CreateCell(4).SetCellValue(item.CodigoSituacao.ToString() == null ? "" : item.CodigoSituacao.ToString());
                    row.CreateCell(5).SetCellValue(Convert.ToString(item.DataInclusao));
                    row.CreateCell(6).SetCellValue(Convert.ToString(item.DataProcessamento));
                    row.CreateCell(7).SetCellValue(item.IdRegistro);
                    //row.CreateCell(4).SetCellFormula("C2+D2");
                }

                //Tamanho das colunas 
                sheet.AutoSizeColumn(0);
                sheet.AutoSizeColumn(1);
                sheet.AutoSizeColumn(2);
                sheet.AutoSizeColumn(3);
                sheet.AutoSizeColumn(4);
                sheet.AutoSizeColumn(5);
                sheet.AutoSizeColumn(6);
                #endregion

                #region :: Salva planilha na área de trabalho ::
                stream = new MemoryStream();
                workbook.Write(stream);

                return File(stream.ToArray(),
                    "application/vnd.ms-excel",
                    _nomeArquivo);
                #endregion

                //stream.Dispose();
            }
            catch (Exception ex)
            {
                //stream.Dispose();
                TempData["mensagemErro"] = string.Format("Download não efetuado! " + ex.Message.ToString());
                return RedirectToAction("Index", "Documento");
            }
            //finally
            //{
            //    stream.Dispose();
            //}
        } 

Answer 1

Fixed. The Exception error of type 'System.Outofmemoryexception' was thrown. was due to a type of varbinary data that was not being treated correctly.