poi 를 이용한 엑셀파일 다운로드처리

<%@ page import="org.apache.poi.hssf.usermodel.*" %>
<%@ page import="org.apache.poi.hssf.*" %>
<%@ page import="org.apache.poi.hssf.util.HSSFColor" %>
<%@ page import="org.apache.poi.ss.util.*" %>
<%
  DBHandler dbhandle = new DBHandler();
  Util util = new Util();
  UtilParam param = new UtilParam(request);

  ArrayList> aryDB = new ArrayList>();
  Hashtable hsROW = new Hashtable();

  String sSql = "";
//////////////////////////////////////////////////////////////////////////////
  out.clear();
  out = pageContext.pushBody();
  response.reset();                   // 이문장이 없으면 excel 등의 파일에서 한글이 깨지는 문제 발생...

  request.setCharacterEncoding("utf-8");
  response.setContentType("application/vnd.ms-excel");

  String today = util.getCurrentDate("yyyyMMddHHmmss");
  String fileSubject = "대출현황_";
  fileSubject = new String(fileSubject.getBytes("KSC5601"), "8859_1");
  String file_name = fileSubject + today+".xls";
  response.setHeader("Content-Disposition", "attachment; filename="+file_name);

  OutputStream fileOut = null;

  // 넘어온 변수 처리..
  String orderBy = param.getParameter("orderBy","");
  String SeekKeyWord = param.getParameter("SeekKeyWord","");
  String Seek = param.getParameter("Seek","");
  // Link 한글 Encoding 처리....
  if(sREQMETHOD.equalsIgnoreCase("GET")){
    Seek = util.ChangeEncoding(Seek,sFROM_CHARSET,sTO_CHARSET);
  }
  String sCurYear = util.getCurrentDate("yyyy");
  String ln_year = param.getParameter("ln_year","");    if(ln_year.equals("")) ln_year = sCurYear;
  String ln_month = param.getParameter("ln_month","");
  String ln_quarter = param.getParameter("ln_quarter","");

  String biz_gubun = param.getParameter("biz_gubun","");

  try{
    // WorkBook 생성..
    HSSFWorkbook objWorkBook = new HSSFWorkbook();
    // WorkSheet 생성..
    HSSFSheet objSheet = objWorkBook.createSheet();
    // WorkSheet 이름..
    objWorkBook.setSheetName(0,"대출현황");
    // 행생성
    HSSFRow objRow = null;
    // 셀생성
    HSSFCell objCell = null;

    // 스타일 객체 생성
    HSSFCellStyle styleHd = objWorkBook.createCellStyle();    //  제목 스타일
    HSSFCellStyle styleCon = objWorkBook.createCellStyle();   //  내용 스타일
    HSSFCellStyle styleConLeft = objWorkBook.createCellStyle();   //  내용 스타일
    HSSFCellStyle styleConRight = objWorkBook.createCellStyle();   //  내용 스타일
    HSSFCellStyle styleConRightNum = objWorkBook.createCellStyle();   //  내용 스타일 : 숫자 타입

    HSSFCellStyle styleSumCon = objWorkBook.createCellStyle();   //  내용 합계 스타일
    HSSFCellStyle styleSumConLeft = objWorkBook.createCellStyle();   //  내용 합계 스타일
    HSSFCellStyle styleSumConRight = objWorkBook.createCellStyle();   //  내용 합계 스타일
    HSSFCellStyle styleSumConRightNum = objWorkBook.createCellStyle();   //  내용 스타일 : 숫자 타입

    // Data Format
    HSSFDataFormat df = objWorkBook.createDataFormat();

    //  제목 스타일
    // Font
    HSSFFont font = objWorkBook.createFont();
    font.setFontHeightInPoints((short)11);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    styleHd.setFont(font);
    // Cell Style : 테두리
    styleHd.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleHd.setTopBorderColor(HSSFColor.BLACK.index);
    styleHd.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleHd.setBottomBorderColor(HSSFColor.BLACK.index);
    styleHd.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleHd.setLeftBorderColor(HSSFColor.BLACK.index);
    styleHd.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleHd.setRightBorderColor(HSSFColor.BLACK.index);
    // Cell Style : 셀 내부 색
    styleHd.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // not BackgroundColor
    styleHd.setFillForegroundColor(HSSFColor.BLUE_GREY.index);
    // Cell Style : 정렬
    styleHd.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    styleHd.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

    //  내용 스타일
    // Cell Style : 테두리
    styleCon.setBorderTop(HSSFCellStyle.BORDER_THIN);
    styleCon.setTopBorderColor(HSSFColor.BLACK.index);
    styleCon.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    styleCon.setBottomBorderColor(HSSFColor.BLACK.index);
    styleCon.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    styleCon.setLeftBorderColor(HSSFColor.BLACK.index);
    styleCon.setBorderRight(HSSFCellStyle.BORDER_THIN);
    styleCon.setRightBorderColor(HSSFColor.BLACK.index);
    // Cell Style : 정렬
    styleCon.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    styleCon.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    // Cell Style : Text Wrap 여부..
    styleCon.setWrapText(true);

    // Style Copy & Add
    styleConLeft.cloneStyleFrom(styleCon);
    styleConLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT);

    styleConRight.cloneStyleFrom(styleCon);
    styleConRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    styleConRightNum.cloneStyleFrom(styleConRight);
    styleConRightNum.setDataFormat(df.getFormat("#,##0"));

    // 내용 Sum 스타일
    styleSumCon.cloneStyleFrom(styleCon);
    styleSumCon.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // not BackgroundColor
    styleSumCon.setFillForegroundColor(HSSFColor.AQUA.index);

    styleSumConLeft.cloneStyleFrom(styleSumCon);
    styleSumConLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT);

    styleSumConRight.cloneStyleFrom(styleSumCon);
    styleSumConRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    styleSumConRightNum.cloneStyleFrom(styleSumConRight);
    styleSumConRightNum.setDataFormat(df.getFormat("#,##0"));

    // 셀병합처리..
    objSheet.addMergedRegion(new CellRangeAddress(0,0,0,1));  // 구분      : int firstRow, int lastRow, int firstCol, int lastCol
    objSheet.addMergedRegion(new CellRangeAddress(0,0,3,4));  // 대출액
    objSheet.addMergedRegion(new CellRangeAddress(0,0,5,7));  // 수수료 산출내역
    objSheet.addMergedRegion(new CellRangeAddress(0,0,9,10));  // 청구금액

    objSheet.addMergedRegion(new CellRangeAddress(1,1,0,1));  // 구분
    objSheet.addMergedRegion(new CellRangeAddress(1,1,3,4));  // 대출액
    objSheet.addMergedRegion(new CellRangeAddress(1,1,5,6));  // 수수료 산출내역
    objSheet.addMergedRegion(new CellRangeAddress(1,1,9,10));  // 청구금액

    objSheet.addMergedRegion(new CellRangeAddress(4,4,1,2));  // 업체수
    objSheet.addMergedRegion(new CellRangeAddress(4,4,3,8));  // 공백

    int iCellCnt = 12;      // Cell 갯수...

    // 1행 : Sum 제목
    objRow = objSheet.createRow(0);
    objRow.setHeight((short)0x150);
    objCell = objRow.createCell(0);  objCell.setCellValue("구분");  objCell.setCellStyle(styleHd);
    objCell = objRow.createCell(1);  objCell.setCellStyle(styleHd);
    objCell = objRow.createCell(2);  objCell.setCellValue("업체수");  objCell.setCellStyle(styleHd);
    objCell = objRow.createCell(3);  objCell.setCellValue("대출액");  objCell.setCellStyle(styleHd);
    objCell = objRow.createCell(4);  objCell.setCellStyle(styleHd);
    objCell = objRow.createCell(5);  objCell.setCellValue("수수료산출내역");  objCell.setCellStyle(styleHd);
    objCell = objRow.createCell(6);  objCell.setCellStyle(styleHd);
    objCell = objRow.createCell(7);  objCell.setCellStyle(styleHd);
    objCell = objRow.createCell(8);  objCell.setCellValue("");  objCell.setCellStyle(styleHd);
    objCell = objRow.createCell(9);  objCell.setCellValue("청구금액");  objCell.setCellStyle(styleHd);
    objCell = objRow.createCell(10);  objCell.setCellStyle(styleHd);
    objCell = objRow.createCell(11);  objCell.setCellValue("");  objCell.setCellStyle(styleHd);

    // 4행 : 제목
    objRow = objSheet.createRow(3);
    objRow.setHeight((short)0x150);
    objCell = objRow.createCell(0);  objCell.setCellValue("번호");  objCell.setCellStyle(styleHd);
    objCell = objRow.createCell(1);  objCell.setCellValue("구분");  objCell.setCellStyle(styleHd);
    objCell = objRow.createCell(2);  objCell.setCellValue("업체명");  objCell.setCellStyle(styleHd);
    objCell = objRow.createCell(3);  objCell.setCellValue("대표자명");  objCell.setCellStyle(styleHd);
    objCell = objRow.createCell(4);  objCell.setCellValue("사업자등록번호");  objCell.setCellStyle(styleHd);
    objCell = objRow.createCell(5);  objCell.setCellValue("주소");  objCell.setCellStyle(styleHd);
    objCell = objRow.createCell(6);  objCell.setCellValue("업종");  objCell.setCellStyle(styleHd);
    objCell = objRow.createCell(7);  objCell.setCellValue("보전율(%)");  objCell.setCellStyle(styleHd);
    objCell = objRow.createCell(8);  objCell.setCellValue("추천일자");  objCell.setCellStyle(styleHd);
    objCell = objRow.createCell(9);  objCell.setCellValue("추천금액(백만원)");  objCell.setCellStyle(styleHd);
    objCell = objRow.createCell(10);  objCell.setCellValue("대출일");  objCell.setCellStyle(styleHd);
    objCell = objRow.createCell(11);  objCell.setCellValue("대출원금(백만원)");  objCell.setCellStyle(styleHd);

    // 정렬순서 처리...
    String sOrder = " ORDER BY BIZ_NAME ";
    if(orderBy.equals("NameDesc")){
      sOrder = " ORDER BY BIZ_NAME DESC ";
    }else if(orderBy.equals("LnDate")){
      sOrder = " ORDER BY LN_DATE ";
    }

    String sWhere = " WHERE LN_SEQ IS NOT NULL ";
    // 검색 쿼리 처리...
    if (!Seek.equals("")){
      if (SeekKeyWord.equals("biz_name")) {
        sWhere = sWhere+" AND BIZ_NAME LIKE '%"+ Seek +"%' ";

      }else if (SeekKeyWord.equals("biz_num")) {
        sWhere = sWhere+" AND BIZ_NUM LIKE '%"+ Seek +"%' ";

      }else if (SeekKeyWord.equals("biz_owner")) {
        sWhere = sWhere+" AND BIZ_OWNER LIKE '%"+ Seek +"%' ";

      }
    }

    // 년도 선택에 따른 조건처리...
    String sIS_YMQ = "Y";
    if(!ln_year.equals("")){
      if(ln_quarter.equals("1")){
        sWhere = sWhere+" AND LN_YEAR = "+ln_year+" AND LN_MONTH IN (1,2,3) ";
        sIS_YMQ = "Q";
      }else if(ln_quarter.equals("2")){
        sWhere = sWhere+" AND LN_YEAR = "+ln_year+" AND LN_MONTH IN (4,5,6) ";
        sIS_YMQ = "Q";
      }else if(ln_quarter.equals("3")){
        sWhere = sWhere+" AND LN_YEAR = "+ln_year+" AND LN_MONTH IN (7,8,9) ";
        sIS_YMQ = "Q";
      }else if(ln_quarter.equals("4")){
        sWhere = sWhere+" AND LN_YEAR = "+ln_year+" AND LN_MONTH IN (10,11,12) ";
        sIS_YMQ = "Q";
      }else if(!ln_month.equals("")){
        sWhere = sWhere+" AND LN_YEAR = "+ln_year+" AND LN_MONTH = "+ln_month+" ";
        sIS_YMQ = "M";
      }
    }

    // 기업 구분처리..
    if(!biz_gubun.equals("")){
      sWhere = sWhere+" AND BIZ_GUBUN = '"+biz_gubun+"' ";
    }

    // Total Count / Pages ...
    int iTotalListCnt = 0;
    sSql = "SELECT COUNT(LN_SEQ) FROM TB_LOAN "+sWhere;
    iTotalListCnt = dbhandle.getSelectoneInt(sSql);

    // List Selecting....
    sSql = "SELECT " +
            "LN_SEQ,  " +
            "BIZ_NUM,  " +
            "BIZ_GUBUN,  " +

            "BIZ_NAME,  " +
            "BIZ_TYPE,  " +
            "BIZ_OWNER,  " +
            "BIZ_ADDR,  " +

            "LN_YEAR,  " +
            "LN_MONTH,  " +

            "INTDF_RATIO,  " +
            "DATE_FORMAT(RECOM_DATE,'%Y%m%d') RECOM_DATE,  " +
            "RECOM_PRICE,  " +
            "DATE_FORMAT(LN_DATE,'%Y%m%d') LN_DATE,  " +
            "LN_PRICE,  " +

            "CHARGE_RATIO, "+
            "CHARGE_PRICE,  "+
            "ETC1,  " +
            "ETC2,  " +
            "ETC3,  " +
            "ETC4,  " +
            "ETC5,  " +
            "REGDATE,  " +
            "REG_ID,  " +
            "REG_IP,  " +
            "EDITDATE,  " +
            "EDIT_ID,  " +
            "EDIT_IP,  " +
            "DATE_FORMAT(REGDATE,'%Y-%m-%d') RDATE,DATE_FORMAT(REGDATE,'%Y%m%d%H%i%s') STRRDATE  " +
            " FROM TB_LOAN " + sWhere + " " + sOrder;
    // System.out.println(sSql);
    aryDB = dbhandle.getSelects(sSql);
    if(aryDB != null && aryDB.size() > 0){
      int iTotalBizCnt = 0;           // 업체수
      long lnTotalLnPrice = 0;        // 대출금 합계
      long lnTotalRecom = 0;         // 추천금액
      int iRatio = cfgCHARGE_RATIO;             // 0.2%  수수료율
      long lnTotalCharge = 0;         // 청구금액(위탁수수료)

      for(int i = 0 ; i < aryDB.size(); i++){
        hsROW = aryDB.get(i);
        // 글번호...
        int num = (iTotalListCnt - i);

        // 구분처리
        String sStrBizGubun = "";
        String sBizGubun = (String)hsROW.get("biz_gubun");  if(sBizGubun == null) sBizGubun = "";

        if(sBizGubun.equals("J")) sStrBizGubun = "중소기업";
        else  sStrBizGubun = "소상공인";

        // n행 : 내용
        objRow = objSheet.createRow(i + 5);
        objCell = objRow.createCell(0);  objCell.setCellValue(num);  objCell.setCellStyle(styleConRight);
        objCell = objRow.createCell(1);  objCell.setCellValue(sStrBizGubun);  objCell.setCellStyle(styleCon);
        objCell = objRow.createCell(2);  objCell.setCellValue((String)hsROW.get("biz_name"));  objCell.setCellStyle(styleConLeft);
        objCell = objRow.createCell(3);  objCell.setCellValue((String)hsROW.get("biz_owner"));  objCell.setCellStyle(styleCon);
        objCell = objRow.createCell(4);  objCell.setCellValue((String)hsROW.get("biz_num"));  objCell.setCellStyle(styleCon);
        objCell = objRow.createCell(5);  objCell.setCellValue((String)hsROW.get("biz_addr"));  objCell.setCellStyle(styleConLeft);
        objCell = objRow.createCell(6);  objCell.setCellValue((String)hsROW.get("biz_type"));  objCell.setCellStyle(styleCon);
        objCell = objRow.createCell(7);  objCell.setCellValue((String)hsROW.get("intdf_ratio"));  objCell.setCellStyle(styleCon);
        objCell = objRow.createCell(8);  objCell.setCellValue((String)hsROW.get("recom_date"));  objCell.setCellStyle(styleCon);
        objCell = objRow.createCell(9);  objCell.setCellValue((String)hsROW.get("recom_price"));  objCell.setCellStyle(styleConRight);
        objCell = objRow.createCell(10);  objCell.setCellValue((String)hsROW.get("ln_date"));  objCell.setCellStyle(styleCon);
        objCell = objRow.createCell(11);  objCell.setCellValue((String)hsROW.get("ln_price")); objCell.setCellStyle(styleConRight);

        iTotalBizCnt++;
        lnTotalLnPrice += util.getParseLong((String)hsROW.get("ln_price"),0);
        lnTotalRecom += util.getParseLong((String)hsROW.get("recom_price"),0);
        lnTotalCharge += util.getParseLong((String)hsROW.get("charge_price"),0);
      }
      // 전체 합계..
      objRow = objSheet.createRow(4);
      objCell = objRow.createCell(0); objCell.setCellValue("합계");  objCell.setCellStyle(styleSumCon);
      objCell = objRow.createCell(1);  objCell.setCellValue(iTotalBizCnt+"개사");  objCell.setCellStyle(styleSumCon);
      objCell = objRow.createCell(2); objCell.setCellStyle(styleSumCon);
      objCell = objRow.createCell(3);  objCell.setCellValue("");  objCell.setCellStyle(styleSumCon);
      objCell = objRow.createCell(4); objCell.setCellStyle(styleSumCon);
      objCell = objRow.createCell(5); objCell.setCellStyle(styleSumCon);
      objCell = objRow.createCell(6); objCell.setCellStyle(styleSumCon);
      objCell = objRow.createCell(7); objCell.setCellStyle(styleSumCon);
      objCell = objRow.createCell(8); objCell.setCellStyle(styleSumCon);
      objCell = objRow.createCell(9);  objCell.setCellValue(lnTotalRecom);  objCell.setCellStyle(styleSumConRightNum);
      objCell = objRow.createCell(10);  objCell.setCellValue("");  objCell.setCellStyle(styleSumCon);
      objCell = objRow.createCell(11);  objCell.setCellValue(lnTotalLnPrice);  objCell.setCellStyle(styleSumConRightNum);

      // 제목 합계..
      objRow = objSheet.createRow(1);
      objCell = objRow.createCell(0);
      if(sIS_YMQ.equals("Y")){
        objCell.setCellValue(ln_year + "년");
      }else if(sIS_YMQ.equals("M")){
        objCell.setCellValue(ln_year + "년 " + ln_month+"월");
      }else if(sIS_YMQ.equals("Q")){
        objCell.setCellValue(ln_year + "년 " + ln_quarter+"/4분기");
      }
      objCell.setCellStyle(styleCon);
      lnTotalLnPrice = lnTotalLnPrice * 1000000;
      objCell = objRow.createCell(1);  objCell.setCellStyle(styleCon);
      objCell = objRow.createCell(2);  objCell.setCellValue(iTotalBizCnt+"개사");  objCell.setCellStyle(styleCon);
      objCell = objRow.createCell(3);  objCell.setCellValue(lnTotalLnPrice);  objCell.setCellStyle(styleConRightNum);
      objCell = objRow.createCell(4);  objCell.setCellStyle(styleCon);
      objCell = objRow.createCell(5);  objCell.setCellValue(lnTotalLnPrice);  objCell.setCellStyle(styleConRightNum);
      objCell = objRow.createCell(6);  objCell.setCellStyle(styleConLeft);
      objCell = objRow.createCell(7);  objCell.setCellValue(" x 0."+cfgCHARGE_RATIO+"%"); objCell.setCellStyle(styleCon);
      objCell = objRow.createCell(8);  objCell.setCellValue("");  objCell.setCellStyle(styleCon);
      objCell = objRow.createCell(9);  objCell.setCellValue(lnTotalCharge);  objCell.setCellStyle(styleConRightNum);
      objCell = objRow.createCell(10);  objCell.setCellStyle(styleCon);
      objCell = objRow.createCell(11);  objCell.setCellValue("");  objCell.setCellStyle(styleCon);
    }

    // Sheet Cell 넓이 : 최종 마지막에 objSheet.autoSizeColumn() 처리
    for(int i = 0 ; i < iCellCnt; i++){
      objSheet.autoSizeColumn(i); objSheet.setColumnWidth(i, (objSheet.getColumnWidth(i)) + 1200); //  자동조정한 사이즈에 1024를 추가
    }

    fileOut = response.getOutputStream();
    objWorkBook.write(fileOut);
    fileOut.close();
  }catch (Exception ee){
    System.out.println("ERR :: "+ee.toString());
  }

  dbhandle.close(dbhandle.con);
%>