Maven Dependency
Simple Library class for Excel Read and Write operations using Apache POI
<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); } } }
