1.EasyExcel简单介绍
EasyExcel工具是阿里的一个操作excel的开源项目,对现有的POI框架进行性能优化,解决了大数据量时内存溢出的问题。同时封装的更加简单灵活,适合初学者上手。
2.快速开始
maven依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.2</version>
</dependency>
本地导出excel、通过web请求导出Excel请直接移步官方文档
本文主要介绍通过web请求导出Excel的复杂案例(excel模板、拦截器,填充功能)
需求介绍:
- 导出excel,要求根据单元格内容动态改变字体样式(拦截器实现)
- 最好能够保证单元格表头、内容美观度(模板实现)
- 在excel文档末尾有补充说明文字(填充功能实现)
具体需求文档样例
模板
ExportExcelServiceImpl
@Service
@Slf4j
public class ExportExcelServiceImpl implements ExportExcelService {
private static Random random = new Random();
@Override
public void exportStudentInfoExcel(HttpServletResponse response) throws IOException {
try {
export(response);
} catch (IOException e) {
// 重置response
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
Map<String, String> map = new HashMap<String, String>();
map.put("status", "failure");
map.put("message", "下载文件失败" + e.getMessage());
response.getWriter().println(JSON.toJSONString(map));
}
}
private void export(HttpServletResponse response) throws IOException {
String templatePath = ExportExcelServiceImpl.class.getResource("/templates/score.xlsx").toString().replace("file:/","");
List<StudentInfo> list = getStudentInfoList();
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("月考成绩.xlsx", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
ExcelWriter excelWriter = EasyExcel
.write(response.getOutputStream())
.registerWriteHandler(new CellWriteHandler()) // 自定义拦截器
.withTemplate(templatePath)
.autoCloseStream(Boolean.FALSE) // 这里需要设置不关闭流
.build();
WriteSheet writeSheet = EasyExcel.writerSheet("测试").build();
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
excelWriter.fill(list,fillConfig, writeSheet);
//设置填充内容
Map<String,String> map = new HashMap<>();
map.put("grade","高三10班");
map.put("key","说明");
map.put("value","* 英语成绩达到80分以上才算优秀");
excelWriter.fill(map, writeSheet);
excelWriter.finish();
}
/**
* @return 随机生成学生成绩列表
*/
private List<StudentInfo> getStudentInfoList(){
List<StudentInfo> list = Lists.newLinkedList();
for (int i = 0; i < 15; i++) {
StudentInfo studentInfo = new StudentInfo();
studentInfo.setName("张三" + i);
//随机生成450-600之间的总分
int totalScore = random.nextInt(151) + 450;
studentInfo.setTotalScore(String.valueOf(totalScore));
//随机生成成绩在90-150之间的分数
int englishScore = random.nextInt(61) + 90;
studentInfo.setEnglishScore(String.valueOf(englishScore));
list.add(studentInfo);
}
return orderByTotalScoreDesc(list);
}
private List<StudentInfo> orderByTotalScoreDesc(List<StudentInfo> list){
list.sort(((o1, o2) -> Integer.parseInt(o2.getTotalScore()) - Integer.parseInt(o1.getTotalScore())));
for (int i = 0; i < list.size(); i++) {
list.get(i).setNum(String.valueOf(i + 1));
}
return list;
}
}
CellWriteHandler拦截器
public class CellWriteHandler extends AbstractCellWriteHandler {
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
int columnIndex = cell.getColumnIndex();
int rowIndex = cell.getRowIndex();
if(rowIndex > 1){
//判断是否是第五列单元格,(单元格下标从0开始的)
if(columnIndex == 4){
String stringCellValue = cell.getStringCellValue();
long cellValue = Long.parseLong(stringCellValue);
//如果第5列单元格内容大于80,则将字体样式设置为红色
if(cellValue > 80){
Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
CellStyle cellStyle = workbook.createCellStyle();
//设置单元格边框类型
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
//水平居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
Font font = workbook.createFont();
//字体设置为红色
font.setColor(IndexedColors.RED.index);
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
}
}
}
}
}
StudentInfo
/**
* 建议将业务相关的成员变量类型设置为String类型,以便后面拦截器内对单元格的内容进行强转
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class StudentInfo {
//学号
private String num;
//姓名
private String name;
//总分
private String totalScore;
//是否进前十
private String isTop3;
//英语成绩
private String englishScore;
}