@ApiOperation(value = "导出埋点到excel")
@GetMapping("getReportMid")
public void getVideoMid5(HttpServletResponse response,String courseId) throws IOException {
List<XXTMDModel> list = iCrawingVideoQuestionXXTService.getVideoQuestion1(courseId);
int allQuestionSize= list.size();
List<NewExcelModel> listExcelModel = new ArrayList<>();
// for(XXTMDModel xxtmdModel : list){
// NewExcelModel newExcelModel = new NewExcelModel();
// String questionType = xxtmdModel.getQuestionType();
// String description = xxtmdModel.getDescription();
// newExcelModel.setQuestionType(questionType);
// newExcelModel.setDescription(description);
// for(XXTMDOptions xxtmdOptions : xxtmdModel.getOptionsList()){
// String name = xxtmdOptions.getName();
// String descriptionOption = xxtmdOptions.getDescription();
// newExcelModel.setOptionName(name);
// newExcelModel.setDescription(descriptionOption);
// listExcelModel.add(newExcelModel);
// }
//
// }
for(XXTMDModel xxtmdModel : list){
for(XXTMDOptions xxtmdOptions : xxtmdModel.getOptionsList()){
NewExcelModel newExcelModel = new NewExcelModel();
String questionType = xxtmdModel.getQuestionType();
String description = xxtmdModel.getDescription();
newExcelModel.setQuestionType(questionType);
newExcelModel.setDescription(description);
String name = xxtmdOptions.getName();
String descriptionOption = xxtmdOptions.getDescription();
newExcelModel.setOptionName(name);
newExcelModel.setOptionDescription(descriptionOption);
listExcelModel.add(newExcelModel);
}
System.out.println("这次我想看到的容量 "+listExcelModel.size());
System.out.println("这次我想看到的内容 "+listExcelModel);
}
batchExport(allQuestionSize,response,listExcelModel);
}
List<XXTMDModel> getVideoQuestion1(String courseId) throws IOException;
//获取爬取的list
@Override
public List<XXTMDModel> getVideoQuestion1(String courseId) throws IOException {
List<String> list = getMId(courseId);
List<XXTModel> list2 = new ArrayList<>();
List<XXTMDModel> listMD = new ArrayList<>();
for(String strMid : list){
XXTVideoQuestionModel videoQuestionModel = new XXTVideoQuestionModel();
videoQuestionModel.setMid(strMid);
// 发起http请求获取请求数据
String videoQuestionBody = HttpUtils.sendHttpGet(videoQuestionModel.getCookie(), chaoXingConfigurationUtils.getVideoQuestion(videoQuestionModel.getMid(), videoQuestionModel.getCpi(), videoQuestionModel.getClassid()));
//小心覆盖的问题
list2 = JSON.parseArray(videoQuestionBody, XXTModel.class);
for(XXTModel xxtModel : list2){
for(XXTQuestionModel xxtQuestionModel : xxtModel.getDatas()){
XXTMDModel xxtmdModel = new XXTMDModel();
String questionType = xxtQuestionModel.getQuestionType();
String desciription = xxtQuestionModel.getDescription();
xxtmdModel.setQuestionType(questionType);
xxtmdModel.setDescription(desciription);
List<XXTMDOptions> list4 = new ArrayList<>();
for (OptionModel optionModel : xxtQuestionModel.getOptions()){
String name = optionModel.getName();
String description = optionModel.getDescription();
XXTMDOptions xxtmdOptions = new XXTMDOptions();
xxtmdOptions.setName(name);
xxtmdOptions.setDescription(description);
list4.add(xxtmdOptions);
xxtmdModel.setOptionsList(list4);
}
listMD.add(xxtmdModel);
}
}
}
return listMD;
}
//导出excel
public Object batchExport(int allQuestionSize,HttpServletResponse response,List<NewExcelModel> mdList) throws IOException {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("视频埋点问题统计", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
//需要合并的列
int[] mergeColumeIndex = {0,1};
//从第二行后开始合并
int mergeRowIndex = 2;
//设置动态标题
List<List<String>> headers = this.getHeaders("所有问题数量："+allQuestionSize+"个");
// 调用合并单元格工具类，此工具类是根据工程名称相同则合并后面数据
ExcelMergeUtil excelFillCellMergeStrategy = new ExcelMergeUtil(mergeRowIndex,mergeColumeIndex);
// 这里需要设置不关闭流
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//设置背景颜色
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
//设置头字体
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short)13);
headWriteFont.setBold(true);
headWriteCellStyle.setWriteFont(headWriteFont);
//设置头居中
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//内容策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
//设置 水平居中
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 垂直居中
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
HorizontalCellStyleStrategy horizontalCellStyleStrategy =
new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
EasyExcel.write(response.getOutputStream(), NewExcelModel.class)
.head(headers)
.registerWriteHandler(horizontalCellStyleStrategy)
.registerWriteHandler(excelFillCellMergeStrategy)
// 导出文件名
.autoCloseStream(Boolean.TRUE).sheet("sheet")
.doWrite(mdList);
return null;
}
@ApiOperation(value = "获取所有埋点")
@GetMapping("getVideoMid")
public FrontResult getVideoMid(String courseId) throws IOException {
List<XXTMDModel> list = iCrawingVideoQuestionXXTService.getVideoQuestion1(courseId);
// iCrawingVideoQuestionXXTService.excludeOrIncludeWrite(list);
if (list != null) {
return FrontResult.getSuccessResult(ResultMsgEnum.FIND_SUCCESS.getMsg(), list);
}
return FrontResult.getExceptionResult(ResultCodeEnum.FAIL.getCode(), ResultMsgEnum.FIND_FAIL.getMsg());
}
package com.example.crawlingcoursebackend.model.CrawlingXXT;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentLoopMerge;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.Data;
import lombok.EqualsAndHashCode;
import java.util.List;
@Data
@EqualsAndHashCode
@ContentRowHeight(20)
@HeadRowHeight(20)
@ColumnWidth(10)
public class XXTMDModel {
@ExcelProperty(value = "题型",index = 0)
private String questionType;
@ColumnWidth(100)
@ExcelProperty(value = "题干",index = 1)
private String description;
@ExcelProperty(value = "选项",index = 2)
@ColumnWidth(40)
private List<XXTMDOptions> optionsList;
}
package com.example.crawlingcoursebackend.model.CrawlingXXT;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Data;
@Data
public class NewExcelModel {
private String questionType;
@ColumnWidth(100)
private String description;
private String optionName;
private String optionDescription;
}
