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.

Difference between “=” and “==” in Java

One liner answer to this question would be:
“=” operator is for assigning a value to a variable whereas “==” operator compares the objects’ location(s) with respect to memory.
Thus,
“=” is an simple Assignment Operator which assigns values of variable on it’s right side variable to the left side operand.
“==” is a Relational Operator which checks if the values of two variables are equal or not. If yes then condition returns true otherwise, it returns false.
For example, below code explain the operators on primitive variables like int, float etc.
Primitive types are the most basic data types available within the Java language. There are 8: boolean , byte , char , short , int , long , float and double . These types serve as the building blocks of data manipulation in Java.
public voidtestMain(Object[] args)
{
                int a = 5;
                int b = 5;
                int c = a+b;
                System.out.println(“c=” + c); //This statement would print 10
                int d = a;
                System.out.println(“d=” + d); //This statement would print 5
                int e = c;
                System.out.println(“e=” + e); //This statement would print 10
                if(a==b)
                                System.out.println(“True”);
                else
                                System.out.println(“False”);
                //The above would print True: a=b=5
                                               
                if(a==c)
                                System.out.println(“True”);
                else
                                System.out.println(“False”);
                //The above would print False: a=5 & c=10
                                               
                if(a==d)
                                System.out.println(“True”);
                else
                                System.out.println(“False”);                 
                //The above would print True; a=5, d=a=5(as initialized above), thus a=d
}
Thus, output of above piece of code is:
c=10
d=5
e=10
True
False
True
Let’s learn some basics on ‘char’ first before proceeding to the example with ‘char’ variables. ‘char’ is a primitive type, and it can hold a single character or a Unicode/ASCII value.
public void testMain(Object[] args)
{
                char a = ‘5’;
                System.out.println(“Value of variable a=” + a);
                System.out.println(“ASCII value of variable a=” + (int) a);
                                               
                char b = ‘5’;
                System.out.println(“nValue of variable b=” + b);
                System.out.println(“ASCII value of b=” + (int) b);
                char c = ‘j’;
                System.out.println(“nValue of variable c=” + c);
                System.out.println(“ASCII value of c=” + (int) c);
                                               
                char d = (char) (a+b); //first adds the ASCII values of variables a and b which is 53+53=106; and then convert that ASCII value to it’s corresponding character which is ‘j’.
                System.out.println(“nValue of variable d=” + d);
                System.out.println(“ASCII value of d=” + (int) d);
                char e = a;
                System.out.println(“nValue of variable e=” + e); //This statement would print 5
                                               
                if(a==b)
                                System.out.println(“True”);
                else
                                System.out.println(“False”);
                //The above would print True: a=b=’5′
                                               
                if(a==c)
                                System.out.println(“True”);
                else
                                System.out.println(“False”);
                //The above would print False: a=’5′ & c=’j’
                                               
                if(a==e)
                                System.out.println(“True”);
                else
                                System.out.println(“False”);                 
                //The above would print True; a=’5′, e=a=’5′(as initialized above), thus a=e
}
Here is the output of above piece of code:
Value of variable a=5
ASCII value of variable a=53
Value of variable b=5
ASCII value of b=53
Value of variable c=j
ASCII value of c=106
Value of variable d=j
ASCII value of d=106
Value of variable e=5
True
False
True
Below code explains the operators on reference variables like string.
public void testMain(Object[] args)
{
                String a = “Smile”; //This statement creates a String Literal which points to a memory location in the String pool (which is shared) if it already exists. If it does not exists, it createsanew.
                String b = new String(“Smile”); //This statement creates a String Object in the heap memory which is not shared.
                               
                if(a==b)
                                System.out.println(“True”);
                else
                                System.out.println(“False”);
                //this prints “False” because variable a and b are pointing to different memory locations.
                               
                String c = “Smile”; //This statement would store variable c in the same memory location as variable a
                if(a==c)
                                System.out.println(“True”);
                else
                                System.out.println(“False”);
                //Since both a and c points to same memory location, thus having same data, it prints True
                               
                                                               
                if(c==“5”)
                                System.out.println(“True”);
                else
                                System.out.println(“False”);
                //This comparisioncreates a new memory location for undeclared data “5”. Thus it would print false                                                          
                               
}
Below is the output of above piece of code
False
True
False
Thus, the statement we gave in the beginning is actually correct but incomplete. The key to understanding here is the last word ‘memory’.
So, the complete one line answer to the question would be:

“=” operator is for assigning a value to a variable whereas “==” operator compares the objects’ location(s) in memory (Not the value).

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.