Ranorex Forum

I know it's a big post, but this represents quite a lot of work for us, and we've made the functionality as powerful as we can while still being easy to use.

I wanted to post it because this would have been such a huge help to us long ago, and I'm hoping it can help someone else.

The Problem:

Our test designs center around being able to update our test iterations during runtime. We store our test iterations in Excel rather than SQL (SQL may make things easier programmtically, but Excel is a much nicer interface to work with from a human perspective, especially for when we need to have manual QAs update test data).

Our test iterations for a particular test can span multiple worksheets in an excel book. For us, a test is its own project and may contain many code modules (we do not use recording at all).

During the course of the test, one or more sheets may need to be updated, and that updated data may need to be referenced in a future iteration using a formulaic reference in Excel.

Here's an example of how it may work (I'm sorry it looks so terrible extra spacing seems to be eliminated and I cannot get table codes to work!):

Columns A B Etc.
Action OrderID Everything else
Row 1 BookOrder OrderID#1 ...
Row2 VerifyOrder =B1 ...

Above is the simplest form of referencing we use. There are other more complex requirements as well, but aren't really necessary for the purpose of this illustration.

There were a few big challenges we ran into, most notably:

1. Had to save the excel sheet to get Ranorex to recognize the data
2. Updating the test data was easy, but the ACTUAL values got recorded and the cell references were lost (so in the example above Column B / Row2 would not be equal to Column B Row 1) - this was the greatest challenge to overcome.
3. Eventually we got Ranorex to recognize the changes to future iterations, but never the current iteration.
4. Updating sheets OTHER than the current bound data sheet would not properly update references.
5. Had to re-write EVERY cell in excel, so updates took a long time (30-60 seconds for large sheets).

Thankfully, we managed to overcome all of these obstacles. Now, updates are instant, you do not have to save the sheet (and we don't want to), Ranorex recognizes the new data immediately, all formulas are preserved no matter what sheet is updated.

To be honest, not having this functionality in Ranorex was a huge hindrance to our workflow and we were in danger of not being able to continue using the product (unfortunate considering how much money we spent ) - it was doubly frustrating because we were moving from QTP to Ranorex and this was something QTP handled natively.

Now that we have our own solution... everything is great (we <3 Ranorex).

I wanted to share the solution with everyone else because I know how painful it was for us to get it working properly and this might be useful to others.

The Solution:

One important requirement is that the machine that is running the test MUST have Excel installed on it. We tried using various references and other interfaces to avoid this requirement, but in ALL cases it simply updated the raw data and formulas were NEVER preserved so that solution was not workable for us.

Something to be aware of:
In your program.cs, you want a finally clause that will kill the excel processes (after properly closing the workbooks), this is important in case ranorex crashes, there are some exceptions or things otherwise terminate abnormally. We use CCNET for our integration and have since updated it to kill excel processes prior to starting tests. If this does not happen, then an errant excel process could continue running and lock the sheet. Once this is set up (which is not hard to do at all), you'll never have a problem.

Additionally, you must have databindings set up properly for your test AND your databinding name must match the excel worksheet to be updated. This is good practice anyway because it keeps things clear.

finally
     {
       	ExcelInterface.CloseExcelWorkbooks();
       	OSUtilities.KillProcess("EXCEL");
     }

For the code itself, I'll just attach the CS file we're using. You will have to make some minor adjustments for it work since we are calling certain classes you won't have (like special exceptions we throw, our own implementation of Reporting) but this is pretty simple. You will probably want to change the namespace as well.

We don't actually call this class directly, we call static methods in another classes. For simplicity I suggest you do the same, but you can use it however you see fit. Below are the methods we call to update the datasheet:

In the below example, all you have to do is call the method and pass the name of the column (defined in the first row of the column) and the new value. We use ignorColumnNotFound for special circumstances, likely you can always leave that false (you can see the parameter is optional).

You can see it calls another method, which I will list after this one...

/// <summary>
        /// Updates the test data as if it had been updated in Excel directly WITHOUT saving the Excel sheet. Note that all value references/formulas etc. will
        /// correctly update as well. It will always update the value for the specified column in the current data row.
        /// </summary>
        /// <param name="columnToUpdate">The name of the column to update (an exact match is required).</param>
        /// <param name="updatedValue">The new value.</param>
        /// <param name="ignoreColumnNotFound">If true, no error will be thrown if the column is not present, otherwise an exception will be thrown</param>
        /// <remarks>In order for this method to work correctly, your data binding MUST have the same name as the worksheet tab to be updated.</remarks>
        public static void UpdateActiveTestCaseData(string columnToUpdate, string updatedValue, bool ignoreColumnNotFound = false)
        {
            Dictionary<string, string> updateData = new Dictionary<string, string>();
            updateData.Add(columnToUpdate, updatedValue);

            UpdateActiveTestCaseData(updateData, ignoreColumnNotFound);
        }

This one takes a dictionary where the column name is the key, and the value to update is the value. This way you can do one or more updates all at once. You can see it calls another method called getExcelConnectorAndRows which will follow below...

public static void UpdateActiveTestCaseData(Dictionary<string, string> updateColumnsAndValues, bool ignoreColumnNotFound = false)
        {
            Ranorex.Core.Data.RowCollection testCollection;
            Ranorex.Core.Data.ExcelDataConnector connector;

            getExcelConnectorAndRows(TestCase.Current.DataContext.Source.Connector.Name, out connector, out testCollection);

            ExcelInterface.PerformExcelUpdate(updateColumnsAndValues, testCollection, connector, TestCase.Current.DataContext.CurrentRowIndex, ignoreColumnNotFound);
            TestCase.Current.DataContext.ReloadData();
        }

This method actually gets the excel data from the Ranorex DataCache.

private static void getExcelConnectorAndRows(string connectorName, out Ranorex.Core.Data.ExcelDataConnector connector, out Ranorex.Core.Data.RowCollection testCollection)
        {
            DataCache dataSheet = DataSources.Get(connectorName);

            connector = (Ranorex.Core.Data.ExcelDataConnector)dataSheet.Connector;
            testCollection = dataSheet.Rows;
        }

Finally, if you want to update a specific sheet - that is, a sheet that does not belong to a test that's currently executing, you can all the following method. Note that the sheet STILL must have a databinding set up AND the sheet name must be the same as the databinding name:

public static void UpdateSpecifiedExcelSheet(string connectorName, Dictionary<string, string> updateColumnsAndValues, int excelRowIndex, bool ignoreColumnNotFound = false)
        {
            Ranorex.Core.Data.RowCollection testCollection;
            Ranorex.Core.Data.ExcelDataConnector connector;
            
            const int ROW_OFFSET_FOR_PERFORMEXCELUPDATE = 1;

            getExcelConnectorAndRows(connectorName, out connector, out testCollection);

            ExcelInterface.PerformExcelUpdate(updateColumnsAndValues, testCollection, connector, (excelRowIndex - ROW_OFFSET_FOR_PERFORMEXCELUPDATE), ignoreColumnNotFound);
            TestCase.Current.DataContext.ReloadData();
        }

So calling these fuctions looks like this if you want to update many cells using a dictionary:

Dictionary<string, string> columnsAndValuesToUpdate = new Dictionary<string, string>();
					
					columnsAndValuesToUpdate.Add(colNameCurrentAvailableBalance, holdingBalances.CurrentAvailableBalance.ToString());
					columnsAndValuesToUpdate.Add(colNameCurrentBookBalance, holdingBalances.CurrentBookBalance.ToString());
					columnsAndValuesToUpdate.Add(colNameEndingBookBalanceAsof, holdingBalances.EndingBookBalanceAsof.ToString());
					TestData.UpdateActiveTestCaseData(columnsAndValuesToUpdate);

Or if you just have one cell to update and you don't need a dictionary (this is our most common use):

string confirmationNumber = OrderConfirmation.GetConfirmationNumber();
			if (!string.IsNullOrEmpty(confirmationNumber))
			{
				Reporting.ReportSuccess("Order was committed successfully.  Confirmation Number is '" + confirmationNumber + "'.");
			}
			TestData.UpdateActiveTestCaseData("ConfirmationNumber", confirmationNumber);

If you give it a try and find you still cannot get it work, feel free to post back and I'll do my best to help you. This has been tested THOROUGHLY and we use it constantly, so I'm very confident it's working correctly. Thanks so much!

Carson.

Hi Carsonw,

This is exactly what I'm looking for as well - thanks for posting it! I'm attempting to compile and running into a bit of a blocker at the "TestDictionary" type, which I assume is one of your custom types. Can you please post the definition for this? I'm new to C#, but have some (very old) experience with good ol' C++...

Thanks,

- John

Hi everyone - hopefully our solution has been useful to some of you! An update is required however.

With the release of 4.1.2 the Excel file in the previous post WILL NOT WORK.

With the help of Ranorex support (thanks so much Support Team!) we have an updated version of our Excel interface that will work in the same manner.

Note: This new version of the Excel will WILL NOT WORK with previous versions of Ranorex. So, this fix is 4.1.2. forward only.

If anyone has any trouble with it, please feel free to ask and we'll do our best to help. Thanks kindly!

Carson.

I am brand new to C# and don't have the slightest idea of how to begin implementing this with ranorex. Can you please send me to a link that might help on putting these two together?

Hello Bill,

Do you want to change your data source file during Runtime?
In general, we don't recommend to change this file.

You may find the following simple example helpful that changes a specific cell of an Excel file.
The following code modifies the data source file that is stored in the output folder of your project.

Excel.Application excelDataconnectorSourceFile = new Excel.ApplicationClass();            
excelDataconnectorSourceFile.Visible = true;  
string workbookPath = System.IO.Directory.GetCurrentDirectory() + "\\ExcelFile.xlsx";  
Excel.Workbook excelWorkbook = excelDataconnectorSourceFile.Workbooks.Open(workbookPath);  
Excel.Sheets excelSheets = excelWorkbook.Worksheets;  
string currentSheet = "Sheet1";  
Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);  
excelDataconnectorSourceFile.Cells[1,4] = "Insert value during runtime";  
excelWorkbook.Save();  
excelWorkbook.Close();

I hope this example helped you to find a better understanding how it works in C#

Regards,
Markus (T)

johng14 wrote:Hi Carsonw,

This is exactly what I'm looking for as well - thanks for posting it! I'm attempting to compile and running into a bit of a blocker at the "TestDictionary" type, which I assume is one of your custom types. Can you please post the definition for this? I'm new to C#, but have some (very old) experience with good ol' C++...

Thanks,

- John

Hi John - we spoke directly but just in case others run into the same issue... yes TestDictionary is our own implementation of the Dictionary Class.

It's more or less the same - the "key" difference is that if you try and refer to a key that does not exist, you get a "null" value back rather than a null reference exception.

For the purposes of this example, however, you should be able to safely use the regular Dictionary Class.

MarkusT wrote:Hello Bill,

Do you want to change your data source file during Runtime?
In general, we don't recommend to change this file.

You may find the following simple example helpful that changes a specific cell of an Excel file.
The following code modifies the data source file that is stored in the output folder of your project.

Excel.Application excelDataconnectorSourceFile = new Excel.ApplicationClass();            
excelDataconnectorSourceFile.Visible = true;  
string workbookPath = System.IO.Directory.GetCurrentDirectory() + "\\ExcelFile.xlsx";  
Excel.Workbook excelWorkbook = excelDataconnectorSourceFile.Workbooks.Open(workbookPath);  
Excel.Sheets excelSheets = excelWorkbook.Worksheets;  
string currentSheet = "Sheet1";  
Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);  
excelDataconnectorSourceFile.Cells[1,4] = "Insert value during runtime";  
excelWorkbook.Save();  
excelWorkbook.Close();

I hope this example helped you to find a better understanding how it works in C#

Regards,
Markus (T)

Hi Markus - thanks for jumping in to help! Two key differences to note with what you suggested vs. what we've implemented are that we do not actually save the excel data back to the worksheet, we do all updates to the excel data in memory. The second is, we try and use the Ranorex data connectors / data caches as much as we can. Although what we're doing is not officially supported (it really should be, we've been asking for this feature ever since we started using the product), we're trying our best to work with the existing Ranorex framework.

One thing we've found is that by updating the sheet directly is that your changes won't be recognized until the NEXT iteration... if you update in memory it is recognized in the current iteration. Additionally, if you're using source control and you have a saved sheet you might run into conflicts (we ran into both of these problems with our old implementation which was similar in concept to what you outlined above).

Carson.

We've done some minor refactoring of implementation that I thought I would share with those of you who might be interested.

Some folks have been asking for examples of this fully implemented - in my original post we actually do have all the code that shows how to use the excel file with a complete implementation.

However, I can understand how it can be a bit confusing because the calling functions are actually NOT in the excel implementation, we had stored them elsewhere.

We've now moved everything into the same file, so the complete "package" is in the attachment.

Note that the TestDictionary class is NOT there, but you can just replace that with Dictionary (literally find and replace) and that SHOULD be ok.

Additionally, our Exception implementations are also not included here, but again you could just replace all of them with the generic exceptions (i.e. find and replace InternalTestException with Exception).

To use it, simply do the following:

In your test suite, make a data connector to your excel sheet. Note that the data connector name MUST be the same as the the worksheet you are using (so, if your worksheet is called Orders, then name your connector Orders). This is standard Ranorex functionality so I won't go into explaining that here, there are examples all over the place for that. You must do that for EVERY sheet you want to update in this manner - so if your excel workbook has three worksheets (i.e. tabs), then you'll need three dataconnectors, all with the same name as the sheet, above (so you may have sheets, one called Orders, one called Customers, and one called Payments or something to that effect. You would then have three connectors with the same name).

Once that's done we run the following code... honestly we put this into place over a year ago and I can't even remember why it's necessary anymore! Other people have implemented this solution without the following, but I've included it because we use it:
We run this as a code module BEFORE accessing our excel data. If we don't, it will not work for us, but I think that's because we do something funky with the datasheet location. If it's really important to people I can dig up why we use it.

To perform your updates, you just call the methods included at the bottom of the attached file:
For us, "Active Test Case" refers to the dataconnector being used by your current test. So if you're running a code module and the dataconnector associated with it is for "Orders" (in our example above), then if you run the above method it will update the "Orders" sheet.

If you need to update a different sheet (remember you MUST have a connector to that sheet), then you just run this method:
And specify the connector name.

Hopefully this makes things a little more clear... if enough people need to make use of this functionality, but find my explanation too complex, or not clear enough, then let me know and I can put together something more detailed with screenshots and the like. Or better yet, maybe I could make a small solution, with an excel file, and provide that as an example (just need to find the time to do so). Thanks!

Carson.

Hello Carson,

Thank you for posting your solution and providing additional information.
This would be a great help for other users.

Regards,
Markus (T)

MarkusT wrote:Hello Bill,

Do you want to change your data source file during Runtime?
In general, we don't recommend to change this file.

You may find the following simple example helpful that changes a specific cell of an Excel file.
The following code modifies the data source file that is stored in the output folder of your project.

Excel.Application excelDataconnectorSourceFile = new Excel.ApplicationClass();            
excelDataconnectorSourceFile.Visible = true;  
string workbookPath = System.IO.Directory.GetCurrentDirectory() + "\\ExcelFile.xlsx";  
Excel.Workbook excelWorkbook = excelDataconnectorSourceFile.Workbooks.Open(workbookPath);  
Excel.Sheets excelSheets = excelWorkbook.Worksheets;  
string currentSheet = "Sheet1";  
Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);  
excelDataconnectorSourceFile.Cells[1,4] = "Insert value during runtime";  
excelWorkbook.Save();  
excelWorkbook.Close();

I hope this example helped you to find a better understanding how it works in C#

Regards,
Markus (T)

I'm currently working on a project to collect text/ numbers from a table in html and store them in a spreadsheet under the appropriately-labeled columns.I want to store the date in a (excel) spreadsheet permanently. The purpose of the solution is to store the data points in a spreadsheet and be able to share that file in a local network folder, where it could be viewed and compared to the previous results of the same test from the same site/ system with the same data.

If you can, please tell me how to incorporate the mentioned solution (download/file.php?id=2021) into the recording module I've attached. By that, I mean what folder do I put the file in?; where to I insert the variables?; from what module to I connect the variables to the excel sheet?; etc.

I say this because Carson's solution does not save the data back to the sheet permanently, it only updates the values of the sheet in memory during runtime.