java springboot poi 从controller 接收不同类型excel 文件处理

    <!-- poi 操作Excel -->    <dependency>      <groupId>org.apache.poi</groupId>      <artifactId>poi</artifactId>      <version>3.17</version>    </dependency>    <dependency>      <groupId>org.apache.poi</groupId>      <artifactId>poi-ooxml</artifactId>      <version>3.17</version>    </dependency>

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<T> {  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<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<T> 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<Object> 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 < 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 Exception("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注解字段");    }  }  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;    }    return obj;  }  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 org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);    } else {      return numericToBigDecimal(cell);    }  }  private Object numericToBigDecimal(Cell cell) {    String valueOf = String.valueOf(cell.getNumericCellValue());    BigDecimal bd = new BigDecimal(valueOf);    return bd;  }}

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<User> reader = new ExcelImportUtil<>();    List<User> 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;  }