poi读取excel 转换实体_POI读取excel工具类 返回实体bean集合(xls,xlsx通用)

package com.boot.utils;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;

import java.util.ArrayList;

import java.util.List;

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

import com.boot.entity.User;public classExcelReader {/**

* 根据excal路径生成实体集合

* @author Changhai

* @data 2017-7-5

* @param filePath

* @return*/

public static List>getList(String filePath){

InputStreamis;try{is = newFileInputStream(filePath);return getList(is);

}catch(Exception e) {

e.printStackTrace();

}return null;

}/**

* 根据输入流生成实体集合

* @param is

* @author Changhai

* @data 2017-7-5

* @return

* @throws IOException*/

public static List getList(InputStream is)

throws IOException {

List> list = ExcelReader.readExcel(is);//-----------------------遍历数据到实体集合开始-----------------------------------

List listBean = new ArrayList();for (int i = 1; i < list.size(); i++) {//i=1是因为第一行不要

User uBean = newUser();

List listStr = list.get(i);for (int j = 0; j < listStr.size(); j++) {switch(j){case 0:uBean.setName(listStr.get(j));break;//第一列

case 1:uBean.setPassword(listStr.get(j));break;//第二列

case 2:uBean.setId(Integer.parseInt(listStr.get(j).substring(0,listStr.get(j).indexOf("."))));

}

}

listBean.add(uBean);

}//----------------------------遍历数据到实体集合结束----------------------------------

returnlistBean;

}/**

* Excel读取 操作*/

public static List> readExcel(InputStream is)

throws IOException {

Workbook wb= null;try{

wb= WorkbookFactory.create(is);

}catch(FileNotFoundException e) {

e.printStackTrace();

}catch(InvalidFormatException e) {

e.printStackTrace();

}catch(IOException e) {

e.printStackTrace();

}/** 得到第一个sheet*/Sheet sheet= wb.getSheetAt(0);/** 得到Excel的行数*/

int totalRows =sheet.getPhysicalNumberOfRows();/** 得到Excel的列数*/

int totalCells = 0;if (totalRows >= 1 && sheet.getRow(0) != null) {

totalCells= sheet.getRow(0).getPhysicalNumberOfCells();

}

List> dataLst = new ArrayList>();/** 循环Excel的行*/

for (int r = 0; r < totalRows; r++) {

Row row=sheet.getRow(r);if (row == null)continue;

List rowLst = new ArrayList();/** 循环Excel的列*/

for (int c = 0; c < totalCells; c++) {

Cell cell=row.getCell(c);

String cellValue= "";if (null !=cell) {