自定义table列，且多级表头，及其导出 - jeesite记录

自定义列，且多级表头，及其导出。 统计报表 - 记录 实例1

Java后台 控制器

	/**
	 * 查询早餐统计列表
	 */
	@RequestMapping(value = "countMorningList")
	public String countMorningList(DishOrder dishOrder, Model model) {
		CompanyInfo companyInfo = companyInfoService.findInfoByUser(dishOrder.getCurrentUser().getUserCode());
		if(!companyInfo.getCorpCode().equals(UserUtils.getUser().getCorpCode())){
			model.addAttribute("isValidCodeLogin", true);
			return "modules/sys/sysLogin";
		}
		//查询门店列表
		StoreInfo storeInfo = new StoreInfo();
		storeInfo.setCompanyId(companyInfo.getCompanyCode());
		//查询数据权限
		storeInfo.setStoreList(dataRoleComp.findStoreListByUser());
		List<StoreInfo> storeList = storeInfoService.findList(storeInfo);
		//查询统计列表
		dishOrder.setCompanyId(companyInfo.getCompanyCode());
		dishOrder.setTimeType(Constants.TIME_TYPE_MORNING);
		if(storeList != null && !storeList.isEmpty()){
			dishOrder.setStoreList(storeList);
		}
		dishOrder = dishOrderComp.findCountByTimeTypeList(dishOrder);
		
		model.addAttribute("dishOrder", dishOrder);
		model.addAttribute("storeList", storeList);
		return "modules/dish/dishOrderCountMorningList";
	}

列表展示 和 导出 的组件

	/**
	 * 订餐统计列表数据 - 按时段
	 * 
	 * @param dishOrder
	 * @return
	 */
	public DishOrder findCountByTimeTypeList(DishOrder dishOrder) {
		Date startDate = null;
		Date endDate = null;
		// 天数
		int dateSize = 0;
		if (dishOrder.getStartDate() == null) {
			Map<String, String> dateMap = DateUtils.getWeekDate();
			dishOrder.setStartDate(DateUtils.parseDate(dateMap.get("mondayDate")));
			dishOrder.setEndDate(DateUtils.parseDate(dateMap.get("sundayDate")));
			dateSize = 7;

		} else {
			dateSize = (int) DateUtils.getDistanceOfTwoDate(dishOrder.getStartDate(), dishOrder.getEndDate()) + 1;
		}

		List<Date> dateList = DateUtils.getDateList(dishOrder.getStartDate(), dishOrder.getEndDate());

		// 二级表头
		String[] twoLevelArr = new String[dateSize];
		for (int i = 0; i < dateSize; i++) {
			if (i == 0) {
				dishOrder.setStartDate(dateList.get(i));
			}
			if (i == (dateList.size() - 1)) {
				dishOrder.setEndDate(dateList.get(i));
			}

			twoLevelArr[i] = DateUtils.formatDate(dateList.get(i));
		}
		dishOrder.setTwoLevel(JSON.toJSONString(twoLevelArr));
		dishOrder.setTwoLevelArr(twoLevelArr);

		startDate = dishOrder.getStartDate();
		endDate = dishOrder.getEndDate();

		// 标题行
		int j = -1;
		String[] headerArr = new String[twoLevelArr.length * 2];
		for (int i = 0; i < twoLevelArr.length; i++) {
			j++;
			headerArr[j] = "菜品名称";
			j++;
			headerArr[j] = "数量";
		}
		dishOrder.setHeader(JSON.toJSONString(headerArr));
		dishOrder.setHeaderArr(headerArr);

		// 参数名
		j = -1;
		String[] nameArr = new String[headerArr.length];
		for (int i = 0; i < headerArr.length; i++) {
			j++;
			int k = 0;
			if (j >= 2) {
				k = j / 2;
			}
			nameArr[j] = twoLevelArr[k] + "_" + "_dishName";
			j++;
			nameArr[j] = twoLevelArr[k] + "_" + "_dishNumber";
			i++;
		}
		dishOrder.setNames(JSON.toJSONString(nameArr));
		dishOrder.setNameArr(nameArr);

		// 参数值统计列表
		int rowNum = 0;// 行数
		// 查询订餐统计列表
		DishOrder dishOrderInfo = new DishOrder();
		dishOrderInfo.setCompanyId(dishOrder.getCompanyId());
		dishOrderInfo.setStartCountDate(DateUtils.formatDate(startDate));
		dishOrderInfo.setEndCountDate(DateUtils.formatDate(endDate));
		dishOrderInfo.setStoreId(dishOrder.getStoreId());
		dishOrderInfo.setTimeType(dishOrder.getTimeType());
		dishOrderInfo.setStoreList(dishOrder.getStoreList());
		List<DishOrder> list = service.findOrderCountList(dishOrderInfo);
		if (list != null && !list.isEmpty()) {
			// 查询菜品详情列表
			DishOrderDetail dishOrderDetail = new DishOrderDetail();
			dishOrderDetail.setOrderList(list);
			dishOrderDetail.setIsAllDay(dishOrder.getIsAllDay());
			dishOrderDetail.setTimeType(dishOrder.getTimeType());
			dishOrderDetail.setStartCountDate(DateUtils.formatDate(startDate));
			dishOrderDetail.setEndCountDate(DateUtils.formatDate(endDate));
			List<DishOrderDetail> detailList = dishOrderDetailService.findCountList(dishOrderDetail);
			if (detailList != null && !detailList.isEmpty()) {
				rowNum = detailList.size();

				String vals = "[";
				for (int i = 0; i < rowNum; i++) {
					// 去掉多余行
					if (detailList.size() == 0) {
						break;
					}
					vals += "{";

					int nameRow = 0;
					int numRow = 1;

					boolean flag = false;
					for (int k = 0; k < nameArr.length; k++) {
						flag = false;
						int x = 0;
						if (k > 0) {
							x = k / 2;
						}
						Date date = DateUtils.parseDate(twoLevelArr[x]);
						for (int l = 0; l < detailList.size(); l++) {
							if (date.getTime() == detailList.get(l).getDate().getTime()) {
								if (k == nameRow) {// 名称
									vals += "\"" + nameArr[k] + "\":" + "\"" + detailList.get(l).getDishName() + "\"";
									// 下标加2
									nameRow += 2;
									flag = true;
									break;
								} else if (k == numRow) {// 数量
									vals += "\"" + nameArr[k] + "\":" + "\"" + detailList.get(l).getNumber() + "\"";
									detailList.remove(l);// 数量获取后，移除
									// 下标加2
									numRow += 2;
									flag = true;
									break;
								}
							}
						}
						if (!flag) {
							if (k == nameRow) {// 名称
								nameRow += 2;
							} else if (k == numRow) {// 数量
								numRow += 2;
							}
							vals += "\"" + nameArr[k] + "\":" + "\"---\"";
							flag = true;
						}
						if (k != (nameArr.length - 1) && flag) {
							vals += ",";
						}
						if (k == (nameArr.length - 1)) {
							String douHao = vals.substring(vals.length() - 1, vals.length());
							if (",".equals(douHao)) {
								vals = vals.substring(0, vals.length() - 1);
							}
						}
					}

					vals += "}";
					if (i < (rowNum - 1)) {
						vals += ",";
					} else {
						String douHao = vals.substring(vals.length() - 1, vals.length());
						if (",".equals(douHao)) {
							vals = vals.substring(0, vals.length() - 1);
						}
					}

				}
				vals += "]";
				dishOrder.setValues(vals);
			}

		} else {
			dishOrder.setValues("0");// 不能为空，前端报错
		}

		return dishOrder;
	}



	/**
	 * 导出订餐统计列表 - 按时段、日期
	 * 
	 * @param dishOrder
	 * @return
	 */
	public boolean exportCountByTimeType(DishOrder dishOrder, HttpServletResponse response) {
		// 二级表头
		String[] twoLevelArr = dishOrder.getTwoLevelArr();
		// 一级表头
		String[] headerArr = dishOrder.getHeaderArr();
		// nameArr
		String[] nameArr = dishOrder.getNameArr();
		// 数据组装
		String values = dishOrder.getValues();
		List<Object[]> dataList = new ArrayList<Object[]>();
		if (!"0".equals(values)) {
			JSONArray valueJsonArr = JSONArray.parseArray(values);
			for (int i = 0; i < valueJsonArr.size(); i++) {
				Object[] arr = new Object[nameArr.length];
				JSONObject valObj = valueJsonArr.getJSONObject(i);
				for (int j = 0; j < nameArr.length; j++) {
					arr[j] = valObj.getString(nameArr[j]);
				}
				dataList.add(arr);
			}
		}

		String storeName = "";
		if (StringUtils.isNotBlank(dishOrder.getStoreId())) {
			StoreInfo storeInfo = storeInfoService.get(dishOrder.getStoreId());
			storeName = storeInfo.getName() + "-";
		}

		String titleStr = "";
		if (StringUtils.isBlank(dishOrder.getTimeType())) {
			titleStr = "按日期";
		} else if ("0".equals(dishOrder.getTimeType())) {
			titleStr = "早餐";
		} else if ("1".equals(dishOrder.getTimeType())) {
			titleStr = "午餐";
		} else if ("2".equals(dishOrder.getTimeType())) {
			titleStr = "晚餐";
		}

		try {
			ExportDishExcel ex = new ExportDishExcel(storeName + "订餐统计表-" + titleStr, twoLevelArr, dataList, response);
			HSSFWorkbook workbook = ex.getWorkbook();
			HSSFSheet sheet = ex.getSheet();

			/*
			 * 产生表格标题行 设置跨列
			 */
			HSSFRow rowm = sheet.createRow(0);
			rowm.setHeight((short) (25 * 35)); // 设置高度
			// sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】
			HSSFCellStyle columnTopStyle = ex.getColumnTopStyle(workbook);// 获取列头样式对象
			HSSFCellStyle style = ex.getStyle(workbook); // 单元格样式对象
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headerArr.length - 1)); //
			{
				HSSFCell cellRowName = rowm.createCell(0); // 创建列头对应个数的单元格
				cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); // 设置列头单元格的数据类型
				HSSFRichTextString text = new HSSFRichTextString(
						ex.getTitle() + "（" + twoLevelArr[0] + " ~ " + twoLevelArr[twoLevelArr.length - 1] + "）");
				cellRowName.setCellValue(text); // 设置列头单元格的值
				cellRowName.setCellStyle(columnTopStyle); // 设置列头单元格样式
			}

			int startNo = 0;
			int endNo = 0;
			// 二级表头
			rowm = sheet.createRow(1);
			startNo = 0;
			for (int i = 0; i < twoLevelArr.length; i++) {
				endNo = startNo + 1;
				sheet.addMergedRegion(new CellRangeAddress(1, 1, startNo, endNo)); //

				startNo += 2;
			}

			// 将列头设置到sheet的单元格中
			int k = 0;
			for (int n = 0; n < twoLevelArr.length * 2; n++) {
				HSSFCell cellRowName = rowm.createCell(n); // 创建列头对应个数的单元格
				cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); // 设置列头单元格的数据类型
				if (n > 1) {
					k = n / 2;
				}
				HSSFRichTextString text = new HSSFRichTextString(twoLevelArr[k]);
				cellRowName.setCellValue(text); // 设置列头单元格的值
				cellRowName.setCellStyle(columnTopStyle); // 设置列头单元格样式
			}

			// 一级表头
			rowm = sheet.createRow(2);

			// 将列头设置到sheet的单元格中
			for (int n = 0; n < headerArr.length; n++) {
				HSSFCell cellRowName = rowm.createCell(n); // 创建列头对应个数的单元格
				cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); // 设置列头单元格的数据类型
				HSSFRichTextString text = new HSSFRichTextString(headerArr[n]);
				cellRowName.setCellValue(text); // 设置列头单元格的值
				cellRowName.setCellStyle(columnTopStyle); // 设置列头单元格样式
			}

			ex.createBox(storeName + "订餐统计表-" + titleStr + ".xls", style, headerArr.length, 3);

			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}

	}

前端页面

<% layout('/layouts/default.html', {title: '订餐统计管理', libs: ['dataGrid']}){ %>
<style type="text/css">
td,
th {
font-style: normal;
font-weight: normal;
text-align: center;
min-width: 30px;
padding: 3px 6px;
}
 
tr {
height: 36px;
}

.firstHead,
.twoHead,{
height: 32px;
}

.firstHead th {
font-size: 13px;
font-weight: bold;
}

.twoHead th {
min-width: 80px;
}
 
table {
border: none;
border-collapse: collapse;
border-color: #D8DFE6;
}
 
table thead {
background: #F3FDFF;
}
 
table tbody tr {
height: 36px;
}

.Wdate-date{
width: 125px!important;
}

body{
font-size: 10px;
}


</style>
<div class="main-content">
	<div class="box box-main">
		<div class="box-header">
			<div class="box-title">
				<i class="fa fa-list-alt"></i> ${text('订餐统计 - 早餐')}
			</div>
			<div class="box-tools pull-right">
				<button type="button" class="btn btn-default" id="btnExport" title="${text('导出')}"><i class="glyphicon glyphicon-export"></i>
					${text('导出')}</button>
			</div>
		</div>
		<div id="talBody" class="box-body scroll-x" >
			<#form:form id="searchForm" model="${dishOrder}" action="${ctx}/dish/di****der/countMo****ist" method="post" class="form-inline"
					data-page-no="${parameter.pageNo}" data-page-size="${parameter.pageSize}" data-order-by="${parameter.orderBy}">
				<#form:hidden path="timeType"/>
				<div class="form-group">
					<label class="control-label">${text('订餐日期')}：</label>
					<div class="control-inline">
						<#form:input path="startDate" readonly="true" maxlength="20" class="form-control Wdate-date"
							dataFormat="date" onclick="WdatePicker({dateFmt:'yyyy-MM-dd',isShowClear:false,onpicked:function(){endDate.click()}});"/>
						&nbsp;--&nbsp;
						<#form:input path="endDate" readonly="true" maxlength="20" class="form-control Wdate-date"
							dataFormat="date" onclick="WdatePicker({dateFmt:'yyyy-MM-dd',isShowClear:false});"/>
					</div>
				</div>
				<div class="form-group">
					<label class="control-label">${text('选择门店')}：</label>
					<div class="control-inline" style="width:150px;">
						<#form:select id="storeId"
							path="storeId" items="${storeList}" itemLabel="name"
							itemValue="id" class="form-control "
							blankOption="true" />
					</div>
				</div>
				<div class="form-group">
					<button type="button" class="btn btn-primary btn-sm" onclick="checkPage('startDate','endDate', 32);">${text('查询')}</button>
				</div>
			</#form:form>
			<div class="table" >
				<table border="1" id="tab" cellspacing="0" cellpadding="10" width="100%">
					<thead>
						<tr class="firstHead">
						<th colspan="1" rowspan="2" width="30px"></th>
						</tr>
						<tr class="twoHead">
						</tr>															
					</thead>
					<tbody>
					</tbody>
				</table>
			</div>
		</div>
	</div>
</div>
<% } %>
<script>

function pageInit(){
	var header = ${dishOrder.header};
	var name = ${dishOrder.names};
	var twoLevelArr = ${dishOrder.twoLevel};
	
	
	var tab = $('#tab');
	//一级表头
	for (var i = 0; i < twoLevelArr.length; i++) {
		tab.find('thead .firstHead').append(""+twoLevelArr[i]+"")
	}
	//二级表头
	for (var i = 0; i < header.length; i++) {
		tab.find('thead .twoHead').append(""+header[i]+"")
	}
	
	var values = ${dishOrder.values};
	//填入值
	for (var i = 0; i < values.length; i++) {
		var tdStr = ";
		if(((i+1) % 2)  == 0 ){
			tdStr += " class='ui-priority-secondary'"
		}
		
		tdStr += ">"+(i + 1)+"";
		$.each(values[i],function(name,value) {
			tdStr = tdStr +""+ value +"";
		});		
		tdStr = tdStr+ "";
		tab.find('tbody').append(tdStr);
	}
}

$(document).ready(function() {
	$("#talBody").height($(document).height() - 80);
	pageInit();
	
	$('#searchButton').click(function(){
		var startDate = $("#startDate").val();
		var endDate = $("#endDate").val();
		if(endDate < startDate){
			js.showMessage("截止日期不允许小于起始日期，请重新选择订餐日期！");
			return false;
		}
	});
});

$('#btnExport').click(function(){
	js.ajaxSubmitForm($('#searchForm'), {
		url:'/a/dish/di****der/exportC*****ype',
		downloadFile:true
		
	});
});

</script>