当前位置： 首页 > ops >正文

EasyExcel 3.x 导出动态表头，动态sheet页

ops 2025/8/27 17:26:15

在这里插入图片描述

动态导出sheet页

 @Overridepublic void exportAnswerListV1(HttpServletResponse response, SmtSurveyUserAnswerRecord smtSurveyUserAnswerRecord) {// 1. 准备问卷数据String formType = smtSurveyUserAnswerRecord.getFormType();if (ObjectUtil.isEmpty(formType)) {throw new BizErrorException("问卷类型不能为空");}SmtSurveyForm surveyForm = getFromByType(formType);if (ObjectUtil.isEmpty(surveyForm)) {throw new BizErrorException("问卷不能为空");}smtSurveyUserAnswerRecord.setFormId(surveyForm.getId());// 2. 准备用户回答记录数据List<SmtSurveyUserAnswerRecord> recordList = batchQueryUserAnswerRecordList(smtSurveyUserAnswerRecord);if (CollectionUtil.isEmpty(recordList)) {throw new BizErrorException("没有查询到数据");}log.info("exportAnswerListV1:recordList:size=>{}",recordList.size());List<String> uuidList = recordList.stream().map(SmtSurveyUserAnswerRecord::getUuid).collect(Collectors.toList());Map<String, List<SmtSurveyUserAnswer>> userAnswerMap = batchQueryUserAnswerList(uuidList);OutputStream outputStream = null;ExcelWriter excelWriter = null;try {// 设置响应头信息response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");// 处理中文文件名String fileName = URLEncoder.encode("问卷明细导出", StandardCharsets.UTF_8.name()).replaceAll("\\+", "%20");response.setHeader("Content-disposition","attachment;filename*=utf-8''" + fileName + ".xlsx");outputStream = response.getOutputStream();// 初始化ExcelWriterexcelWriter = EasyExcel.write(outputStream)//动态设置列宽.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();// 第一个sheet - 基本信息WriteSheet basicInfoSheet = EasyExcel.writerSheet(0, "基础数据").head(getBasicDataHead(surveyForm)) // 基本信息表头.build();// 写入第一个sheet的数据excelWriter.write(getBasicData(surveyForm, recordList, userAnswerMap), basicInfoSheet);List<SmtSurveyUserAnswerStatisticsDimension> dimensionList = getStatisticsDimensionListByForm(surveyForm);if (CollectionUtil.isNotEmpty(dimensionList)){ArrayList<SmtSurveyUserAnswerStatisticsDimension> deptDimensionList = dimensionList.stream().collect(Collectors.collectingAndThen(Collectors.toCollection(() -> new TreeSet<>(Comparator.comparing(SmtSurveyUserAnswerStatisticsDimension::getDeptDimension))), ArrayList::new));for (int i = 0; i < deptDimensionList.size(); i++) {SmtSurveyUserAnswerStatisticsDimension deptDimensionObj = deptDimensionList.get(i);String deptDimension = deptDimensionObj.getDeptDimension();//写表头WriteSheet answerDetailSheet = EasyExcel.writerSheet(1 + i + 1, deptDimension).head(getDeptDimensionHead(surveyForm, deptDimension, dimensionList)).build();//写数据excelWriter.write(getDeptDimensionData(surveyForm, recordList, userAnswerMap, deptDimension,dimensionList), answerDetailSheet);}}} catch (IOException e) {e.printStackTrace();response.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);} finally {// 关闭资源if (excelWriter != null) {excelWriter.finish();}if (outputStream != null) {try {outputStream.flush();outputStream.close();} catch (IOException e) {e.printStackTrace();}}}}

分批查询数据

public List<SmtSurveyUserAnswerRecord> batchQueryUserAnswerRecordList(SmtSurveyUserAnswerRecord smtSurveyUserAnswerRecord) {LambdaQueryWrapper<SmtSurveyUserAnswerRecord> queryWrapper = new LambdaQueryWrapper<>();queryWrapper.eq(SmtSurveyUserAnswerRecord::getFormType, smtSurveyUserAnswerRecord.getFormType());queryWrapper.eq(SmtSurveyUserAnswerRecord::getFormId, smtSurveyUserAnswerRecord.getFormId());if (ObjectUtil.isNotEmpty(smtSurveyUserAnswerRecord.getState())) {queryWrapper.eq(SmtSurveyUserAnswerRecord::getState, smtSurveyUserAnswerRecord.getState());}if (ObjectUtil.isNotEmpty(smtSurveyUserAnswerRecord.getFormName())) {queryWrapper.like(SmtSurveyUserAnswerRecord::getFormName, smtSurveyUserAnswerRecord.getFormName());}//时间维度if (ObjectUtil.isNotEmpty(smtSurveyUserAnswerRecord.getStartTime())) {queryWrapper.gt(SmtSurveyUserAnswerRecord::getCreateTime, smtSurveyUserAnswerRecord.getStartTime());}if (ObjectUtil.isNotEmpty(smtSurveyUserAnswerRecord.getEndTime())) {queryWrapper.le(SmtSurveyUserAnswerRecord::getCreateTime, smtSurveyUserAnswerRecord.getEndTime());}if (ObjectUtil.isNotEmpty(smtSurveyUserAnswerRecord.getStartDate())) {queryWrapper.gt(SmtSurveyUserAnswerRecord::getCreateTime, smtSurveyUserAnswerRecord.getStartDate());}if (ObjectUtil.isNotEmpty(smtSurveyUserAnswerRecord.getEndDate())) {queryWrapper.le(SmtSurveyUserAnswerRecord::getCreateTime, smtSurveyUserAnswerRecord.getEndDate());}//科室维度if (ObjectUtil.isNotEmpty(smtSurveyUserAnswerRecord.getDeptCode())) {queryWrapper.eq(SmtSurveyUserAnswerRecord::getDeptCode, smtSurveyUserAnswerRecord.getDeptCode());}if (ObjectUtil.isNotEmpty(smtSurveyUserAnswerRecord.getDeptName())) {queryWrapper.like(SmtSurveyUserAnswerRecord::getDeptName, smtSurveyUserAnswerRecord.getDeptName());}//医生维度if (ObjectUtil.isNotEmpty(smtSurveyUserAnswerRecord.getDoctorCode())) {queryWrapper.eq(SmtSurveyUserAnswerRecord::getDoctorCode, smtSurveyUserAnswerRecord.getDoctorCode());}if (ObjectUtil.isNotEmpty(smtSurveyUserAnswerRecord.getDoctorName())) {queryWrapper.like(SmtSurveyUserAnswerRecord::getDoctorName, smtSurveyUserAnswerRecord.getDoctorName());}return smtSurveyUserAnswerRecordService.list(queryWrapper);}/*** 分批查询用户答案*/public Map<String, List<SmtSurveyUserAnswer>> batchQueryUserAnswerList(List<String> uuidList) {int batchSize = 500; // 默认批次大小int totalSize = uuidList.size();// 初始化容量提升性能List<SmtSurveyUserAnswer> allResults = new ArrayList<>(totalSize);// 分批处理查询for (int i = 0; i < totalSize; i += batchSize) {int batchNumber = (i / batchSize) + 1;int endIndex = Math.min(i + batchSize, totalSize);List<String> batchUuids = uuidList.subList(i, endIndex);// 构建查询条件LambdaQueryWrapper<SmtSurveyUserAnswer> queryWrapper = new LambdaQueryWrapper<>();queryWrapper.in(SmtSurveyUserAnswer::getUuid, batchUuids);// 执行查询并添加结果List<SmtSurveyUserAnswer> batchResults = smtSurveyUserAnswerService.list(queryWrapper);log.info("批量查询用户回答结果，第{}次查询，总批次大小{}，查询到的记录数为{}", batchNumber, totalSize, batchResults.size());if (CollectionUtil.isNotEmpty(batchResults)) {allResults.addAll(batchResults);}}// 按uuid分组Map<String, List<SmtSurveyUserAnswer>> map = allResults.stream().collect(Collectors.groupingBy(SmtSurveyUserAnswer::getUuid));return map;}

动态表头和动态数据

 /*** 获取基本信息sheet的表头（带合并单元格的多级表头）*/private List<List<String>> getBasicDataHead(SmtSurveyForm surveyForm) {// 实际业务的表头定义，外层List代表行，内层List代表列，利用文本相同合并特性List<List<String>> head = new ArrayList<>();head.add(Arrays.asList("序号", "序号"));head.add(Arrays.asList("部门", "部门"));head.add(Arrays.asList("医生", "医生"));JSONObject jsonObject = JSONObject.parseObject(surveyForm.getContent());JSONObject properties = jsonObject.getJSONObject("schema").getJSONObject("properties");List<String> sortedKeys = properties.keySet().stream().sorted(Comparator.comparingInt(key -> properties.getJSONObject(key).getIntValue("x-index"))).collect(Collectors.toList());//动态生成标题for (String sortedKey : sortedKeys) {JSONObject questionJSONObject = properties.getJSONObject(sortedKey);String title = questionJSONObject.getString("title");String questionType = questionJSONObject.getString("x-component");//主观题，没有分数if (StrUtil.equals("Input", questionType) || StrUtil.equals("Input.TextArea", questionType)) {head.add(Arrays.asList(title, "内容"));} else {head.add(Arrays.asList(title, "选项"));head.add(Arrays.asList(title, "分数"));}}head.add(Arrays.asList("提交时间", "提交时间"));head.add(Arrays.asList("问卷总分", "问卷总分"));head.add(Arrays.asList("用户回答分数", "用户回答分数"));head.add(Arrays.asList("用户满意度", "用户满意度"));return head;}/*** 用户填写基本信息*/private List<List<String>> getBasicData(SmtSurveyForm surveyForm, List<SmtSurveyUserAnswerRecord> recordList, Map<String, List<SmtSurveyUserAnswer>> userAnswerMap) {JSONObject jsonObject = JSONObject.parseObject(surveyForm.getContent());JSONObject properties = jsonObject.getJSONObject("schema").getJSONObject("properties");List<String> sortedKeys = properties.keySet().stream().sorted(Comparator.comparingInt(key -> properties.getJSONObject(key).getIntValue("x-index"))).collect(Collectors.toList());// 问卷总分int surveyFormTotalScore = getSurveyFormTotalScore(sortedKeys, properties);List<List<String>> list = new ArrayList<>();for (SmtSurveyUserAnswerRecord record : recordList) {List<String> row = new ArrayList<>();row.add(recordList.indexOf(record) + 1 + "");row.add(record.getDeptName());row.add(record.getDoctorName());List<SmtSurveyUserAnswer> userAnswerList = userAnswerMap.get(record.getUuid());int surveyFormUserScore = 0;if (CollectionUtil.isEmpty(userAnswerList)) {for (String sortedKey : sortedKeys) {JSONObject questionJSONObject = properties.getJSONObject(sortedKey);String questionType = questionJSONObject.getString("x-component");fillExcelCellDefaultByQuestionType(questionType, row);}} else {surveyFormUserScore = getSurveyFormUserAnswerScoreByUser(properties, record, userAnswerList);Map<String, SmtSurveyUserAnswer> userAnswerMapByQuestionId = userAnswerList.stream().collect(Collectors.toMap(SmtSurveyUserAnswer::getQuestionId, o -> o));for (String sortedKey : sortedKeys) {JSONObject questionJSONObject = properties.getJSONObject(sortedKey);String questionType = questionJSONObject.getString("x-component");SmtSurveyUserAnswer userAnswer = userAnswerMapByQuestionId.get(sortedKey);//该题没有找到用户填写记录，默认填空，为了和表头对齐if (ObjectUtil.isEmpty(userAnswer)) {fillExcelCellDefaultByQuestionType(questionType, row);} else {//主观题，没有分数if (StrUtil.equals("Input", questionType) || StrUtil.equals("Input.TextArea", questionType)) {row.add(userAnswer.getChoiceName());} else {row.add(userAnswer.getChoiceName());row.add(userAnswer.getChoiceScore());}}}}String formattedDateTime = DateUtil.format(record.getCreateTime(), "yyyy-MM-dd HH:mm:ss");row.add(formattedDateTime);row.add(StrUtil.toString(surveyFormTotalScore));row.add(StrUtil.toString(surveyFormUserScore));String userSatisfaction = safePercent(surveyFormUserScore, surveyFormTotalScore, 2);row.add(userSatisfaction);list.add(row);}// 根据实际业务获取数据return list;}
http://www.xdnf.cn/news/18789.html

相关文章：

  • Rust：函数与控制流
  • 《Java反射与动态代理详解：从原理到实践》
  • 【Ansible】Ansible部署K8s集群--准备环境--配置网络
  • PEFT 模型解析（59）
  • 《数据之心》——鱼小妖的觉醒
  • ctfshow_萌新web16-web20-----文件包含日志注入
  • 《信息检索与论文写作》实验报告二 引文索引数据库检索
  • 我们来学mysql -- safe启动
  • 解析xml文件并录入数据库
  • 类似ant design和element ui的八大Vue的UI框架详解优雅草卓伊凡
  • Vue中的scoped属性
  • 推荐系统王树森（三）粗排精排
  • 【NER学习笔记】：基于AdaSeq的NER模型训练笔记
  • Linux下TCPT通信
  • 8.26 支持向量机
  • 什么样的 IP 能穿越周期，持续被用户买单？​
  • 基于大模型的智能占卜系统实战-Qwen-VL、RAG、FastAPI
  • “喵汪联盟”宠物领养系统的设计与实现（代码+数据库+LW）
  • Python编程快速上手—让繁琐工作自动化
  • OpenCV打开视频函数VideoCapture使用详解
  • 数据与端点安全 (Protect data and apps)
  • 【学习笔记】系统时间跳变会影响time接口解决措施
  • Matlab使用——开发上位机APP，通过串口显示来自单片机的电压电流曲线，实现光伏I-V特性监测的设计
  • es-toolkit 是一个现代的 JavaScript 实用库
  • UE4生成Target文件
  • 【RAGFlow代码详解-11】知识库管理
  • 无人机倾斜摄影农田航线规划
  • ProfiNet 转 Ethernet/IP基于西门子 S7 - 1500 与罗克韦尔 PLC 的汽车零部件加工线协同案例
  • 【QT学习之路】-Qt入门
  • 解决Windows更新后WPF程序报TypeLoadException异常的问题