SpringBoot项目中Excel设置密码（java）

SpringBoot项目中为Excel设置密码，当打开Excel文件时需要密码才能打开

xls格式的导出

        注意：因为Biff8EncryptionKey中使用了ThreadLocal，所以在当前请求结束后，需要Biff8EncryptionKey.setCurrentUserPassword(null)；注意：这种导出方式，wps就算输入正确密码也无法打开。

/**
     * xsl导出加密
     *
     * @param workbook workbook
     * @param password 密码
     * @return 加密Excel
     */
    public static HSSFWorkbook excelLock(HSSFWorkbook workbook, String password) {
        ByteArrayOutputStream out = null;
        ByteArrayInputStream in = null;
        POIFSFileSystem fs = null;
        try {
            out = new ByteArrayOutputStream();
            workbook.write(out);
            out.flush();
            in = new ByteArrayInputStream(out.toByteArray());
            fs = new POIFSFileSystem(in);
            Biff8EncryptionKey.setCurrentUserPassword(password);
            return new HSSFWorkbook(fs);
        } catch (Exception e) {
            LOGGER.error("导出文件加密异常", e);
            throw new BusinessException("导出文件加密异常");
        } finally {
            IOUtils.closeQuietly(fs);
            IOUtils.closeQuietly(in);
            IOUtils.closeQuietly(out);
        }

    }

        注意：返回的HSSFWorkbook可以使用write方法，写到字节流中，然后再做处理，最后记得流一定要关。

ByteArrayOutputStream baos = new ByteArrayOutputStream();
HSSFWorkbook hssfWorkbook = excelLock(workBook, password);
hssfWorkbook.write(baos);
byte[] bytes = baos.toByteArray();

xlsx导出设置密码-方法1

/**
     * xlsx格式的加密方式
     * @param workbook XSSFWorkbook
     * @param password 密码
     * @return byte
     */
    public static byte[] excelLock(XSSFWorkbook workbook, String password) throws GeneralSecurityException, IOException, InvalidFormatException {
        ByteArrayOutputStream bout = null;
        ByteArrayInputStream bin = null;
        OutputStream dataStream = null;
        ByteArrayOutputStream resultOut = null;
        try {
            bout = new ByteArrayOutputStream();
            workbook.write(bout);
            bout.flush();
            bin = new ByteArrayInputStream(bout.toByteArray());
            POIFSFileSystem poifsFileSystem =new POIFSFileSystem();
            // poi版本不同，这里和下边的方法2有细微差别
            EncryptionInfo encryptionInfo = new EncryptionInfo(EncryptionMode.agile);
            Encryptor encryptor = encryptionInfo.getEncryptor();
            encryptor.confirmPassword(password);
            OPCPackage opc = OPCPackage.open(bin);
            dataStream = encryptor.getDataStream(poifsFileSystem);
            opc.save(dataStream);
            opc.close();
            resultOut = new ByteArrayOutputStream();
            poifsFileSystem.writeFilesystem(resultOut);
            resultOut.flush();
            return resultOut.toByteArray();
        } catch (Exception e) {
            LOGGER.error("导出文件加密异常", e);
            throw e;
        } finally {
            IOUtils.closeQuietly(resultOut);
            IOUtils.closeQuietly(dataStream);
            IOUtils.closeQuietly(bout);
            IOUtils.closeQuietly(bin);
        }
    }

xslx导出设置密码-方法2

由于POI版本不一样，处理有细微差别

/**
     * xlsx格式的加密方式
     * @param workbook XSSFWorkbook
     * @param password 密码
     * @return byte
     */
    public static byte[] excelLock(XSSFWorkbook workbook, String password) {
        ByteArrayOutputStream bout = null;
        ByteArrayInputStream bin = null;
        OutputStream dataStream = null;
        ByteArrayOutputStream resultOut = null;
        try {
            bout = new ByteArrayOutputStream();
            workbook.write(bout);
            bout.flush();
            bin = new ByteArrayInputStream(bout.toByteArray());
            POIFSFileSystem poifsFileSystem =new POIFSFileSystem();
            // POI版本不同，这里会和方法1有细微差别，
            EncryptionInfo encryptionInfo = new EncryptionInfo(poifsFileSystem, EncryptionMode.agile);
            Encryptor encryptor = encryptionInfo.getEncryptor();
            encryptor.confirmPassword(password);
            OPCPackage opc = OPCPackage.open(bin);
            dataStream = encryptor.getDataStream(poifsFileSystem);
            opc.save(dataStream);
            opc.close();
            resultOut = new ByteArrayOutputStream();
            poifsFileSystem.writeFilesystem(resultOut);
            resultOut.flush();
            return resultOut.toByteArray();
        } catch (Exception e) {
            logger.error("导出文件加密异常", e);
            throw new BusinessException("导出文件加密异常");
        } finally {
            IOUtils.closeQuietly(resultOut);
            IOUtils.closeQuietly(dataStream);
            IOUtils.closeQuietly(bout);
            IOUtils.closeQuietly(bin);
        }
    }

暂不知什么原因，方法1和方法2大部分没问题，但是个别环境导出会损坏文件（可能与机器或者编码等有关系吧，纯属猜测）。如果出现损坏，采用以下方法

/**
     * xlsx格式的加密方式
     * @param workbook XSSFWorkbook
     * @param password 密码
     * @return byte
     */
    public static byte[] excelLock(XSSFWorkbook workbook, String password) {
        ByteArrayOutputStream resultOut = null;
        try {
            POIFSFileSystem poifsFileSystem =new POIFSFileSystem();
            EncryptionInfo encryptionInfo = new EncryptionInfo(EncryptionMode.agile);
            Encryptor encryptor = encryptionInfo.getEncryptor();
            encryptor.confirmPassword(password);
            OutputStream dataStream1 = encryptor.getDataStream(poifsFileSystem);
            workbook.write(dataStream1);
            dataStream1.flush();
            workbook.close();
            dataStream1.close();
            resultOut = new ByteArrayOutputStream();
            poifsFileSystem.writeFilesystem(resultOut);
            return resultOut.toByteArray();
        } catch (Exception e) {
            logger.error("导出文件加密异常", e);
            throw new RuntimeException("导出文件加密异常");
        } finally {
            org.apache.commons.io.IOUtils.closeQuietly(resultOut);
        }
    }