[Spring] [Excel] 웹에서 DB를 엑셀파일로 추출시키기.

 

<!-- [dependency 추가]  -->
<dependency>
    <groupId>org.apache.poi</groupId>
     <artifactId>poi</artifactId>
     <version>3.14</version>
</dependency>

Mapper를 만들고,
C11ReceiptVO - 도메인 생성 만들고,

액셀에서 각 4가지 카테고리별로 4가지 시트로 생성시킬 것이다.

//: [C11EvaluationExcelView.Java]
public class C11EvaluationExcelView extends AbstractExcelView  {

    private static final Logger logger = LoggerFactory.getLogger(C11EvaluationExcelView.class);


    protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request,
            HttpServletResponse response) throws Exception {
        // TODO Auto-generated method stub
        logger.debug("[EvaluationExcelView] START >>>>>>>>>>>>>>>>>>>>>>>>>> ");

        Map<String, Object> map = (Map<String, Object>) model.get("excelMap");
        
        List<C11ReceiptVO> product = (List<C11ReceiptVO>) map.get("product");
        List<C11ReceiptVO> system = (List<C11ReceiptVO>) map.get("system");
        List<C11ReceiptVO> poster = (List<C11ReceiptVO>) map.get("poster");
        List<C11ReceiptVO> cali = (List<C11ReceiptVO>) map.get("cali");
        List<C11ReceiptVO> essay = (List<C11ReceiptVO>) map.get("essay");
        
        String title = "심사 결과";
        
        List<String> aStr = new ArrayList<String>();
        aStr.add("번호");
        aStr.add("접수번호");
        aStr.add("대상");
        aStr.add("그룹");
        aStr.add("심사위원1");
        aStr.add("심사위원2");
        aStr.add("심사위원3");
        aStr.add("심사위원4");
        aStr.add("심사위원5");
        aStr.add("심사위원6");
        aStr.add("총점");
        aStr.add("평균");

        createSheetWithData(workbook, "제품아이디어", aStr, product);
        createSheetWithData(workbook, "설비아이디어", aStr, system);
        createSheetWithData(workbook, "포스터", aStr, poster);
        createSheetWithData(workbook, "캘리그라피", aStr, cali);
        createSheetWithData(workbook, "에세이", aStr, essay);
        
        
        String excelName= URLEncoder.encode(title,"UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "ATTachment; Filename=" + excelName + ".xls");
        
        logger.debug("[EvaluationExcelView] END >>>>>>>>>>>>>>>>>>>>>>>>>> ");
        
    }

    private void createSheetWithData(HSSFWorkbook workbook, String title, List<String> aStr, List<C11ReceiptVO> data) {
boolean isVO = false;
        
        if(data.size() > 0) {
            Object obj = data.get(0);
            isVO = obj instanceof C11ReceiptVO;
        }
        
        Sheet sheet = workbook.createSheet(title);
        sheet.setDefaultColumnWidth(16);
        
        Font bold11 = workbook.createFont();
        bold11.setBold(true);
        bold11.setFontHeightInPoints((short) 11);
        
        CellStyle cs = workbook.createCellStyle();
        cs.setAlignment(CellStyle.ALIGN_CENTER); 
        cs.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);  
        cs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cs.setFont(bold11);
        
        CellStyle defaultCs = workbook.createCellStyle();
        defaultCs.setAlignment(CellStyle.ALIGN_CENTER); 
        
        Font boldFont16 = workbook.createFont();
        boldFont16.setBold(true);
        boldFont16.setFontHeightInPoints((short) 16);
        
        CellStyle bold16 = workbook.createCellStyle();
        bold16.setFont(boldFont16);
        
        Row titleRow = sheet.createRow(0);
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellStyle(bold16);
        titleCell.setCellValue("<" + title + "> 심사결과"); // 첫번째 셀에 텍스트 입력
        
        Row header = sheet.createRow(2);
        for (int j=0; j<aStr.size(); j++) {
            Cell cell = header.createCell(j);
            cell.setCellStyle(cs);
            cell.setCellType(Cell.CELL_TYPE_STRING);  
            cell.setCellValue(aStr.get(j));
        }
        
        for (int i=0; i<data.size(); i++) {
            if (isVO) {
                C11ReceiptVO vo = (C11ReceiptVO)data.get(i);
                
                for (int k=0; k<aStr.size(); k++) {
                    Row courseRow = sheet.createRow(i + 3);
                    courseRow.createCell(0).setCellValue(i + 1);
                    courseRow.createCell(1).setCellValue(vo.getIdx());
                    courseRow.createCell(2).setCellValue(vo.getTarget());
                    courseRow.createCell(3).setCellValue(vo.getGroup());
                    courseRow.createCell(4).setCellValue(vo.getValuer1());
                    courseRow.createCell(5).setCellValue(vo.getValuer2());
                    courseRow.createCell(6).setCellValue(vo.getValuer3());
                    courseRow.createCell(7).setCellValue(vo.getValuer4());
                    courseRow.createCell(8).setCellValue(vo.getValuer5());
                    courseRow.createCell(9).setCellValue(vo.getValuer6());
                    courseRow.createCell(10).setCellValue(vo.getTotal());
                    courseRow.createCell(11).setCellValue(vo.getAvg());
                }
            }
        }
    }
}

＊ HSSFWork 객체는 xls, XSSFWork 객체는 xlsx 확장명으로 가능하고 둘다 가능하게 끔 하려면 Workfactory가 필요하다.

[Controller]

@RequestMapping(value="/evaluation", method = {RequestMethod.GET}, produces = "application/json; charset=UTF-8")
    public ModelAndView evaluation() {
        Map<String, Object> param = new HashMap<String, Object>();

        param.put("productType", "0");
        for(int i = 1; i <= 6; i++) {
            param.put("valuer"+i, "test"+i);
        }
        List<C11ReceiptVO> product = loginService.getExcelBuild(param);
        System.out.println(">>>>>>>>>>"+product);
        param.put("productType", "1");
        for(int i = 7; i <= 12; i++) {
            param.put("valuer" + (i - 6), "test"+i);
        }
        List<C11ReceiptVO> system = loginService.getExcelBuild(param);

        param.put("productType", "2");
        for(int i = 13; i <= 18; i++) {
            param.put("valuer" + (i - 12), "test"+i);
        }
        List<C11ReceiptVO> poster = loginService.getExcelBuild(param);

        param.put("productType", "3");
        for(int i = 19; i <= 24; i++) {
            param.put("valuer" + (i - 18), "test"+i);
        }
        List<C11ReceiptVO> cali = loginService.getExcelBuild(param);

        param.put("productType", "4");
        for(int i = 25; i <= 30; i++) {
            param.put("valuer" + (i - 24), "test"+i);
        }
        List<C11ReceiptVO> essay = loginService.getExcelBuild(param);
        
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("product", product);
        map.put("system", system);
        map.put("poster", poster);
        map.put("cali", cali);
        map.put("essay", essay);
        return new ModelAndView(new C11EvaluationExcelView(), "excelMap", map);
    }