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

Go to download
Show more of this group Show more artifacts with this name
Show all versions of sql2java-excel Show documentation
sql2java excel helper
There is a newer version: 5.2.0
package gu.sql2java.excel;

import java.io.IOException;
import java.io.InputStream;
import java.nio.charset.Charset;
import java.util.Collections;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.function.Consumer;

import org.apache.poi.ss.usermodel.Cell;
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.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.CellRangeAddress;
import com.google.common.collect.ContiguousSet;
import com.google.common.collect.DiscreteDomain;
import com.google.common.collect.Range;

import gu.sql2java.excel.config.SheetConfig;
import gu.sql2java.utils.DateSupport;

import static gu.sql2java.excel.ExcelReader.createWorkbook;


/**
 * EXCEL Sheet(工作薄)导入
 * @author guyadong
 * @since 3.29.6
 *
 */
public class SheetReader  extends BaseExcelReader{

	private static final DataFormatter formatter = new DataFormatter();

	public SheetReader() {
		super();
	}
	public SheetReader(Consumer beanConsumer) {
		super(beanConsumer);
	}
	public SheetReader(SheetConfig sheetConfig, Consumer beanConsumer) {
		super(sheetConfig, beanConsumer);
	}
	public SheetReader(SheetConfig sheetConfig, List rows, Consumer beanConsumer) {
		super(sheetConfig, rows, beanConsumer);
	}
	public SheetReader(SheetConfig sheetConfig) {
		super(sheetConfig);
	}
	@SuppressWarnings("rawtypes")
	public SheetReader(BaseExcelReader builder) {
		super(builder);
	}
	/**
	 * 从第一个工作薄导入数据
	 */
	@Override
	public void read(InputStream inputStream, Charset charset, String format) throws IOException {
		try(Workbook workbook = createWorkbook(inputStream, format)){
			read(workbook.getSheetAt(0));
		}
	}
	void read(Sheet sheet) {
		Iterator itor = sheet.iterator();
		read(itor);
		consumeRows();
	}
	@Override
	protected String getCellAsString(Row row, int idx) {
		if(null != row) {
			Cell cell = row.getCell(idx);
		    if(cell !=null) {
		    	switch(cell.getCellType()) {
		    	case STRING:
		    		return cell.getRichStringCellValue().getString();
		    	case BLANK:
		    		return null;
		    	case BOOLEAN :
		    		return Boolean.toString(cell.getBooleanCellValue());
		    	case NUMERIC:
		    		if (DateUtil.isCellDateFormatted(cell, null)) {
		    			Date d = cell.getDateCellValue();
		    			return DateSupport.formatDate(d, DateSupport.TIMESTAMP_FORMATTER_STR);
		    		}
		    	default:
					return formatter.formatCellValue(cell);
		    	}
		    }
		}
		return null;
	}

	@Override
	protected boolean isEmptyCell(Row row, int idx) {
		if(null != row) {
			Cell cell = row.getCell(idx);
			if(null != cell) {
				switch(cell.getCellType()) {
		    	case STRING:
		    		return cell.getStringCellValue().trim().isEmpty();
		    	case BLANK:
		    		return true;
		    	default:
		    		return false;
		    	}
			}
		}
		return true;
	}

	@Override
	protected List indexsOfRow(Row row) {
		return null == row 
				? Collections.emptyList() 
				: ContiguousSet.create(
						Range.closedOpen((int)row.getFirstCellNum(), (int)row.getLastCellNum()),	
						DiscreteDomain.integers()).asList();
	}

	@Override
	protected boolean moreEmptyCheck(Row row) {
		for(CellRangeAddress region: row.getSheet().getMergedRegions()) {
			/** 行的所有单元被合并 */
			if(region.getFirstRow()==row.getRowNum() || region.getLastRow()==row.getRowNum()) {
				return true;
			}
		}
		return super.moreEmptyCheck(row);
	}

}