Skip to content

Top 2 Alternatives To Apache POI Library That Can Actually Make Your Life Better

How to Read and Write Excel using Java

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 support 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 alternatives to Apache POI.

Read/Write Excel:

There are a lot of Libraries available which can read and write excels 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 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:

1)Create an Excel Workbook.

2)Create a sheet in the workbook.

3)Enter values in the 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()));

      }
    }
  }

}
}

Suppose in the next task we want to create another sheet in the same workbook and add different supported data types as cell values.

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();
    	}

    }
Alternatives to Apache POI
How to read and write Excel

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

1)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.