subinto의 개발노트

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

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);

    }            

}

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

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

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

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

@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;

}

//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);

}

//엑셀 다운로드

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});

}

}

org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (IO 오류: The Network Adapter could not establish the connection)

OracleOraDb11g_home1TNSListener 서비스가 로컬 컴퓨터에서 시작했다가 중지되었습니다

 /** SysadmService */
 @Resource(name="sysadmService")
 private SysadmService sysadmService;

 @Override
 public void execute(Request tilesRequest, AttributeContext attributeContext) throws PreparerException {
  List<?> menuList;
  try {
   Map<String, String> map = tilesRequest.getParam();
   SearchVO searchVO = new SearchVO();
   
   //로그인한 세션정보 가져오기
   HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
   HttpSession session = request.getSession();
   LoginVO loginVO = (LoginVO)session.getAttribute("loginVO");
   
   //접근사용자구분 세팅
   searchVO.setSchUserGubun(loginVO.getUserGubun());
   
   //관리자메뉴 세팅
   menuList = sysadmService.getSelectMngMenuList(searchVO);
   attributeContext.putAttribute("menuList", new Attribute(menuList), true);
  } catch (Exception e) {
   e.printStackTrace();
  }
 }

} 

<!DOCTYPE html>
<html lang="ko">
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge">

<!-- Stylesheets ============================================= -->
<link rel="stylesheet" href="/css/bootstrap.css" type="text/css" />
<link rel="stylesheet" href="/css/style.css" type="text/css" />

<!-- External JavaScripts ============================================= -->
<script type="text/javascript" src="/js/jquery.js"></script>

<tilesx:useAttribute name="curPath" />
<script type="text/javascript" src="/script/${curPath}.js"></script>

<meta name="viewport" content="width=device-width, initial-scale=1" />
<title>관리자페이지</title>
</head>

<body class="stretched">

<!-- Document Wrapper ============================================= -->
<div id="wrapper" class="clearfix">
 <!-- Header ============================================= -->
 <tiles:insertAttribute name="header"/>
 <!-- #header end -->

    <!-- Content ============================================= -->
    <section id="content" class="content-grid">
        <div class="content-wrap nopadding">
   <div class="container-fluid">
    <div id="tabs" class="card">
     <!-- 2depth 메뉴 -->
     <tiles:insertAttribute name="menuLvl2" />
     <!-- //2depth 메뉴 -->
     
     <div class="tab-content">
      <div class="tab-pane active" id="default">
       <div class="card-body">
        <!-- 3depth 메뉴 -->
        <tiles:insertAttribute name="menuLvl3" />
        <!-- //3depth 메뉴 -->
        
        <!-- bodyContent -->
        <tiles:insertAttribute name="body" />
        <!-- //bodyContent -->
       </div>
      </div>
     </div>
    </div>
   </div>
  </div>
    </section>
    <!-- #content end -->
</div>
<!-- #wrapper end -->
</body>
</html> 

<tiles:importAttribute name="menuList" />
<ul class="nav nav-tabs ui-sortable">
 <c:forEach var="result" items="${menuList}" varStatus="status">
  <c:if test="${result.lvl eq 2}">
   <li ${result.isActive eq 'Y' ? 'class="active"' : ''}><a href="${result.menuUrl }?menuId=${result.menuId}" ${result.popGubun eq 'Y' ? 'target="_blank"' : ''}>${result.menuNm }</a></li>
  </c:if>
 </c:forEach>
</ul>