利用excelpoi和excelexcel配合实现导入导出

​ 不知不觉也已经在一家初创公司实习了半年了，在领导和同事的帮助以及自己的不断学习下，也是终于算是“入行”了哈哈哈，xiaolin在公司主要负责平日的后端开发工作，由于系统需求，系统需要频繁地使用excel导入和导出为excel的功能，于是便有了将其封装为公共类的想法。借用同事的话说“学习学的不是代码，而是编程的思想”，所以也想总结下这个过程中的想法，小白的分享，有些表达不清晰或者代码有问题的地方欢迎沟通ouo

依赖

首先导入easyexcel，easypoi的依赖

<!-- Excel easypoi  -->
<dependency>
  <groupId>cn.afterturn</groupId>
  <artifactId>easypoi-spring-boot-starter</artifactId>
  <version>4.1.3</version>
</dependency>

<!-- easyexcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
</dependency>

导入

系统的导入为模版导入，可根据需求自定义模板，字典配合excel的数据验证功能也可达到下拉选择，避免用户手输程序找不到对应的字典值而出现异常，注意，如果列数据应为字符串类型的话，最好选中整列设置单元格类型为“文本”类型

导入的接收工具类，需要注意的是，ApiModelProperty的value一定要与导入excel文档的表头一样！！！

@ConvertToValue注解是xiaolin自己写的一个注解类，只有”dictType“和”isConvert“两个属性，后面在封装的程序中会利用发射拿到注解的属性值将拿到的字典值转换为键（数据库存放的都是键值，减少测盘开销的同时也便于数据库的管理）

/**
 * @Author xiaolin
 * 房间管理导入VO
 * @Date 2023/5/10 17:45
 */
@Data
public class RoomImportVO {

    @ApiModelProperty(value = "*房间号")
    private String roomNum;

    @ApiModelProperty(value = "*宿舍类型")
    @ConvertToValue(dictType = "base_room_type",isConvert = true)
    private String roomType;

    @ApiModelProperty(value = "具体地址")
    private String location;

    @ApiModelProperty(value = "*可容纳数量")
    private Integer capacity;

    @ApiModelProperty(value = "已容纳数量")
    private Integer alreadyAccommodated;

    @ApiModelProperty(value = "*状态")
    @ConvertToValue(dictType = "base_place_statue",isConvert = true)
    private String statue;

}

这是所用到的excel工具类

主要的思路是使用getExcelJSONArray这个方法将解析excel文件，得到一个存放Json的数组JsonArray

public class ExcelUtil {

    private static final String XLSX = ".xlsx";
    private static final String XLSM = ".xlsm";
    private static final String XLS = ".xls";
    public static final String ROW_NUM = "rowNum";
    private static final NumberFormat NUMBER_FORMAT = NumberFormat.getNumberInstance();

    /**
     * 根据上传的文件转成workbook对象
     * @param file
     * @return
     */
    public static Workbook getBookByMultipartFile(MultipartFile file) {
        // 解析表格数据
        InputStream in = null;
        String fileName;
        Workbook book = null;
        try {
            // 上传文件解析
            in = file.getInputStream();
            //获得文件后缀名
            fileName = file.getOriginalFilename();

            if (fileName.endsWith(XLSX)||fileName.endsWith(XLSM)) {
                book = new XSSFWorkbook(in);
            } else {
                POIFSFileSystem poifsFileSystem = new POIFSFileSystem(in);
                book = new HSSFWorkbook(poifsFileSystem);
            }
            return book;
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                book.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
            try {
                in.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    /**
     * 获取excel数据对象
     * @param file 文件
     * @param sheetNum Sheet页 下标
     * @param rowStart 首行 下标
     * @return JSONArray对象
     */
    public static JSONArray getExcelJSONArray(MultipartFile file,int sheetNum,int rowStart) {
        return read(getBookByMultipartFile(file),sheetNum,rowStart);
    }

    private static JSONArray read(Workbook book,int sheetNum,int rowStart) {
        // 根据sheetNum获取Excel的 Sheet 页面
        Sheet sheet = book.getSheetAt(sheetNum);
        return readSheet(sheet,rowStart);
    }

    private static JSONArray readSheet(Sheet sheet,int rowStart) {
        // 尾行下标
        int rowEnd = sheet.getLastRowNum();
        // 获取表头行
        Row headRow = sheet.getRow(rowStart);
        if (headRow == null) {
            return new JSONArray();
        }
        int cellStart = headRow.getFirstCellNum();
        int cellEnd = headRow.getLastCellNum();
        Map<Integer, String> keyMap = new HashMap<>();
        for (int j = cellStart; j < cellEnd; j++) {
            // 获取表头数据
            String val = (String) getCellValue(headRow.getCell(j));
            if (val != null && val.trim().length() != 0) {
                keyMap.put(j, val);
            }
        }
        // 如果表头没有数据则不进行解析
        if (keyMap.isEmpty()) {
            return (JSONArray) Collections.emptyList();
        }
        // 获取每行JSON对象的值
        JSONArray array = new JSONArray();
        // 如果首行与尾行相同，表明只有一行，返回表头数据
        if (rowStart == rowEnd) {
            JSONObject obj = new JSONObject();
            // 添加行号
            obj.put(ROW_NUM, 1);
            for (int i : keyMap.keySet()) {
                obj.put(keyMap.get(i), "");
            }
            array.add(obj);
            return array;
        }
        for (int i = rowStart + 1; i <= rowEnd; i++) {
            Row eachRow = sheet.getRow(i);
            JSONObject obj = new JSONObject();
            // 添加行号
            obj.put(ROW_NUM, i + 1);
            StringBuilder sb = new StringBuilder();
            for (int k = cellStart; k < cellEnd; k++) {
                if (eachRow != null) {
                    Object val = getCellValue(eachRow.getCell(k));
                    // 所有数据添加到里面，用于判断该行是否为空
                    sb.append(val);
                    obj.put(keyMap.get(k), val);
                }
            }
            if (sb.length() > 0) {
                array.add(obj);
            }
        }
        return array;
    }

    public static Object getCellValue(Cell cell) {
        // 空白或空
        if (cell == null || cell.getCellTypeEnum() == CellType.BLANK) {
            return "";
        }

        // String类型
        if (cell.getCellTypeEnum() == CellType.STRING) {
            String val = cell.getStringCellValue();
            if (val == null || val.trim().length() == 0) {
                return "";
            }
            return val.trim();
        }
        // 数字类型
        if (cell.getCellTypeEnum() == CellType.NUMERIC) {
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                return cell.getDateCellValue();
            }
            // 科学计数法类型
            return cell.getNumericCellValue() + "";
        }
        // 布尔值类型
        if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
            return cell.getBooleanCellValue() + "";
        }
        // 错误类型
        return cell.getCellFormula();
    }

    public static MultipartFile getMultipartFile(File file) {
        FileItem item = new DiskFileItemFactory().createItem("file"
                , MediaType.MULTIPART_FORM_DATA_VALUE
                , true
                , file.getName());
        try (InputStream input = new FileInputStream(file);
             OutputStream os = item.getOutputStream()) {
            // 流转移
            IOUtils.copy(input, os);
        } catch (Exception e) {
            throw new IllegalArgumentException("Invalid file: " + e, e);
        }

        return new CommonsMultipartFile(item);
    }

}

再循环这个JsonArray数组，解析每个Json对象，将Json对象的每个属性变为Object属性，再由程序过滤，将Object转为导入VO所需的属性，在转换过程中，不可避免地会出现用户填写的excel单元格格式和所需格式不符的情况，这时候不仅需要抛出异常还需要指明是哪一行的那一列出现的问题，可以通过ExcelUtil中的ROOM_NUM拿到行数，再用反射拿到@ApiModelProperty的value值，就能知道是哪一行列名为什么定位到这个单元格，方便用户改错。但由于转换的过程基本是一成不变的，所以在这里就利用泛型编写了一个公共类，代码如下：

/**
 * @Author xiaolin
 * @Date 2023/3/28 10:08
 */
public class HandleImport {

    public static <T> T handle(JSONObject row, Class<T> aclass) throws Exception {
        //实例化类
        T t = aclass.newInstance();
        if (row != null) {
            Field[] declaredFields = aclass.getDeclaredFields();
            for (Field field : declaredFields) {
                ApiModelProperty annotation = field.getAnnotation(ApiModelProperty.class);
                //通过反射拿到 ConvertToValue 注解
                ConvertToValue annotationByConvert = field.getAnnotation(ConvertToValue.class);

                field.setAccessible(true);
                Object value = row.get(annotation.value());

                //如果ConvertToValue注解存在 且 isConvert为true , 将传过来的标签转换为字典的键
                if (annotationByConvert != null && annotationByConvert.isConvert() && value != null) {
                    String filedName = field.getType().toString();
                    if (filedName.equals("interface java.util.List")){
                        try {
                            value = DictFrameworkUtils.parseDictDataValueToList(annotationByConvert.dictType(),  (String)value);
                        }catch (Exception e){
                            if (e instanceof RuntimeException){
                                throw exception(new ErrorCode(5000000,"第" + row.get(ExcelUtil.ROW_NUM) + "行《" + annotation.value() + "》不在字典范围内！"));
                            }
                        }
                    }else {
                        value = DictFrameworkUtils.parseDictDataValue(annotationByConvert.dictType(), (String)value);
                        if (value == null){
                            throw exception(new ErrorCode(5000000,"第" + row.get(ExcelUtil.ROW_NUM) + "行《" + annotation.value() + "》不在字典范围内！"));
                        }
                    }
                }

                if (value != null && value != "") {
                    //类型转换
                    if (field.getType().getTypeName().contains("BigDecimal")) {
                        String string = value.toString();
                        value = new BigDecimal(string);
                    } else if (field.getType().getTypeName().contains("Double")) {
                        String string = value.toString();
                        value = Double.parseDouble(string);
                    }else if (field.getType().getTypeName().contains("Integer")) {
                        String string = value.toString();
                        value = Integer.valueOf(string);
                    } else if (field.getType().getTypeName().equals("java.util.Date") && value instanceof String) {
                        try {
                            String str = value.toString();
                            SimpleDateFormat dd = new SimpleDateFormat("yyyy-MM-dd");

                            Date date = dd.parse(str);
                            value = date;
                        } catch (ParseException e) {
                            throw exception(new ErrorCode(3000000, "第" + row.get(ExcelUtil.ROW_NUM) + "行《" + annotation.value() + "》列数据格式不正确！"));
                        }
                    }
                    field.set(t, value);
                }
            }
        }
        return t;
    }

}

其中DictFrameworkUtils.parseDictDataValue方法为将单选的值转换为系统中字典的键，DictFrameworkUtils.parseDictDataValueToList为多选的值键转换，这个以后会谈

调用的时候，需要传入json对象和对应导入VO.class

T handle = HandleImport.handle(excelJsonArray.getJSONObject(i), aclass);

而且为了公共类的异常能被全局异常捕获，要在调用的那层判断异常类型

try {
    T handle = HandleImport.handle(excelJsonArray.getJSONObject(i), aclass);
    handlePlace.add(handle);
} catch (Exception e) {
    if (e instanceof ServiceException) {
        throw exception(new ErrorCode(((ServiceException) e).getCode(), e.getMessage()));
    }
    throw exception(new ErrorCode(3200404, "导入《" + fileName + "》失败"));
}

导出和多选部分下次分享~
导出部分：https://blog.csdn.net/Pikoan/article/details/131047300?spm=1001.2014.3001.5501