javaspringbootpoi从controller接收不同类型excel文件处理

    
    
      org.apache.poi
      poi
      3.17
    
    
      org.apache.poi
      poi-ooxml
      3.17
    

import com.guard.biz.common.util.excel.ExcelIn;
import org.apache.commons.beanutils.BeanUtilsBean;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.reflect.FieldUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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 org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
 * @author Wei
 * @time 2019/10/29
 * @Description excel 导入工具类
 */
public class ExcelImportUtil {
  private static final Logger log = LoggerFactory.getLogger(ExcelImportUtil.class);
  private static BeanUtilsBean beanUtilsBean = new BeanUtilsBean();
  static {
    beanUtilsBean.getConvertUtils().register(new org.apache.commons.beanutils.converters.DateConverter(null), java.util.Date.class);
  }
  /**
   * 表头名字和对应所在第几列的下标，用于根据title取到对应的值
   */
  private final Map title_to_index = new HashMap<>();
  /**
   * 所有带有ExcelIn注解的字段
   */
  private final List 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) throws Exception {
    gatherAnnotationFields(clazz);
    configSheet(in);
    configHeader();
    List rList = null;
    try {
      rList = readContent(clazz);
    } catch (IllegalAccessException e) {
      throw new Exception(e);
    } catch (InstantiationException e) {
      throw new Exception(e);
    } catch (InvocationTargetException e) {
      throw new Exception(e);
    }
    return rList;
  }
  private List readContent(Class clazz) throws IllegalAccessException, InstantiationException, InvocationTargetException {
    Object o = null;
    Row row = null;
    List rsList = new ArrayList<>();
    Object value = null;
    for (int i = (firstRowNum + 1); i <= lastRowNum; i++) {
      o = clazz.newInstance();
      row = sheet.getRow(i);
      Cell cell = null;
      for (Field field : fields) {
        //根据注解中的title，取到表格中该列所对应的的值
        Integer column = title_to_index.get(field.getAnnotation(ExcelIn.class).title());
        if (column == null) {
          continue;
        }
        cell = row.getCell(column);
        value = getCellValue(cell);
        if (null != value && StringUtils.isNotBlank(value.toString())) {
          beanUtilsBean.setProperty(o, field.getName(), value);
        }
      }
      rsList.add(o);
    }
    return rsList;
  }
  private void configSheet(InputStream in) throws Exception {
    // 根据文件类型来分别创建合适的Workbook对象
    try (Workbook wb = WorkbookFactory.create(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 

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
 * @author Lei
 * @time 2019/10/29
 * @Description
 */
@Retention(value = RetentionPolicy.RUNTIME)
@Target(value = {ElementType.TYPE, ElementType.FIELD})
public @interface ExcelIn {
  /**
   * 导入sheet名称
   *
   * @return
   */
  String sheetName() default "";
  /**
   * 字段对应的表头名称
   *
   * @return
   */
  String title() default "";
}

import lombok.Data;
import lombok.ToString;
import java.util.Date;
/**
 * @author Lei
 * @time 2019/10/29
 * @Description
 */
@ToString
@Data
@ExcelIn(sheetName = "用户")
public class User {
  private String id;
  @ExcelIn(title = "姓名")
  private String name;
  @ExcelIn(title = "年龄")
  private Integer age;
  @ExcelIn(title = "出生日期")
  private Date birthDate;
}

@PostMapping("/batch/excel")
  @ApiOperation(value = "根据excel文件批量导入")
  public ResponseVO batchAddDeviceByExcelImport(MultipartFile multipartFile) {
    return new ResponseVO(deviceService.addDeviceByExcelImport(multipartFile));
  }

public boolean addDeviceByExcelImport(MultipartFile multipartFile) {
    File file = null;
    try {
      file = File.createTempFile("temp", null);
    } catch (IOException e) {
      e.printStackTrace();
    }
    try {
      multipartFile.transferTo(file);
    } catch (IOException e) {
      e.printStackTrace();
    }
    file.deleteOnExit();
    InputStream inputStream = null;
    try {
      inputStream = new FileInputStream(file);
    } catch (FileNotFoundException e) {
      e.printStackTrace();
    }
    ExcelImportUtil reader = new ExcelImportUtil<>();
    List userList = null;
    try {
      userList = reader.read(inputStream, User.class);
    } catch (Exception e) {
      log.error(e.getMessage());
      throw new CodeException("51302", e.getMessage());
    }
      userList.stream().forEach(e -> log.info(e.toString()));
    return true;
  }