Excel Sheet Operations

  • Excel sheets are used for storing test data. Spreadsheets will be of two extensions. xls and xlsx. xls is older and xlsx is newer version of MS office.

  • Read and write operation in xls files can be done through jxl jar. And those files with extension xlsx can be read or written by poi jars.

  • Poi is having more features it also support old formats like xls format to read and write.

  • Also, we need to know how to work with excel sheet as we will be storing the results of test cases into excel.



  1. Download jxl.jar any version.

  2. Add that jar into the project.

  3. Create a new Java file and write code inside main method.

add external jars in eclipse java project

  1. Here Workbook represents excel sheet.

  2. The workbook can have many sheets

  3. One sheet can have many cells, rows, columns.

  4. Now we can fetch data from cell object

Tasks expected from you:

Explore all different methods in workbook class. Sheet class.

How to get no of rows and no of columns here.

Once you get this how we can print excel sheet by using for loop.

  1. Hint: there will be 2 for loops

  2. One for loop is for rows and inside that for loop

    1. one for loop is for columns.

  3. getCell method will be using both rowno and column no dynamically.


You will see excel sheet after refreshing project as shown below: location of .xls excel sheet in java project

Example .xls excel sheet in java project

Apache POI is one of them and is well trusted over time. In short, you can read and write MS Excel files using Java. In addition to this you can also read and write MS Word and MS PowerPoint files using Java.

If you are working on a maven project, you can include the POI dependency in pom.xml file using the following index:

Syntax:


If you are not using maven, then you can download maven jar files from POI download page. Include following jar files in order to run the sample code:

  • dom4j-1.6.1.jar

  • poi-3.9-20121203.jar

  • poi-ooxml-3.9-20121203.jar

  • poi-ooxml-schemas-3.9-20121203.jar

  • xmlbeans-2.3.0.jar

Writing an excel file by POI:

We will be taking this example first so that we can reuse the excel sheet created by this code to read back in the next example.

Writing a file using POI is very simple and it involves the following steps:

  1. Create a workbook

  2. Create a sheet in the workbook

  3. Create a row in the sheet

  4. Add cells in the sheet

  5. Repeat step 3 and 4 to write more data

maven jar files to read and write ms excel files in java and selenim

Program for writing into excel sheet by poi is as follow.


Program for writing into excel sheet by poi is as follow.


Note:

  • You can try to read xls format excel sheet by poi.

  • Always save files properly and path of files needs to be carefully mentioned in the program.

  • “Unable to recognize OLE stream error” you may face because of wrong format. Save as your file then save into xlsx format. Do not rename.