管制层
@PostMapping("/questionListExport")@ApiOperation(httpMethod = "POST",value = "",notes = "列表导")public WGJsonResult questionListExport(@RequestBody BackQuestionExport param, HttpServletResponse response) throws Exception { return backQuestionService.questionListExport(param,response);}
服务层
@Overridepublic WGJsonResult questionListExport(BackQuestionExport param, HttpServletResponse response) throws IOException { PageHelper.startPage(param.getPageNo(),param.getPageSize()); List<BackQuestionExport> eports = backQuestionMapper.questionListExport(param); Map<String,List<BackQuestionExport>> dataMap = eports.stream().collect(Collectors.groupingBy(p->p.getInvitName()+"_"+p.getInvitId(), Collectors.toList())); exportExcel(response, dataMap); //导出之后记录日志 ask_exp_imp_record addLog(eports,1); return success("下载胜利");}private void exportExcel(HttpServletResponse response, Map<String, List<BackQuestionExport>> map) throws IOException { SimpleDateFormat sdf4 = new SimpleDateFormat("yyyy_MM_dd_hh_mm_ss"); String nowDate = sdf4.format(new Date()); String fileName = nowDate + ".zip"; // 获取我的项目根门路 String rootPath =System.getProperty("user.dir")+"_"+nowDate;// System.out.println("-----------"+System.getProperty("user.dir")); File f = new File(rootPath); if (!f.exists()) { f.mkdirs(); } String tempPath = ""; response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE); response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8")); ZipOutputStream zipOutputStream = new ZipOutputStream(new FileOutputStream(rootPath + File.separator + fileName)); try { for (Map.Entry<String, List<BackQuestionExport>> entry : map.entrySet()) { String k = entry.getKey(); List<BackQuestionExport> value = entry.getValue(); tempPath = rootPath + File.separator + k + ".xlsx"; EasyExcel.write(tempPath, BackQuestionExport.class).sheet("问答数据").doWrite(value); //创立输出流读取文件 BufferedInputStream bis = new BufferedInputStream(new FileInputStream(tempPath)); //将文件写入zip内，行将文件进行打包 zipOutputStream.putNextEntry(new ZipEntry(nowDate+File.separator+k + ".xlsx")); //写入文件的办法，同上 int size = 0; byte[] buffer = new byte[1024]; //设置读取数据缓存大小 while ((size = bis.read(buffer)) > 0) { zipOutputStream.write(buffer, 0, size); } bis.close(); } //敞开数据流，留神敞开的程序 zipOutputStream.close(); FileInputStream fis = new FileInputStream(rootPath + File.separator + fileName); BufferedInputStream buff = new BufferedInputStream(fis); byte[] b = new byte[1024];// 相当于咱们的缓存 int k = 0;// 该值用于计算以后理论下载了多少字节 OutputStream out = response.getOutputStream();// 从response对象中失去输入流,筹备下载 // 开始循环下载 while ((k = buff.read(b)) > 0) { out.write(b, 0, k); } out.close(); buff.close(); } catch (Exception e) { System.out.println(e.fillInStackTrace()); } finally { if (!f.exists()) return; if (f.isFile()) { f.delete(); return; } File[] files = f.listFiles(); for (int i = 0; i < files.length; i++) { files[i].delete(); } f.delete(); }}
sql
<select id="questionListExport" resultMap="UserBackQuestionExpMap"> SELECT * FROM ( SELECT ic.question_id, ic.id, ic.invit_id, ic.invit_name, DATE_FORMAT( ic.insert_time, '%Y-%m-%d %H:%i:%S' ) insert_time, ic.is_export, aq.question, aq.label_value, aq.lable_name, aq.`describe`, REPLACE ( uc.file_path, ',', ',\\n' ) file_path, IF(ak.answer IS NOT NULL,1,0) answerFlag FROM invit_contact ic LEFT JOIN ask_question aq ON ic.question_id = aq.id LEFT JOIN ( SELECT table_id, GROUP_CONCAT( file_path ) file_path FROM upload_change WHERE table_name = 'ask_question' GROUP BY table_id ) uc ON uc.table_id = ic.question_id LEFT JOIN (SELECT question_id, GROUP_CONCAT( ak.result SEPARATOR ',' )as answer FROM ask_question aq LEFT JOIN ask_answer ak ON aq.id = ak.question_id GROUP BY ak.question_id ) ak ON ic.question_id = ak.question_id WHERE aq.is_del = 0 )f <include refid="SearchSql"/> ORDER BY insert_time DESC</select>