[SPRING]APACHE POI를 활용한 EXCEL 다운로드

APACHE POI를 활용한 엑셀 다운로드 기능을 구현

 

1 . POM.xml 에 POI 관련 dependency 등록

 

 

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.13</version>
</dependency>

 

- 만약 행이 엄청 많아서 xlsx로 다운받고 싶을때는

 

<!--xlsx로 받고싶을때-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.13</version>
</dependency>

 

2 . 컨트롤러에서 excelView 로 리턴시키면 ExcelView.java로 가게하는 설정

dispatcher-servlet.xml

 

<bean class = "org.springframework.web.servlet.view.BeanNameViewResolver">
    <property name = "order" value = "1" />
</bean>
<!--컨트롤러 리턴시 다른것들보다 먼저 읽혀야 하기 때문에 order를 0과 1로 지정-->
<bean class = "org.springframework.web.servlet.handler.SimpleMappingExceptionResolver">
    <property name = "order" value = "0" />
    <property name = "defaultErrorView" value = "error" />
    <property name = "exceptionMappings">
        <props>
            <props key = "RuntimeException">error</prop>
        </props>
    </property>
</bean>

<bean name = "excelView" class = "net.yg.cmmn.excel.ExcelView" />

 

3 . ExcelView가 상속받을 AbstractExcelView.java 작성

 

public class AbstractExcelView extends AbstractView {
    
    private static final String CONTENT_TYPE_XLSX = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

    public AbstractExcelView(){}

    @Override
    protected boolean generatesDownloadContent(){
        return true;
    }

    @Override
    protected final void renderMergedOutputModel(Map<String,Object> model , HttpServletRequest request , HttpServletResponse response) throws Exception{
        
        Workbook workbook = createWorkbook();

        setContentType("CONTENT_TYPE_XLSX");

        buildExcelDocument(model , workbook , request , response);

        response.setContentType(getContentType());

        ServletOutputStream out = response.getOutputStream();
        out.flush();
        workbook.write(out);
        out.flush();
        
        if(workbook instanceof SXSSFWorkbook){
            ((SXSSFWorkbook) workbook).dispose();
        }
    }
    
    protected abstract Workbook createWorkbook();

    protected abstract void buildExcelDocument(Map<String,Object> model, Workbook workbook , HttpServletRequest request , HttpServletResponse response) throws Exception;
}

 

4 . ExcelView.java 작성

 

    public class ExcelView extends AbstractExcelView{
        @Override
        protected void buildExcelDocument(Map<String,Object> model , Workbook workbook , HttpServletRequest request , HttpServletResponse response) throws Exception{
            
            String type = model.get("excelType").toString();
            
                if(type.equals("board")){
                
                List<BoardVO> excelList = (List<BoardVO>)model.get("excelList");
                Sheet sheet = workbook.createSheet(type);
                Row row = null;
                int rowCount = 0;
                int cellCount = 0;
                
                row = sheet.createRow(rowCount++);

                row.createCell(cellCount++).setCellValue("번호");
                row.createCell(cellCount++).setCellValue("제목");
                row.createCell(cellCount++).setCellValue("작성자");
                row.createCell(cellCount++).setCellValue("작성일");
                row.createCell(cellCount++).setCellValue("조회수");

                for(BoardVO vo : excelList){
                    row = sheet.createRow(rowCount++);
                    cellCount = 0;
                    row.createCell(cellCount++).setCellValue(vo.getBdSq());
                    row.createCell(cellCount++).setCellValue(vo.getBdTitl());
                    row.createCell(cellCount++).setCellValue(vo.getBdWrtr());
                    row.createCell(cellCount++).setCellValue(vo.getRgDt());
                    row.createCell(cellCount++).setCellValue(vo.getBdCnt());
                }
            }
        }
        @Override
        protected Workbook createWorkbook(){
            return new XSSFWorkbook();
        }
    }

 

5 . ExcelController.java 작성

 

    public class ExcelController{
        @Resource(name = "excelService")
        private ExcelService excelService;

        @RequestMapping(value = "/getBoardByExcel")
        public String getBoardList(BoardVO vo , Map<String,Object> modelMap , HttpServletResponse response) throws Exception{

            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Pragma","public");
            response.setHeader("Expires","0");
            response.setHeader("Content-Disposition","attachment; filename = test.xls");

            List<Object> excelList = excelService.getBoardList(vo);

            modelMap.put("excelList",excelList);
            modelMap.put("excelType",board);

            return "excelView";
        }
    }