Easy Util For Apache POI

ExcelPOIUtils.java

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
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;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

/**
 * Created by Rahul_Server on 5/15/2016.
 */
public class ExcelPOIUtils {
    File excelFile;
    public ExcelPOIUtils(File excelFile) throws Exception {
        if(!excelFile.getName().endsWith(".xlsx")) {
            throw new Exception("The file should be xlsx!");
        }
        this.excelFile = excelFile;
    }

    /**
     *
     * @param sheetIndex 0 based
     * @return
     * @throws IOException
     * @throws InvalidFormatException
     */
    public int getTotalRowCount(int sheetIndex) throws IOException, InvalidFormatException {
        Workbook workbook = new XSSFWorkbook(excelFile);
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        int numRows = sheet.getPhysicalNumberOfRows();
        workbook.close();
        return numRows;
    }

    private Row getRowAt(int sheetIndex, int rowIndex) throws IOException, InvalidFormatException {
        Workbook workbook = new XSSFWorkbook(excelFile);
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        Row row = sheet.getRow(rowIndex);
        workbook.close();
        return row;
    }

    private Cell getCellAtRow(Row row, int index) {
        return row.getCell(index);
    }

    public String getCellValueAt(int sheetIndex, int rowIndex, int colIndex) throws IOException, InvalidFormatException {
        Row row = getRowAt(sheetIndex, rowIndex);
        Cell cell = row.getCell(colIndex);
        return getCellContentAsString(cell);
    }

    public String getCellContentAsString(Cell cell) {
        if(cell.getCellType() == Cell.CELL_TYPE_STRING) {
            return cell.getStringCellValue();
        } else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            return String.valueOf(cell.getNumericCellValue());
        } else if(cell.getCellType() == Cell.CELL_TYPE_BLANK) {
            return "";
        } else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else {
            return "NULL";
        }
    }

    public void writeCellValueAt(int sheetIndex, int rowIndex, int colIndex, String cellValue) throws IOException, InvalidFormatException {
        FileInputStream fis = new FileInputStream(excelFile);
        Workbook workbook = new XSSFWorkbook(fis);
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        Row row = sheet.getRow(rowIndex);
        if(row==null) {
            sheet.createRow(rowIndex);
        }

        Cell cell = row.createCell(colIndex);
        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue(cellValue);
        fis.close();

        FileOutputStream fos = new FileOutputStream(excelFile);
        workbook.write(fos);
        fos.close();
        workbook.close();
    }
}