Read/Write excel sheet using OpenXML sdk

Back after such a long time. But with some useful information.

Recently was exploring OpenXML sdk. 

Its available as NuGet package. And can be installed directly from Visual studio package manager console. 

I was trying to resolve a problem to update a cell by identifying its location based on the keyName. I found many solution on web to read and update any cell value by passing the exact cell reference.

But I just wanted to pass a keyName to identify the cell and update its corresponding value in sheet. I just wanted to pass Name of the test and then read or update the value.

One thing which we need to keep in mind is that when you are trying to read a particular value it has to be unique like my test names. If not then the sdk will pick up the first matched text.

Example

I created a simple console application to pass the required data to the following helper method

Code

Now lets focus on each line an options :

using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(filename, true))   

The parameter 'true' or 'false' decides if document is being opened for editing it. 
If you just want to read values from document just pass it as 'false'.

var sheet = workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == "Results");
The above line is just to find the sheet in the document. Now here is a common sense trick to read large document. If you want to read a large document with several worksheets in it then pass this as parameter to this method and iterate through it. 

As the SDK also uses the .NET Framework Language-Integrated Query (LINQ) technology to provide strongly typed object access to the XML content inside the parts of Open XML documents, its very easy to access the inner elements and update them if you need to.

So next few lines are trying to get to the cell level.
 var workSheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
 var sharedStringPart = workbookPart.SharedStringTablePart;
 var values = sharedStringPart.SharedStringTable.Elements<SharedStringItem>().ToArray();
 var cells = workSheetPart.Worksheet.Descendants<Cell>();


Now we need to loop through cells to find the value we are looking for.
Now cell can have text values, numbers or formula. So if cell contains a string input which is not formula then we are doing following task:


 if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
     {
          var index = int.Parse(cell.CellValue.Text);

           if (values[index].InnerText == keyName)
                {
                  var nextCell = cell.NextSibling<Cell>();
                  keyValue = nextCell.InnerText;
                  return keyValue;
                }
     }


If you want to read numbers then
 if (cell.CellValue != null && cell.CellValue.Text == keyName)
     {
         var nextCell = cell.NextSibling<Cell>();
         keyValue = nextCell.InnerText;
        return keyValue;
     }


Now the next one which I have not highlighted in the above code( As I was just trying to read keyValue) is to get the formula applied on any cell.
if (cell.CellFormula != null)

  {

     Console.WriteLine(cell.CellFormula.Text);                   

  }

Now coming back to my example

Have a look at NextSibling method. It is used to traverse through columns if you have identified your row based on the keyName you passed.
var nextCell = cell.NextSibling<Cell>();

keyValue = nextCell.InnerText;

So if I will pass Test1 as keyName then nextCell will give me cell B2 and then nextCell.InnerText will return 10.

What if I want to access the actual value Cells (C2) ? Here it is, a basic LINQ concept:
var nextCell = cell.NextSibling().NextSibling<Cell>();
keyValue = nextCell.InnerText;

Now this is all about reading cell values. Lets see how to update cell value

Updating a cell Value
First of all the sheet need to be opened in editable mode as explained above.

Here I have tried updating the value of C2 cell to newValue. As this is numerical value so have used CellValues.Number.


if (cell.CellValue != null && cell.CellValue.Text == keyValue)
   {
      var nextCell = cell.NextSibling().NextSibling<Cell>();
      nextCell.CellValue = new CellValue(newValue);
      nextCell.DataType = new EnumValue<CellValues>(CellValues.Number);
      workbookPart.Workbook.Save();
   }


If you want to update text value then

nextCell.DataType = new EnumValue<CellValues>(CellValues.SharedString);

CellValues has got enums like Boolean, Number, Error, SharedString, String, InlineString, Date.


And finally don't forget to Save the Workbook.

Sample Code:

        /// <summary>
        /// Reads the excel.
        /// </summary>
        /// <param name="keyName">Name of the key.</param>
        /// <returns>Key Value</returns>
        public string ReadExcel(string keyName)
        {
            string filename = ConfigurationManager.AppSettings.Get("C:/Logfile.xlsx");
            string keyValue = string.Empty;
            using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(filename, false))
            {
                var workbookPart = spreadSheetDocument.WorkbookPart;
                var sheet = workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == "Results");

                if (sheet == null)
                {
                    throw new InvalidOperationException("Unable to find the Results sheet");
                }

                var workSheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
                var sharedStringPart = workbookPart.SharedStringTablePart;
                var values = sharedStringPart.SharedStringTable.Elements<SharedStringItem>().ToArray();
                var cells = workSheetPart.Worksheet.Descendants<Cell>();
                foreach (var cell in cells)
                {
                    if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
                    {
                        var index = int.Parse(cell.CellValue.Text);

                        if (values[index].InnerText == keyName)
                        {
                            var nextCell = cell.NextSibling<Cell>();
                            keyValue = nextCell.InnerText;
                            return keyValue;
                        }
                    }

                    if (cell.CellValue != null && cell.CellValue.Text == keyName)
                    {
                        var nextCell = cell.NextSibling<Cell>();
                        keyValue = nextCell.InnerText;
                        return keyValue;
                    }
                }

                return keyValue;
            }
        }

If you want to read further about this please follow below links:

1. http://msdn.microsoft.com/en-us/library/office/documentformat.openxml.aspx

2. http://www.microsoft.com/en-us/download/details.aspx?id=5124

Selecting a value from dropdown using selenium webdriver 2.0 and Fluent Automation API

Hello

While using selenium webdriver i found out that there is no straight forward method to select values from dropdown.Then i found out below solution to resolve this issue:

First identify the dropdown element by its Id or name etc. Then using SelectElement method we can directly select the  dropdown element using text as below:


 IWebElement expiryMonth = Driver.FindElement(By.Id("CreditCard_ExpiryDate_Month"));
 SelectElement selectExpiryMonth = new SelectElement(expiryMonth);
 selectExpiryMonth.SelectByText("December");

The above operation is very easy in fluent automation. As name suggests its very fluent :-)

I.Select("2007").From("#PassedOut);

Where 2007 is the value in the dropdown and #PassedOut is the id of the dropdown field. We can use class name identifier as well. So three lines of code can be done in single line using fluent automation.

We can even make use of jQuery selectors to identify the elements. Chrome provides a nice support to test you jQuery selectors under watch expressions tab (under Sources) in developers mode. 

Thanks
Nishant

Passing data between steps in Specflow

Hi All

Specflow - which brings developer and business domain expert on the same platform and provides the base of Behavior driven development(BDD). SpecFlow also supports the concepts of Acceptance Test Driven Development (ATDD) and Behavior Driven Development (BDD), which are often used synonymously with Specification-By-Example.

While working on the complex scenarios i had to generate the data randomly and use them for next step. Using session variables are obviously not a choice, as it won't be available inside your sepcflow test project(ofcourse System.Web.HttpContext.Current will be null). So i used something called ScenarioContext. It comes with Specflow. ScenarioContext is a class that stores information relative to the state of the current scenario. One of its greatest uses is to pass values between steps. Please note that I am specifically talking about Scenario, not feature. So the ScenarioContext is not valid outside the scope of scenario.

Just like session variables, it is quite easy to use. You need to store the variable in current context and you can retrieve the value later in some other step wherever its required. 

ScenarioContext.Current.Add("name",firstName);

Now "name" can be retrieved in the current context / any of the step definition

var name = ScenarioContext.Current.Get<string>("name");

We can make use to generics as well. So nice and simple, give a try and reuse the steps. Hope it helps

Thanks 

Nishant

Simplest possible key/value pair file parsing in .NET

Hi All

Recently i was working on a requirement to pick a value from config file, in order to make the application more flexible. The one commonly used method is to either use the .config file or else go for XML. I cannot dump whole data into .config so i ruled out that option and i don't want to go for the same old XML parsing nodes technique. So i browsed and came across "Nini" (not sure how they got this name :-)). Yeah it is simple to use.


You can define your own file with just a simple syntax of pair key value.Something like :

[Configuration]

Name = Nishant Kumar 

Phone = +44-0123456789

And then you can read the keys as below :

var source = new IniConfigSource ("Settings.ini"); 

var config = source.Configs ["Configuration"]; 

string name = config.Get ("Name"); 

string phone = config.Get ("Phone");

So looks quite simple,Obviously, you need to have a reference to the dll's of Nini. This can be downloaded from

http://sourceforge.net/projects/nini/files/latest/download?source=files

Hope it helps.

Thanks

Nishant