导入jar
<!-- Hutool超级工具类 http://hutool.mydoc.io/ -->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.4.6</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
//获取list
List<VideoLearnLog> videoLearnLogs= VideoLearnLog.dao.
template("videoLearnLog.find", select).find();
//excel
String fileUuid = UuidKit.getUUIDNoMinus();
//项目路径 给随机文件名 打jar用
String path = (PathKit.getWebRootPath() + "/"+fileUuid+"/model/").replace("\\", "/");
//excel第一行
String[] headers = {"用户", "视频", "方案", "观看秒数", "学习时间","状态"};
Collection coll = new ArrayList();
//放入
List<String> row = CollUtil.newArrayList(headers);
coll.add(row);
//循环
for (int i = 0; i < videoLearnLogs.size(); i++) {
Integer total_learn_second = busEducationVideoLearnLogs.get(i).get("total_learn_second");
String total_learn_secondStr=String.valueOf(total_learn_second);
String[] colums ={videoLearnLogs.get(i).get("uname"),videoLearnLogs.get(i).get("vtitle") ,
videoLearnLogs.get(i).get("epname"),
total_learn_secondStr,
videoLearnLogs.get(i).getStudyTime(),videoLearnLogs.get(i).get("state_name")};
//存入
List<String> row2 = CollUtil.newArrayList(colums);
coll.add(row2);
//将图片下载path目录下 img为oss图片下载地址
String img= downloadImage(path,videoLearnLogs.get(i).getImg(), videoLearnLogs.get(i).getName(), fileUuid);
}
//生成excel
String excelName="视频记录";
//headers数
Integer count=6;
downloadExcel(path,excelName,count,coll);
//当前目录下 压缩成jar
File file = ZipUtil.zip(PathKit.getWebRootPath() + "/"+fileUuid+"/model");
//给前端
renderFile(file);
//图片下载 img为oss图片下载地址
public static String downloadImage(String path,String img, String name, String fileUuid) {
try {
URL httpurl = new URL(img);
String newName = null;
File f = new File(path + newName);
FileUtils.copyURLToFile(httpurl, f);
return path + newName;
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
//excel下载
public static void downloadExcel(String path, String excelName, Integer count, Collection coll) {
List<List<String>> rows = CollUtil.newArrayList(coll);
ExcelWriter writer = ExcelUtil.getWriter(path + excelName + ".xlsx");
// 合并单元格后的标题行,使用默认标题样式
writer.merge(count - 1, excelName);
// 一次性写出内容,使用默认样式,强制输出标题
writer.write(rows, true);
// 关闭writer,释放内存
writer.close();
}