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 aft...
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

        This makes a lot more sense, I'm still getting used to programming but picking up as I go along. You have again been a great help and thanks for the example.