Introduction to EasyExcel
The well-known frameworks for Java parsing and generating Excel include Apache poi and jxl. But they all have a serious problem that consumes a lot of memory. POI has a SAX mode API that can solve some memory overflow problems to a certain extent, but POI still has some defects, such as Excel version 07 decompression and storage after decompression. It is done in memory, and the memory consumption is still very large.
easyexcel rewrote poi’s analysis of Excel version 07, a 3M excel with POI sax analysis still needs about 100M memory, using easyexcel can reduce it to a few M, and no matter how big the excel is, there will be no memory overflow; version 03 depends on The sax mode of POI has encapsulated the model conversion on the upper layer, making it easier and more convenient for users
Website
- Official website: https://easyexcel.opensource.alibaba.com/
- github address: https://github.com/alibaba/easyexcel
- gitee address: https://gitee.com/easyexcel/easyexcel
Data import
demo
@PostMapping(value = "/t3", consumes = MediaType. MULTIPART_FORM_DATA_VALUE)
public void t3(@RequestPart("file") MultipartFile file) throws IOException {<!-- -->
ReadListener<OrderExcel> readListener = new ReadListener<OrderExcel>() {<!-- -->
@Override
public void invoke(OrderExcel orderExcel, AnalysisContext analysisContext) {<!-- -->
System.out.println(orderExcel);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {<!-- -->
System.out.println(analysisContext);
}
};
EasyExcel.read(file.getInputStream(), OrderExcel.class, readListener).sheet().doRead();
}
}
Source code interpretation
EasyExcelFactory#read(InputStream, Class, ReadListener)
public static ExcelReaderBuilder read(InputStream inputStream, Class head, ReadListener readListener) {<!-- -->
ExcelReaderBuilder excelReaderBuilder = new ExcelReaderBuilder();
excelReaderBuilder.file(inputStream);
if (head != null) {<!-- -->
excelReaderBuilder. head(head);
}
if (readListener != null) {<!-- -->
excelReaderBuilder.registerReadListener(readListener);
}
return excelReaderBuilder;
}
ExcelReaderBuilder#sheet(), set the sheet.
public ExcelReaderSheetBuilder sheet() {<!-- -->
return this.sheet((Integer)null, (String)null);
}
ExcelReaderSheetBuilder#doRead, perform a read operation.
public void doRead() {<!-- -->
if (this. excelReader == null) {<!-- -->
throw new ExcelGenerateException("Must use 'EasyExcelFactory.read().sheet()' to call this method");
} else {<!-- -->
this.excelReader.read(new ReadSheet[]{<!-- -->this.build()});
this.excelReader.finish();
}
}
ExcelReader#read(ReadSheet...), read.
public ExcelReader read(ReadSheet... readSheet) {<!-- -->
return this. read(Arrays. asList(readSheet));
}
public ExcelReader read(List<ReadSheet> readSheetList) {<!-- -->
this.excelAnalyser.analysis(readSheetList, Boolean.FALSE);
return this;
}
ExcelAnalyserImpl#analysis, call this.excelReadExecutor.execute();
public void analysis(List<ReadSheet> readSheetList, Boolean readAll) {<!-- -->
try {<!-- -->
if (!readAll & amp; & amp; CollectionUtils.isEmpty(readSheetList)) {<!-- -->
throw new IllegalArgumentException("Specify at least one read sheet.");
} else {<!-- -->
this.analysisContext.readWorkbookHolder().setParameterSheetDataList(readSheetList);
this.analysisContext.readWorkbookHolder().setReadAll(readAll);
try {<!-- -->
this.excelReadExecutor.execute();
} catch (ExcelAnalysisStopException var4) {<!-- -->
if (LOGGER.isDebugEnabled()) {<!-- -->
LOGGER. debug("Custom stop!");
}
}
}
} catch (RuntimeException var5) {<!-- -->
this. finish();
throw var5;
} catch (Throwable var6) {<!-- -->
this. finish();
throw new ExcelAnalysisException(var6);
}
}
DefaultAnalysisEventProcessor#endRow, call the listeners one by one to process the data. If ReadListener is not set, then there is only ModelBuildEventListener in the listener collection.
@Override
public void endRow(AnalysisContext analysisContext) {<!-- -->
if (RowTypeEnum.EMPTY.equals(analysisContext.readRowHolder().getRowType())) {<!-- -->
if (LOGGER.isDebugEnabled()) {<!-- -->
LOGGER. debug("Empty row!");
}
if (analysisContext. readWorkbookHolder(). getIgnoreEmptyRow()) {<!-- -->
return;
}
}
dealData(analysisContext);
}
private void dealData(AnalysisContext analysisContext) {<!-- -->
ReadRowHolder readRowHolder = analysisContext. readRowHolder();
Map<Integer, ReadCellData<?>> cellDataMap = (Map)readRowHolder.getCellMap();
readRowHolder.setCurrentRowAnalysisResult(cellDataMap);
int rowIndex = readRowHolder. getRowIndex();
int currentHeadRowNumber = analysisContext.readSheetHolder().getHeadRowNumber();
boolean isData = rowIndex >= currentHeadRowNumber;
// Last head column
if (!isData & amp; & amp; currentHeadRowNumber == rowIndex + 1) {<!-- -->
buildHead(analysisContext, cellDataMap);
}
// Now is data
for (ReadListener readListener : analysisContext.currentReadHolder().readListenerList()) {<!-- -->
try {<!-- -->
if (isData) {<!-- -->
readListener.invoke(readRowHolder.getCurrentRowAnalysisResult(), analysisContext);
} else {<!-- -->
readListener.invokeHead(cellDataMap, analysisContext);
}
} catch (Exception e) {<!-- -->
onException(analysisContext, e);
break;
}
if (!readListener.hasNext(analysisContext)) {<!-- -->
throw new ExcelAnalysisStopException();
}
}
}
ModelBuildEventListener#invoke, the ModelBuildEventListener model build event listener is used to build the model. buildUserModel is mainly to obtain the incoming class information and instantiate it. Convert entity class to Map, BeanMap dataMap = BeanMapUtils.create(resultModel);.
@Override
public void invoke(Map<Integer, ReadCellData<?>> cellDataMap, AnalysisContext context) {<!-- -->
ReadSheetHolder readSheetHolder = context. readSheetHolder();
if (HeadKindEnum.CLASS.equals(readSheetHolder.excelReadHeadProperty().getHeadKind())) {<!-- -->
context. readRowHolder()
.setCurrentRowAnalysisResult(buildUserModel(cellDataMap, readSheetHolder, context));
return;
}
context.readRowHolder().setCurrentRowAnalysisResult(buildStringList(cellDataMap, readSheetHolder, context));
}
private Object buildUserModel(Map<Integer, ReadCellData<?>> cellDataMap, ReadSheetHolder readSheetHolder,
AnalysisContext context) {<!-- -->
ExcelReadHeadProperty excelReadHeadProperty = readSheetHolder.excelReadHeadProperty();
Object resultModel;
try {<!-- -->
resultModel = excelReadHeadProperty.getHeadClazz().newInstance();
} catch (Exception e) {<!-- -->
throw new ExcelDataConvertException(context. readRowHolder(). getRowIndex(), 0,
new ReadCellData<>(CellDataTypeEnum. EMPTY), null,
"Can not instance class: " + excelReadHeadProperty. getHeadClazz(). getName(), e);
}
Map<Integer, Head> headMap = excelReadHeadProperty. getHeadMap();
BeanMap dataMap = BeanMapUtils.create(resultModel);
for (Map.Entry<Integer, Head> entry : headMap.entrySet()) {<!-- -->
Integer index = entry. getKey();
Head head = entry. getValue();
String fieldName = head. getFieldName();
if (!cellDataMap. containsKey(index)) {<!-- -->
continue;
}
ReadCellData<?> cellData = cellDataMap.get(index);
Object value = ConverterUtils.convertToJavaObject(cellData, head.getField(),
ClassUtils.declaredExcelContentProperty(dataMap, readSheetHolder.excelReadHeadProperty().getHeadClazz(),
fieldName), readSheetHolder.converterMap(), context, context.readRowHolder().getRowIndex(), index);
if (value != null) {<!-- -->
dataMap. put(fieldName, value);
}
}
return resultModel;
}
ConverterUtils#doConvertToJavaObject, get Converter, and perform the conversion operation according to the converter on the property.
private static Object doConvertToJavaObject(ReadCellData<?> cellData, Class<?> clazz,
ExcelContentProperty contentProperty, Map<ConverterKey, Converter<?>> converterMap, AnalysisContext context,
Integer rowIndex, Integer columnIndex) {<!-- -->
Converter<?> converter = null;
if (contentProperty != null) {<!-- -->
converter = contentProperty. getConverter();
}
boolean canNotConverterEmpty = cellData.getType() == CellDataTypeEnum.EMPTY
& amp; & amp; !(converter instanceof NullableObjectConverter);
if (canNotConverterEmpty) {<!-- -->
return null;
}
if (converter == null) {<!-- -->
converter = converterMap.get(ConverterKeyBuild.buildKey(clazz, cellData.getType()));
}
if (converter == null) {<!-- -->
throw new ExcelDataConvertException(rowIndex, columnIndex, cellData, contentProperty,
"Converter not found, convert " + cellData. getType() + " to " + clazz. getName());
}
try {<!-- -->
return converter. convertToJavaData(new ReadConverterContext<>(cellData, contentProperty, context));
} catch (Exception e) {<!-- -->
throw new ExcelDataConvertException(rowIndex, columnIndex, cellData, contentProperty,
"Convert data " + cellData + " to " + clazz + " error ", e);
}
}
The use of BeanMap modifies the data of the entity class by setting the key in the Map.
public static void main(String[] args) {<!-- -->
OrderExcel orderExcel = new OrderExcel();
BeanMap beanMap = BeanMapUtils.create(orderExcel);
beanMap.put("orderNo", "111");
System.out.println(beanMap);
System.out.println(orderExcel);
}
Data export
demo
@PostMapping("/t4")
public void t4(HttpServletResponse response) throws Exception {<!-- -->
OrderExcel orderExcel = new OrderExcel();
List<OrderExcel> orderExcels = Arrays. asList(orderExcel);
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// Here URLEncoder.encode can prevent Chinese garbled characters, of course it has nothing to do with easyexcel
//"Test": is the name of the document we want to generate, you can change it to your own
String fileName = URLEncoder.encode("Test", "UTF-8").replaceAll("\ + ", " ");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), OrderExcel.class).sheet("template").doWrite(orderExcels);
}
Source code interpretation
ExcelWriterSheetBuilder#doWrite(java.util.Collection), perform write operation
public void doWrite(Collection<?> data) {<!-- -->
if (excelWriter == null) {<!-- -->
throw new ExcelGenerateException("Must use 'EasyExcelFactory.write().sheet()' to call this method");
}
excelWriter.write(data, build());
excelWriter. finish();
}
ExcelWriter#write(Collection, WriteSheet), call the built-in ExcelBuilder
public ExcelWriter write(Collection<?> data, WriteSheet writeSheet) {<!-- -->
return write(data, writeSheet, null);
}
public ExcelWriter write(Collection<?> data, WriteSheet writeSheet, WriteTable writeTable) {<!-- -->
excelBuilder. addContent(data, writeSheet, writeTable);
return this;
}
ExcelBuilderImpl#addContent(), create ExcelWriteAddExecutor to add.
@Override
public void addContent(Collection<?> data, WriteSheet writeSheet, WriteTable writeTable) {<!-- -->
try {<!-- -->
context.currentSheet(writeSheet, WriteTypeEnum.ADD);
context. currentTable(writeTable);
if (excelWriteAddExecutor == null) {<!-- -->
excelWriteAddExecutor = new ExcelWriteAddExecutor(context);
}
excelWriteAddExecutor.add(data);
} catch (RuntimeException e) {<!-- -->
finishOnException();
throw e;
} catch (Throwable e) {<!-- -->
finishOnException();
throw new ExcelGenerateException(e);
}
}
ExcelWriteAddExecutor#add, add data to WorkBook.
public void add(Collection<?> data) {<!-- -->
if (CollectionUtils. isEmpty(data)) {<!-- -->
data = new ArrayList<>();
}
WriteSheetHolder writeSheetHolder = writeContext.writeSheetHolder();
int newRowIndex = writeSheetHolder.getNewRowIndexAndStartDoWrite();
if (writeSheetHolder.isNew() & amp; & amp; !writeSheetHolder.getExcelWriteHeadProperty().hasHead()) {<!-- -->
newRowIndex += writeContext.currentWriteHolder().relativeHeadRowIndex();
}
// BeanMap is out of order, so use sortedAllFieldMap
Map<Integer, Field> sortedAllFieldMap = new TreeMap<>();
int relativeRowIndex = 0;
for (Object oneRowData : data) {<!-- -->
int lastRowIndex = relativeRowIndex + newRowIndex;
addOneRowOfDataToExcel(oneRowData, lastRowIndex, relativeRowIndex, sortedAllFieldMap);
relativeRowIndex++;
}
}
private void addOneRowOfDataToExcel(Object oneRowData, int rowIndex, int relativeRowIndex,
Map<Integer, Field> sortedAllFieldMap) {<!-- -->
if (oneRowData == null) {<!-- -->
return;
}
RowWriteHandlerContext rowWriteHandlerContext = WriteHandlerUtils.createRowWriteHandlerContext(writeContext,
rowIndex, relativeRowIndex, Boolean. FALSE);
WriteHandlerUtils.beforeRowCreate(rowWriteHandlerContext);
Row row = WorkBookUtil.createRow(writeContext.writeSheetHolder().getSheet(), rowIndex);
rowWriteHandlerContext.setRow(row);
WriteHandlerUtils.afterRowCreate(rowWriteHandlerContext);
if (oneRowData instanceof Collection<?>) {<!-- -->
addBasicTypeToExcel(new CollectionRowData((Collection<?>)oneRowData), row, rowIndex, relativeRowIndex);
} else if (oneRowData instanceof Map) {<!-- -->
addBasicTypeToExcel(new MapRowData((Map<Integer, ?>)oneRowData), row, rowIndex, relativeRowIndex);
} else {<!-- -->
addJavaObjectToExcel(oneRowData, row, rowIndex, relativeRowIndex, sortedAllFieldMap);
}
WriteHandlerUtils.afterRowDispose(rowWriteHandlerContext);
}
ExcelWriteAddExecutor#addJavaObjectToExcel, add java entity class to Excel China.
private void addJavaObjectToExcel(Object oneRowData, Row row, int rowIndex, int relativeRowIndex,
Map<Integer, Field> sortedAllFieldMap) {<!-- -->
WriteHolder currentWriteHolder = writeContext. currentWriteHolder();
BeanMap beanMap = BeanMapUtils.create(oneRowData);
// Bean the contains of the Map Key method with poor performance, So to create a keySet here
Set<String> beanKeySet = new HashSet<>(beanMap. keySet());
Set<String> beanMapHandledSet = new HashSet<>();
int maxCellIndex = -1;
// If it's a class it needs to be cast by type
if (HeadKindEnum.CLASS.equals(writeContext.currentWriteHolder().excelWriteHeadProperty().getHeadKind())) {<!-- -->
Map<Integer, Head> headMap = writeContext.currentWriteHolder().excelWriteHeadProperty().getHeadMap();
for (Map.Entry<Integer, Head> entry : headMap.entrySet()) {<!-- -->
int columnIndex = entry. getKey();
Head head = entry. getValue();
String name = head. getFieldName();
if (!beanKeySet. contains(name)) {<!-- -->
continue;
}
ExcelContentProperty excelContentProperty = ClassUtils.declaredExcelContentProperty(beanMap,
currentWriteHolder.excelWriteHeadProperty().getHeadClazz(), name);
CellWriteHandlerContext cellWriteHandlerContext = WriteHandlerUtils.createCellWriteHandlerContext(
writeContext, row, rowIndex, head, columnIndex, relativeRowIndex, Boolean.FALSE,
excelContentProperty);
WriteHandlerUtils.beforeCellCreate(cellWriteHandlerContext);
Cell cell = WorkBookUtil.createCell(row, columnIndex);
cellWriteHandlerContext.setCell(cell);
WriteHandlerUtils.afterCellCreate(cellWriteHandlerContext);
cellWriteHandlerContext.setOriginalValue(beanMap.get(name));
cellWriteHandlerContext.setOriginalFieldClass(head.getField().getType());
converterAndSet(cellWriteHandlerContext);
WriteHandlerUtils.afterCellDispose(cellWriteHandlerContext);
beanMapHandledSet.add(name);
maxCellIndex = Math.max(maxCellIndex, columnIndex);
}
}
// Finish
if (beanMapHandledSet. size() == beanMap. size()) {<!-- -->
return;
}
maxCellIndex++;
Map<String, Field> ignoreMap = writeContext.currentWriteHolder().excelWriteHeadProperty().getIgnoreMap();
initSortedAllFieldMapFieldList(oneRowData. getClass(), sortedAllFieldMap);
for (Map.Entry<Integer, Field> entry : sortedAllFieldMap.entrySet()) {<!-- -->
Field field = entry. getValue();
String fieldName = FieldUtils. resolveCglibFieldName(field);
boolean uselessData = !beanKeySet.contains(fieldName) || beanMapHandledSet.contains(fieldName)
|| ignoreMap.containsKey(fieldName);
if (uselessData) {<!-- -->
continue;
}
Object value = beanMap. get(fieldName);
ExcelContentProperty excelContentProperty = ClassUtils.declaredExcelContentProperty(beanMap,
currentWriteHolder.excelWriteHeadProperty().getHeadClazz(), fieldName);
CellWriteHandlerContext cellWriteHandlerContext = WriteHandlerUtils.createCellWriteHandlerContext(
writeContext, row, rowIndex, null, maxCellIndex, relativeRowIndex, Boolean. FALSE, excelContentProperty);
WriteHandlerUtils.beforeCellCreate(cellWriteHandlerContext);
// fix https://github.com/alibaba/easyexcel/issues/1870
// If there is data, it is written to the next cell
Cell cell = WorkBookUtil.createCell(row, maxCellIndex);
cellWriteHandlerContext.setCell(cell);
WriteHandlerUtils.afterCellCreate(cellWriteHandlerContext);
cellWriteHandlerContext.setOriginalValue(value);
cellWriteHandlerContext.setOriginalFieldClass(FieldUtils.getFieldClass(beanMap, fieldName, value));
converterAndSet(cellWriteHandlerContext);
WriteHandlerUtils.afterCellDispose(cellWriteHandlerContext);
maxCellIndex++;
}
}
AbstractExcelWriteExecutor#doConvert, get the converter on the corresponding attribute, if not, get the default converter of converterMap, and call the converter to convert.
private WriteCellData<?> doConvert(CellWriteHandlerContext cellWriteHandlerContext) {<!-- -->
ExcelContentProperty excelContentProperty = cellWriteHandlerContext. getExcelContentProperty();
Converter<?> converter = null;
if (excelContentProperty != null) {<!-- -->
converter = excelContentProperty. getConverter();
}
if (converter == null) {<!-- -->
// csv is converted to string by default
if (writeContext.writeWorkbookHolder().getExcelType() == ExcelTypeEnum.CSV) {<!-- -->
cellWriteHandlerContext.setTargetCellDataType(CellDataTypeEnum.STRING);
}
converter = writeContext.currentWriteHolder().converterMap().get(
ConverterKeyBuild.buildKey(cellWriteHandlerContext.getOriginalFieldClass(),
cellWriteHandlerContext.getTargetCellDataType()));
}
if (cellWriteHandlerContext.getOriginalValue() == null & amp; & amp; !(converter instanceof NullableObjectConverter)) {<!-- -->
return new WriteCellData<>(CellDataTypeEnum.EMPTY);
}
if (converter == null) {<!-- -->
throw new ExcelWriteDataConvertException(cellWriteHandlerContext,
"Can not find 'Converter' support class " + cellWriteHandlerContext.getOriginalFieldClass()
.getSimpleName() + ".");
}
WriteCellData<?> cellData;
try {<!-- -->
cellData = ((Converter<Object>)converter).convertToExcelData(
new WriteConverterContext<>(cellWriteHandlerContext.getOriginalValue(), excelContentProperty,
writeContext));
} catch (Exception e) {<!-- -->
throw new ExcelWriteDataConvertException(cellWriteHandlerContext,
"Convert data:" + cellWriteHandlerContext.getOriginalValue() + " error, at row:"
+ cellWriteHandlerContext.getRowIndex(), e);
}
if (cellData == null || cellData.getType() == null) {<!-- -->
throw new ExcelWriteDataConvertException(cellWriteHandlerContext,
"Convert data:" + cellWriteHandlerContext. getOriginalValue()
+ " return is null or return type is null, at row:"
+ cellWriteHandlerContext. getRowIndex());
}
return cellData;
}
