EasyExcel的使用方法
一、导入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
</dependency>
二、编写实体类
@Data
public class DemoData {
@ExcelProperty(value = "学生编号", index = 0)
private Integer sno;
@ExcelProperty(value = "学生姓名", index = 1)
private String sname;
}
三、写入Excel
@Test
public void writeExcel() {
//实现excel写操作
// 写法1 JDK8+
// since: 3.0.0-beta1
String fileName = "F:\\online-education/student.xlsx";
// 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
// 如果这里想使用03 则 传入excelType参数即可
EasyExcel.write(fileName, DemoData.class).sheet("Sheet1").doWrite(getData());
}
private static List<DemoData> getData(){
List<DemoData> list=new ArrayList<>();
for (int i=0;i<10;i++){
DemoData demoData = new DemoData();
demoData.setSno(i);
demoData.setSname("liming"+i);
list.add(demoData);
}
return list;
}
四、写入Excel并以流的方式发送给前端并下载
1.编辑响应体参数
/**
* 导出
*
* @param response
* @param data
* @param fileName
* @param sheetName
* @param clazz
* @throws Exception
*/
public static void writeExcel(HttpServletResponse response, List<? extends Object> data, String fileName, String sheetName, Class clazz) throws Exception {
//表头样式
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//设置表头居中对齐
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//内容样式
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
//设置内容靠左对齐
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
EasyExcel.write(getOutputStream(fileName, response), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).registerWriteHandler(horizontalCellStyleStrategy).doWrite(data);
}
/**
* 流方式响应给前端
*
* @param fileName
* @param response
* @return
* @throws Exception
*/
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 + ".xlsx");
response.setStatus(RESPONSE_SUCCESS_CODE);
return response.getOutputStream();
}
@PostMapping("exportExcel")
public void webWriteExcel(HttpServletResponse response) throws IOException {
ExcelUtil.writeExcel(response, ptPartDetails, fileName, sheetName, PtPartDetail.class);
}
五、读取Excel
编写监听类并重写所需方法
public class ExcelListener extends AnalysisEventListener<DemoData> {
//一行一行的读取excel内容
@Override
public void invoke(DemoData demoData, AnalysisContext analysisContext) {
System.out.println("数据："+demoData);
}
//读取表头内容
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
System.out.println("表头："+ headMap);
}
//读取完成之后执行
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}
读取Excel文件
@Test
public void readExcel(){
String fileName = "F:\\online-education/student.xlsx";
EasyExcel.read(fileName,DemoData.class,new ExcelListener()).sheet().doRead();
}
使用模板写入
1、编辑模板
其中{xxxx}表示单个内容，{.xxxx}表示循环遍历内容
2、创建实体类
@Data
public class User implements Comparable<User>{
private Integer index;
private String username;
private String password;
private Integer age;
private String hobby;
private String habit;
private String phone;
private Integer num;
public User(Integer index, String username, String password, Integer age, String hobby, String habit, String phone, Integer num) {
this.index = index;
this.username = username;
this.password = password;
this.age = age;
this.hobby = hobby;
this.habit = habit;
this.phone = phone;
this.num = num;
}
public User() {
}
@Override
public int compareTo(User o) {
return this.username.compareTo(o.getUsername());
}
3、编辑填充方法
String templateFileName =
TestFileUtil.getPath() + "demo" + File.separator + "fill" + File.separator + "complex.xlsx";
String fileName = TestFileUtil.getPath() + "complexFill" + System.currentTimeMillis() + ".xlsx";
try (ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build()) {
WriteSheet writeSheet = EasyExcel.writerSheet().build();
// 这里注意 入参用了forceNewRow 代表在写入list的时候不管list下面有没有空行 都会创建一行，然后下面的数据往后移动。默认 是false，会直接使用下一行，如果没有则创建。
// forceNewRow 如果设置了true,有个缺点 就是他会把所有的数据都放到内存了，所以慎用
// 简单的说 如果你的模板有list,且list不是最后一行，下面还有数据需要填充 就必须设置 forceNewRow=true 但是这个就会把所有数据放到内存 会很耗内存
// 如果数据量大 list不是最后一行 参照下一个
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
excelWriter.fill(data(), fillConfig, writeSheet);
excelWriter.fill(data(), fillConfig, writeSheet);
Map<String, Object> map = MapUtils.newHashMap();
map.put("obj", 需要填充到表头的对象);
excelWriter.fill(map, writeSheet);
}
合并单元格（根据上一行的内容进行判断是否进行合并）
1、同填充方法使用同一个实体
2、编辑方法(测试数据+写出方法)
List<User> list=new ArrayList<>();
list.add(new User(1,"张三","1234567",13,"篮球1","学习0","123456",112));
list.add(new User(2,"李四","12456",15,"篮球1","学习","123456",12));
list.add(new User(3,"张三","1234567",13,"篮球3","学习2","123456",112));
list.add(new User(4,"赵六","13456",17,"篮球1","学习","123456",12));
list.add(new User(5,"李四","12456",15,"篮球2","学习","123456",12));
list.add(new User(6,"王武","12346",16,"篮球1","学习","123456",12));
list.add(new User(7,"张三","1234567",13,"篮球2","学习1","123456",112));
list.add(new User(8,"王武","12346",16,"篮球2","学习2","123456",12));
list.add(new User(9,"赵六","13456",17,"篮球2","学习","123456",12));
list.add(new User(10,"嘿嘿","1456",17,"篮球1","学习","123456",12));
List<User> collect = list.stream().sorted(Comparator.comparing(User::getUsername)).collect(Collectors.toList());
// 查询数据
String fileName = TestController.class.getResource("/").getPath()+"导出文件名.xlsx" ;
//需要合并的列
int[] mergeColumeIndex = {1,2,3};
// 从第二行后开始合并
int mergeRowIndex = 2;
// LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(2, 1,4);
EasyExcel.write(fileName,User.class).registerWriteHandler(new ExcelMergeHandler(mergeRowIndex, mergeColumeIndex)).sheet("Sheet1").doWrite(collect);
3、合并工具类
public class ExcelMergeHandler implements CellWriteHandler {
private int[] mergeColumnIndex;
private int mergeRowIndex;
public ExcelMergeHandler() {
}
public ExcelMergeHandler(int mergeRowIndex, int[] mergeColumnIndex) {
this.mergeRowIndex = mergeRowIndex;
this.mergeColumnIndex = mergeColumnIndex;
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
//当前行
int curRowIndex = cell.getRowIndex();
//当前列
int curColIndex = cell.getColumnIndex();
if (curRowIndex > mergeRowIndex) {
for (int i = 0; i < mergeColumnIndex.length; i++) {
if (curColIndex == mergeColumnIndex[i]) {
mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
break;
}
}
}
}
/**
* 当前单元格向上合并
*
* @param writeSheetHolder
* @param cell 当前单元格
* @param curRowIndex 当前行
* @param curColIndex 当前列
*/
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
//获取当前行的当前列的数据和上一行的当前列数据，通过上一行数据是否相同进行合并
Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
Cell preCell = cell.getSheet().getRow(curRowIndex - 1 ).getCell(curColIndex);
Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
//比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行
if (curData.equals(preData)){
Sheet sheet = writeSheetHolder.getSheet();
List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
boolean isMerged = false;
for (int i = 0; i < mergedRegions.size() && !isMerged; i++) {
CellRangeAddress cellAddresses = mergedRegions.get(i);
//若上 一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
if (cellAddresses.isInRange(curRowIndex - 1 , curColIndex)){
sheet.removeMergedRegion(i);
cellAddresses.setLastRow(curRowIndex);
sheet.addMergedRegion(cellAddresses);
isMerged = true;
}
}
//若上一个单元格未被合并，则新增合并单元
if (!isMerged){
CellRangeAddress cellAddresses = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
sheet.addMergedRegion(cellAddresses);
}
}
}
}
