绵阳梓潼网站制作公司

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

根据poi接收controller层的excel文件导入

创新互联公司提供成都做网站、网站设计、外贸营销网站建设、网页设计，成都品牌网站建设，一元广告等致力于企业网站建设与公司网站制作，十载的网站开发和建站经验,助力企业信息化建设，成功案例突破1000多家,是您实现网站建设的好选择.

       可使用后缀名xls或xlsx格式的excel。

1.pom引入

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

2.ExcelImportUtil 工具类创建　

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 < 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;
  }
}
 3.ExcelIn注解
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 "";
}
 4.创建excel中的对象
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;
}
 5.controller层接收
@PostMapping("/batch/excel")
  @ApiOperation(value = "根据excel文件批量导入")
  public ResponseVO batchAddDeviceByExcelImport(MultipartFile multipartFile) {
    return new ResponseVO(deviceService.addDeviceByExcelImport(multipartFile));
  }
 6.service处理(此处仅打印)
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;
  }
7.测试
（1）两种文件类型的excel
java springboot poi 从controller 接收不同类型excel 文件处理
 （2）excel中格式如下，注意红色箭头所指的地方 对应user对象中的字段以及sheet名
java springboot poi 从controller 接收不同类型excel 文件处理
 （3）swagger测试
java springboot poi 从controller 接收不同类型excel 文件处理
（4）成功打印
java springboot poi 从controller 接收不同类型excel 文件处理
总结
以上所述是小编给大家介绍的java springboot poi 从controller 接收不同类型excel 文件处理,希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对创新互联网站的支持！
如果你觉得本文对你有帮助，欢迎转载，烦请注明出处，谢谢！
            
            
                            

                分享文章：javaspringbootpoi从controller接收不同类型excel文件处理                

                文章分享：http://myzitong.com/article/pejeij.html
            
        
        

            
其他资讯