Jqgrid Excel export using Spring + Jakarta POI

1. JSP

<script type="text/javascript">

//화면 로드 시 조회

function doOnLoadQuery(){

//jqGrid 세팅

var grid = $('#WPCP16002_Grid');

grid.clearGridData();

grid.jqGrid({

url:'/WPC/P16/WPCP16002_tab1_popup_dtl_list.do',

datatype: "json",

jsonReader: {repeatitems : false},

        colNames:['호선', 'PE블록', '2차 PE', '1차 PE', 'BLOCK', 'BLOCK<br>TYPE', '팀'

                  ,'착수','완료', '착수','완료', '지연일', '완료주차'],

        colModel: [ //데이터 매핑 및 로우 속성

          {name:'project_code' ,index:'project_code' ,width:60  ,align:"center"},

          {name:'pe_blk'       ,index:'pe_blk'       ,width:80  ,align:"center"},

          {name:'sec_blk'      ,index:'sec_blk'      ,width:80  ,align:"center"},

          {name:'first_blk'    ,index:'first_blk'    ,width:80  ,align:"center"},

          {name:'blk_no'       ,index:'blk_no'       ,width:80  ,align:"center"},

          {name:'blk_type'     ,index:'blk_type'     ,width:80  ,align:"center"},

          {name:'work_nm'      ,index:'work_nm'      ,width:80  ,align:"center"},

          {name:'ps_dt'        ,index:'ps_dt'        ,width:80  ,align:"center"},

          {name:'pf_dt'        ,index:'pf_dt'        ,width:80  ,align:"center"},

          {name:'ts_dt'        ,index:'ts_dt'        ,width:80  ,align:"center"},

          {name:'tf_dt'        ,index:'tf_dt'        ,width:80  ,align:"center"},

          {name:'delay_day'    ,index:'delay_day'    ,width:70  ,align:"center"},

          {name:'txn_week'     ,index:'txn_week'     ,width:60  ,align:"center"}

          ],

      //caption: 1,

      pager: "#pagingBar",

      rowList: [],

      pgbuttons: false,

      pgtext: null,

      viewrecords: false,

      rowNum:-1,

      //loadonce : true,

      height:'500',

      gridview:true,

      shrinkToFit:false,

      viewrecords:false,

      loadError: function (jqXHR, textStatus, errorThrown) {

          $("#WPCP16002_Grid").html(jqXHR.responseText);

      },

      postData:{

       radio_tp : $('#radio_tp').val(),

  fr_dt    : $('#fr_dt').val(),

  to_dt    : $('#to_dt').val(),

  team     : $('#team').val(),

  team_nm  : $('#team_nm').val(),

  proj_no  : $('#proj_no').val(),

  },

      loadComplete: function (data) {

          },

          gridComplete: function () {

         //천단위 구분자 넣기

           var len, point, str, countRow;

           countRow = $("#WPCP16002_Grid").getGridParam("records") + "";

              point = countRow.length % 3

              len = countRow.length;

              str = countRow.substring(0, point);

              while (point < len) {

                  if (str != "") str += ",";

                  str += countRow.substring(point, point + 3);

                  point += 3;

              }

           $('#totalRows').text('총: '+ str +' 건');

          }

    });

    //Header Grouping

    grid.jqGrid('setGroupHeaders', {

  useColSpanStyle: true, 

  groupHeaders:[

{startColumnName: 'ps_dt', numberOfColumns: 2, titleText: '계획'},

{startColumnName: 'ts_dt', numberOfColumns: 4, titleText: '실적'}

  ]

});

    // Excel Export

    grid.jqGrid('navGrid','#pagingBar', {excel:true, edit:false, add:false

     , del:false, refresh:false, search:false

    });

    grid.jqGrid('navButtonAdd','#pagingBar', {caption:"Excel DownLoad",

    onClickButton: function() {

  grid.jqGrid('excelExport', {url:'/WPC/P16/WPCP16002_tab1_popup_dtl_excelView.do'});

    }});

grid.trigger("reloadGrid");

}

</script>

<div id="WPCP16002_Div">

<table summary="조회 리스트" id="WPCP16002_Grid"></table>

<div id="pagingBar"></div>

</div>

2. Java controller

/**

 * 엑셀다운로드

 * @param request

 * @param response

 * @param model

 * @return

 * @throws Exception

 */

@SuppressWarnings("static-access")

@RequestMapping(value = "/WPC/P16/WPCP16002_tab1_popup_dtl_excelView.do")

public ModelAndView WPCP16002_tab1_popup_dtl_list_excel(HttpServletRequest request, HttpServletResponse response, ModelMap model) throws Exception {

HashMap<String, Object> paraMap = new RequestUtil().paramToHashMap(request);

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

try {

//1. Excel sheet name

String sheetName = "선행직종 공정현황";

String sheetStyle = "normal";

//2. Excel sheet title(목록)

List<String> title = new ArrayList<String>();

title.add("호선");

title.add("PE블록");

title.add("2차 PE");

title.add("1차 PE");

title.add("BLOCK");

title.add("BLOCK TYPE");

title.add("팀");

title.add("착수_계획");

title.add("완료_계획");

title.add("착수_실적");

title.add("완료_실적");

title.add("지연일");

title.add("완료 주차");

//3. Excel data 조회

paraMap.put("FIRST_TYPE", paraMap.get("radio_tp"));

paraMap.put("FRDT", paraMap.get("fr_dt"));

paraMap.put("TODT", paraMap.get("to_dt"));

paraMap.put("TEAM_NM", paraMap.get("team"));

paraMap.put("PROJ_NO", paraMap.get("proj_no").equals("") ? "" : Arrays.asList(((String) paraMap.get("proj_no")).split(",")));

List<WPCP16001_ExcelVO> list = wPCP16002Service.selectWPCP16002_tab1_popup_list_excel(paraMap);

//4. ExcelDownView로 데이터를 넘겨주기 위한 작업

Map map;

List excelList = new ArrayList();

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

{

//title의 목록과 동일하게 구성

map = new HashMap();

map.put(title.get(0).toString(), list.get(i).getProject_code());

map.put(title.get(1).toString(), list.get(i).getPe_blk());

map.put(title.get(2).toString(), list.get(i).getSec_blk());

map.put(title.get(3).toString(), list.get(i).getFirst_blk());

map.put(title.get(4).toString(), list.get(i).getBlk_no());

map.put(title.get(5).toString(), list.get(i).getBlk_type());

map.put(title.get(6).toString(), list.get(i).getWork_nm());

map.put(title.get(7).toString(), list.get(i).getPs_dt());

map.put(title.get(8).toString(), list.get(i).getPf_dt());

map.put(title.get(9).toString(), list.get(i).getTs_dt());

map.put(title.get(10).toString(), list.get(i).getTf_dt());

map.put(title.get(11).toString(), list.get(i).getDelay_day());

map.put(title.get(12).toString(), list.get(i).getTxn_week());

excelList.add(map);

}

//5. HashMap에 담아 ModelAndView 리턴할 때 모두 함께 담아서 보냄

resultMap.put("sheetNm", sheetName);

resultMap.put("sheetSt", sheetStyle);

resultMap.put("Title", title);

resultMap.put("ListExcelDown", excelList);

System.out.println("==============================================");

System.out.println("ExcelDown Controller 끝");

System.out.println("==============================================");

} catch(Exception e) {

e.printStackTrace();

}

return new ModelAndView("ExcelDownView","ListExcelDownMap" ,resultMap);

}

3. ExcelDownView.java

package egovframework.com.cmm;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

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.HSSFSheet;

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

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

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

import org.springframework.web.servlet.view.document.AbstractExcelView;

/**

 * @author

 * @function Excel DownLoad

 * @search Spring AbstractExcelView, Spring + Jakarta POI

 */

public class ExcelDownView extends AbstractExcelView {

@Override

protected void buildExcelDocument(Map<String, Object> model,

HSSFWorkbook workbook, HttpServletRequest request,

HttpServletResponse response) throws Exception {

// TODO Auto-generated method stub

HSSFCell cell = null;

int idx = 0;

int cnt = 0;

//1. 각각의 controller에서 마지막에 넘긴 ModelAndView 가져옴

Map<String, Object> map = (Map<String, Object>) model.get("ListExcelDownMap");

//2. 시트이름 설정

String sheetName = (String) map.get("sheetNm");

String sheetStyle = (String) map.get("sheetSt");//시트 스타일 설정

//3. 엑셀파일 생성

HSSFSheet sheet = workbook.createSheet(sheetName);

cell = getCell(sheet, 0, 0);

setText(cell, sheetName);//시트(0, 0)셀에 제목과 같은 텍스트 세팅

//셀병합 

sheet.addMergedRegion(new CellRangeAddress(

                  0, //시작 행번호

                  0, //마지막 행번호

                  0, //시작 열번호

                  5  //마지막 열번호

));

idx = 0;

//4. 컬럼제목 및 색세팅

List<Object> titleList = (List<Object>) map.get("Title");

//셀 폰트 & 스타일 추가

HSSFCellStyle CellStyle = workbook.createCellStyle();

CellStyle.setFillBackgroundColor(HSSFColor.LIGHT_GREEN.index);

CellStyle.setFillPattern(HSSFCellStyle.ALIGN_CENTER);

HSSFFont font = workbook.createFont();

font.setColor(HSSFColor.RED.index);

//엑셀 상단제목세팅

if(sheetStyle.equals("normal")) {

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

    setText(getCell(sheet, 2, idx++), titleList.get(i).toString());

    getCell(sheet, 2, i).setCellStyle(CellStyle);

}

}

//5. 조회된 데이터 세팅

HashMap getMap = new HashMap();

List<Object> ListExcelDownList = (List<Object>) map.get("ListExcelDown");

//데이터 세팅

if(sheetStyle.equals("normal")) {

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

getMap = (HashMap)ListExcelDownList.get(i);

for(int j = 0; j < titleList.size(); j++) {

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

setText(cell, (String) getMap.get(titleList.get(j).toString()));

//sheet.autoSizeColumn((short)j);//셀 width 자동조정

//sheet.setColumnWidth(j, (sheet.getColumnWidth(j))+512 ); //자동조정 후 잘리는 부분조정 

}

}

//these cords have been separated from second loop(for(int j = 0; j < titleList.size(); j++)) to enhance the download speed!

for(int k=0; k < titleList.size(); k++){

sheet.autoSizeColumn((short)k);//cell width auto resize

sheet.setColumnWidth(k, (sheet.getColumnWidth(k))+512 ); //자동조정 후 잘리는 부분조정

}

}

}

}

4. context-excel.xml

<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">

<bean id="excelZipService" class="egovframework.rte.fdl.excel.impl.EgovExcelServiceImpl">

<property name="mapClass" value="egovframework.com.sym.ccm.zip.service.impl.EgovCcmExcelZipMapping" />

<property name="sqlMapClient" ref="egov.sqlMapClient" />

</bean>

<bean id="excelRdnmadZipService" class="egovframework.rte.fdl.excel.impl.EgovExcelServiceImpl">

<property name="mapClass" value="egovframework.com.sym.ccm.zip.service.impl.EgovCcmExcelRdnmadZipMapping" />

<property name="sqlMapClient" ref="egov.sqlMapClient" />

</bean>

<bean id="ExcelDownView" class="egovframework.com.cmm.ExcelDownView"></bean>

<bean class="org.springframework.web.servlet.view.BeanNameViewResolver">

<property name="order" value="0"></property>

</bean>

</beans>

* Issue

데이터 row가 1000 건 이상 넘어갈 경우 다운로드 시간 오래걸림.

3~4000 rows 정도일 때는 무반응 혹은 error 발생...

3번에 ExcelDownView.java 파일에서 원래 있던 코드를 없애고 아래로 옮기면 문제 해결!!!~^_^

* 참고로 ExcelDownView.java 에서 아래 스타일의 코드도 가능.

//5. 조회된 데이터 세팅

HashMap getMap = new HashMap();

List<Object> ListExcelDownList = (List<Object>) map.get("ListExcelDown");

//데이터 세팅

if(sheetStyle.equals("normal")) {

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

HSSFRow excelRow = sheet.createRow(i+3);

getMap = (HashMap)ListExcelDownList.get(i);

for(int j = 0; j < titleList.size(); j++){

excelRow.createCell(j).setCellValue((String) getMap.get(titleList.get(j).toString()));

}

}

for(int k=0; k < titleList.size(); k++){

sheet.autoSizeColumn((short)k);//셀 width 자동조정

sheet.setColumnWidth(k, (sheet.getColumnWidth(k))+512 ); //자동조정 후 잘리는 부분조정

}