public class ExcelPOIHelper {
Map<Integer, List<String>> data = new HashMap<>();
FileInputStream file = new FileInputStream(new File(fileLocation));
Workbook workbook = new XSSFWorkbook(file);
Sheet sheet = workbook.getSheetAt(0);
int i = 0;
for (Row row : sheet) {
data.put(i, new ArrayList<String>());
for (Cell cell : row) {
switch (cell.getCellTypeEnum()) {
case STRING:
data.get(i)
.add(cell.getRichStringCellValue()
.getString());
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
data.get(i)
.add(cell.getDateCellValue() + "");
} else {
data.get(i)
.add((int)cell.getNumericCellValue() + "");
}
break;
case BOOLEAN:
data.get(i)
.add(cell.getBooleanCellValue() + "");
break;
case FORMULA:
data.get(i)
.add(cell.getCellFormula() + "");
break;
default:
data.get(i)
.add(" ");
}
}
i++;
}
if (workbook != null){
workbook.close();
}
return data;
}
Workbook workbook = new XSSFWorkbook();
try {
Sheet sheet = workbook.createSheet("Persons");
sheet.setColumnWidth(0, 6000);
sheet.setColumnWidth(1, 4000);
Row header = sheet.createRow(0);
CellStyle headerStyle = workbook.createCellStyle();
headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
XSSFFont font = ((XSSFWorkbook) workbook).createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 16);
font.setBold(true);
headerStyle.setFont(font);
Cell headerCell = header.createCell(0);
headerCell.setCellValue("Name");
headerCell.setCellStyle(headerStyle);
headerCell = header.createCell(1);
headerCell.setCellValue("Age");
headerCell.setCellStyle(headerStyle);
CellStyle style = workbook.createCellStyle();
style.setWrapText(true);
Row row = sheet.createRow(2);
Cell cell = row.createCell(0);
cell.setCellValue("Rajiv Sharma");
cell.setCellStyle(style);
cell = row.createCell(1);
cell.setCellValue(20);
cell.setCellStyle(style);
File currDir = new File(".");
String path = currDir.getAbsolutePath();
String fileLocation = path.substring(0, path.length() - 1) + "temp.xlsx";
FileOutputStream outputStream = new FileOutputStream(fileLocation);
workbook.write(outputStream);
} finally {
if (workbook != null) {
workbook.close();
}
}
}
}
//Read Excel File
public Map<Integer, List<String>> readExcel(String fileLocation) throws IOException {Map<Integer, List<String>> data = new HashMap<>();
FileInputStream file = new FileInputStream(new File(fileLocation));
Workbook workbook = new XSSFWorkbook(file);
Sheet sheet = workbook.getSheetAt(0);
int i = 0;
for (Row row : sheet) {
data.put(i, new ArrayList<String>());
for (Cell cell : row) {
switch (cell.getCellTypeEnum()) {
case STRING:
data.get(i)
.add(cell.getRichStringCellValue()
.getString());
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
data.get(i)
.add(cell.getDateCellValue() + "");
} else {
data.get(i)
.add((int)cell.getNumericCellValue() + "");
}
break;
case BOOLEAN:
data.get(i)
.add(cell.getBooleanCellValue() + "");
break;
case FORMULA:
data.get(i)
.add(cell.getCellFormula() + "");
break;
default:
data.get(i)
.add(" ");
}
}
i++;
}
if (workbook != null){
workbook.close();
}
return data;
}
writeExcel using POI
public void writeExcel() throws IOException {Workbook workbook = new XSSFWorkbook();
try {
Sheet sheet = workbook.createSheet("Persons");
sheet.setColumnWidth(0, 6000);
sheet.setColumnWidth(1, 4000);
Row header = sheet.createRow(0);
CellStyle headerStyle = workbook.createCellStyle();
headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
XSSFFont font = ((XSSFWorkbook) workbook).createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 16);
font.setBold(true);
headerStyle.setFont(font);
Cell headerCell = header.createCell(0);
headerCell.setCellValue("Name");
headerCell.setCellStyle(headerStyle);
headerCell = header.createCell(1);
headerCell.setCellValue("Age");
headerCell.setCellStyle(headerStyle);
CellStyle style = workbook.createCellStyle();
style.setWrapText(true);
Row row = sheet.createRow(2);
Cell cell = row.createCell(0);
cell.setCellValue("Rajiv Sharma");
cell.setCellStyle(style);
cell = row.createCell(1);
cell.setCellValue(20);
cell.setCellStyle(style);
File currDir = new File(".");
String path = currDir.getAbsolutePath();
String fileLocation = path.substring(0, path.length() - 1) + "temp.xlsx";
FileOutputStream outputStream = new FileOutputStream(fileLocation);
workbook.write(outputStream);
} finally {
if (workbook != null) {
workbook.close();
}
}
}
}