exce读取工具类

java exce读取工具类

package com.haier.hrsalary.importdata.util;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Excel相关
 */
public final class ExcelUtil {

    /**
     * 校验导入Excel标题是否一致
     * @param titles 标题数组
     * @param row 标题列
     * @return
     */
    public static boolean validTitel(String[] titles, Row row) {
        for (int i = 0; i < titles.length; i++) {
            if (!titles[i].equals(row.getCell(i).getStringCellValue())) {
                return false;
            }
        }
        return true;
    }

    /**
     * 将小数字符串转换成BigDecimal
     * @param str
     * @return 如果不符合格式或有效小数位数>2,则返回null
     */
    public static BigDecimal formatDecimal(String str) {
        try{
            BigDecimal b = new BigDecimal(str).stripTrailingZeros();
            if(b.scale() > 2) {
                return null;
            }
            return b;
        }catch (Exception e) {
            return null;
        }
    }

    /**
     * 校验字符串是否符合正则
     * @param str 待校验字符串
     * @param regex 正则表达式
     * @return
     */
    public static boolean validStrByMatcher(String str, String regex) {
        if(str == null || str.isEmpty()) {
            return false;
        }
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(str);
        return matcher.matches();
    }

    /**
     * 格式化用户编码,不足8位补零
     * @param empsn
     * @return 123 -> 00000123
     */
    public static String formatEmpsn(String empsn) {
        if(StringUtils.isBlank(empsn)) {
            return "";
        }
        empsn = empsn.toUpperCase();
        if(empsn.startsWith("Z")) {
            if(!validStrByMatcher(empsn.substring(1),"\\d{7}")) {
                return "";
            }
            return empsn;
        }else {
            if(!validStrByMatcher(empsn,"\\d{1,8}")) {
                return "";
            }
            DecimalFormat format = new DecimalFormat("00000000");
            return format.format(Integer.valueOf(empsn));
        }
    }

    /**
     * 格式化用户录入的批量人员数据
     * @param empsns
     * @return 123,6666/'123','6666' -> '00000123','00006666'
     */
    public static String formatEmpsns(String empsns) {
        if(StringUtils.isBlank(empsns)) {
            return "";
        }
        StringBuilder sb = new StringBuilder();
        Pattern pattern = Pattern.compile("(\\d{2,})|(Z\\d+)");
        Matcher matcher = pattern.matcher(empsns);
        while (matcher.find()) {
            String empsn = formatEmpsn(matcher.group());
            if(StringUtils.isNoneBlank(empsn))
                sb.append("'").append(empsn).append("'").append(",");
        }
        return sb.length() == 0 ? "" : sb.substring(0, sb.length() - 1);

    }

    /**
     * 获取表格中的数据
     * @param cell
     * @return
     */
    public static String getValue(Cell cell) {
        if(cell == null) {
            return "";
        }
        cell.setCellType(Cell.CELL_TYPE_STRING);
        String value = cell.getStringCellValue();
        if(value != null) {
            return value.trim();
        }
        return "";
    }

    public static String getCellValue(Cell cell) {
        DecimalFormat df = new DecimalFormat("#");
        if (cell == null)
            return "";
        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            return cell.getStringCellValue();
        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
            return cell.getCellFormula();
        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            return df.format(cell.getNumericCellValue());
        }
        return "";
    }

}