excel导入通用代码
导入模板的样式：
1:自定义注解
/** * 用于注解需要插入的对象 */ @Documented @Retention(RetentionPolicy.RUNTIME) @Target(ElementType.TYPE) public @interface Excel { } /** * 用于注解对象的属性 */ @Target(ElementType.FIELD) @Retention(RetentionPolicy.RUNTIME) @Documented public @interface Column { /** * 单元格对应的列号，必须指明，不能重复 * * @return */ public int column() default -1; /** * 对应的名字、描述 * * @return */ public String description() default "no description"; /** * 单元格内容是否可为空 * true：可以为空，false:不能为空 * @return */ public boolean nullable() default true; }
2：使用注解
@Data @Excel public class CreateReservoirDto { @Column(column = 0,nullable = false,description = "测站名字") private String name; @Column(column = 1,nullable = false,description = "测站编码") private String number; @Column(column = 2,nullable = false,description = "东经") private String lng; @Column(column = 3,nullable = false,description = "北纬") private String lat; @Column(column = 4,description = "地区") private String address; @Column(column = 5,description = "坝顶高程") private Double damTop; @Column(column = 6,description = "堰顶高程") private Double weirTop; @Column(column = 7,description = "汛限水位") private Double floodWaterLevel; @Column(column = 8,description = "校核洪水位") private Double checkFloodWaterLevel; @Column(column = 9,description = "设计洪水位") private Double designFloodWaterLevel; @Column(column = 10,description = "正常蓄水位") private Double normalStoreWater; @Column(column = 11,description = "死水位") private Double deadWaterLevel; @Column(column = 12,description = "总库容") private Double totalStorage; @Column(column = 13,description = "兴利库容") private Double utilizableCapacity; @Column(column = 14,description = "死库容") private Double deadStorage; @Column(column = 15,description = "创建时间") private Long createdTime; }
3：导入转转对象的工具类
import com.irrigation.icl.exception.ContextRuntimeException; import com.irrigation.synthetic.annotation.Column; import com.irrigation.synthetic.annotation.Excel; import lombok.extern.slf4j.Slf4j; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.util.StringUtils; import org.springframework.web.multipart.MultipartFile; import java.io.IOException; import java.io.InputStream; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.util.*; @Slf4j public class ExcelUtils { /** * 将excel数据转换为javaBean * * @param file excel文件 * @param startRow 导入内容的开始行号（排除表头） * @param clazz 要转换的对象 * @param <T> * @return */ public static <T> List<T> getDataFromExcel(MultipartFile file, int startRow, Class<T> clazz) { List<T> list = new ArrayList<>(); InputStream is = null; try { //创建工作簿 is = file.getInputStream(); String excelFileName = file.getOriginalFilename(); Workbook workbook = createWorkbook(is, excelFileName); //创建工作表sheet Sheet sheet = getSheet(workbook, 0); //获取sheet中数据的行数 int rows = sheet.getPhysicalNumberOfRows(); //获取表头单元格个数 int cells = sheet.getRow(0).getPhysicalNumberOfCells(); //判断是否使用excel注解 if (!clazz.isAnnotationPresent(Excel.class)) { log.error(clazz + "没有使用@Excel注解"); return null; } //利用反射，给JavaBean的属性进行赋值 Field[] fields = clazz.getDeclaredFields(); Map<Integer, Field> fieldMap = new HashMap<>(); if (null != fields && fields.length > 0) { for (Field field : fields) { if (field.isAnnotationPresent(Column.class)) { Column column = field.getAnnotation(Column.class); int number = column.column(); if (number < 0) { log.error(field.getName() + "没有使用@column注解"); return null; } else { fieldMap.put(number, field); } } } } //包装对象 for (int i = startRow; i < rows; i++) { Row row = sheet.getRow(i); int index = 0; T instance = clazz.newInstance(); while (index < cells) { Field field = fieldMap.get(index); if (null != field) { //获得单元格 Cell cell = row.getCell(index); Column column = field.getAnnotation(Column.class); Object value = null; if (Integer.class.equals(field.getType()) || Double.class.equals(field.getType())) { if(cell!=null&&cell.getCellTypeEnum()!=CellType.BLANK){ cell.setCellType(CellType.NUMERIC); value =cell.getNumericCellValue(); } } else if (String.class.equals(field.getType())) { if(cell!=null&&cell.getCellTypeEnum()!=CellType.BLANK){ cell.setCellType(CellType.STRING); value = cell.getStringCellValue(); } } else if (Date.class.equals(field.getType())) { if(cell!=null&&cell.getCellTypeEnum()!=CellType.BLANK){ cell.setCellType(CellType.STRING); value = cell.getDateCellValue(); } } else if (Boolean.class.equals(field.getType())) { if(cell!=null&&cell.getCellTypeEnum()!=CellType.BLANK){ cell.setCellType(CellType.BOOLEAN); value = cell.getBooleanCellValue(); } } else { log.error("单元格类型错误"); return null; } //不能为空 if (!column.nullable()) { if (StringUtils.isEmpty(value)) { throw new ContextRuntimeException("第" + i+1 + "行第" + index+1 + "列不能为空"); } } //设置属性值 if(!StringUtils.isEmpty(value)){ String fieldName = field.getName(); String methodName = "set" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1); //获得set方法的名字 Method setMethod = clazz.getMethod(methodName, new Class[]{field.getType()}); setMethod.invoke(instance, new Object[]{value}); } } index++; } list.add(instance); } } catch (ContextRuntimeException e) { throw e; } catch (Exception e) { log.error(e.getMessage()); } finally { try { if (null != is) { is.close();//关闭流 } } catch (Exception e2) { log.error(e2.getMessage()); } } return list; } /** * 创建工作簿 * * @param is * @param excelFileName * @return * @throws IOException */ private static Workbook createWorkbook(InputStream is, String excelFileName) throws IOException { if (null != excelFileName) { if (excelFileName.endsWith(".xls")) { return new HSSFWorkbook(is); } else if (excelFileName.endsWith(".xlsx")) { return new XSSFWorkbook(is); } } return null; } /** * 创建工作表 * * @param workbook * @param sheetIndex * @return */ private static Sheet getSheet(Workbook workbook, int sheetIndex) { return workbook.getSheetAt(0); } }
4：controller层
@ApiOperation(value = "导入excel", notes = "导入excel") @ApiImplicitParam(name = "irriAreaId", value = "灌区id", paramType = "query", dataType = "int", required = true) @PostMapping("/import") public RestResult<String> importReservoir(@RequestParam("file") MultipartFile file, @RequestParam("irriAreaId") Integer irriAreaId) { try { reservoirService.importReservoir(file, irriAreaId); return RestResultGeneratorUtil.getSuccessResult(); } catch (ContextRuntimeException e) { throw e; } catch (Exception e) { throw new ContextRuntimeException(ErrorEnum.IMPORT_DATA_ERROR.getMessage()); } }
5：service层
@Override public void importReservoir(MultipartFile file, Integer irriAreaId) { //excel表中每一列对应数据字段的属性 List<CreateReservoirDto> createReservoirDtoList = ExcelUtils.getDataFromExcel(file,1, CreateReservoirDto.class); if (!CollectionUtils.isEmpty(createReservoirDtoList)) { createReservoirDtoList = createReservoirDtoList.parallelStream().map(r -> { r.setIrriAreaId(irriAreaId); r.setCreatedTime(System.currentTimeMillis()); return r; }).collect(Collectors.toList()); //批量写入数据库 reservoirMapper.batchInsert(createReservoirDtoList); } }
