[BE] Spring boot Excel Download (feat. apache.poi)

Spring Boot + apache.poi를 활용하여 Excel Download를 구현해보자.

1. build.gradle에 apache.poi 종속성를 추가한다. (5.0.0 버전을 사용했다.)

dependencies {
    implementation 'org.apache.poi:poi:5.0.0'
    implementation 'org.apache.poi:poi-ooxml:5.0.0'
}

2. 다운로드 요청을 받을 컨트롤러를 추가한다.

@RestController
public class ExcelController {

	@GetMapping(value="/exceldownload")
	public void ExcelDownload(HttpServletResponse response) throws Exception {
    }
}

3. Excel 객체를 생성한다.
https://poi.apache.org/apidocs/dev/org/apache/poi/xssf/streaming/SXSSFWorkbook.html

SXSSFWorkbook wb = new SXSSFWorkbook(100); //Row Size in Memory, exceeding rows will be flushed to disk
Sheet sheet = wb.createSheet("SHEET");
Row row1 = sheet.createRow(0);
Cell cell = row1.createCell(0);
cell.setCellValue(0);

4. Excel객체를 Response 스트림에 Write한다.

try {
    response.setContentType("application/vnd.ms-excel");
    response.setHeader("Content-Disposition", String.format("attachment;filename=%s.xlsx", "workbook.xlsx"));
    wb.write(response.getOutputStream());
    wb.dispose(); //for SXSSFWorkbook

    response.getOutputStream().flush();
    response.getOutputStream().close();
    
} catch (FileNotFoundException e) {
    e.printStackTrace();
} catch (IOException e) {
    e.printStackTrace();
}

5. API를 테스트한다.