public void WriteExcel(string path, string sheet, int row, string result) { string screenshothref = ""; Application excel = null; Workbook wb = null; Worksheet ws = null; Range rng = null; FileHandler file = new FileHandler(); object missing = Type.Missing; DateTime now = DateTime.Now; string date = now.ToString(); date = date.Replace(":", "-"); date = date.Replace("/", "-"); date = date.Replace(" ", "-"); FileHandler CurPath = new FileHandler(); string c = Directory.GetCurrentDirectory(); var stringSeparators = new[] { "bin" }; string[] str1 = c.Split(stringSeparators, StringSplitOptions.None); string imagepath = Parallel_Quest.ImagePath; try { excel = new Application(); //If I use Open or _Open it gives the same wb = excel.Workbooks.Open(path, missing, //updatelinks false, //readonly missing, //format missing, //Password missing, //writeResPass true, //ignoreReadOnly missing, //origin missing, //delimiter true, //editable missing, //Notify missing, //converter missing, //AddToMru missing, //Local missing); //corruptLoad ws = (Worksheet)wb.Sheets[sheet]; ws.Select(Type.Missing); rng = ws.get_Range("F" + row, missing); rng.Value2 = result.ToUpper(); rng.Font.Bold = true; string StepName = ws.Name + row.ToString(); if ((result.ToLower() == "pass")) { rng.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green); } else if ((result.ToLower() == "fail")) { screenshothref = Take_Screenshot(sheet, row, "fail", imagepath); rng.Hyperlinks.Add(rng, screenshothref); rng.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); } string tmpName = Path.GetTempFileName(); File.Delete(tmpName); wb.SaveAs(tmpName, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing); wb.Close(false, missing, missing); GC.Collect(); GC.WaitForPendingFinalizers(); Marshal.FinalReleaseComObject(rng); Marshal.FinalReleaseComObject(ws); Marshal.FinalReleaseComObject(wb); File.Delete(path); File.Move(tmpName, path); excel.Quit(); Marshal.FinalReleaseComObject(excel); } catch (Exception) { GC.Collect(); GC.WaitForPendingFinalizers(); Marshal.FinalReleaseComObject(rng); Marshal.FinalReleaseComObject(ws); Marshal.FinalReleaseComObject(wb); excel.Quit(); Marshal.FinalReleaseComObject(excel); } }
public Workbook DashBoard_Update(string path, Workbook wb, int row, List <string> result) { string sheet = "DashBoard"; string screenshothref = ""; Worksheet ws = null; Range rng1 = null; Range rng2 = null; Range rng3 = null; Range rng4 = null; Range rng = null; FileHandler file = new FileHandler(); object missing = Type.Missing; DateTime now = DateTime.Now; string date = now.ToString(); date = date.Replace(":", "-"); date = date.Replace("/", "-"); date = date.Replace(" ", "-"); FileHandler CurPath = new FileHandler(); string c = Directory.GetCurrentDirectory(); var stringSeparators = new[] { "bin" }; string[] str1 = c.Split(stringSeparators, StringSplitOptions.None); string imagepath = Parallel_Quest.ImagePath; try { ws = (Worksheet)wb.Sheets[sheet]; ws.Select(Type.Missing); rng1 = ws.get_Range("E" + row, missing); rng1.Value2 = result[3]; rng1.Font.Bold = true; string StepName = ws.Name + row.ToString(); if ((result[3].ToLower() == "pass")) { rng1.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green); } else if ((result[3].ToLower() == "fail")) { screenshothref = Take_Screenshot(sheet, row, "fail", imagepath); // rng.Hyperlinks.Add(rng, screenshothref); rng1.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); } rng2 = ws.get_Range("F" + row, missing); rng2.Value2 = result[0]; rng2.Font.Bold = true; rng3 = ws.get_Range("G" + row, missing); rng3.Value2 = result[1]; rng3.Font.Bold = true; rng3.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green); rng4 = ws.get_Range("H" + row, missing); rng4.Value2 = result[2]; rng4.Font.Bold = true; rng4.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); string tmpName = Path.GetTempFileName(); File.Delete(tmpName); wb.SaveAs(tmpName, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing); GC.Collect(); GC.WaitForPendingFinalizers(); Marshal.FinalReleaseComObject(rng1); Marshal.FinalReleaseComObject(rng2); Marshal.FinalReleaseComObject(rng3); Marshal.FinalReleaseComObject(rng4); // Marshal.FinalReleaseComObject(ws); File.Delete(path); File.Copy(tmpName, path); return(wb); } catch (Exception) { GC.Collect(); GC.WaitForPendingFinalizers(); Marshal.FinalReleaseComObject(rng1); Marshal.FinalReleaseComObject(rng2); Marshal.FinalReleaseComObject(rng3); Marshal.FinalReleaseComObject(rng4); // Marshal.FinalReleaseComObject(ws); return(null); } }
public Worksheet Write_Result_Excel(Worksheet ws, string result, int row) { Range rng = null; Range rngErrormessage = null; object missing = Type.Missing; string screenshothref = ""; FileHandler file = new FileHandler(); DateTime now = DateTime.Now; string date = now.ToString(); date = date.Replace(":", "-"); date = date.Replace("/", "-"); date = date.Replace(" ", "-"); FileHandler CurPath = new FileHandler(); string c = Directory.GetCurrentDirectory(); var stringSeparators = new[] { "bin" }; string[] str1 = c.Split(stringSeparators, StringSplitOptions.None); string imagepath = Parallel_Quest.ImagePath; try { rng = ws.get_Range("F" + row, missing); rng.Font.Bold = true; string StepName = ws.Name + row.ToString(); if ((result.ToLower() == "pass")) { rng.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green); rng.Value2 = result.ToUpper(); } else if ((result.ToLower() == "fail")) { screenshothref = Take_Screenshot(ws.Name, row, "fail", imagepath); rng.Hyperlinks.Add(rng, screenshothref); rng.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); rng.Value2 = "FAIL"; rngErrormessage = ws.get_Range("G" + row, missing); rngErrormessage.Value2 = result.ToUpper(); } else if ((result.ToLower() != "fail")) { screenshothref = Take_Screenshot(ws.Name, row, "fail", imagepath); rng.Hyperlinks.Add(rng, screenshothref); rng.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); rng.Value2 = "FAIL"; rngErrormessage = ws.get_Range("G" + row, missing); rngErrormessage.Value2 = result.ToLower(); } return(ws); } catch (System.Exception) { Marshal.FinalReleaseComObject(rng); return(null); } finally { Marshal.FinalReleaseComObject(rng); } }