一、前言
项目又提需求导出复杂样式表格,还要根据不同区划导出不同数据,又琢磨学习了下easyexcel和poi,实现效果如下:
1、多级表头合并
2、表头底纹+表格边框
3、合并相同数据单元格
4、尾行加入备注
二、效果图
直接上图
三、依赖
注意文中还需引入Lombok注解
Easyexcel
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
四、具体实现
1、测试类说明
注解说明:
@ContentRowHeight(25) //内容行高
@HeadRowHeight(20)//表头行高
@ColumnWidth(15)列宽度
@ExcelProperty(value = {"用户信息","行政区划","市级"}, index = 1)表头信息:
value中 相同表头会自动合并,index要按照自己需求设置好,否则字段顺序会乱
示例中用户信息是一级表头,行政区划是二级表头,市级、县市区是三级
代码
package com.hua.bo;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.Data;
import java.io.Serializable;
/**
* @Author su
* @Date 2023-09-15 14:47
* @Description
**/
@Data
@ContentRowHeight(25) //内容行高
@HeadRowHeight(20)//表头行高
public class ReportUserInfoBO implements Serializable {
@ColumnWidth(5)//宽度
@ExcelProperty(value = {"用户信息","序号"}, index = 0)
private String id;
@ColumnWidth(10)
@ExcelProperty(value = {"用户信息","行政区划","市级"}, index = 1)
private String sjmc;
@ColumnWidth(15)
@ExcelProperty(value = {"用户信息","行政区划","县(市、区)"}, index = 2)
private String qxmc;
@ColumnWidth(15)
@ExcelProperty(value = {"用户信息","用户名称"}, index = 3)
private String userName;
@ColumnWidth(15)
@ExcelProperty(value = {"用户信息","下单总数"}, index = 4)
private String sumNum;
}
2、控制层入口
说明:这块代码主要是造数据并在失败异常时处理异常转化为json格式方便前端展现。为序号复制根据自己需求来即可。
@GetMapping("/excelTest")
public void excel(HttpServletRequest req, HttpServletResponse res,@RequestParam("xzqhdm") String xzqhdm) throws Exception {
try {
log.info("开始导出");
res.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
res.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("test", "UTF-8").replaceAll("\\+", "%20");
res.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
List<ReportUserInfoBO> userList = new ArrayList<>();
ReportUserInfoBO bo;
//造数据
for (int i = 1; i < 30; i++) {
bo = new ReportUserInfoBO();
if (i < 10) {
bo.setQxmc("桥西");
bo.setSjmc("石家庄");
} else {
bo.setQxmc("桥东");
bo.setSjmc("秦皇岛");
}
bo.setSumNum(String.valueOf(i * (i + 9)));
bo.setUserName("测试" + i);
userList.add(bo);
}
//模仿数据库取出数据没有序号赋值
for (int i = 0; i < userList.size(); i++) {
userList.get(i).setId(String.valueOf(i + 1));
}
excelUtil.ReportExcel(userList, res);
log.info("导出成功");
} catch (Exception e) {
log.error(e.getMessage());
res.reset();
res.setContentType("application/json");
res.setCharacterEncoding("utf-8");
String origin = req.getHeader("Origin");
res.setHeader("Access-Control-Allow-Origin", origin);
res.getWriter().println(JSON.toJSONString(SysResult.fail(e.getMessage())));
}finally {
res.getOutputStream().close();
}
3、导出工具类ExcelUtil
说明:
写入数据需要嵌套List所以准备了beizhu这个list,在MergeLastWriteHandler中会使用WriteCellStyle是风格样式字体、底纹、边框等在这里设置
mergeColumeIndex是合并市级字段所需参数
mergeRowIndex 是合并市级字段所需参数
public void ReportExcel(List<ReportUserInfoBO> listVo, HttpServletResponse response) throws IOException {
//末尾备注
List<List<String>> beizhu = new ArrayList<>();
List<String> list = new ArrayList<>();
//末尾添加说明 合并最后一行单元格时赋值在 MergeLastWriteHandler
list.add("说明");
beizhu.add(list);
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 标题字体大小
WriteFont contentWriteFont = new WriteFont();
contentWriteFont.setFontHeightInPoints((short) 12);
headWriteCellStyle.setWriteFont(contentWriteFont);
//标题黄色底纹
headWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
//边框
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
// 水平居中
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 垂直居中
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置自动换行,前提内容中需要加「\n」才有效
contentWriteCellStyle.setWrapped(true);
//初始化样式
HorizontalCellStyleStrategy horizontalCellStyleStrategy =
new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
//合并单元格所需参数
//合并坐标
int[] mergeColumeIndex = {1, 1};
//从第二行后开始合并
int mergeRowIndex = 1;
try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), ReportUserInfoBO.class).autoCloseStream(Boolean.FALSE)
//风格样式
.registerWriteHandler(horizontalCellStyleStrategy)
//合并说明并赋值
.registerWriteHandler(new MergeLastWriteHandler())
//自动合并市级单元格 注意两个入参是出事
.registerWriteHandler(new ExcelMergeUtil(mergeRowIndex, mergeColumeIndex))
.build()) {
WriteSheet sheet = EasyExcel.writerSheet("测试").build();
excelWriter.write(listVo, sheet);
excelWriter.write(beizhu, sheet);
}
}
4、合并工具类
1、某个字段合并
借鉴前辈的代码,注意不同版本可能cell的某些方法不一致
mergeColumnIndex是你要合并单元格的范围
mergeRowIndex从第几行开始合并
package com.hua.Util;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
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 ExcelMergeUtil implements CellWriteHandler {
private int[] mergeColumnIndex;
private int mergeRowIndex;
public ExcelMergeUtil() {
}
public ExcelMergeUtil(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 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.getCellType() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
Object preData = preCell.getCellType()== CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
// 将当前单元格数据与上一个单元格数据比较
Boolean dataBool = preData.equals(curData);
//此处需要注意,获取每一行第二列数据和上一行第一列数据进行比较,如果相等合并,getCell里面的值,是名称所在列的下标
Boolean bool = cell.getRow().getCell(1).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(1).getStringCellValue());
if (dataBool && bool) {
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);
}
}
}
}
2、合并文末说明
在excelutil中会在末尾插入一个为名为beizhu的list,本方法或根据beizhu的值判断是否合并单元格
package com.hua.Util;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
public class MergeLastWriteHandler extends AbstractMergeStrategy {
@Override
protected void merge(Sheet sheet, Cell cell, Head head, Integer integer) {
String strValue = cell.getStringCellValue();
if (StringUtils.equals(strValue,"说明")){
//如果最后一行是说明则重新赋值
cell.setCellValue("说明:该表格仅用于测试学习哦!来看一看最终效果吧");
//获取表格最后一行
int lastRowNum = sheet.getLastRowNum();
//合并单元格
CellRangeAddress region = new CellRangeAddress(lastRowNum,lastRowNum,0,4);
sheet.addMergedRegion(region);
}
}
}
五、测试
测试工具发送保存请求
保存
最终结果