Now, it's causing us problems and I have two different solutions, neither of which work. Essentially, I am trying to update my test data on the fly so I can reuse that data in later iterations.
Here is what we are trying to accomplish, imagine this is a simple data table in Excel:
Simple Sheet example:
Code: Select all
A B
1 Comment OrderID
2 Create Order / Get ID ???
3 Verify Order ID =B2
To get this information, I have references in my Excel sheet to cell, B2.
In Ranorex, I have successfully done the following, but neither accomplish my goals for different reasons.
Solution #1
Updated the actual Excel Datasheet Cell #B2 with the data. Opened the sheet during run time and confirmed both B2 and B3 have the correct data.
Didn't Work Because...
I could never get Ranorex to reload the data from Excel. I tried all kinds of ways to this, here are a couple:
Code: Select all
TestCase.Current.DataContext.ReloadData();
TestCase.Current.DataContext.ReloadData(true);
TestCase.Current.DataContext.Source.Load()
I tried a different approach after that - rather than updating the sheet, I updated the TestCase.Current.DataContext.Source.Rows with the new data instead. This is preferred because it doesn't mess with our "saved" "hardcopy" of the data.
Didn't Work Because...
For some reason, the references are lost. So, I was able to update cell B2, but the value in cell B3 remained the same as it was when the data was originally loaded - as if the reference to cell B2 never existed. Somehow this works in QTP without a hitch.
I tried the above methods reload the data, and somehow it loaded the original data, not sure from where because when I changed the sheet it wasn't reloaded then so it must be held elsewhere.
Code I Used
Here is the code I used for solution 1 (note, it uses a package called EPPlus (http://epplus.codeplex.com/):
Code: Select all
int rowtoUpdate = TestCase.Current.DataContext.CurrentRowIndex;
Ranorex.Core.Data.ExcelDataConnector connector = (Ranorex.Core.Data.ExcelDataConnector)TestCase.Current.DataContext.Source.Connector;
ExcelPackage package = new ExcelPackage(new FileInfo(connector.FileName));
ExcelWorksheet sheetToBeUpdated = package.Workbook.Worksheets[connector.WorksheetName];
ExcelRow headerRow = sheetToBeUpdated.Row(1);
int columnID = -1;
for(int i = 1; i < sheetToBeUpdated.Cells.Count(); i++)
{
if ((string)sheetToBeUpdated.Cells[1, i].Value == columnToUpdate)
{
columnID = i;
break;
}
}
if(columnID == -1)
{
throw new InternalTestException("Column: " + columnToUpdate + " was not found in " + connector.WorksheetName + " at " + connector.ResolvedFileName);
}
sheetToBeUpdated.SetValue(rowtoUpdate+1, columnID, updatedValue);
package.Save();
Code: Select all
int columnIndex = -1;
foreach(Ranorex.Core.Data.Column dataColumn in TestCase.Current.DataContext.Source.Columns)
{
if (dataColumn.Name == columnToUpdate)
{
columnIndex = dataColumn.Index;
break;
}
}
int rowCounts = TestCase.Current.DataContext.Source.Rows.Count;
int currentRowIndex = TestCase.Current.DataContext.CurrentRowIndex;
Ranorex.Core.Data.RowCollection testCollection = TestCase.Current.DataContext.Source.Rows;
for (int i = 0; i < testCollection.Count; i++)
{
Reporting.LogInfoMessage("Row #: " + i.ToString() + string.Join(",", testCollection[i].Values));
}
TestCase.Current.DataContext.Source.Rows[TestCase.Current.DataContext.CurrentRowIndex-1].Values[columnIndex] = updatedValue;
Somehow, I need this to work. I will go through the forums and try and find other solutions, but my preference would be something along the lines of solution #2 so the sheet itself does not have to change.
Thanks!