导出导入

说明

本项目采用了easy-poi来实现导入导出功能，easy-poi的详细使用请参考easy-poi官方文档。下面介绍本项目的二次封装：

后端实现导入

PoiController 公共导入

java

default R<Boolean> importExcel(@RequestParam(value = "file") MultipartFile simpleFile, HttpServletRequest request,
                               HttpServletResponse response) throws Exception {
    ImportParams params = new ImportParams();

    params.setTitleRows(StrUtil.isEmpty(request.getParameter("titleRows")) ? 0 : Convert.toInt(request.getParameter("titleRows")));
    params.setHeadRows(StrUtil.isEmpty(request.getParameter("headRows")) ? 1 : Convert.toInt(request.getParameter("headRows")));
    List<Map<String, String>> list = ExcelImportUtil.importExcel(simpleFile.getInputStream(), Map.class, params);

    if (list != null && !list.isEmpty()) {
        return handlerImport(list);
    }
    return validFail("导入Excel无有效数据！");
}

default R<Boolean> importExcel(@RequestParam(value = "file") MultipartFile simpleFile, HttpServletRequest request,
                               HttpServletResponse response) throws Exception {
    ImportParams params = new ImportParams();

    params.setTitleRows(StrUtil.isEmpty(request.getParameter("titleRows")) ? 0 : Convert.toInt(request.getParameter("titleRows")));
    params.setHeadRows(StrUtil.isEmpty(request.getParameter("headRows")) ? 1 : Convert.toInt(request.getParameter("headRows")));
    List<Map<String, String>> list = ExcelImportUtil.importExcel(simpleFile.getInputStream(), Map.class, params);

    if (list != null && !list.isEmpty()) {
        return handlerImport(list);
    }
    return validFail("导入Excel无有效数据！");
}

参考用户导入

java

@Override
public R<Boolean> importExcel(@RequestParam("file") MultipartFile simpleFile, HttpServletRequest request,
                              HttpServletResponse response) throws Exception {
    ImportParams params = new ImportParams();
    params.setTitleRows(0);
    params.setHeadRows(1);
    params.setNeedVerify(true);
    params.setVerifyGroup(new Class[]{Default.class});
    // 下面的2个handler只能支持少量数据导入，因为每一条数据都会执行N次查询。有谁能提供优雅解决方式，欢迎PR
    params.setVerifyHandler(excelUserVerifyHandler);
    params.setDictHandler(userExcelDictHandlerIImpl);

    ExcelImportResult<UserExcelVO> result = ExcelImportUtil.importExcelMore(simpleFile.getInputStream(), UserExcelVO.class, params);

    if (result.isVerifyFail()) {
        // 更加优雅的方式，应该是让用户下载错误的数据， 有谁能提供优雅解决方式，欢迎PR
        return R.validFail(result.getFailList().stream()
                .map(item -> StrUtil.format("第{}行检验错误: {}", item.getRowNum(), item.getErrorMsg()))
                .collect(Collectors.joining("<br/>")));
    }

    List<UserExcelVO> list = result.getList();
    if (list.isEmpty()) {
        return this.validFail("导入数据不能为空");
    }

    Set<String> accounts = new HashSet<>();
    List<User> userList = list.stream().map(item -> {
        ArgumentAssert.notContain(accounts, item.getAccount(), "Excel中存在重复的账号: {}", item.getAccount());

        accounts.add(item.getAccount());
        User user = new User();
        BeanUtil.copyProperties(item, user);
        user.setSalt(RandomUtil.randomString(20));
        user.setPassword(SecureUtil.sha256(BizConstant.DEF_PASSWORD + user.getSalt()));
        return user;
    }).collect(Collectors.toList());

    baseService.saveBatch(userList);
    return this.success(true);
}

@Override
public R<Boolean> importExcel(@RequestParam("file") MultipartFile simpleFile, HttpServletRequest request,
                              HttpServletResponse response) throws Exception {
    ImportParams params = new ImportParams();
    params.setTitleRows(0);
    params.setHeadRows(1);
    params.setNeedVerify(true);
    params.setVerifyGroup(new Class[]{Default.class});
    // 下面的2个handler只能支持少量数据导入，因为每一条数据都会执行N次查询。有谁能提供优雅解决方式，欢迎PR
    params.setVerifyHandler(excelUserVerifyHandler);
    params.setDictHandler(userExcelDictHandlerIImpl);

    ExcelImportResult<UserExcelVO> result = ExcelImportUtil.importExcelMore(simpleFile.getInputStream(), UserExcelVO.class, params);

    if (result.isVerifyFail()) {
        // 更加优雅的方式，应该是让用户下载错误的数据， 有谁能提供优雅解决方式，欢迎PR
        return R.validFail(result.getFailList().stream()
                .map(item -> StrUtil.format("第{}行检验错误: {}", item.getRowNum(), item.getErrorMsg()))
                .collect(Collectors.joining("<br/>")));
    }

    List<UserExcelVO> list = result.getList();
    if (list.isEmpty()) {
        return this.validFail("导入数据不能为空");
    }

    Set<String> accounts = new HashSet<>();
    List<User> userList = list.stream().map(item -> {
        ArgumentAssert.notContain(accounts, item.getAccount(), "Excel中存在重复的账号: {}", item.getAccount());

        accounts.add(item.getAccount());
        User user = new User();
        BeanUtil.copyProperties(item, user);
        user.setSalt(RandomUtil.randomString(20));
        user.setPassword(SecureUtil.sha256(BizConstant.DEF_PASSWORD + user.getSalt()));
        return user;
    }).collect(Collectors.toList());

    baseService.saveBatch(userList);
    return this.success(true);
}

实体类字段上标记@Excel注解

java

public class UserExcelVO implements Serializable, IExcelModel, IExcelDataModel {
    private String errorMsg;
    private Integer rowNum;
  
    @Excel(name = "账号")
    private String account;
}

public class UserExcelVO implements Serializable, IExcelModel, IExcelDataModel {
    private String errorMsg;
    private Integer rowNum;
  
    @Excel(name = "账号")
    private String account;
}

后端导出

java

@RequestMapping(value = "/export", method = RequestMethod.POST, produces = "application/octet-stream")
default void exportExcel(@RequestBody @Validated PageParams<PageQuery> params, HttpServletRequest request, HttpServletResponse response) {
    ExportParams exportParams = getExportParams(params);

    List<?> list = findExportList(params);

    Map<String, Object> map = new HashMap<>(7);
    map.put(NormalExcelConstants.DATA_LIST, list);
    map.put(NormalExcelConstants.CLASS, getExcelClass());
    map.put(NormalExcelConstants.PARAMS, exportParams);
    Object fileName = params.getExtra().getOrDefault(NormalExcelConstants.FILE_NAME, "临时文件");
    map.put(NormalExcelConstants.FILE_NAME, fileName);
    PoiBaseView.render(map, request, response, NormalExcelConstants.EASYPOI_EXCEL_VIEW);
}

@RequestMapping(value = "/export", method = RequestMethod.POST, produces = "application/octet-stream")
default void exportExcel(@RequestBody @Validated PageParams<PageQuery> params, HttpServletRequest request, HttpServletResponse response) {
    ExportParams exportParams = getExportParams(params);

    List<?> list = findExportList(params);

    Map<String, Object> map = new HashMap<>(7);
    map.put(NormalExcelConstants.DATA_LIST, list);
    map.put(NormalExcelConstants.CLASS, getExcelClass());
    map.put(NormalExcelConstants.PARAMS, exportParams);
    Object fileName = params.getExtra().getOrDefault(NormalExcelConstants.FILE_NAME, "临时文件");
    map.put(NormalExcelConstants.FILE_NAME, fileName);
    PoiBaseView.render(map, request, response, NormalExcelConstants.EASYPOI_EXCEL_VIEW);
}

后端导出预览

java

@RequestMapping(value = "/preview", method = RequestMethod.POST)
default R<String> preview(@RequestBody @Validated PageParams<PageQuery> params) {
    ExportParams exportParams = getExportParams(params);
    List<?> list = findExportList(params);
    Workbook workbook = ExcelExportUtil.exportExcel(exportParams, getExcelClass(), list);
    return success(ExcelXorHtmlUtil.excelToHtml(new ExcelToHtmlParams(workbook)));
}

@RequestMapping(value = "/preview", method = RequestMethod.POST)
default R<String> preview(@RequestBody @Validated PageParams<PageQuery> params) {
    ExportParams exportParams = getExportParams(params);
    List<?> list = findExportList(params);
    Workbook workbook = ExcelExportUtil.exportExcel(exportParams, getExcelClass(), list);
    return success(ExcelXorHtmlUtil.excelToHtml(new ExcelToHtmlParams(workbook)));
}

acuity-web-plus 前端实现

导出预览组件： PreviewExcelModel.vue
导入组件：ImportExcelModel.vue

typescript

// 导入按钮
<a-button type="primary" @click="handleImport">{{ t('common.title.import') }}</a-button>
// 导入页面
<ImpExcelModel
  @register="importRegister"
  @success="handleImportSuccess"
  :api="importFile"
  :templateHref="templateUrl.user"
/>

// 导入按钮
<a-button type="primary" @click="handleImport">{{ t('common.title.import') }}</a-button>
// 导入页面
<ImpExcelModel
  @register="importRegister"
  @success="handleImportSuccess"
  :api="importFile"
  :templateHref="templateUrl.user"
/>

导出和导出预览功能：

typescript

// 导出按钮
<a-button type="primary" @click="handleExport">{{ t('common.title.export') }}</a-button>

// 导出预览页面
<PreviewExcelModel
  width="70%"
  @register="exportRegister"
  @success="handleExportSuccess"
  :exportApi="exportFile"
  :previewApi="exportPreview"
/>

// 点击导出按钮执行后台方法
function handleExport() {
  const form = getForm();
  let params = { ...form.getFieldsValue() };
  params = handleSearchInfoByCreateTime(params);
  params.extra = {
    ...{
      fileName: t('acuity.org.user.table.title'),
    },
    ...params?.extra,
  };
  params.size = 20000;

  exportModel.openModal(true, {
    params,
  });
}

// 导出按钮
<a-button type="primary" @click="handleExport">{{ t('common.title.export') }}</a-button>

// 导出预览页面
<PreviewExcelModel
  width="70%"
  @register="exportRegister"
  @success="handleExportSuccess"
  :exportApi="exportFile"
  :previewApi="exportPreview"
/>

// 点击导出按钮执行后台方法
function handleExport() {
  const form = getForm();
  let params = { ...form.getFieldsValue() };
  params = handleSearchInfoByCreateTime(params);
  params.extra = {
    ...{
      fileName: t('acuity.org.user.table.title'),
    },
    ...params?.extra,
  };
  params.size = 20000;

  exportModel.openModal(true, {
    params,
  });
}

acuity-web 前端实现

导入组件： Import.vue

导出导入 ​

后端实现导入 ​

后端导出 ​

后端导出预览 ​

acuity-web-plus 前端实现 ​

acuity-web 前端实现 ​

导出导入

后端实现导入

后端导出

后端导出预览

acuity-web-plus 前端实现

acuity-web 前端实现