I have a code that converts bunch of csv files into excel and then highlights some rows.When I feed multiple files it throws memory out of exception . I believe there are memory leaks in code pertaining to com objects.Also, I am not sure how to correctly place garbage collectors. Below is my do work method of background worker . It works fine if I put single file but for multiple file is throws exception. I just need help in assesing if i am correctly releasing memory ,or my logic is inefficient, or really i am using extra memory [I dont believe this might be case] Giving below my code for expert analysis
try
{
Microsoft.Office.Interop.Excel.Application oXL;
Microsoft.Office.Interop.Excel.Workbook oWB;
Microsoft.Office.Interop.Excel.Worksheet oSheet;
Microsoft.Office.Interop.Excel.Range oRng;
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = false;
oWB = (Microsoft.Office.Interop.Excel.Workbook)(oXL.Workbooks.Add(""));
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.ActiveSheet;
oSheet.Name = "Summary Report";
object misvalue = System.Reflection.Missing.Value;
int file_progress_Delta = 72 / lstFileName.Count();
int droppedCount = 0;
int acceptedCount = 0;
int invalidMACCount = 0;
int ARCRepeatedCount = 0;
int tMAC1matchedCount = 0;
int tMACmatchedCount = 0;
int tMAC2matchedCount = 0;
int tMAC3matchedCount = 0;
int syncFrameARCUpdatedCount = 0;
int resyncFrameARCUpdatedCount = 0;
int syncFrameARCNotUpdatedCount = 0;
int resyncFrameARCNotUpdatedCount = 0;
int securedinvalidframeCount = 0;
int ARClessthanwindowCount = 0;
int tMACNotMatchedSyncFrame = 0;
int tMACNotMatchedReSyncFrame = 0;
int tMACMatched = 0;
int IPDUDLCZeroCount = 0;
int IPDUDLCEightCount = 0;
int invalidpaddingCount = 0;
int invalidContainerFrame = 0;
oSheet.Cells[2, 3] = "Log Analysis Report";
oSheet.Cells[4, 4] = "Category";
oSheet.Cells[4, 5] = "Count";
oSheet.Cells[6, 4] = "Result";
oSheet.Cells[7, 4] = "Accepted";
oSheet.Cells[8, 4] = "Dropped";
oSheet.Cells[10, 4] = "Remarks";
oSheet.Cells[11, 4] = "Invalid MAC";
oSheet.Cells[12, 4] = "ARC Repeated";
oSheet.Cells[13, 4] = "tMAC1 matched";
oSheet.Cells[14, 4] = "tMAC2 matched";
oSheet.Cells[15, 4] = "tMAC3 matched";
oSheet.Cells[16, 4] = "Sync Frame ARC Updated";
oSheet.Cells[17, 4] = "Resync Frame ARC Updated";
oSheet.Cells[18, 4] = "MAC matched. Sync frame ARC not updated";
oSheet.Cells[19, 4] = "MAC matched. Resync frame ARC not updated";
oSheet.Cells[20, 4] = "Secured invalid frame";
oSheet.Cells[21, 4] = "ARC less than window";
oSheet.Cells[22, 4] = "tMAC Not Matched Sync Frame";
oSheet.Cells[23, 4] = "tMAC Not Matched ReSync Frame";
oSheet.Cells[24, 4] = "tMAC Matched";
oSheet.Cells[25, 4] = "Secure container frame check";
oSheet.Cells[26, 4] = "IPDU DLC =0";
oSheet.Cells[27, 4] = "IPDU DLC >8";
oSheet.Cells[28, 4] = "Invalid padding";
oSheet.Cells[29, 4] = "Invalid Container Frame";
oSheet.Cells[30, 4] = "Sync ARC jumps greater than 1 million";
oSheet.get_Range("E7", "E30").HorizontalAlignment =
Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
oSheet.get_Range("C2", "F2").HorizontalAlignment =
Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
oSheet.get_Range("C2", "F2").Font.Bold = true;
oSheet.get_Range("C2", "F2").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightBlue);
oSheet.get_Range("D4", "E4").Font.Bold = true;
oSheet.get_Range("D4", "E4").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightBlue);
oSheet.get_Range("D4", "E4").VerticalAlignment =
Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
oSheet.get_Range("D6", "E6").Font.Bold = true;
oSheet.get_Range("D6", "E6").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightBlue);
oSheet.get_Range("D6", "E6").VerticalAlignment =
Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
oSheet.get_Range("D10", "E10").Font.Bold = true;
oSheet.get_Range("D10", "E10").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightBlue);
oSheet.get_Range("D10", "E10").VerticalAlignment =
Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
oSheet.get_Range("D25", "E25").Font.Bold = true;
oSheet.get_Range("D25", "E25").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightBlue);
oSheet.get_Range("D25", "E25").VerticalAlignment =
Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
oSheet.get_Range("D30", "E30").Font.Bold = true;
oSheet.get_Range("D30", "E30").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightBlue);
oSheet.get_Range("D30", "E30").VerticalAlignment =
Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
oRng = oSheet.get_Range("C2", "F2");
oRng.Borders.Color = System.Drawing.Color.Black.ToArgb();
oRng = oSheet.get_Range("D4", "D30");
oRng.Borders.Color = System.Drawing.Color.Black.ToArgb();
oRng = oSheet.get_Range("E4", "E30");
oRng.Borders.Color = System.Drawing.Color.Black.ToArgb();
oSheet.Range[oSheet.Cells[2, 3], oSheet.Cells[2, 6]].Merge();
oRng = oSheet.get_Range("D4", "E4");
oRng.EntireColumn.AutoFit();
//backgroundWorker1.ReportProgress(3,0);
backgroundWorker1.ReportProgress(5);
percentageCounter = 5;
fileCount = 0;
foreach (String file in lstFileName)
{
//GC.Collect();
//GC.WaitForPendingFinalizers();
Thread.Sleep(1000);
Microsoft.Office.Interop.Excel.Application appCSVToExcel;
Excel.Application appHighlight;
string name = file.Split('.')[0].ToString().Split('\\').Last();
//Converting each file from .csv to excel
appCSVToExcel = new Microsoft.Office.Interop.Excel.Application();
appCSVToExcel.DisplayAlerts = false;
Microsoft.Office.Interop.Excel.Workbook wbCSVToExcel = appCSVToExcel.Workbooks.Open(file, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wbCSVToExcel.SaveAs(outputFolderPath + "\\" + name + ".xlsx", Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wbCSVToExcel.Close();
appCSVToExcel.Quit();
//int generation_3 = System.GC.GetGeneration(appCSVToExcel);
//System.GC.Collect(generation_3);
Marshal.ReleaseComObject(wbCSVToExcel);
Marshal.ReleaseComObject(appCSVToExcel);
//appCSVToExcel =null;
//wbCSVToExcel = null;
//GC.Collect();
//GC.WaitForPendingFinalizers();
//Thread.Sleep(2);
//backgroundWorker1.ReportProgress(10);
//Highlighting the excel files
//Application.DoEvents();
//GC.Collect();
appHighlight = new Excel.Application();
var wbHighlight = appHighlight.Workbooks;
var workbook = wbHighlight.Open(outputFolderPath + "\\" + name + ".xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet worksheet = workbook.ActiveSheet;
Microsoft.Office.Interop.Excel.Range usedRange = worksheet.UsedRange;
Microsoft.Office.Interop.Excel.Range rows = usedRange.Rows;
Microsoft.Office.Interop.Excel.Range columns = usedRange.Columns;
appHighlight.DisplayAlerts = false;
r = rows.Count;
c = columns.Count;
var startCell = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1];
var endCell = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r, 12];
object[,] RangeValues = worksheet.get_Range(startCell, endCell).Value2;
ArrayList higlight = new ArrayList();
for (int i = 1; i <= r; i++)
{
if (RangeValues[i, 8] != null)
{
//if (RangeValues[i, 9].ToString() == "Invalid MAC"
//|| RangeValues[i, 9].ToString() == "ARC Repeated"
//|| RangeValues[i, 9].ToString() == "Secured invalid frame"
//|| RangeValues[i, 9].ToString() == "ARC less than window")
if (RangeValues[i, 8].ToString() == "Dropped")
{
higlight.Add(i);
}
}
//Thread.Sleep(2);
//backgroundWorker1.ReportProgress(20);
string firstCellValue1 = "";
if (RangeValues[i, 8] != null)
{
firstCellValue1 = RangeValues[i, 8].ToString();
}
if (firstCellValue1 == "Accepted")
{
acceptedCount++;
}
if (firstCellValue1 == "Dropped")
{
droppedCount++;
}
string cell = "";
if (RangeValues[i, 9] != null)
{
cell = RangeValues[i, 9].ToString();
}
switch (cell)
{
case "Invalid MAC":
invalidMACCount++;
break;
case "ARC Repeated":
ARCRepeatedCount++;
break;
case "tMAC1 matched":
tMAC1matchedCount++;
break;
case "tMAC2 matched":
tMAC2matchedCount++;
break;
case "tMAC3 matched":
tMAC3matchedCount++;
break;
case "Sync Frame ARC Updated":
syncFrameARCUpdatedCount++;
break;
case "Resync Frame ARC Updated":
resyncFrameARCUpdatedCount++;
break;
case "MAC matched. Sync frame ARC not updated":
syncFrameARCNotUpdatedCount++;
break;
case "MAC matched. Resync frame ARC not updated":
resyncFrameARCNotUpdatedCount++;
break;
case "ARC less than window":
ARClessthanwindowCount++;
break;
case "tMAC Matched":
tMACmatchedCount++;
break;
case "tMAC Not Matched Sync Frame":
tMACNotMatchedSyncFrame++;
break;
case "tMAC Not Matched ReSync Frame":
tMACNotMatchedReSyncFrame++;
break;
default:
break;
}
string cellReceptionRemarks = "";
if (RangeValues[i, 12] != null)
{
cellReceptionRemarks = RangeValues[i, 12].ToString();
}
switch (cellReceptionRemarks)
{
case "Zero DLC of 'contained I-PDU' has been detected.":
IPDUDLCZeroCount++;
break;
case "DLC greater than 8 in 'contained I-PDU' has been detected.":
IPDUDLCEightCount++;
break;
case "Padding other than 0x00 or 0xFF is observed in frame":
invalidpaddingCount++;
break;
case "Invalid container frame structure.":
invalidContainerFrame++;
break;
}
//backgroundWorker1.ReportProgress(25,0);
//percentageCounterFile++;
//int countProcessFile = r;
//percentFile = 100 - (((countProcessFile - percentageCounterFile) * 100) / countProcessFile);
//backgroundWorker1.ReportProgress(percentFile);
}
for (int k = 0; k < higlight.Count; k++)
{
string exclcmnS1 = "A" + higlight[k];
string exclcmnL1 = "L" + higlight[k];
Excel.Range _range1;
_range1 = worksheet.get_Range(exclcmnS1, exclcmnL1);
_range1.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Pink);
_range1.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
}
workbook.SaveAs(outputFolderPath + "\\" + name + ".xlsx", Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// GC.Collect();
//GC.WaitForPendingFinalizers();
//GC.Collect();
//GC.WaitForPendingFinalizers();
int generation = System.GC.GetGeneration(appHighlight);
System.GC.Collect(generation);
//int generation_4 = System.GC.GetGeneration(wbHighlight);
//System.GC.Collect(generation_4);
//int generation_5 = System.GC.GetGeneration(workbook);
//System.GC.Collect(generation_4);
//int generation_6 = System.GC.GetGeneration(worksheet);
//System.GC.Collect(generation_4);
//int generation_7 = System.GC.GetGeneration(usedRange);
//System.GC.Collect(generation_4);
//int generation_8 = System.GC.GetGeneration(rows);
//System.GC.Collect(generation_4);
//int generation_9 = System.GC.GetGeneration(columns);
//System.GC.Collect(generation_4);
wbHighlight.Close();
//workbook.Close(0);
appHighlight.Quit();
//usedRange.Clear();
//rows.Clear();
//columns.Clear();
while (Marshal.ReleaseComObject(appHighlight) != 0) { }
while (Marshal.ReleaseComObject(wbHighlight) != 0) { }
while (Marshal.ReleaseComObject(workbook) != 0) { }
while (Marshal.ReleaseComObject(worksheet) != 0) { }
while (Marshal.ReleaseComObject(usedRange) != 0) { }
while (Marshal.ReleaseComObject(rows) != 0) { }
while (Marshal.ReleaseComObject(columns) != 0) { }
//while (Marshal.ReleaseComObject(worksheet.get_Range(startCell, endCell)) != 0) { }
//while (Marshal.ReleaseComObject(RangeValues) != 0) { }
//while (Marshal.ReleaseComObject(_range1) != 0) { }
appHighlight = null;
wbHighlight = null;
workbook = null;
worksheet = null;
usedRange = null;
rows = null;
columns = null;
RangeValues = null;
//_range1 = null;
//startCell = null;
//endCell = null;
//higlight = null;
//KillSpecificExcelFileProcess(name + ".xlsx");
//Thread.Sleep(2);
//backgroundWorker1.ReportProgress(60,0);
//GC.Collect();
//GC.WaitForPendingFinalizers();
//var processes = from p in Process.GetProcessesByName("EXCEL") select p;
//foreach (var process in processes)
//{
// process.Kill();
//}
//For Abnormal ARC
//rch_updates.AppendText(DateTime.Now.ToString("t") + " " + "Analyzing file : "+file +" for Abnormal ARC Increments.\n");
// Application.DoEvents();
var excel_Report = new excel.Application();
var excelWB = excel_Report.Workbooks.Add();
var workSheet = excelWB.ActiveSheet;
dict_ADAS = new Dictionary<Int64, Int64>();
dict_BCM = new Dictionary<Int64, Int64>();
dict_CDM = new Dictionary<Int64, Int64>();
dict_AVM = new Dictionary<Int64, Int64>();
dict_SONAR = new Dictionary<Int64, Int64>();
dict_PWT = new Dictionary<Int64, Int64>();
dict_ATCU = new Dictionary<Int64, Int64>();
// List of class Logdata
data = new List<LogData>();
dict_LogData = new Dictionary<Int64, LogData>();
List<string> lines = new List<string>();
workSheet.Name = "Abnormal ARC Observations";
excel_Report.Visible = false;
workSheet.Cells[1, "A"] = "Time Stamp";
workSheet.Cells[1, "B"] = "CAN ID";
workSheet.Cells[1, "C"] = "DLC";
workSheet.Cells[1, "D"] = "CAN PAYLOAD";
workSheet.Cells[1, "E"] = "RESULT";
workSheet.Cells[1, "F"] = "REMARK";
workSheet.Cells[1, "G"] = "ARC Difference";
//Thread.Sleep(2);
//backgroundWorker1.ReportProgress(65,0);
readCSV(file, lines);
//backgroundWorker1.ReportProgress(70,0);
categorizeSyncFrames();
//backgroundWorker1.ReportProgress(75,0);
identifyAbnormalIndices();
//backgroundWorker1.ReportProgress(80);
writeToReport(workSheet);
//Thread.Sleep(2);
//backgroundWorker1.ReportProgress(85);
dict_LogData.Clear();
dict_ATCU.Clear();
dict_ADAS.Clear();
dict_AVM.Clear();
dict_CDM.Clear();
dict_PWT.Clear();
dict_SONAR.Clear();
dict_BCM.Clear();
ADAS_Indices_List.Clear();
BCM_Indices_List.Clear();
CDM_Indices_List.Clear();
AVM_Indices_List.Clear();
SONAR_Indices_List.Clear();
PWT_Indices_List.Clear();
ATCU_Indices_List.Clear();
data.Clear();
dict_LogData.Clear();
excel_Report.DisplayAlerts = false;
excelWB.SaveAs(outputFolderPath + "\\" + "Abnormal_ARC_Increment" + DateTime.Now.ToLongDateString() + ".xlsx", excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//GC.Collect();
//GC.WaitForPendingFinalizers();
int generation_2 = System.GC.GetGeneration(excel_Report);
System.GC.Collect(generation_2);
excelWB.Close(0);
excel_Report.Quit();
//rch_updates.AppendText(DateTime.Now.ToString("t") + " " + "File Analysis complete for : " + file + " for Abnormal ARC Increments.\n");
while (Marshal.ReleaseComObject(excel_Report) != 0) { }
while (Marshal.ReleaseComObject(excelWB) != 0) { }
while (Marshal.ReleaseComObject(workSheet) != 0) { }
excel_Report = null;
excelWB = null;
workSheet = null;
fileCount++;
percentageCounter += file_progress_Delta;
backgroundWorker1.ReportProgress(percentageCounter);
}
oSheet.Cells[7, 5] = acceptedCount;
oSheet.Cells[8, 5] = droppedCount;
oSheet.Cells[11, 5] = invalidMACCount;
oSheet.Cells[12, 5] = ARCRepeatedCount;
oSheet.Cells[13, 5] = tMAC1matchedCount;
oSheet.Cells[14, 5] = tMAC2matchedCount;
oSheet.Cells[15, 5] = tMAC3matchedCount;
oSheet.Cells[16, 5] = syncFrameARCUpdatedCount;
oSheet.Cells[17, 5] = resyncFrameARCUpdatedCount;
oSheet.Cells[18, 5] = syncFrameARCNotUpdatedCount;
oSheet.Cells[19, 5] = resyncFrameARCNotUpdatedCount;
oSheet.Cells[20, 5] = securedinvalidframeCount;
oSheet.Cells[21, 5] = ARClessthanwindowCount;
oSheet.Cells[22, 5] = tMACNotMatchedSyncFrame;
oSheet.Cells[23, 5] = tMACNotMatchedReSyncFrame;
oSheet.Cells[24, 5] = tMACMatched;
oSheet.Cells[26, 5] = IPDUDLCZeroCount;
oSheet.Cells[27, 5] = IPDUDLCEightCount;
oSheet.Cells[28, 5] = invalidpaddingCount;
oSheet.Cells[29, 5] = invalidContainerFrame;
//Abnromal ARC observations count
oSheet.Cells[30, 5] = (i - 1) / 2;
backgroundWorker1.ReportProgress(85);
oXL.Visible = false;
oXL.UserControl = false;
oXL.ActiveWindow.DisplayGridlines = false;
oXL.DisplayAlerts = false;
oWB.SaveAs(outputFolderPath + "\\" + DateTime.Now.ToLongDateString() + "Report.xlsx", Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oWB.Close(0);
oXL.Quit();
backgroundWorker1.ReportProgress(90);
//Marshal.FinalReleaseComObject(oSheet);
//Marshal.FinalReleaseComObject(oWB);
//Marshal.FinalReleaseComObject(oXL);
//Marshal.FinalReleaseComObject(oRng);
while (Marshal.ReleaseComObject(oXL) != 0) { }
while (Marshal.ReleaseComObject(oWB) != 0) { }
while (Marshal.ReleaseComObject(oSheet) != 0) { }
while (Marshal.ReleaseComObject(oRng) != 0) { }
oXL = null;
oWB = null;
oSheet = null;
oRng = null;
//KillSpecificExcelFileProcess(DateTime.Now.ToLongDateString() + "Report.xlsx");
//Thread.Sleep(2);
backgroundWorker1.ReportProgress(100);
//progressBar3.Value = 100;
e.Result = true;
}
catch (Exception ex)
{
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
MessageBox.Show(ex.ToString());
//wbHighlight.Close();
//appHighlight.Quit();
//int generation = System.GC.GetGeneration(appHighlight);
//System.GC.Collect(generation);
var processes = from p in Process.GetProcessesByName("EXCEL") select p;
foreach (var process in processes)
{
process.Kill();
}
}
finally
{
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
//wbHighlight.Close();
//appHighlight.Quit();
//int generation = System.GC.GetGeneration(appHighlight);
//System.GC.Collect(generation);
var processes = from p in Process.GetProcessesByName("EXCEL") select p;
foreach (var process in processes)
{
process.Kill();
}
}
