目录
一 EasyExcel简介
Java领域解析、生成Excel比较有名的框架有Apache poi、jxl等。但他们都存在一个严重的问题就是非常的耗内存。如果你的系统并发量不大的话可能还行，但是一旦并发上来后一定会OOM或者JVM频繁的full gc。
EasyExcel是阿里巴巴开源的一个excel处理框架，以使用简单、节省内存著称。EasyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中，而是从磁盘上一行行读取数据，逐个解析。
EasyExcel采用一行一行的解析模式，并将一行的解析结果以观察者的模式通知（AnalysisEventListener）
二 导出场景
pom依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>测试类
所有测试类都是这个格式,没有其他代码.
import com.minghe.jiaozhu.service.ExportService;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.web.bind.annotation.GetMapping;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
@SpringBootTest
public class ExportTest {
@Resource
private ExportService exportService;
/**
* 普通导出
*/
@Test
public void exportExcel1() {
String path = "D:\\test\\test.xlsx";
exportService.exportExcel1(path);
}
}代码导入包
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.converters.string.StringNumberConverter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.*;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.util.MapUtils;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.merge.LoopMergeStrategy;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.fastjson.JSON;
import com.minghe.jiaozhu.handler.*;
import com.minghe.jiaozhu.model.dto.*;
import com.minghe.jiaozhu.service.ExportService;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.stereotype.Service;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
@Service
public class ExportServiceImpl implements ExportService {
}数据源
private List<ExportHeadersExcel> data() {
List<ExportHeadersExcel> list = ListUtils.newArrayList();
for (int i = 0; i < 10; i++) {
ExportHeadersExcel excel = new ExportHeadersExcel();
excel.setJin("10");
excel.setMu("2.00");
excel.setShui("3.00");
excel.setHuo("4.00");
excel.setTu("5.00");
excel.setYin("6.00");
excel.setYang("7.01");
list.add(excel);
}
return list;
}1.正常导出
实体类
@Data
@HeadRowHeight(30)
//属性注释在最下
@ContentStyle(wrapped = BooleanEnum.TRUE, verticalAlignment = VerticalAlignmentEnum.CENTER, borderBottom = BorderStyleEnum.THIN, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN)
public class ExportExcel {
/**
* 金
*/
@ExcelProperty(value = "金")
private String jin;
/**
* 木
*/
@ExcelProperty(value = "木")
private String mu;
/**
* 水
*/
@ExcelProperty(value = "水")
private String shui;
/**
* 火
*/
@ExcelProperty(value = "火")
private String huo;
/**
* 土
*/
@ExcelProperty(value = "土")
private String tu;
/**
* 阴
*/
@ExcelProperty(value = "阴")
private String yin;
/**
* 阳
*/
@ExcelProperty(value = "阳")
private String yang;
}代码
public void exportExcel1(String path) {
String sheetName = "sheetName";
List<ExportExcel> exportExcelList = new ArrayList<>();
ExportExcel excel = new ExportExcel();
excel.setShui("aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa");
exportExcelList.add(excel);
ExcelWriter excelWriter = EasyExcel.write(path).build();
//sheetName可以不写
WriteSheet tableNameSheet = EasyExcel.writerSheet(sheetName).head(ExportExcel.class).build();
excelWriter.write(exportExcelList, tableNameSheet);
excelWriter.finish();
}效果
表头,和实体类对应,aaa是set的shui的值,sheetName是sheet的名字
另一种实现方式
ExcelWriterSheetBuilder sheetBuilder = EasyExcel.write(path, head)
.sheet(sheetName);
sheetBuilder.doWrite(exportExcelList);第三种实现方式
EasyExcel.write(path, ExportHeadersExcel.class)
.sheet(sheetName)
.doWrite(excels);我觉得第三种最简洁,文章中会使用第三种
从效果图可以看到,aaa太多,自动换行了,加上这行代码LongestMatchColumnWidthStyleStrategy,可以自动处理宽度,但是不太精确,效果也不太好,会把单元格变小
WriteSheet tableNameSheet = EasyExcel.writerSheet(sheetName).head(ExportExcel.class)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.build();效果
加上这行代码可以在表头上空行,参数写几就空几行.这是空5行的效果
2.添加密码
有时候文档需要加密,这是最简单的一种添加密码的方式
public void exportExcel2(String path) {
String sheetName = "sheetName";
List<ExportHeadersExcel> exportExcelList = new ArrayList<>();
ExportHeadersExcel excel = new ExportHeadersExcel();
excel.setShui("aaaaaaaaaaaaaaaaaaaaa");
exportExcelList.add(excel);
ExcelWriterSheetBuilder sheetBuilder = EasyExcel.write(path, ExportExcel.class)
.password("123456")
.sheet(sheetName);
sheetBuilder.doWrite(exportExcelList);
}打开文档时
输入密码 123456 后,可以正常编辑
3.导出多级表头和标题
实体类
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.alibaba.excel.enums.BooleanEnum;
import com.alibaba.excel.enums.poi.BorderStyleEnum;
import com.alibaba.excel.enums.poi.VerticalAlignmentEnum;
import lombok.Data;
@Data
@HeadRowHeight(30)
//属性注释在最下
@ContentStyle(wrapped = BooleanEnum.TRUE, verticalAlignment = VerticalAlignmentEnum.CENTER, borderBottom = BorderStyleEnum.THIN, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN)
public class ExportHeadersExcel {
/**
* 金
*/
@ExcelProperty(value = {"七灵图","五行", "金"})
//合并单元格
//@ContentLoopMerge(eachRow = 2)
private String jin;
/**
* 木
*/
@ExcelProperty(value = {"七灵图","五行", "木"})
private String mu;
/**
* 水
*/
@ExcelProperty(value = {"七灵图","五行", "水"})
private String shui;
/**
* 火
*/
@ExcelProperty(value = {"七灵图","五行", "火"})
private String huo;
/**
* 土
*/
@ExcelProperty(value = {"七灵图","五行", "土"})
private String tu;
/**
* 阴
*/
@ExcelProperty(value = {"七灵图","两仪", "阴"})
private String yin;
/**
* 阳
*/
@ExcelProperty(value = {"七灵图","两仪", "阳"})
private String yang;
}
代码
public void exportHeaders(String path) {
String sheetName = "sheetName";
List<ExportHeadersExcel> excels = new ArrayList<>();
ExportHeadersExcel exportHeadersExcel = new ExportHeadersExcel();
exportHeadersExcel.setShui("一元重水");
excels.add(exportHeadersExcel);
EasyExcel.write(path, ExportHeadersExcel.class)
.sheet(sheetName)
.doWrite(excels);
}效果图
可以看到,代码和正常导出一样,关键在于实体类的配置.在真正的表头金木水火土等字段上加了
{"七灵图","五行",金},阴阳上加了{"七灵图","两仪",阴},最终的效果就是"七灵图"成了标题,金木水火土在五行标题下,阴阳在两仪标题下
实体类上的注解的用法
/**
* ContentStyle注解的属性的注释
dataFormat 日期格式
hidden 设置单元格使用此样式隐藏
locked 设置单元格使用此样式锁定
quotePrefix 在单元格前面增加`符号，数字或公式将以字符串形式展示
horizontalAlignment 设置是否水平居中
wrapped 设置文本是否应换行。将此标志设置为true通过在多行上显示使单元格中所有内容可见
verticalAlignment 设置是否垂直居中
rotation 设置单元格中文本旋转角度。03版本的Excel旋转角度区间为-90° ~ 90°，07版本的Excel旋转角度区间为0°~180°
indent 设置单元格中缩进文本的空格数
borderLeft 设置左边框的样式
borderRight 设置右边框样式
borderTop 设置上边框样式
borderBottom 设置下边框样式
leftBorderColor 设置左边框颜色
rightBorderColor 设置右边框颜色
topBorderColor 设置上边框颜色
bottomBorderColor 设置下边框颜色
fillPatternType 设置填充类型
fillBackgroundColor 设置背景色
fillForegroundColor 设置前景色
shrinkToFit 设置自动单元格自动大小
*/
//边框设置
/* border-width：
（1）thin：定义细的边框
（2）medium：默认，中等边框
（3）thick：定义粗的边框
（4）length：自定义边框的宽度
border-style：
（1）none 定义无边框
（2）hidden 与none相同，不过应用于表时除外，对于表，hidden用于解决边框冲突。
（3）dotted 定义点状边框。在大多数浏览器显示为实线。
(4）dashed 定义虚线。在大多数浏览器显示为实线。
(5）soild 定义实线
（6）double 定义双线，双线的宽度等于border-width的值。
（7）groove 定义3d凹槽边框，效果取决于border-color
（8）ridge 定义3d垄状边框，其效果取决于border-color
（9）inset 同上
（10）outset 同上*/
4.导出多级表头和标题+没有表头的动态值
有时候,我们需要在导出的时候添加一个动态的值,比如导出的时候把时间作为一行展示.同时表头和标题都需要存在.
实体类
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.alibaba.excel.enums.BooleanEnum;
import com.alibaba.excel.enums.poi.BorderStyleEnum;
import com.alibaba.excel.enums.poi.VerticalAlignmentEnum;
import lombok.Data;
@Data
@HeadRowHeight(30)
//属性注释在最下
@ContentStyle(wrapped = BooleanEnum.TRUE, verticalAlignment = VerticalAlignmentEnum.CENTER, borderBottom = BorderStyleEnum.THIN, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN)
public class ExportHeadersValueExcel {
//cellWritervalue对应ValueCellWriteHandler里的cellWritervalue
/**
* 金
*/
@ExcelProperty(value = {"七灵图","总纲","五行", "金"})
private String jin;
/**
* 木
*/
@ExcelProperty(value = {"七灵图","${cellWriterValue}","五行", "木"})
private String mu;
/**
* 水
*/
@ExcelProperty(value = {"七灵图","${cellWriterValue}","五行", "水"})
private String shui;
/**
* 火
*/
@ExcelProperty(value = {"七灵图","${cellWriterValue}","五行", "火"})
private String huo;
/**
* 土
*/
@ExcelProperty(value = {"七灵图","${cellWriterValue}","五行", "土"})
private String tu;
/**
* 阴
*/
@ExcelProperty(value = {"七灵图","${cellWriterValue}","两仪", "阴"})
private String yin;
/**
* 阳
*/
@ExcelProperty(value = {"七灵图","${cellWriterValue}","两仪", "阳"})
private String yang;
}
代码
public void exportHeadersValue(String path) {
String sheetName = "sheetName";
List<ExportHeadersValueExcel> excels = new ArrayList<>();
ExportHeadersValueExcel exportHeadersValueExcel = new ExportHeadersValueExcel();
exportHeadersValueExcel.setShui("一元重水");
excels.add(exportHeadersValueExcel);
String value = "阴阳包括五行,五行含有阴阳";
EasyExcel.write(path, ExportHeadersValueExcel.class)
.registerWriteHandler(new ValueCellWriteHandler(value))
.sheet(sheetName).doWrite(excels);
}处理器
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.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.util.PropertyPlaceholderHelper;
import java.util.List;
import java.util.Properties;
/**
* @author 冥河教主
* @Date 2021/02/08 16:00
*/
public class ValueCellWriteHandler implements CellWriteHandler {
private String cellWritervalue;
PropertyPlaceholderHelper propertyPlaceholderHelper = new PropertyPlaceholderHelper("${","}");
public ValueCellWriteHandler(String cellvalue) {
this.cellWritervalue = cellvalue;
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
if (head != null) {
List<String> headNameList = head.getHeadNameList();
if (CollectionUtils.isNotEmpty(headNameList)) {
Properties properties = new Properties();
properties.setProperty("cellWriterValue", cellWritervalue);
for (int i = 0; i < headNameList.size(); i++) {
headNameList.set(i, propertyPlaceholderHelper.replacePlaceholders(headNameList.get(i), properties));
}
}
}
}
}
效果
实体类中的"${cellWriterValue}",取的是处理器中的cellWriterValue,在写入流的时候, .registerWriteHandler(new ValueCellWriteHandler(value)),注册处理器就行了
5.处理数值型单元格的角标
有时候数据中会存在小数,为了防止科学计数法等会将小数转成string存储,导出的时候单元格的格式就是string,(因为字段类型就是string),但是值是数值型,此时单元格上就会出现角标
在数字的左上方出现了角标.这种情况有两种解决办法,一种是将wps或者office设置为不显示角标[😀]..另一种是通过转换器转换成数值型.
代码
public void exportHeadersValueText1(String path) {
String sheetName = "sheetName";
List<ExportHeadersValueTextExcel> excels = new ArrayList<>();
ExportHeadersValueTextExcel excel = new ExportHeadersValueTextExcel();
excel.setJin("10");
excel.setMu("2.00");
excel.setShui("3.00");
excel.setHuo("4.00");
excel.setTu("5.00");
excel.setYin("6.00");
excel.setYang("7.01");
excels.add(excel);
EasyExcel.write(path, ExportHeadersValueTextExcel.class)
//.registerWriteHandler(new ValueCellWriteNumberHandler()) 如果需要对数值型做单独处理的可以用这个
// .registerConverter(new StringNumberConverter())
.sheet(sheetName)
.doWrite(excels);
}StringNumberConverter是easyExcel内部的数值转换器.但是使用了数值型后导出的小数将会自动去0.
如果能接受小数格式不统一可以用这种方法,另外如果在实体类中将字段类型改为bigdeciml类型也是一样的效果.
如果需要单独对单元格类型做处理,可以用ValueCellWriteNumberHandler单独处理
import cn.hutool.core.util.NumberUtil;
import com.alibaba.excel.enums.CellDataTypeEnum;
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.*;
/**
* @author 冥河教主
* @Date 2021/02/08 16:00
*/
public class ValueCellWriteNumberHandler implements CellWriteHandler {
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
WriteCellData<?> cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
String stringValue = cellData.getStringValue();
if (NumberUtil.isNumber(stringValue)){
cellData.setType(CellDataTypeEnum.NUMBER);
cellData.setNumberValue(NumberUtil.toBigDecimal(stringValue));
}
}
}
6.导出的excel中有图片
实体类
package com.minghe.jiaozhu.model.dto;
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.ContentStyle;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.alibaba.excel.converters.string.StringImageConverter;
import com.alibaba.excel.enums.BooleanEnum;
import com.alibaba.excel.enums.poi.BorderStyleEnum;
import com.alibaba.excel.enums.poi.VerticalAlignmentEnum;
import lombok.Data;
@Data
@HeadRowHeight(30)//设置表头的行高度
@ContentRowHeight(60)//设置单元格的高度
@ColumnWidth(100 / 8)
//属性注释在最下
@ContentStyle(wrapped = BooleanEnum.TRUE, verticalAlignment = VerticalAlignmentEnum.CENTER, borderBottom = BorderStyleEnum.THIN, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN)
public class ExportHeadersValueImageExcel {
//cellWritervalue对应ValueCellWriteHandler里的cellWritervalue
/**
* 金
*/
@ExcelProperty(value = {"七灵图","总纲","五行", "金"})
private String jin;
/**
* 木
*/
@ExcelProperty(value = {"七灵图","${cellWriterValue}","五行", "木"})
private String mu;
/**
* 水
*/
@ExcelProperty(value = {"七灵图","${cellWriterValue}","五行", "水"})
private String shui;
/**
* 火
*/
@ExcelProperty(value = {"七灵图","${cellWriterValue}","五行", "火"})
private String huo;
/**
* 土
*/
@ExcelProperty(value = {"七灵图","${cellWriterValue}","五行", "土"})
private String tu;
/**
* 阴
*/
@ExcelProperty(value = {"七灵图","${cellWriterValue}","两仪", "阴"})
private String yin;
/**
* 阳
*/
@ExcelProperty(value = {"七灵图","${cellWriterValue}","两仪", "阳"})
private String yang;
/**
* 如果图片用string类型 必须指定转换器，string默认转换成string
*/
@ExcelProperty(value = {"七灵图","${cellWriterValue}", "两仪","灵图"},converter = StringImageConverter.class)
@ColumnWidth(30)//图片的列宽单独设置
private String imagePath;
//有五种写入图片的方式,url是写图片的地址.测试中发现InputStream转换有问题 实际应用中可以看看具体什么问题
/* private File file;
private InputStream inputStream;
private String string;
private byte[] byteArray;
private URL url;*/
}
代码
public void exportHeadersValueImage(String path) {
String sheetName = "sheetName";
String imagePath = "D:\\test\\miao.jpg";
List<ExportHeadersValueImageExcel> excels = new ArrayList<>();
ExportHeadersValueImageExcel imageExcel = new ExportHeadersValueImageExcel();
imageExcel.setJin("10");
imageExcel.setMu("2.00");
imageExcel.setShui("3.00");
imageExcel.setHuo("4.00");
imageExcel.setTu("5.00");
imageExcel.setYin("6.00");
imageExcel.setYang("7.01");
imageExcel.setImagePath(imagePath);
excels.add(imageExcel);
EasyExcel.write(path, ExportHeadersValueImageExcel.class)
.sheet(sheetName).doWrite(excels);
}效果
实体类中,字段上可以加注解@ColumnWidth(30)来单独设置该字段的列宽,@ExcelProperty注解上要指定converter @ExcelProperty(value = {"七灵图", "两仪","灵图"},converter = StringImageConverter.class)
图片的方式有五种,文件,文件流,string(我理解为path),字节流,url(适用于图片在云端存储的)
/* private File file; private InputStream inputStream; private String string; private byte[] byteArray; private URL url;*/
7.导出的文件中有超链接和批注和公式
7.1 代码实现
实体类
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.alibaba.excel.enums.BooleanEnum;
import com.alibaba.excel.enums.poi.BorderStyleEnum;
import com.alibaba.excel.enums.poi.VerticalAlignmentEnum;
import com.alibaba.excel.metadata.data.WriteCellData;
import lombok.Data;
@Data
@HeadRowHeight(30)
//属性注释在最下
@ContentStyle(wrapped = BooleanEnum.TRUE, verticalAlignment = VerticalAlignmentEnum.CENTER, borderBottom = BorderStyleEnum.THIN, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN)
public class ExportHeadersLinkExcel {
/**
* 金
*/
@ExcelProperty(value = {"七灵图","五行", "金"})
private String jin;
/**
* 木
*/
@ExcelProperty(value = {"七灵图","五行", "木"})
private String mu;
/**
* 水
*/
@ExcelProperty(value = {"七灵图","五行", "水"})
private String shui;
/**
* 火
*/
@ExcelProperty(value = {"七灵图","五行", "火"})
private String huo;
/**
* 土
*/
@ExcelProperty(value = {"七灵图","五行", "土"})
private String tu;
/**
* 阴
*/
@ExcelProperty(value = {"七灵图","两仪", "阴"})
private String yin;
/**
* 阳
*/
@ExcelProperty(value = {"七灵图","两仪", "阳"})
private String yang;
/**
* 超链接
*
*/
private WriteCellData<String> hyperlink;
/**
* 备注
*
*/
private WriteCellData<String> commentData;
/**
* 公式
*
*/
private WriteCellData<String> formulaData;
/**
* 指定单元格的样式。当然样式 也可以用注解等方式。
*
*/
private WriteCellData<String> writeCellStyle;
/**
* 指定一个单元格有多个样式
*
*/
private WriteCellData<String> richText;
}
代码
public void exportHeadersValueLink(String path) {
String sheetName = "sheetName";
ExportHeadersLinkExcel excel = new ExportHeadersLinkExcel();
excel.setJin("10");
excel.setMu("2.00");
excel.setShui("3.00");
excel.setHuo("4.00");
excel.setTu("5.00");
excel.setYin("6.00");
excel.setYang("7.01");
// 设置超链接
WriteCellData<String> hyperlink = new WriteCellData<>("java生成pdf图表链接");
HyperlinkData hyperlinkData = new HyperlinkData();
hyperlinkData.setAddress("https://blog.csdn.net/lol19950605/article/details/128929870");
hyperlinkData.setHyperlinkType(HyperlinkData.HyperlinkType.URL);
hyperlink.setHyperlinkData(hyperlinkData);
excel.setHyperlink(hyperlink);
// 设置备注
WriteCellData<String> comment = new WriteCellData<>("批注的单元格信息");
CommentData commentData = new CommentData();
commentData.setAuthor("冥河教主");
commentData.setRichTextStringData(new RichTextStringData("这是一个批注"));
// 备注的默认大小是按照单元格的大小 这里想调整到4个单元格那么大 所以向后 向下 各额外占用了一个单元格 但是没生效
//commentData.setRelativeLastColumnIndex(1);
//commentData.setRelativeLastRowIndex(1);
comment.setCommentData(commentData);
excel.setCommentData(comment);
// 设置公式
WriteCellData<String> formula = new WriteCellData<>();
FormulaData formulaData = new FormulaData();
// 将 123456789 中的第一个数字替换成 2
// 这里只是例子 如果真的涉及到公式 能内存算好尽量内存算好 公式能不用尽量不用
//REPLACE(123456789,1,1,2) REPLACE:公式名 123456789:原字符串 第一个1:起始位置 第二个1:字符数 2:要替换的字符串
formulaData.setFormulaValue("REPLACE(123456789,1,1,2)");
formula.setFormulaData(formulaData);
excel.setFormulaData(formula);
List<ExportHeadersLinkExcel> excels = new ArrayList<>();
excels.add(excel);
EasyExcel.write(path, ExportHeadersLinkExcel.class)
.sheet(sheetName)
.doWrite(excels);
}
效果
超链接
批注
公式
7.2 处理器实现
代码
.registerWriteHandler(new WriteRowHandler()) 处理器
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 lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
/**
* @author 冥河教主
* @Date 2021/02/08 16:00
*/
@Slf4j
public class WriteRowHandler implements CellWriteHandler {
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
Head head, Integer relativeRowIndex, Boolean isHead) {
if (isHead) {
Drawing<?> drawingPatriarch = writeSheetHolder.getSheet().createDrawingPatriarch();
// 在第一行 第二列创建一个批注
Comment comment;
int columnIndex = cell.getColumnIndex();
switch (columnIndex){
case 1:
comment =drawingPatriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0,
cell.getColumnIndex(), cell.getRowIndex(),cell.getColumnIndex()+1,cell.getRowIndex()+1));
// 输入批注信息
comment.setString(new XSSFRichTextString("创建批注!"));
break;
default:
break;
}
}
}
}
必须使用xlsx格式,不能用xls格式,涉及到XSSF和HSSF解析
8.导出文件中设置单元格样式和字体样式
实体类 上同
代码
public void exportHeadersValueStyle(String path) {
String sheetName = "sheetName";
ExportHeadersLinkExcel excel = new ExportHeadersLinkExcel();
excel.setJin("10");
excel.setMu("2.00");
excel.setShui("3.00");
excel.setHuo("4.00");
excel.setTu("5.00");
excel.setYin("6.00");
excel.setYang("7.01");
// 设置单个单元格的样式 当然样式 很多的话 也可以用注解等方式。
WriteCellData<String> writeCellStyle = new WriteCellData<>("单元格样式");
WriteCellStyle writeCellStyleData = new WriteCellStyle();
// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.
writeCellStyleData.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
// 背景绿色
writeCellStyleData.setFillForegroundColor(IndexedColors.GREEN.getIndex());
writeCellStyle.setWriteCellStyle(writeCellStyleData);
writeCellStyle.setType(CellDataTypeEnum.STRING);
excel.setWriteCellStyle(writeCellStyle);
// 设置单个单元格多种样式
// 这里需要设置 inMomery=true 不然会导致无法展示单个单元格多种样式，所以慎用
WriteCellData<String> richTest = new WriteCellData<>();
RichTextStringData richTextStringData = new RichTextStringData();
richTextStringData.setTextString("红色绿色默认");
// 前2个字红色
WriteFont writeFont = new WriteFont();
writeFont.setColor(IndexedColors.RED.getIndex());
richTextStringData.applyFont(0, 2, writeFont);
// 接下来2个字绿色
writeFont = new WriteFont();
writeFont.setColor(IndexedColors.GREEN.getIndex());
richTextStringData.applyFont(2, 4, writeFont);
richTest.setRichTextStringDataValue(richTextStringData);
richTest.setType(CellDataTypeEnum.RICH_TEXT_STRING);
excel.setRichText(richTest);
List<ExportHeadersLinkExcel> excels = new ArrayList<>();
excels.add(excel);
EasyExcel.write(path, ExportHeadersLinkExcel.class)
.inMemory(true)
.sheet(sheetName)
.doWrite(excels);
}一个单元格多种样式需要启用内存.
在设置单元格背景或字体的颜色的时候，颜色会对应一个short类型的数据，下面是具体对应关系
9.导出多sheet页
9.1导出多sheet页同一个对象
代码
public void exportHeadersSheets(String path) {
String sheetName = "sheetName";
ExcelWriter excelWriter = EasyExcel.write(path, ExportHeadersExcel.class).build();
List<ExportHeadersExcel> data = this.data();
for (int i = 0; i < data.size(); i++) {
WriteSheet writeSheet = EasyExcel.writerSheet(i, sheetName + i).build();
excelWriter.write(data,writeSheet);
}
excelWriter.finish();
}this.data() 数据来自1正常导出前的多数据
效果
9.2导出多sheet页不同对象
代码
public void exportHeadersClassSheets(String path) {
ExcelWriter excelWriter = EasyExcel.write(path, ExportHeadersExcel.class).build();
List<ExcelSheets> sheets = new ArrayList<>();
List<ExportHeadersExcel> data = this.data();
//添加不同的对象 sheet名一定不能一样
sheets.add(new ExcelSheets("ExportHeadersExcel1",ExportExcel.class,data) );
sheets.add(new ExcelSheets("ExportHeadersExcel2",ExportHeadersExcel.class,data) );
sheets.add(new ExcelSheets("ExportHeadersExcel3",ExportHeadersValueExcel.class,data) );
//3个sheet,不同对象
for (ExcelSheets sheet : sheets) {
WriteSheet writeSheet = EasyExcel.writerSheet(sheet.getSheetName()).head(sheet.getClazz()).build();
excelWriter.write(sheet.getList(),writeSheet);
}
excelWriter.finish();
}效果
10.注解形式自定义设置样式
10.1 注解形式设置样式
实体类
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.*;
import com.alibaba.excel.enums.BooleanEnum;
import com.alibaba.excel.enums.poi.BorderStyleEnum;
import com.alibaba.excel.enums.poi.FillPatternTypeEnum;
import com.alibaba.excel.enums.poi.VerticalAlignmentEnum;
import lombok.Data;
@Data
@HeadRowHeight(30)
//属性注释在最下 // 内容的背景设置成绿色 IndexedColors.GREEN.getIndex()
@ContentStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 17,wrapped = BooleanEnum.TRUE, verticalAlignment = VerticalAlignmentEnum.CENTER, borderBottom = BorderStyleEnum.THIN, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN)
// 头背景设置成红色 IndexedColors.RED.getIndex()
@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 10)
// 头字体设置成20
@HeadFontStyle(fontHeightInPoints = 20)
// 内容字体设置成20
@ContentFontStyle(fontHeightInPoints = 20)
public class ExportHeadersStyleExcel {
/**
* 金
*/
@ExcelProperty(value = {"七灵图","五行", "金"})
// 字符串的头背景设置成粉红 IndexedColors.PINK.getIndex()
@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 14)
// 字符串的头字体设置成20
@HeadFontStyle(fontHeightInPoints = 30)
// 字符串的内容的背景设置成天蓝 IndexedColors.SKY_BLUE.getIndex()
@ContentStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40)
// 字符串的内容字体设置成20
@ContentFontStyle(fontHeightInPoints = 30)
private String jin;
/**
* 木
*/
@ExcelProperty(value = {"七灵图","五行", "木"})
private String mu;
/**
* 水
*/
@ExcelProperty(value = {"七灵图","五行", "水"})
private String shui;
/**
* 火
*/
@ExcelProperty(value = {"七灵图","五行", "火"})
private String huo;
/**
* 土
*/
@ExcelProperty(value = {"七灵图","五行", "土"})
private String tu;
/**
* 阴
*/
@ExcelProperty(value = {"七灵图","两仪", "阴"})
private String yin;
/**
* 阳
*/
@ExcelProperty(value = {"七灵图","两仪", "阳"})
private String yang;
}
代码 同普通导出
效果
10.2 使用已有策略自定义样式
代码
public void exportHeadersStyle2(String path) {
String sheetName = "sheetName";
List<ExportHeadersExcel> excels = new ArrayList<>();
ExportHeadersExcel exportHeadersExcel = new ExportHeadersExcel();
exportHeadersExcel.setShui("一元重水");
excels.add(exportHeadersExcel);
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 背景设置为红色
headWriteCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short)20);
headWriteCellStyle.setWriteFont(headWriteFont);
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
// 背景绿色
contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
WriteFont contentWriteFont = new WriteFont();
// 字体大小
contentWriteFont.setFontHeightInPoints((short)20);
contentWriteCellStyle.setWriteFont(contentWriteFont);
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
HorizontalCellStyleStrategy horizontalCellStyleStrategy =
new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
EasyExcel.write(path, ExportHeadersExcel.class)
.registerWriteHandler(horizontalCellStyleStrategy)
.sheet(sheetName)
.doWrite(excels);
}效果
9.3 使用easyexcel的方式完全自己定义样式
代码
public void exportHeadersStyle3(String path) {
String sheetName = "sheetName";
List<ExportHeadersExcel> excels = new ArrayList<>();
ExportHeadersExcel exportHeadersExcel = new ExportHeadersExcel();
exportHeadersExcel.setShui("一元重水");
excels.add(exportHeadersExcel);
EasyExcel.write(path, ExportHeadersExcel.class)
.registerWriteHandler(new EasyexcelCellWriteHandler())
.sheet(sheetName)
.doWrite(excels);
}处理器
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.util.BooleanUtils;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
/**
* @author 冥河教主
* @Date 2021/02/08 09:09
*/
public class EasyexcelCellWriteHandler implements CellWriteHandler {
@Override
public void afterCellDispose(CellWriteHandlerContext context) {
// 当前事件会在 数据设置到poi的cell里面才会回调
// 判断不是头的情况 如果是fill 的情况 这里会==null 所以用not true
if (BooleanUtils.isNotTrue(context.getHead())) {
// 第一个单元格
// 只要不是头 一定会有数据 当然fill的情况 可能要context.getCellDataList() ,这个需要看模板，因为一个单元格会有多个 WriteCellData
WriteCellData<?> cellData = context.getFirstCellData();
// 这里需要去cellData 获取样式
// 很重要的一个原因是 WriteCellStyle 和 dataFormatData绑定的 简单的说 比如你加了 DateTimeFormat
// ，已经将writeCellStyle里面的dataFormatData 改了 如果你自己new了一个WriteCellStyle，可能注解的样式就失效了
// 然后 getOrCreateStyle 用于返回一个样式，如果为空，则创建一个后返回
WriteCellStyle writeCellStyle = cellData.getOrCreateStyle();
writeCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND
writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
// 这样样式就设置好了 后面有个FillStyleCellWriteHandler 默认会将 WriteCellStyle 设置到 cell里面去 所以可以不用管了
}
}
}
效果
11.合并单元格
11.1 策略形式
代码
public void exportHeadersMerge(String path) {
String sheetName = "sheetName";
List<ExportHeadersExcel> excels = new ArrayList<>();
ExportHeadersExcel exportHeadersExcel = new ExportHeadersExcel();
exportHeadersExcel.setShui("一元重水");
excels.add(exportHeadersExcel);
ExportHeadersExcel exportHeadersExcel1 = new ExportHeadersExcel();
exportHeadersExcel1.setShui("玄冥真水");
excels.add(exportHeadersExcel1);
ExportHeadersExcel exportHeadersExcel2 = new ExportHeadersExcel();
exportHeadersExcel2.setShui("天一真水");
excels.add(exportHeadersExcel2);
ExportHeadersExcel exportHeadersExcel3 = new ExportHeadersExcel();
exportHeadersExcel3.setShui("无形真水");
excels.add(exportHeadersExcel3);
//自定义策略
//参数 int eachRow, int columnExtend, int columnIndex
//eachRow 2 代表每两行就合并,每一行都执行,如果不够两行,要合并的这一列会没有下方的边框
//columnExtend 合并扩展列， 向右合并列
// columnIndex 列索引,从零开始，第columnIndex列开始执行合并，往右合并columnExtend列
LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(2, 0);
//注解形式 @ContentLoopMerge(eachRow = 2)
EasyExcel.write(path, ExportHeadersExcel.class)
.registerWriteHandler(loopMergeStrategy)
.sheet(sheetName)
.doWrite(excels);
}效果
LoopMergeStrategy的参数释义: //eachRow 2 代表每两行就合并,每一行都执行,如果不够两行,要合并的这一列会没有下方的边框 //columnExtend 合并扩展列， 向右合并列 // columnIndex 列索引,从零开始，第columnIndex列开始执行合并，往右合并columnExtend列
11.2 注解形式
实体类
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ContentLoopMerge;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.alibaba.excel.enums.BooleanEnum;
import com.alibaba.excel.enums.poi.BorderStyleEnum;
import com.alibaba.excel.enums.poi.VerticalAlignmentEnum;
import lombok.Data;
@Data
@HeadRowHeight(30)
//属性注释在最下
@ContentStyle(wrapped = BooleanEnum.TRUE, verticalAlignment = VerticalAlignmentEnum.CENTER, borderBottom = BorderStyleEnum.THIN, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN)
public class ExportHeadersExcel {
/**
* 金
*/
@ExcelProperty(value = {"七灵图","五行", "金"})
//合并单元格
@ContentLoopMerge(eachRow = 2)
private String jin;
/**
* 木
*/
@ExcelProperty(value = {"七灵图","五行", "木"})
private String mu;
/**
* 水
*/
@ExcelProperty(value = {"七灵图","五行", "水"})
private String shui;
/**
* 火
*/
@ExcelProperty(value = {"七灵图","五行", "火"})
private String huo;
/**
* 土
*/
@ExcelProperty(value = {"七灵图","五行", "土"})
private String tu;
/**
* 阴
*/
@ExcelProperty(value = {"七灵图","两仪", "阴"})
private String yin;
/**
* 阳
*/
@ContentLoopMerge(eachRow = 2)
@ExcelProperty(value = {"七灵图","两仪", "阳"})
private String yang;
}
代码
public void exportHeadersMerge(String path) {
String sheetName = "sheetName";
List<ExportHeadersExcel> excels = new ArrayList<>();
ExportHeadersExcel exportHeadersExcel = new ExportHeadersExcel();
exportHeadersExcel.setShui("一元重水");
excels.add(exportHeadersExcel);
ExportHeadersExcel exportHeadersExcel1 = new ExportHeadersExcel();
exportHeadersExcel1.setShui("玄冥真水");
excels.add(exportHeadersExcel1);
ExportHeadersExcel exportHeadersExcel2 = new ExportHeadersExcel();
exportHeadersExcel2.setShui("天一真水");
excels.add(exportHeadersExcel2);
ExportHeadersExcel exportHeadersExcel3 = new ExportHeadersExcel();
exportHeadersExcel3.setShui("无形真水");
excels.add(exportHeadersExcel3);
EasyExcel.write(path, ExportHeadersExcel.class)
.sheet(sheetName)
.doWrite(excels);
}效果
注解形式可以设定某一列进行合并,实体类上对金和阳两个字段加了注解,所以这两列进行了合并.策略形式不知道怎么设置某一列,知道的大佬在评论回复一下具体操作,万分感谢!
12.使用table,达成两层表头效果
代码
public void exportHeadersTable(String path) {
String sheetName = "sheetName";
List<ExportHeadersExcel> excels = new ArrayList<>();
ExportHeadersExcel exportHeadersExcel = new ExportHeadersExcel();
exportHeadersExcel.setShui("一元重水");
excels.add(exportHeadersExcel);
ExportHeadersExcel exportHeadersExcel1 = new ExportHeadersExcel();
exportHeadersExcel1.setShui("玄冥真水");
excels.add(exportHeadersExcel1);
List<ExportHeadersExcel> excels1 = new ArrayList<>();
ExportHeadersExcel exportHeadersExcel2 = new ExportHeadersExcel();
exportHeadersExcel2.setShui("天一真水");
excels1.add(exportHeadersExcel2);
ExportHeadersExcel exportHeadersExcel3 = new ExportHeadersExcel();
exportHeadersExcel3.setShui("无形真水");
excels1.add(exportHeadersExcel3);
// 把sheet设置为不需要头 不然会输出sheet的头 这样看起来第一个table 就有2个头了
WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).needHead(Boolean.FALSE).build();
// 这里必须指定需要头，table 会继承sheet的配置，sheet配置了不需要，table 默认也是不需要
WriteTable writeTable0 = EasyExcel.writerTable(0).needHead(Boolean.TRUE).build();
WriteTable writeTable1 = EasyExcel.writerTable(1).needHead(Boolean.TRUE).build();
// 第一次写入会创建头
ExcelWriter excelWriter = EasyExcel.write(path, ExportHeadersExcel.class).build();
excelWriter.write(excels, writeSheet, writeTable0);
// 第二次写入也会创建头，然后在第一次的后面写入数据
excelWriter.write(excels1, writeSheet, writeTable1);
excelWriter.finish();
}效果
13.动态表头
表头信息
private List<List<String>> head() {
List<List<String>> list = new ArrayList<List<String>>();
List<String> head0 = new ArrayList<String>();
head0.add("地" );
List<String> head1 = new ArrayList<String>();
head1.add("水");
List<String> head2 = new ArrayList<String>();
head2.add("火");
List<String> head3 = new ArrayList<String>();
head3.add("风");
list.add(head0);
list.add(head1);
list.add(head2);
list.add(head3);
return list;
}代码
public void exportDynamicHeaders(String path) {
//因为水在ExportHeadersExcel类中是第三个表头,对应head()方法中地水火风的火,所以"一元重水"在表头火下
String sheetName = "sheetName";
List<ExportHeadersExcel> excels = new ArrayList<>();
ExportHeadersExcel exportHeadersExcel = new ExportHeadersExcel();
exportHeadersExcel.setShui("一元重水");
excels.add(exportHeadersExcel);
List<List<String>> head = this.head();
EasyExcel.write(path)
.head(head)
.sheet(sheetName)
.doWrite(excels);
}效果
exportHeadersExcel.setShui("一元重水");shui这个字段在exportHeadersExcel对象中是第三个字段,所以导出的excel中,表头火下是一元重水.也可以在exportHeadersExcel对象中用@ExcelProperty(index = 0)来指定列.还可以直接用list<string>等形式
14 .操作单元格(第四行第一列添加超链接)
从14开始,后面的案例都是告诉大家,代码还可以这么实现
代码
public void exportHeadersRow(String path) {
//
String sheetName = "sheetName";
List<ExportHeadersExcel> data = data();
EasyExcel.write(path, ExportHeadersExcel.class)
.registerWriteHandler(new WriteRowHandler())
.sheet(sheetName)
.doWrite(data);
}处理器
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 lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
/**
* @author 冥河教主
* @Date 2021/02/08 16:00
*/
@Slf4j
public class WriteRowHandler implements CellWriteHandler {
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
Head head, Integer relativeRowIndex, Boolean isHead) {
if (isHead) {
Drawing<?> drawingPatriarch = writeSheetHolder.getSheet().createDrawingPatriarch();
// 在第一行 第二列创建一个批注
Comment comment;
int columnIndex = cell.getColumnIndex();
switch (columnIndex){
case 1:
comment =drawingPatriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0,
cell.getColumnIndex(), cell.getRowIndex(),cell.getColumnIndex()+1,cell.getRowIndex()+1));
// 输入批注信息
comment.setString(new XSSFRichTextString("创建批注!"));
break;
default:
break;
}
}
}
}
效果
第四行,第a列,值为10的单元格,有超链接,图片上显示不了
15.操作多sheet(第一列第一行和第二行添加下拉框)
代码
.registerWriteHandler(new WriteSheetHandler())
处理器
package com.minghe.jiaozhu.handler;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.handler.context.SheetWriteHandlerContext;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
/**
* @author 冥河教主
* @Date 2021/02/08 16:00
*/
@Slf4j
public class WriteSheetHandler implements SheetWriteHandler {
@Override
public void afterSheetCreate(SheetWriteHandlerContext context) {
log.info("第{}个Sheet写入成功。", context.getWriteSheetHolder().getSheetNo());
// 区间设置 第一列第一行和第二行的数据。由于第一行是头，所以第一、二行的数据实际上是第二三行
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 2, 0, 0);
DataValidationHelper helper = context.getWriteSheetHolder().getSheet().getDataValidationHelper();
DataValidationConstraint constraint = helper.createExplicitListConstraint(new String[] {"测试1", "测试2"});
DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
context.getWriteSheetHolder().getSheet().addValidationData(dataValidation);
}
}
效果
git仓库:导入导出: 导入导出的实例
读取excel场景:多场景easyExcel读取excel文件(二)-CSDN博客
根据模板填充excel:多场景easyExcel根据模板填充excel文件(三)-CSDN博客
1736
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
