SpringBoot-（十二）SpringBoot实现数据批量导入导出

本文最后更新于：February 16, 2022 pm

SpringBoot框架中有两个非常重要的策略：开箱即用和约定优于配置。其设计目的是用来简化新Spring应用的初始搭建以及开发过程。该框架使用了特定的方式来进行配置，从而使开发人员不再需要定义样板化的配置。

目录

使用Hutool官方文档 。也可以用 POI 实现。

依赖

1
2
3
4
5
6
7
8
9
10
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.7.20</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

导出

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
@GetMapping("/export")
public void export(HttpServletResponse response) throws Exception{
    System.out.println("->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>zhix");

    List<Map<String, Object>> list = CollUtil.newArrayList();
    List<Item> all = itemMapper.finall();

    for(Item it : all){
        Map<String, Object> row1 = new LinkedHashMap<>();
        row1.put("物品编号",it.getId());
        row1.put("物品名称",it.getName());
        row1.put("物品类型",it.getType());
        row1.put("物品价格",it.getPrice());
        row1.put("物品数量",it.getCount());
        row1.put("送货时间",it.getItem_time());
        row1.put("添加时间",it.getCreate_time());
        row1.put("物品位置",it.getPosition());
        row1.put("物品规格",it.getSize());
        row1.put("送货人",it.getManager());
        row1.put("备注",it.getDescription());

        list.add(row1);
    }

    // 2. 写excel
    ExcelWriter writer = ExcelUtil.getWriter(true);
    writer.write(list, true);

    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
    String fileName = URLEncoder.encode("用户信息", "UTF-8");
    response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");

    ServletOutputStream out = response.getOutputStream();
    writer.flush(out, true);
    writer.close();
    IoUtil.close(System.out);
}

可以进行token传参，进而检验身份。

导入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
@PostMapping("/import")
public boolean imp(MultipartFile file) {
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    List<Item> items = CollUtil.newArrayList();
    try {
        InputStream inputStream = file.getInputStream();
        ExcelReader reader = ExcelUtil.getReader(inputStream);

        List<List<Object>> list = reader.read(1);

        System.out.println(list.size());
        for (List<Object> row : list) {
            Item item = new Item();
            item.setName(row.get(0).toString());
            item.setType(row.get(1).toString());
            item.setPrice(Float.valueOf(row.get(2).toString()));
            item.setCount(Long.valueOf(row.get(3).toString()));
            item.setItem_time(sdf.parse(row.get(4).toString()));
            item.setPosition(row.get(5).toString());
            item.setSize(row.get(6).toString());
            item.setManager(row.get(7).toString());
            item.setDescription(row.get(8).toString());

            items.add(item);
        }
    }catch (Exception e){
        return false;
    }

    for(Item it : items){
        itemMapper.insert(it);
    }

    return true;
}

实例

前端

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
<el-upload action="http://localhost:8081/item/import" style="display: inline-block;margin-left: 5px" :on-success="successimport" :on-error="errimport" :show-file-list="false">
    <el-button type="success">导入 <i class="el-icon-upload"></i>
    </el-button>
</el-upload>

<el-button type="success" @click="itemexport" style="margin-left: 5px">导出 <i class="el-icon-download"></i>
</el-button>

methods:{
    errimport(){ //导入失败
      this.$message({
        message: '导入失败',
        type: 'error'
      });
    },
    successimport(){ //导入成功
      this.netword()
      this.$message({
        type: 'success',
        message: '导入成功'
      });
    },
    itemexport() { //导出
      //验证token
      let token = localStorage.getItem('token')
      this.$request.post("/user/check",token).then(res=>{
        console.log('cg')
        console.log(res)
        //成功
        console.log(res.state)
        if(res.state=='ok'){
          this.$message({
            type: 'success',
            message: '导出成功'
          });
          window.open("http://localhost:8081/item/export",'_self') //_self本窗口直接下载
        }else{
          this.$message({
            message: '导出失败',
            type: 'error'
          });
        }

      }).catch(err=>{
        console.log(err)
        console.log('token验证')
        this.$message({
          message: '导出失败',
          type: 'error'
        });
      })

    },
}

方式二（推荐）

使用插件 js-file-download

1
npm install js-file-download --save

在需要用的组件中导入：

1
import fileDownload from 'js-file-download'
使用
1
2
3
4
5
6
7
8
9
10
11
itemexport() { //导出
    this.$request.get("/item/export", {
        responseType: 'arraybuffer'
    }).then(res=>{
        console.log('成功导出')
        fileDownload(res,'用户信息.xlsx') //注意！这里的res不能写成res.data，否则导不出数据
    }).catch(err=>{
        console.log('失败导出')
    })

},

注意：这里 fileDownload 中不能写res.data，否则不能导出数据。有些网上的东西不能完全相信，不然掉坑的就是自己。。。

后端

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
@GetMapping("/export")
public void export(HttpServletRequest request, HttpServletResponse response) throws Exception{
    String token = request.getHeader("token");
    System.out.println(token);
    System.out.println("->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>zhix");

    List<Map<String, Object>> list = CollUtil.newArrayList();
    List<Item> all = itemMapper.finall();

    for(Item it : all){
        Map<String, Object> row1 = new LinkedHashMap<>();
        row1.put("物品编号",it.getId());
        row1.put("物品名称",it.getName());
        row1.put("物品类型",it.getType());
        row1.put("物品价格",it.getPrice());
        row1.put("物品数量",it.getCount());
        row1.put("送货时间",it.getItem_time());
        row1.put("添加时间",it.getCreate_time());
        row1.put("物品位置",it.getPosition());
        row1.put("物品规格",it.getSize());
        row1.put("送货人",it.getManager());
        row1.put("备注",it.getDescription());

        list.add(row1);
    }

    // 2. 写excel
    ExcelWriter writer = ExcelUtil.getWriter(true);
    writer.write(list, true);

    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
    String fileName = URLEncoder.encode("用户信息", "UTF-8");
    response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");

    ServletOutputStream out = response.getOutputStream();
    writer.flush(out, true);
    writer.close();
    IoUtil.close(System.out);


}


@PostMapping("/import")
public boolean imp(MultipartFile file) {
    System.out.println("->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>zhi1");

    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    List<Item> items = CollUtil.newArrayList();
    try {

        System.out.println("->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>zhixx1");
        InputStream inputStream = file.getInputStream();
        ExcelReader reader = ExcelUtil.getReader(inputStream);
        System.out.println("->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>zhixx2");

        List<List<Object>> list = reader.read(1);
        System.out.println("->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>zhixx3");

        System.out.println(list.size());
        for (List<Object> row : list) {
            Item item = new Item();
            item.setName(row.get(0).toString());
            item.setType(row.get(1).toString());
            item.setPrice(Float.valueOf(row.get(2).toString()));
            item.setCount(Long.valueOf(row.get(3).toString()));
            item.setItem_time(sdf.parse(row.get(4).toString()));
            item.setPosition(row.get(5).toString());
            item.setSize(row.get(6).toString());
            item.setManager(row.get(7).toString());
            item.setDescription(row.get(8).toString());

            items.add(item);
        }
        System.out.println("->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>zhi2");
    }catch (Exception e){
        return false;
    }

    for(Item it : items){
        itemMapper.insert(it);
    }
    System.out.println("->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>zhi3");

    return true;
}