关于excel:导出zip

56次阅读

共计 3474 个字符,预计需要花费 9 分钟才能阅读完成。

管制层

@PostMapping("/questionListExport")
@ApiOperation(httpMethod = "POST",value = "",notes =" 列表导 ")
public WGJsonResult questionListExport(@RequestBody BackQuestionExport param, HttpServletResponse response) throws Exception {return backQuestionService.questionListExport(param,response);
}

服务层

@Override
public 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>

正文完
 0