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

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

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

1.pom引入

   <!-- 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>

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<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;
 }
}

 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<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;
 }

7.测试

（1）两种文件类型的excel

 （2）excel中格式如下，注意红色箭头所指的地方 对应user对象中的字段以及sheet名

 （3）swagger测试

（4）成功打印

总结

以上所述是小编给大家介绍的java springboot poi 从controller 接收不同类型excel 文件处理,网站的支持！
如果你觉得本文对你有帮助，欢迎转载，烦请注明出处，谢谢！

来源：https://www.cnblogs.com/wei57960/archive/2019/10/30/11763020.html