项目实战 | Excel导出(一)——Excel导出方式
章节 |
---|
一、Excel导出方式 |
二、Excel返回内容优化 |
三、Excel导出样式 |
1、Excel模板填充导出
场景:将每个教师的教案数据都导出到各个以教师命名的sheet
难点:怎么根据教师数据,生成多个以教师名命名sheet的Excel模板去填充?
解决方式:
1、生成Excel模板的输入IO流:
①pom.xml配置
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
②通过基础Excel模板,动态生成多个sheet的Excel模板流
基础Excel模板:
动态生成多个sheet的excel模板流的代码:
private InputStream getTemplateIn(String fileName,List<TeachPlanList> teachPlanLists) throws IOException {
ByteArrayOutputStream bos = new ByteArrayOutputStream();
//原模板只有一个sheet,通过poi复制出需要的sheet个数的模板
XSSFWorkbook workbook = new XSSFWorkbook(new ClassPathResource(fileName).getInputStream());
//设置模板的第一个sheet的名称
String sheet =teachPlanLists.get(0).getUserName()+"-"+ teachPlanLists.get(0).getUserId();
workbook.setSheetName(0,sheet);
for (int i = 1; i < teachPlanLists.size(); i++) {
//复制模板,得到第i个sheet
workbook.cloneSheet(0,teachPlanLists.get(i).getUserName()+"-"+teachPlanLists.get(i).getUserId());
}
//写到流里
workbook.write(bos);
byte[] bArray = bos.toByteArray();
return new ByteArrayInputStream(bArray);
}
2、将生成的Excel输入流当做模板,进行数据填充,导出Excel
public void exportTeachingPlanList(String schoolID, HttpServletResponse response) throws IOException {
List<TeachPlanList> teachPlanLists =teachPlanMgrBaseInfoMapper.selectTeachPlanBySchoolId(schoolID);
String fileName = URLEncoder.encode("个人教案库统计数据", "UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
InputStream is = getTemplateIn("excel/TeachPlanData.xlsx",teachPlanLists);
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
.withTemplate(is)
.registerConverter(new FileSizeConverter())
.build();
for (int i = 0; i < teachPlanLists.size(); i++) {
WriteSheet writeSheet = EasyExcel.writerSheet(i).build();
excelWriter.fill(teachPlanLists.get(i).getTeachPlanExcelInfoList(), writeSheet);
//填充单独统计数据
Map<String, Object> map = new HashMap<>(2);
map.put("userName", teachPlanLists.get(i).getUserName());
map.put("countTeachPlan", teachPlanLists.get(i).getCountTeachPlan());
//填充列表数据
excelWriter.fill(map, writeSheet);
}
excelWriter.finish();
}
3、导出结果展示:
2、通过类结构导出Excel
场景:将数据进行统计导出成文档
1、构造Excel导出对象
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ShareTpExcelInfo {
@ExcelProperty("共享者名称")
@ColumnWidth(15)
private String sharerName;
@ExcelProperty("分享教案个数")
@ColumnWidth(15)
private Integer sharerCount;
@ExcelProperty("分享教案被使用次数")
@ColumnWidth(15)
private Integer useCount;
@ExcelProperty("分享教案平均被使用次数")
@ColumnWidth(15)
@JSONField(name="AvgUseCount")
private Double avgUseCount;
}
2、导出成Excel
public void exportShareTpExcel(HttpServletResponse response, List<ShareTpExcelInfo> list) throws IOException {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("共享教案库数据" + System.currentTimeMillis(), String.valueOf(StandardCharsets.UTF_8));
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), ShareTpExcelInfo.class).sheet("教案数据").doWrite(list);
}
3、导出结果