POI 엑셀 생성 및 다운로드 (feat. RowHandler, ResultHandler)
Apache POI
아파치 소프트웨어 재단에서 만든 라이브러리로서 마이크로소프트 오피스 파일 포맷을 순수 자바 언어로 읽고 쓰는 기능을 제공한다.
[위키 백과]
여러가지 ms 오피스 포맷을 제공하지만, 가장 많이 쓰이는 엑셀 포맷에 대해 알아보자.
public ExcelWriterTest {
public void writeExcel() {
HSSFWorkbook workBook = new HSSFWorkBook(); // 엑셀 파일 1개를 의미
Sheet sheet = workBook.createSheet(); // 엑셀 파일의 시트(sheet)
int rowIdx = 0; // 행 idx
int colIdx = 0; // 열 idx
Row row = sheet.createRow(rowIdx++); // 엑셀 파일의 행
Cell cell = null; // 엑셀 파일의 열
cell = row.createCell(colIdx++);
cell.setCellValue("첫번재 열 제목");
cell = row.createCell(colIdx++);
cell.setCellValue("두번째 열 제목");
colIdx = 0;
row = sheet.createRow(rowIdx);
cell = row.createCell(colIdx++);
cell.setCellValue("첫번재 열 데이터");
cell = row.createCell(colIdx++);
cell.setCellValue("두번째 열 데이터");
FileOutputStream fos = null;
try (fos = new FileOutputStream(new File("c:\\excel\\exceltestfile.xls"))) {
workBook.write(fos);
} catch (Exception e) {
logger.error("Write Excel File Error.", e);
}
}
}간단하게 보자면, 대략 위와 같은 형태이다.
HSSFWorkbook 클래스는 POI에서 제공하는 클래스로 엑셀 파일 1개라고 보면 된다.
HSSFWorkbook에서 Sheet를 만들고, Sheet에서 Row를 만들고, Row에서 Cell을 만드는 형태이다.
HSSFWorkbook은 주로 .xls 엑셀 파일을 생성할 때 사용되며,
.xlsx 엑셀 파일을 생성할 때는 XSSWorkbook을 사용하면 된다.
xls형식
- Microsoft Excel 97-2003 버젼 사용되는 형식
- 최대 256컬럼, 65,536행 제한
- POI를 이용하여 데이터 접근 시 org.apache.poi.hssf 패키지 이용org.apache.poi.hssf.usermodel.HSSFWorkbook
- org.apache.poi.hssf.usermodel.HSSFSheet
- org.apache.poi.hssf.usermodel.HSSFRow
- org.apache.poi.hssf.usermodel.HSSFCell
xlsx형식
- Microsoft Excel 2007 이 후 버젼 사용되는 형식
- 최대 16,384컬럼, 1,048,567행 제한
- POI를 이용하여 데이터 접근 시 org.apache.poi.xssf패키지 이용org.apache.poi.xssf.usermodel.XSSFWorkbook
- org.apache.poi.xssf.usermodel.XSSFSheet
- org.apache.poi.xssf.usermodel.XSSFRow
- org.apache.poi.xssf.usermodel.XSSFCell
위의 내용을 보면 대용량 데이터를 엑셀로 저장할 때는 당연히 xlsx형식을 사용하는게 좋다.
그렇지만...고객사에서 사용하는 Excel 버전이 97-2003 버전이라면, xls형식을 사용하면서 대용량 데이터를 엑셀로 저장할 수 있도록 해줘야한다.
또 만약 100만건도 넘는 데이터를 위와 같은 방식으로 엑셀로 저장하려 한다면?
메모리가 꽤 크지 않는 이상 OOME가 발생할 가능성이 높다.
이런 문제 때문에 ibatis, mybatis에서는 RowHandler, ResultHandler라는 interface를 제공하고 있다.
RowHandler, ResultHandler는 쿼리에서 모든 데이터를 한번에 가져오는 것이 아니라 fetch 사이즈를 지정하여 정해진 수 만큼씩 데이터를 가져와서 각 row별로 처리를 할 수 있게 지원한다.
(* fetch 사이즈 지정하는 방법은 아래에서 설명 예정)
RowHandler / ResultHandler
RowHandler : ibatis에서 제공하는 Handler interface
ResultHandler : mybatis에서 제공하는 Handler interface
ResultHandler를 사용하기 위해서는 먼저 구현체를 생성해야한다.
(RowHandler는 ResultHandler와 거의 명칭만 다르기 때문에 예시로 ResultHandler만 작성함.)
public class ExcelHandler<T> implements ResultHandler {
private Workbook workbook;
private Sheet sheet;
private Row row;
private int rowIdx;
@Override
public void handleResult(ResultContext resultContext) {
ResultDto dto = (ResultDto) resultContext.getResultObject();
createBody(dto);
}
// 생성자
public ExcelHandler(Workbook workbook) {
this.workbook = workbook;
createHeader();
}
// 엑셀 제목 생성
public void createHeader() {
int colIdx = 0;
row = sheet.createRow(0);
row.createCell(colIdx++).setCellValue("타이틀1");
row.createCell(colIdx++).setCellValue("타이틀2");
...
}
// 엑셀 내용 생성
public void createBody(ResultDto dto) {
int colIdx = 0;
row = sheet.createRow(rowIdx++);
row.createCell(colIdx++).setCellValue(dto.getData1());
row.createCell(colIdx++).setCellValue(dto.getData2());
...
}
}이제 위의 Handler 구현체를 사용하는 부분을 작성해보자.
HSSFWorkbook workbook = new HSSFWorkbook();
ExcelHandler<ResultDto> handler = new ExcelHandler<>(workbook);
// 아래는 예시로 작성한 dao, 메소드
testDao.getDataWithHandler(param, handler);예시로 작성한 testDao의 내용은 아래와 같다.
public void getDataWithHandler(ParamDto param, ExcelHandler<ResultDto> handler) {
sqlsession.select("getDataWithHandler", param, handler);
}이렇게 하게되면, SQL 쿼리 결과를 한번에 다 가져오지않고, row별로 엑셀에 저장할 수 있게 되고, 불필요한 메모리 할당이 없어지기 때문에 OOME를 방지할 수 있다.
* fetch 사이즈 조정하는 방법!
- mapper xml 파일에 작성한 select 쿼리에서 조정할 수 있다.
<select id="getDataWithHandler" resulttype="string" fetchSize="-2147483648">
SELECT name FROM players
</select>- 위의 예시와 같이 fetchSize를 설정하면 설정된 값만큼 씩 row를 잘라서 가져오게 된다
- 근데 웃긴건...fetchSize를 1로 지정하면 1개의 row씩 가져오지 않는다... integer의 최소값 (-2147483648)으로 지정해야지만 1개씩 가져올 수 있다. (공식 문서에 나와있다..왜인지는 모르겠다..)