一.依赖:
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.11</version>
</dependency>
二.读取 不使用注解和实体
1.简单读取全部数据
/**
* @author Eng
*/
public static void main(String[] args) throws Exception {
String filePath = "C:\\Users\\Admin\\Desktop\\upload2.xlsx";
String fileName = "upload2.xlsx";
File file = new File(filePath);
FileInputStream inputStream = new FileInputStream(file);
ExcelReaderBuilder excelReaderBuilder = EasyExcel.read(inputStream)
.autoCloseStream(true)
// .readCache(new Ehcache(500))//这里和readCacheSelector() 只能配置一个
.readCacheSelector(new SimpleReadCacheSelector(1, 500))
.ignoreEmptyRow(true)//忽略空行
.headRowNumber(0);//读取到多少行算作表头
String suffixName = FilenameUtils.getExtension(fileName);
List<Object> objectList = excelReaderBuilder
.excelType(suffixName.equals("xlsx") ? ExcelTypeEnum.XLSX : (suffixName.equals("xls") ? ExcelTypeEnum.XLS : null))//读取的文件类型
.sheet(0)//读取的sheet 可以是行号也可以是sheet名
.doReadSync();//同步返回数据
System.out.println(objectList.size());//获取的数据集合
}
2.监听器读取并处理数据
/**
* @author Eng
* @Description 读取数据
*/
/**
* @author Eng
* @Description 读取数据
*/
public static void main(String[] args) throws Exception {
String filePath = "C:\\Users\\Admin\\Desktop\\upload2.xlsx";
String fileName = "upload2.xlsx";
String suffixName = FilenameUtils.getExtension(fileName);
File file = new File(filePath);
FileInputStream inputStream = new FileInputStream(file);
EasyExcel.read(inputStream,new ExcelListener(10,getConfig()))
.autoCloseStream(true)
// .readCache(new Ehcache(500))//这里和readCacheSelector() 只能配置一个
.readCacheSelector(new SimpleReadCacheSelector(1, 500))
.ignoreEmptyRow(true)//忽略空行
.headRowNumber(1)//读取到多少行算作表头
.excelType(suffixName.equals("xlsx") ? ExcelTypeEnum.XLSX : (suffixName.equals("xls") ? ExcelTypeEnum.XLS : null))//读取的文件类型
.sheet(0)//读取的sheet 可以是行号也可以是sheet名
.doRead();//同步返回数据
}
private static TreeSet<Triple<String,String,Integer>> getConfig(){
TreeSet<Triple<String,String,Integer>> configFields = new TreeSet<>();
// 获取顺序
for (int i = 0; i < 10; i++) {
configFields.add(Triple.of("表头"+i,"field"+i,i));
}
return configFields;
}
package com.local.excel.easyexcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.lang3.tuple.Triple;
import java.util.*;
import java.util.stream.Collectors;
/**
* @author Eng
* @date 2021/10/11 10:57
* @Description
*/
public class ExcelListener extends AnalysisEventListener {
/**
* 保存数据
*/
private List<Object> data = new ArrayList<Object>();
/**
* 数据处理限制
*/
private int totalLimit;
/**
* 总数
*/
private int total;
/**
* 配置表头顺序<中文,字段,排序/下标>
*/
private TreeSet<Triple<String,String,Integer>> configFields;
ExcelListener(int totalLimit,TreeSet<Triple<String,String,Integer>> configFields){
this.totalLimit = totalLimit;
this.configFields = configFields;
}
@Override
public void invokeHeadMap(Map headMap, AnalysisContext context) {
// map 是 <0,中文名> <下标,表头名>
Map<Integer,String> map = new HashMap<Integer,String>(headMap);
Map<String, Integer> reversalHeadMap = map.entrySet().stream().collect(Collectors.toMap(Map.Entry::getValue, Map.Entry::getKey));
TreeSet<Triple<String,String,Integer>> newConfigFields = new TreeSet<>();
// 获取顺序
configFields.forEach(triple-> {
newConfigFields.add(Triple.of(triple .getLeft(),triple .getMiddle(),reversalHeadMap.get(triple .getLeft())));
});
this.configFields = newConfigFields;
}
@Override
public void invoke(Object object, AnalysisContext context) {
Map headDataMap = JSONObject.parseObject(JSON.toJSONString(object),Map.class);
Map<String,Object> rowData = new HashMap();
configFields.forEach(triple-> {
Object dataValue = headDataMap.get(triple.getRight());
rowData.put(triple.getMiddle(),dataValue);
});
data.add(rowData);
if(data.size() == totalLimit){
//处理数据
data.clear();
}
total++;
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
//读取所有数据最后做的事
System.out.println(total);
}
@Override
public boolean hasNext(AnalysisContext context){
//可以修改为false 控制读取结束
return true;
}
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
//如果这里抛出异常也会结束读取
throw exception;
}
}
三.写excel
/**
* @author Eng
*/
public static void main(String[] args) throws IOException {
OutputStream out = new FileOutputStream("C:\\Users\\Admin\\Desktop\\upload2.xlsx");
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
ExcelWriter excelWriter = EasyExcelFactory.write(byteArrayOutputStream)
.excelType(ExcelTypeEnum.XLSX)//写直接指定类型 因为xls sheet中会有写入限制
.head(head())//这里或者sheet上写表头就要在table中关闭表头
.autoCloseStream(false)
.build();
//如果想要写入多个sheet 这里就直接for创建多个 进行多次write 就可以
WriteSheet writeSheet = EasyExcel.writerSheet(0)
// .head(head())
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())//设置自动宽度 这里自动宽度设置暂时没有找到按照表头宽为标准设置数据行配置
.registerWriteHandler(createStyleStrategy())
.registerWriteHandler(new CellStyleWriteHandler(1,9,null))
.build();
//这里可以实现分批次写入 setTableNoI(0) 数字要每次都增加
WriteTable writeTable = new WriteTable();
// writeTable.setHead(head());//如果每次写入都有表头或者不同表头就在这里写
writeTable.setTableNo(0);
writeTable.setNeedHead(false);
excelWriter.write(data(600),writeSheet,writeTable);
excelWriter.finish();//这里进行关闭后才会写入流 注意执行顺序
out.write(byteArrayOutputStream.toByteArray());
out.close();
}
/**
* 创建一个通用写excel样式
* @return
*/
public static HorizontalCellStyleStrategy createStyleStrategy() {
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 样式
headWriteCellStyle.setFillForegroundColor(IndexedColors.YELLOW1.getIndex());
headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
headWriteCellStyle.setBorderRight(BorderStyle.THIN);
headWriteCellStyle.setBorderTop(BorderStyle.THIN);
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
headWriteCellStyle.setQuotePrefix(true);
// 设置字体
WriteFont headWriteFont = new WriteFont();
headWriteFont.setColor(IndexedColors.BLACK.getIndex());
headWriteFont.setFontHeightInPoints((short) 14);
headWriteFont.setBold(true);
headWriteFont.setFontName("微软雅黑");
headWriteCellStyle.setWriteFont(headWriteFont);
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
//样式
contentWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
contentWriteCellStyle.setWrapped(true);
contentWriteCellStyle.setQuotePrefix(true);
// 字体
WriteFont contentWriteFont = new WriteFont();
contentWriteFont.setFontHeightInPoints((short) 12);
contentWriteFont.setColor(IndexedColors.BLACK.getIndex());
contentWriteFont.setFontName("宋体");
contentWriteCellStyle.setWriteFont(contentWriteFont);
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
HorizontalCellStyleStrategy horizontalCellStyleStrategy =
new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
return horizontalCellStyleStrategy;
}
private static List<List<String>> head(){
List<List<String>> heads = new ArrayList<>();
for (int i = 0; i < 10; i++) {
List<String> head = new ArrayList<>();
head.add("表头" + i);
heads.add(head);
}
return heads;
}
private static List<Object> data(int x) {
List<Object> rows = new ArrayList<>();
List<Integer> errorList = new ArrayList();
int currentIndexRow = 0;
for (int i = 0; i < x; i++){
List<Object> row = new ArrayList<>();
row.add("1" + i);
row.add(null);
row.add(i);
row.add(null);
row.add(i);
row.add(null);
row.add(i);
row.add(null);
row.add(i);
row.add("10" + i);
rows.add(row);
if (i % 2 == 0){
errorList.add(currentIndexRow);
}
currentIndexRow++;
}
return rows;
}
自定义样式
package com.local.excel.easyexcel;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.StyleUtil;
import com.alibaba.excel.write.handler.AbstractCellWriteHandler;
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 org.apache.poi.ss.usermodel.*;
import org.springframework.util.Assert;
import org.springframework.util.CollectionUtils;
import java.util.List;
/**
* @author Eng
* @date 2021/11/15 9:32
* @Description 自定义样式 这里可以精确样式到 cell要配合 使用 HorizontalCellStyleStrategy 实现
* 还有一种实现方式是 写每个table时放入一个style 以达到不同样式目的,查看后发现方法被弃用
* 使用HorizontalCellStyleStrategy 可以更好实现但不满足导入写回执文件需求
*/
public class CellStyleWriteHandler extends AbstractCellWriteHandler {
// @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 afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { }
/**
* 错误数据行 不包含表头行
*/
private List<Integer> rowError;
/**
* 总行数
*/
private Integer rowLine;
/**
* 总列数
*/
private Integer columnTotal;
/**
* cellSytle
*/
private CellStyle successCellStyle;
private CellStyle errorCellStyle;
private CellStyle bodyCellStyle;
private CellStyle headCellStyle;
/**
* @param rowLine 总行数 从1开始
* @param columnTotal 总列数从1开始计算
*/
public CellStyleWriteHandler(Integer rowLine, Integer columnTotal,List<Integer> rowError){
this.rowLine = rowLine != null ? rowLine - 1 : 0;
this.columnTotal = columnTotal != null ? columnTotal - 1 : 0;
this.rowError = rowError;
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
int columnIndex = cell.getColumnIndex();//当前列
int rowIndex = cell.getRowIndex();//当前行
if (columnIndex == 0 && rowIndex == 0){//第一次进行创建
this.setCreateCellStyle(cell.getSheet().getWorkbook());
}
//表头样式
if (rowIndex <= rowLine){
//表头样式写在这里不生效 多行表头时应该是最后一行表头在这里配置不生效 其余行有效
cell.getRow().getCell(columnIndex).setCellStyle(headCellStyle);
//表头宽度
// if (sheet == null){
// sheet = writeSheetHolder.getSheet();
// }
// sheet.setColumnWidth(cell.getColumnIndex(), (cell.getStringCellValue().length() * 1000));
} else {
// 内容样式
Assert.isTrue(columnTotal > 0,"表头不能小于0");
if (columnIndex <= columnTotal){
//数据样式
cell.getRow().getCell(columnIndex).setCellStyle(bodyCellStyle);
}else {
}
}
}
private void setCreateCellStyle(Workbook workbook){
successCellStyle = StyleUtil.buildContentCellStyle(workbook, this.setSuccessWriteCellStyle());
errorCellStyle = StyleUtil.buildContentCellStyle(workbook, this.setErrorWriteCellStyle());;
bodyCellStyle = StyleUtil.buildContentCellStyle(workbook, this.setBodyWriteCellStyle());;
headCellStyle = StyleUtil.buildContentCellStyle(workbook, this.setHeadWriteCellStyle());;
}
private WriteCellStyle setSuccessWriteCellStyle(){
//
WriteCellStyle writeCellStyle = new WriteCellStyle();
// 样式
writeCellStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
writeCellStyle.setBorderBottom(BorderStyle.THIN);
writeCellStyle.setBorderLeft(BorderStyle.THIN);
writeCellStyle.setBorderRight(BorderStyle.THIN);
writeCellStyle.setBorderTop(BorderStyle.THIN);
// writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
writeCellStyle.setQuotePrefix(true);
// 设置字体
WriteFont headWriteFont = new WriteFont();
headWriteFont.setColor(IndexedColors.GREEN.getIndex());
headWriteFont.setFontHeightInPoints((short) 12);
// headWriteFont.setBold(true);
headWriteFont.setFontName("宋体");
writeCellStyle.setWriteFont(headWriteFont);
return writeCellStyle;
}
private WriteCellStyle setErrorWriteCellStyle(){
//
WriteCellStyle writeCellStyle = new WriteCellStyle();
// 样式
writeCellStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
writeCellStyle.setBorderBottom(BorderStyle.THIN);
writeCellStyle.setBorderLeft(BorderStyle.THIN);
writeCellStyle.setBorderRight(BorderStyle.THIN);
writeCellStyle.setBorderTop(BorderStyle.THIN);
// writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
writeCellStyle.setQuotePrefix(true);
// 设置字体
WriteFont headWriteFont = new WriteFont();
headWriteFont.setColor(IndexedColors.RED.getIndex());
headWriteFont.setFontHeightInPoints((short) 14);
// headWriteFont.setBold(true);
headWriteFont.setFontName("宋体");
writeCellStyle.setWriteFont(headWriteFont);
return writeCellStyle;
}
private WriteCellStyle setHeadWriteCellStyle(){
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 样式
headWriteCellStyle.setFillForegroundColor(IndexedColors.YELLOW1.getIndex());
headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
headWriteCellStyle.setBorderRight(BorderStyle.THIN);
headWriteCellStyle.setBorderTop(BorderStyle.THIN);
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
headWriteCellStyle.setQuotePrefix(true);
// 设置字体
WriteFont headWriteFont = new WriteFont();
headWriteFont.setColor(IndexedColors.BLACK.getIndex());
headWriteFont.setFontHeightInPoints((short) 14);
headWriteFont.setBold(true);
headWriteFont.setFontName("微软雅黑");
headWriteCellStyle.setWriteFont(headWriteFont);
return headWriteCellStyle;
}
private WriteCellStyle setBodyWriteCellStyle(){
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
//样式
contentWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
contentWriteCellStyle.setWrapped(true);
contentWriteCellStyle.setQuotePrefix(true);
// 字体
WriteFont contentWriteFont = new WriteFont();
contentWriteFont.setFontHeightInPoints((short) 12);
contentWriteFont.setColor(IndexedColors.BLACK.getIndex());
contentWriteFont.setFontName("宋体");
contentWriteCellStyle.setWriteFont(contentWriteFont);
return contentWriteCellStyle;
}
}
避坑:
如果想要获取到输出流一定要先执行结束方法 excelWriter.finish(); 并且要关闭掉自动关闭流 不然等到回收时候再次执行excelWriter.finish();方法就会在写入流时空异常 然后抛出 ExcelGenerateException :Can not close IO.
想直接获取workbook 不在最开始放入流 这样 EasyExcelFactory.write().build(); 回收时一定会执行finish();方法 抛出异常
excelWriter.writeContext().writeWorkbookHolder().getWorkbook().write(outputStream);
问题记录:
1.多个table写入文件时、只有第一个table有样式问题
public static void main(String[] args) throws IOException {
OutputStream out = new FileOutputStream("C:\\Users\\Admin\\Desktop\\upload2.xlsx");
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
ExcelWriter excelWriter = EasyExcelFactory.write(byteArrayOutputStream)
.excelType(ExcelTypeEnum.XLSX)//写直接指定类型 因为xls sheet中会有写入限制
.head(head())//这里或者sheet上写表头就要在table中关闭表头
.needHead(Boolean.TRUE)
.autoCloseStream(false)
.build();
/**
* 多个table 在sheet上加入自动宽度策略 在table中加入样式策略 不能在sheet中加入 否则会导致table中不生效
*/
//如果想要写入多个sheet 这里就直接for创建多个 进行多次write 就可以
WriteSheet writeSheet = EasyExcel.writerSheet(0)
// .head(head())
.needHead(Boolean.FALSE)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())//设置自动宽度 这里自动宽度设置暂时没有找到按照表头宽为标准设置数据行配置
// .registerWriteHandler(createStyleStrategy1())
// .registerWriteHandler(new CellStyleWriteHandler(1,7,null))
.build();
HorizontalCellStyleStrategy horizontalCellStyleStrategy = createStyleStrategy1();
CellStyleWriteHandler cellStyleWriteHandler = new CellStyleWriteHandler(1,7,null);
/**
* 如果有多个table写入并且样式相同,只能使用一个定义类
* 如果多次new 一个新的类会导致只有第一次样式可以写入 其余table没有样式
*/
//这里可以实现分批次写入 setTableNoI(0) 数字要每次都增加
for (int i = 0; i < 3; i++) {
WriteTable writeTable = EasyExcel.writerTable()
.tableNo(i)
.needHead(Boolean.FALSE)
// .head(head())//如果每次写入都有表头或者不同表头就在这里写
// .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.registerWriteHandler(horizontalCellStyleStrategy)
.registerWriteHandler(cellStyleWriteHandler)
// .registerWriteHandler(createStyleStrategy1())
// .registerWriteHandler(new CellStyleWriteHandler(1,7,null))
.build();
excelWriter.write(data(600),writeSheet,writeTable);
}
excelWriter.finish();//这里进行关闭后才会写入流 注意执行顺序
out.write(byteArrayOutputStream.toByteArray());
out.close();
}