Until today, to perform data-driven testing, we have used libraries like Apache POI, JExcel etc to read/write Excel files. To work with these libraries, we have to create spreadsheet, save in a repository and we need to write much amount of code to access the spreadsheet and test all the data dependent scenarios.
Steps to be followed while updating an excel file –
1. Open Existing workbook using an input stream since, we want to read content from existing file.
2. Get existing sheet then row and then cell objects for existing content that we want to update.
3. Else, Create sheet/row/cell on need.
4. Update the selected cells.
5. Open an output stream and write to workbook.
6. Close all streams and workbook objects.
Let us see an example by using the above steps :
As we know the main difference with Java JXL which does not support the Excel 2007 ".xlsx" file format. It only supports the old BIFF (binary) ".xls" format. Where as Apache POI supports both xls and xlsx file formats.
To read an Excel 2007 (.xlsx) we need to use XSSF (XML SpreadSheet Format) and we will use the below classes to work with xlsx files by importing the below statements
Normally, to read a data in excel, first we should have access to workbook, sheet which we want to read as workbook contains multiple sheets and if you want to read a particular cell we need location of a Cell.
In this article, we will discuss how to access workbook, sheet and a Cell using Jxl library Download jxl jar and add it to build path.