[java] POI사용하여 sheet분할 Excel파일 생성

구조설명

Controller -> Interface -> Impl -> DAO -> XML

VO에 담김

########################## ArchDiffSQL.xml #####################################

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"
    "http://www.ibatis.com/dtd/sql-map-2.dtd">

<sqlMap namespace="ArchDiffSQL"> 
 
 <typeAlias alias="EtArchDiffVO" type="mil.mnd.gui.vo.EtArchDiffVO"/>      <!-- 아키텍처 차이 분석  -->

 <!-- 아키텍처 차이분석 조회-->
 <resultMap id="ArchDiffListMap" class="EtArchDiffVO">
  <result property="enttId"  column="ENTT_ID"/>
  <result property="enttLocalNm" column="ENTT_LOCAL_NM"/>
  <result property="objId1"     column="OBJ_ID1"/>
  <result property="objNm1"     column="OBJ_NM1"/>
  <result property="objId2"     column="OBJ_ID2"/>
  <result property="objNm2"     column="OBJ_NM2"/>
  <result property="notEqlCnt" column="NOT_EQL_CNT"/>
 </resultMap>

 

 <!-- 아키텍처 기준아키텍처와 비교아키텍처 차이분석 엑셀저장 --> 
 <select id="ArchMngDAO.getArchDiffListExcel" parameterClass="map" resultMap="ArchDiffListMap">
      /* ArchMngDAO.getArchDiffListExcel */
   SELECT  NVL(AA.ENTT_ID,BB.ENTT_ID) ENTT_ID
         , NVL(AA.ENTT_LOCAL_NM,BB.ENTT_LOCAL_NM) ENTT_LOCAL_NM
         , AA.OBJ_ID OBJ_ID1
         , AA.OBJ_NM OBJ_NM1
         , BB.OBJ_ID OBJ_ID2
         , BB.OBJ_NM OBJ_NM2
         , (CASE WHEN AA.OBJ_NM != BB.OBJ_NM OR BB.OBJ_NM IS NULL THEN 1 ELSE 0 END) NOT_EQL_CNT
   FROM (SELECT D.ENTT_ID
              , D.ENTT_LOCAL_NM
              , C.OBJ_ID
              , C.OBJ_NM
           FROM ET_PDT A, ET_PDT_OBJ_MAPP B, ET_OBJ C, ET_ENTT D
          WHERE A.ARCH_ID = #baseArchId#
            AND A.PDT_ID  = B.PDT_ID
            AND B.OBJ_ID  = C.OBJ_ID
            AND C.ENTT_ID = D.ENTT_ID
        ) AA,
        (SELECT D.ENTT_ID
              , D.ENTT_LOCAL_NM
              , C.OBJ_ID
              , C.OBJ_NM
           FROM ET_PDT A, ET_PDT_OBJ_MAPP B, ET_OBJ C, ET_ENTT D
          WHERE A.ARCH_ID = #compaArchId#
            AND A.PDT_ID  = B.PDT_ID
            AND B.OBJ_ID  = C.OBJ_ID
            AND C.ENTT_ID = D.ENTT_ID
        ) BB
     WHERE AA.ENTT_LOCAL_NM = BB.ENTT_LOCAL_NM(+)
    AND AA.OBJ_NM        = BB.OBJ_NM(+)
   UNION
      SELECT NVL(AA.ENTT_ID,BB.ENTT_ID) ENTT_ID
           , NVL(AA.ENTT_LOCAL_NM,BB.ENTT_LOCAL_NM) ENTT_LOCAL_NM
           , AA.OBJ_ID OBJ_ID1
           , AA.OBJ_NM OBJ_NM1
           , BB.OBJ_ID OBJ_ID2
           , BB.OBJ_NM OBJ_NM2
           , (CASE WHEN AA.OBJ_NM IS NULL OR AA.OBJ_NM != BB.OBJ_NM THEN 1 ELSE 0 END) NOT_EQL_CNT
        FROM (SELECT D.ENTT_ID
                   , D.ENTT_LOCAL_NM
                   , C.OBJ_ID
                   , C.OBJ_NM
                FROM ET_PDT A, ET_PDT_OBJ_MAPP B, ET_OBJ C, ET_ENTT D
               WHERE A.ARCH_ID = #baseArchId#
                 AND A.PDT_ID  = B.PDT_ID
                 AND B.OBJ_ID  = C.OBJ_ID
                 AND C.ENTT_ID = D.ENTT_ID
             ) AA,
             (SELECT D.ENTT_ID
                   , D.ENTT_LOCAL_NM
                   , C.OBJ_ID
                   , C.OBJ_NM
               FROM ET_PDT A, ET_PDT_OBJ_MAPP B, ET_OBJ C, ET_ENTT D
              WHERE A.ARCH_ID = #compaArchId#
                AND A.PDT_ID  = B.PDT_ID
                AND B.OBJ_ID  = C.OBJ_ID
                AND C.ENTT_ID = D.ENTT_ID
             ) BB
      WHERE AA.ENTT_LOCAL_NM (+)= BB.ENTT_LOCAL_NM
        AND AA.OBJ_NM        (+)= BB.OBJ_NM
 </select>
 

########################## EtArchDiffVO.java #####################################  

ackage mil.mnd.gui.vo;

public class EtArchDiffVO {
 
 private String enttId;
 private String enttLocalNm;
 private String objId1;
 private String objNm1;
 private String objId2;
 private String objNm2;
 private String notEqlCnt;
 
 private String objNm;
 private String val1;
 private String val2;
 private String attrDesc;
 
 public String getObjNm() {
  return objNm;
 }

public void setObjNm(String objNm) {
  this.objNm = objNm;
 }

.................

}

 

########################## ArchDifferAnalysisMain.jsp###################################

<s-ript language="javas-ript">

 /* 보고서 엑셀로 저장 */
 function go_reportSaving(){
  var f = document.iForm;
  
  if(f.baseArchId.value == "") {
   alert("기준 아키텍처를 검색하여 주십시요.");
   return;
  }
  
  if(f.compaArchId.value == "") {
   alert("비교 아키텍처를 검색하여 주십시요.");
   return;
  }
  f.target = "ifExcel";
  f.action = "/archmng.gui?mthd=getArchDiffListExcel";
  f.submit();
 }

<body>

<!---- Button ---->

<form name="iForm" method="post" action="">

    <table>

      <div class="btn">
       <div class="btn_r">
       <span><a href="javas-ript:go_reportSaving();">

                  <img src="/gui/images/img_admin/btn_saveexcel.gif" alt="엑셀저장"/></a>

       </span>
      </div>

      <!---- Button End ---->

    </table>

</from>

<iframe name="ifExcel" src="" width="0" height="0" ></iframe>

</body>

########################## ArchMngController.java ##################################### 

package mil.mnd.gui.controller.arch;

import java.util.ArrayList;

import java.io.BufferedOutputStream;
import java.net.URLEncoder;
import java.nio.charset.Charset;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

 

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

 

import mil.mnd.gui.vo.EtArchDiffVO;

 

  /*ken add 2011227*/
 @SuppressWarnings({ "rawtypes", "unchecked", "deprecation" })
 public ModelAndView  getArchDiffListExcel(HttpServletRequest req
            , HttpServletResponse res) throws Exception {

  System.out.println("=================== getArchDiffListExcel 메소드 호출 ==================");

  Map<String, Object>map      = new HashMap<String,Object>();
  Map<String, Object>paramMap = new HashMap<String,Object>();
  String baseVerNm            = req.getParameter("baseVerNm");  // 버전 정보명 넘기기(최종-상세비교 팝업에서 사용)
  String compaVerNm           = req.getParameter("compaVerNm"); // 버전 정보명 넘기기(최종-상세비교 팝업에서 사용)
  String baseArchId           = req.getParameter("baseArchId");
  String compaArchId          = req.getParameter("compaArchId");
    
  System.out.println("++++++++++++++++++++++++++++++++++++++++++++++++++++++");
  System.out.println("--- baseVerNm :" + baseVerNm);
  System.out.println("--- compaVerNm :" + compaVerNm);
  System.out.println("--- baseArchId :" + baseArchId);
  System.out.println("--- compaArchId :" + compaArchId);
  System.out.println("++++++++++++++++++++++++++++++++++++++++++++++++++++++");
  
  BufferedOutputStream outs = null;
   
  try{ 
   paramMap.put("baseArchId", baseArchId);     //기준아키텍처ID
   paramMap.put("compaArchId", compaArchId); //비교아키텍처ID
   
   List<Object> resultList = iArchMngService.getArchDiffListExcel(paramMap);
   
   int totCount = resultList.size();
   
   String[] cell_index = {"객체종류",baseVerNm,compaVerNm,"동일여부","속성 값 갯수(기준/비교)"};  

         System.out.println("++++++++++++++++++++++++++++++++++++++++++++++++++++++");
   System.out.println("--- totCount  :" + totCount);
   System.out.println("++++++++++++++++++++++++++++++++++++++++++++++++++++++");
   
   // ken 20111227 sheet에 maxSheetCount만큼 쪼개 totalList에 넣어주기
   int maxSheetCount = 60000;
   int sheetCount = (int) (1 + Math.ceil(totCount/maxSheetCount));
    
   ArrayList<ArrayList> totalList = new ArrayList<ArrayList>();
   ArrayList<EtArchDiffVO> itemList = new ArrayList<EtArchDiffVO>();
   int x = 1;     
   for (int i = 0 ; i < totCount; i++){
    itemList.add((EtArchDiffVO) resultList.get(i));
    if (i == (maxSheetCount*x) - 1){
     totalList.add((ArrayList)itemList);
     x = x + 1;
     itemList = new ArrayList<EtArchDiffVO>(); 
    } else if ( i == totCount - 1) {
     totalList.add((ArrayList)itemList);
    }     
   } 
   

         System.out.println("++++++++++++++++++++++++++++++++++++++++++++++++++++++");
         System.out.println("--- totalList  :" + totalList.size());
   System.out.println("--- sheetCount  :" + sheetCount);
   System.out.println("++++++++++++++++++++++++++++++++++++++++++++++++++++++");
      
   // 엑셀 워크북을 생성 by ken 20111219
   HSSFWorkbook workbook = new HSSFWorkbook();
   
   // 엑셀 스타일 설정
   HSSFFont font = null;
   font = workbook.createFont();
   font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

   HSSFCellStyle styleI = null;
   styleI = workbook.createCellStyle();
   styleI.setFillBackgroundColor(HSSFColor.BLUE.index);
   styleI.setAlignment(HSSFCellStyle.ALIGN_CENTER);
   styleI.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);
   styleI.setBorderLeft(HSSFCellStyle.BORDER_THIN);
   styleI.setBorderRight(HSSFCellStyle.BORDER_THIN);
   styleI.setBorderTop(HSSFCellStyle.BORDER_THIN);

   HSSFCellStyle styleL = null;
   styleL = workbook.createCellStyle();
   styleL.setAlignment(HSSFCellStyle.ALIGN_LEFT);
   styleL.setBorderBottom(HSSFCellStyle.BORDER_THIN);
   styleL.setBorderLeft(HSSFCellStyle.BORDER_THIN);
   styleL.setBorderRight(HSSFCellStyle.BORDER_THIN);

   HSSFCellStyle styleC = null;
   styleC = workbook.createCellStyle();
   styleC.setAlignment(HSSFCellStyle.ALIGN_CENTER);
   styleC.setBorderBottom(HSSFCellStyle.BORDER_THIN);
   styleC.setBorderLeft(HSSFCellStyle.BORDER_THIN);
   styleC.setBorderRight(HSSFCellStyle.BORDER_THIN);
   
   // 엑셀 시트를 생성   
   HSSFSheet[] sheet = new HSSFSheet[sheetCount];
   
   for (int y=0; y < sheetCount; y++){
    // 엑셀시트 라벨만들기
    String sheetName = "sheet" + (y+1);
    sheet[y] = workbook.createSheet(sheetName);
    // 엑셀 컬럼 사이즈 설정
    sheet[y].setColumnWidth((short)0, (short) (1000 * 5));
    sheet[y].setColumnWidth((short)1, (short) (10000 * 1));
    sheet[y].setColumnWidth((short)2, (short) (10000 * 1));
    sheet[y].setColumnWidth((short)3, (short) (1000 * 2));
    sheet[y].setColumnWidth((short)4, (short) (1000 * 5));
    HSSFRow index = sheet[y].createRow(0);
    
    // index를 sheet에 넣기
          for (int h = 0 ; h < cell_index.length; h++){
           HSSFCell cell = index.createCell((short)h);
           cell.setCellValue(cell_index[h]);         
          }   
         
          HSSFRow row = null;
                  
          // 컬럼 제목
    row = sheet[y].createRow(0);
    HSSFCell cell0 = row.createCell((short)0);
    cell0.setCellValue(cell_index[0]);
    cell0.setCellStyle(styleI);
    
    HSSFCell cell1 = row.createCell((short)1);
    cell1.setCellValue(baseVerNm);
    cell1.setCellStyle(styleI);
    
    HSSFCell cell2 = row.createCell((short)2);
    cell2.setCellValue(compaVerNm);
    cell2.setCellStyle(styleI);
    
    HSSFCell cell3 = row.createCell((short)3);
    cell3.setCellValue(cell_index[3]);
    cell3.setCellStyle(styleI);
    
    HSSFCell cell4 = row.createCell((short)4);
    cell4.setCellValue(cell_index[4]);
    cell4.setCellStyle(styleI);
    
    ArrayList<EtArchDiffVO> itemListTemp = (ArrayList)totalList.get(y);
    for (int i = 0 ; i < itemListTemp.size() ; i++){  
     row = sheet[y].createRow(i+1);
     cell0 = row.createCell((short)0);
     cell0.setCellStyle(styleL);
     cell1 = row.createCell((short)1);
     cell1.setCellStyle(styleL);
     cell2 = row.createCell((short)2);
     cell2.setCellStyle(styleL);
     cell3 = row.createCell((short)3);          
     cell3.setCellStyle(styleC);
     cell4 = row.createCell((short)4);        
     cell4.setCellStyle(styleC);
     
     EtArchDiffVO vo = null;
     vo = (EtArchDiffVO)itemListTemp.get(i);
     cell0.setCellValue(vo.getEnttLocalNm());
     cell1.setCellValue(vo.getObjNm1());
     cell2.setCellValue(vo.getObjNm2());
     String notEqlStr = null;
     if("1".equals(vo.getNotEqlCnt())) notEqlStr = "X";
     else notEqlStr = "O";
     cell3.setCellValue(notEqlStr);          
     cell4.setCellValue(""); 
          }
    
   }

         res.reset(); 
   Charset charset = Charset.forName("UTF-8");
   String mime = "application/octet-stream;";       
   res.setContentType(mime + "; charset=" + charset);
   String userAgent = req.getHeader("User-Agent");
   
   String fileName = "ArchDiffValidChkList.xls";
   
   if (userAgent != null && userAgent.indexOf("MSIE 5.5") > -1) {
    res.setHeader("Content-Disposition",
      "filename=" + URLEncoder.encode(fileName, "UTF-8") + ";");
   } else if (userAgent != null && userAgent.indexOf("MSIE") > -1) {
    res.setHeader("Content-Disposition", "attachment; filename="
      + java.net.URLEncoder.encode(fileName, "UTF-8") + ";");
   } else {
    res.setHeader("Content-Disposition", "attachment; filename="
      + new String(fileName.getBytes(charset), "latin1") + ";");
   }
/*   res.setHeader("Content-disposition","attachment; filename=" + fileName + ";" );*/
   res.setHeader("Content-disposition","attachment; filename=ArchDiffValidChkList.xls" );
   outs = new BufferedOutputStream(res.getOutputStream());
   workbook.write(outs);

  }catch (Exception ex){
   ex.printStackTrace();   
   map.put("error", "오류가 발생하였습니다.");
   return new ModelAndView("common/excel_down","map",map);
  }finally{
   try {
    outs.close();
   } catch (Exception e) {
    e.printStackTrace();
    map.put("error", "오류가 발생하였습니다.");
    return new ModelAndView("common/excel_down","map",map);
   }
  }

// ModelAndView가 outputStream을 이미사용하기 때문에 return null을 해야 에러안남
  return null;
 }  
 /*ken add 2011227 end */

 

########################## ArchMngDaoImpl.java #####################################

 @SuppressWarnings("unchecked")
 public List<Object> getArchDiffListExcel(SqlMapClient sqlMap, Map<String, Object> paramMap) throws SQLException {
  List<Object> list = null;
  try {
   list = sqlMap.queryForList("ArchMngDAO.getArchDiffListExcel", paramMap);
  } catch (DataAccessException e) {
   e.printStackTrace();
  }
  return list;
 }
 

 

########################## ArchMngServiceImpl.java #####################################

 public List<Object> getArchDiffListExcel(Map<String, Object> paramMap) throws SQLException {
  SqlMapClient sqlMap = super.getSqlMapClient();
  ArchMngDaoImpl  archMngDao = new ArchMngDaoImpl();  
  List<Object> list = null;
  try{
   list = archMngDao.getArchDiffListExcel(sqlMap, paramMap);
  }catch (Exception e){
   e.printStackTrace();
  }
  return list;
 }

########################## IArchMngService.java #####################################

public interface IArchMngService {

  //아키텍처 차이분석 엑셀저장

   public List<Object> getArchDiffListExcel(Map<String, Object> paramMap) throws SQLException;  

 

}

 

### POI ###

자동 줄 바꿈

HSSFCellStyle.setWrapText(true);

맞춤
HSSFCellStyle.setAlignment(HSSFCellStyle.ALIGN_FILL);

 

위로정렬

HSSFCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

 

병합
sheet.addMergedRegion(new Region(0,(short)0,1,(short)0)); //세로병합
sheet.addMergedRegion(new Region(0,(short)1,0,(short)3)); //가로병합