gu.sql2java.excel.BaseExcelReader Maven / Gradle / Ivy
package gu.sql2java.excel;
import static com.google.common.base.Preconditions.checkNotNull;
import static gu.sql2java.SimpleLog.log;
import static net.gdface.bean.BeanPropertySupport.BEAN_SUPPORT;
import static net.gdface.bean.BeanPropertySupport.isEmpty;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.nio.charset.Charset;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Consumer;
import org.springframework.web.multipart.MultipartFile;
import com.google.common.base.Throwables;
import com.google.common.collect.Maps;
import gu.sql2java.excel.annotations.ExcelHandlerAdapter;
import gu.sql2java.excel.config.ExcelPropertyConfig;
import gu.sql2java.excel.config.MapExpression;
import gu.sql2java.excel.config.PropertyConfig;
import gu.sql2java.excel.config.SheetConfig;
import net.gdface.cache.FunctionCached;
/**
* EXCEL(.xls,.xlsx,.csv)读取实现基类
* @author guyadong
* @since 3.29.0
*
* @param 输入数据的行数据类型
*/
public class BaseExcelReader implements IExcelReader{
protected SheetConfig sheetConfig;
/**
* 存储导入的数据列表
*/
@SuppressWarnings("rawtypes")
private List rows;
/**
* 记录处理对象
*/
@SuppressWarnings("rawtypes")
private final Consumer beanConsumer;
/**
* 数据导入列表处理器，所有数据导入成功后调用
*/
@SuppressWarnings("rawtypes")
private Consumer rowsConsumer;
private List indexsRange;
protected Map fetchColumns;
/** 顶级字段 */
private Map topColumns;
/** '.'分割的嵌套字段 */
private Map subColumns;
/** 为{@code true} 嵌套字段优先,否则按输入字段顺序处理 */
private boolean subFirst = true;
/** 在解析表头时获取的第一行数据 */
private R firstRow;
protected boolean debugOutput = false;
/**
* @param sheetConfig
* @param rows
* @param beanConsumer 记录处理对象，为{@code null}则将记录保存到{@link #rows}
*/
public BaseExcelReader(SheetConfig sheetConfig, List rows,Consumer beanConsumer) {
this.sheetConfig = null == sheetConfig ? new SheetConfig() : sheetConfig;
this.beanConsumer = null == beanConsumer ? o->getRows().add(o): beanConsumer;
if(beanConsumer==null) {
/** 没有指定记录处理对象时，rows需要用于保存记录，不能为null */
this.rows = checkNotNull(rows,"rows is null");
}else {
this.rows = rows;
}
}
public BaseExcelReader(SheetConfig sheetConfig) {
this(sheetConfig,new ArrayList<>(),null);
}
public BaseExcelReader(SheetConfig sheetConfig,Consumer beanConsumer) {
this(sheetConfig,new ArrayList<>(),beanConsumer);
}
public BaseExcelReader(Consumer beanConsumer) {
this(null,new ArrayList<>(),beanConsumer);
}
public BaseExcelReader() {
this(null,new ArrayList<>(),null);
}
@SuppressWarnings({ "rawtypes", "unchecked" })
public BaseExcelReader(BaseExcelReader builder) {
this(builder.sheetConfig,builder.rows,builder.beanConsumer);
debugOutput(builder.debugOutput);
subFirst(builder.subFirst);
withRowsConsumer(builder.rowsConsumer);
}
/**
* 设置调试输出标志
* @param debugOutput
* @return 当前对象
* @since 3.29.7
*/
public BaseExcelReader debugOutput(Boolean debugOutput) {
if(null != debugOutput) {
this.debugOutput = debugOutput;
}
return this;
}
/**
* 打开/关闭嵌套字段优先模式,默认开启
* @param subFirst 为{@code true} 嵌套字段优先,否则按输入字段顺序处理
* @return 当前对象
* @since 3.29.8
*/
public BaseExcelReader subFirst(Boolean subFirst) {
if(null != subFirst) {
this.subFirst = subFirst;
}
return this;
}
/**
* 设置解析成功后解析数据的处理器
* @param rowsConsumer
* @return 当前对象
* @since 3.29.7
*/
@SuppressWarnings("rawtypes")
public BaseExcelReader withRowsConsumer(Consumer rowsConsumer) {
this.rowsConsumer = (Consumer)rowsConsumer;
return this;
}
@Override
public SheetConfig getSheetConfig() {
return sheetConfig;
}
@Override
public void setSheetConfig(SheetConfig sheetConfig) {
if(this.sheetConfig == null) {
this.sheetConfig = sheetConfig;
}else {
this.sheetConfig.merge(sheetConfig);
}
}
@Override
public void read(File file, Charset charset) throws IOException {
try(FileInputStream inputStream = new FileInputStream(checkNotNull(file,"file is null"))) {
log(debugOutput,"FILE: {}",file);
read(inputStream, charset, suffixOf(file.getName()));
}
}
@Override
public void read(String file, Charset charset) throws IOException {
try(FileInputStream inputStream = new FileInputStream(checkNotNull(file,"file is null"))) {
log(debugOutput,"FILE: {}",file);
read(inputStream, charset, suffixOf(file));
}
}
@Override
public void read(MultipartFile uplodFile, Charset charset) throws IOException {
try(InputStream inputStream = checkNotNull(uplodFile,"uplodFile is null").getInputStream()){
log(debugOutput,"UPLOAD FILE: {}",uplodFile.getOriginalFilename());
read(inputStream, charset, suffixOf(uplodFile.getOriginalFilename()));
}
}
/**
* 返回解析的记录列表
*/
@Override
@SuppressWarnings({ "unchecked" })
public List getRows() {
return rows;
}
@Override
public void setRows(List rows) {
if(null!= rows) {
this.rows = rows;
}
}
/**
* 遍历导入数据保存到{@link #rows}
* @param itor
*/
protected void read(Iterator itor ) {
fetchColumns = headers(itor);
topColumns = Maps.newLinkedHashMap(Maps.filterValues(fetchColumns, v->v.getImportColumnName().indexOf('.')<0)) ;
subColumns = Maps.newLinkedHashMap(Maps.filterValues(fetchColumns, v->v.getImportColumnName().indexOf('.')>0));
log(debugOutput,"header {}",Maps.transformValues(fetchColumns, p->p.getExcelColumnName()));
log(debugOutput,"top columns {}",Maps.transformValues(topColumns, p->p.getImportColumnName()));
log(debugOutput,"sub columns {}",Maps.transformValues(subColumns, p->p.getImportColumnName()));
if(firstRow!=null) {
read(firstRow);
}
while(itor.hasNext()) {
read( itor.next());
}
}
/**
* 将行数据解析为对象加入{@link #rows}
* @param r
*/
@SuppressWarnings("unchecked")
private void read(R r) {
log(debugOutput,"ROW {}",r);
Object value = injectValue(r);
log(debugOutput,"Object {}",value);
if(null!=value) {
beanConsumer.accept(value);
}
}
/**
* @since 3.29.7
*/
protected final void consumeRows() {
if(null != rowsConsumer) {
rowsConsumer.accept(getRows());
}
}
/**
* 从{@link InputStream}将导入数据记录，
* 子类必须重写此方法
* @param inputStream
* @param charset 数据编码为{@code null}使用默认值
* @param format 文件格式,即文件后缀: .xls,xlxs,.cxv
* @throws IOException
*/
@Override
public void read(InputStream inputStream, Charset charset, String format) throws IOException{
throw new UnsupportedOperationException();
}
/**
* 从单元格读取数据转为String，
* 子类必须重写此方法
* @param row
* @param idx
*/
protected String getCellAsString(R row, int idx) {
throw new UnsupportedOperationException();
}
/**
* 行由idx指定单元为空返回{@code true}，否则返回{@code false}，
* 子类必须重写此方法
* @param row
* @param idx
*/
protected boolean isEmptyCell(R row,int idx) {
throw new UnsupportedOperationException();
}
/**
* 返回列的索引范围列表，
* 子类必须重写此方法
* @param row
*/
protected List indexsOfRow(R row){
throw new UnsupportedOperationException();
}
/**
* 解析行数据生成数据对象
* @param row 行数据
*/
private Object injectValue(R row) {
if(null != row) {
try {
if(sheetConfig.getCustomImporter()!=null) {
return sheetConfig.getCustomImporter().parse(injectAsMap(row));
}else if(!sheetConfig.getBeanClass().equals(Object.class)) {
Object bean = sheetConfig.getBeanClass().newInstance();
/**
* 嵌套字段优先机制：
* 先处理顶级字段,再处理嵌套字段，
* 这样嵌套字段的值可以覆盖顶级字段JSON中存在的值
*/
if(subFirst) {
injectBean(row,bean,topColumns);
injectBean(row,bean,subColumns);
}else {
injectBean(row,bean,fetchColumns);
}
return bean;
}else {
return injectAsMap(row);
}
} catch (Exception e) {
Throwables.throwIfUnchecked(e);
throw new RuntimeException(e);
}
}
return null;
}
/**
* 解析行数据生成注入java bean对象
* @param row 行数据
* @param injectColumns 注入字段
*/
private void injectBean(R row,Object bean,MapinjectColumns) {
for(int idx:indexsRange) {
PropertyConfig p = injectColumns.get(idx);
if(null != p) {
Object cell = readFrom(row,idx,p);
if(null != cell) {
p.writeTo(bean,cell);
}
}
}
}
/**
* 将行数据转为字段名-值映射
* @param row
*/
private Map injectAsMap(R row) {
Map map = new LinkedHashMap<>();
/**
* 嵌套字段优先机制：
* 先处理顶级字段,再处理嵌套字段，
* 这样嵌套字段的值可以覆盖顶级字段JSON中存在的值
*/
if(subFirst) {
injectAsMap(row,map,topColumns);
injectAsMap(row,map,subColumns);
}else {
injectAsMap(row,map,fetchColumns);
}
return map;
}
/**
* 将行数据转为字段名-值映射
* @param row
*/
private Map injectAsMap(R row,Map map,MapinjectColumns) {
for(int idx:indexsRange) {
PropertyConfig p = injectColumns.get(idx);
if(null != p) {
Object cell = readFrom(row,idx,p);
if(null != cell) {
String name = p.getImportColumnName();
if(!isEmpty(name)) {
BEAN_SUPPORT.setProperty(map, name, String.valueOf(cell));
}
}
}
}
return map;
}
/**
* 找到表头，并从表头读取列名，返回列名-PropertyConfig映射
* @param itor
*/
private Map headers(Iterator itor){
R headerRecord = headerRecord(itor);
indexsRange = indexsOfRow(headerRecord);
Map map=new LinkedHashMap<>();
if(null!=headerRecord) {
Map columns = sheetConfig.getExcelConfigs();
if(!columns.isEmpty()) {
for(int i:indexsRange) {
String n = getCellAsString( headerRecord,i);
if(columns.containsKey(n)) {
PropertyConfig p = columns.get(n);
p.getColumnConfig().setSort(i);
map.put(i,p);
}
}
}else if (sheetConfig.getBeanClass().equals(Object.class)) {
for(int i:indexsRange) {
String n = getCellAsString( headerRecord,i);
map.put(i,new ExcelPropertyConfig(n,null, i));
}
}else {
throw new IllegalStateException("CAN NOT COMPUTE field map for " + sheetConfig.getBeanClass().getName());
}
}else {
/** 读取表头失败 */
throw new IllegalStateException("FAILT TO HEADER OF TAB");
}
return map;
}
private R headerRecord(Iterator itor) {
R c0 = nextNoempty(itor);
if(c0 != null) {
R c1 = nextNoempty(itor);
if(c1==null) {
/** 只有一行作为表头 */
return c0;
}
if(hasEmptyCell(c0)) {
/** 第一行有空单元则第二行是表头 */
return c1;
}else {
firstRow = c1;
/** 第一行是表头 */
return c0;
}
}
return null;
}
/**
* 非全空的行返回{@code true}
* @param record
*/
private boolean noemptyRow(R record) {
if(null!= record) {
for(int i:indexsOfRow(record)) {
if(!isEmptyCell(record, i)){
return true;
}
}
}
return false;
}
private R nextNoempty(Iterator itor) {
if(null!=itor) {
while (itor.hasNext()) {
R r = itor.next();
if(noemptyRow(r)) {
return r;
}
}
}
return null;
}
/**
* 存在空单元返回{@code true}
* @param record
*/
private boolean hasEmptyCell(R record) {
if(null != record) {
for(int i:indexsOfRow(record)) {
if(isEmptyCell(record, i)){
return true;
}
}
return moreEmptyCheck(record);
}
return true;
}
protected boolean moreEmptyCheck(R row) {
return false;
}
private static final
FunctionCached,ExcelHandlerAdapter>
handlerAdapterCache = FunctionCached.of(c->{
try {
return c.newInstance();
} catch (Exception e) {
Throwables.throwIfUnchecked(e);
throw new RuntimeException(e);
}
});
@SuppressWarnings("unchecked")
protected Object converExp(Object s, PropertyConfig p) {
if(null!=p) {
if(!p.getColumnConfig().getReadConverterExp().isEmpty()) {
return MapExpression.reverseByExp(s, p.getColumnConfig().getReadConverterExp(), ",");
}
Class handlerClass = p.getColumnConfig().getHandler();
if(handlerClass != ExcelHandlerAdapter.class && ExcelHandlerAdapter.class.isAssignableFrom(handlerClass)){
String[] args = p.getColumnConfig().getArgs();
return handlerAdapterCache.get((Class) handlerClass).unformat(s, args);
}
}
return s;
}
/**
* 从行记录指定的索引读取单元数据
* @param record
* @param idx
* @param p
*/
private Object readFrom(R record,int idx,PropertyConfig p ) {
String s = getCellAsString(record,idx);
String defValue;
/** 字段值未定义时，如果字段配置中有定义默认值则使用默认值 */
if(null == s && !(defValue = p.getColumnConfig().getDefaultValue()).isEmpty()) {
s = defValue;
}
return converExp(s,p);
}
/**
* 返回输入文件名的后缀(转小写),即最后一个'.'及后面的内容,
* 如果输入字符串不包含'.'返回{@code null}
* @param input 为{@code null}返回{@code null}
*/
public static String suffixOf(String input) {
String suffix = null;
if(null != input) {
int idx = input.lastIndexOf('.');
if(idx >= 0) {
suffix = input.substring(idx).toLowerCase();
}
}
return suffix;
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy