[Spring] DB 엑셀 다운로드 기능 구현하기

DB 출력해서 엑셀로 다운로드하는 기능 구현하기

DB가 SELECT되는 내용을 추출해서 엑셀로 다운받을 수 있게 구현했다.

 

1. Front

HTML

<button class="btnQnaExcel" onclick="fn_ExcelDown()">
	엑셀다운
</button>

JS

function fn_ExcelDown() {
    var comSubmit = new ComSubmit();
    comSubmit.setUrl("<c:url value='/StdManageExcelDown.do' />");
    comSubmit.submit();
}

 

 

2. Back

728x90

Controller

/**
 * <pre>
 * 엑셀다운로드
 * </pre>
 * @param CommandMap commandMap
 * @throws Exception
 */
@RequestMapping(value="/StdManageExcelDown.do")
public void selectQnaManageExcelList(CommandMap commandMap, HttpServletResponse response, HttpServletRequest request) throws Exception{

    String searchKeyword = "";

    commandMap.getMap().put("SEARCH", searchKeyword);		

    List<Map<String,Object>> selectResistrationStdExcelList = resistrationStdManageService.selectResistrationStdExcelList(commandMap.getMap());
    // 엑셀파일 객체 생성
    WritableWorkbook workbook = null;
    // 시트 객체 생성
    WritableSheet sheet = null;
    // 셀 객체 생성
    Label label = null;

    //오늘날짜
    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");

    // 저장할 파일 객체 생성
    File file = new File(request.getSession().getServletContext().getRealPath("/") + "file/" + "엑셀파일명(" + format.format(new Date())+").xls");
    try{
        // 파일 생성
        workbook = Workbook.createWorkbook(file);
        // 시트 생성
        workbook.createSheet("sheet1", 0);
        sheet = workbook.getSheet(0);
        // 셀에 쓰기
        label = new Label(0, 0, "NO");
        sheet.addCell(label);
        label = new Label(1, 0, "컬럼명1");
        sheet.addCell(label);
        label = new Label(2, 0, "컬럼명2");
        sheet.addCell(label);
        label = new Label(3, 0, "컬럼명3");
        sheet.addCell(label);

        // 데이터 삽입
        for(int i=0; i < selectResistrationStdExcelList.size(); i++){
            Map<String,Object> rs = (Map<String,Object>)selectResistrationStdExcelList.get(i) ;
  
  			label = new Label(0, (i+1), String.valueOf(selectResistrationStdExcelList.size()-i));
            sheet.addCell(label);
            label = new Label(1, (i+1), CommonUtils.NVL(String.valueOf(rs.get("컬럼명1"))));
            sheet.addCell(label);
            label = new Label(2, (i+1), CommonUtils.NVL(String.valueOf(rs.get("컬럼명2"))));
            sheet.addCell(label);
            label = new Label(3, (i+1), CommonUtils.NVL(String.valueOf(rs.get("컬럼명3"))));
            sheet.addCell(label);

        }
        workbook.write();
        workbook.close();

    }catch(Exception e){
        e.printStackTrace();
    }

    byte fileByte[] = FileUtils.readFileToByteArray(new File(request.getSession().getServletContext().getRealPath("/") +"file/" + "수강생목록(" + format.format(new Date())+").xls"));

    response.setContentType("application/octet-stream");
    response.setContentLength(fileByte.length);
    response.setHeader("Content-Disposition", "attachment; fileName=\"" + URLEncoder.encode("엑셀파일명(" + format.format(new Date())+").xls","UTF-8")+"\";");
    response.setHeader("Content-Transfer-Encoding", "binary");
    response.getOutputStream().write(fileByte);
    response.getOutputStream().flush();
    response.getOutputStream().close();
}

 

DAO

@SuppressWarnings("unchecked")
public List<Map<String, Object>> selectResistrationStdExcelList(Map<String, Object> map) throws Exception{
    return (List<Map<String, Object>>)selectList("resistrationStdManage.selectResistrationStdExcelList", map);
}

 

Service

/**
 * <pre>
 * 수강생목록 엑셀 다운로드
 * </pre>
 * @param Map<String, Object> map
 * @return List
 * @throws Exception
 */
List<Map<String, Object>> selectResistrationStdExcelList(Map<String, Object> map) throws Exception;

 

ServiceImpl

@Override
public List<Map<String, Object>> selectResistrationStdExcelList(Map<String, Object> map) throws Exception {
    return resistrationStdManageDAO.selectResistrationStdExcelList(map);
}

 

Mapper.xml

<!-- 수강생리스트 엑셀 다운로드-->
<select id="selectResistrationStdExcelList" parameterType="hashmap" resultType="hashmap"> 
        SELECT 컬럼명1, 컬럼명2, 컬럼명3 FROM 테이블명
</select>

* 쿼리문의 경우 출력할(다운로드 할 ) DB의 쿼리문을 작성하면 된다.