springboot导出导入

pom.xml文件依赖

> 
>org.apache.poi>
 >poi-ooxml-schemas> 
>4.1.2>
 >

<!-- hutool-->

                cn.hutool
                hutool-all
                5.7.19
            

<!-- poi-->

            
                org.apache.poi
                poi-ooxml
                5.0.0
            
        
            org.apache.poi
            poi
            4.0.1
        
        
        commons-net
        commons-net
        3.6
        

controller

    @RequestMapping("/export")
    public void export1(HttpServletResponse response){
        List list = new ArrayList<>();


        //传入数据
        List userEntityList = userService.list();


        for (UserEntity user : userEntityList) {
            UserEntity obj = new UserEntity();
            obj.setId(user.getId());
            obj.setName(user.getName());
            obj.setEmail(user.getEmail());
            obj.setMobile(user.getMobile());
            obj.setStatus (user.getStatus());
            obj.setDeptId(user.getDeptId());
            obj.setCreateBy(user.getCreateBy());
            obj.setCreateTime(user.getCreateTime());
            obj.setLastUpdateBy(user.getLastUpdateBy());
            obj.setLastUpdateTime(user.getLastUpdateTime());
            obj.setDelFlag(user.getDelFlag());
            list.add(obj);
        }

        // 1.创建ExcelWriter
        // 通过工具类创建writer，默认创建xls格式
        ExcelWriter writer = ExcelUtil.getWriter();
        writer.renameSheet(0, "用户记录");
        //创建工作表
        //创建xlsx格式的
        //ExcelWriter writer = ExcelUtil.getWriter(true);

        // 2.设置一级标题
        // 合并单元格后的标题行，使用默认标题样式，从0开始
        // 设置表头高度
        writer.setRowHeight(0, 25);

        // 3.设置二级标题
        writer.addHeaderAlias("id", "编号");
        writer.addHeaderAlias("name", "用户名");
        writer.addHeaderAlias("email", "邮箱");
        writer.addHeaderAlias("mobile", "手机号");
        writer.addHeaderAlias("status", "状态  0：禁用   1：正常");
        writer.addHeaderAlias("deptId", "机构ID");
        writer.addHeaderAlias("createBy", "创建人");
        writer.addHeaderAlias("createTime", "创建时间");
        writer.addHeaderAlias("lastUpdateBy", "更新人");
        writer.addHeaderAlias("lastUpdateTime", "更新时间");
        writer.addHeaderAlias("delFlag", "是否删除  -1：已删除  0：正常");


        // 4.设置表头字体
        // 获取表头样式，获取样式后可自定义样式
        CellStyle headCellStyle = writer.getHeadCellStyle();
        // 获取单元格样式
//        CellStyle cellStyle = excelWriter.getCellStyle();
        // 设置内容字体
        Font font = writer.createFont();
        // 设置字体
        font.setFontName("宋体");
        // 设置字体大小
        font.setFontHeightInPoints((short) 14);
        // 字体加粗
        font.setBold(true);
        // 字体颜色
        font.setColor(Font.SS_NONE);
        headCellStyle.setFont(font);

        // 5.设置单元格宽度
        int[] arr = {30, 30, 25};
        for (int i = 0; i < arr.length; i++) {
            writer.setColumnWidth(i, arr[i]);
        }
        writer.merge(list.size()-2, "员工信息表");

        // 只导出有别名的字段
        writer.setOnlyAlias(true);

        // 一次性写出内容，使用默认样式，强制输出标题
        writer.write(list, true);

        // 从第几行写入
//        excelWriter.setCurrentRow(1);
//        excelWriter.writeRow(data());
        // 设置某个单元格的样式
//        CellStyle orCreateCellStyle = excelWriter.getOrCreateCellStyle(0, 1);
        // 设置某行的样式
//        excelWriter.setRowStyle();
        ServletOutputStream out = null;
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        try {
            String fileName = URLEncoder.encode(dateFormat.format(new Date())+"用户信息表" , StandardCharsets.UTF_8.name());
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            out = response.getOutputStream();
            // 将Excel Workbook刷出到输出流
            writer.flush(out, true);

        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException("文件写入失败！");
        } finally {
            // 记住关流
            IoUtil.close(writer);

            IoUtil.close(out);
        }
    }

 导入

 // 处理文件上传
    @PostMapping("/excelImport")
    public String uploadImg(@RequestParam("file") MultipartFile file, HttpServletRequest request) throws Exception {

        System.out.println(file);

        if (file.isEmpty()) {
            System.out.println("文件为空！");
            return "文件为空";
        }

        // 1.获取上传文件输入流
        InputStream inputStream = null;
        try {
            inputStream = file.getInputStream();
        } catch (Exception e) {
            // return ResponseData.fail(ResponseCodeEnum.ERROR_PARAM_INVALID);
            e.printStackTrace();
        }

        // 调用用 hutool 方法读取数据 默认调用第一个sheet
        ExcelReader excelReader = ExcelUtil.getReader(inputStream);
        // 从第二行开始获取数据   excelReader.read的结果是一个2纬的list，外层是行，内层是行对应的所有列
        // 读取方式1
    List> read = excelReader.read(2, excelReader.getRowCount());
    List excels = new ArrayList<>();
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        // 循环获取的数据
    for (int i = 0; i < read.size(); i++) {
      List list = read.get(i);
        UserEntity excel = new UserEntity();
      //按照列获取
      excel.setName(list.get(1).toString());
      excel.setEmail(list.get(2).toString());
      excel.setMobile(list.get(3).toString());
      excel.setStatus(Integer.parseInt(list.get(4).toString()));
      excel.setDeptId(Long.parseLong(list.get(5).toString()));
      excel.setLastUpdateBy(list.get(6).toString());
        String s = list.get(7).toString();
        Date date = sdf.parse(s);
        excel.setCreateTime(date);
        excel.setLastUpdateBy(list.get(8).toString());
       excel.setLastUpdateTime(sdf.parse(list.get(9).toString()));
      excel.setDelFlag(Integer.parseInt(list.get(10).toString()));
      //强制类型转换
      //excel.setWeight(Double.parseDouble(list.get(3).toString()));
      //excel.setStatus(Integer.parseInt(list.get(5).toString()));
      excels.add(excel);
    }
    excels.forEach(
        index -> {
          System.out.println(index);
        });

        for (UserEntity excel : excels) {
            System.out.println(excel);
           userService.save(excel);
        }
        System.out.println("导入成功");
        return "导入成功";
    }