EasyExcel用模版导出动态表格

在工作中有这么一个需求就是导出一个表格，上面有学生的信息下面有一个表格记录学生每科的成绩，要导出这样一个表格我们要怎么做呢？其实很简单，可以用导出模版做到，Easyexcel已经有实现方法了，下面我们来一步一步的实现这个需求。

一、环境准备

引入依赖

<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>easyexcel</artifactId>
	<version>3.3.2</version>
</dependency>

准备模版

模版有点丑哈，但是不重要，自己想怎么调就怎么调。但是呢就是要注意列表的那边是有一个.的。

二、代码编写

  void templateExportTest() throws IOException {
        HashMap<String,Object> map=new HashMap<>();
        map.put("studentName","小温");
        map.put("gender", "男");
        map.put("opid",1234554);
        map.put("semester","2024-2023-1");
        map.put("className","计算机一班");
        List<StudentCourseDTO> exportList=new ArrayList<>();
        for (int i=1;i<2;i++){
           exportList.add(StudentCourseDTO.builder().index(i).courseName("物理")
                   .credits("4").normalScore("4").courseCredits("4")
                   .normalScoreGpa("4")
                   .normalSemester("2024-2023-1").build());
        }

        String fileName ="D:/export/导出数据. "+ ".xlsx";
        InputStream is = this.getClass().getClassLoader().getResourceAsStream("导出模版.xls");
        //设置创建行的方式
        FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
        ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(is).excelType(ExcelTypeEnum.XLS).build();
        //写入到sheet
        WriteSheet oneSheet = EasyExcel.writerSheet(0).build();
        //填充
        excelWriter.fill(map, oneSheet);
        excelWriter.fill(exportList, fillConfig, oneSheet);
        excelWriter.finish();

        assert is != null;
        is.close();
 	}

三、具体实现

public void exportStudentCourseDetail(AnalyseStudentScoresPagingDTO dto,
										  HttpServletResponse response) {
		StudentInfoDTO studentInfo = subjectDao.getStudentInfo(dto.getSubjectId());
		AtomicInteger index= new AtomicInteger();
		this.setStudentCode(dto);
		// 获得所有原始记录信息
		List<StudentScoresCourseDetailDTO> detailList = analyseStudentScoresSemesterDao.pagingCourseDetail(dto);
		// 转换为导出对象
		List<ExportScoresCourseDetailDTO> exportList = detailList.stream().map(item -> {
			ExportScoresCourseDetailDTO exportDto = new ExportScoresCourseDetailDTO();
			exportDto.setIndex(index.incrementAndGet());
			exportDto.setIsPass(ScoreDef.IsPass.from(item.getIsPass()).getName());
			BeanUtils.copyProperties(item, exportDto);
			return exportDto;
		}).collect(Collectors.toList());
		String fileName = UUID.randomUUID() + ".xls";
		// 拼装表头上面的数据
		HashMap<String, Object> map = new HashMap<>();
		map.put("studentName",studentInfo.getStudentName());
		map.put("gender", SubjectDef.Gender.fromCode(studentInfo.getGender()).getName());
		map.put("opid",studentInfo.getOpid());
		map.put("semester",dto.getSemester());
		map.put("className",studentInfo.getGradeName()+"/"+studentInfo.getInstituteName()+"/"+studentInfo.getMajorName()+"/"+studentInfo.getClassName());
		// 从阿里云导出
		try {
			InputStream is = this.getClass().getClassLoader().getResourceAsStream("templates/"+"学生成绩明细信息导出模版.xls");
//			InputStream is = ossFileService.download(ossProperties, "学生成绩明细信息导出模版.xls", 1);
			EasyExcelUtils.templateWrite(fileName,exportList,is,map,response);
		}catch (Exception e){
			throw new BadRequestException("下载模版文件失败");
		}
		// 发送消息
		String userName = SecurityUtils.getUser().getName();
		String newFileName = "学生成绩明细信息"+UUID.randomUUID() + ".xls";
		remoteBigDataExportDangerMsgService.sendBigDataExportDanger(userName, newFileName, SecurityConstants.FROM_IN);
	}

这里因为是要与前端交互，返回一个把我们导出的文件写到response，最后我封装了一下代码这样方便以后调用。

public class EasyExcelUtils {
	private EasyExcelUtils() {
	}
	public static <T> void templateWrite(String fileName, List<T> exportList, InputStream is, HashMap<String,Object> map, HttpServletResponse response){
		try {
			OutputStream os = getOutputStream(fileName,response);
			//设置创建行的方式
			FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
			ExcelWriter excelWriter = EasyExcel.write(os).withTemplate(is).excelType(ExcelTypeEnum.XLS).build();
			//写入到sheet
			WriteSheet oneSheet = EasyExcel.writerSheet(0).build();
			//填充
			excelWriter.fill(map, oneSheet);
			excelWriter.fill(exportList, fillConfig, oneSheet);
			excelWriter.finish();
			os.close();
			is.close();
		}catch (Exception e){
			String msg = "export occur error";
			throw new BadRequestException(msg);
		}
	}
	private static OutputStream getOutputStream(String fileName,
												HttpServletResponse response) throws Exception {
			fileName = URLEncoder.encode(fileName, "UTF-8");
			response.setContentType("application/vnd.ms-excel");
			response.setCharacterEncoding("utf8");
			response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xls");
			response.setHeader("Pragma", "public");
			response.setHeader("Cache-Control", "no-store");
			response.addHeader("Cache-Control", "max-age=0");
			return response.getOutputStream();
	}
}

我的实现过程大概就是这样了，写的代码不是很规范，希望能得到大家的指导。