MyTechFinds.com

Managing test cases in Excel spredsheets is the most usual practice in test management. We all know the beauty and power that Excel provides. But, with Visual Studio Team System 2008, things are a little different. VSTS as it is called, uses a different file format for managing manual tests and also another major difference is, every manual test is a separate .mht file.

I was faced with this situation of converting all my excel based test cases into VSTS compatible manual test cases. I had around 500+ tests and converting them into VSTS manually would have probably taken more time than to develop them afresh.

I quickly pulled out a new C# project to do this task. It is pretty crude but works! and saved me days of manual work.

Solution

The solution is nothing but, data manipulation. I knew how my excel spreadsheet lays out test cases and what output format of VSTS manual test case. Read one line from Excel and spit out the data in MS Word saving the file with .mht extension. Simple right!

The excel looks like -

It really did not matter to me that my "generated" VSTS manual test cases fit exactly to the format shown in their sample. I used my own template (base) file which I will fill in with data read from the excel to create a new .mht file.

I have used a simple console application for this utility. You are going to need following references to be added to the project, mainly to work with Excel and Word.

• Microsoft Office 12.0 Object Library (COM)
• Microsoft.Office.Interop.Excel (.NET)
• Microsoft.Office.Interop.Word (.NET)
• Microsoft Visual Basic for Applications Extensibility 5.3 (COM)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using Microsoft.Office.Core;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop.Word;
using System.Reflection;

Now, I have everything in single Main function, but you can always be a little smarter to split the code. I just wanted to put it to use as soon as I could :)

So, what I am doing here is, getting hold of the excel file first. I am going to dump all the files in a folder my local machine at "C:\Manual Tests".

Note: the last line in the below snippet is the most important. This is where you specify which "sheet" from the excel file you want to read. In this case is it the first one so specify get_Item(1). You can also put a bigger loop around to go through all the sheets in your excel file. I wanted to play safe!

namespace ConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            object oMissing = System.Reflection.Missing.Value;
            string baseDir = "C:\\Manual Tests"; 
  
  // Create a excel object to read
            Microsoft.Office.Interop.Excel.Application ExcelObj = null;
            ExcelObj = new Microsoft.Office.Interop.Excel.Application();
            Workbook theWorkbook = ExcelObj.Workbooks.Open("C:\\VSTSTests.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            Sheets sheets = theWorkbook.Worksheets;
            ////change the sheet number as applicable
            Worksheet worksheet = (Worksheet)sheets.get_Item(1);

As I mentioned earlier, I am using a template.mht to generate me test cases. I have placed the template.mht in the same folder where I want to dump the generated test cases. My template.mht is an "empty" word file with mht extension. You can have different sections in your template like Description, Title, Stetps, Expected results etc. and then fill the file accordingly. But that will need some more coding and I am not doing that here.

The only reason to create a .mht template is to give the output test case file its properties. For example, when you import any manual test case file with .mht extension in VSTS manual test project, the "Description" of that test case is read/populated from the "Title" property of that file. Why?, that's the way it is!

        
            DirectoryInfo rootDir = new DirectoryInfo(baseDir);
            FileInfo[] baseFile = rootDir.GetFiles("template.mht");

After having the hold of the excel file and the specific sheet, you have to define the ranage, the number of columns and rows to be read. The first for loop does just that. It is going to read from row 3rd till row 92nd for columns A to F.

While I read from this range, I also start splitting out the read data into separate .mht files. First I make a copy of template.mht, then open it for writing. I am using the sheet name + first column value to specify the name of the file. I even apply a font.

Once I have the newly made .mht file open, I write the column values one after the other from the current read row into the file. Here you can do all the formatting you wish for the output test case. Like, I am putting some extra text "Priority:" and "Method:" for those specific field values.

Once you are done dumping all the data, just save and close the file. If you import this file into VSTS, it would not show up "Description" in VSTS .vsmdi as we have not yet set it. Having the description helps me as my file names are not very descritpive.           

            for(int x = 3; x <= 92; x++)
            {
                Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A"+x.ToString(), "F" + x.ToString());
                Object[,] myvalues = (Object[,])range.Value2;

                Microsoft.Office.Interop.Word.Application WordObj = null;
                WordObj = new Microsoft.Office.Interop.Word.Application();
                FileInfo cp = baseFile[0].CopyTo(baseDir + "\\"+ worksheet.Name + myvalues[1, 1].ToString()+".mht",true);
                Object fileName = (Object)cp.FullName;
                Document newTest = WordObj.Documents.Open(ref fileName, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing,ref oMissing,ref oMissing,ref oMissing,ref oMissing,ref oMissing,ref oMissing,ref oMissing,ref oMissing,ref oMissing,ref oMissing);
                Paragraph para = newTest.Paragraphs.Add(ref oMissing);
                para.Range.Font.Name = "Calibri";
                //System.Console.Write(para.Range.Font.Name.ToString());
                for (int i = 2; i <= 6; i++)
                {
                    
                    if (myvalues[1, i] != null)
                    {
                        if (i == 5)
                        {
                            para.Range.Text = "Priority: " + myvalues[1, i].ToString() + "\r";
                        }
                        else if (i == 6)
                        {
                            para.Range.Text = "Method: " + myvalues[1, i].ToString() + "()\r";
                        }
                        else 
                        { para.Range.Text = myvalues[1, i].ToString() + "\r"; }
                        para.Range.InsertParagraphAfter();
                    }
                    
                }
                newTest.Save();

Once the new .mht test case is ready, I hold it one more time. This time to update the "Title" property of the file as I know that is going reflect as "Description" in VSTS. One the way, I also set the "Author" property.

                object builtInProps = newTest.BuiltInDocumentProperties;
                Type typeDocBuiltInProps = builtInProps.GetType();
                //Set the Subject property.
                String strIndex = "Author";
                object oDocAuthorProp = typeDocBuiltInProps.InvokeMember("Item", BindingFlags.Default | BindingFlags.GetProperty, null, builtInProps, new object[] { strIndex });
                Type typeDocAuthorProp = oDocAuthorProp.GetType();
                strIndex = "Title";
                String strValue  = "";
                
      // set the title equal to second column which is test case title.      
                String strValue = myvalues[1, 2].ToString();
                typeDocAuthorProp.InvokeMember("Item", BindingFlags.Default | BindingFlags.SetProperty, null, builtInProps, new object[] { strIndex, strValue });
                               
                newTest.Close(ref oMissing, ref oMissing, ref oMissing);
            }
            theWorkbook.Close(Type.Missing,Type.Missing,Type.Missing);
            
        }
    }    
}

What happens at the end of this code? You get a bunch of .mht files which are equal to the number of test cases you have in your excel spreadsheet with their titles set correctly. Now, all you have to do is, open your VSTS project and import these file.