Apache POI 3.15를 사용한 엑셀 다운로드 기능

[2017-04-12 수요일]

* 내용: Apache POI 사용

1. 활용준비

1) 라이브러리 다운로드

2) import

2. 적용 방법

1) VO 생성

2) AbstractView를 활용하여 ExcelBuilder Class 생성

3) Bean 등록(beanNameViewResolver 활용)

4) Controller 설정

5) jsp(뷰)

 

1. 실행 방법

---

1) 라이브러리 다운로드

※ 이 프로젝트는 메이븐 프로젝트가 아니기 때문에 직접 다운로드하고 직접 import하는 방식을 사용하였음

※ 참고 사이트

- 라이브러리 import 관련: http://jsonobject.tistory.com/127

- Excel Write 관련: http://javaslave.tistory.com/79

- XSSFSheet 관련: http://blog.whitelife.co.kr/199

- SXSSFSheet 관련: http://stackoverflow.com/questions/2498536/poi-performance

 

* 다운로드

- url: http://poi.apache.org/download.html#POI-3.15

2) import

- 압축 풀고 아래 jar 파일 import

 

A. Properites

B. Add External JARs

 

C. open POI JARs

D. 확인

 

 

2. 적용 방법

---

 

1) VO 생성

 

* pojo 형태의 vo 생성

public class TestVO extends AbstractVO {
    private String COMPANY_CODE;
    private String COMPANY_NAME;
    private String DEPT_CODE;
    private String DEPT_NAME;
    private String USER_ID;
    private String USER_NAME;
    public String getCOMPANY_CODE() {
        return COMPANY_CODE;
    }
    public void setCOMPANY_CODE(String cOMPANY_CODE) {
        COMPANY_CODE = cOMPANY_CODE;
    }
    public String getCOMPANY_NAME() {
        return COMPANY_NAME;
    }
    public void setCOMPANY_NAME(String cOMPANY_NAME) {
        COMPANY_NAME = cOMPANY_NAME;
    }
    public String getDEPT_CODE() {
        return DEPT_CODE;
    }
    public void setDEPT_CODE(String dEPT_CODE) {
        DEPT_CODE = dEPT_CODE;
    }
    public String getDEPT_NAME() {
        return DEPT_NAME;
    }
    public void setDEPT_NAME(String dEPT_NAME) {
        DEPT_NAME = dEPT_NAME;
    }
    public String getUSER_ID() {
        return USER_ID;
    }
    public void setUSER_ID(String uSER_ID) {
        USER_ID = uSER_ID;
    }
    public String getUSER_NAME() {
        return USER_NAME;
    }
    public void setUSER_NAME(String uSER_NAME) {
        USER_NAME = uSER_NAME;
    }
}

 

2) AbstractView를 활용하여 ExcelBuilder Class 생성

public class OverallExcelBuilder extends AbstractView {
    private static final String CONTENT_TYPE = "application/vnd.ms-excel"; // Content Type 설정
   
    Logger log = LoggerFactory.getLogger(OverallExcelBuilder.class);
    public OverallExcelBuilder() {
        setContentType(CONTENT_TYPE);
    }
   
    @Override
    protected void renderMergedOutputModel(Map<String, Object> model, HttpServletRequest request, HttpServletResponse response) throws Exception {
        try {
           
            // 파일 이름 설정
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
            Calendar c1 = Calendar.getInstance();
            String yyyymmdd = sdf.format(c1.getTime());
            String fileName = yyyymmdd + "_테스트 엑셀.xlsx";
            fileName = URLEncoder.encode(fileName,"UTF-8"); // UTF-8로 인코딩
           
            // 다운로드 되는 파일명 설정
            response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
           
            // SXSSFWorkbook 생성
            SXSSFWorkbook workbook = new SXSSFWorkbook();
            workbook.setCompressTempFiles(true);
 
            // SXSSFSheet 생성
            SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet();
            sheet.setRandomAccessWindowSize(100); // 메모리 행 100개로 제한, 초과 시 Disk로 flush
           
            // 엑셀에 출력할 List
            List<TestVO> resultList = (List<TestVO>) model.get("resultList");
           
            // Cell 스타일 값
            sheet.setDefaultColumnWidth(30);
            CellStyle style = workbook.createCellStyle();
            Font font = workbook.createFont();
            font.setFontName("Arial");
            style.setFillForegroundColor(HSSFColor.BLUE.index);
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            font.setColor(HSSFColor.WHITE.index);
            style.setFont(font);
            
            // header 생성
            SXSSFRow header = (SXSSFRow) sheet.createRow(0);
            setHeaderCellValue(header); // 헤더 칼럼명 설정
            setHeaderStyle(header, style); // 헤더 스타일 설정
            
            // 행 데이터 생성
            int rowCount = 1;
            if(resultList != null) {
        for (TestVO testVO : resultList) {
            SXSSFRow aRow = (SXSSFRow) sheet.createRow(rowCount++);
            setEachRow(aRow, testVO);
            log.debug(String.valueOf(rowCount));
        }
        }
            ServletOutputStream out = response.getOutputStream();
            workbook.write(out);
            if (out != null) out.close();
           
        } catch (Exception e) {
            throw e;
        }
    }
   
    private void setHeaderCellValue(SXSSFRow header) {
        header.createCell(0).setCellValue("회사코드");
        header.createCell(1).setCellValue("회사");
        header.createCell(2).setCellValue("부서코드");
        header.createCell(3).setCellValue("부서명");
        header.createCell(4).setCellValue("사번");
        header.createCell(5).setCellValue("사원명");
    }
   
    private void setHeaderStyle(SXSSFRow header, CellStyle style) {
        header.getCell(0).setCellStyle(style);
        header.getCell(1).setCellStyle(style);
        header.getCell(2).setCellStyle(style);
        header.getCell(3).setCellStyle(style);
        header.getCell(4).setCellStyle(style);
        header.getCell(5).setCellStyle(style);
    }
   
    private void setEachRow(SXSSFRow aRow, TestVO testVO) {
        aRow.createCell(0).setCellValue(testVO.getCOMPANY_CODE());
        aRow.createCell(1).setCellValue(testVO.getCOMPANY_NAME());
        aRow.createCell(2).setCellValue(testVO.getDEPT_CODE());
        aRow.createCell(3).setCellValue(testVO.getDEPT_NAME());
        aRow.createCell(4).setCellValue(testVO.getUSER_ID());
        aRow.createCell(5).setCellValue(testVO.getUSER_NAME());
    }
}
 

3) Bean 등록(beanNameViewResolver 활용)

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:mvc="http://www.springframework.org/schema/mvc"
    xmlns:aop="http://www.springframework.org/schema/aop"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xsi:schemaLocation="http://www.springframework.org/schema/mvc
                    http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd
                    http://www.springframework.org/schema/beans
                    http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
                    http://www.springframework.org/schema/context
                    http://www.springframework.org/schema/context/spring-context-3.2.xsd
                    http://www.springframework.org/schema/tx
                   http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
                   http://www.springframework.org/schema/aop
                   http://www.springframework.org/schema/aop/spring-aop-3.2.xsd">
 
    <!-- enable autowire -->
    <context:annotation-config />
   
    <!-- enable transaction demarcation with annotations -->
    <tx:annotation-driven />
    <mvc:annotation-driven />
   
    <!-- Controller Bean -->
    <context:component-scan base-package="com.*.*.*.*.controller"/>
    <!--// Controller Bean -->
    <!-- Service Bean -->
    <context:component-scan base-package="com.*.*.*.*.service" />
    <!-- // Service Bean -->
   
    <!-- ViewResolver 설정 -->
    <bean id="beanNameViewResolver" class="org.springframework.web.servlet.view.BeanNameViewResolver" />
    <!-- Example: a logical view name of 'showMessage' is mapped to '/WEB-INF/jsp/showMessage.jsp' -->
 
   <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
        <property name="cache" value="false"/>
        <property name="prefix" value="/WEB-INF/view/" />
        <property name="suffix" value=".jsp" />
    </bean>
   
    <!--  Excel -->
    <bean name="excelView" class="com.~~~.~~~.excel.view.OverallExcelBuilder" />
</beans>

 

4) Controller 설정

/**
 * 엑셀 다운로드
 */
@ResponseBody
@RequestMapping(value = "/doDownloadExcel.do", method=RequestMethod.GET)
public ModelAndView downloadExcel(HttpServletRequest request, HttpServletResponse response) {
    Gson gson = new GsonBuilder().setPrettyPrinting().create();   
    Map<String, Object> searchMap = new HashMap<String, Object>();
    List<ListFirstVO> resultList = null;
   
    // searchMap.put("test", request.getParameter("test")); // 검색 조건
       
    try {           
       
        resultList = listFirstService.selectList(searchMap);           
       
    } catch (Exception e) {
        log.debug(e);
        e.printStackTrace();
    }
 
    ModelAndView modelAndView = new ModelAndView("excelView", "resultList", resultList);
    return modelAndView;
}

 

5) jsp

 

* 버튼

<a>
    <button type="button" class="btn btn-primary btn-sm" id="btn_excel_down">엑셀 다운로드</button>
</a>
<h3><a id="anchorExcelDown" href="/blah/blah/blah/doGetExcelFile.do" hidden>Download Excel Document</a></h3>

 

* 클릭 이벤트

$("#btn_excel_down").click(function() {
    fn_excel_down();
});

 

* 함수: 강제적으로 anchor tag 클릭 이벤트 발생하도록 한다

function fn_excel_down() {
    // 컨트롤러에 값 전달
    var json_params = getSearchCondition();
   
    console.log(encodeData("/blah/blah/blah/doDownloadExcel.do" + "?" + json_params));
   
    $("#anchorExcelDown").prop("href", "/blah/blah/blah/doDownloadExcel.do" + "?" + encodeData(json_params));
    $("#anchorExcelDown")[0].click(); // Click Event 발생시킨다
}
 
// GET 방식의 파라미터로 생성
function encodeData(data) {
    return Object.keys(data).map(function(key) {
        return [key, data[key]].map(encodeURIComponent).join("=");
    }).join("&");
}