- excel模板格式
- 创建一个对象,用于封装导入的数据
package com.ha.elevator.base.vo; import com.ha.common.core.annotation.Excel; import java.io.Serializable; /** * Vo 类导入对象 */ @Data public class ImportExcelVo implements Serializable { /** * 序号,用于对列表重新排序使用 */ private Integer serial; /** * 小区名称 */ private String neighborhoodName; /** * 小区地址 */ private String neighborhoodAddress; /** * 小区编号 */ private String neighborhoodNo; /** * 物业公司名称 */ private String propertyName; /** * 物业联系人名称 */ private String propertyContactName; /** * 物业联系人电话 */ private String propertyContactPhone; /** * 小区负责人 */ private String principalName; /** * 小区负责人电话 */ private String principalPhone; /** * 楼栋名称 */ private String buildingName; /** * 单元名称 */ private String unitName; /** * 楼层 */ private String floorName; /** * 房门编号 */ private String roomNo; /** * 业主名称 */ private String ownerName; /** * 业主手机号 */ private String ownerPhone; }
- 上传文件到后台去解析数据,后台接口
@PostMapping(value = "/importExcel") public AjaxResult importExcel(MultipartFile file) { try { if (file == null) { return error("请上传文件"); } Workbook wb = null; //判断类型 if (file.getOriginalFilename().endsWith(".xls")) wb = new HSSFWorkbook(file.getInputStream()); if (file.getOriginalFilename().endsWith(".xlsx")) wb = new XSSFWorkbook(file.getInputStream()); int sheets = wb.getNumberOfSheets(); List<ImportExcelVo> importExcelVoList = new ArrayList<>(); //循环表格sheet表单数量 for (int i = 0; i < sheets; i++) { Sheet sheet = wb.getSheetAt(i); if (null == sheet) continue; //舍弃第一行表头数据 for (int j = sheet.getFirstRowNum() + 1; j <= sheet.getLastRowNum(); j++) { ImportExcelVo excelVo = new ImportExcelVo(); excelVo.setSerial(j); //获取当前行 Row row = sheet.getRow(j); if (null == row) continue; //遍历行所有的列 int firstCellNum = row.getFirstCellNum(); int lastCellNum = row.getLastCellNum(); //列数 for (int k = firstCellNum; k < lastCellNum; k++) { //获取单元格的值,不管是否合并的单元格,都可以获取 String value = getSheelValue(sheet, j, k); //把当前列的值赋值给对应的属性字段 if (k == 0) excelVo.setNeighborhoodName(value); if (k == 1) excelVo.setNeighborhoodAddress(value); if (k == 2) excelVo.setNeighborhoodNo(value); if (k == 3) excelVo.setPropertyName(value); if (k == 4) excelVo.setPropertyContactName(value); if (k == 5) excelVo.setPropertyContactPhone(value); if (k == 6) excelVo.setPrincipalName(value); if (k == 7) excelVo.setPrincipalPhone(value); if (k == 8) excelVo.setBuildingName(value); if (k == 9) excelVo.setUnitName(value); if (k == 10) excelVo.setFloorName(value); if (k == 11) excelVo.setRoomNo(value); if (k == 12) excelVo.setOwnerName(value); if (k == 13) excelVo.setOwnerPhone(value); } importExcelVoList.add(excelVo); } } //判断手机号是否有重复的 Map<String, Long> collect = importExcelVoList.stream().map(ImportExcelVo::getOwnerPhone).collect( Collectors.groupingBy(Function.identity(), Collectors.counting())); List<String> list = new ArrayList<>(); for (String s : collect.keySet()) { if (collect.get(s) > 1) { list.add(s); } } if (list.size() > 0) { String collect1 = list.stream().collect(Collectors.joining(",")); return error("业主手机号码重复:" + collect1); } //业务逻辑处理...... return success(); } catch (Exception e) { e.printStackTrace(); return error("excel解析数据异常......"); } } /** * 获取单元格内容,包括合并单元格的 * * @param sheet sheet表单 * @param row 当前行下标 * @param col 当前列下标 * @return */ public String getSheelValue(Sheet sheet, int row, int col) { int mergedRegions = sheet.getNumMergedRegions(); for (int i = 0; i < mergedRegions; i++) { CellRangeAddress region = sheet.getMergedRegion(i); //行开始下标 int firstRow = region.getFirstRow(); //行结束下标 int lastRow = region.getLastRow(); //列开始下标 int firstColumn = region.getFirstColumn(); //列结束下标 int lastColumn = region.getLastColumn(); if (row >= firstRow && row <= lastRow) { if (col >= firstColumn && col <= lastColumn) { Row fRow = sheet.getRow(firstRow); Cell cell = fRow.getCell(firstColumn); //所有内容已字符串形式处理 cell.setCellType(CellType.STRING); return cell.getStringCellValue(); } } } Cell cell = sheet.getRow(row).getCell(col); //所有内容已字符串形式处理 cell.setCellType(CellType.STRING); return cell.getStringCellValue(); }
- 处理完合并单元格之后的结果,跟以下图的结果导入是一样的