엑셀 다운로드(전자정부프레임워크 + Tabulator 사용)

1. context-common.xml (bean 추가)

<bean id="excelView" class="egovframework.cmn.cmn.ExcelView" />

2. ExcelView.java (공통 엑셀다운로드)

package egovframework.cmn.cmn;

import java.net.URLEncoder;

import java.util.List;

import java.util.Map;

import javax.servlet.ServletOutputStream;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.hssf.usermodel.HSSFFont;

import org.apache.poi.hssf.util.HSSFColor;

import org.apache.poi.ss.usermodel.CellStyle;

import org.apache.poi.ss.usermodel.Font;

import org.apache.poi.ss.util.CellRangeAddress;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.springframework.web.servlet.view.AbstractView;

import egovframework.rte.psl.dataaccess.util.EgovMap;

public class ExcelView extends AbstractView {

    /** The content type for an Excel response */

     private static final String CONTENT_TYPE_XLSX = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

    /**

     * Default Constructor. Sets the content type of the view for excel files.

     */

    public ExcelView() {

    }

    @Override

    protected boolean generatesDownloadContent() {

        return true;

    }

    /**

     * Renders the Excel view, given the specified model.

     */

    @Override

    protected final void renderMergedOutputModel(Map<String, Object> model, HttpServletRequest request, HttpServletResponse response) throws Exception {

        XSSFWorkbook workbook = new XSSFWorkbook();

        setContentType(CONTENT_TYPE_XLSX);

        buildExcelDocument(model, workbook, request, response);

        

        // Set the filename

        String sFilename = "";

        if(model.get("filename") != null){

            sFilename = (String)model.get("filename");

        }else if(request.getAttribute("filename") != null){

            sFilename = (String)request.getAttribute("filename");

        }else{

            sFilename = getClass().getSimpleName();

         }

        response.setContentType(getContentType());

        

        String header = request.getHeader("User-Agent");

        sFilename = sFilename.replaceAll("\r","").replaceAll("\n","");

        if(header.contains("MSIE") || header.contains("Trident") || header.contains("Chrome")){

            sFilename = URLEncoder.encode(sFilename,"UTF-8").replaceAll("\\+","%20");

            response.setHeader("Content-Disposition","attachment;filename="+sFilename+".xlsx;");

        }else{

            sFilename = new String(sFilename.getBytes("UTF-8"),"ISO-8859-1");

            response.setHeader("Content-Disposition","attachment;filename=\""+sFilename + ".xlsx\"");

        }

        

        // Flush byte array to servlet output stream.

        ServletOutputStream out = response.getOutputStream();

        out.flush();

        workbook.write(out);

        out.flush();

        // Don't close the stream - we didn't open it, so let the container handle it.

        // http://stackoverflow.com/questions/1829784/should-i-close-the-servlet-outputstream

    }

    

    @SuppressWarnings("unchecked")

    protected void buildExcelDocument(Map<String, Object> model, XSSFWorkbook wb, HttpServletRequest req, HttpServletResponse resp) throws Exception {

    List<Map<String, Object>> dataMapList = (List<Map<String, Object>>) model.get("dataMapList");

   

    for(Map<String, Object> dataMap : dataMapList) {

            XSSFCell cell = null;

     

            String sheetNm = (String) dataMap.get("sheetNm"); // 엑셀 시트 이름

            

            String[] titleArr = (String[]) dataMap.get("titleArr"); // 각 컬럼 이름

            String[] fieldArr = (String[]) dataMap.get("fieldArr"); // 각 컬럼의 변수 이름

            

            List<EgovMap> dataList = (List<EgovMap>) dataMap.get("list"); // 데이터가 담긴 리스트 

            

            CellStyle cellStyle = wb.createCellStyle(); // 제목셀의 셀스타일

            cellStyle.setWrapText(true); // 줄 바꿈            

            cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); // 셀 색상

            cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 셀 색상 패턴

            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 셀 가로 정렬

            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 셀 세로 정렬

            cellStyle.setDataFormat((short)0x31); // 셀 데이터 형식

            cellStyle.setBorderRight(HSSFCellStyle.BORDER_DOUBLE);

            cellStyle.setBorderLeft(HSSFCellStyle.BORDER_DOUBLE);

            cellStyle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE);

            cellStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);

            

            // 셀 폰트색상, bold처리

            Font font = wb.createFont();

            font.setColor(HSSFColor.WHITE.index);

            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

            cellStyle.setFont(font);

            

            CellStyle cellStyle2 = wb.createCellStyle(); // 데이터셀의 셀스타일

            cellStyle2.setWrapText(true); // 줄 바꿈           

            cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 셀 가로 정렬

            cellStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 셀 세로 정렬

            cellStyle2.setDataFormat((short)0x31); // 셀 데이터 형식

            cellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);

            cellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);

            cellStyle2.setBorderTop(HSSFCellStyle.BORDER_THIN);

            cellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN);

            

            XSSFSheet sheet = wb.createSheet(sheetNm);

            sheet.setDefaultColumnWidth(12);

            

            // 컬럼명 삽입

            for(int i=0; i<titleArr.length; i++){

                setText(getCell(sheet, 0, i), titleArr[i]);

                getCell(sheet, 0, i).setCellStyle(cellStyle);

                sheet.autoSizeColumn(i);

                int columnWidth = (sheet.getColumnWidth(i))*5;

                sheet.setColumnWidth(i, columnWidth);

                

                if(dataList.size() < 1){

                    cell = getCell(sheet, 1, i);

                    if(i==0){

                        setText(cell, "등록된 정보가 없습니다.");

                    }

                    cell.setCellStyle(cellStyle2);

                }

            }

            

            if(dataList.size() > 0){ // 저장된 데이터가 있을때

                // 리스트 데이터 삽입

                for (int i = 0; i<dataList.size(); i++) {

                    EgovMap dataEgovMap = dataList.get(i);

                    

                    // 맨 앞 컬럼인 "번호"는 idx라는 이름으로 여기서 생성하여 넣어준다.

                    dataEgovMap.put("idx", (i+1)+""); 

                    for(int j=0; j<fieldArr.length; j++){

                        String data = String.valueOf(dataEgovMap.get(fieldArr[j]));

                        cell = getCell(sheet, 1 + i, j);

                        setText(cell, data);

                        cell.setCellStyle(cellStyle2);

                    }

                }

            }else{ // 저장된 데이터가 없으면 셀 병합

                // 셀 병합(시작열, 종료열, 시작행, 종료행)

                sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, titleArr.length-1));

            }

    }

    }

    /**

     * Convenient method to obtain the cell in the given sheet, row and column.

     * 

     * <p>Creates the row and the cell if they still doesn't already exist.

     * Thus, the column can be passed as an int, the method making the needed downcasts.</p>

     * 

     * @param sheet a sheet object. The first sheet is usually obtained by workbook.getSheetAt(0)

     * @param row thr row number

     * @param col the column number

     * @return the XSSFCell

     */

    protected XSSFCell getCell(XSSFSheet sheet, int row, int col) {

        XSSFRow sheetRow = sheet.getRow(row);

        if (sheetRow == null) {

            sheetRow = sheet.createRow(row);

        }

        XSSFCell cell = sheetRow.getCell((short) col);

        if (cell == null) {

            cell = sheetRow.createCell((short) col);

        }

        return cell;

    }

    /**

     * Convenient method to set a String as text content in a cell.

     * 

     * @param cell the cell in which the text must be put

     * @param text the text to put in the cell

     */

    protected void setText(XSSFCell cell, String text) {

        cell.setCellType(XSSFCell.CELL_TYPE_STRING);

        cell.setCellValue(text);

    }            

}

3. SearchVO.java (VO 변수 추가)

private String schFileNm = ""; /** 엑셀다운로드 파일명 */

private String[] schSheetNmArr = null; /** 엑셀다운로드 시트명 배열 */

private String[][] schTitleArrArr = null; /** 엑셀다운로드 Title 배열 */

private String[][] schFieldArrArr = null; /** 엑셀다운로드 Field 배열 */

4. XxxController.java (Controller 엑셀다운로드 함수 추가)

@RequestMapping("disorderAllList.do")

public ModelAndView selectDisorderAllList(@ModelAttribute("searchVO") SearchVO searchVO, HttpServletRequest request, HttpServletResponse response, BindingResult bindingResult) throws Exception {

//코드관리 목록 조회

List<?> list = cfgService.selectDisorderList(searchVO);

//코드1관리 목록 조회

List<?> list1 = cfgService.selectCauseList(searchVO);

//코드2관리 목록 조회

List<?> list2 = cfgService.selectActionList(searchVO);

    ModelAndView mav = new ModelAndView("excelView");

    List<Map<String, Object>> dataMapList = new ArrayList<Map<String, Object>>();

    Map<String, Object> subMap = null;

    

    for(int i = 0 ; i < searchVO.getSchSheetNmArr().length ; i++) {

    subMap = new HashMap<String, Object>();

    subMap.put("titleArr", searchVO.getSchTitleArrArr()[i]);

    subMap.put("fieldArr", searchVO.getSchFieldArrArr()[i]);

    subMap.put("sheetNm", searchVO.getSchSheetNmArr()[i]);

    if(i == 0) subMap.put("list", list);

    else if(i == 1) subMap.put("list", list1);

    else if(i == 2) subMap.put("list", list2);

   

    dataMapList.add(i, subMap);

    }

    mav.addObject("dataMapList", dataMapList);

    mav.addObject("filename", searchVO.getSchFileNm());

    

    return mav;

}

5. common.js (공통 Tabulator 엑셀 다운로드 함수 추가)

//Tabulator 엑셀 다운로드

function downBaseExcelTabulator(vAction, vFileNm, vSheetNmArr, vSheetArr, vParams){

//action 정보 세팅

var vForm = document.createElement("FORM");

vForm.action = vAction;

vForm.method = "POST";

//파일명 세팅

appendMakeInput(vForm, "schFileNm", vFileNm);

//시트명 세팅

for(var i = 0 ; i < vSheetNmArr.length ; i++){

appendMakeInput(vForm, "schSheetNmArr[" + i + "]", vSheetNmArr[i]);

}

//sheet 컬럼 정보 세팅

for(var j = 0 ; j < vSheetArr.length ; j++){

var vColumns = vSheetArr[j];

for(var jj = 0 ; jj < vColumns.length ; jj++){

if(vColumns[jj].download != false){

appendMakeInput(vForm, "schTitleArrArr[" + j + "]", vColumns[jj].title);

appendMakeInput(vForm, "schFieldArrArr[" + j + "]", vColumns[jj].field);

}

}

}

//params 정보 세팅

if(vParams != null && vParams != ""){

for(var k = 0 ; k < vParams.length ; k++){

appendMakeInput(vForm, vParams[k].name, vParams[k].value);

}

}

window.document.body.appendChild(vForm);

vForm.submit();

}

//Tabulator 단일 시트 엑셀 다운로드

function downOneExcelTabulator(vAction, vFileNm, vSheetNm, vSheet, vParams){

var vSheetNmArr = [vSheetNm];

var vSheetArr = [vSheet.getColumnDefinitions()];

downBaseExcelTabulator(vAction, vFileNm, vSheetNmArr, vSheetArr, vParams);

}

//Tabulator 멀티 시트 엑셀 다운로드

function downMultiExcelTabulator(vAction, vFileNm, vSheetNmArr, vSheetArr, vParams){

var reSheetArr = new Array();

for(var i = 0 ; i < vSheetArr.length ; i++){

var tempArr = new Array();

for(var j = 0 ; j < vSheetArr[i].getColumns().length ; j++){

tempArr[j] = vSheetArr[i].getColumns()[j].getDefinition();

}

reSheetArr[i] = tempArr;

}

downBaseExcelTabulator(vAction, vFileNm, vSheetNmArr, reSheetArr, vParams);

}

//input hidden 객체 만들어 붙이기

function appendMakeInput(obj, nm, val){

var vInput = document.createElement("INPUT");

vInput.name = nm;

vInput.value = val;

vInput.type = "hidden";

obj.append(vInput);

}

6. Xxx.js (각 화면 엑셀 다운로드 함수 추가)

//엑셀 다운로드

function excelDown(opt){

if(opt == "ALL"){

var vAction = "/common/disorderAllList.do";

var vFileNm = "코드관리(전체)";

var vSheetNmArr = ["코드관리", "코드1관리", "코드2관리"];

var vSheetArr = [disorderSheet, causeSheet, actionSheet];

var vParams = null;

//Tabulator 엑셀 다운로드

downMultiExcelTabulator(vAction, vFileNm, vSheetNmArr, vSheetArr, vParams);

}

else{

var sheets = {

    "코드관리": true,

    "코드1관리": causeSheet,

    "코드2관리": actionSheet

};

disorderSheet.download("xlsx", "코드관리.xlsx", {sheets:sheets});

}

}