[Java] apache poi excel

apache에서 제공하는 Poi 라이브러리는 Excel 출력을 도와준다. 나는 maven을 build tool로 사용했다.

1. maven pom.xml에 dependency 추가
<!-- apache poi -->
<dependency>
<groupId>org.apache.poi</groupId>xls
<artifactId>poi</artifactId>
<version>3.16-beta2</version>
</dependency>
-> 이 부분이 .xls까지 지원
 
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.16-beta2</version>
</dependency>

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>xlsx, office open xml
<version>3.16-beta2</version>
</dependency>
    -> 이 부분이 .xlsx까지 지원, 이때 ooxml은 Office Open Xml이라고 ecma표준. (.docx, .pptx, xlsx)

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-excelant</artifactId>
<version>3.16-beta2</version>
</dependency>

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.16-beta2</version>
</dependency>
 
    

2. Controller service단에 excel 문서 출력함수 추가

@Service

@Transactional(readOnly = true)

public class ExcelDownSupporter {

@Autowired

private ServletContext sc;

public void buildExcelDoc(HttpServletRequest request, HttpServletResponse response, 

String title, String[] headerList, String[] dataInfo, List<?> dataList) throws Exception {

ObjectMapper mapper = new ObjectMapper();

List<Map<String, Object>> mapList = mapper.convertValue(dataList, new TypeReference<List<Map<String, Object>>>() {});

//response.setHeader("ContentType", "application/vnd.ms-excel");

response.setHeader("ContentType", "application/octet-stream");

response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(title,"UTF-8") + ".xlsx");

//row 갯수

int rowCnt = 0;

//Excel 생성(초기화)

SXSSFWorkbook workBook = new SXSSFWorkbook();

workBook.setCompressTempFiles(true);

//sheet 생성

SXSSFSheet sheet = workBook.createSheet();

//첫번째 row 공백

sheet.createRow(rowCnt);

//제목

SXSSFRow titleRow = sheet.createRow(rowCnt++);

SXSSFCell titleCell = titleRow.createCell(1);

titleCell.setCellValue(title);

//제목과 리스트 사이의 공백생성

sheet.createRow(rowCnt++);

//header row 생성

SXSSFRow headerRow = sheet.createRow(rowCnt++);

CellStyle cs = workBook.createCellStyle();

cs.setWrapText(true);

//header Cell별로 데이터 밀어넣기

SXSSFCell headerCell = null;

int headerCnt = 1;

for(String headerData : headerList) {

headerCell = headerRow.createCell(headerCnt);

headerCell.setCellValue(headerData);

headerCell.setCellStyle(cs);

}

//dataList rows생성

int cellCnt = 1;

SXSSFRow dataRow = null;

SXSSFCell dataCell = null;

SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

String switchData = null;

for(Map<String, Object> rows : mapList) {

dataRow = sheet.createRow(rowCnt++);

for(String cellKey : dataInfo) {

dataCell = dataRow.createCell(cellCnt);

if(cellKey.indexOf("_function") != -1) {

switchData = this.renderFunction(cellKey, rows);

dataCell.setCellValue(switchData);

} else if(rows.get(cellKey) != null && cellKey.toUpperCase().indexOf("DATE") != -1) {

dataCell.setCellValue(dateFormat.format(rows.get(cellKey)).toString());

} else if(rows.get(cellKey) != null) {

dataCell.setCellValue(rows.get(cellKey).toString());

}

        

cellCnt++;

}

cellCnt = 1;

}

ServletOutputStream out = null;

try {

out = response.getOutputStream();

workBook.write(out);

} catch(IOException e) {

System.out.println("ExcelDownSuppoter.class 105 Line!! error : " + e.getMessage());

} finally {

try {

out.flush();

workBook.dispose();

} catch(Exception e) {

System.out.println("Finally Error");

}

}