Egov Excel download

전자정부 프레임워크에서 엑셀 다운로드하는 방법

 

 

:: pom.xml

egovframework.rte.fdl.excel 라이브러리 추가

<dependency>
  <groupId>egovframework.rte</groupId>
  <artifactId>egovframework.rte.fdl.excel</artifactId>
  <version>${egovframework.rte.version}</version>
</dependency>

 

 

:: dispatcher-servlet.xml

viewResolver - MVC에서 컨트롤러가 처리한 결과를 어떤 View로 렌더링할지 결정하는 역할

<!-- ViewResolver 설정 -->
<bean id="beanNameViewResolver" class="org.springframework.web.servlet.view.BeanNameViewResolver" p:order="1"/>
	
<!-- UrlBasedViewResolver 설정 -->
<bean class="org.springframework.web.servlet.view.UrlBasedViewResolver" p:order="2"
	p:viewClass="org.springframework.web.servlet.view.JstlView"
	p:prefix="/WEB-INF/jsp/" p:suffix=".jsp"/>

 

 

:: context-common.xml

ExcelDownloadView - 해당 클래스를 bean id로 지정하여 뷰로 사용

...

<!-- excel bean -->
<bean id="ExcelDownloadView" class="egovframework.gnus.cmmn.util.ExcelDownloadView"></bean>

 

 

:: ExcelDownloadView.java

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

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

이부분이 엑셀 데이터에 번호를 의미한다 1부터 번호가 찍히므로 상황에 맞게 수정하면 된다.

package egovframework.gnus.cmmn.util;

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.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.servlet.view.AbstractView;

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

public class ExcelDownloadView extends AbstractView {
    private static final Logger LOGGER  = LoggerFactory.getLogger(ExcelDownloadView.class);

    /** The content type for an Excel response */
     private static final String CONTENT_TYPE_XLSX = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

    @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();
        LOGGER.debug("Created Excel Workbook from scratch");

        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 model, XSSFWorkbook wb, HttpServletRequest req, HttpServletResponse resp) throws Exception {
        Map<String, Object> dataMap = (Map<String, Object>) model.get("dataMap");
        XSSFCell cell = null;
 
        String sheetNm = (String) dataMap.get("sheetNm"); // 엑셀 시트 이름
        
        String[] columnArr = (String[]) dataMap.get("columnArr"); // 각 컬럼 이름
        String[] columnVarArr = (String[]) dataMap.get("columnVarArr"); // 각 컬럼의 변수 이름
        
        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<columnArr.length; i++){
            setText(getCell(sheet, 0, i), columnArr[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<columnVarArr.length; j++){
                    String data = (String) dataEgovMap.get(columnVarArr[j]);
                    cell = getCell(sheet, 1 + i, j);
                    setText(cell, data);
                    cell.setCellStyle(cellStyle2);
                }
            }
        }else{ // 저장된 데이터가 없으면 셀 병합
            // 셀 병합(시작열, 종료열, 시작행, 종료행)
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, columnArr.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 doesnt 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);
    }
    
}

 

 

:: BoardController.java

   list - excel에 표시할 데이터 리스트

   filename - 파일명

   columnArr - 엑셀 첫번째 줄 컬럼명

   columnVarrArr - list 데이터 키

   * columnArr와 columnVarrArr 순서는 일치시켜야 한다

@RequestMapping(value={"/excel.do"})
public ModelAndView excel(HttpServletRequest request, HttpServletResponse response, HttpSession session) throws Exception{
    // 빈에 등록된 아이디 값
    ModelAndView mav = new ModelAndView("ExcelDownloadView");
    Map<String, Object> dataMap = new HashMap<String, Object>();

    // excel에 표시할 데이터 리스트 호출하기
    // List<?> list = boardSvc.getBoardAllList(paramVO);
    
    // String filename = "게시물 목록_"+DateTime.getDateTimeMinSec();
    String filename = String.format("%s-%s", "게시물 목록_", LocalDate.now().toString());
    String[] columnArr = {"번호", "제목", "내용", "작성일"};
    String[] columnVarArr = {"idx", "title", "author", "regDate"};
            
    dataMap.put("columnArr", columnArr);
    dataMap.put("columnVarArr", columnVarArr);
    dataMap.put("sheetNm", "게시물 목록");    
    dataMap.put("list", list);
    
    mav.addObject("dataMap", dataMap);
    mav.addObject("filename", filename);
    
    return mav;
}

 

 

:: 전체 엑셀 클릭시 /excel.do 호출

 

 

:: excel 결과

 

 

출처 - https://eastglow.github.io/back-end/2019/03/07/Spring-Apache-POI를-이용한-Excel-Download.html