JAVA Code to Create an Excel File in Relative Path

Using excel files is one of the most common method for test data and other types of data management. The java code to create an Excel file is very easy and most of the code is self explanatory. Lets have a look at the same:
 
Eclipse needs to be pointed to the required JAR files in order to make it work with various functions provided by apache. Read more on Apache at it’s official website: Apache POI

 

The poi(s) need to be added to the project before running this code. Different versions may or may not support all functions and need to be worked with accordingly. Below is the snapshot of POI(s) used in my program. They can be downloaded from https://poi.apache.org/download.html#archive

Eclipse - Java Build Path
Eclipse-Java Build Path

 

Below is the JAVA class which creates an excel file in the “Relative Path”.

The code first checks for the directory “temp” at the relative path and creates if it does not exists.

Next, the excel file with three columns namely – “First Name”, “Last Name” and “Country”. If the file already exists, the code overwrites the same.

The code prints following on console when run the first time:
Directory already exists!
relativePath:tempMyExcelFile.xlsx
Excel File created: C:FunctionalTestAutomationproject_TestProject1tempMyExcelFile.xlsx
When we run the code second time, the console is as below:
Directory already exists!
relativePath:tempMyExcelFile.xlsx
Excel File created: C:FunctionalTestAutomationproject_TestProject1tempMyExcelFile.xlsx

Tada! That was one of the ways to write JAVA code and create an Excel file at the relative path. Hope it helped.

Apache POIXML Exception: InvalidFormatException:Package should contain a content type part [M1.13]

Problem:
I received following error while using “Apache POI(XSSF API)” for reading xlsx file”.
Exception occurred during playback of script [TC002_Login] [CRFCN0019E: RationalTestScriptException on line 45 of script TC002_Login – org.apache.poi.POIXMLException: org.apache.poi.openxml4j.exceptions.InvalidFormatException: Package should contain a content type part [M1.13].].
Below is the error displayed in log file:
  • exception_name = org.apache.poi.POIXMLException
  • exception_message = org.apache.poi.openxml4j.exceptions.InvalidFormatException: Package should contain a content type part [M1.13]
  • script_name = TC002_Login
  • script_id = TC002_Login.java
  • line_number = 45
exception_stack = at org.apache.poi.util.PackageHelper.open(PackageHelper.java:41)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.(XSSFWorkbook.java:204)
at SuperHelperClass.SuperScriptHelperlibrary.updateExcelReportScriptname(SuperScriptHelperlibrary.java:1018)
at TC002_Login.testMain(TC002_Login.java:45)

at org.eclipse.equinox.internal.app.EclipseAppContainer.callMethodWithException(EclipseAppContainer.java:587)
at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:198)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:134)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:104)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:380)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:235)
at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java:648)
at org.eclipse.equinox.launcher.Main.basicRun(Main.java:603)
at org.eclipse.equinox.launcher.Main.run(Main.java:1465)
at org.eclipse.equinox.launcher.Main.main(Main.java:1438)
at org.eclipse.core.launcher.Main.main(Main.java:34)
at org.apache.poi.openxml4j.opc.ZipPackage.getPartsImpl(ZipPackage.java:178)
at org.apache.poi.openxml4j.opc.OPCPackage.getParts(OPCPackage.java:662)
at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:269)
at org.apache.poi.util.PackageHelper.open(PackageHelper.java:39)
Line number 45 read:
updateExcelReportScriptname(DestReport); //DestReport is a string variable with path+filename
The function ‘updateExcelReportScriptname’ is defined as below:
public void updateExcelReportScriptname(String DestReport)
{
                        FileInputStream file;
                        try {
                                    file = new FileInputStream(DestReport);
                                    XSSFWorkbook workbook;
                                    try {
                                                workbook = new XSSFWorkbook(file); //Line NUmber 1018
                                                XSSFSheet mySheet1 = workbook.getSheetAt(0);
                                                XSSFRow myRow1 = null;
                                                XSSFCell myCell1 = null;
                                                XSSFCellStyle style = workbook.createCellStyle();
                                                style.setAlignment(CellStyle.ALIGN_JUSTIFY);
                                                style.setFillPattern(CellStyle.SOLID_FOREGROUND);
                                                style.setBorderBottom(CellStyle.BORDER_THIN);
                                                style.setBorderTop(CellStyle.BORDER_THIN);
                                                style.setBorderRight(CellStyle.BORDER_THIN);
                                                style.setBorderLeft(CellStyle.BORDER_THIN);
                                                style.setWrapText(true);
                                                String excelData1[][] = new String[500][500];
                                                int row = mySheet1.getPhysicalNumberOfRows();
                                                excelData1[row][0] = getScriptName();
                                                myRow1 = mySheet1.createRow(row);
                                                myCell1 = myRow1.createCell(0);
                                                myCell1.setCellValue(excelData1[row][0]);
                                                style.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
                                                myCell1.setCellStyle(style);
                                                for(int cellNum = 1; cellNum <= 19; cellNum++)
                                                {
                                                            XSSFCell myCell = null; 
                                                            myCell = myRow1.createCell(cellNum);
                                                            myCell.setCellValue(“”);
                                                }
                                                file.close();       
                                                FileOutputStream outFile =new FileOutputStream(DestReport); 
                                                workbook.write(outFile);  
                                                outFile.close();
                                    } catch (IOException e) {
                                                // TODO Auto-generated catch block
                                                e.printStackTrace();
                                    }   
                        } catch (FileNotFoundException e) {
                                    // TODO Auto-generated catch block
                                    e.printStackTrace();
                        }
            }
Solution:
There are two reasons I could analyse which may cause this error:
1. When I try to read ‘.xls’ file with my code and for ‘.xlsx’ files. There are different classes for xls and xlsx files namely:
Classes to be used for XLS file: HSSFWorkbook & HSSFSheet
Classes to be used for XLSX file: XSSFSheet & XSSFSheet
Required one must be used accordingly.
2. Sometimes, the same error is thrown even when the correct file format is used. The reason for this could be any of the below:
  • File is password protected
  • File is corrupted. For me, this was the case and I realized it when I tried to open the file and got bellow error. 

Microsoft Excel Error for Corrupt File
 Hope this helps solve the problem. 

Find elements of a dropdown list: RFT/Rational Functional Tester


To display all elements of a drop down list on the console, following code may be used:
Find out the id/name property of the required drop down list and replace the same in line 2.
Also remember, if name property is used, do not forget to replace “.id” by “.name”.

RootTestObject root1 = getRootTestObject();

TestObject[] to = root1.find(atDescendant(“.class”, “Html.SELECT”, “.id”, “Id_Of_List_Or_Dropdown”));

System.out.println(to[0].getClass());

SelectGuiSubitemTestObject object_for_select = ((SelectGuiSubitemTestObject) to[0]);

ITestDataList dataList = (ITestDataList)object_for_select.getTestData(“list”);

ITestDataElementList elementList = (ITestDataElementList)dataList.getElements();

for(int i = 0; i

{

 This statement prints the Items of the Listbox or combo box.

         System.out.println(“Item Index  “+i+” “+elementList.getElement(i).getElement().toString());

}

RST/RPT – RPTK1017E – Error Message: Error attempting to acquire licenses for test run.

While i was creating my scripts on RST – SOA extension, i faced below error.

Even after reinstalling RPT, the issue was not resolved.

Finally, the issue was resolved after installing
-Instal ‘License Key Administrator 8.1.3’
-Map the  tool to license server and i was done.





Life Cycle of Functional Test Automation

Based on my experience,here is the life cycle of Functional Test Automation as per my understanding.

  1. Check if the automation tool to be used supports the application to be tested.
  2. Identify the scenarios to be tested.
    • The test scenarios identified should be defined in the form of actions (QTP)/scripts (RFT).
  3. Object Identification/Recognition
    • Any tool understands and identified objects on an application via a feature commonly called as object repository.
    • For e.g., QTP has OR (object repository) which identifies objects by class name, text, parent name etc. properties.
    • RFT has Object Map to identify the objects
  4. Object Map
    • Decide which Object Map has to be used; public object map or private Object Map has to be used.
    • In RFT perspective,
Public Object Map
Private Object Map
Its objects are accessible to all the scripts within a project.
Its objects are accessible only to a particular script.
    • If Public map is to be used, a map file must be created.
  1. Create Script
    • A script consists of Steps, Data and verification.
    • Points to be taken care while recording a script
                                                               i.      Do not open any other application while recording.
                                                             ii.      Perform only relevant actions.
                                                            iii.      When finished, come back to the initial step. For eg, if the script opens a browser, it must close it at the end of script.
  1. Play back
    • Playback the recorded script to see if it works fine and only required actions are recorded.
  2. Synchronization
    • It refers to waiting till a defined condition is satisfied.
    • In QTP -> waitforproperty().
    • In RFT -> waitforesistance().
  3. Verification
    • Refers to mentioning the expected data or a property value.
    • Continuation or failing of a script on a failed verification point depends upon the scenario.
  4. Load Functional Libraries
    • Normally, there are two types of function libraries
                                                               i.      General – It includes the functions which may be used across projects – like Functions on excel files.
                                                             ii.      Application Specific – It includes the functions which cannot be used across projects and are specific to the applications
  1. Create data – data driven
    • If verifications points run successfully, a data pool must be created.
  2. Output Value Identification
    • It is necessary for reporting or scenarios where output of one step or script may be required as an input to other step/script.
  3. Exception Handling/Recovery Scenarios
  4. Run
  5. Batch Run
  6. Defect Tracking

Load Runner(LR) – Error: “-27751: Step download timeout (120 seconds) has expired when downloading resource(s)…”

If step download time error is coming after few iterations that means your application is actually taking this much time to process it. 
To resolve the issue, try following
  • Go to Run-Time settings,
  • Go to Internet Protocol -> Preferences and select the “Options” button,
  • Change the “Step DownLoad Timeout (secs)” from the default value of 120 seconds to the desired value.
Note that in the Run-Time Setting dialog, the maximum limit for step download timeout is 32000 seconds. To specify an increased limit, use the web_set_timeout() function as follows:
  • web_set_timeout(STEP, “xx”); //for regular web V user
  • web.set_timeout(STEP, “xx”); //for regular Java / JavaScript V user
The Step Download Timeout encompasses all requests made from a single Load Runner statement. For example, a step in the script may consist of a single request to the server, or may consist of 10 requests. For example an HTML mode web_URL request to WWW.Google.com would consist of an initial request to the Google server for the main HTML file. After parsing this HTML file, further additional resources will be downloaded (e.g. .gif files). In HTML mode, these resources are downloaded automatically as a part of the web_URL for WWW.Google.com. Thus, the Page Download Timeout will encompass all the requests, that is, the initial request and the requests arising from any related resources).
 

Common Myths/Misconceptions about Automation Testing

Software Test Automation is often associated with some common misconceptions and myths. These myths generally lead to Failure of automation processes:-

  • It’s magic viel
    • Time is an important factor when it comes to automation. Management want all the test cases to be automated asap  whici ofteen lead to unrealistic expectations. Time is required to be invested to develop framework and a basic approach which will work in long run.
  • Immediate Test Effort Reduction
    • We must understand that the introduction of automated test tools will not immediately reduce the test effort.
  •  Immediate Reduction in Schedule.
    • An automated test tool will not immediately minimize the testing schedule.
    • Once an automated testing process has been established and effectively implemented, the project can expect to experience gains in productivity and turnaround time that have a positive effect on schedule and cost.
  • Universal Application of AST.
    • Not all tests can be automated.
  • 100% Test Coverage.
    • Even with automation, not everything can be tested.
  •  Equating Capture/Playback to AST.
    • Hitting a Record button doesn’t produce an effective automated script.
  • Cost benifits.
    • Very early returns from automation are expected, which cannot be achieved initially. It is assumed that, now we have automated 90% of the test cases which is running well in the system, lets cut the cost of few resources. What if something changes in the application which is very likely (otherwise – application is dead)which require changes in the script? here comes maintenance of the scripts which is always thrown as question to the automation engineer as – why we need to change this , it was working earlier?

    Automation Framework: Keyword Driven Framework

    Keyword Driven framework is also known as table-driven or action-word driven framework. In this framework, an application map is defined including it’s objects, components, actions and an expected value for each component. It requires very deep knowledge of applications and automation testing expertise.

    Keyword-driven tests look very similar to manual test cases. In a keyword-driven test, the functionality of the application-under-test is documented in a table as well as in step-by-step instructions for each test. In this method, the entire process is data-driven, including functionality.

    Merits

    • The Detail Test Plan can be written in Spreadsheet format containing all input and verification data.
    • Application, language, tool independent.
    • Takes time, but most successful in long run.
     Demerits 

    •  The Development of “customized” Functions and Utilities requires proficiency in the tool’s Scripting language.
    • Takes lot of time iitially.

    Automation Framework: Data Driven Framework

    Data Driven Framework is a systematic approach to test an application. In data-driven framework test input and output values are read/write from/to data files (Mostly Excel files) and are loaded into variables in captured or manually coded scripts. In this framework, variables are used for both input values and output verification values. Navigation through the program, reading of the data files, and logging of test status and information are all coded in the test script.

    Merits of Data Driven Framework:-

    • Scripts may be developed while application development is still in progress.
    • Utilizing a modular design, and using files or records to both input and verify data, reduces redundancy and duplication of effort in creating automated test scripts.
    • If functionality changes, only the specific “Business Function” script needs to be updated.
    • Data input/output and expected results are stored as easily maintainable text records.
    • Functions return “TRUE” or “FALSE” values to the calling script, rather than aborting, allowing for more effective error handling, and increasing the robustness of the test scripts. This, along with a well-designed “recovery” routine, enables “unattended” execution of test scripts.

    Demerits of Data Driven Framework:-

    • Requires proficiency in the scripting language used by the tool (technical personnel).
    • Multiple data-files are required for each Test Case. There may be any number of data-inputs and verifications required, depending on how many different screens are accessed. This usually requires data-files to be kept in separate directories by Test Case.
    • Tester must not only maintain the Detail Test Plan with specific data, but must also re-enter this data in the various required data-files
    • If a simple “text editor” such as Notepad is used to create and maintain the data-files, careful attention must be paid to the format required by the scripts/functions that process the files, or script-processing errors will occur due to data-file format and/or content being incorrect

    Automation Framework: Modular Automation Framework

    A Modular Automation Testing Framework is also one of the most basic type of automation framework. In Modular Automation Framework, each business module/functionality is separated out and handled independently. It is also known as ‘Test Script Modularity Framework’.

    The Modular Automation Testing Framework requires the creation of small, independent scripts that represent modules, sections, and functions of the application-under-test. These small scripts are then used in a hierarchical fashion to construct larger tests, realizing a particular test case. 

     Advantages:

    • It reduces test Automation time.
    • Performance is high than recording and playback.
    • Execution and debugging is possible while you are building a component.
    Drawbacks:
    • Less usage of keywords,no concentration on Data driven testing and low performance than latest Frameworks like Keyword Driven Framework..

    Types of Modular driven Framework

    1.  Test Script Modularity Framework: Enables creation of Small, Independent Scripts representing  Modules & Functions of the Application under Test (AUT).
    2. Test Library Architecture Framework: Enables creation of Library Files representing Modules & Functions of the Application under Test (AUT)