[JAVA] 엑셀 파일 생성과 다운로드 예제 (feat. Apache Poi Excel Create)

 

 

 

 

 

 

 

 

 

 

 

1. GET 방식의 API 생성

---

@GetMapping("/get/excelDown")
public void excel(HttpServletRequest req, HttpServletResponse res) {
  try {
  	// 엑셀 다운로드 
  	ExcelUtil.excelDown(res);
  }catch(Exception e) {
  	e.printStackTrace();
  }
}

 

 

 

 

 

 

2. ExcelUtil 클래스에 엑셀 다운로드 메서드 생성

---

public static void excelDown(HttpServletResponse res) {
    List<PlayerVo> listData = new ArrayList<PlayerVo>();
    PlayerVo playerVo = new PlayerVo();

    playerVo.setPlayerName("손흥민");
    playerVo.setPlayerNum("7");
    playerVo.setPosition("ST");
    listData.add(playerVo);

    playerVo = new PlayerVo();
    playerVo.setPlayerName("로메로");
    playerVo.setPlayerNum("17");
    playerVo.setPosition("CB");
    listData.add(playerVo);

    playerVo = new PlayerVo();
    playerVo.setPlayerName("메디슨");
    playerVo.setPlayerNum("10");
    playerVo.setPosition("CAM");
    listData.add(playerVo);

    if(listData != null && listData.size() > 0) {
        //엑셀 파일명
        final String fileName = "playerList.xlsx";

        //엑셀 그리기
        final String[] colNames = {
        	"순서", "성명", "등번호", "포지션"
        };

        //헤더 사이즈
        final int[] colWidths = {
        	3000, 5000, 5000, 3000
        };

        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = null;
        XSSFCell cell = null;
        XSSFRow row = null;

        //Font 설정
        Font fontHeader = workbook.createFont();
        fontHeader.setFontName("맑은 고딕");	//글씨체
        fontHeader.setFontHeight((short)(9 * 20));	//사이즈
        fontHeader.setBoldweight(Font.BOLDWEIGHT_BOLD);	//볼드(굵게)
        Font font9 = workbook.createFont();
        font9.setFontName("맑은 고딕");	//글씨체
        font9.setFontHeight((short)(9 * 20));	//사이즈
        //엑셀 헤더 셋팅
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
        headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        headerStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        headerStyle.setFont(fontHeader);
        //엑셀 바디 셋팅
        CellStyle bodyStyle = workbook.createCellStyle();
        bodyStyle.setAlignment(CellStyle.ALIGN_CENTER);
        bodyStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        bodyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        bodyStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        bodyStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        bodyStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        bodyStyle.setFont(font9);
        //엑셀 왼쪽 설정
        CellStyle leftStyle = workbook.createCellStyle();
        leftStyle.setAlignment(CellStyle.ALIGN_LEFT);
        leftStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        leftStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        leftStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        leftStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        leftStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        leftStyle.setFont(font9);

        //rows
        int rowCnt = 0;
        int cellCnt = 0;
        int listCount = listData.size();

        //엑셀 시트명 설정
        sheet = workbook.createSheet("토트넘 선수 명단");
        row = sheet.createRow(rowCnt++);

        //헤더 정보 구성
        for (int i = 0; i < colNames.length; i++) {
            cell = row.createCell(i);
            cell.setCellStyle(headerStyle);
            cell.setCellValue(colNames[i]);
            sheet.setColumnWidth(i, colWidths[i]);	//column width 지정
        }

        //데이터 부분 생성
        for(PlayerVo vo : listData) {
            cellCnt = 0;
            row = sheet.createRow(rowCnt++);
            //순서
            cell = row.createCell(cellCnt++);
            cell.setCellStyle(bodyStyle);
            cell.setCellValue(listCount--);
            //성명
            cell = row.createCell(cellCnt++);
            cell.setCellStyle(bodyStyle);
            cell.setCellValue(vo.getPlayerName());
            //등번호
            cell = row.createCell(cellCnt++);
            cell.setCellStyle(bodyStyle);
            cell.setCellValue(vo.getPlayerNum());
            //포지션
            cell = row.createCell(cellCnt++);
            cell.setCellStyle(bodyStyle);
            cell.setCellValue(vo.getPlayerPosition());
        }
        res.setContentType("application/vnd.ms-excel");
        //엑셀 파일명 설정
        res.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        try {
            workbook.write(res.getOutputStream());
            } catch(IOException e) {
                e.printStackTrace();
            } catch(Exception e) {
                e.printStackTrace();
            }
        }
}

 

 

 

 

 

 

3. 호출하기

---

location.href="/get/excelDown";