Reading Excel file data and making java objects Example by using POI-API

Note: .xlsx Which will accommodate more number of rows(maximum 1,048,576). Usually in .xls format which will throw java heap space problem, can’t create more than 65,000 rows.

Reading Excel file data and making java objects Example by using POI-API

ExcelReader.java
===============================================
import java.math.BigDecimal;

import java.util.List;

import java.util.Map;

import java.util.ArrayList;

import java.util.HashMap;

import java.io.FileInputStream;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.Row;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import com.xyz.vo.CourseVo;

public class ExcelReader {

    private static final Logger LOG = LoggerFactory.getLogger(ExcelReader.class);

    public List<CourseVo> readDataFromExcel(String filePath){

        Map<String,Object> map = readExcelSheet(filePath);

        LOG.info((String)map.get("headers"));

        List<CourseVo> valueObjectList = (List<CourseVo>)map.get("valueObjectList");

        for (CourseVo vo:valueObjectList) {
            LOG.info("Code :  " + vo.getCode()+"  Name: "+vo.getName()+"  Sessions : "+vo.getSessions()+
                    "  Duration : "+vo.getDuration()+"  MaxScoreMarks : "+vo.getScoreMarks()+" StartMonth  : "+vo.getStartMonth());

        }

        return volist;

    }

 

    public Map<String,Object> readExcelSheet(String filePath) {

     

            CourseVo courseVo = null;

            Map<String, Object> courseVOMap = null;

            try {

                LOG.info("File path"+filePath);

                XSSFWorkbook myWorkBook = new XSSFWorkbook(new FileInputStream(filePath));

                XSSFSheet mySheet = myWorkBook.getSheetAt(0);

                boolean isFirstRow = true;

                courseVOMap = new HashMap<String, Object>();

                List<CourseVo> cellsList = new ArrayList<CourseVo>();

                String headers = null;

                for(Row myRow : mySheet){

                    courseVo = new CourseVo();

                    if (isFirstRow) {

                        headers = addHeaderRowCells(courseVo, myRow);

                        courseVOMap.put("headers", headers);

                    } else {

                        addVoRowCells(cellsList, myRow);

                    }

                 

                    if(isFirstRow) {

                        isFirstRow = false;

                    }

                }

                courseVOMap.put("valueObjectList", cellsList);

            } catch (Exception e) {

                LOG.error(e.getMessage());

            }

            return courseVOMap;

        }

    public void addVoRowCells(List<CourseVo> cellList, Row row) {

        int cellInd = 0;

        CourseVo valueObject = new CourseVo();

     

        Cell cell = row.getCell(cellInd++);

            try{

            valueObject.setCode(String.valueOf((int)cell.getNumericCellValue()));

            }catch(Exception e){

                LOG.error(e.getMessage());

            }

            myCell = row.getCell(cellInd++);

            try{

            valueObject.setName(cell.getStringCellValue());

            }catch(Exception e){

                LOG.error(e.getMessage());

            }

            myCell = row.getCell(cellInd++);

            try{

            valueObject.setSessions((short)cell.getNumericCellValue());

            }catch(Exception e){

                LOG.error(e.getMessage());

            }

            myCell = row.getCell(cellInd++);

            try{

            valueObject.setDuration(BigDecimal.valueOf(cell.getNumericCellValue()));

            }catch(Exception e){

                LOG.error(e.getMessage());

            }

            myCell = row.getCell(cellInd++);

            try{

            valueObject.setScoreMarks(BigDecimal.valueOf((int)cell.getNumericCellValue()));

            }catch(Exception e){

                LOG.error(e.getMessage());

            }

            myCell = row.getCell(cellInd++);

            try{

            valueObject.setStartMonth(cell.getDateCellValue());

            }catch(Exception e){

                LOG.error(e.getMessage(););

            }

            cellList.add(valueObject);
}

 

    public String addHeaderRowCells(CourseVo vo, Row row) {

        int cellInd = 0;

        String header = "";

        try{

            Cell cell = row.getCell(cellInd++);

            header = cell.getStringCellValue();

            cell = row.getCell(cellInd++);

            header += "," + cell.getStringCellValue();

            cell = row.getCell(cellInd++);

            header += "," + cell.getStringCellValue();

            cell = row.getCell(cellInd++);

            header += "," + cell.getStringCellValue();

            cell = row.getCell(cellInd++);

            header += "," + cell.getStringCellValue();

            cell = row.getCell(cellInd++);

            header += "," + cell.getStringCellValue();

        }catch(Exception e){

            LOG.error(e.getMessage(););

        }

        return header;

    }

    }

CourseVo.java
========================================
package com.xyz.vo;

import java.math.BigDecimal;

import java.util.Date;

/**

 * @author Pedababu M

 *

 */

public class CourseVo {

    String code;

    String name;

    short sessions;

    BigDecimal Duration;

    BigDecimal scoreMarks;

    Date startMonth;

    /**

     * @return the code

     */

    public String getCode() {

        return code;

    }

    /**

     * @param code the code to set

     */

    public void setCode(String code) {

        this.code = code;

    }

    /**

     * @return the name

     */

    public String getName() {

        return name;

    }

    /**

     * @param name the name to set

     */

    public void setName(String name) {

        this.name = name;

    }

    /**

     * @return the sessions

     */

    public short getSessions() {

        return sessions;

    }

    /**

     * @param sessions the sessions to set

     */

    public void setSessions(short sessions) {

        this.sessions = sessions;

    }

    /**

     * @return the duration

     */

    public BigDecimal getDuration() {

        return Duration;

    }

    /**

     * @param duration the duration to set

     */

    public void setDuration(BigDecimal duration) {

        Duration = duration;

    }

    /**

     * @return the scoreMarks

     */

    public BigDecimal getScoreMarks() {

        return scoreMarks;

    }

    /**

     * @param scoreMarks the scoreMarks to set

     */

    public void setScoreMarks(BigDecimal scoreMarks) {

        this.scoreMarks = scoreMarks;

    }

    /**

     * @return the startMonth

     */

    public Date getStartMonth() {

        return startMonth;

    }

    /**

     * @param startMonth the startMonth to set

     */

    public void setStartMonth(Date startMonth) {

        this.startMonth = startMonth;

    }

}