Excel Methods - ChrisMell/OEL-wiki GitHub Wiki

ExcelMethods Overview

The ExcelMethods class contains a series of methods that handle basic data operations with excel workbooks.

(Seen in the main view port, is a portion of the ExcelMethods class)

The methods and their functions are listed below

writeToExcel - takes a map and writes the data from the map to an excel workbook, which it saves at the remote location specified in the output.properties file.

readFromExcel – given a file name, this method will locate the file at the remote location, configured in the output.properties file, and return the file’s data, in the form of a tree map.

readFromExcelAtPath – Functionality is the same as readFromExcel, except that it takes as an additional parameter, a location of the file to read that the tester specifies.

updateExcel – given a map containing the data, this method will take as its second parameter, the file name, and overwrite the existing file, with the new data.

(Presented above is an instance of the updateExcel method used within another method.)

Using writeToExcel

After running a SQL query and storing the results in a result set, there are three main components to this process:

• Extract the results
• Create a map containing the data from the result set
• Convert the map into an excel file that is saved to a specified location

Extract the results

Upon successfully retrieving data from the database, the rows containing the values to work with are stored in a result set (ResultSet resultSet). You can then iterate over the rows contained in the result set by the use of the .next() method.

For this demonstration, a single row is required, permitting the tester to use an ‘if’ statement to iterate to the next row in the result set. However, for multiple rows, placing resultSet.next() within the condition clause of a while loop will allow the tester to iterate through multiple rows.

Once arrived at the appropriate row, the tester can retrieve values from the 'resultset' variable, using any of its "getter" methods, and store them in variables and/or arrays, in preparation for the next step.

Create a map from the result Set

After capturing the values that you wish to write to your excel file, the next step is to transfer them into a map. To do this, place the captured values into object arrays, and use the .put() method of the Map class, to add the object arrays to the map; one object array representing one row of data; one map key corresponding to one row at a time.

Write to excel file

After adding all rows of data (object arrays), to the map, the tester may now parse the map into the writeToExcel() method. Invoke the writeToExcel() method, parse the data map into it, along with the name that you would like to give the file, and finally wrap it in a try-catch block to accommodate file-handling exceptions.

When the three sections above are combined, the result is a test step definition that retrieves some values from the database, creates a map containing the data, and saves the data into an excel file, located in the place of the tester’s choosing:

Using the updateExcel() method

Using the updateExcel() method is the same as the writeToExcel() method; the difference being that writeToExcel() creates a new file, while the updateExcel() method replaces the information in an existing file with new data, specified by the user.

Note: Rather than adding new data to an existing file, this method replaces all existing data with the user-provided new map of data. As a result, this method is normally used in conjunction with other data handling methods, to fulfil the “update” functionality required by the test case.

An example of this implementation is seen in the addElementTableDataToExcel() method, found in the CommonDataHandlerMethods class, which updates the file with additional columns and rows of data, taken from a table in the web-app ui.

(The screen capture above shows the updateExcel() method being used within another method called 'addElementTableDataToExcel()')