java对excel操作导入导出

最新推荐文章于 2024-03-02 12:23:34 发布

来自外太空的鱼-张小张

最新推荐文章于 2024-03-02 12:23:34 发布

阅读量1.3k

点赞数

文章标签： java 开发语言

本文链接：https://blog.csdn.net/qq_26144365/article/details/125766350

版权

操作EXCEl

1.解析excel

@GetResource(path = "/importExcel", name = "解析excel", requiredPermission = false, requiredLogin = false)
    @ApiOperation(value = "解析 excel")
    public ResponseData importExcel(@RequestParam(name = "filePath") String filePath) throws ParseException {
        File file = new File(filePath);
        ImportParams params = new ImportParams();
        //设置表头
        params.setHeadRows(2);
        //开启验证
        params.setNeedVerify(true);
        params.setVerifyHandler(verifyHandler);
        ExcelImportResult<BookVo> result = null;
        try {
            result = ExcelImportUtil.importExcelMore(file, BookVo.class, params);
        } catch (Exception e) {
            log.error("解析文件错误：", e.toString());
            return ResponseData.error("解析文件错误！");
        }
        //校验成功的集合
        List<BookVo> successList = result.getList();
        //校验失败的集合
        List<BookVo> failList = result.getFailList();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        for (BookVo bookVo : failList) {
            bookVo.setRowNumTitle("第" + (bookVo.getRowNum() + 1) + "行");
            bookVo.setCreateTime(sdf.parse(sdf.format(new Date())));
        }
        Map map = new HashMap();
        map.put("successList", successList);
        map.put("failList", failList);
        return ResponseData.success(map);
    }

2添加数据库

  @PostResource(path = "/importData", name = "Excel导入", requiredPermission = false, requiredLogin = false)
    @ApiOperation(value = "添加数据库")
    public ResponseData addData(@RequestBody List<Book> bookList) {
        bookService.addData(bookList);
        return ResponseData.success();
    }

3.上传导入的模板文件

 @PostResource(path = "/uploadExcel", name = "上传文件", requiredPermission = false, requiredLogin = false)
    @ApiOperation("上传文件")
    public ResponseData uploadExcel(@RequestPart MultipartFile file) {
        String name = file.getOriginalFilename().substring(0, file.getOriginalFilename().lastIndexOf("."));
        String filePath = "";
        //上传的导入模板文件
        filePath = FileUtil.fileUp(file, UploadPath, name);
        return ResponseData.success(filePath);
    }

4.导出Excel

 @PostResource(path = "/exportExcel", name = "导出图书列表", requiredPermission = false, requiredLogin = false)
    @ApiOperation("导出图书列表")
    public void exportCustomExcel(@RequestBody BookParam param, HttpServletResponse response) {
        List<BookVo> list = bookService.getList(param);
        ExportParams params = new ExportParams("图书列表导出", "图书列表导出", ExcelType.HSSF);
        params.setStyle(ExcelStyleUtil.class);
        params.setHeight((short) 16);
        params.setTitleHeight((short) 16);
        Workbook workbook = ExcelExportUtil.exportExcel(params, BookVo.class, list);
        try {
            FileUtil.setExportExcelFormat(response, workbook, "图书列表");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

5.下载模板文件

	@GetResource(path = "/downLoadFile", name = "下载文档模板", requiredPermission = false, requiredLogin = false)
    @ApiOperation("下载文档模板")
    public void downLoadFile(HttpServletResponse response) {
        String path = bookImportTemplate;
        FileUtil.downloadStream(response, path, "UTF-8");
    }