APACHE POI KEY VALUE PAIR EXAMPLE

package com.readExcelFile;

import java.io.File;
import java.io.FileInputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class MainPoi {
static HashMap<String, String> hm = new HashMap<>();

public static void main(String[] args) throws Exception {
getKeyValuePair();
System.out.println(replacedValue());

}

public static HashMap<String, String> getKeyValuePair() throws Exception {
File excel = new File("E:\\EclipseWorkSpace_8thApril\\ReadExcelFile\\test1.xlsx");
FileInputStream fis = new FileInputStream(excel);
XSSFWorkbook wb = new XSSFWorkbook(fis);
XSSFSheet ws = wb.getSheetAt(0);
ws.setForceFormulaRecalculation(true);

int rowNum = ws.getLastRowNum() + 1;
int colNum = ws.getRow(0).getLastCellNum();
int surnameHeaderIndex = -1, valueHeaderIndex = -1;

XSSFRow rowHeader = ws.getRow(0);
for (int j = 0; j < colNum; j++) {
XSSFCell cell = rowHeader.getCell(j);
String cellValue = cellToString(cell);
if ("SURNAME".equalsIgnoreCase(cellValue)) {
surnameHeaderIndex = j;
} else if ("VALUE".equalsIgnoreCase(cellValue)) {
valueHeaderIndex = j;
}
}

if (surnameHeaderIndex == -1 || valueHeaderIndex == -1) {
throw new Exception("Could not find header indexes\nSurname : " + surnameHeaderIndex + " | Value : "
+ valueHeaderIndex);
}

for (int i = 1; i < rowNum; i++) {
XSSFRow row = ws.getRow(i);
String surname = cellToString(row.getCell(surnameHeaderIndex));
String value = cellToString(row.getCell(valueHeaderIndex));
hm.put(surname, value);
}
return hm;
}

public static String cellToString(XSSFCell cell) {
int type;
Object result = null;
type = cell.getCellType();

switch (type) {

case XSSFCell.CELL_TYPE_NUMERIC:
result = BigDecimal.valueOf(cell.getNumericCellValue()).toPlainString();

break;
case XSSFCell.CELL_TYPE_STRING:
result = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_BLANK:
result = "";
break;
case XSSFCell.CELL_TYPE_FORMULA:
result = cell.getCellFormula();
}

return result.toString();
}

public static List<String> getList() {
ArrayList<String> al = new ArrayList<>();
al.add("A1");
al.add("B1");
al.add("Z1");
al.add("C1");
return al;
}

public static List<String> replacedValue() {
List<String> list = new ArrayList<>();
for (int i = 0; i < getList().size(); i++) {
for (Map.Entry<String, String> entry : hm.entrySet()) {
for (String listValue : getList()) {
if (listValue.equalsIgnoreCase(entry.getKey()) && (i < getList().size())) {
listValue = entry.getValue();
list.add(listValue);
i++;

} else if (i < getList().size()) {
list.add(listValue);
i++;
}
}
}

}

return list;
}
}

Excel Sheet for the key value pair mapping

surname	value
A1	B1
A2	B2
A3	B3

OUTPUT: [B1, B1, Z1, C1]