I used Apache POI libraries to read and write from
excel file, I add the Apache poi dependencies to my pom.xml file.
In order to connect I use following classes
-FileInputStream from Java. It is used to create connection
to the file. We pass the file path as constructor to it.
-WorkBook is a class that represents the excel file. We create
workbook is a class from Apache POI that represents the excel
file. We create Workbook object using the FileInputStream
object.
-Sheet represents a single sheet from the excel file. We create
sheet using Workbook object. We can create worksheet using
the 0 based index.
public String readExcel(String path, String sheetName, int rowNum, int colNum){ try { FileInputStream file = new FileInputStream(path); Workbook book = WorkbookFactory.create(file); Sheet sheet = book.getSheet(sheetName); Row row = sheet.getRow(rowNum);	Cell cell = row.getCell(colNum); String cellData = cell.toString(); return cellData; } catch (Exception e) { throw new RuntimeException(e); } }
How to get row and column numbers: int rowCount = workSheet.getLastRowNum()+1; ==> why we add '+1'? Because row num starts from 0. int colCount = workSheet.getRow(0).getLastCellNum(); String sheetName = workSheet.getSheetName();
The data we get from excel can be converted to different formats such as set,
list, map
-------------------EXCEL WRITING AND SAVING---------------------------------
The first creation part is same then go to a cell where you want to write.
Row row = sheet.getRow(0)
Cell resultCell = row.getCell(2)
Let's imagine you have values in index 0 and index 1.
Now you want to create a cell on index 2. First, check if it is null to
avoid problems.
if(resultCell==null){ resultCell = row.createCell(2);
}
resultCell.setValue("Germany");
in order to save:
// class is used to open file and write to it
FileOutputStream fileOutputStream = new FileOutputStream("src/test/resources/
Countries.xlsx");
// write the changes to the file and save
workbook.write(fileOutputStream);