Excel Encryption - 加密导出

工作中经常系统导出Excel表格后，我们会发现导出后很可能会造成数据泄露，导出后不可能每次都记得去给Excel表格手动设置密码，后台直接进行加密，这样可以很有效的防止数据被盗取.

加密前需要了解excel有两种常见的文件格式，且加密方式也不同

文件格式	结构	版本	行	列	优点
xls	复合文档	2003及以前	65536	256	-
xlsx	XML	2007及以后	1048576	16384	兼容xls 相同内容占用更小

需要用到的包 POI

1. 文件转文件

   /**
   * 设置密码
   * @return enc
   */
   public Encryptor confirmPassWord(String passWord){
       EncryptionInfo info = new EncryptionInfo(EncryptionMode.agile);
       Encryptor enc = info.getEncryptor();
       enc.confirmPassword(StringUtils.isBlank(passWord)?"123456":passWord);
       return enc;
   }
   /**
   * 加密EXCEL文档(xls)
   * @param sourceFilePath源文件
   * @param targetFilePath目标文件
   * @param passWord  密码
   * @return
   * @throws Exception
   */
   public boolean encrypXLS(String sourceFilePath, String targetFilePath, String passWord)throws IOException{
      InputStream ins = new FileInputStream(sourceFilePath);
      OutputStream out = new FileOutputStream(targetFilePath);
      POIFSFileSystem fs = new POIFSFileSystem(ins);
      HSSFWorkbook hwb = new HSSFWorkbook(fs);
      try {
          Biff8EncryptionKey.setCurrentUserPassword(passWord);
          hwb.write(out);
          return true;
      } catch (Exception e) {
          log.error("XLS文档加密失败:{}",e.getMessage());
          return false;
      }finally {
          ins.close();
          out.close();
          fs.close();
          hwb.close();
      }
   }
   /**
   * 加密EXCEL文档(xlsx)
   * @param sourceFilePath 源文件
   * @param targetFilePath 目标文件
   * @param  passWord  密码
   */
   public void encrypXLSX(String sourceFilePath, String targetFilePath,String passWord)throws IOException{
       OutputStream fos = new FileOutputStream(targetFilePath);
       try (POIFSFileSystem fs = new POIFSFileSystem()) {
           OPCPackage opc = OPCPackage.open(sourceFilePath, PackageAccess.READ_WRITE);
           OutputStream os = confirmPassWord(passWord).getDataStream(fs);
           opc.save(os);
           fs.writeFilesystem(fos);
       }catch (Exception e) {
           log.error("XLSX文档加密失败:{}",e.getMessage());
           e.printStackTrace();
       }finally {
           fos.close();
       }
   }

2. 对象转客户端

   /**
   * 将excel 对象转为输出 Byte数组
   * @param workbook
   * @return ByteArrayInputStream
   */
   public ByteArrayInputStream parseByteArrayInputStream(Workbook workbook){
       ByteArrayInputStream workbookInput = null;
       try{
           ByteArrayOutputStream baos = new ByteArrayOutputStream();
           workbook.write(baos);
           baos.flush();
           workbookInput = new ByteArrayInputStream(baos.toByteArray());// 输出流转为输入流
       }catch (IOException io){
           io.printStackTrace();
       }
       return workbookInput;
   }
   /**
   * 加密EXCEL文档(xls) 不知道好不好用，未测试
   * @param hssfWorkbook
   * @param fileName 文件名
   * @param response 客户端
   * @param passWord 密码
   * @throws IOException
   */
   public void encrypXLS(HSSFWorkbook hssfWorkbook,String fileName,HttpServletResponse response,String passWord)throws IOException{
       try {
           Biff8EncryptionKey.setCurrentUserPassword(passWord);
           response.reset();
           response.setContentType("application/octet-stream; charset=utf-8");
           response.addHeader("Content-Disposition", "attachment; filename*=utf-8'zh_cn'"+ URLEncoder.encode(fileName, "UTF-8"));
           hssfWorkbook.write(response.getOutputStream());
       } catch (Exception e) {
           log.error("XLS文档加密失败:{}",e.getMessage());
       }finally {
           hssfWorkbook.close();
       }
   }  
   /**
   * 加密EXCEL文档(xlsx)
   * @param workbook
   * @param fileName 文件名
   * @param response 客户端
   * @param passWord 密码
   */
   public void encrypXLSX(Workbook workbook,String fileName,HttpServletResponse response,String passWord){
       try (POIFSFileSystem fs = new POIFSFileSystem()) {
           ByteArrayInputStream workbookInput = parseByteArrayInputStream(workbook);
           try (OPCPackage opc = OPCPackage.open(workbookInput);OutputStream os = confirmPassWord(passWord).getDataStream(fs)) {
               opc.save(os);
           }
   // 写出加密版本
           ByteArrayOutputStream baos = new ByteArrayOutputStream();
           fs.writeFilesystem(baos);
           baos.flush();
   
           response.reset();
           response.setContentType("application/octet-stream; charset=utf-8");
           response.addHeader("Content-Disposition", "attachment; filename*=utf-8'zh_cn'"+ URLEncoder.encode(fileName, "UTF-8"));
           response.getOutputStream().write(baos.toByteArray());
       }catch (Exception e) {
           e.printStackTrace();
       }
   }