Forum Discussion

Vec84's avatar
Vec84
Contributor
8 years ago

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



}
excel
  • tristaanogre's avatar
    tristaanogre
    8 years ago

    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 logs

    This will flush all log data that is in memory to disk before you start working with it.  

  • shankar_r's avatar
    shankar_r
    8 years ago

    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.

  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    8 years ago

    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 logs

    This will flush all log data that is in memory to disk before you start working with it.  

    • Vec84's avatar
      Vec84
      Contributor
      8 years ago

      Is there a better way to write out to excel other than my example, the reason being it isn't a great way to write as I have to take off the number of cells to go back to the start position and if I need to add further info in the future it becomes cumbersome and not a great design. I added it at the time as it worked but for a scenario to use at work it will need to be more flexible.

      I don't get any intelli sense on the excel specific stuff to try and pick it up which makes it difficult to work with and figure out

       

      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;

       

      • tristaanogre's avatar
        tristaanogre
        Esteemed Contributor
        8 years ago

        Well... couple of things:

        The data you want to write to Excel, I would store it in an Array rather than individual variables.  The reason being is that you're going to write a "for" loop to loop through the cells in your Excel row and input the data in each cell.

         

        If I were to re-write, it looks like this:

        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 (Status) {
        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
    
    for (i = 0; i < columnCount; i++) {
        app.Cells(rowCount, i) = arrayLogData[i]; //This then maps your array of data to yhour column count and sets the values
    }
    book.Save();
    app.Quit();
}

        Then, if you need to add more data, the column count automatically follows through.  You can increase the used columns in your Excel sheet before you add the code here to plan ahead.  Then you just need to assign the new data to a new element of your array and the for loop takes over.

        I THINK this is a better design... but I'd be willing to bet someone will come along and give even better advice. :)

  • Vec84's avatar
    Vec84
    Contributor
    8 years ago
    Thanks very much tristaanogre, you are the TestComplete Master. You have been a great help with all my posts.


  • Vec84's avatar
    Vec84
    Contributor
    8 years ago
    It was the reply I had from tristaanogre - but I can't seem to get it working at all.


    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 (Status) {
    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

    for (i = 0; i < columnCount; i++) {
    app.Cells(rowCount, i) = arrayLogData[i]; //This then maps your array of data to yhour column count and sets the values
    }
    book.Save();
    app.Quit();
    }
    • shankar_r's avatar
      shankar_r
      Community Hero
      8 years ago

      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.

      • Vec84's avatar
        Vec84
        Contributor
        8 years ago

        Thanks again for your reply.

         

        This worked brilliantly and will make it easy for me to add records in the future- Many thanks