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

1、引入poi

1）poi版本

<poi.version>4.1.0</poi.version>

2）pom.xml

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>${poi.version}</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>${poi.version}</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>${poi.version}</version>
</dependency>

2、PoiExcel.java 完整代码

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;

/**
 * excel 操作工具类
 * @author ZhangJi
 */
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);
        }
    }

    /**
     * 获取单元格值
     * @param sheetIndex 工作簿索引
     * @param rowIndex 行索引
     * @param cellIndex 列索引
     * @return String
     */
    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;
        }

    }

    /**
     * 获取单元格String值
     * @param sheetIndex 工作簿索引
     * @param rowIndex 行索引
     * @param cellIndex 列索引
     * @return String
     */
    public String getStringVal(Integer sheetIndex, Integer rowIndex, Integer cellIndex) {

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

    /**
     * 获取单元格double值
     * @param sheetIndex 工作簿索引
     * @param rowIndex 行索引
     * @param cellIndex 列索引
     * @return String
     */
    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;
    }

    /**
     * 获取boolean值
     * @param sheetIndex 工作簿索引
     * @param rowIndex 行索引
     * @param cellIndex 列索引
     * @return boolean
     */
    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++;
        }
    }
}

3、相关参数、方法

1）字符串判空

/**
	 * 判断字符串是否为空：null、""均为空<br />
	 * 不允许空格：" "也为空
	 *
	 * @param str	需判断的字符串
	 * @param isExistSpace	是否允许存在空格
	 * @return	判断结果：true表示为空
	 */
	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());
	}

2）字符串验证

/**
	 * 字符串验证
	 *
	 * @param str	待过滤字符串
	 * @param reg	过滤格式
	 * @return boolean
	 */
	public static boolean isIncludeStr(String str, String reg, RegType type) {
		if(isEmpty(str)) {
			return false;
		}

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

3）字符串匹配

/**
	 *
	 * @param type 类型
	 * @param reg 表达式
	 * @param str 字符串
	 * @return Matcher
	 */
	public static Matcher regStr(RegType type, String reg, String str) {
		Pattern pat = Pattern.compile(reg, type.getValue());

		return pat.matcher(str);
	}

4）字符串匹配验证类型枚举类

import java.util.regex.Pattern;

/**
 * 字符串匹配验证类型枚举类
 * @author ZhangJi
 */
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;
        }
    }

5）实数正则表达式

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