I am reading excel files from a folder. One by one I am adding a tab in each excel file and save that file. I am getting System.OutOfMemoryException error while saving the excel of large size (above 10mb) on azure stack although it is running fine on local machine. Below is my code I am using for reading excel and adding new tab in each excel. Is anyone have idea what I can do to resolve this error?
using (var workbook = new XLWorkbook(file.FullName))
{
try
{
var worksheet = workbook.Worksheets.Add("Summary");
var currentRow = 1;
string carrier = file.Name.Substring(file.Name.IndexOf("_") + 1);//Remove all before
carrier = carrier.Remove(carrier.IndexOf("_"));//Remove all after
List<FinalTrackingFile> clientList = new List<FinalTrackingFile>();
clientList = finalList.Where(m => m.Client.Contains(carrier)).ToList();
worksheet.Cell(currentRow, 1).Value = "Client";
worksheet.Cell(currentRow, 2).Value = "Direct Subsidy";
worksheet.Cell(currentRow, 3).Value = "Sequestration Adjustment";
worksheet.Cell(currentRow, 4).Value = "Net Direct Subsidy (for report)";
worksheet.Cell(currentRow, 5).Value = "Low Income Premium Subsidy (LIPS) - Directly Paid to Client";
worksheet.Cell(currentRow, 6).Value = "Reinsurance";
worksheet.Cell(currentRow, 7).Value = "Late Enrollment Penalty";
worksheet.Cell(currentRow, 8).Value = "CMS Educational Fees";
worksheet.Cell(currentRow, 9).Value = "Coordination of benefits (COB)";
worksheet.Cell(currentRow, 10).Value = "Net Amount - Direct Pmt to Client - Check/ACH ";
IXLRange contents = worksheet.Range(1, 1, 1, 10);
contents.Style.Alignment.WrapText = true;
contents.Style.Fill.BackgroundColor = XLColor.LightGray;
contents.Style.Font.Bold = true;
contents.Style.Font.SetFontSize(12);
contents.Style.Border.BottomBorder = XLBorderStyleValues.Thin;
contents.Style.Border.RightBorder = XLBorderStyleValues.Thin;
worksheet.Columns(1, 1).Width = 50;
worksheet.Columns(2, 10).Width = 20;
foreach (var item in clientList)
{
currentRow++;
worksheet.Cell(currentRow, 1).Value = item.Client;
worksheet.Cell(currentRow, 2).Value = item.DirectSubsidy;
worksheet.Cell(currentRow, 3).Value = item.SequestrationAdjustment;
worksheet.Cell(currentRow, 4).Value = item.NetDirectSubsidy;
worksheet.Cell(currentRow, 5).Value = item.LIPS_PaidtoClient;
worksheet.Cell(currentRow, 6).Value = item.Reinsurance;
worksheet.Cell(currentRow, 7).Value = item.LateEnrollmentPenalty;
worksheet.Cell(currentRow, 8).Value = item.CMSEducationalFees;
worksheet.Cell(currentRow, 9).Value = item.COB;
worksheet.Cell(currentRow, 10).SetFormulaA1("=ROUND((D" + currentRow + "+E" + currentRow + "+F" + currentRow + "+G" + currentRow + "+H" + currentRow + "+I" + currentRow + "),2)");
worksheet.Range(currentRow, 1, currentRow, 10).Style.NumberFormat.Format = "_(* #,##0.00_);_(* (#,##0.00);_(* \"-\"_);_(@_)";
worksheet.Range(currentRow, 1, currentRow, 10).Style.Alignment.WrapText = true;
}
using (var ms = new MemoryStream())
{
workbook.Save();
}
}
catch (Exception ex)
{
fileNames = fileNames == "" ? file.Name : fileNames + ", " + file.Name;
EmailService.SendEmail(Startup.errorEmail, "Error in file - " + file.Name, ex.Message.ToString() + "\n Exception Trace: " + ex.StackTrace, "");
}
finally {
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
workbook.Dispose();
}
}
