easyexcel 在 设置标题_七. EasyExcel标题加批注和标题字体填充红色
发布日期:2021-08-20 01:25:57 浏览次数:1 分类:技术文章

本文共 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 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!

上一篇:md5算出来不一样_相同的烧鹅配方,不一样的人做出来不同的烧鹅?
下一篇:eplan实战设计pdf百度云_EPLAN实战设计

发表评论

最新留言

感谢大佬
[***.8.128.20]2024年03月25日 23时16分39秒

关于作者

    喝酒易醉,品茶养心,人生如梦,品茶悟道,何以解忧?唯有杜康!
-- 愿君每日到此一游!

推荐文章

php 上传头像 裁剪插件,PHP+ajaxfileupload+jcrop插件完美实现头像上传剪裁_PHP教程 2019-04-21
php workerman怎么样,workerman怎样完成高并发_PHP开发框架教程 2019-04-21
c语言数组124048,根据GPS经纬度判断当前所属的市区 2019-04-21
c语言 迷宫图形界面 动态,C语言实现迷宫求解问题(详细思路+附源代码) 2019-04-21
百度地图 轨迹回放 android,【移动端】百度地图之历史轨迹回放 2019-04-21
p30手机更多鸿蒙,搭配麒麟980的华为P30还能升级到鸿蒙吗?同意网友的回答 2019-04-21
鸿蒙os推送计划,鸿蒙OS今年计划推送3亿终端 2019-04-21
html 时间控件 只选择年,求一个移动端日期选择插件(只选择年月)? 2019-04-21
交替性注意力_儿童交替性斜视怎么治 2019-04-21
rabbitmq接口异常函数方法_Rabbitmq消息服务器通讯异常: name must not be blank 2019-04-21
linux 重启kettle服务_kettle在linux启动spoon.sh报错(示例代码) 2019-04-21
字符串算法 金策_GitHub - 662d7/shareOI: OI & ACM 课件分享 2019-04-21
linux建立ftp suse_SUSE Linux 安装FTP 2019-04-21
snmpwalk 获取端口流量_通过snmpwalk命令计算接口速率 2019-04-21
刷b站点击量_B站首席运营官:月活首次突破2亿,日均视频播放量13亿次 2019-04-21
android 复制文件到指定文件夹_如何在 Linux 上复制文件/文件夹到远程系统? 2019-04-21
k歌的录音伴奏合成技术如何实现_天天唱的卡拉OK,你知道是如何诞生的吗? 2019-04-21
getchar()到底怎么用_微量元素肥料到底怎么用? 2019-04-21
base64解码乱码如何解决_CAD打开图纸文件有乱码问号我们如何有效解决呢 2019-04-21
2020统计局的行政划分表_2020年怀化市区划数据,怀化市有几个区、县、县级市... 2019-04-21