[JAVA] 암호걸린 엑셀 이메일로 전송

※ 이슈 

1. 개인정보 보호이슈로 엑셀 파일에 암호 필요

2. 암호 전달을 위해 form.submit을 통한 다운로드 대신 이메일 발송

    public boolean xlsDownload(User user) {
        try{
            List<Book> books= getBookList();

            //워크북 생성
            String strHeaders = "title,author,rank";
            String password = RandomStringUtils.randomAlphanumeric(8);
            List<String> selectedHeader = Arrays.asList(strHeaders.split(","));
            List<String> headerNames = this.getSelectedHeader(selectedHeader);
            ByteArrayOutputStream byteArrayOutputStream = this.makeByteArrayOutputStream(books, selectedHeader, headerNames, password);
            //이메일 보냄(내부적으로 byteArrayOutputStream 으로 파일을 첨부) 
            return this.sendMailWithXls(byteArrayOutputStream, password, user);
        }
        catch(ApiResponseException e){
            throw new RuntimeException();
        }
    }
    //header가 여러개라고 가정했을 경우 갖고 오고 싶은 정보만 엑셀에 넣기 위해 따로 작업
    //BookField{ title, author, rank, date, ... }
    private List<String> getSelectedHeader(List<String> selectedHeader){
        List<String> selectedHeaderName = new ArrayList<>();
        selectedHeader.stream().forEach( header -> selectedHeaderName.add(BookField.valueOf(header).getDesc()) );
        return selectedHeaderName;
    }
    private ByteArrayOutputStream makeByteArrayOutputStream(List<Book> books, List<String> selectedHeader, List<String> headerNames, String password) {
        //INIT
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Sheet1");
        int col = 0, rowNum = 0, idx;
        Cell cell;

        //STYLE
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 10);
        font.setFontName("Arial");
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setFont(font);
        style.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.index);
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);

        Row row = sheet.createRow(rowNum++);
        //HEADER
        for (String header : headerNames) {
            cell = row.createCell(col);
            cell.setCellValue(header);
            cell.setCellStyle(style);
            col++;
        }
        //DATA SET
        for (Book item : books) {
            idx = 0;
            row = sheet.createRow(rowNum++);
            //클래스의 속성 정보를 BeanWrapperImpl을 통해 get
            BeanWrapper itemWrapper = new BeanWrapperImpl(item);
            for (String headerCode : selectedHeader) {
                createCell(row, itemWrapper.getPropertyValue(headerCode), headerCode, idx++);
            }
        }
        //EXPORT XLS
        try {
            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
            workbook.write(outputStream);
            outputStream = this.encrypt(outputStream, password);
            outputStream.close();
            return outputStream;
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
    private ByteArrayOutputStream encrypt(ByteArrayOutputStream byteArrayOutputStream, String password) {
        try {
            //전달받은 excel outputstream에 암호를 추가
            InputStream inputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
            POIFSFileSystem fs = new POIFSFileSystem();
            EncryptionInfo info = new EncryptionInfo(fs, EncryptionMode.agile);
            Encryptor enc = info.getEncryptor();
            enc.confirmPassword(password);
            OPCPackage opc = OPCPackage.open(inputStream);
            OutputStream os = enc.getDataStream(fs);
            opc.save(os);
            opc.close();
            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
            fs.writeFilesystem(outputStream);
            outputStream.close();
            return outputStream;
        }catch(IOException e ){
            throw new RuntimeException();
        } catch( InvalidFormatException e){
            throw new RuntimeException();
        } catch( GeneralSecurityException e ){
            throw new RuntimeException();
        }
    }
    private Cell createCell(Row row, Object value, String headerCode, int columnNumber) {
        Cell cell;
        if (value == null) {
            cell = row.createCell(columnNumber, Cell.CELL_TYPE_BLANK);
        } else if (value instanceof String) {
            cell = row.createCell(columnNumber, Cell.CELL_TYPE_STRING);
            //headerCode에 따라 현재 들어가있는 정보가 변환이 필요한지 체크하여 변환된 텍스트를 세팅
            cell.setCellValue(this.changeText(headerCode, value.toString()));
        } else if (value instanceof Number) {
            cell = row.createCell(columnNumber, Cell.CELL_TYPE_NUMERIC);
            cell.setCellValue(((Number) value).doubleValue());
        } else if (value instanceof Date) {
            cell = row.createCell(columnNumber, Cell.CELL_TYPE_STRING);
            cell.setCellValue(value.toString());
        } else if (value instanceof Calendar) {
            cell = row.createCell(columnNumber, Cell.CELL_TYPE_NUMERIC);
            cell.setCellValue((Calendar) value);
        } else if (value instanceof Boolean) {
            cell = row.createCell(columnNumber, Cell.CELL_TYPE_BOOLEAN);
            cell.setCellValue((Boolean) value);
        } else if (value instanceof LocalDate) {
            cell = row.createCell(columnNumber, Cell.CELL_TYPE_STRING);
            cell.setCellValue(DATE_FORMATTER.format(((LocalDate) value)));
        } else if (value instanceof LocalDateTime) {
            cell = row.createCell(columnNumber, Cell.CELL_TYPE_STRING);
            cell.setCellValue(DATE_TIME_FORMATTER.format(((LocalDateTime) value)));
        } else {
            cell = row.createCell(columnNumber, Cell.CELL_TYPE_STRING);
            cell.setCellValue(value.toString());
        }
        return cell;
    }
    private String changeText(String code, String value){..}
    private boolean sendMailWithXls(ByteArrayOutputStream baos, String pwd, User user){ ..//send mail.. }

* 참고

- https://poi.apache.org/encryption.html

- https://stackoverflow.com/questions/8817290/create-a-password-protected-excel-file-using-apache-poi

- http://www.quicklyjava.com/create-password-protected-excel-using-apache-poi/