Hutool excel导入导出示例

曾经终败给现在 2023-09-30 09:21 4阅读 0赞

> 下载生成excel模板

示例代码：

①模板的实体类：

@Getter
    @Setter
    @ToString
    @SuperBuilder
    @NoArgsConstructor
    public class WsWorkerTemplate{
        
        @ApiModelProperty(value = "姓名")
        private String workerName;
    
        @ApiModelProperty(value = "手机号")
        private String workerPhone;
        
        @ApiModelProperty(value = "身份证号")
        private String workerIdcard;
    }

② 生成模板的类：

@ApiOperation("下载生成excel模板")
        @GetMapping("/downloadExcel")
        public void downloadExcel(HttpServletResponse response) throws IOException {
        
    
            WsWorkerTemplate wsWorker = new WsWorkerTemplate();
    //        wsWorker.setWorkerName("张三");
    //        wsWorker.setWorkerPhone("15992714704");
    //        wsWorker.setWorkerIdcard("130501197512158843");
            List<WsWorkerTemplate> rows = CollUtil.newArrayList(wsWorker);
    
            // 通过工具类创建writer，默认创建xls格式
            ExcelWriter writer = ExcelUtil.getWriter();
            //自定义标题别名
            writer.addHeaderAlias("workerName", "姓名");
            writer.addHeaderAlias("workerPhone", "手机号");
            writer.addHeaderAlias("workerIdcard", "身份证号");
            // 合并单元格后的标题行，使用默认标题样式
            String content = "填表须知：\n\t" +
                    "1、姓名：2至15个汉字；\n\t" +
                    "2、手机号：11位手机号格式；\n\t" +
                    "3、身份证号：18位第二代身份证号；\n\t";
            writer.merge(2,content,false);
            //设置列宽
            writer.setColumnWidth(-1,30);
    
            writer.setRowHeight(0,80);
            // 一次性写出内容，使用默认样式，强制输出标题
            writer.write(rows, true);
            //out为OutputStream，需要写出到的目标流
            //response为HttpServletResponse对象
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
            //response.setHeader("Content-Disposition", "attachment;filename=测试文件.xls");
            String excelName="worker.xls";
            // 在设置response.setHeader时，如果含有中文字符，一定要转换成ISO8859-1格式，否则设置的中文会出现错误。
            response.setHeader("content-disposition","attachment;filename="+new      String(excelName.getBytes("gb2312"), "ISO8859-1"));
            ServletOutputStream out = response.getOutputStream();
            writer.flush(out, true);
            // 关闭writer，释放内存
            writer.close();
            //此处记得关闭输出Servlet流
            IoUtil.close(out);
        }

> excel 导入

/**
         * 批量导入
         *
         * @param file
         * @return
         */
        public ResultObj<Object> importWorker(@RequestParam("file") MultipartFile file, String worksiteId) {
        
            ResultObj<Object> resultObj = new ResultObj<Object>();
            String message1 = "";
            List<String> strList = new LinkedList<>();
            List<WsWorker> list = new LinkedList<>();
            try {
        
                InputStream inputStream = file.getInputStream();
                list = getWsWorkerExcel(inputStream);
                if (list != null && list.size() > 0) {
        
                        for (WsWorker w : list) {
        
                            w.setWorksiteId(worksiteId);
                            w.setCheckResult(0);
                            wsWorkerMapper.insertSelective(w);
                        }
                        resultObj.setStatus(200);
                        resultObj.setMsg("导入成功！");
                  }
            } catch (IOException e) {
        
                e.printStackTrace();
            }
            return resultObj;
        }

获取excel的数据：

public List<WsWorker> getWsWorkerExcel(InputStream inputStream) {
        
    
            ExcelReader reader = ExcelUtil.getReader(inputStream);
            reader.addHeaderAlias("姓名", "workerName");
            reader.addHeaderAlias("手机号", "workerPhone");
            reader.addHeaderAlias("身份证号", "workerIdcard");
    
            List<WsWorker> workers = reader.read(1, 2, WsWorker.class);
            return workers;
        }