easyExcel合并数据导出(一对多)
语言 java
框架 ssm
需求 :看图 也是导出效果
数据库查询为(关系为一对多) 一个学生对应多个课程
实现步骤
1.实体类配置, 建议单独写个实体用来导出使用()
学生信息字段正常配置 , 课程表字段 使用 @ExcelProperty({"学生和选课中间表","学生编号"})
StudentExcel.java
package com.example.mybatisplus.pojo; import com.alibaba.excel.annotation.ExcelProperty; import com.baomidou.mybatisplus.annotation.IdType; import com.baomidou.mybatisplus.annotation.TableField; import com.baomidou.mybatisplus.annotation.TableId; import com.fasterxml.jackson.annotation.JsonFormat; import io.swagger.annotations.ApiModel; import io.swagger.annotations.ApiModelProperty; import lombok.Data; import java.io.Serializable; import java.util.Date; /** * easy_excel导出 * @author three * @date 2021/8/23 16:13 */ @ApiModel("easy_excel导出实体类") @Data public class StudentExcel implements Serializable { private static final long serialVersionUID = 1L; // ----学生信息表( 一 ) @ExcelProperty("学生姓名") @ApiModelProperty("学生姓名") private String sname; @ExcelProperty("性别") @ApiModelProperty("性别") private String sgender; @ExcelProperty("出生年月") @ApiModelProperty("出生年月") @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss") private Date sbirth; //----学生和选课中间表(多)---- @ExcelProperty({"学生和选课中间表","学生编号"}) @ApiModelProperty("学生编号") private Integer sno; @ExcelProperty({"学生和选课中间表","课程编号"}) @ApiModelProperty("课程编号") private String cno; @ExcelProperty({"学生和选课中间表","分数"}) @ApiModelProperty("分数") private String score; }
2.代码 工具类 ExcelFillCellMergeStrategy.java
package com.example.mybatisplus.utils; import com.alibaba.excel.metadata.CellData; import com.alibaba.excel.metadata.Head; import com.alibaba.excel.write.handler.CellWriteHandler; import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; import com.alibaba.excel.write.metadata.holder.WriteTableHolder; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.CellRangeAddress; import java.util.List; public class ExcelFillCellMergeStrategy implements CellWriteHandler { private int[] mergeColumnIndex; private int mergeRowIndex; public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) { this.mergeRowIndex = mergeRowIndex; this.mergeColumnIndex = mergeColumnIndex; } @Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) { } @Override public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { } @Override public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) { } @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) { 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(); // 将当前单元格数据与上一个单元格数据比较 Boolean dataBool = preData.equals(curData); if (dataBool) { Sheet sheet = writeSheetHolder.getSheet(); List<CellRangeAddress> mergeRegions = sheet.getMergedRegions(); boolean isMerged = false; for (int i = 0; i < mergeRegions.size() && !isMerged; i++) { CellRangeAddress cellRangeAddr = mergeRegions.get(i); // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元 if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) { sheet.removeMergedRegion(i); cellRangeAddr.setLastRow(curRowIndex); sheet.addMergedRegion(cellRangeAddr); isMerged = true; } } // 若上一个单元格未被合并，则新增合并单元 if (!isMerged) { CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex); sheet.addMergedRegion(cellRangeAddress); } } } }
3. EasyExcelController.java
3.1 int[] mergeColumeIndex = {0,1,2}; 数据重复部分会合并成一个单元格,只需标出需要合并的列数
3.2 int mergeRowIndex = 0; 标出合并的开始列数 从0开始
package com.example.mybatisplus.controller; import com.alibaba.excel.EasyExcel; import com.example.mybatisplus.pojo.StudentExcel; import com.example.mybatisplus.pojo.StudentinfoEntity; import com.example.mybatisplus.service.StudentinfoService; import com.example.mybatisplus.utils.ExcelFillCellMergeStrategy; import io.swagger.annotations.Api; import io.swagger.annotations.ApiOperation; import org.springframework.http.HttpRequest; import org.springframework.web.bind.annotation.*; import javax.annotation.Resource; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.net.URLEncoder; import java.util.List; /** * EasyExcel test * 测试地址 http://localhost:8088/easy-excel/export * 传输数据 [1,2,3] * @author zou * @date 2021/8/23 15:51 */ @Api(tags = "easy-excel的使用") @RestController @RequestMapping("/easy-excel") public class EasyExcelController { @Resource private StudentinfoService studentinfoService; @PostMapping("/export") @ApiOperation("导出excel(一对多关系)") public void excel(HttpServletResponse response,@RequestBody Integer[] sno) throws IOException { List<StudentExcel> studentinfoEntities = studentinfoService.studentToElectives(sno); // 设置响应格式 response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); //需要合并的列 int[] mergeColumeIndex = {0,1,2}; // 从那一列开始合并 int mergeRowIndex = 0; String fileName = URLEncoder.encode("one-to-many", "UTF-8"); response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx"); EasyExcel.write(response.getOutputStream(), StudentExcel.class) .sheet("one-to-many") .registerWriteHandler(new ExcelFillCellMergeStrategy(mergeRowIndex, mergeColumeIndex)) .doWrite(studentinfoEntities); } }
4.服务层,持久层略去一千行....
....
