zhoupan / jmesa

Automatically exported from code.google.com/p/jmesa
0 stars 0 forks source link

export excel with 0 to many relationship #337

Open GoogleCodeExporter opened 9 years ago

GoogleCodeExporter commented 9 years ago
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.

Original issue reported on code.google.com by uncleyeu...@gmail.com on 11 Jun 2012 at 10:10