Maven Dependency
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.5</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.5</version>
</dependency>
Simple Library class for Excel Read and Write operations using Apache POI
package com.apache.excel.example;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Calendar;
import java.util.Date;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelOperations {
Workbook workbook;
public ExcelOperations(String filePath) {
try {
workbook = new XSSFWorkbook(filePath);
} catch (IOException e) {
throw new RuntimeException("Error while reading workbook",e);
}
}
public ExcelOperations() {
workbook = new XSSFWorkbook();
}
public Object getValue(int sheetIndex, int rowIndex,int colIndex){
Sheet sheet = workbook.getSheetAt(sheetIndex);
return getValue(sheet, rowIndex, colIndex);
}
public Object getValue(String sheetName, int rowIndex,int colIndex){
Sheet sheet = workbook.getSheet(sheetName);
return getValue(sheet, rowIndex, colIndex);
}
public Object getValue( int rowIndex,int colIndex){
return getValue(workbook.getActiveSheetIndex(), rowIndex, colIndex);
}
public Object getValue(Sheet sheet, int rowIndex,int colIndex){
Object value =null;
Cell c = sheet.getRow(rowIndex).getCell(colIndex);
switch (c.getCellType()) { case STRING:
value= c.getStringCellValue();
break;
case NUMERIC:
value=c.getNumericCellValue();
break;
case _NONE:
case BLANK:
value="";
break;
case ERROR:
value="#ERR#";
break;
case BOOLEAN:
value = c.getBooleanCellValue();
break;
case FORMULA:
value= c.getCellFormula();
break;
default:
break;
}
return value;
}
public int getRowCount(){
return activeSheet().getLastRowNum()+1;
}
private Sheet activeSheet(){
int index = workbook.getActiveSheetIndex();
return workbook.getSheetAt(index);
}
public int getColumnCount(int rowIndex){
return getRow(rowIndex).getLastCellNum();
}
private Row getRow(int rowIndex) {
return activeSheet().getRow(rowIndex);
}
public void save(String saveToFilePath) {
try {
FileOutputStream fos = new FileOutputStream(saveToFilePath);
workbook.write(fos);
fos.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
public void setValue(int rowNo, int cellNo, Object value) {
setValue(workbook.getActiveSheetIndex(), rowNo, cellNo, value);
}
public void setValue(int sheetIndex, int rowNo, int cellNo, Object value) {
Sheet sheet = workbook.getSheetAt(sheetIndex);
setValue(sheet, rowNo, cellNo, value);
}
public void setValue(String sheetName, int rowNo, int cellNo, Object value) {
Sheet sheet = workbook.getSheet(sheetName);
setValue(sheet, rowNo, cellNo, value);
}
public void setValue(Sheet sheet, int rowNo, int cellNo, Object value) {
Row row = sheet.getRow(rowNo);
if(row==null){
row = sheet.createRow(rowNo);
}
Cell cell = row.getCell(cellNo);
if(cell==null){
cell = row.createCell(cellNo);
}
writeToCell(cell,value);
}
void closeReading() {
try {
workbook.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void createSheet(String sheetName) {
Sheet sheet = workbook.getSheet(sheetName);
if(sheet==null){
workbook.createSheet(sheetName);
int index = workbook.getSheetIndex(sheetName);
workbook.setActiveSheet(index);
}
}
public void write2DData(Object[][] data, int startRowIndex, int startColIndex) {
Sheet sheet = activeSheet();
for (int i = 0; i < data.length; i++) { // iterate over rows (i is row offset counter)
Row row = sheet.getRow(startRowIndex+i);
if(row==null){
row = sheet.createRow(startRowIndex+i);
}
writeToRow(row,data[i],startColIndex);
}
}
private void writeToRow(Row row, Object[] data, int startCellNo) {
for (int j = 0; j < data.length; j++) { // iterate over columns, j is column offset index
Cell cell = row.getCell(startCellNo+j);
if(cell == null){
cell = row.createCell(startCellNo+j);
}
writeToCell(cell,data[j]);
}
}
private void writeToCell(Cell cell, Object valueObject) {
if(valueObject instanceof String) {
cell.setCellValue((String)valueObject);
}
else if(valueObject instanceof Integer || valueObject instanceof Double || valueObject instanceof Float || valueObject instanceof Long){
Double doubleValue = Double.parseDouble(valueObject.toString());
cell.setCellValue(doubleValue);
}else if(valueObject instanceof Boolean){
cell.setCellValue((Boolean) valueObject);
}else if(valueObject instanceof Date){
cell.setCellValue((Date)valueObject);
}else if(valueObject instanceof Calendar){
cell.setCellValue((Calendar)valueObject);
}
}
}
