Read/Write Excel File

1. Add latest version maven dependency
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.7</version>
</dependency>

2. write method to support ".xls" and ".xlsx"

     Workbook getWorkbookInstance(File inputFile) throws Exception {
FileInputStream excelFile = new FileInputStream(inputFile);
Workbook workbook = null;
String fileAbsolutePath = inputFile.getAbsolutePath();
String fileExtension = fileAbsolutePath.substring(fileAbsolutePath.lastIndexOf('.') + 1);

if ("XLS".equalsIgnoreCase(fileExtension)) {
workbook = new HSSFWorkbook(excelFile);
} else if ("XLSX".equalsIgnoreCase(fileExtension)) {
workbook = new XSSFWorkbook(excelFile);
} else {
// Change this code to support other format
workbook = new XSSFWorkbook(excelFile);
}

return workbook;
}

3. Read File:: Get sheet from workbook, row from sheet, cell from row
    // get sheet from workbook
    Sheet sheet = workbook.getSheetAt(0);
    // row iterator
    Iterator<Row> iterator = sheet.iterator();
    while (iterator.hasNext()) {
        // row from sheet
Row currentRow = iterator.next();
// skip the header row
if (currentRow.getRowNum() == 0) {
    continue; // just skip the rows if row number is 0
}
// read the cell data from row
       // input formatter
DataFormatter df = new DataFormatter();
        currentRow.getCell(0).getStringCellValue()
        df.formatCellValue(currentRow.getCell(1)

4. Write in File::
void writeUnprocessedDuplicateReportInFile(List<RoboticData> roboticReport) {
// create new workbook
XSSFWorkbook workbook = new XSSFWorkbook();
// create sheet in workbook
XSSFSheet unprocessedDuplicateSheet = workbook.createSheet("DuplicateRecords");
int rowCount = 0;
// Write header columns
Row header = unprocessedDuplicateSheet.createRow(rowCount++);
header.createCell(0).setCellValue("Master Agreement ID");
header.createCell(1).setCellValue("Master Agreement Name");
header.createCell(2).setCellValue("Child MA");

Row row = null;
for (RoboticData data : roboticReport) {
// create new rows and write data from bean to sheet row
row = unprocessedDuplicateSheet.createRow(rowCount++);
row.createCell(0).setCellValue(data.getMasterMAID());
row.createCell(1).setCellValue(data.getMasterMAName());
row.createCell(2).setCellValue(data.getChildMA());
}

// Write into file
try {
// Create directory
File dir = new File("output");
dir.mkdirs();// return true if directory created & false if already exist
File file = new File(dir, "fileName.xlsx");
// Delete if file already exist
Files.deleteIfExists(Paths.get(file.getAbsolutePath()));
FileOutputStream outputStream = new FileOutputStream(file.getAbsolutePath());
// write data into file
workbook.write(outputStream);
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}