Language/Java
SpringBoot에서 엑셀파일 암호화 후 내려받기
건담아빠
2024. 6. 18. 17:30
개인정보가 들어간 엑셀 파일을 다운로드 하게 되면 보안 이슈가 발생 할수도 있으니 급하게 엑셀 다운로드 파일에 암호를 넣고 릴리즈 후 기록해 둔다.
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를 믿지 말자. 이놈이 자꾸 훼이크 써써 시간이 더 걸릴때도 많다.