excel 用byte数组存储 pgsql数据库的 导入 导出

 

 

这次需求是用数据库来存储excel,不用任何操作，只记录excel 文件。

 

这次的灵感来源于这句话

 

 

 

 

 

pom 文件:

 

  <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.5.9</version>
        </dependency>

     <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.0</version>
        </dependency>

 

为了简化代码，本次使用的是

hutool工具类

数据库字段:

 

 

 

 

实体字段：

 

 

 

 

 

 

 

主要代码：

 

上传 ：

 

    @Override
    public boolean upload(MultipartFile file) throws IOException {
        Workbook book = WorkbookUtil.createBook(file.getInputStream(), null);
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        book.write(baos);
        byte[] bytes = baos.toByteArray();
        TAuditFile tAuditFile = new TAuditFile();
        tAuditFile.setFilecontent(bytes);
        tAuditFileService.save(tAuditFile);
        return  false;
    }

 

 

下载：

  @Override
    public void downLoad(HttpServletResponse response) {
        TAuditFile one = tAuditFileService.findOne(Wrappers.<TAuditFile>lambdaQuery().eq(TAuditFile::getId, "072018eb-e5c4-4ec9-a9c5-600495fea9fa"));
        InputStream sbs = new ByteArrayInputStream(one.getFilecontent());
        Workbook sheets = WorkbookUtil.createSXSSFBook(sbs,null);

        downLoadExcel("记录.xlsx",response, sheets);
    }

    public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            //throw new NormalException(e.getMessage());
        }
    }