Issue 337: export excel with 0 to many relationship
Status:  New
Owner: ----
Reported by uncleyeu...@gmail.com, Jun 11, 2012
The following data is display well in jsp:-

<%@ taglib uri="/WEB-INF/tld/jmesa.tld" prefix="jmesa" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<%@ taglib prefix="form" uri="http://www.springframework.org/tags/form" %> 
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>

<c:url value="/cpt/userMain.do" var="userMainAction"></c:url>
<form:form method="post" action="${userMainAction}" commandName="userMainForm">
<table width="100%">
<tr>
	<td valign="top">
		<jmesa:tableModel
			id="userMain" 
		    items="${userMainTable}"
		    var="bean"
		    filterMatcherMap="cn.ccbi.web.view.CptCompanyMatchFilterMap"
		>
			<jmesa:htmlTable width="95%">
				<jmesa:htmlRow>
					<jmesa:htmlColumn property="userId" title="员工登入*" width="15%">
					    <c:url value="/cpt/userView.do?method=edit&paramCcbiUserId=${bean.ccbiUserId}" var="userIdUrl"></c:url>
						<a class="nextpage" href="${userIdUrl}">${bean.userId}</a>
					</jmesa:htmlColumn>
					<jmesa:htmlColumn property="staffNo" title="员工编号" width="15%"/>
					<jmesa:htmlColumn property="company" title="公司" width="15%" filterEditor="cn.ccbi.web.view.CptCompanyDroplistFilterEditor">
						<c:out value="${bean.companyName}" />
					</jmesa:htmlColumn>
					<jmesa:htmlColumn property="userEname" title="英文名" width="25%"/>
					<jmesa:htmlColumn property="userCname" title="中文名" width="20%" filterable="false" sortable="false">
						${bean.userCname}&nbsp;
					</jmesa:htmlColumn>
					<jmesa:htmlColumn property="licList" title="持有牌照" width="10%" filterable="false" sortable="false">
						<c:choose>
						<c:when test="${bean.licList.size() > 0}">
							<c:forEach var="i" begin="0" end="${bean.licList.size()}" step="1">
								<c:if test="${i>0}"><br></c:if>						
								${bean.licList[i].licName}
							</c:forEach>						
						</c:when>
						<c:otherwise>&nbsp;</c:otherwise>
						</c:choose>
					</jmesa:htmlColumn>
				</jmesa:htmlRow>
			</jmesa:htmlTable> 
		</jmesa:tableModel>
	</td>
</tr>
<tr>
	<td>&nbsp;</td>
</tr>
<tr>
	<td>
		<input type="button" class="button" value="<fmt:message key="Button.Add" />" onclick="location.href='<c:url value='/cpt/userView.do?method=add'/>'"/>		
	</td>
</tr>
</table>
</form:form>



But fail to export excel
code in Controller will be:-

tableModel.setItems(formUserList);
.
.
.
formUserList = getFormModel(cptUserList, paramYear, paramStatus);
.
.
.
	private List<CptUserRecords> getFormModel(List<CptUserMast> cptUserList, Long paramYear, String paramStatus) {
		List<CptUserRecords> formUserlist = new ArrayList<CptUserRecords>();
			
		for (CptUserMast cptUser : cptUserList) {
			
			CptUserRecords formUser = new CptUserRecords();
			
			formUser.setCcbiUserId(cptUser.getCcbiUserId());
			formUser.setUserId(cptUser.getUserId());
			formUser.setStaffNo(cptUser.getStaffNo());
			formUser.setCompany(cptUser.getCompany());
			formUser.setUserEname(cptUser.getUserNameEn());
			formUser.setRegNo(cptUser.getRegNo());
			formUser.setStatus(cptUser.getStatus());
			
			if (cptUser.getCptUserLicLists()==null || cptUser.getCptUserLicLists().size()==0) {
				formUserlist.add(formUser);
				continue;
			}
			
			Map<String, BigDecimal> sponsorHourRemainMap = new HashMap<String, BigDecimal>();
			
			List<CptUserLicRecords> licList = new ArrayList<CptUserLicRecords>();
			for (CptUserLicList cptLicList:cptUser.getCptUserLicLists()) {
								
				long licId = cptLicList.getId().getLicId();
				long licPeriod = cptLicList.getId().getLicPeriod();
				String lictype= cptLicList.getId().getLicType();
				
				//filter paramYear
				if (licPeriod != paramYear) {
					continue;
				}
								
				CptUserLicRecords licRec = new CptUserLicRecords();
				licRec.setLicId(licId);
				licRec.setCcbiUserId(formUser.getCcbiUserId());
				licRec.setLicPeriod(licPeriod);
				licRec.setLicName(cptLicList.getCptLicense().getLicName());
				licRec.setLicType(lictype);
				licRec.setHourRequest(cptLicList.getHourRequest());

				if (cptLicList.getHourRequest()!=null) {
				
					BigDecimal hourComplete=new BigDecimal(0);
										
					//Calculation for completed hours
					for (CptCourseTxn cptCourseTxn : cptUser.getCptCourseTxns()) {
						//count if status=E
						if (cptCourseTxn.getCptCourseLicList().getCptCourseMast().getStatus()!=null 
							&& cptCourseTxn.getCptCourseLicList().getCptCourseMast().getStatus().equals(Constant.CPT_COURSE_STATUS_ENABLE)) {
							//same lic id, period and type
							if (licId == cptCourseTxn.getId().getLicId() && licPeriod == cptCourseTxn.getId().getCoursePeriod()&& cptCourseTxn.getCptCourseLicList().getId().getLicType().equals(lictype) ) {
								//[long->decimal] hourComplete += cptCourseTxn.getCptCourseLicList().getHoursCount();
								hourComplete = hourComplete.add(cptCourseTxn.getCptCourseLicList().getHoursCount());
							}
						}
					}
					
					cptLicList.setHourComplete(hourComplete);
					licRec.setHourComplete(cptLicList.getHourComplete());
										
					//[long->decimal] licRec.setHourRemain(cptLicList.getHourRequest()- cptLicList.getHourComplete());					
					licRec.setHourRemain(cptLicList.getHourRequest().subtract(cptLicList.getHourComplete()));
					//[long->decimal] if (licRec.getHourRemain()<1) {
					if (licRec.getHourRemain().doubleValue()<=0.0) {
						
						//[long->decimal] if (licRec.getLicType().equals(Constant.CPT_LICENCE_TYPE_SPONSOR) && licRec.getHourRemain()<0) {
						if (licRec.getLicType().equals(Constant.CPT_LICENCE_TYPE_SPONSOR) && licRec.getHourRemain().doubleValue()<0) {
							//[long->decimal] sponsorHourRemainMap.put(cptLicList.getId().getLicId()+"*"+cptLicList.getId().getLicPeriod(), Math.abs(licRec.getHourRemain()));
							sponsorHourRemainMap.put(cptLicList.getId().getLicId()+"*"+cptLicList.getId().getLicPeriod(), licRec.getHourRemain().abs());
						}
						licRec.setHourRemain(new BigDecimal(0));
						licRec.setStatus(Constant.CPT_LICENCE_STATUS_COMPLETE);
						
					} else {
						licRec.setStatus(Constant.CPT_LICENCE_STATUS_INCOMPLETE);
					}
				}
				
				//filter status
				if (licRec.getStatus().equals(paramStatus) || paramStatus.equals(Constant.CPT_LICENCE_STATUS_ALL)) {
					licList.add(licRec);
				}
			}			
								
			for (CptUserLicRecords record: licList) { 
				//[long->decimal] if (record.getLicType().equals(Constant.CPT_LICENCE_TYPE_BASIC) && record.getHourRemain()>0 ) {					
				if (record.getLicType().equals(Constant.CPT_LICENCE_TYPE_BASIC) && record.getHourRemain().doubleValue()>0 ) {
					//[long->decimal] Long remainSponsorHour = sponsorHourRemainMap.get(record.getLicId()+"*"+record.getLicPeriod());
					BigDecimal remainSponsorHour = sponsorHourRemainMap.get(record.getLicId()+"*"+record.getLicPeriod());
					//[long->decimal] if (remainSponsorHour!=null && remainSponsorHour>0) {
					if (remainSponsorHour!=null && remainSponsorHour.doubleValue()>0) {
						
						//[long->decimal] if (remainSponsorHour > record.getHourRemain()) {
						if (remainSponsorHour.doubleValue() > record.getHourRemain().doubleValue()) {
							record.setHourComplete(record.getHourRequest());
							record.setHourRemain(new BigDecimal(0));
							//[long->decimal] sponsorHourRemainMap.put(record.getLicId()+"*"+record.getLicPeriod(), remainSponsorHour-record.getHourRemain());
							sponsorHourRemainMap.put(record.getLicId()+"*"+record.getLicPeriod(), remainSponsorHour.subtract(record.getHourRemain()));
						} else {
							//[long->decimal] record.setHourComplete(record.getHourComplete() + remainSponsorHour);							
							record.setHourComplete(record.getHourComplete().add(remainSponsorHour));
							//[long->decimal] record.setHourRemain(record.getHourRemain()-remainSponsorHour);
							record.setHourRemain(record.getHourRemain().subtract(remainSponsorHour));
							sponsorHourRemainMap.put(record.getLicId()+"*"+record.getLicPeriod(), new BigDecimal(0));
						}
						
						//[long->decimal] if (record.getHourRemain()<1) {
						if (record.getHourRemain().doubleValue()<=0.0) {						
							record.setStatus(Constant.CPT_LICENCE_STATUS_COMPLETE);
						}
					}
				}
				
			}
			
			for (int i=0;i<licList.size();i++) {				
				CptUserLicRecords record = licList.get(i);
				if (record.getLicType().equals(Constant.CPT_LICENCE_TYPE_SPONSOR)){
					if (sponsorHourRemainMap.containsKey(record.getLicId()+"*"+record.getLicPeriod())) {
						//[long->decimal] record.setHourComplete(sponsorHourRemainMap.get(record.getLicId()+"*"+record.getLicPeriod()) + record.getHourRequest());
						record.setHourComplete(sponsorHourRemainMap.get(record.getLicId()+"*"+record.getLicPeriod()).add(record.getHourRequest()));
					}
				}
				//filter status
				if (!paramStatus.equals(Constant.CPT_LICENCE_STATUS_ALL) && !record.getStatus().equals(paramStatus)) {
					licList.remove(i);
				}
				record.setLicType(CptUtil.getLicCourseType(record.getLicType()));				
			}
			
			Collections.sort(licList);			
			formUser.setLicList(licList);
			
			//remove no record user
			if (formUser.getLicList()!=null && formUser.getLicList().size()>0) {
				formUserlist.add(formUser);
			}
		}
		
		return formUserlist;
	}


Please note that the above code can work well in jmesa table but have problem in excel export.