根据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）成功打印
最后，欢迎留言交流吐槽。。。