EasyExcel工具类

/**
 * EasyExcel工具类
 *
 * @author sunpy
 * @date 2023-03-08
 */
public class EasyExcelUtils<T> {

    /**
     * 导出excel
     *
     * @param response
     * @param fileName
     * @param sheetName
     * @param clazz
     * @param dataList
     * @param <T>
     * @throws IOException
     */
    public static <T> void exportExcel(HttpServletResponse response,
                                       String fileName,
                                       String sheetName,
                                       Class<T> clazz,
                                       List<T> dataList) throws IOException {

        response.setCharacterEncoding("UTF-8");
        // 告知浏览器字节流下载
        response.setContentType("application/octet-stream");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));

        ExcelWriterBuilder excelWriterBuilder = new ExcelWriterBuilder();

        ExcelWriterSheetBuilder sheet = excelWriterBuilder.excelType(ExcelTypeEnum.XLSX)
                .file(response.getOutputStream())
                .autoCloseStream(true)
                .head(clazz)
                .sheet(sheetName);
        sheet.doWrite(dataList);
    }

    public static <T> List<T> importExcel(HttpServletRequest request,
                                          Class<T> clazz) throws IOException {
        return importExcel(request, clazz, 0, 1);
    }


    /**
     * 导入excel
     *
     * @param request
     * @param clazz
     * @param sheetno
     * @param headerRows
     * @param <T>
     * @return
     * @throws IOException
     */
    public static <T> List<T> importExcel(HttpServletRequest request,
                                          Class<T> clazz,
                                          int sheetno,
                                          int headerRows) throws IOException {
        List<T> resultList = new ArrayList<>();

        ExcelReaderBuilder builder = EasyExcelFactory.read().autoCloseStream(true).autoTrim(true)
                .registerReadListener(new AnalysisEventListener<T>() {

                    @Override
                    public void invoke(T t, AnalysisContext analysisContext) {
                        resultList.add(t);
                    }

                    @Override
                    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                        System.out.println("所有的数据都解析完毕");
                        resultList.clear();
                    }
                });

        builder.head(clazz).file(request.getInputStream())
                .ignoreEmptyRow(true)
                .headRowNumber(headerRows)
                .sheet(sheetno)
                .doRead();

        return resultList;
    }
}

转换类：

@Component
public class DataConverter implements Converter<String> {

    @Override
    public Class supportJavaTypeKey() {
        return String.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public String convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        if (StringUtils.isEmpty(cellData.getStringValue())) {
            return "";
        }

        return cellData.getStringValue();
    }

    @Override
    public CellData<?> convertToExcelData(String s, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        if (StringUtils.isEmpty(s)) {
            return new CellData<>("");
        }

        if (excelContentProperty.getField() != null) {
            if (excelContentProperty.getField().getName().equals("status")) {
                if (s.equals("0")) {
                    return new CellData<>("失败");
                } else {
                    return new CellData<>("成功");
                }
            }
        }


        return new CellData<>(s);
    }
}

使用：

@Data
@ExcelIgnoreUnannotated
public class UserModel {

    @ExcelProperty(value = "用户名", index = 0)
    private String username;
    @ExcelProperty(value = "年龄", index = 1)
    private Integer age;
    @ExcelProperty(value = "状态", index = 2, converter = DataConverter.class)
    private String status;
    
}