Excel的下载与快速大量导入数据库数据

使用EasyPoi下载数据以excel格式导出

首先在你的项目中导入依赖

    cn.afterturn
    easypoi-spring-boot-starter
    4.1.3

 对需要导出的数据库字段添加Excel注解

@TableId(value = "id", type = IdType.AUTO)
private Integer id;

/**
 *
 */
@TableField(value = "username")
@Excel(name = "用户姓名")
private String username;

@TableField(value = "password")
@Excel(name = "用户密码")
private String password;

@TableField(value = "state")
@Excel(name = "用户状态")
private String state;
@Serial
@TableField(exist = false)
private static final long serialVersionUID = 1L;

编写数据导出接口

@GetMapping(value = "/export",produces = "application/octet-stream")
public void exportEmployee(HttpServletResponse response){
    // 获取所有员工数据
    List list = adminService.list();
    System.out.println(list);
    //导出的参数  生成文件名称和下载的文件后缀
    ExportParams params = new ExportParams("用户表","用户表", ExcelType.HSSF);
    //导出员工表 出去导出的参数 实体类和需要导出的信息
    Workbook workbook = ExcelExportUtil.exportExcel(params, Admin.class, list);
    ServletOutputStream out = null;
    try {
        //流形式传输
        response.setHeader("content-type","application/octet-stream");
        //防止中文乱码
        response.setHeader("content-disposition","attachment;filename="+ URLEncoder.encode("用户表.xls", StandardCharsets.UTF_8));
        out = response.getOutputStream();
        //流形式导出
        workbook.write(out);
    } catch (IOException e) {
        e.printStackTrace();
    }finally {
        if (null!=out){
            try {
                //关闭流
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

 

 上传Excel表格批量导入数据库数据

接口编写 

@PostMapping("/import")
public R importEmployee(@RequestPart MultipartFile file){
    ImportParams params = new ImportParams();
    //去掉标题行
    params.setTitleRows(1);
    try {
        //查询出所有的员工数据
        List list = ExcelImportUtil.importExcel(file.getInputStream(), Admin.class, params);
        //mybatis 添加多个
        if (adminService.saveBatch(list)){
            return R.ok("导入成功!");
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return R.error("导入失败！");
}

 最简单的上传表单