Apache POI 라이브러리를 이용해 Java에서 엑셀 파일 만들기 (DB to File)

 Apache POI란?

Java에서 MS Office 문서를 다룰 수 있는 라이브러리로 엑셀 파일을 다룰 때 주로 사용된다.

 

 

POI에서 엑셀 파일을 읽고 쓸 때 사용하는 Workbook 클래스 (HSSFWorkbook VS XSSFWorkbook)

	HSSFWorkbook	XSSFWorkbook	SXSSFWorkbook
특징	Excel 97-2003 포맷(.xls) 파일 지원 바이너리 포맷 사용 65,536 행과 256 열로 제한	Excel 2007 이상 포맷(.xlsx) 파일처리 XML 기반 OpenXML 포맷 사용 1,048,576 행과 16,384 열을 지원	XSSFWorkbook을 기반으로 한 스트리밍 방식의 워크북. 메모리 사용을 줄이기 위해 대량의 데이터(수백만 셀 이상)를 다룰 때 적합. 작업 시점에 메모리에 저장되지 않고, 디스크를 이용해 데이터를 저장하며 처리.
장점	.xls 파일과 호환성이 필요할 때 적합	최신 엑셀 포맷을 사용하며, 행과 열의 수 제한이 적음 xlsx 형식은 더 효율적인 압축을 지원하여 파일 크기가 작음 조건부 서식, 테두리, 스타일 등 지원	대량의 데이터(수백만 행 이상)를 처리할 때 메모리 효율적입
단점	.xls 형식은 용량이 크고, 최신 Excel 기능을 지원하지 않음. 행과 열의 수 제한. 조건부 서식 등 일부 기능 제한.	.xls보다 처리 속도가 약간 느릴 수 있음	기존 데이터에 대한 Random Access 제한 읽기보다는 쓰기에 적합
선택기준	.xls 파일과의 호환성 필요 시	.xlsx 파일을 처리하거나 대규머 데이터 처리 시	대량 데이터 처리와 메모리 사용 최적화 중요 시

 

 

예제 코드

// HSSFWorkbook: .xls 파일 생성
Workbook workbook = new HSSFWorkbook();

// XSSFWorkbook: .xlsx 파일 생성
Workbook workbook = new XSSFWorkbook();

// SXSSFWorkbook: 대량 데이터 처리에 적합한 .xlsx 파일 생성
Workbook workbook = new SXSSFWorkbook();

// 공통 처리
Sheet sheet = workbook.createSheet("Sheet1");
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("Hello POI");

// 파일 저장
try (FileOutputStream fileOut = new FileOutputStream("example.xlsx")) {
    workbook.write(fileOut);
}

 

 

셀 서식 적용 - CellStyle

: 테두리, 셀 스타일 등을 적용하는 스타일 객체를 과도하게 생성하면 메모리와 성능에 영향을 미친다.

동일한 스타일을 여러 셀에 적용할 때는 셀 스타일을 개별로 생성하지 말고 재사용 하는 것이 좋다.

CellStyle style = workbook.createCellStyle();
style.setBorderBottom(BorderStyle.THIN);
// 여러 셀에 동일 스타일 적용
cell1.setCellStyle(style);
cell2.setCellStyle(style);

 

* CellStyle의 구현체로 조금 더 세밀한 스타일 적용이 가능한 XSSFCellStyle도 있다. 하지만 SXSSFWorkbook에서는 기본적인 CellStyle만 사용 가능하다. XSSFCellStyle을 적용하기 위해서는 SXSFFWorkbook으로 파일을 먼저 작성 후 XSSFWorkbook으로 파일을 열어 추가하면 된다.

 

* CellStyle과 XSSFCellStyle 비교

기능	CellStyle (인터페이스)	XSSFCellStyle (XSSF 구현체)
지원 파일 포맷	HSSF(.xls), XSSF(.xlsx) 등 공통 인터페이스	XSSF(.xlsx) 전용
색상 지원	제한된 팔레트 (HSSF는 56색)	RGB 기반 색상 (사용자 정의 가능)
조건부 서식	기본 지원	복잡한 조건부 서식 지원
배경 및 채우기 패턴	단순 패턴만 지원	다양한 색상 및 채우기 패턴 제공
글꼴 스타일	기본 글꼴 설정 (색상, 크기 제한)	글꼴 굵기, 색상, 서체, 이중 취소선 등 지원
데이터 형식	기본적인 데이터 형식(날짜, 숫자 등)만 설정 가능	사용자 정의 데이터 형식 가능
테두리 스타일	간단한 스타일 (얇은, 굵은 등 제한적)	더 다양한 테두리 옵션 제공

 

 

조건부 서식 적용 - SheetConditionalFormatting 사용

SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

// 조건부 서식 조건 생성
ConditionalFormattingRule rule = sheetCF.createConditionalFormattingRule(
    ComparisonOperator.EQUAL, "1"
);

// 조건에 맞는 데이터에 서식 지정
PatternFormatting fill = rule.createPatternFormatting();
        fill.setFillBackgroundColor(IndexedColors.YELLOW.index);
        fill.setFillPattern(FillPatternType.SOLID_FOREGROUND);

// 조건부 서식 적용 범위(Al:A100에 위에서 생성한 조건 (rule) 적용
sheetCF.addConditionalFormatting(new CellRangeAddress[] {
    CellRangeAddress.valueOf("A1:A100")
}, rule);

 

 

Apache Poi 공식 홈페이지를 보면 Javadoc과 가이드 등이 예시와 함께 제공되고 있다.

https://poi.apache.org/components/spreadsheet/examples.html

HSSF and XSSF Examples

 

 

---

 

내가 다뤄야할 데이터는 DB기준 대략 8천 Row, 그리고 엑셀에 넣고 싶은 내용은 350 Colum 정도였다.

그러니까 8,000 X 350 = 대략 280만 Cell이 포함된 시트를 만들어야 한다.

(이게 내가 생각하는 거의 최대 데이터였고, 적은 건 200 row인 경우도 있었다.)

 

필요한 서식은 다음과 같다.

• 헤더에 날짜 데이터 입력
• 틀 고정
• 전체 테두리 적용
• 헤더는 Bold 및 가운데 정렬
• 회색 음영 지정
• 날짜 데이터 기준으로 특정 조건에 조건부 서식 지정
• 연속 데이터는 셀병합
• 자돌 열너비 조정
• 배율 지정

이런 상황에서 선택할 수 있는 워크북은 XSSF나 SXSSF.

처음에 XSSFWorkbook을 이용해 작성해서 어느 정도 원하는 모양이 나왔으나, 속도 이슈가 있어서 윈도우 사이즈를 1000으로 지정하고 SXSSF로 바꿨더니 데이터가 1000건을 넘는 경우 6천개가 넘는 row가 전부 null로 인식이 되는거다. 😭

내 생각엔 윈도우 사이즈가 1000이므로 0~999는 제대로 나오고, 나머지가 null 인건 이해가 됬으나 0~6xxx가 전부 null이라는게 의아했다. 그래서 SXSSF의 특징을 다시 찾아보니 메모리 사용을 최소화 하기 위해 Sheet에서 생성된 최근 N(윈도우 사이즈)개의 Row만 메모리에 유지한다고 한다. 즉 윈도우 사이즈를 초과하는 Row는 메모리에서 제거되었기 때문에 0~6xxx의 row는 전부 null이었고, 마지막으로 생성한 7000~만 접근이 가능한 문제였다.

 

나는

1. DB에서 데이터 조회

2. SXSSF를 이용해 .xlsx 파일생성

3. DB 데이터를 이용 해 헤더 Row, Colum 생성 후 테두리 및 음영 등 서식 지정

4. 조건부 서식 지정 -> 조건부 서식이 적용된 셀 중 연속 데이터는 셀 병합

5. 틀 고정 및 자동 열 너비 적용

6. 배율 지정

7. 파일 저장

의 과정으로 파일을 작성하고 있었다.

 

위의 문제가 발생한 지점은 4번에서 조건부 서식 지정 후 열 병합 중 발생하는 문제였다.

SXSSFWorkbok에서는 이전 Row에 대한 임의 접근이 불가능하므로 Row를 생성하자 마자 조건에 따라 병합을 하는 것으로 순서를 조정하자 문제가 해결되었다.