Solved
Test result status not refreshing
I have this function which I have picked up from a few examples online and managed to get it to write to excel with the result of the test that run. However it seems that either it doesn't refresh after every run or it gets the previous result log. I have created a test to test this function which writes log messages and putting the result in the 3 possible outcomes and everything I run it it puts the precious result to excel... Could someone point me in the right direction as I believe I'm using this method incorrectly.
function WriteDataToExcel(fname, sheetName,testTime, errorCount, warnCount)
{
var app = Sys.OleObject("Excel.Application");
var book = app.Workbooks.Open(fname);
var sheet = book.Sheets.Item(sheetName);
var rowCount = sheet.UsedRange.Rows.Count+1;
var columnCount = sheet.UsedRange.Columns.Count;
var Logs;
var Count;
var Name;
var Status = null;
// Obtains the object that holds the list of project logs
Logs = Project.Logs;
Count = Logs.LogItemsCount
// Iterates through the list of project logs
for (var i = 0; i < Count; i++)
{
var LogItem = LogsCol.LogItem(i);
Name = Logs.LogItem(i).Name;
Status = Logs.LogItem.Status;
}
var TestResult = Status;
switch (Status)
{
case 0:
TestResult = "Passed"
break;
case 1:
TestResult = "Warnings"
break;
case 2:
TestResult = "Failed"
break;
default :
TestResult = "Error"
break;
}
app.Cells(rowCount,columnCount-5) = (aqConvert.DateTimeToFormatStr(aqDateTime.Now(), "%d/%m/%y %H:%M"));
app.Cells(rowCount,columnCount-4) = Name;
app.Cells(rowCount,columnCount-3) = TestResult;
app.Cells(rowCount, columnCount-2) = testTime;
app.Cells(rowCount, columnCount-1) = errorCount;
app.Cells(rowCount, columnCount-0) = warnCount;
book.Save();
app.Quit();
}
function WriteDataToExcel(fname, sheetName,testTime, errorCount, warnCount)
{
var app = Sys.OleObject("Excel.Application");
var book = app.Workbooks.Open(fname);
var sheet = book.Sheets.Item(sheetName);
var rowCount = sheet.UsedRange.Rows.Count+1;
var columnCount = sheet.UsedRange.Columns.Count;
var Logs;
var Count;
var Name;
var Status = null;
// Obtains the object that holds the list of project logs
Logs = Project.Logs;
Count = Logs.LogItemsCount
// Iterates through the list of project logs
for (var i = 0; i < Count; i++)
{
var LogItem = LogsCol.LogItem(i);
Name = Logs.LogItem(i).Name;
Status = Logs.LogItem.Status;
}
var TestResult = Status;
switch (Status)
{
case 0:
TestResult = "Passed"
break;
case 1:
TestResult = "Warnings"
break;
case 2:
TestResult = "Failed"
break;
default :
TestResult = "Error"
break;
}
app.Cells(rowCount,columnCount-5) = (aqConvert.DateTimeToFormatStr(aqDateTime.Now(), "%d/%m/%y %H:%M"));
app.Cells(rowCount,columnCount-4) = Name;
app.Cells(rowCount,columnCount-3) = TestResult;
app.Cells(rowCount, columnCount-2) = testTime;
app.Cells(rowCount, columnCount-1) = errorCount;
app.Cells(rowCount, columnCount-0) = warnCount;
book.Save();
app.Quit();
}
The reason being is that the current log MIGHT not be saved to disk at the time you go to access the Project.Logs object. There is a setting to save the log every n minutes but that means that you might still have missing data of up to 60 seconds of time.
So... taking your code as you have it, I would add the following (in bold)
unction WriteDataToExcel(fname, sheetName,testTime, errorCount, warnCount) { var app = Sys.OleObject("Excel.Application"); var book = app.Workbooks.Open(fname); var sheet = book.Sheets.Item(sheetName); var rowCount = sheet.UsedRange.Rows.Count+1; var columnCount = sheet.UsedRange.Columns.Count; var Logs; var Count; var Name; var Status = null; // Obtains the object that holds the list of project logs Log.SaveToDisk(); Logs = Project.Logs; Count = Logs.LogItemsCount // Iterates through the list of project logsThis will flush all log data that is in memory to disk before you start working with it.
So, I have changed some of the lines which are highlighted and added function to get properly enter the data into excel sheet.
function WriteDataToExcel(fname, sheetName,testTime, errorCount, warnCount){ var app = Sys.OleObject("Excel.Application"); var book = app.Workbooks.Open(fname); var sheet = book.Sheets.Item(sheetName); var rowCount = sheet.UsedRange.Rows.Count+1; var columnCount = sheet.UsedRange.Columns.Count; var Logs; var Count; // Obtains the object that holds the list of project logs Logs = Project.Logs; Count = Logs.LogItemsCount; var TestResult = Logs.LogItem(Count-1).Status; switch (TestResult) //previously it is like Status which is undefined
{ case 0: TestResult = "Passed" break; case 1: TestResult = "Warnings" break; case 2: TestResult = "Failed" break; default : TestResult = "Error" break; } var arrayLogData = [aqConvert.DateTimeToFormatStr(aqDateTime.Now(), "%d/%m/%y %H:%M"), Logs.LogItem(Count-1).Name, TestResult, testTime, errorCount, warnCount] // When you need to add additional data, just add the new element here sheet.Range("A" + rowCount).Value = alignData(arrayLogData[0]); sheet.Range("B" + rowCount).Value = alignData(arrayLogData[1]); sheet.Range("C" + rowCount).Value = alignData(arrayLogData[2]); sheet.Range("D" + rowCount).Value = alignData(arrayLogData[3]); sheet.Range("E" + rowCount).Value = alignData(arrayLogData[4]); sheet.Range("F" + rowCount).Value = alignData(arrayLogData[5]); // If you want add more columns then you can add like below //sheet.Range("G" + inputRow).Value = alignData(arrayLogData[6]); book.Save(); app.Quit(); } function alignData(datatoEnter) { try { var tempValue = aqConvert.VarToStr(datatoEnter); if(tempValue != "") { return tempValue; } else { return ""; } } catch(ex) { return "ERROR FIELD"; } }Let me know your results.
