1.查询方法
@Override public DishAppStallPersonnelPageBaseVo pageList(Page<DishAppStallPersonnelPageVo> page, DishAppStallPersonnelPageDto pageDto) { // 获取已绑定的收银门店集合 Map<String, MutablePair<String, StoreInfoListVO>> storeMap = getStoreIds(); List<BaseAttributesJsonVo> stallList = this.infoService.getAttributesList(List.of(), DishInfoTypeEnum.DISH_INFO_STALL.getCode()); // 校验是否有新增门店 this.checkStallPersonnelStore(stallList, storeMap); // 获取门店id集合 List<String> storeIds = StrUtil.isNotBlank(pageDto.getKeyword()) ? storeMap.entrySet().stream().filter(entry -> entry.getKey().contains(pageDto.getKeyword())).map(entry -> entry.getValue().getLeft()).collect(Collectors.toList()) : new ArrayList<>(storeMap.values().stream().map(MutablePair::getLeft).collect(Collectors.toList())); Page<DishAppStallPersonnelPageVo> pageList = baseMapper.getPageList(page, storeIds); if (CollUtil.isEmpty(pageList.getRecords())) { return DishAppStallPersonnelPageBaseVo.builder() .isStall(CollUtil.isEmpty(stallList) ? 0 : 1) .pageList(CultivatePage.coverPageList(page)) .build(); } // 设置档口人员列表 pageList.getRecords().forEach(pageListVo -> { pageListVo.setStallPersonnelList(JSON.parseArray(pageListVo.getStallPersonnelJson(), BaseAttributesJsonVo.class)); }); //处理每条数据的长度不一样，要处理成一样的，并且保证顺序一致 processStallPointData(stallList, pageList.getRecords(), storeMap); return DishAppStallPersonnelPageBaseVo.builder() .isStall(CollUtil.isEmpty(stallList) ? 0 : 1) .pageList(CultivatePage.coverPageList(pageList)) .build(); } public void export(HttpServletResponse response, DishAppStallPersonnelPageDto pageDto) { // 获取已绑定的收银门店集合 Map<String, MutablePair<String, StoreInfoListVO>> storeMap = getStoreIds(); // 获取获取系统最新档口数据集合(动态渲染列) List<BaseAttributesJsonVo> stallList = this.infoService.getAttributesList(List.of(), DishInfoTypeEnum.DISH_INFO_STALL.getCode()); // 校验是否有新增门店 this.checkStallPersonnelStore(stallList, storeMap); // 获取门店id集合 List<String> storeIds = StrUtil.isNotBlank(pageDto.getKeyword()) ? storeMap.entrySet().stream().filter(entry -> entry.getKey().contains(pageDto.getKeyword())).map(entry -> entry.getValue().getLeft()).collect(Collectors.toList()) : new ArrayList<>(storeMap.values().stream().map(MutablePair::getLeft).collect(Collectors.toList())); List<DishAppStallPersonnelPageVo> summaryVoList = this.baseMapper.getDataList(storeIds); if (CollUtil.isNotEmpty(summaryVoList)) { // 设置档口人员列表 summaryVoList.forEach(pageListVo -> { pageListVo.setStallPersonnelList(JSON.parseArray(pageListVo.getStallPersonnelJson(), BaseAttributesJsonVo.class)); }); } //处理每条数据的长度不一样，要处理成一样的，并且保证顺序一致 processStallPointData(stallList, summaryVoList, storeMap); //获取动态表头 List<List<String>> list = new ArrayList<>(); list.add(Collections.singletonList("门店名称")); List<List<String>> headers = EasyExcelUtil.getHeaders(stallList, list); //获取动态数据 List<List<Object>> dataList = EasyExcelUtil.getDataList(summaryVoList); EasyExcelUtil.rankingExportData(dataList, headers, "档口人员数据", "档口人员数据表", response); }
2.处理动态列数据
/** * 处理每条数据的长度不一样，要处理成一样的，并且保证顺序一致 * * @param attributesJsonVos 档口集合 * @param pageListVoList 待处理数据 * @param storeMap 门店集合 */ private void processStallPointData(List<BaseAttributesJsonVo> attributesJsonVos, List<DishAppStallPersonnelPageVo> pageListVoList, Map<String, MutablePair<String, StoreInfoListVO>> storeMap) { //获取所有技能点,并且按照sort字段排序 List<BaseAttributesJsonVo> sortedStallList = attributesJsonVos.stream().sorted(Comparator.comparing(BaseAttributesJsonVo::getSort)).collect(Collectors.toList()); LinkedHashMap<String, BaseAttributesJsonVo> skillCountVoMap = sortedStallList.stream().collect(Collectors.toMap(BaseAttributesJsonVo::getName, baseAttributesJsonVo -> baseAttributesJsonVo, (k1, k2) -> k1, LinkedHashMap::new)); Map<String, BaseAttributesJsonVo> skillCountMap; Map<String, StoreInfoListVO> storeInfoMap = storeMap.values().stream().collect(Collectors.toMap(MutablePair::getLeft, MutablePair::getRight)); for (DishAppStallPersonnelPageVo pageListVo : pageListVoList) { skillCountMap = pageListVo.getStallPersonnelList().stream().collect(Collectors.toMap(BaseAttributesJsonVo::getName, attributesJsonVo -> attributesJsonVo, (k1, k2) -> k1)); Map<String, BaseAttributesJsonVo> baseAttributesJsonVoMap = skillCountMap; List<BaseAttributesJsonVo> attributesJsonVoList = new LinkedList<>(); skillCountVoMap.forEach((name, skillCountVo) -> { if (baseAttributesJsonVoMap.containsKey(name)) { attributesJsonVoList.add(baseAttributesJsonVoMap.get(name)); } else { attributesJsonVoList.add(BaseAttributesJsonVo.builder() .id(skillCountVo.getId()) .sort(skillCountVo.getSort()) .name(skillCountVo.getName()) .value(null).build()); } }); pageListVo.setStallPersonnelList(attributesJsonVoList); pageListVo.setStoreName(storeInfoMap.get(pageListVo.getStoreId()).getName()); } }
3.导出工具类
package com.fantaibao.util; import cn.hutool.core.collection.CollUtil; import cn.hutool.http.Header; import cn.idev.excel.EasyExcel; import cn.idev.excel.util.ListUtils; import com.fantaibao.handler.CustomCellStyleHandler; import com.fantaibao.handler.CustomCellWriteHandler; import com.fantaibao.handler.CustomRowHeightHandler; import com.fantaibao.module.vo.appDish.BaseAttributesJsonVo; import com.fantaibao.module.vo.appDish.DishAppStallPersonnelPageVo; import org.apache.poi.ss.usermodel.*; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse; import java.io.InputStream; import java.net.URLEncoder; import java.nio.charset.StandardCharsets; import java.util.*; /** * easyExcel导入导出工具类 * @author yier */ public class EasyExcelUtil { /** * 获取动态表头 * @param stallList 数据集合 * @param list 已存在的动态表头 * @return 返回动态表头 */ public static List<List<String>> getHeaders(List<BaseAttributesJsonVo> stallList, List<List<String>> list) { if (CollUtil.isNotEmpty(stallList) && CollUtil.isNotEmpty(stallList)) { for (BaseAttributesJsonVo baseAttributesJsonVo : stallList) { List<String> head = new ArrayList<>(); head.add(baseAttributesJsonVo.getName()); list.add(head); } } return list; } /** * 获取表内容数据 * @param dataList 数据集合 * @return 表内容数据 */ public static List<List<Object>> getDataList(List<DishAppStallPersonnelPageVo> dataList) { List<List<Object>> list = ListUtils.newArrayList(); if (CollUtil.isNotEmpty(dataList)) { for (DishAppStallPersonnelPageVo rankingDTO : dataList) { List<Object> data = ListUtils.newArrayList(); data.add(rankingDTO.getStoreName()); if (CollUtil.isNotEmpty(rankingDTO.getStallPersonnelList())) { for (BaseAttributesJsonVo skillCountVo : rankingDTO.getStallPersonnelList()) { data.add(Objects.nonNull(skillCountVo.getValue()) ? skillCountVo.getValue() : ""); } } list.add(data); } } return list; } /** * 导出排行榜数据 * * @param list 导出数据 * @param headers 表头 * @param fileName 导出文件名称 * @param sheetName sheet名称 * @param response 响应对象 * @throws Exception 抛出异常 */ public static void rankingExportData(List<List<Object>> list, List<List<String>> headers, String fileName, String sheetName, HttpServletResponse response) throws Exception { EasyExcelUtil.export(response, fileName + ".xlsx", sheetName, list, headers); } /** * Excel导出功能(根据自定义表头) * * @param response 响应数据 * @param fileName 文件名 * @param sheetName sheet名 * @param list 数据列表 * @param headers 表头集合 */ public static void export(HttpServletResponse response, String fileName, String sheetName, List<List<Object>> list, List<List<String>> headers) throws Exception { ServletOutputStream outputStream = response.getOutputStream(); response.setCharacterEncoding("utf-8"); response.setHeader(Header.CONTENT_DISPOSITION.toString(), "attachment; filename=".concat(URLEncoder.encode(fileName, StandardCharsets.UTF_8))); response.setContentType("application/octet-stream"); EasyExcel.write(outputStream) .head(headers) .sheet(sheetName) .registerWriteHandler(new CustomRowHeightHandler()) .registerWriteHandler(new CustomCellStyleHandler()) .registerWriteHandler(new CustomCellWriteHandler()) .doWrite(list); } /** * 导入Excel，返回列表形式的数据 * * @param inputStream Excel文件流 * @return List<List < String>> 二维列表，第一行是表头，后续行是数据 */ public static List<List<String>> importExcel(InputStream inputStream) { List<List<String>> data = new ArrayList<>(); try { Workbook workbook = WorkbookFactory.create(inputStream); Sheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); List<String> rowData = new ArrayList<>(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); rowData.add(getCellStringValue(cell)); } data.add(rowData); } workbook.close(); } catch (Exception e) { throw new RuntimeException("导入Excel失败: " + e.getMessage(), e); } return data; } /** * 获取单元格的字符串值 */ private static String getCellStringValue(Cell cell) { if (cell == null) { return ""; } switch (cell.getCellType()) { case STRING: return cell.getStringCellValue().trim(); case NUMERIC: double value = cell.getNumericCellValue(); if (value == (long) value) { return String.valueOf((long) value); } else { return String.valueOf(value); } case BOOLEAN: return String.valueOf(cell.getBooleanCellValue()); case FORMULA: try { return cell.getStringCellValue(); } catch (Exception e) { return String.valueOf(cell.getNumericCellValue()); } default: return ""; } } /** * 将Excel数据转换为Map列表格式 */ public static List<Map<String, String>> convertToMapList(List<List<String>> excelData) { if (excelData == null || excelData.isEmpty()) { return new ArrayList<>(); } List<Map<String, String>> result = new ArrayList<>(); // 第一行是表头 List<String> headers = excelData.get(0); for (int i = 1; i < excelData.size(); i++) { List<String> rowData = excelData.get(i); Map<String, String> rowMap = new HashMap<>(); for (int j = 0; j < headers.size(); j++) { String header = headers.get(j); String value = j < rowData.size() ? rowData.get(j) : ""; rowMap.put(header, value); } result.add(rowMap); } return result; } }
