用hutool对Excel数据导入与导出

用hutool对Excel数据导入与导出

配置环境

hutool官网

首先导入依赖

 <!--hutool-->
        <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>

编写dao

@TableName("user")
@Data
@Repository
public class User {
    /**
     * @TableName("user") 对应数据库表名
     * id主键自增 @TableId(type = IdType.AUTO)
     */
    @TableId(type = IdType.AUTO)
    private Integer id;
    private String username;
    private String password;
    private String nickName;
    private Integer age;
    private String sex;
    private String address;

}

Excel数据导出

 @GetMapping("/download")
    public void download(HttpServletResponse response) throws Exception {
        //查询所有用户
        List<User> list= userService.list();
        //在内存操作，写到浏览器
        ExcelWriter writer= ExcelUtil.getWriter(true);
        //自定义标题别名
        writer.addHeaderAlias("username","用户名");
        writer.addHeaderAlias("password","密码");
        writer.addHeaderAlias("nickname","昵称");
        writer.addHeaderAlias("address","地址");

        //默认配置
        writer.write(list,true);
        //设置content—type
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset:utf-8");

        //设置标题
        String fileName= URLEncoder.encode("用户信息","UTF-8");
      
        //如何显示附加的文件。
        response.setHeader("ContentDisposition","attachment;filename="+fileName+".xlsx");
        ServletOutputStream outputStream= response.getOutputStream();

        //将Writer刷新到OutPut
        writer.flush(outputStream,true);
        outputStream.close();
        writer.close();

    }

然后在发送网页发送http://localhost:9090/user/download的get请求就可以，以附件形式下载Excel

Excel数据导入

然后我们想把Excel中的数据一起导入到数据库中在页面中显示

  @PostMapping("/importExcel")
    public Boolean importExcel(MultipartFile file) throws IOException {
        InputStream inputStream = file.getInputStream();
        ExcelReader reader = ExcelUtil.getReader(inputStream);
        List<User> list = reader.readAll(User.class);
        log.info(list.toString());
        userService.saveBatch(list);//插入数据
        return true;
    }

当我们将Excel文件以上面代码对发送post请求时

数据库添加的数据时，对于中文字段数据的不能添加进去

对于Excel表格中的中文字段名字不能匹配到

	 @PostMapping("/importExcel")
    public Boolean importExcel(MultipartFile file) throws IOException {
        InputStream inputStream = file.getInputStream();
        ExcelReader reader = ExcelUtil.getReader(inputStream);
        reader.addHeaderAlias("用户名","username");
        reader.addHeaderAlias("密码","password");
        reader.addHeaderAlias("昵称","nickname");
        reader.addHeaderAlias("地址","address");
        List<User> list = reader.readAll(User.class);
        System.out.println(list);
        userService.saveBatch(list);//插入数据
        return true;
    }

添加 reader.addHeaderAlias("昵称","nickname");方法与Excel表格字段名一一对应

数据就添加到数据库中了
这样子Excel数据导入与导出就完成了