做个记录,试了很多方法都不行(本来想使用对象单独写一个table,但是没找到属性),又不想更换依赖,后面才看到的模板填充,能用............
//依赖<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>${easypoi.version}</version></dependency>
控制层
//Controller层@ApiOperation(value = "导出", notes = "导出")@GetMapping("/export/{projectId}")public void exportByProjectId(@PathVariable String projectId,HttpServletResponse response) {projectOrderService.exportByProjectId(projectId,response);}
添加一个模板
代码( projectOrderExcelVO 是一个对象,他里面带了一个list )
service层
//service层public void exportByProjectId(String projectId, HttpServletResponse response) {//查询出来的对象ProjectOrderExcelVO projectOrderExcelVO = getProjectOrderExcelVO(projectId);//使用模板//获取模板ClassPathResource classPathResource = new ClassPathResource("/templates/专案执行单.xlsx");//输入流InputStream inputStream = null;//输出流ServletOutputStream outputStream = null;//Excel对象ExcelWriter excelWriter = null;try {//输入流inputStream = classPathResource.getInputStream();// 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postmanresponse.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");outputStream = response.getOutputStream();//设置输出流和模板信息excelWriter = EasyExcel.write(outputStream).withTemplate(inputStream).build();WriteSheet writeSheet = EasyExcel.writerSheet().build();//开启自动换行,自动换行表示每次写入一条list数据是都会重新生成一行空行,此选项默认是关闭的,需要提前设置为trueFillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();//列表excelWriter.fill(projectOrderExcelVO.getProjectOrderContentExcelList(), fillConfig, writeSheet);//对象excelWriter.fill(projectOrderExcelVO, writeSheet);excelWriter.finish();} catch (Exception e) {e.printStackTrace();}finally {// 千万别忘记finish 会帮忙关闭流if (excelWriter != null) {excelWriter.finish();}//关闭流if (outputStream != null) {try {outputStream.close();} catch (IOException e) {e.printStackTrace();}}if (inputStream != null) {try {inputStream.close();} catch (IOException e) {e.printStackTrace();}}}}
在模板上添加 单个对象 {属性名} 或 多条数据 {.属性名} (可以使用map)
中途遇到的bug
excelgenerateexception create workbook failure //没有图片了,就这一句话或是往下滑
Unexpected record signature: 0X9 //这一句
可以先看下target的classes文件下有没有把模板编译进去,没有的话就重新编译(pom.xml文件下得有这个配置)
<build><resources><resource><directory>src/main/resources</directory><filtering>true</filtering><excludes><exclude>**/*.xlsx</exclude><exclude>**/*.xls</exclude></excludes></resource><resource><directory>src/main/resources</directory><filtering>false</filtering><includes><include>**/*.xlsx</include><include>**/*.xls</include></includes></resource></resources></build>
成功的效果图
写的初版
//service层public void exportByProjectId(String projectId, HttpServletResponse response) {//查询出来的对象ProjectOrderExcelVO projectOrderExcelVO = getProjectOrderExcelVO(projectId);List<ProjectOrderExcelVO> projectOrderExcelList = new ArrayList<>();projectOrderExcelList.add(projectOrderExcelVO);//初版try {// 方法1 如果写到同一个sheetString fileName = "tableWrite" + System.currentTimeMillis() + ".xlsx";// 这里直接写多个table的案例了，如果只有一个 也可以直一行代码搞定，参照其他案例ExcelWriter excelWriter = null;// 头的策略WriteCellStyle headWriteCellStyle = new WriteCellStyle();// 背景色headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());//内容的策略WriteCellStyle contentWriteCellStyle = new WriteCellStyle();// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);//设置 自动换行//contentWriteCellStyle.setWrapped(true);//设置 垂直居中contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置 水平居中contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);//设置边框样式contentWriteCellStyle.setBorderLeft(THIN);contentWriteCellStyle.setBorderTop(THIN);contentWriteCellStyle.setBorderRight(THIN);contentWriteCellStyle.setBorderBottom(THIN);HorizontalCellStyleStrategy horizontalCellStyleStrategy =new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);try {// 这里不指定classexcelWriter = EasyExcel.write(fileName).build();// 把sheet设置为不需要头 不然会输出sheet的头 这样看起来第一个table 就有2个头了WriteSheet writeSheet = EasyExcel.writerSheet("专案执行单").needHead(Boolean.FALSE).registerWriteHandler(horizontalCellStyleStrategy).build();// 这里必须指定需要头，table 会继承sheet的配置，sheet配置了不需要，table 默认也是不需要WriteTable writeTable0 = EasyExcel.writerTable(0).head(ProjectOrderExcelVO.class).needHead(Boolean.TRUE).build();// 第二个对象 读取对象的excel实体类中的标题WriteTable writeTable1 = EasyExcel.writerTable(1).head(ProjectOrderContentExcelVO.class).needHead(Boolean.TRUE).relativeHeadRowIndex(1).build();// 第一次写入会创建头excelWriter.write(projectOrderExcelList, writeSheet, writeTable0);// 第二次写如也会创建头，然后在第一次的后面写入数据excelWriter.write(projectOrderExcelVO.getProjectOrderContentExcelList(), writeSheet, writeTable1);} finally {// 千万别忘记finish 会帮忙关闭流if (excelWriter != null) {excelWriter.finish();}}} catch (Exception e) {e.printStackTrace();}}
半成品 实现两个列表(table)
//service层public void exportByProjectId(String projectId, HttpServletResponse response) {//查询出来的对象ProjectOrderExcelVO projectOrderExcelVO = getProjectOrderExcelVO(projectId);List<ProjectOrderExcelVO> projectOrderExcelList = new ArrayList<>();projectOrderExcelList.add(projectOrderExcelVO);//半成品 没有实现单个列折叠// 头的策略WriteCellStyle headWriteCellStyle = new WriteCellStyle();// 背景色headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());//内容的策略WriteCellStyle contentWriteCellStyle = new WriteCellStyle();// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);//设置 自动换行contentWriteCellStyle.setWrapped(true);//设置 垂直居中contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置 水平居中contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);//设置边框样式contentWriteCellStyle.setBorderLeft(THIN);contentWriteCellStyle.setBorderTop(THIN);contentWriteCellStyle.setBorderRight(THIN);contentWriteCellStyle.setBorderBottom(THIN);HorizontalCellStyleStrategy horizontalCellStyleStrategy =new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);ServletOutputStream outputStream = null;// 这里直接写多个table的案例了，如果只有一个 也可以直一行代码搞定，参照其他案例ExcelWriter excelWriter = null;try {// 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postmanresponse.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");outputStream = response.getOutputStream();// 这里不指定classexcelWriter = EasyExcel.write(outputStream).build();// 把sheet设置为不需要头 不然会输出sheet的头 这样看起来第一个table 就有2个头了WriteSheet writeSheet = EasyExcel.writerSheet("专案执行单").needHead(Boolean.FALSE).registerWriteHandler(horizontalCellStyleStrategy).build();// 这里必须指定需要头，table 会继承sheet的配置，sheet配置了不需要，table 默认也是不需要WriteTable writeTable0 = EasyExcel.writerTable(0).head(ProjectOrderExcelVO.class).needHead(Boolean.TRUE).build();// 第二个对象 读取对象的excel实体类中的标题WriteTable writeTable1 = EasyExcel.writerTable(1).head(ProjectOrderContentExcelVO.class).needHead(Boolean.TRUE).relativeHeadRowIndex(1).build();// 第一次写入会创建头excelWriter.write(projectOrderExcelList, writeSheet, writeTable0);// 第二次写如也会创建头，然后在第一次的后面写入数据excelWriter.write(projectOrderExcelVO.getProjectOrderContentExcelList(), writeSheet, writeTable1);} catch (Exception e) {e.printStackTrace();} finally {// 千万别忘记finish 会帮忙关闭流if (excelWriter != null) {excelWriter.finish();}//关闭流if (outputStream != null) {try {outputStream.close();} catch (IOException e) {e.printStackTrace();}}}}
//成品
