How read data from MS excel file in java

Apache POI used to read data for exel file Apache POI is a popular API that allows programmers to create, modify, and display MS Office files using Java programs.  It is an open source library developed and distributed by Apache Software Foundation to design or modify Microsoft Office files using Java program. we need following jar file to read data from excel file . dom4j-1.5.jardom4j-1.6.jarooxml-schemas-1.0.jarostermillerutils_1_07_00.jarpoi-contrib-3.5-20090928.jarpoi-ooxml-3.5-FINAL-20090928.jarpoi-ooxml-3.8.jarpoi-ooxml-schemas-3.7-beta1.jarpoi-scratchpad-3.8-beta5-20111217.jarxercesImpl.jarxmlbeans-2.3.0.jarxmlbeans.jar
you can download latest Appache POI api jar from here https://poi.apache.org/download.html

Sample java code for reading data from MS excel file 

MS excel file data in file 

package in.jk;

import java.io.ByteArrayInputStream;

import java.io.File;

import java.io.FileInputStream;

import java.io.IOException;

import java.math.BigDecimal;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

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.ss.usermodel.WorkbookFactory;

public class ExcelParser {

private static int numColumns = -1;

public static void main(String[] args) {

String userId = null;

String password = null;

ByteArrayInputStream arrayInputStream = null;

List<Map<Integer, Object>> listMap = null;

FileInputStream fileInputStream = null;

try {

File file = new File("E:/login.xlsx");

fileInputStream = new FileInputStream(file);

byte fileContent[] = new byte[(int) file.length()];

fileInputStream.read(fileContent);

arrayInputStream = new ByteArrayInputStream(fileContent);

listMap = getExcelSheet(arrayInputStream, 0);

} catch (Exception exception) {

System.out.println(exception);

} finally {

if (fileInputStream != null) {

try {

fileInputStream.close();

} catch (IOException e) {

e.printStackTrace();

}

}

}

System.out.println("User Id      " + "Password");

for (int i = 1; i < listMap.size(); i++) {

Map<Integer, Object> dataMap = listMap.get(1);

userId = (String) dataMap.get(0);

password = (String) dataMap.get(1);

System.out.println(userId + "        " + password);

}

}

private static List<Map<Integer, Object>> getExcelSheet(ByteArrayInputStream arrayInputStream, Integer sheetNumber)

throws Exception {

        Workbook workbook = null;

List<Map<Integer, Object>> sheetData = null;

workbook = WorkbookFactory.create(arrayInputStream);

if (sheetNumber == null) {

sheetNumber = 0;

}

        Sheet sheet = workbook.getSheetAt(sheetNumber);

sheetData = getSheetData(sheet);

return sheetData;

}

private static List<Map<Integer, Object>> getSheetData(Sheet sheet) {

int numRows = -1;

List<Map<Integer, Object>> sheetData = null;

if (sheet == null) {

return null;

}

numRows = sheet.getLastRowNum();

System.out.println("number of rows in the sheet are ::" + numRows);

sheetData = new ArrayList<Map<Integer, Object>>();

for (int i = 0; i <= numRows; i++) {

Row row = sheet.getRow(i);

Map<Integer, Object> rowData = getRowData(row);

sheetData.add(rowData);

}

return sheetData;

}

private static Map<Integer, Object> getRowData(Row row) {

int numCols = -1;

Map<Integer, Object> data = null;

if (row == null) {

return null;

}

data = new HashMap<Integer, Object>();

numCols = row.getLastCellNum();

if (numCols > numColumns) {

numColumns = numCols;

}

       for (int i = 0; i < numCols; i++) {

Cell cell = row.getCell(i);

Object cellData = getCellData(cell);

if (cell != null) {

data.put(i, cellData);

}

}

return data;

}

private static Object getCellData(Cell cell) {

int cellType = -1;

if (cell == null) {

return null;

}

cellType = cell.getCellType();

switch (cellType) {

case Cell.CELL_TYPE_BLANK:

return null;

case Cell.CELL_TYPE_BOOLEAN:

return String.valueOf(cell.getBooleanCellValue());

case Cell.CELL_TYPE_NUMERIC:

if (checkInteger(cell.getNumericCellValue())) {

return String.valueOf(BigDecimal.valueOf((long) cell.getNumericCellValue()));

} else {

return String.valueOf(BigDecimal.valueOf(cell.getNumericCellValue()));

}

case Cell.CELL_TYPE_STRING:

return String.valueOf(cell.getStringCellValue());

case Cell.CELL_TYPE_ERROR:

return String.valueOf(cell.getErrorCellValue());

case Cell.CELL_TYPE_FORMULA:

return cell.getStringCellValue();

default:

return null;

}

}

private static boolean checkInteger(double p_double) {

if ((p_double * 10) % 10 == 0) {

return true;

}

return false;

}

}

Output  of java program after reading data form MS excel file