读取Excel文件到map
public static HashMap<Integer, List<String>> readExcel(String fileName) throws IOException {
HashMap<Integer, List<String>> map = new HashMap<>();
FileInputStream fis = null;
Workbook workbook = null;
try{
fis = new FileInputStream(fileName);
if(fileName.endsWith(".xlsx")){
workbook = new XSSFWorkbook(fis);
}else if(fileName.endsWith(".xls") || fileName.endsWith(".et")){
workbook = new HSSFWorkbook(fis);
}
Sheet sheet = workbook.getSheetAt(0);
Row row0 = sheet.getRow(0);
int length = row0.getLastCellNum();
Iterator<Row> rowIterator = sheet.rowIterator();
Row row;
while(rowIterator.hasNext()){
List<String> r = new ArrayList<String>();
row = rowIterator.next();
if(row.getRowNum() == 0)continue;;
for (int i = 0; i < length; i++) {
String cellValue = getCellValue(row.getCell(i,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK));
r.add(cellValue);
}
map.put(row.getRowNum(),r);
}
}catch (Exception e){
}finally {
if(fis != null){
fis.close();
}
if(workbook != null){
workbook.close();
}
}
return map;
}
读取每个单元格的值
private static String getCellValue(Cell cell) {
String cellValue = "";
if (null != cell) {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
if (0 == cell.getCellType()) {
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
cellValue = getFormat().format(date);
} else {
DecimalFormat df = new DecimalFormat("####.####");
cellValue = df.format(cell.getNumericCellValue());
}
}
break;
case HSSFCell.CELL_TYPE_NUMERIC:
cellValue = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
cellValue = cell.getBooleanCellValue() + "";
break;
case HSSFCell.CELL_TYPE_FORMULA:
cellValue = String.valueOf(Double.valueOf(cell.getNumericCellValue()));
break;
case HSSFCell.CELL_TYPE_BLANK:
cellValue = "";
break;
case HSSFCell.CELL_TYPE_ERROR:
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
}
return cellValue;
}
将读取的excel反射创建对象
public static <T> List<T> reflectCrtObj(HashMap<Integer,List<T>> map,Class<T> clazz){
ArrayList<T> res = new ArrayList<>();
if (map.isEmpty())return res;
map.forEach((rowNum,rowData)->{
try{
T bean = clazz.newInstance();
Field[] fields = clazz.getDeclaredFields();
for (int i = 0; i < fields.length; i++) {
Field field = fields[i];
field.setAccessible(true);
field.set(bean,rowData.get(i));
}
res.add(bean);
}catch (Exception e){
e.printStackTrace();
}
});
return res;
}
完整代码
package poi;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.FileInputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
public class ExcelUtil {
public static HashMap<Integer, List<String>> readExcel(String fileName) throws IOException {
HashMap<Integer, List<String>> map = new HashMap<>();
FileInputStream fis = null;
Workbook workbook = null;
try{
fis = new FileInputStream(fileName);
if(fileName.endsWith(".xlsx")){
workbook = new XSSFWorkbook(fis);
}else if(fileName.endsWith(".xls") || fileName.endsWith(".et")){
workbook = new HSSFWorkbook(fis);
}
Sheet sheet = workbook.getSheetAt(0);
Row row0 = sheet.getRow(0);
int length = row0.getLastCellNum();
Iterator<Row> rowIterator = sheet.rowIterator();
Row row;
while(rowIterator.hasNext()){
List<String> r = new ArrayList<String>();
row = rowIterator.next();
if(row.getRowNum() == 0)continue;;
for (int i = 0; i < length; i++) {
String cellValue = getCellValue(row.getCell(i,Row.MissingCellPolicy.CREATE_NULL_AS_BLANK));
r.add(cellValue);
}
map.put(row.getRowNum(),r);
}
}catch (Exception e){
}finally {
if(fis != null){
fis.close();
}
if(workbook != null){
workbook.close();
}
}
return map;
}
private static String getCellValue(Cell cell) {
String cellValue = "";
if (null != cell) {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
if (0 == cell.getCellType()) {
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
cellValue = getFormat().format(date);
} else {
DecimalFormat df = new DecimalFormat("####.####");
cellValue = df.format(cell.getNumericCellValue());
}
}
break;
case HSSFCell.CELL_TYPE_NUMERIC:
cellValue = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
cellValue = cell.getBooleanCellValue() + "";
break;
case HSSFCell.CELL_TYPE_FORMULA:
cellValue = String.valueOf(Double.valueOf(cell.getNumericCellValue()));
break;
case HSSFCell.CELL_TYPE_BLANK:
cellValue = "";
break;
case HSSFCell.CELL_TYPE_ERROR:
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
}
return cellValue;
}
public static <T> List<T> reflectCrtObj(HashMap<Integer,List<T>> map,Class<T> clazz){
ArrayList<T> res = new ArrayList<>();
if (map.isEmpty())return res;
map.forEach((rowNum,rowData)->{
try{
T bean = clazz.newInstance();
Field[] fields = clazz.getDeclaredFields();
for (int i = 0; i < fields.length; i++) {
Field field = fields[i];
field.setAccessible(true);
field.set(bean,rowData.get(i));
}
res.add(bean);
}catch (Exception e){
e.printStackTrace();
}
});
return res;
}
private static final SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
private static SimpleDateFormat getFormat(){
return format;
}
}