easyExcel的基本使用
添加依赖
最新版本是2.2.7
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
</dependency>
简单的读取
excel表格的数据如下(多表头加合并单元格)
后台对应的实体类如下
package com.kdmins.blog.pojo;
import com.alibaba.excel.annotation.ExcelProperty;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import java.util.Date;
@Data
public class Artitle{
@ExcelProperty(value = {"作者", "作者"}, index = 0)
String author;
@ExcelProperty(value ={"文章信息","文章名称"}, index = 1)
String name;
}
读取的逻辑如下
package com.kdmins.easyExcel;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.enums.CellExtraTypeEnum;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.CellExtra;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.fastjson.JSON;
import com.kdmins.blog.pojo.Artitle1;
import org.junit.Assert;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
/**
* @ClassName: ExcelListener
* @Author: Leo
* @Description:
* @Date: 7/29/2019 10:21 AM
*/
public class ExcelListener extends AnalysisEventListener {
private static final Logger LOGGER = LoggerFactory.getLogger(AnalysisEventListener.class);
/**
* 合并单元格
*/
private List<CellExtra> extraMergeInfoList = new ArrayList<>();
@Override
public void extra(CellExtra extra, AnalysisContext context) {
LOGGER.info("读取到了一条额外信息:{}", JSON.toJSONString(extra));
switch (extra.getType()) {
case COMMENT: {
LOGGER.info("额外信息是批注,在rowIndex:{},columnIndex;{},内容是:{}", extra.getRowIndex(), extra.getColumnIndex(),
extra.getText());
break;
}
case HYPERLINK: {
if ("Sheet1!A1".equals(extra.getText())) {
LOGGER.info("额外信息是超链接,在rowIndex:{},columnIndex;{},内容是:{}", extra.getRowIndex(),
extra.getColumnIndex(), extra.getText());
} else if ("Sheet2!A1".equals(extra.getText())) {
LOGGER.info(
"额外信息是超链接,而且覆盖了一个区间,在firstRowIndex:{},firstColumnIndex;{},lastRowIndex:{},lastColumnIndex:{},"
+ "内容是:{}",
extra.getFirstRowIndex(), extra.getFirstColumnIndex(), extra.getLastRowIndex(),
extra.getLastColumnIndex(), extra.getText());
} else {
Assert.fail("Unknown hyperlink!");
}
break;
}
case MERGE: {
LOGGER.info(
"额外信息是合并单元格,而且覆盖了一个区间,在firstRowIndex:{},firstColumnIndex;{},lastRowIndex:{},lastColumnIndex:{}",
extra.getFirstRowIndex(), extra.getFirstColumnIndex(), extra.getLastRowIndex(),
extra.getLastColumnIndex());
System.out.println(extra.getRowIndex());
System.out.println();
/*
System.out.println(headRowNumber);
*/
/*if (extra.getRowIndex() >= headRowNumber) {
extraMergeInfoList.add(extra);
}*/
break;
}
default: {
}
}
}
public static void main(String[] args) {
try {
FileInputStream inputStream = new FileInputStream("D:/123.xlsx");
ExcelListener excelListener = new ExcelListener();
EasyExcel.read(inputStream, Artitle1.class,excelListener).extraRead(CellExtraTypeEnum.MERGE).sheet(0).doRead();
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
//自定义用于暂时存储data。
//可以通过实例获取该值
private List<Object> datas = new ArrayList<Object>();
public void invoke(Object object, AnalysisContext context) {
System.out.println("当前行:" + context.getCurrentRowNum());
System.out.println(object);
datas.add(object);//数据存储到list,供批量处理,或后续自己业务逻辑处理。
doSomething(object);//根据自己业务做处理
}
private void doSomething(Object object) {
//1、入库调用接口
}
public void doAfterAllAnalysed(AnalysisContext context) {
datas.clear();//解析结束销毁不用的资源
}
public List<Object> getDatas() {
return datas;
}
public void setDatas(List<Object> datas) {
this.datas = datas;
}
}
写的简单代码如下
package com.kdmins.easyExcel;
import com.alibaba.excel.metadata.CellData;
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.Data;
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;
/**
* 单元格合并
*
* @author Jamin
* @date 2020/11/9 11:35
*/
@Data
public class ExcelFillCellMergeStrategy implements CellWriteHandler {
/**
* 合并字段的下标
*/
private int[] mergeColumnIndex;
/**
* 合并几行
*/
private int mergeRowIndex;
public ExcelFillCellMergeStrategy() {
}
public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {
this.mergeRowIndex = mergeRowIndex;
this.mergeColumnIndex = mergeColumnIndex;
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
Head head, Integer integer, Integer integer1, Boolean aBoolean) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
Head head, Integer integer, Boolean aBoolean) {
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
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;
}
}
}
}
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
//获取当前行的当前列的数据和上一行的当前列列数据,通过上一行数据是否相同进行合并
Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() :
cell.getNumericCellValue();
Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() :
preCell.getNumericCellValue();
// 比较当前行的第一列的单元格与上一行是否相同,相同合并当前单元格与上一行
//
if (curData.equals(preData)) {
Sheet sheet = writeSheetHolder.getSheet();
List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
boolean isMerged = false;
boolean isPreColMerged = false;
for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
CellRangeAddress cellRangeAddr = mergeRegions.get(i);
// 若上一个单元格已经被合并,则先移出原有的合并单元,再重新添加合并单元
if(curColIndex == 0){
isPreColMerged=true;
}else{
isPreColMerged =cellRangeAddr.isInRange(curRowIndex, curColIndex-1);
}
if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex) && isPreColMerged) {
sheet.removeMergedRegion(i);
cellRangeAddr.setLastRow(curRowIndex);
sheet.addMergedRegion(cellRangeAddr);
isMerged = true;
}
}
if (!isMerged && isPreColMerged) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex,
curColIndex);
sheet.addMergedRegion(cellRangeAddress);
}
}
}
}
调用如下
String fileName ="D:/123.xlsx";
int[] a= {0,1};
EasyExcel.write(fileName, Artitle1.class).sheet("模板").registerWriteHandler(new ExcelFillCellMergeStrategy(0,a)).doWrite(data);