Print

Writing To Excel In Robot Framework

In some cases, users may need to write some test data or test result data into Excel Spreadsheet. Although there is robotframework-excellibrary available for use, it is not easy to manipulate Excel file with provided keywords. In this article, I explain how to use xlsxwriter python package to write to Excel File in Robot Framework. XlsxWriter is a Python module that can be used to write text, numbers, formulas and hyperlinks to multiple worksheets in an Excel 2007+ XLSX file.

Here is a comprehensive list of tools for working with Python and Excel. It covers writing Excel Add-Ins in Python, reading and writing Excel files, and interacting with Excel. It's a great resource for understanding the differences between all the different Python/Excel tools out there.
 Here's the page: https://www.pyxll.com/blog/tools-for-working-with-excel-and-python/
 

 

Here is the implementation idea.

1. Install XlsxWriter Python  package in your computer that runs robot framework;

2. Create a custom python class to write to excel file;

3. Add the custom python file as a resource in the robot framework test suite;

4. Create a list with row, column, and content in the robot framework;

5. Using the custom python file to write the content to an Excel File (see the picture below for the excel file test3.xlsx);

Picture 1: robot framework resource and library

Picture 2: robot framework test case steps

Picture 3: robot framework test result

Code Implementation

Note: after writing code for "CreateExcelFile.py", you need to save it in the robot framework test suite folder.

Robot Framework Ride Code

*** Settings ***
Resource          CreateExcelFile.py
Library           Collections
Library           CreateExcelFile.ExcelUtility

*** Variables ***

*** Test Cases ***
Write Excel Test
    [Tags]
    @{content}    Create List
    Append To List    ${content}    1    1    Test Case 1
    Append To List    ${content}    2    1    Test Case 2
    Append To List    ${content}    3    1    Test Case 3
    Append To List    ${content}    4    1    Test Case 4
    Write To Excel File    test3.xlsx    ${content}

Run the test and see the result. The test passed. "test3.xlsx" file is created. 

Robotexample.Excel Test.Excel Suite                                                                                           | PASS |
1 critical test, 1 passed, 0 failed
1 test total, 1 passed, 0 failed
======================================================================================================================================
Robotexample.Excel Test                                                                                                       | PASS |
1 critical test, 1 passed, 0 failed
1 test total, 1 passed, 0 failed
======================================================================================================================================
Robotexample                                                                                                                  | PASS |
1 critical test, 1 passed, 0 failed
1 test total, 1 passed, 0 failed
======================================================================================================================================

CreateExcelFile.py

import xlsxwriter

class ExcelUtility(object):

    def __init__(self):
        print "write to excel file"
    
    def group(self,lst, n):
        """group([0,3,4,10,2,3], 2) => [(0,3), (4,10), (2,3)]
        
        Group a list into consecutive n-tuples. Incomplete tuples are
        discarded e.g.
        
        >>> group(range(10), 3)
        [(0, 1, 2), (3, 4, 5), (6, 7, 8)]
        """
        return zip(*[lst[i::n] for i in range(n)])
    def write_to_excel_file(self,filename,content_list):
                
            # Create an new Excel file and add a worksheet.
            workbook = xlsxwriter.Workbook(filename)
            worksheet = workbook.add_worksheet()

            #content_list=[1,1,'hello',2,1,'brother',3,1,'how are you',4,1,'are you good today']
            t=self.group(content_list,3)
            for item in t:
                worksheet.write(int(item[0]), int(item[1]), item[2])
                                                                

            # close work book
            workbook.close()