一开始我使用阿里巴巴 easyexcel 进行操作,觉得太繁琐了,觉得麻烦,然后我就用easypoi-base ,感觉挺不错了,操作简单方便。话不多说直接上代码:
一、首先相关的依赖
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.2.0</version>
<exclusions>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
</exclusion>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
</exclusion>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
</exclusion>
</exclusions>
</dependency>
二、读取excel数据工具类
import org.apache.commons.beanutils.BeanUtilsBean;
import org.apache.commons.beanutils.converters.DateConverter;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.reflect.FieldUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.util.*;
import java.util.regex.Pattern;
/**
* 导入excel工具类
* @author ZHY
* @param <T>
*/
public class ExcelReader<T> {
private static BeanUtilsBean beanUtilsBean = new BeanUtilsBean();
static {
beanUtilsBean.getConvertUtils().register(new DateConverter(null), Date.class);
}
/**
* 表头名字和对应所在第几列的下标,用于根据title取到对应的值
*/
private final Map<String,Integer> title_to_index = new HashMap<>();
/**
* 所有带有ExcelIn注解的字段
*/
private final List<Field> fields = new ArrayList<>();
/**
* 统计表格的行和列数量用来遍历表格
*/
private int firstCellNum = 0;
private int lastCellNum = 0;
private int firstRowNum = 0;
private int lastRowNum = 0;
private String sheetName ;
private Sheet sheet ;
public List read(InputStream in , Class clazz,String fileName) throws Exception {
/**
* 获取封装得实体得字段注解名称
*/
gatherAnnotationFields(clazz);
/**
*
*/
configSheet(in,fileName);
/**
* 表头配置
*/
configHeader();
/**
* 读取sheet内容
*/
List rList = readContent(clazz);
return rList ;
}
/**
* 读取内容值
* @param clazz
* @return
* @throws HandException
* @throws IllegalAccessException
* @throws InstantiationException
* @throws InvocationTargetException
*/
private List readContent(Class clazz) throws HandException, IllegalAccessException, InstantiationException, InvocationTargetException {
Object o = null ;
List<Object> rsList = new ArrayList<>();
for(int i = (firstRowNum+1);i<=lastRowNum;i++){
o = clazz.newInstance();
Row row = sheet.getRow(i);
beanUtilsBean.setProperty(o,"rowNum",i+1);
for (Field field : fields) {
//根据注解中的title,取到表格中该列所对应的的值
ExcelIn annotation = field.getAnnotation(ExcelIn.class);
Integer column=title_to_index.get(annotation.title());
if(column==null){
continue;
}
Cell cell = row.getCell(column);
Object value = getCellValue(cell);
//验证必填项
isRequired((i+1),column+1,annotation,value);
if(null != value && StringUtils.isNotBlank(value.toString())) {
beanUtilsBean.setProperty(o, field.getName(), value);
}
}
rsList.add(o);
}
return rsList ;
}
/**
* 验证是否为空
* @param rowNum
* @param cellNum
* @param annotation
* @param value
* @throws HandException
*/
private void isRequired(int rowNum,int cellNum,ExcelIn annotation,Object value) throws HandException {
boolean required =annotation.required();
String msg =annotation.msg();
String title =annotation.title();
String regex =annotation.regex();
if( required && (null == value || StringUtils.isBlank(value.toString()))){
if(title.indexOf("*")!=-1){
title=title.replace("*","");
}
throw new HandException("第"+rowNum+"行"+"第"+cellNum+"列"+title+"不能为空");
}
if(StringUtils.isNotBlank(regex)){
if(!Pattern.matches(regex,value.toString())){
throw new HandException("第"+rowNum+"行"+"第"+cellNum+"列"+msg);
}
}
}
/**
* 判断是否是这两种类型文件
* @param in
* @param fileName
* @throws Exception
*/
private void configSheet(InputStream in,String fileName) throws Exception {
//HSSFWorkbook:只能创建97-03版本的Excel,即:以xls结尾的Excel
// 想要导入xlsx结尾的Excel,用XSSFWorkbook
String ext = fileName.substring(fileName.lastIndexOf("."));
if (".xls".equals(ext)) {
try(Workbook wb = new HSSFWorkbook(in)){
getSheetByName(wb);
} catch (FileNotFoundException e) {
throw new Exception(e);
} catch (IOException e) {
throw new Exception(e);
}
} else if (".xlsx".equals(ext)) {
try(Workbook wb = new XSSFWorkbook(in)){
getSheetByName(wb);
} catch (FileNotFoundException e) {
throw new Exception(e);
} catch (IOException e) {
throw new Exception(e);
}
}
}
/**
* 根据sheet获取对应的行列值,和表头对应的列值映射
*/
private void configHeader(){
this.firstRowNum = sheet.getFirstRowNum() ;
this.lastRowNum = sheet.getLastRowNum() ;
//第一行为表头,拿到表头对应的列值
Row row = sheet.getRow(firstRowNum);
this.firstCellNum = row.getFirstCellNum();
this.lastCellNum = row.getLastCellNum();
for (int i = firstCellNum;i<lastCellNum;i++){
title_to_index.put(row.getCell(i).getStringCellValue(),i);
}
}
/**
* 根据sheet名称获取sheet
* @param workbook
* @return
* @throws Exception
*/
private void getSheetByName(Workbook workbook) throws Exception {
int sheetNumber = workbook.getNumberOfSheets();
for (int i = 0; i < sheetNumber; i++) {
String name = workbook.getSheetName(i);
if(StringUtils.equals(this.sheetName,name)) {
this.sheet = workbook.getSheetAt(i);
return;
}
}
throw new HandException("excel中未找到名称为"+this.sheetName+"的sheet");
}
/**
* 根据自定义注解,获取所要导入表格的sheet名称和需要导入的字段名称
* @param clazz
* @throws Exception
*/
private void gatherAnnotationFields(Class clazz) throws Exception {
if(!clazz.isAnnotationPresent(ExcelIn.class)){
throw new Exception(clazz.getName()+"类上没有ExcelIn注解");
}
ExcelIn excelIn = (ExcelIn)clazz.getAnnotation(ExcelIn.class) ;
this.sheetName = excelIn.sheetName();
// 得到所有定义字段
Field[] allFields = FieldUtils.getAllFields(clazz) ;
// 得到所有field并存放到一个list中
for (Field field : allFields) {
if (field.isAnnotationPresent(ExcelIn.class)) {
fields.add(field);
}
}
if( fields.isEmpty()){
throw new Exception(clazz.getName()+"中没有ExcelIn注解字段");
}
}
/**
* 获取单元格得值
* @param cell
* @return
*/
private Object getCellValue(Cell cell) {
if (cell == null) {
return "";
}
Object obj = null;
switch (cell.getCellTypeEnum()) {
case BOOLEAN:
obj = cell.getBooleanCellValue();
break;
case ERROR:
obj = cell.getErrorCellValue();
break;
case FORMULA:
try {
obj = String.valueOf(cell.getStringCellValue());
} catch (IllegalStateException e) {
obj = numericToBigDecimal(cell);
}
break;
case NUMERIC:
obj = getNumericValue(cell);
break;
case STRING:
String value = String.valueOf(cell.getStringCellValue());
value = value.replace(" ", "");
value = value.replace("\n", "");
value = value.replace("\t", "");
obj = value;
break;
default:
break;
}
DataFormatter dataFormatter = new DataFormatter();
dataFormatter.addFormat("###########", null);
obj=dataFormatter.formatCellValue(cell);
return obj;
}
/**
*
* @param cell
* @return
*/
private Object getNumericValue(Cell cell){
// 处理日期格式、时间格式
if (HSSFDateUtil.isCellDateFormatted(cell)) {
return cell.getDateCellValue();
}else if (cell.getCellStyle().getDataFormat() == 58) {
// 处理自定义日期格式:m月d日(通过判断单元格的格式id解决,id的值是58)
double value = cell.getNumericCellValue();
return DateUtil.getJavaDate(value);
} else {
return numericToBigDecimal(cell);
}
}
/**
*
* @param cell
* @return
*/
private Object numericToBigDecimal(Cell cell) {
cell.setCellType(CellType.STRING);
return String.valueOf(cell.getRichStringCellValue().getString());
}
}
三、对应实体的注解
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD ,ElementType.TYPE})
public @interface ExcelIn {
/**
* 导入sheet名称
* @return
*/
String sheetName() default "";
/**
* 字段对应的表头名称
* @return
*/
String title() default "";
boolean required() default false;
/**
* 提示信息
* @return
*/
String msg() default "";
/**
* 正则匹配
* @return
*/
String regex() default "";
}
四、请求接收的实体
import cn.afterturn.easypoi.excel.annotation.Excel;
/**
* @author ZHY
* @since 2021/3/11 16:07
*/
@ExcelIn(sheetName = "员工信息导入模板")
public class StaffDTO {
@Excel(name = "手机号码*")
@ExcelIn(title = "手机号码*",required=true,regex = Regex.PHONE,msg = "手机号只能是11位")
private String cellPhone;
@Excel(name = "员工姓名*")
@ExcelIn(title = "员工姓名*",required=true)
private String userName;
public String getCellPhone() {
return cellPhone;
}
public void setCellPhone(String cellPhone) {
this.cellPhone = cellPhone;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
@Override
public String toString() {
return "StaffDTO{" +
"cellPhone='" + cellPhone + '\'' +
", userName='" + userName + '\'' +
'}';
}
}
五、业务操作流程
/**
* @param file
* @return
*/
@Override
public int importAddStaff(MultipartFile file) {
try {
InputStream is = file.getInputStream();
ExcelReader<StaffDTO> reader = new ExcelReader<>();
String fileName = file.getOriginalFilename();
//读取excel数据
List<StaffDTO> data = reader.read(is, StaffDTO.class, fileName);
Integer shopId = getUser().getShopId();
List<ShopUserInfo> infoList = new ArrayList<>();
for (StaffDTO e : data) {
ShopUserInfo shopUserInfo = shopUserInfoDao.selectByPhones(e.getCellPhone(), shopId);
if (shopUserInfo != null) {
logger.info("Staff importAddStaff fail,The query is exist according shopId and phone,shopId:{},phone:{}", shopId,e.getCellPhone());
continue;
}
if (!RegexUtils.isChinaUnicomPhoneNum(e.getCellPhone())) {
logger.info("Staff importAddStaff fail, Verify the mobile phone number does not conform to Unicom mobile phoneNumber,phone:{}",e.getCellPhone());
continue;
}
ShopUserInfo info = new ShopUserInfo();
info.setName(e.getUserName());
info.setPhone(e.getCellPhone());
info.setAuthStatus(2);
info.setShopId(shopId);
info.setMsgStatus(2);
info.setCreateTime(LocalDateTime.now());
infoList.add(info);
}
if (infoList.size() == 0) {
logger.info("Staff importAddStaff fail, Excluding according to Excel file import does not get the data that needs to be imported,file:{}",file.getOriginalFilename());
return 6;
}
return shopUserInfoDao.insertBatchUserInfo(infoList);
} catch (Exception e) {
logger.error("Failed to get employee import Excel file,{}", e.getMessage(), e);
}
return 0;
}
六、excel模板如图:
如果上面没有解决你的问题你留言稍后帮你解决