PoiExcel数据导入工具类（持续更新）

4.1.0

    org.apache.poi
    poi-ooxml
    ${poi.version}


    org.apache.poi
    poi-ooxml-schemas
    ${poi.version}


    org.apache.poi
    poi
    ${poi.version}

import cn.jjzqkj.base.defaultEnum.RegType;
import cn.jjzqkj.base.exception.BusinessException;
import cn.jjzqkj.base.util.RegExpLib;
import cn.jjzqkj.base.util.StringUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;


public class PoiExcel {

    private final Workbook workBook;

    public PoiExcel(String filePath) throws IOException {

        this(new File(filePath));
    }

    public PoiExcel(File file) throws IOException {

        this.verifyXls(file);

        InputStream inputStream = new FileInputStream(file);

        // 根据不同的后缀，创建不同的对象
        if(StringUtil.isIncludeStr(file.getName(), ".*\.xlsx$", RegType.CASE_INSENSITIVE)){

            this.workBook = new XSSFWorkbook(inputStream);
        } else{

            this.workBook = new HSSFWorkbook(inputStream);
        }
    }

    
    public Object getObjVal(Integer sheetIndex, Integer rowIndex, Integer cellIndex) {

        Cell cell = this.getSheet(sheetIndex).getRow(rowIndex).getCell(cellIndex);
        switch (cell.getCellType()) {
            case STRING: return cell.getStringCellValue();
            case NUMERIC: return cell.getNumericCellValue();
            case BOOLEAN: return cell.getBooleanCellValue();
            default: return null;
        }

    }

    
    public String getStringVal(Integer sheetIndex, Integer rowIndex, Integer cellIndex) {

        Object cellVal = this.getObjVal(sheetIndex, rowIndex, cellIndex);
        return null != cellVal?cellVal.toString(): "";
    }

    
    public Double getDoubleval(Integer sheetIndex, Integer rowIndex, Integer cellIndex) {

        Object cellVal = this.getObjVal(sheetIndex, rowIndex, cellIndex);
        return null == cellVal?.0: StringUtil.isIncludeStr(cellVal.toString(), RegExpLib.REG_FLOAT, RegType.DOTALL)?Double.parseDouble(cellVal.toString()): .0;
    }

    
    public boolean getBooleanVal(Integer sheetIndex, Integer rowIndex, Integer cellIndex) {

        Object cellVal = this.getObjVal(sheetIndex, rowIndex, cellIndex);
        return null != cellVal && (boolean) cellVal;
    }

    public Sheet getSheet(Integer sheetIndex) {

        return this.workBook.getSheetAt(sheetIndex);
    }

    private void verifyXls(File file){

        if(!file.exists() || file.isDirectory()) {

            throw new BusinessException("导入文件不存在！");
        }

        if(!StringUtil.isIncludeStr(file.getName(), ".*\.xls$|.*\.xlsx$", RegType.CASE_INSENSITIVE)) {

            throw new BusinessException("导入文件类型错误！");
        }
    }

    public static void main(String[] args) throws IOException {

        PoiExcel excel = new PoiExcel("F:\Users\ZhangJi\yx-tech\project\鲜花商城\设计文档\价格动态导入模板.xlsx");

        int rowIndex = 1;
        while (true) {

            int cellIndex = 0;
            String v1 = excel.getStringVal(0, rowIndex, cellIndex++);
            if(StringUtil.isEmpty(v1)) {

                break;
            }

            String v2 = excel.getStringVal(0, rowIndex, cellIndex++);
            double v3 = excel.getDoubleval(0, rowIndex, cellIndex++);
            double v4 = excel.getDoubleval(0, rowIndex, cellIndex++);
            double v5 = excel.getDoubleval(0, rowIndex, cellIndex);

            System.out.println(v1 + " - " + v2 + " - " + v3 + " - " + v4 + " - " + v5);
            rowIndex++;
        }
    }
}

	public static boolean isEmpty(String str, boolean isExistSpace) {
		if(null == str)	{
			return true;
		}
		if("".equals(str) || str.length() == 0)	{
			return true;
		}
		return !isExistSpace && "".equals(str.trim());
	}

	public static boolean isIncludeStr(String str, String reg, RegType type) {
		if(isEmpty(str)) {
			return false;
		}

		return regStr(type, reg, str).find();
	}

	public static Matcher regStr(RegType type, String reg, String str) {
		Pattern pat = Pattern.compile(reg, type.getValue());

		return pat.matcher(str);
	}

import java.util.regex.Pattern;


public enum RegType {
        
        DOTALL(1, Pattern.DOTALL),

        
        CASE_INSENSITIVE(2, Pattern.CASE_INSENSITIVE);

        private final int value;
        private final int type;

        RegType(int value, int type) {
            this.value = value;
            this.type = type;
        }

        public int getValue() {
            return value;
        }

        public int getType() {
            return type;
        }
    }

    public final static String REG_FLOAT = "^[\+|\-|[1-9]|0]{1}\d*[.]{0,1}\d*[1-9|0]{1}$";