Excel实现导入导出

这里使用: Hutool工具类

Excel导出

通过输出流的方式导出数据

导出数据流程

1.拿到所有数据

2.构建ExcelWriter

3.设置中文表头

4.写出数据到writer

5.设置输出的文件名称以及输出流的头信息

6.写出到输出流并关闭writer

默认的,未添加到alias的属性也会写出,如果想只写出加了别名的字段,可以调用此方法排除之

1
writer.setOnlyAlias(true);
1
2
3
4
5
6
<!--处理office文件的依赖-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.3</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
@GetMapping("/export")
    public void exportData(HttpServletResponse response) throws Exception {
        //1.拿到所有的数据
        List<Admin> admins = adminService.selectAll();
        //2.构建Writer对象
        ExcelWriter writer = ExcelUtil.getWriter(true);//是Excel
        //3.设置中文表头
        writer.addHeaderAlias("username","账号");
        writer.addHeaderAlias("name","名称");
        writer.addHeaderAlias("phone","电话");
        writer.addHeaderAlias("email","邮箱");

        writer.setOnlyAlias(true);
        //4.写出数据到writer
        writer.write(admins);
        //5.设置输出的文件的名称以及输出流的头信息
        String fileName = URLEncoder.encode("管理员信息", StandardCharsets.UTF_8);
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition","attachment;filename="+fileName + ".xlsx");
        //6.写出到输出流并关闭writer
        ServletOutputStream os = response.getOutputStream();
        writer.flush(os);
        writer.close();
        os.close();

    }

条件导出

1
2
3
4
5
const exportData= () => {
  let url = `http://localhost:9999/admin/export?username=${data.username}&name=${data.name}`//这里也可以使用三目表达式data.username===null?'':data.username
  window.open(url)
}

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
@GetMapping("/export")
    public void exportData(Admin admin,HttpServletResponse response) throws Exception {
        //1.拿到所有的数据
        List<Admin> admins = adminService.selectAll(admin);
        //2.构建Writer对象
        ExcelWriter writer = ExcelUtil.getWriter(true);//是Excel
        //3.设置中文表头
        writer.addHeaderAlias("username","账号");
        writer.addHeaderAlias("name","名称");
        writer.addHeaderAlias("phone","电话");
        writer.addHeaderAlias("email","邮箱");

        writer.setOnlyAlias(true);
        //4.写出数据到writer
        writer.write(admins);
        //5.设置输出的文件的名称以及输出流的头信息
        String fileName = URLEncoder.encode("管理员信息", StandardCharsets.UTF_8);
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition","attachment;filename="+fileName + ".xlsx");
        //6.写出到输出流并关闭writer
        ServletOutputStream os = response.getOutputStream();
        writer.flush(os);
        writer.close();
        os.close();

    }

选择导出:

选中的id传到后台

解析参数

1
2
3
4
5
String ids = admin.getIds();
        if (StrUtil.isNotBlank(ids)) {
            String[] idsArr = ids.split(",");
            admin.setIdsArr(idsArr);
        }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<select id="selectAll" resultType="com.yjy.entity.Admin">
       SELECT * FROM `admin`
       <where>
           <if test="name != null and name != ''">
               name LIKE CONCAT('%', #{name}, '%')
           </if>

           <if test="username != null and username != ''">
               and username LIKE CONCAT('%', #{username}, '%')
           </if>
           
           <if test="ids !=null and ids!=''">
               and id in
               <foreach collection="idsArr" open="(" close=")" separator="," item="id">
                   #{id}
           </foreach>
           </if>
       </where>
       ORDER BY id DESC
   </select>

前端导出的方法:

1
2
3
4
5
6
7
const exportData= () => {
  let idsStr = data.ids.join(",") //把数组转换成字符串[1,2,3] -> "1,2,3"
  let url = `http://localhost:9999/admin/export?username=${data.username}&name=${data.name}`//这里也可以使用三目表达式data.username===null?'':data.username
  + `&ids=${idsStr}`
  window.open(url)
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
package com.yjy.entity;

import lombok.Data;

@Data
public class Admin {
    private Integer id; //null
    private String username;
    private String password;
    private String name;
    private String phone;
    private String email;
    private String ids;//非数据库字段
    private String[] idsArr;//非数据库字段
}

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
/**
     *
     * @param admin
     * @param ids 1 , 2 , 3
     * @param response
     * @throws Exception
     */
    @GetMapping("/export")
    public void exportData(Admin admin,/*@RequestParam(required = false)String ids,*/HttpServletResponse response) throws Exception {
//        if(StrUtil.isBlank(ids)){
//            String[] arr = ids.split(",");
//        }
        String ids = admin.getIds();
        if (StrUtil.isNotBlank(ids)) {
            String[] idsArr = ids.split(",");
            admin.setIdsArr(idsArr);
        }
        //1.拿到所有的数据
        List<Admin> admins = adminService.selectAll(admin);
        //2.构建Writer对象
        ExcelWriter writer = ExcelUtil.getWriter(true);//是Excel
        //3.设置中文表头
        writer.addHeaderAlias("username","账号");
        writer.addHeaderAlias("name","名称");
        writer.addHeaderAlias("phone","电话");
        writer.addHeaderAlias("email","邮箱");

        writer.setOnlyAlias(true);
        //4.写出数据到writer
        writer.write(admins);
        //5.设置输出的文件的名称以及输出流的头信息
        String fileName = URLEncoder.encode("管理员信息", StandardCharsets.UTF_8);
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition","attachment;filename="+fileName + ".xlsx");
        //6.写出到输出流并关闭writer
        ServletOutputStream os = response.getOutputStream();
        writer.flush(os);
        writer.close();
        os.close();

    }

批量导入

前端:

1
2
3
4
5
6
7
8
9
10
11
12
const handleImportSuccess = (res) => {
  if(res.code=='200') {
    ElMessage.success("批量导入数据成功")
    load()
  }else{
    ElMessage.error(res.msg)
  }
}

  <el-upload action="http://localhost:9999/admin/import" style="display: inline-block;margin-left: 10px;" :show-file-list="false" :on-success="handleImportSuccess">
        <el-button type="success" >批量导入</el-button>
      </el-upload>

后端:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
@PostMapping("/import")
   public Result importData(MultipartFile file) throws Exception {
       //1.拿到输入流,构建reader
       InputStream inputStream = file.getInputStream();
       ExcelReader reader = ExcelUtil.getReader(inputStream);
       //2.通过Reader 获取excel里面的数据
       reader.addHeaderAlias("账号","username");
       reader.addHeaderAlias("名称","name");
       reader.addHeaderAlias("电话","phone");
       reader.addHeaderAlias("邮箱","email");

       //3.将数据读取到数据库
       List<Admin> list = reader.readAll(Admin.class);
       for (Admin admin : list) {
           adminService.add(admin);
       }
       return Result.success();

   }