Jfinal学习----批量导出数据到excel

展示：

 

 

主要代码：

/**
     * 批量导出
     */
    public void exportExcel(){
        String elektitaLabel = getPara("elektitaLabel");
        Integer status = getParaToInt("status");//0-未租 ；1-在租   (0闲置中1出租未满 2出租已满)
        List<List<String>> rows = houseService.exportExcel(elektitaLabel, status);
        String uuid = IdUtil.randomUUID();
        String url = PathKit.getWebRootPath()+"/download/"+uuid+".xlsx";
        if(rows.size() <= 1000){
            ExcelWriter writer = ExcelUtil.getWriter(url);
            writer.write(rows);
            writer.close();
        }else{
            BigExcelWriter writer= ExcelUtil.getBigWriter(url);
            writer.write(rows);
            writer.close();
        }
        EventKit.post(new FileEvent(url));
        renderFile(new File(url), DateUtil.now()+".xlsx");
    }

/**
	 * 小程序后台管理-房间管理 批量导出
	 * @param elektitaLabel
	 * @param status
	 * @return
	 */
	public List<List<String>> exportExcel(String elektitaLabel, Integer status){
		List<Object> params = new ArrayList<Object>();
		StringBuilder builder = new StringBuilder("select h.id,b.communityName,h.numberPlate,h.status,c.tenantName,c.tenantMobile,h.rentStatus,h.elektita_label as elektitaLabel, c.checkInTime,c.checkOutTime,c.signStatus");
		builder.append("  from fwzl_house h join fwzl_building b on h.buildingId = b.id left join");
		builder.append("  (select  hs.houseId,hs.checkInTime,hs.checkOutTime,hs.status as signStatus,aet.fullName as tenantName,aet.mobile as tenantMobile");
		builder.append(" from fwzl_house_sign hs join upms_account_expand aet on hs.accountId = aet.accountId where hs.status = ? ) c on c.houseId = h.id");
		params.add(FwzlConstant.HOUSE_SIGN_STATUS_ING);
		builder.append("  where h.delFlag = 0");
		if(Assert.CheckNotNull(elektitaLabel)){
			builder.append(" and h.elektitaLabel like ?");
			params.add("%"+elektitaLabel+"%");
		}
		if(Assert.CheckNotNull(status)){
			if(status == 0){
				builder.append(" and (h.status = ? or h.status = ?)");
				params.add(FwzlConstant.HOUSE_STATUS_ING);
				params.add(FwzlConstant.HOUSE_STATUS_NOT_FULL);
			}else if(status == 1){
				builder.append(" and (h.status = ? or h.status = ?)");
				params.add(FwzlConstant.HOUSE_STATUS_NOT_FULL);
				params.add(FwzlConstant.HOUSE_STATUS_HAD_FULL);
			}
		}
		builder.append("  group by h.id order by b.communityName desc,h.numberPlate desc");
		List<Record> list = Db.find(builder.toString(), params.toArray());
		List<List<String>> rows = new ArrayList<List<String>>();
		List<String> row1 = CollUtil.newArrayList("ID", "小区名", "房间名", "房间状态", "租客信息", "租客手机号", "显示状态", "精选标签", "租期");
		rows.add(row1);
		for(Record record : list){
			String statusStr = "";
			if(record.getInt("status") == 0){
				statusStr = "空房";
			}else if(record.getInt("status") == 1){
				statusStr = "在租/空房";
			}else if(record.getInt("status") == 2){
				statusStr = "在租";
			}else if(record.getInt("status") == 3){
				statusStr = "自住";
			}
			String displayStatusStr = "";
			if(record.getInt("rentStatus") == 0){
				displayStatusStr = "下架";
			}else if(record.getInt("rentStatus") == 1){
				displayStatusStr = "上架";
			}
			List<String> row = CollUtil.newArrayList(record.getStr("id"), record.getStr("communityName"), record.getStr("numberPlate"),
					statusStr,record.getStr("tenantName"),record.getStr("tenantMobile"),displayStatusStr,record.getStr("elektitaLabel"),
					DateUtil.formatDateTime(record.getDate("checkOutTime")));
			rows.add(row);
		}
		return rows;
	}