本文共 5120 字,大约阅读时间需要 17 分钟。
一, 概述
在日常开发中, 经常会碰到导入导出的场景, 有导入就肯定有导入模板, 本文将介绍利用EasyExcel给标题添加批注和挑剔字体填充颜色
二. 代码
2.1 编写样式处理类: TitleHandler
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.StyleUtil;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import java.util.HashMap;
import java.util.List;
@Slf4j
public class TitleHandler implements CellWriteHandler {
//操作列
private List columnIndexs;
//颜色
private Short colorIndex;
// 批注
private HashMap annotationsMap;
public TitleHandler(List columnIndexs, Short colorIndex, HashMap annotationsMap) {
this.columnIndexs = columnIndexs;
this.colorIndex = colorIndex;
this.annotationsMap = annotationsMap;
}
public TitleHandler(List columnIndexs, Short colorIndex) {
this.columnIndexs = columnIndexs;
this.colorIndex = colorIndex;
}
@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 cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
if(isHead){
// 设置列宽
Sheet sheet = writeSheetHolder.getSheet();
sheet.setColumnWidth(cell.getColumnIndex(), 14 * 256);
writeSheetHolder.getSheet().getRow(0).setHeight((short)(1.8*256));
Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
Drawing> drawing = sheet.createDrawingPatriarch();
// 设置标题字体样式
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontName("宋体");
headWriteFont.setFontHeightInPoints((short)14);
headWriteFont.setBold(true);
if (CollectionUtils.isNotEmpty(columnIndexs) &&
colorIndex != null &&
columnIndexs.contains(cell.getColumnIndex())) {
// 设置字体颜色
headWriteFont.setColor(colorIndex);
}
headWriteCellStyle.setWriteFont(headWriteFont);
headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
CellStyle cellStyle = StyleUtil.buildHeadCellStyle(workbook, headWriteCellStyle);
cell.setCellStyle(cellStyle);
if (null != annotationsMap && annotationsMap.containsKey(cell.getColumnIndex())) {
// 批注内容
String context = annotationsMap.get(cell.getColumnIndex());
// 创建绘图对象
Comment comment=drawing.createCellComment(new XSSFClientAnchor(0, 0, 0,0, (short) cell.getColumnIndex(), 0, (short) 5, 5));
comment.setString(new XSSFRichTextString(context));
cell.setCellComment(comment);
}
}
}
}
2.2. 编写工具类 : EasyExcelUtil
public class EasyExcelUtil {
/**
* 导出excel
* @param outputStream 输出流
* @param dataList 导出的数据
* @param classT 模板类
* @param sheetName sheetName
* @param cellWriteHandlers 样式处理类
*/
public static void writeExcelWithModel(OutputStream outputStream, List extends Object> dataList, Class extends Object> classT, String sheetName, CellWriteHandler... cellWriteHandlers) {
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 单元格策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 初始化表格样式
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
ExcelWriterSheetBuilder excelWriterSheetBuilder = EasyExcel.write(outputStream, classT).sheet(sheetName).registerWriteHandler(horizontalCellStyleStrategy);
if (null != cellWriteHandlers && cellWriteHandlers.length > 0) {
for (int i = 0; i < cellWriteHandlers.length; i++) {
excelWriterSheetBuilder.registerWriteHandler(cellWriteHandlers[i]);
}
}
// 开始导出
excelWriterSheetBuilder.doWrite(dataList);
}
}
2.3 测试
public class TestEasyExcel {
@Data
@ColumnWidth(20)
public static class TestVO {
@ExcelProperty(value = "*姓名", index = 0)
private String name;
@ExcelProperty(value = "*年龄", index = 1)
private int age;
@ExcelProperty(value = "学校", index = 2)
private String school;
}
/**
* 测试导出模板
* 1. 标题指定某列标红色字段
* 2. 标题指定某列加批注
*/
@Test
public void testExport1() throws FileNotFoundException {
// 输出流
OutputStream outputStream = new FileOutputStream(new File("D:\\1.xlsx"));
// 导出的数据
List dataList = new ArrayList<>();
// 指定标红色的列
List columns = Arrays.asList(0, 1);
// 指定批注
HashMap annotationsMap = new HashMap<>();
annotationsMap.put(0,"第一列标题批注");
annotationsMap.put(1,"第二列标题批注");
TitleHandler titleHandler = new TitleHandler(columns, IndexedColors.RED.index,annotationsMap);
EasyExcelUtil.writeExcelWithModel(outputStream, dataList, TestVO.class, "sheetName", titleHandler);
}
}
转载地址:https://blog.csdn.net/weixin_32145817/article/details/112017301 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!