MS Dynamics CRM – Automation Testing: Writing Data to Excel File – Reporting

During Automation Framework Development, we often need to create excel reports to share them with a client. As excel reports are straight forward and easy to understand and interpret, using them for Automation Testing is a common practice.

Writing Test Case Status along with Status of Steps, is a rather simple task in Excel because we can add value in excel reporting by integrating the images captured through the Automation Framework. However, Automation Testing is relatively tricky, and our focus is on achieving it successfully in this blog.

Writing Data to Excel file involves different steps. Each of these steps is described below:

Step 1: Create an Excel File: Create an Excel File and add it to the existing project where reporting is required. A section of the project can easily be defined. For the purpose of this blog, we have formatted the Excel File in a way that it contains the Test Case ID/ Name, Execution Status and Screenshots in a row and have named the sheet as “TestResult” & Column names as “TestCaseID” and “TestStatus”.

Step 2: Create Helping Functions:

Write Data to Excel:

If the Cell has a Picture

Step 3: Insert Image Function:

Step 4: Write the Excel Image:

Step 5: Delete Report Data: Whenever the Test Suite is executed, it appends the old test execution status with the new one. So, in order to resolve this problem, we need to Delete the Report data before the most recent execution. Following is the function that would empty the Excel Report excluding the headers:

Step 6: Copy Excel File: As the report needs to be saved with a unique name, this Excel report can be copied with the desired file name, and the Report Data can be deleted later. This way, the next execution cycle can make changes to the base report accordingly.

The following function is used to copy the Excel report:

Reading and Writing in Excel might be tricky as we use complex data and traverse through thousands of records. For instance, in CRM portals, we must fetch information from thousands of records, which requires navigation between different excel sheets to fetch the records. The above-given functions will be very handy for such cases.

Note: There need to be certain references to be imported and referenced as part of the implementation process.

There you have it! This blog aimed to help you understand how to write test cases in Automation Testing! I hope this blog has brought clarity to the process. If you have any insights, questions, or queries, please leave a comment below!