自定义列，且多级表头，及其导出。 统计报表 - 记录
实例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 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";
}
列表展示 和 导出 的组件
public DishOrder findCountByTimeTypeList(DishOrder dishOrder) {
Date startDate = null;
Date endDate = null;
// 天数
int dateSize = 0;
if (dishOrder.getStartDate() == null) {
Map 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 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 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 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;
}
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 dataList = new ArrayList();
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']}){ %>
<#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"/>
<% } %>
