easyexcel工具类,github地址:https://github.com/alibaba/easyexcel
引入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.22</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
</dependency>
读取excel文件
1、表头实体类
@ExcelProperty()可以定义表头的中文形式;@DateTimeFormate()可以定义日期的格式;@NumberFormat()可以定义数字的格式,如:数字格式是否为十进制
@Data
public class DemoData {
@ExcelProperty("姓名")
private String name;
@ExcelProperty("性别")
private String gender;
@ExcelProperty("年龄")
private int age;
@ExcelProperty("日期")
@DateTimeFormat("yyyy-MM-dd")
private Date date;
}
2、模板读取类
log.info("解析数据[{}]", JSON.toJSONString(demoData));读取到excel中的数据
当插入数据库时,可以分批插入;每存储100条,清理一次list
@Slf4j
public class DemoDataListener implements ReadListener<DemoData> {
/**
* 每存储100条,就清理List,方便内存回收
*/
private static final int BATCH_COUNT=100;
/**
* 缓存数据
*/
private List<DemoData> cachedDataList= ListUtils.newArrayListWithCapacity(BATCH_COUNT);
private DemoDao demoDao;
//便于测试
public DemoDataListener(){
demoDao=new DemoDao();
}
//spring 需要用有参
public DemoDataListener(DemoDao demoDao) {
this.demoDao = demoDao;
}
/**
* 每条数据解析时会回调
* @param demoData
* @param analysisContext
*/
public void invoke(DemoData demoData, AnalysisContext analysisContext) {
log.info("解析数据[{}]", JSON.toJSONString(demoData));
cachedDataList.add(demoData);
if(cachedDataList.size()>=BATCH_COUNT){
//存入数据库
saveData();
//清理lit
cachedDataList=ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
}
/**
* 读取完毕.
* @param analysisContext
*/
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
//保存数据库
saveData();
log.info("解析完毕");
}
private void saveData(){
log.info("一共有{}条数据",cachedDataList.size());
demoDao.save(cachedDataList);
log.info("存储数据库成功!");
}
}
(实际项目如果需要将读取到的数据存入数据库,需要加一个demoDao或者demoService)
public class DemoDao {
public void save(List<DemoData> list){
//mybatis的batchinsert
}
}
3、测试-读取文件
@Test
public void readTest(){
String fileName="D:\\JavaProject\\easyexcel\\test1.xlsx";
EasyExcel.read(fileName, DemoData.class,new DemoDataListener()).sheet().doRead();
}
写入excel文件
1、表头实体类
一些注释:
@ContentRowHeight(30)行高;@HeadRowHeight(20)第一列的行高;@ColumnWidth(25)列宽(可以写在类上==定义了所有单元格的列宽;也可以单独写在某一个属性上==单独定义某一个单元格的列宽);
@Data
//行高
@ContentRowHeight(30)
//第一列的行高
@HeadRowHeight(20)
//列宽
@ColumnWidth(25)
public class DemoData {
@ExcelProperty("姓名")
@ColumnWidth(50)//宽度50
private String name;
@ExcelProperty("性别")
private String gender;
@ExcelProperty("年龄")
private int age;
@ExcelProperty("日期")
@DateTimeFormat("yyyy-MM-dd")
private Date date;
}
2、造数据
private List<DemoData> date(){
List<DemoData> list = ListUtils.newArrayList();
for(int i=0;i<10;i++){
DemoData demoData = new DemoData();
demoData.setName("黎明"+i);
demoData.setGender("男");
Random random = new Random();
int age = random.nextInt(60);
demoData.setAge(age);
demoData.setDate(new Date());
list.add(demoData);
}
return list;
}
3、写入数据
(1)直接写入
sheet("模板")默认定义第一个sheet的名字,也可以sheet(2,"模板")定义第2个sheet的名字
/**
* 直接写入
*/
@Test
public void simpleWrite(){
//写法1
String fileName="D:\\JavaProject\\easyexcel\\test1.xlsx";
EasyExcel.write(fileName,DemoData.class)
.sheet("模板")
.doWrite(()->date());
}
(2)排除/只填入需要的 列写入
excludeColumnFiledNames()排除;includeColumnFiledNames()包括
/**
* 输入需要排除的列,再写入
* 可以创建一个indexData.class,指定需要写入的列,替换DemoData.class
*/
@Test
public void simpleWrite2(){
String fileName="D:\\JavaProject\\easyexcel\\test1.xlsx";
//排除的
Set<String> excludeSet = new HashSet<>();
excludeSet.add("name");
//需要写入的
// Set<String> includeSet = new HashSet<>();
// includeSet.add("name");
EasyExcel.write(fileName,DemoData.class)
.excludeColumnFiledNames(excludeSet)
// .includeColumnFiledNames(includeSet)
.sheet("模板")
.doWrite(()->date());
}
(3)重复写入
多次调用excelWriter.write(date,sheet);
/**
* 重复写入
*/
@Test
public void duplicateWrite(){
String fileName="D:\\JavaProject\\easyexcel\\test1.xlsx";
ExcelWriter excelWriter=null;
//1.一个文件分段导入
try {
excelWriter = EasyExcel.write(fileName, DemoData.class).build();
WriteSheet sheet = EasyExcel.writerSheet("模板").build();
//模拟数据库分页查询
for(int i=0;i<5;i++){
List<DemoData> date = date();
excelWriter.write(date,sheet);
//放在不同的sheet中
//WriteSheet sheet = EasyExcel.writerSheet(i,"模板"+i).build();
//excelWriter.write(date,sheet);
}
} finally {
//关闭流
if (excelWriter!=null){
excelWriter.finish();
}
}
}
写入图片到excel
1、实体类3种方法:
(1)File (2)InputStream (3)byte[] (4)URL
@Data
public class ImageDemoData {
private File file;
private InputStream inputStream;
private byte[] byteArray;
//网址
private URL url;
//根据文件导出 并 设置导出位置
private WriteCellData<Void> writeCellDataFile;
}
2、写入图片
/**
* 写入图片
*/
@Test
public void writeImage(){
String fileName="D:\\JavaProject\\easyexcel\\test1.xlsx";
String imagePath="D:\\个人资料\\毕业论文\\技术路线图.jpg";
try (FileInputStream inputStream = FileUtils.openInputStream(new File(imagePath))){
ArrayList<Object> list = ListUtils.newArrayList();
ImageDemoData imageData = new ImageDemoData();
list.add(imageData);
//三种方式 放入单元格
imageData.setByteArray(FileUtils.readFileToByteArray(new File(imagePath)));
imageData.setFile(new File(imagePath));
imageData.setInputStream(inputStream);
//没有设置url
//放入单元格的格式
//图片靠左 和 额外文字
WriteCellData<Void> writeCellData = new WriteCellData<>();
imageData.setWriteCellDataFile(writeCellData);
//需要加入的额外的东西,不需要type就设置为CellDataTypeEnum.EMPTY
writeCellData.setType(CellDataTypeEnum.STRING);
writeCellData.setStringValue("额外文字");
//放图片
List<ImageData> imageList=new ArrayList<>();
ImageData image = new ImageData();
imageList.add(image);
image.setImage(FileUtils.readFileToByteArray(new File(imagePath)));
image.setImageType(ImageData.ImageType.PICTURE_TYPE_PNG);
//设置单元格大小
image.setTop(5);
image.setRight(40);
image.setBottom(5);
image.setLeft(5);
// 设置图片的位置 假设 现在目标 是 覆盖 当前单元格 和当前单元格右边的单元格
// 起点相对于当前单元格为0 当然可以不写
// imageData.setRelativeFirstRowIndex(0);
// imageData.setRelativeFirstColumnIndex(0);
// imageData.setRelativeLastRowIndex(0);
// 前面3个可以不写 下面这个需要写 也就是 结尾 需要相对当前单元格 往右移动一格
// 也就是说 这个图片会覆盖当前单元格和 后面的那一格
writeCellData.setImageDataList(imageList);
image.setRelativeLastColumnIndex(1);
EasyExcel.write(fileName,ImageDemoData.class).sheet(1,"模板1").doWrite(list);
} catch (IOException e) {
e.printStackTrace();
} finally {
}
}
设计样式
WriteCellStyle可以设置表头的格式,也可以设置内容的格式。两个设置完后放入HorizontalCellStyleStrategy()
最后注册策略:registerWriteHandler
@Test
public void handlerStyleWrite(){
String fileName="D:\\JavaProject\\easyexcel\\test1.xlsx";
//1.设置第一列的格式
WriteCellStyle headStyle = new WriteCellStyle();
//设置背景颜色
headStyle.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
WriteFont headFont = new WriteFont();
//字体大小
headFont.setFontHeightInPoints((short)20);
headStyle.setWriteFont(headFont);
//2.设置内容的格式
WriteCellStyle contentStyle = new WriteCellStyle();
//头部的背景 默认可以显示颜色;内容的背景,必须将type设置 才能显示
contentStyle.setFillPatternType(FillPatternType.LEAST_DOTS);
contentStyle.setFillBackgroundColor(IndexedColors.GREEN.getIndex());
WriteFont contentFont = new WriteFont();
contentFont.setFontHeightInPoints((short)20);
contentStyle.setWriteFont(contentFont);
//3.自定义策略:头的样式和内容的样式由自己定义
HorizontalCellStyleStrategy styleStrategy = new HorizontalCellStyleStrategy(headStyle, contentStyle);
//写入
EasyExcel.write(fileName,DemoData.class)
//注册策略
.registerWriteHandler(styleStrategy)
.sheet("模板1").doWrite(date());
}
setFillPatternType样式的参数
合并单元格
LoopMergeStrategy合并单元格策略
最后注册策略:registerWriteHandler
@Test
public void mergeWrite(){
// 每隔2行会合并 把eachColumn 设置成 3 也就是我们数据的长度,所以就第一列会合并。当然其他合并策略也可以自己写
// LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(2, 0);
// // 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
// EasyExcel.write(fileName, DemoData.class).registerWriteHandler(loopMergeStrategy).sheet("模板").doWrite(data());
}
生成表头
(1)实时生成普通表头
1、普通三列
private List<List<String>> head(){
List<List<String>> list = ListUtils.newArrayList();
//列
List<String> head = ListUtils.newArrayList();
head.add("字符串"+System.currentTimeMillis());
List<String> head1 = ListUtils.newArrayList();
head1.add("数字" + System.currentTimeMillis());
List<String> head2 = ListUtils.newArrayList();
head2.add("日期" + System.currentTimeMillis());
list.add(head);
list.add(head1);
list.add(head2);
return list;
}
2、用Head()注册表头
@Test
public void generateHead(){
String fileName="D:\\JavaProject\\easyexcel\\test1.xlsx";
EasyExcel.write(fileName)
.head(head())
.sheet("模板")
.doWrite(new ArrayList<>());
}
(2)实时生成高级表头
@Test
public void generateHead2(){
String fileName="D:\\JavaProject\\easyexcel\\test1.xlsx";
//1.设置第一列的格式
WriteCellStyle headStyle = new WriteCellStyle();
//设置背景颜色
headStyle.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
WriteFont headFont = new WriteFont();
//字体大小
headFont.setFontHeightInPoints((short)20);
headStyle.setWriteFont(headFont);
//2.设置内容的格式
WriteCellStyle contentStyle = new WriteCellStyle();
//头部的背景 默认可以显示颜色;内容的背景,必须将type设置 才能显示
WriteFont contentFont = new WriteFont();
contentFont.setFontHeightInPoints((short)20);
contentStyle.setWriteFont(contentFont);
//3.自定义策略:头的样式和内容的样式由自己定义
HorizontalCellStyleStrategy styleStrategy = new HorizontalCellStyleStrategy(headStyle, contentStyle);
EasyExcel.write(fileName)
.head(head2())
.registerWriteHandler(styleStrategy)
.registerWriteHandler(new CellRowHeightStyleStrategy())//行高
.registerWriteHandler(new ExcelWidthStyleStrategy())
.sheet("晨检表")
.doWrite(new ArrayList<>());
}
自定义策略:1、表头、内容的背景颜色、字体;2、行高 3、列宽
行高:
public class CellRowHeightStyleStrategy extends AbstractRowHeightStyleStrategy {
@Override
protected void setHeadColumnHeight(Row row, int i) {
if (i==0){
//行高20,设置20*20=400
row.setHeight((short)400);
}
}
@Override
protected void setContentColumnHeight(Row row, int i) {
}
}
列宽:
public class ExcelWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {
//统计被调用多少次
private static int count=0;
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
Sheet sheet = writeSheetHolder.getSheet();
sheet.setColumnWidth(cell.getColumnIndex(),5000);
System.out.println(count++);
}
}
下拉框、超链接
1、下拉框:
设置下拉框:
DataValidationHelper helper = context.getWriteSheetHolder().getSheet().getDataValidationHelper(); DataValidationConstraint constraint = helper.createExplicitListConstraint(new String[]{"李明", "王阳", "李逵"});
可以用DataValidation设置参数校验,若输入非下拉框的参数,可以弹出提示框
@Slf4j
public class CustomSheetWriteHandler implements SheetWriteHandler {
@Override
public void afterSheetCreate(SheetWriteHandlerContext context) {
log.info("第{}个sheet写入成功",context.getWriteSheetHolder().getSheetNo());
//四个数字分别是 第一行、最后一行、第一列、最后一列
//第1 2行指排除 头后的第1 2
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 65536, 0, 0);
//设置下拉框
DataValidationHelper helper = context.getWriteSheetHolder().getSheet().getDataValidationHelper();
DataValidationConstraint constraint = helper.createExplicitListConstraint(new String[]{"李明", "王阳", "李逵"});
DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
//阻止输入非下拉框选项的值
dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
dataValidation.setShowErrorBox(true);
dataValidation.setSuppressDropDownArrow(true);
dataValidation.createErrorBox("提示","请选择下拉框内数据");
//设置日期验证
CellRangeAddressList dateList = new CellRangeAddressList(1, 65536, 3, 3);
DataValidationConstraint dateConstraint = helper.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN, "Date(2022,4,1)", "Date(2022,4,30)", "yyyy-MM-dd");
DataValidation validation = helper.createValidation(dateConstraint, dateList);
//提示
validation.createErrorBox("提示","请输入[yyyy-MM-dd]格式日期,范围:[2022-04-01,2022-04-30]");
validation.setShowErrorBox(true);
context.getWriteSheetHolder().getSheet().addValidationData(dataValidation);
context.getWriteSheetHolder().getSheet().addValidationData(validation);
}
}
2、超链接:
helper.createHyperlink(HyperlinkType.URL);还可以创建文件连接等等
@Slf4j
public class CustomCellWriteHandler implements CellWriteHandler {
@Override
public void afterCellDispose(CellWriteHandlerContext context) {
Cell cell = context.getCell();
// 这里可以对cell进行任何操作
log.info("第{}行,第{}列写入完成。", cell.getRowIndex(), cell.getColumnIndex());
if (BooleanUtils.isTrue(context.getHead())&&cell.getColumnIndex()==0){
CreationHelper helper = context.getWriteSheetHolder().getSheet().getWorkbook().getCreationHelper();
Hyperlink hyperlink = helper.createHyperlink(HyperlinkType.URL);
hyperlink.setAddress("https://www.baidu.com");
cell.setHyperlink(hyperlink);
}
}
}