• Home
• com.gitee.l0km
• sql2java-excel
• 4.4.0
• source code
• ExcelReader.java

×

Project download

Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $

You can buy this project and download/modify it how often you want.

gu.sql2java.excel.ExcelReader Maven / Gradle / Ivy

package gu.sql2java.excel;

import static com.google.common.base.Preconditions.checkNotNull;
import static com.google.common.base.Preconditions.checkArgument;
import static com.google.common.base.Preconditions.checkState;
import static gu.sql2java.SimpleLog.log;
import static gu.sql2java.excel.BaseExcelReader.suffixOf;
import static net.gdface.bean.BeanPropertySupport.isEmpty;
import static com.google.common.base.Strings.nullToEmpty;
import static com.google.common.base.MoreObjects.firstNonNull;

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.Arrays;
import java.util.Collection;
import java.util.List;
import java.util.function.Consumer;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import com.google.common.base.Predicates;
import com.google.common.collect.ContiguousSet;
import com.google.common.collect.DiscreteDomain;
import com.google.common.collect.Iterables;
import com.google.common.collect.Lists;
import com.google.common.collect.Range;
import com.google.common.primitives.Ints;

import gu.sql2java.excel.config.SheetConfig;

/**
 * EXCEL(.xls,.xlsx,.csv)文件导入,支持多工作薄导入

 * 
 * @author guyadong
 * @since 3.29.0
 *
 */
public final class ExcelReader implements IExcelReader {
	@SuppressWarnings("rawtypes")
	private final ArrayList builders = Lists.newArrayList();
	private Collection readerWorkSort;
	private Boolean debugOutput;
	/**
	 * 默认构造方法，创建第一个工作薄配置
	 */
	public ExcelReader() {
		this(null,new ArrayList<>(),null);
	}

	/**
	 * 构造方法
	 * 创建第一个工作薄配置
	 * @param beanConsumer
	 */
	public ExcelReader(Consumer beanConsumer) {
		this(null,new ArrayList<>(),beanConsumer);
	}

	/**
	 * 构造方法
	 * 创建第一个工作薄配置
	 * @param sheetConfig
	 * @param beanConsumer
	 */
	public ExcelReader(SheetConfig sheetConfig, Consumer beanConsumer) {
		this(sheetConfig,new ArrayList<>(),beanConsumer);
	}

	/**
	 * 构造方法
	 * 创建第一个工作薄配置
	 * @param sheetConfig
	 * @param rows
	 * @param beanConsumer
	 */
	public ExcelReader(SheetConfig sheetConfig, List rows, Consumer beanConsumer) {
		configSheet(sheetConfig, rows, beanConsumer,0);
	}

	public ExcelReader(SheetConfig sheetConfig) {
		this(sheetConfig,new ArrayList<>(),null);
	}
	public ExcelReader (BaseExcelReader builder) {
		configSheet(builder,0);
	}

	/**
	 * 设置调试输出标志
	 * @param debugOutput
	 */
	public ExcelReader debugOutput(Boolean debugOutput) {
		this.debugOutput = debugOutput;
		return this;
	}
	/**
	 * 打开/关闭嵌套字段优先模式,默认开启
	 * @param subFirst 为{@code true} 嵌套字段优先,否则按输入字段顺序处理
	 * @param indexes 工作薄解析配置对象索引
	 * @return 当前对象
	 * @since 3.29.9
	 */
	public ExcelReader subFirst(boolean subFirst,int...indexes) {
		if(indexes != null) {
			for(int index:indexes) {
				if(index >= 0 && index < builders.size()) {
					getBuilder(index).subFirst(subFirst);
				}
			}
		}
		return this;
	}
	/**
	 * 打开/关闭嵌套字段优先模式,默认开启
	 * @param subFirst 为{@code true} 嵌套字段优先,否则按输入字段顺序处理
	 * @param names 工作薄解析配置对象对应的工作薄名称
	 * @return 当前对象
	 * @since 3.29.9
	 */
	public ExcelReader subFirst(boolean subFirst,String...names) {
		if(names != null) {
			for(String name:names) {
				if(!isEmpty(name)) {
					BaseExcelReader builder = getBuilderOrNull(name);
					if(null != builder) {
						builder.subFirst(subFirst);
					}
				}
			}
		}
		return this;
	}
	/**
	 * 多工作薄导入的情况下，
	 * 指定工作薄解析配置的执行顺序，如果不指定则从0开始顺序执行 
	 * @param indexes
	 * @return 当前对象
	 * @since 3.29.7
	 */
	public ExcelReader readerWorkSort(Iterable indexes) {
		if(null != indexes) {
			
			this.readerWorkSort = Lists.newArrayList(Iterables.filter(indexes, Predicates.notNull())) ;
		}
		return this;
	}
	/**
	 * 多工作薄导入的情况下，
	 * 指定工作薄解析配置的执行顺序，如果不指定则从0开始顺序执行 
	 * @param indexes
	 * @return 当前对象
	 * @since 3.29.7
	 */
	public ExcelReader readerWorkSort(int... indexes) {
		if(null != indexes) {
			this.readerWorkSort = Ints.asList(indexes);
		}
		return this;
	}

	/**
	 * 顺序设置工作薄解析配置对象
	 * @param readers
	 * @return 当前对象
	 * @since 3.29.8
	 */
	public ExcelReader configSheet(Iterable> readers) {
		if(null != readers) {
			builders.clear();
			builders.addAll(readers instanceof Collection 
					? (Collection>)readers
					: Lists.newArrayList(readers));
		}
		return this;
	}
	/**
	 * 顺序设置工作薄解析配置对象
	 * @param readers
	 * @return 当前对象
	 * @since 3.29.8
	 */
	public ExcelReader configSheet(BaseExcelReader... readers) {
		if(null != readers) {
			configSheet(Arrays.asList(readers));
		}
		return this;
	}
	/**
	 * 为指定索引设置工作薄解析配置
	 * @param reader
	 * @param index 指定配置索引位置(0-based) 
	 * @return 当前对象
	 * @since 3.29.7
	 */
	public >ExcelReader configSheet(B reader, int index) {
		checkArgument(index>=0,"INVALID index %s",index);
		for(int i = builders.size();i<=index;++i) {
			builders.add(null);
		}
		builders.set(index, checkNotNull(reader,"builder is null"));
		return this;
	}
	/**
	 * 为指定索引设置工作薄解析配置
	 * @param sheetConfig
	 * @param rows 工作薄的解析记录保存列表
	 * @param beanConsumer
	 * @param index 指定配置索引位置(0-based) 
	 * @return 当前对象
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public ExcelReader configSheet(SheetConfig sheetConfig, List rows, Consumer beanConsumer, int index) {
		BaseExcelReader builder = new BaseExcelReader(sheetConfig, rows, beanConsumer);
		configSheet(builder,index);
		return this;
	}
	/**
	 * 为指定索引设置工作薄解析配置
	 * @param sheetConfig
	 * @param beanConsumer
	 * @param index 0-based index
	 * @return 当前对象
	 * @see #configSheet(SheetConfig, List, Consumer, int)
	 */
	public ExcelReader configSheet(SheetConfig sheetConfig,Consumer beanConsumer, int index) {
		configSheet(sheetConfig, new ArrayList<>(), beanConsumer, index);
		return this;
	}
	/**
	 * 为指定索引设置工作薄解析配置
	 * @param sheetConfig
	 * @param index 0-based index
	 * @return 当前对象
	 * @see #configSheet(SheetConfig, List, Consumer, int)
	 */
	public ExcelReader configSheet(SheetConfig sheetConfig, int index) {
		return configSheet(sheetConfig, new ArrayList<>(),null, index);
	}
	/**
	 * 为指定索引设置工作薄解析配置
	 * @param beanConsumer
	 * @param index 0-based index
	 * @return 当前对象
	 * @see #configSheet(SheetConfig, List, Consumer, int)
	 */
	public ExcelReader configSheet(Consumer beanConsumer, int index) {
		return configSheet(new SheetConfig(), new ArrayList<>(),beanConsumer, index);
	}
	/**
	 * 从{@link InputStream}将导入数据记录

	 * 多工作薄导入时，如果解析配置{@link SheetConfig} 指定了工作薄名称(参见 {@code SheetConfig.sheetName }字段 )，优先使用SheetName查找工作薄
	 * 如果没有指定工作薄名，则查找对应的索引获取 Sheet读取数据
	 * @param inputStream
	 * @param format 文件格式,即文件后缀: .xls,xlxs,.cxv
	 * @throws IOException
	 */
	@SuppressWarnings("rawtypes")
	public void read(InputStream inputStream, Charset charset, String format) throws IOException {
		switch(nullToEmpty(format).toLowerCase()) {
		case ".csv":
			new CSVReader(checkNotNull(builders.get(0),"Sheet Config(0) is null"))
				.debugOutput(debugOutput)
				.read(inputStream, charset, format);
		break;
		case ".xls":
		case ".xlsx":{
			try(Workbook workbook = createWorkbook(inputStream, format)){
				if(readerWorkSort == null) {
					/** 如果没有指定工作薄解析配置的执行顺序则从0开始顺序执行 */
					readerWorkSort = ContiguousSet.create(Range.closedOpen(0, builders.size()),
							DiscreteDomain.integers());
				}
				for(Integer i:readerWorkSort) {
					checkArgument(i>=0&&i=0 AND < %s required",i,builders.size());
					BaseExcelReader builder = builders.get(i);
					if(null == builder) {
						continue;
					}
					SheetReader sheetReader = builder instanceof SheetReader 
							? (SheetReader)builder
							: new SheetReader(builder);
					sheetReader.debugOutput(debugOutput);
					Sheet sheet = null;
					// 如果指定了工作薄名称，优先使用SheetName查找工作薄
					String sheetName = builder.getSheetConfig().getSheetName();
					if(!"exportedExcel".equals(sheetName) && !isEmpty(sheetName)) {
						sheet  = workbook.getSheet(sheetName);
					}
					if(null == sheet && i < workbook.getNumberOfSheets()) {
						sheet = workbook.getSheetAt(i);
					}
					checkState(null != sheet,"NOT FOUND Sheet for SheetConfig "+i);
					log("READ Sheet name '{}' index= {}",sheetName,i);
					sheetReader.read(sheet);
				}
			}
			break;
		}
		default:
			throw new  IOException("UNSUPPORTED format :"+format);
		}
	}
	static Workbook createWorkbook(InputStream inputStream, String format) throws IOException {
		switch(nullToEmpty(format).toLowerCase()) {
		case ".xls":
			return new HSSFWorkbook(inputStream);
		case ".xlsx":
			return new XSSFWorkbook(inputStream);
		default:
			throw new  IOException("UNSUPPORTED format :"+format);
		}
	}
	/**
	 * 设置第一个工作薄解析配置对象的解析记录保存列表为{@code null}忽略
	 */
	@Override
	public  void setRows(List rows) {
		setRows(rows, 0);
	}

	/**
	 * 返回第一个工作薄解析配置对象的解析记录保存列表
	 */
	@Override
	public  List getRows() {
		return getRows(0);
	}
	/**
	 * 设置索引指定的工作薄的解析记录保存列表为{@code null}忽略，
	 * 索引无效抛出异常
	 * @param 
	 * @param rows
	 * @param index
	 * @return 当前对象
	 */
	public  ExcelReader setRows(List rows,int index) {
		getBuilder(index).setRows(rows);
		return this;
	}
	/**
	 * 设置工作薄名指定的工作薄的解析记录保存列表为{@code null}忽略，
	 * 按工作薄名无法找到工作薄则抛出异常
	 * @param 
	 * @param rows
	 * @param sheetName 工作薄名
	 * @return 当前对象
	 */
	public  ExcelReader setRows(List rows,String sheetName) {
		getBuilder(sheetName).setRows(rows);
		return this;
	}
	
	/**
	 * 返回索引指定的工作薄的解析记录保存列表，
	 * 索引无效抛出异常
	 * @param 
	 * @param index
	 */
	public  List getRows(int index) {
		return getBuilder(index).getRows();
	}
	/**
	 * 返回工作薄名指定的工作薄的解析记录保存列表，
	 * 按工作薄名无法找到工作薄则抛出异常
	 * @param 
	 * @param sheetName
	 */
	public  List getRows(String sheetName) {
		return getBuilder(sheetName).getRows();
	}

	private BaseExcelReader getBuilder(int index) {
		try {
			return checkNotNull(this.builders.get(index),"NOT DEFINE Sheet Config at index  "+index);
		} catch (IndexOutOfBoundsException e) {
			throw new IllegalArgumentException("INVALID index "+ index);
		}
	}
	private  BaseExcelReader getBuilder(String sheetName) {
		try {
			return Iterables.tryFind(builders, 
					b->null != b && b.sheetConfig.getSheetName().equals(sheetName)).get();
		} catch (IllegalStateException e) {
			throw new IllegalArgumentException("INVALID sheetName "+ sheetName);
		}
	}
	private  BaseExcelReader getBuilderOrNull(String sheetName) {
		return Iterables.tryFind(builders, 
			b->null != b && b.sheetConfig.getSheetName().equals(sheetName)).orNull();
	}
	@Override
	public void setSheetConfig(SheetConfig sheetConfig) {
		this.builders.get(0).setSheetConfig(sheetConfig);
	}

	@Override
	public SheetConfig getSheetConfig() {
		return this.builders.get(0).getSheetConfig();
	}

	@Override
	public void read(MultipartFile uplodFile, Charset charset) throws IOException {
	    try(InputStream inputStream = checkNotNull(uplodFile,"uplodFile is null").getInputStream()){
	    	log(firstNonNull(debugOutput,false),"UPLOAD FILE: {}",uplodFile.getOriginalFilename());
	    	read(inputStream, charset, suffixOf(uplodFile.getOriginalFilename()));
	    }		
	}

	@Override
	public void read(String file, Charset charset) throws IOException {
		try(FileInputStream inputStream = new FileInputStream(checkNotNull(file,"file is null"))) {
			log(firstNonNull(debugOutput,false),"FILE: {}",file);
			read(inputStream, charset, suffixOf(file));
		}
	}

	@Override
	public void read(File file, Charset charset) throws IOException {
		try(FileInputStream inputStream = new FileInputStream(checkNotNull(file,"file is null"))) {
			log(firstNonNull(debugOutput,false),"FILE: {}",file);
			read(inputStream, charset, suffixOf(file.getName()));
		}
	}
}