展示:
主要代码:
/**
* 批量导出
*/
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;
}