SpringBoot에서 엑셀파일 암호화 후 내려받기

개인정보가 들어간 엑셀 파일을 다운로드 하게 되면 보안 이슈가 발생 할수도 있으니 급하게 엑셀 다운로드 파일에 암호를 넣고 릴리즈 후 기록해 둔다.

 

TestController

@Tag(name = "test")
@AppController(path = "/v1")
@RequiredArgsConstructor
public class TestController extends BaseController {
  ...
  
  @Operation(summary = "어쩌구 저쩌구 엑셀 다운로드", description = "")
  @GetMapping(value = "/tests/{testId}/comments/export")
  public ResponseEntity<byte[]> export(@PathVariable("testId") String testId, JsonNode jsonNode, HttpServletResponse response, ...)
    throws Exception {
    ...

    byte[] bytes = testService.getCommentListExcel(params);

    // CORS 헤더 허용 (React에서 파일명 받아야 하니깐 추가)
    response.addHeader("Access-Control-Expose-Headers", "Content-Disposition");

    HttpHeaders headers = new HttpHeaders();
    headers.setContentType(MediaType.parseMediaType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
    headers.setContentLength(bytes.length);
    headers.setContentDispositionFormData("attachment", URLEncoder.encode("콘텐츠_댓글_사용자목록.xlsx", "UTF-8"));

    return new ResponseEntity<>(bytes, headers, HttpStatus.OK);
  }
  ...
}

 

TestService

@Service
@RequiredArgsConstructor
public class TestService extends BaseService {
  ...
  
  public byte[] getCommentListExcel(Map<String, Object> params) throws Exception {
    String password = Utils.makeNumEngRandomString(6);

    List<Map<String, String>> data = commentDao.getCommentList(params);

    Header header = new Header();
    header.addColumn(new Column("ROW_NUM", "순서", 200,  Style.ColumnAlign.CENTER));
    header.addColumn(new Column("NICKNAME", "닉네임", 400, Style.ColumnAlign.CENTER));
    ...
    header.addColumn(new Column("CONTENT", "내용", 700, Style.ColumnAlign.LEFT));
	
    List<String[]> items = this.getCommentListExcelItems(header, data);
    byte[] excelBytes = ExcelUtil.getEncryptedExcelBytes(header, items, password);

    // SNS 발송
    StringBuilder sb = new StringBuilder();
    sb.append("[댓글작성 회원목록]\n");
    sb.append(String.format("비밀번호 : %s\n", password));
    PurplebookBase.sendMessage(params.get("hpNo").toString(), sb.toString());

    return excelBytes;
  }

  public static List<String[]> getCommentListExcelItems(Header header, List<Map<String, String>> list) throws Exception {
    List<String[]> items = new ArrayList<>();

    for (int rowIndex = 0; rowIndex < list.size(); rowIndex++) {
      Map<String, String> rowData = list.get(rowIndex);
      String[] row = new String[header.getColumns().size()];

      for (int columnIndex = 0; columnIndex < header.getColumns().size(); columnIndex++) {
        Column column = header.getColumns().get(columnIndex);
        String value = rowData.get(column.getId());

        if (column.getId().equals("ROW_NUM") == true) {
          row[columnIndex] = String.valueOf(rowIndex + 1);
          ...
          ...
        } else if (column.getId().equals("REG_DT") == true || column.getId().equals("MOD_DT") == true) {
          row[columnIndex] = DateUtils.getDate(DateUtils.getDateFromStringFormat("yyyy-MM-dd HH:mm:ss", value), "yyyy-MM-dd HH:mm");
        } else {
          row[columnIndex] = value;
        }
      }

      items.add(row);
    }

    return items;
  }
  
  ...  
}

 

ExcelUtils

package net.neoflat.common.core.util;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import net.neoflat.common.dto.excel.Column;
import net.neoflat.common.dto.excel.Header;
import net.neoflat.common.dto.excel.Style;
import org.apache.poi.poifs.crypt.EncryptionInfo;
import org.apache.poi.poifs.crypt.EncryptionMode;
import org.apache.poi.poifs.crypt.Encryptor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtil {
  ...
  
  private static XSSFWorkbook getWorkbook(Header header, List<String[]> items) {

    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet();

    int rowIndex = 0;
    XSSFRow row = sheet.createRow(rowIndex++);
    for (int columnIndex = 0; columnIndex < header.getColumns().size(); columnIndex++) {
      Column column = header.getColumns().get(columnIndex);

      // TODO. 열 너비
//      sheet.setColumnWidth(0, characterWidth * 256); // 첫 번째 열의 너비 설정
      sheet.setColumnWidth(columnIndex, column.getWidth() * 10);

      Cell cell = row.createCell(columnIndex);
      cell.setCellValue(column.getTitle());

      // TODO. Style 지정
      cell.setCellStyle(Style.getHeaderStyle(workbook));
    }

    for (String[] record : items) {
      row = sheet.createRow(rowIndex++);
      for (int columnIndex = 0; columnIndex < header.getColumns().size(); columnIndex++) {
        Column column = header.getColumns().get(columnIndex);

        Cell cell = row.createCell(columnIndex);
        String value = record[columnIndex];
        if (value != null) {
          cell.setCellValue(value.toString());
          cell.setCellStyle(Style.getCellStyle(workbook, column));
        }
      }
    }
    
    ...

    return workbook;
  }

  /**
   * 엑셀 기본
   *
   * @param header
   * @param items
   * @return
   * @throws IOException
   */
  public static byte[] getExcelBytes(Header header, List<String[]> items) throws IOException {
    XSSFWorkbook workbook = getWorkbook(header, items);

    // Write workbook to ByteArrayOutputStream
    ByteArrayOutputStream bos = new ByteArrayOutputStream();
    workbook.write(bos);
    workbook.close();
    bos.close();

    return bos.toByteArray();
  }

  /**
   * 엑셀 파일 암호화
   *
   * @param header
   * @param items
   * @param password
   * @return
   * @throws Exception
   */
  public static byte[] getEncryptedExcelBytes(Header header, List<String[]> items, String password) throws Exception {
    XSSFWorkbook workbook = getWorkbook(header, items);

    // Create a ByteArrayOutputStream to hold the encrypted Excel file
    ByteArrayOutputStream bos = new ByteArrayOutputStream();

    // Encrypt the workbook and write to the ByteArrayOutputStream
    POIFSFileSystem fs = new POIFSFileSystem();
    EncryptionInfo info = new EncryptionInfo(EncryptionMode.standard);
    Encryptor encryptor = info.getEncryptor();
    encryptor.confirmPassword(password); // Replace with your desired password

    try (OutputStream os = encryptor.getDataStream(fs)) {
      workbook.write(os);
    }

    workbook.close();

    fs.writeFilesystem(bos);
    bos.close();

    return bos.toByteArray();
  }
  
  ...
}

 

너무 GPT를 믿지 말자. 이놈이 자꾸 훼이크 써써 시간이 더 걸릴때도 많다.

 

참고)

https://www.wrapuppro.com/programing/view/YWSRrN2wMH3FlW9