com.github.liaochong.myexcel.core.DefaultExcelReader Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of myexcel Show documentation
Show all versions of myexcel Show documentation
MyExcel, a new way to operate Excel.
/*
* Copyright 2019 liaochong
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package com.github.liaochong.myexcel.core;
import com.github.liaochong.myexcel.core.annotation.ExcelColumn;
import com.github.liaochong.myexcel.core.context.ReadContext;
import com.github.liaochong.myexcel.core.converter.ConvertContext;
import com.github.liaochong.myexcel.core.converter.ReadConverterContext;
import com.github.liaochong.myexcel.core.reflect.ClassFieldContainer;
import com.github.liaochong.myexcel.exception.ExcelReadException;
import com.github.liaochong.myexcel.utils.ConfigurationUtil;
import com.github.liaochong.myexcel.utils.FieldDefinition;
import com.github.liaochong.myexcel.utils.ReflectUtil;
import com.github.liaochong.myexcel.utils.StringUtil;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFAnchor;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFPicture;
import org.apache.poi.hssf.usermodel.HSSFShape;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFPicture;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.Collections;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Spliterator;
import java.util.function.BiFunction;
import java.util.function.Consumer;
import java.util.function.Function;
import java.util.function.Predicate;
import java.util.function.Supplier;
import java.util.stream.Collectors;
/**
* @author liaochong
* @version 1.0
*/
public class DefaultExcelReader {
private static final Logger log = LoggerFactory.getLogger(DefaultExcelReader.class);
private static final int DEFAULT_SHEET_INDEX = 0;
private final Class dataType;
private int sheetIndex = DEFAULT_SHEET_INDEX;
private Predicate rowFilter = row -> true;
private Predicate beanFilter = bean -> true;
private Workbook wb;
private BiFunction exceptionFunction = (e, c) -> false;
private final ReadContext readContext = new ReadContext<>(new ConvertContext(false));
private Map xssfPicturesMap = Collections.emptyMap();
private Map hssfPictureMap = Collections.emptyMap();
private boolean isXSSFSheet;
private String sheetName;
private Function trim = v -> {
if (v == null) {
return v;
}
return v.trim();
};
/**
* sheet前置处理函数
*/
private Consumer startSheetConsumer = sheet -> {
};
private DefaultExcelReader(Class dataType) {
this.dataType = dataType;
// 全局配置获取
if (dataType != Map.class) {
ClassFieldContainer classFieldContainer = ReflectUtil.getAllFieldsOfClass(dataType);
ConfigurationUtil.parseConfiguration(classFieldContainer, readContext.convertContext.configuration);
List fields = classFieldContainer.getFieldsByAnnotation(ExcelColumn.class);
fields.forEach(fieldDefinition -> {
ExcelColumn excelColumn = fieldDefinition.getField().getAnnotation(ExcelColumn.class);
if (excelColumn == null) {
return;
}
ExcelColumnMapping mapping = ExcelColumnMapping.mapping(excelColumn);
readContext.convertContext.excelColumnMappingMap.put(fieldDefinition.getField(), mapping);
});
}
}
public static DefaultExcelReader of(Class clazz) {
return new DefaultExcelReader<>(clazz);
}
public DefaultExcelReader sheet(int index) {
if (index >= 0) {
this.sheetIndex = index;
} else {
throw new IllegalArgumentException("Sheet index must be greater than or equal to 0");
}
return this;
}
public DefaultExcelReader sheet(String sheetName) {
this.sheetName = sheetName;
return this;
}
public DefaultExcelReader rowFilter(Predicate rowFilter) {
this.rowFilter = rowFilter;
return this;
}
public DefaultExcelReader beanFilter(Predicate beanFilter) {
this.beanFilter = beanFilter;
return this;
}
public DefaultExcelReader exceptionally(BiFunction exceptionFunction) {
this.exceptionFunction = exceptionFunction;
return this;
}
public DefaultExcelReader noTrim() {
this.trim = v -> v;
return this;
}
public DefaultExcelReader trimToNull() {
this.trim = StringUtil::trimToNull;
return this;
}
public DefaultExcelReader startSheet(Consumer startSheetConsumer) {
this.startSheetConsumer = startSheetConsumer;
return this;
}
public List read(InputStream fileInputStream) {
return this.read(fileInputStream, null);
}
public List read(InputStream fileInputStream, String password) {
return this.doRead(() -> getSheetOfInputStream(fileInputStream, password));
}
public List read(File file) {
return this.read(file, null);
}
public List read(File file, String password) {
return this.doRead(() -> getSheetOfFile(file, password));
}
private List doRead(Supplier sheetSupplier) {
Map fieldDefinitionMap = ReflectUtil.getFieldDefinitionMapOfExcelColumn(dataType);
if (fieldDefinitionMap.isEmpty()) {
return Collections.emptyList();
}
try {
Sheet sheet = sheetSupplier.get();
this.startSheetConsumer.accept(sheet);
return getDataFromFile(sheet, fieldDefinitionMap);
} finally {
clearWorkbook();
}
}
public void readThen(InputStream fileInputStream, Consumer consumer) {
readThen(fileInputStream, null, consumer);
}
public void readThen(InputStream fileInputStream, String password, Consumer consumer) {
this.doReadThen(() -> getSheetOfInputStream(fileInputStream, password), consumer, null);
}
public void readThen(File file, Consumer consumer) {
readThen(file, null, consumer);
}
public void readThen(File file, String password, Consumer consumer) {
this.doReadThen(() -> getSheetOfFile(file, password), consumer, null);
}
public void readThen(InputStream fileInputStream, Function function) {
readThen(fileInputStream, null, function);
}
public void readThen(InputStream fileInputStream, String password, Function function) {
this.doReadThen(() -> getSheetOfInputStream(fileInputStream, password), null, function);
}
public void readThen(File file, Function function) {
readThen(file, null, function);
}
public void readThen(File file, String password, Function function) {
this.doReadThen(() -> getSheetOfFile(file, password), null, function);
}
private void doReadThen(Supplier sheetSupplier, Consumer consumer, Function function) {
Map fieldDefinitionMap = ReflectUtil.getFieldDefinitionMapOfExcelColumn(dataType);
if (fieldDefinitionMap.isEmpty()) {
return;
}
try {
Sheet sheet = sheetSupplier.get();
readThenConsume(sheet, fieldDefinitionMap, consumer, function);
} finally {
clearWorkbook();
}
}
private void clearWorkbook() {
if (Objects.nonNull(wb)) {
try {
wb.close();
} catch (IOException e) {
throw new ExcelReadException("Close workbook failure", e);
}
}
}
private Sheet getSheetOfInputStream(InputStream fileInputStream, String password) {
try {
if (StringUtil.isBlank(password)) {
wb = WorkbookFactory.create(fileInputStream);
} else {
wb = WorkbookFactory.create(fileInputStream, password);
}
} catch (IOException | EncryptedDocumentException e) {
throw new ExcelReadException("Get sheet of excel failure", e);
}
return getSheet();
}
private Sheet getSheetOfFile(File file, String password) {
try {
if (StringUtil.isBlank(password)) {
wb = WorkbookFactory.create(file);
} else {
wb = WorkbookFactory.create(file, password);
}
} catch (IOException | EncryptedDocumentException e) {
throw new ExcelReadException("Get sheet of excel failure", e);
}
return getSheet();
}
private Sheet getSheet() {
Sheet sheet;
if (sheetName != null) {
sheet = wb.getSheet(sheetName);
if (sheet == null) {
throw new ExcelReadException("Cannot find sheet based on sheetName:" + sheetName);
}
} else {
sheet = wb.getSheetAt(sheetIndex);
}
getAllPictures(sheet);
return sheet;
}
private List getDataFromFile(Sheet sheet, Map fieldDefinitionMap) {
long startTime = System.currentTimeMillis();
final int firstRowNum = sheet.getFirstRowNum();
final int lastRowNum = sheet.getLastRowNum();
log.info("FirstRowNum:{},LastRowNum:{}", firstRowNum, lastRowNum);
if (lastRowNum < 0) {
log.info("Reading excel takes {} milliseconds", System.currentTimeMillis() - startTime);
return Collections.emptyList();
}
DataFormatter formatter = new DataFormatter();
List result = new LinkedList<>();
for (int i = firstRowNum; i <= lastRowNum; i++) {
Row row = sheet.getRow(i);
if (row == null) {
log.info("Row of {} is null,it will be ignored.", i);
continue;
}
boolean noMatchResult = rowFilter.negate().test(row);
if (noMatchResult) {
log.info("Row of {} does not meet the filtering criteria, it will be ignored.", i);
continue;
}
int lastColNum = row.getLastCellNum();
if (lastColNum < 0) {
continue;
}
T obj = instanceObj(fieldDefinitionMap, formatter, row);
if (beanFilter.test(obj)) {
result.add(obj);
}
}
log.info("Reading excel takes {} milliseconds", System.currentTimeMillis() - startTime);
return result;
}
private void readThenConsume(Sheet sheet, Map fieldDefinitionMap, Consumer consumer, Function function) {
long startTime = System.currentTimeMillis();
final int firstRowNum = sheet.getFirstRowNum();
final int lastRowNum = sheet.getLastRowNum();
log.info("FirstRowNum:{},LastRowNum:{}", firstRowNum, lastRowNum);
this.startSheetConsumer.accept(sheet);
if (lastRowNum < 0) {
log.info("Reading excel takes {} milliseconds", System.currentTimeMillis() - startTime);
return;
}
DataFormatter formatter = new DataFormatter();
for (int i = firstRowNum; i <= lastRowNum; i++) {
Row row = sheet.getRow(i);
if (row == null) {
log.info("Row of {} is null,it will be ignored.", i);
continue;
}
boolean noMatchResult = rowFilter.negate().test(row);
if (noMatchResult) {
log.info("Row of {} does not meet the filtering criteria, it will be ignored.", i);
continue;
}
int lastColNum = row.getLastCellNum();
if (lastColNum < 0) {
continue;
}
T obj = instanceObj(fieldDefinitionMap, formatter, row);
if (beanFilter.test(obj)) {
if (consumer != null) {
consumer.accept(obj);
} else if (function != null) {
Boolean noStop = function.apply(obj);
if (!noStop) {
break;
}
}
}
}
log.info("Reading excel takes {} milliseconds", System.currentTimeMillis() - startTime);
}
private void getAllPictures(Sheet sheet) {
if (sheet instanceof XSSFSheet) {
isXSSFSheet = true;
XSSFDrawing xssfDrawing = ((XSSFSheet) sheet).getDrawingPatriarch();
if (xssfDrawing == null) {
return;
}
xssfPicturesMap = xssfDrawing.getShapes()
.stream()
.filter(s -> s instanceof XSSFPicture)
.map(s -> (XSSFPicture) s)
.collect(Collectors.toMap(s -> {
XSSFClientAnchor anchor = (XSSFClientAnchor) s.getAnchor();
return anchor.getRow1() + "_" + anchor.getCol1();
}, s -> s));
} else if (sheet instanceof HSSFSheet) {
HSSFPatriarch hssfPatriarch = ((HSSFSheet) sheet).getDrawingPatriarch();
if (hssfPatriarch == null) {
return;
}
Spliterator spliterator = hssfPatriarch.spliterator();
hssfPictureMap = new HashMap<>();
spliterator.forEachRemaining(shape -> {
if (shape instanceof HSSFPicture) {
HSSFPicture picture = (HSSFPicture) shape;
HSSFAnchor anchor = picture.getAnchor();
if (anchor instanceof HSSFClientAnchor) {
int row = ((HSSFClientAnchor) anchor).getRow1();
int col = ((HSSFClientAnchor) anchor).getCol1();
hssfPictureMap.put(row + "_" + col, picture);
}
}
});
}
}
private T instanceObj(Map fieldDefinitionMap, DataFormatter formatter, Row row) {
T obj = ReflectUtil.newInstance(dataType);
fieldDefinitionMap.forEach((index, fieldDefinition) -> {
if (fieldDefinition.getField().getType() == InputStream.class) {
convertPicture(row, obj, index, fieldDefinition.getField());
return;
}
Cell cell = row.getCell(index, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
if (cell == null) {
return;
}
String content = formatter.formatCellValue(cell);
if (content == null) {
return;
}
content = trim.apply(content);
readContext.reset(obj, fieldDefinition, content, row.getRowNum(), index);
ReadConverterContext.convert(obj, readContext);
});
return obj;
}
private void convertPicture(Row row, T obj, Integer index, Field field) {
byte[] pictureData;
if (isXSSFSheet) {
XSSFPicture xssfPicture = xssfPicturesMap.get(row.getRowNum() + "_" + index);
if (xssfPicture == null) {
return;
}
pictureData = xssfPicture.getPictureData().getData();
} else {
HSSFPicture hssfPicture = hssfPictureMap.get(row.getRowNum() + "_" + index);
if (hssfPicture == null) {
return;
}
pictureData = hssfPicture.getPictureData().getData();
}
try {
field.set(obj, new ByteArrayInputStream(pictureData));
} catch (IllegalAccessException e) {
throw new ExcelReadException("Failed to read picture.", e);
}
}
}