DataLoop on Excels: Ability to read both the derived Value and the "TEXT" value of an Excel Cell

Hi Smartbear Team,

TestComplete has a limitation in its out-of-the-box Data Loop feature. The DataLoop feature is not able to read the TEXT property of the Excel Cells.

Example: In an Excel Sheet, we may have a Cell 'A' using "Custom" format applied on it and the Cell 'A' reference another Cell 'B' that holds the actual Date value.

When TestComplete DataLoop reads the Cell 'A', it is unable to read the TEXT value of the Cell, i.e, the displayed value.

To read the TEXT value of the Cell, we instead need to use the COM interface:

function ReadTextFromExcel()

{

var value, text;

var Excel = Sys.OleObject("Excel.Application");

Excel.DisplayAlerts = false;

Excel.Workbooks.Open("C:\\temp\\AutomatedTestData.xlsx", true);

var RowCount   = Excel.ActiveSheet.UsedRange.Rows.Count;

var ColumnCount = Excel.ActiveSheet.UsedRange.Columns.Count;

for (var i = 1; i <= RowCount; i++) {

   for (var j = 1; j <= ColumnCount; j++) {

     value = Excel.Cells(i, j);

     text = Excel.Cells(i, j).Text;

     Log.Message("Cells(" + i + ", " + j + ") = " + value + " (" + text + ")");

   }

}

Excel.Quit();

}

However, this requires COM scripting and may not be a suitable approach for (non-developer) testers.

Raised this issue with all details and example Project Suite in Case #00134392 on the Support Portal and confirmed the limitation with the Support team person (Joseph Michaud). The example Project Suite is attached here for everyone's reference.

Simply run it, and check the Log Messages genearted with the test data in the Excel (Common - Stores) folder to see the issue.

Enhancement:

I suggest that SmartBear should consider an enhancement to the Data Loop feature so that Testers can choose to configure whether to read the "Value" of the Excel Cell or the "TEXT" of the Excel Cell.

The enhancement would make the Data Loop feature more powerful and help non-developer Users.

Thank you.

Regards

Anshuman Johri

Melbourne, Australia