很多时候我们需要导入数据,读取excel的时候一个表头一个表头的对应,贼烦
搞一个直接导入成javaBeans的
package com.physicalpoint.ahim.utils;
import com.google.common.base.Preconditions;
import com.physicalpoint.ahim.vo.DepartmentExcelVo;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.WordUtils;
import org.apache.commons.lang.time.DateUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.springframework.web.multipart.MultipartFile;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import static com.google.common.base.Preconditions.checkArgument;
import static org.slf4j.LoggerFactory.getLogger;
/**
* excel文件解析
* 传入excel 文件 文件表头 example :FIELD_NAME/注释
* 传入转换后实体的实例
* <p>
* Create by ldl on 2019/8/16.
*/
public class ExcelUtils {
static Logger logger = getLogger(ExcelUtils.class);
public static <T> List<T> fillData(File excelFile, Class<T> clazz) throws FileNotFoundException {
return fillData(new FileInputStream(excelFile), clazz);
}
public static <T> List<T> fillData(MultipartFile excelFile, Class<T> clazz) throws IOException {
return fillData(excelFile.getInputStream(), clazz);
}
private static <T> List<T> fillData(InputStream io, Class<T> clazz) {
checkArgument(io != null, "Excel文件不能为空");
XSSFWorkbook wb = null;
List<T> dataList = new ArrayList<>();//定义数据列表集合
try {
wb = new XSSFWorkbook(io);//创建对象
XSSFSheet sheet = wb.getSheetAt(0);
XSSFRow rows = sheet.getRow(0);//得到第一行,匹配数据表列
int colNums = sheet.getLastRowNum();//显示展示条数
Iterator<Cell> cellIterator = rows.cellIterator();//得到第一行列数据集合
List<String> cols = new ArrayList<>();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
checkArgument(cell != null && (StringUtils.isNotEmpty(cell.getStringCellValue().trim())), "表头信息不能为空");
cols.add(cell.getStringCellValue().split(EXCEL_HEAD_LINK)[0]);
}
for (int i = 1; i <= colNums; i++) {// 第二行开始为数据集
XSSFRow row = sheet.getRow(i);//获取行
if (row != null) {
dataList.add(getCellData(row, clazz, cols));//解析每一行添加到集合
}
}
} catch (IOException e) {
logger.error(e.getMessage(), e);
} finally {
try {
wb.close();
io.close();
} catch (IOException e) {
logger.error(e.getMessage(), e);
}
}
return dataList;
}
private static <T> T getCellData(XSSFRow row, Class<T> clazz, List<String> cols) {
try {
Object obj = clazz.newInstance();
for (int i = 0; i < cols.size(); i++) {
String key = cols.get(i);
XSSFCell cell = row.getCell(i);
try {
Field field = obj.getClass().getDeclaredField(col2prop(key));
Class[] clazzArr = {field.getType()};
Method method = clazz.getDeclaredMethod(findSetMethodName(key), clazzArr);
Object argVal = getValue(cell);// 类型不同
if (field.getType() == Date.class) {
argVal = DateUtils.parseDate(argVal.toString(), new String[]{DATE_TIME_EXPRESSION_GENERAL});
} else if (field.getType() != String.class) {
Method valMethod = field.getType().getDeclaredMethod("valueOf", String.class);
argVal = valMethod.invoke(String.class, argVal.toString());
}
method.invoke(obj, argVal);
} catch (Exception e) {
logger.error(e.getMessage(), e);
}
}
return (T) obj;
} catch (Exception e) {
logger.error(e.getMessage(), e);
}
return null;
}
private static String COL_NAME_LINK = "_";
private static String EXCEL_HEAD_LINK = "/";
private static String DATE_TIME_EXPRESSION_GENERAL = "yyyy-MM-dd HH:mm:ss";
private static String col2prop(String colName) {
Preconditions.checkArgument(StringUtils.isNotEmpty(colName));
StringBuffer strbuf = new StringBuffer();
String[] arr = colName.toLowerCase().split(COL_NAME_LINK);
for (int i = 0; i < arr.length; i++) {
String str = arr[i];
if (i == 0) {
strbuf.append(str);
} else {
strbuf.append(WordUtils.capitalize(str));
}
}
return strbuf.toString();
}
private static String findSetMethodName(String colName) {
return "set" + WordUtils.capitalize(col2prop(colName));
}
public static String getValue(Cell hssfCell) {
DecimalFormat df = new DecimalFormat("#");
if (hssfCell == null)
return "";
CellType cellType = hssfCell.getCellTypeEnum();
if (cellType.equals(CellType.NUMERIC)) {
if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {
SimpleDateFormat sdf = new SimpleDateFormat(DATE_TIME_EXPRESSION_GENERAL);
return sdf.format(HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue())).toString();
}
return df.format(hssfCell.getNumericCellValue());
} else if (cellType.equals(CellType.STRING)) {
return hssfCell.getStringCellValue();
} else if (cellType.equals(CellType.FORMULA)) {
return hssfCell.getCellFormula();
} else if (cellType.equals(CellType.BOOLEAN)) {
return hssfCell.getBooleanCellValue() + "";
} else {
return "";
}
}
/*
public static void main(String[] args) throws FileNotFoundException {
List<DepartmentExcelVo> list= ExcelUtils.fillData(new File("C:\\Users\\hasee\\Desktop\\ybxsso\\测试数据.xlsx"), DepartmentExcelVo.class);
}*/
}
有个要求就是表头 下划线驼峰哦
java 实体类
import io.swagger.annotations.ApiModelProperty;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
/**
* Create by ldl on 2019/8/16.
*/
@Getter
@Setter
@ToString
public class DepartmentExcelVo {
@ApiModelProperty("序号")
private String serialNumber;
@ApiModelProperty("科室名称")
private String departmentName;
@ApiModelProperty("科室代码")
private String departmentCode;
}