SpringBoot导入导出数据实现方法详解

在开发Web应用程序时，数据的导入和导出是一项核心功能。SpringBoot提供了多种方式来实现数据的导入和导出，本文将详细介绍其中常用的两种方式。

基于EasyPOI实现Excel导入和导出

EasyPOI是一个简单易用的Java Excel操作库，它提供了丰富的API和自定义标签来快速创建Excel文件。下面是在SpringBoot项目中使用EasyPOI进行Excel导入和导出的步骤。

导出Excel

1. 添加依赖

<dependency>
  <groupId>cn.afterturn</groupId>
  <artifactId>easypoi-base</artifactId>
  <version>5.5.0</version>
</dependency>

1. 创建Excel模板

public class ExportUserInfoVO {
    @Excel(name = "序号", orderNum = "0", width = 15)
    private Integer id;

    @Excel(name = "姓名", orderNum = "1", width = 20)
    private String name;

    @Excel(name = "年龄", orderNum = "2", width = 15)
    private Integer age;

    // 省略getters和setters
}

1. 实现导出接口

@GetMapping("/exportUserInfo")
public void exportUserInfo(HttpServletResponse response) throws IOException {
    List<UserInfo> userList = userInfoService.getAllUserInfo();
    List<ExportUserInfoVO> voList = userList.stream().map(ConvertUtil::convert).collect(Collectors.toList());

    Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), ExportUserInfoVO.class, voList);

    response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + new String("用户信息导出.xlsx".getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1));
    response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);

    workbook.write(response.getOutputStream());
}

这里的ConvertUtil是自己实现的一个工具类，用来做Java Bean之间的转换。

导入Excel

1. 添加依赖

<dependency>
  <groupId>cn.afterturn</groupId>
  <artifactId>easypoi-base</artifactId>
  <version>5.5.0</version>
</dependency>

1. 创建Excel模板

public class ImportUserInfoVO {
    @Excel(name = "姓名", orderNum = "0", width = 20)
    private String name;

    @Excel(name = "年龄", orderNum = "1", width = 15)
    private String age;

    // 省略getters和setters
}

1. 实现导入接口

@PostMapping("/importUserInfo")
public void importUserInfo(@RequestParam("file") MultipartFile file) throws IOException {
    List<ImportUserInfoVO> dataList = ExcelImportUtil.importExcel(file.getInputStream(), ImportUserInfoVO.class, new ImportParams());

    dataList.forEach(item -> {
        UserInfo userInfo = new UserInfo();
        userInfo.setName(item.getName());
        userInfo.setAge(Integer.parseInt(item.getAge()));
        userInfoService.save(userInfo);
    });
}

基于Jxls实现Excel导入和导出

Jxls是一个开源的Java库，专门用于生成Excel、Word和PowerPoint格式的报表。下面是在SpringBoot项目中使用Jxls进行Excel导入和导出的步骤。

导出Excel

1. 添加依赖

<dependency>
  <groupId>org.jxls</groupId>
  <artifactId>jxls-core</artifactId>
  <version>2.12.1</version>
</dependency>

1. 创建Excel模板

Excel模板可参考Jxls官方文档。

1. 实现导出接口

@GetMapping("/exportUserInfo2")
public void exportUserInfo2(HttpServletResponse response) throws IOException {
    List<UserInfo> userList = userInfoService.getAllUserInfo();
    List<Map<String, Object>> dataList = new ArrayList<>();

    for (int i = 0; i < userList.size(); i++) {
        UserInfo userInfo = userList.get(i);
        Map<String, Object> item = new HashMap<>();
        item.put("id", userInfo.getId());
        item.put("name", userInfo.getName());
        item.put("age", userInfo.getAge());
        dataList.add(item);
    }

    response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + new String("用户信息导出2.xlsx".getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1));
    response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
    InputStream is = this.getClass().getResourceAsStream("/templates/user-info-export-template.xlsx");

    try (OutputStream os = response.getOutputStream()) {
        Context context = new Context();
        context.putVar("dataList", dataList);
        JxlsHelper.getInstance().setUseFastFormulaProcessor(false).processTemplate(is, os, context);
    }
}

导入Excel

1. 添加依赖

<dependency>
  <groupId>org.jxls</groupId>
  <artifactId>jxls-reader</artifactId>
  <version>2.4.3</version>
</dependency>

1. 实现导入接口

@PostMapping("/importUserInfo2")
public void importUserInfo2(@RequestParam("file") MultipartFile file) throws IOException {
    try (InputStream inputStream = file.getInputStream()) {
        XLSReader reader = ReaderBuilder.buildFromXML(this.getClass().getResourceAsStream("/templates/user-info-import-template.xml"));
        List<UserInfo> userList = new ArrayList<>();
        List<Size> sizeList = new ArrayList<>();
        List<RowData> rowList = new ArrayList<>();

        Map<String, Object> beans = new HashMap<>();
        beans.put("userList", userList);
        beans.put("sizeList", sizeList);
        beans.put("rowList", rowList);

        SimpleBlockReader simpleBlockReader = new SimpleBlockReader() {
            @Override
            public void addRows(int sheetIndex, int rowIndex, List row) {
                if (rowIndex > 0) {
                    UserInfo userInfo = new UserInfo();
                    userInfo.setName((String) row.get(0));
                    userInfo.setAge((Integer) row.get(1));
                    userList.add(userInfo);
                }
            }

            @Override
            public void addSize(int sheetIndex, int rowIndex, int columnIndex, Size size) {
                sizeList.add(size);
            }

            @Override
            public void mergeCells(int sheetIndex, List<CellRangeAddress> mergedRegions) {
            }

            @Override
            public void row(int sheetIndex, int rowIndex) {
                if (rowIndex > 0) {
                    Object row = new RowData(rowIndex);
                    rowList.add((RowData) row);
                }
            }
        };

        ReaderConfig.getInstance().setSimpleBlockReader(simpleBlockReader);
        reader.read(inputStream, beans);
        userInfoService.saveBatch(userList);
    }
}

结语

以上就是使用EasyPOI和Jxls实现SpringBoot应用中Excel数据的导入和导出的方法了。根据实际需求选择相应的实现方式，可大大提高开发效率。

阅读剩余 77%