I'm using this generated NPOI to create the excel worksheets and I'm facing the following problem:
The generated worksheet has 300 lines and takes 1 minute to generate and sometimes the Exception of type 'System.OutOfMemoryException' was thrown."
error.
What can I do to improve performance and eliminate the error?
Note: The linq query is very fast.
Here is the relevant excerpt 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();
//}
}