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);
}
}
