Microsoft Excel is a widely used program to import/export data in different forms. It is extensively used in test automation to perform data-driven testing. Excels are easy to use as it is convenient to keep data for automated tests in special storage that supports sequential access to a set of data.
While writing automation test scripts we face several challenges such as reading/writing data from Excel, validating dynamic toast messages, extracting text from images, and so on. In this article, we will see 2 Java libraries that are Apache POI Alternatives.
Read/Write Excel:
There are a lot of Libraries available that can read and write Excel and users can use any of them as per their requirements. Apache POI is one of them, but in this article, we are not going to use it.
1)FastExcel
The first library we will be using is fast Excel. FastExcel is a library for reading and writing Excel files in Java using the free XLS format. It is an alternative to the Apache POI library.
FastExcel supports a limited set of features – it focuses on speed and memory reduction. In Fast Excel, each worksheet in the workbook can be generated by a different thread, while fully supporting shared strings and styles.
Prerequisite: Add the below-mentioned dependency in the pom.xml file.
<dependency>
<groupId>org.dhatim</groupId>
<artifactId>fastexcel</artifactId>
<version>0.12.8</version>
</dependency>
<dependency>
<groupId>org.dhatim</groupId>
<artifactId>fastexcel-reader</artifactId>
<version>0.12.3</version>
</dependency>Example:
- Create an Excel Workbook.
2. Create a sheet in the Excel workbook.
3. Enter values in the Excel sheet.
4. Add Style to a cell value.
5. Read the excel sheet.
/***
*Function to create excel sheet with workbook 'sheet 1' and enter values in rows.
*/
public static void ExcelWrite() {
List < String > words = List.of("Cricket", "Hockey", "Football", "Chess", "Skating");
int row = 0;
int col = 0;
//Path of the file
File f = new File("/src/test/resources/words.xlsx");
try (FileOutputStream fos = new FileOutputStream(f)) {
//Create Workbook with worksheet one
Workbook wb = new Workbook(fos, "Application", "1.0");
Worksheet ws = wb.newWorksheet("Sheet 1");
ws.value(1, 1, "old falcon");
//Adding style to the cell value
ws.style(1, 1).horizontalAlignment("center").bold().italic().set();
for (String word: words) {
ws.value(row, col, word);
row++;
}
wb.finish();
}
}
/***
*Function to Read values from the above created excel sheet
*/
public static void FastExcelRead() throws IOException {
File f = new File("src/test/resources/words.xlsx");
try (ReadableWorkbook wb = new ReadableWorkbook(f)) {
Sheet sheet = wb.getFirstSheet();
try (Stream < Row > rows = sheet.openStream()) {
Iterator < Row > it = rows.iterator();
while (it.hasNext()) {
Row row = it.next();
row.stream().forEach(cell -> System.out.println(cell.getText()));
}
}
}
}
}Understand the code:
ExcelWrite() is a method that creates an Excel sheet with a workbook named “Application” and a worksheet named “Sheet 1” and sets up this file under /src/test/resources/ directory.
FastExcelRead() is a method responsible for reading values from the previously created Excel sheet. It gets the first sheet from the workbook. For each row, it opens a stream of cells and prints the text content of each cell.
Let’s suppose we want to create another sheet in the same workbook and add different supported data types as cell values in the newly created sheet.
public static void createNewSheet() throws FileNotFoundException, IOException
{
try (FileOutputStream os = new FileOutputStream(f)) {
Worksheet ws = wb.newWorksheet("Sheet 2");
ws.value(0, 0, "This is a string in A1");
ws.value(0, 1, new Date());
ws.value(0, 2, 1234);
ws.value(0, 3, 123456L);
ws.value(0, 4, 1.234);
wb.finish();
}
}
After execution, we will get this workbook in our project folder with 2 sheets. So with fast Excel, we can automate Excel handling tasks easily in our test automation framework. It is fast as compared to Apache POI and can easily be used with Selenium.
2)GcExcel
Our Next Java library is GcExcel. GcExcel is a comprehensive API that allows quick creation, manipulation, conversion, and sharing of Microsoft Excel-compatible spreadsheets. It Requires a low memory footprint and produces output in varied formats like .xlsx and pdf.
Prerequisite:
<dependency>
<groupId>com.grapecity.documents</groupId>
<artifactId>gcexcel</artifactId>
<version>4.2.4</version>
</dependency>Example: Create a workbook and initialize data
- Create a workbook.
2. Create a 2D Array to initialize data.
3. Add Labels and calculations for each range of data.
public class GcexcelDemo {
public static void main(String[] args)
{
Workbook workbook = new Workbook();
Object[][] sourceData = new Object[][]{
{"ITEM", "AMOUNT"},
{"Income 1", 2500},
{"Income 2", 1000},
{"Other", 250},
};
Object[][] sourceData1 = new Object[][]{
{"ITEM", "AMOUNT"},
{"Rent/mortgage", 800},
{"Electricity", 120},
{"Gas", 50},
{"Cell phone", 45},
{"Groceries", 500},
{"Car payment", 273},
};
IWorksheet worksheet = workbook.getWorksheets().get(0);
worksheet.setName("Table");
worksheet.getRange("B3:C7").setValue(sourceData);
worksheet.getRange("B10:C23").setValue(sourceData1);
worksheet.getRange("B2:C2").merge();
worksheet.getRange("B2").setValue("MONTHLY INCOME");
worksheet.getRange("B9:C9").merge();
worksheet.getRange("B9").setValue("MONTHLY EXPENSES");
worksheet.getRange("E2:G2").merge();
workbook.save("./GcExcelFeatures.xlsx");
}
}By using GcExcel we can create tables, and charts set styles and set formulas to tables.
Apart from these 2 alternatives, there are other alternatives as well for example JExcelApi, OpenXML4J etc. Understand your project requirements and choose wisely.