iamberry-watero/watero-wechat-web/src/main/java/com/iamberry/wechat/utils/ExcelUtil.java

package com.iamberry.wechat.utils;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Excel工具类
 *
 * @author 献
 * @company 深圳爱贝源科技有限公司
 * @website www.iamberry.com
 * @tel 18271840547
 * @date 2017/5/25
 */
@Component
public class ExcelUtil {

    private static final org.slf4j.Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    /**
     * 读取Excel文件头
     *
     * @param filePath
     * @return
     */
    public static List<String> readExcelHead(String filePath) throws IOException {
        List<String> heads = new ArrayList<String>();
        // 判断文件是否存在
        File file = new File(filePath);
        if (!file.exists()) {
            return null;
        }
        // 获取Workbook
        InputStream inputStream = new BufferedInputStream(new FileInputStream(file));
        Workbook wb = null;
        if (filePath.endsWith("xls")) {
            wb = new HSSFWorkbook(inputStream);
        } else {
            wb = new XSSFWorkbook(inputStream);
        }
        inputStream.close();

        // 获取Sheet
        Sheet sheet = wb.getSheetAt(0);
        // 获取第一行数据
        Row row = sheet.rowIterator().next();

        Iterator<Cell> cells = row.cellIterator();
        // 获得第一行的迭代器,并遍历cell
        while (cells.hasNext()) {
            Cell cell = cells.next();
            heads.add(cell.getStringCellValue());
        }

        return heads;
    }

    /**
     * 读取Excel内容
     * 去除重复
     *
     * @param filePath
     * @return
     */
    public static List<String> readCell(String filePath, String cellName) throws IOException {
        // 判断文件是否存在
        File file = new File(filePath);
        if (!file.exists()) {
            return null;
        }
        // 获取Workbook
        InputStream inputStream = new BufferedInputStream(new FileInputStream(file));
        Workbook wb = null;
        if (filePath.endsWith("xls")) {
            wb = new HSSFWorkbook(inputStream);
        } else {
            wb = new XSSFWorkbook(inputStream);
        }
        inputStream.close();

        // 获取Sheet
        Sheet sheet = wb.getSheetAt(0);
        Iterator<Row> rows = sheet.rowIterator();

        // 获取第一行的数据，匹配到表头
        int cellNum = -1;
        Row row = rows.next();
        Iterator<Cell> cells = row.cellIterator();
        while (cells.hasNext()) {
            Cell cell = cells.next();
            if (cellName.trim().equals(cell.getStringCellValue().trim())) {
                cellNum = cell.getColumnIndex();
                break;
            }
        }
        if (cellNum == -1) return null;

        // 读取数据
        Map<String, String> map = new HashMap<>();
        while (rows.hasNext()) {
            Row temp = rows.next();
            Cell cell = temp.getCell(cellNum);
            // 非空的列需要使用
            if (cell == null) {
                continue;
            }
            String name = getValue(cell);
            if (name != null && !"".equals(name.trim())) {
                String cellValue = getValue(cell);
                if (map.get(cellValue) == null) {
                    map.put(cellValue, cellValue);
                }
            }
        }

        // Map to List
        Collection<String> valueCollection = map.values();
        return new ArrayList<String>(valueCollection);
    }

    /**
     * 读取Excel内容 -- 行数是否超过100，超过false  不超过true；
     * @param filePath
     * @return
     */
    public static boolean readExcelNumber(String filePath,Integer maxNuber) throws IOException {
        // 判断文件是否存在
        File file = new File(filePath);
        if (!file.exists()) {
            return false;
        }
        // 获取Workbook
        InputStream inputStream = new BufferedInputStream(new FileInputStream(file));
        Workbook wb = null;
        if (filePath.endsWith("xls")) {
            wb = new HSSFWorkbook(inputStream);
        } else {
            wb = new XSSFWorkbook(inputStream);
        }
        inputStream.close();

        // 获取Sheet
        Sheet sheet = wb.getSheetAt(0);

        Integer number = 0;     //控制有效行数在maxNumber之内
        Integer i = 0;         //控制总行数在MaxNumber之内，因为会出现无效行数的问题，如果一直循环，会循环65535行

        Iterator<Row> rows = sheet.rowIterator();
        while (rows.hasNext()) {
            Row row = rows.next();
            Cell cell = row.getCell(0);
            // 非空的列需要使用
            if (cell == null) {
                continue;
            }
            String name = getValue(cell);
            String cellValue = "";
            if (name != null && !"".equals(name.trim())) {
                cellValue = getValue(cell);
            }

            if (cellValue != null && !"".equals(cellValue)) {
                number ++;
            }
            i++;

            if(number > maxNuber+1){
                return false;
            }

            if(i > maxNuber+1){
                break;
            }
        }
        return true;
    }

    /**
     * 读取Excel内容
     * 不去除重复
     *
     * @param filePath
     * @return
     */
    public static List<String> readCell2(String filePath, String cellName) throws IOException {
        // 判断文件是否存在
        File file = new File(filePath);
        if (!file.exists()) {
            return null;
        }
        // 获取Workbook
        InputStream inputStream = new BufferedInputStream(new FileInputStream(file));
        Workbook wb = null;
        if (filePath.endsWith("xls")) {
            wb = new HSSFWorkbook(inputStream);
        } else {
            wb = new XSSFWorkbook(inputStream);
        }
        inputStream.close();

        // 获取Sheet
        Sheet sheet = wb.getSheetAt(0);
        Iterator<Row> rows = sheet.rowIterator();

        // 获取第一行的数据，匹配到表头
        int cellNum = -1;
        Row row = rows.next();
        Iterator<Cell> cells = row.cellIterator();
        while (cells.hasNext()) {
            Cell cell = cells.next();
            if (cellName.trim().equals(cell.getStringCellValue().trim())) {
                cellNum = cell.getColumnIndex();
                break;
            }
        }
        if (cellNum == -1) return null;

        // 读取数据
        ArrayList<String> arreyMap = new ArrayList<String>();
        while (rows.hasNext()) {
            Row temp = rows.next();
            Cell cell = temp.getCell(cellNum);
            // 非空的列需要使用
            if (cell == null) {
                continue;
            }
            String name = getValue(cell);
            if (name != null && !"".equals(name.trim())) {
                String cellValue = getValue(cell);
                arreyMap.add(cellValue);
            }
        }

        // Map to List
        /*Collection<String> valueCollection = map.values();*/
        return arreyMap;
    }

    /**
     * 读取Excel内容(上传excel文档)
     * 不去除重复
     *
     * @param filePath
     * @return
     */
    public static List<String> readCell3(String filePath) throws IOException {
        // 判断文件是否存在
        File file = new File(filePath);
        if (!file.exists()) {
            return null;
        }
        // 获取Workbook
        InputStream inputStream = new BufferedInputStream(new FileInputStream(file));
        Workbook wb = null;
        if (filePath.endsWith("xls")) {
            wb = new HSSFWorkbook(inputStream);
        } else {
            wb = new XSSFWorkbook(inputStream);
        }
        inputStream.close();

        // 获取Sheet
        Sheet sheet = wb.getSheetAt(0);
        Iterator<Row> rows = sheet.rowIterator();

        // 获取第一行的数据，匹配到表头
        int cellNum = -1;
        Row row = rows.next();
        Iterator<Cell> cells = row.cellIterator();
        while (cells.hasNext()) {
            Cell cell = cells.next();
                cellNum = cell.getColumnIndex();
                break;
        }
        if (cellNum == -1) return null;

        // 读取数据
        ArrayList<String> arreyMap = new ArrayList<String>();
        while (rows.hasNext()) {
            Row temp = rows.next();
            Cell cell = temp.getCell(cellNum);
            // 非空的列需要使用
            if (cell == null) {
                continue;
            }
            String name = getValue(cell);
            if (name != null && !"".equals(name.trim())) {
                String cellValue = getValue(cell);
                arreyMap.add(cellValue);
            }
        }

        // Map to List
        /*Collection<String> valueCollection = map.values();*/
        return arreyMap;
    }

    /**
     *获取值
     * @param cell
     * @return
     */
    private static Object getExcelCell(Cell cell){
        Object obj;
        if (null != cell) {
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                    double cellValue = cell.getNumericCellValue();
                    obj = new DecimalFormat("#").format(cellValue);
                    break;
                case HSSFCell.CELL_TYPE_STRING: // 字符串
                    obj = cell.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                    obj = cell.getBooleanCellValue();
                    break;
                case HSSFCell.CELL_TYPE_FORMULA: // 公式
                    obj = cell.getCellFormula();
                    break;
                case HSSFCell.CELL_TYPE_BLANK: // 空值
                    obj = "";
                    break;
                case HSSFCell.CELL_TYPE_ERROR: // 故障
                    obj = "";
                    break;
                default:
                    obj = "";
                    break;
            }
        } else {
            obj = "";
        }
        return obj;
    }

    /**
     * 地址 - 整体切割符
     */
    private static String[] ENTIRETY_SPLIT;

    /**
     * 地址 - 省份数据切割符
     */
    private static String[][] PROVINCE_CITY_AERA_SPLIT;

    static {
        ENTIRETY_SPLIT = new String[]{" ", "-", "_", "*"};
        PROVINCE_CITY_AERA_SPLIT = new String[][]{
                {"省", "市", "区"},
                {"省", "市", "县"},
                {"省", "市", "乡"},
                {"省", "市", "镇"},
                {"省", "市", "市"},

                {"自治区", "市", "区"},
                {"自治区", "市", "县"},
                {"自治区", "市", "乡"},
                {"自治区", "市", "镇"},
                {"自治区", "市", "市"},

                {"省", "州", "区"},
                {"省", "州", "县"},
                {"省", "州", "乡"},
                {"省", "州", "市"},
                {"省", "州", "镇"},

                {"市", "市", "区"},
                {"市", "市", "县"},
                {"市", "市", "镇"},
                {"市", "市", "乡"},

                {"市", "区"},
                {"市", "乡"},
                {"市", "镇"},
                {"市", "县"},

                {"省", "县", "镇"}
        };
    }

    /**
     * 切割地址
     */
    private static String[] splitAddress(String addrInfo) {
        String[] addrs = null;

        // 首先通过整体切割符合切割
        for (String charSplit : ENTIRETY_SPLIT) {
            addrs = StringUtils.split(addrInfo, charSplit);
            if (addrs != null && addrs.length >= 4) {
                break;
            } else if (addrs != null && addrs.length == 3 && !addrs[0].contains("省")) {
                break;
            }
        }

        // 详细地址不能直接切割
        if (addrs != null && addrs.length >= 4) {
            // 防止区域数据出现小区 扰乱数据
            if (!addrs[2].contains("小区")) {
                return new String[]{addrs[0], addrs[1], addrs[2], addrInfo};
            }

        } else if (addrs != null && addrs.length == 3) {
            // 防止区域数据出现小区 扰乱数据
            if (!addrs[1].contains("小区")) {
                return new String[]{addrs[0], addrs[0], addrs[1], addrInfo};
            }
        }

        for (String[] splits : PROVINCE_CITY_AERA_SPLIT) {

            // 每组独立切割字符单独循环
            String tempInfo = addrInfo;
            addrs = new String[splits.length + 1];
            boolean isNext = false;
            for (int i = 0; i < splits.length; i++) {
                String splitChar = splits[i];
                String[] temps = StringUtils.split(tempInfo, splitChar);
                if (temps.length >= 2) {
                    // 获取到第一个地址
                    addrs[i] = temps[0] + splitChar;
                    // 下一次迭代内容
                    tempInfo = StringUtils.substring(tempInfo, temps[0].length() + splitChar.length());
                } else {
                    // 如果首次匹配失败，则停止匹配
                    isNext = true;
                    break;
                }
            }
            if (!isNext) {
                if ((addrs.length - 1) == 2) {
                    // 防止区域数据出现小区 扰乱数据
                    if (addrs[1].contains("小区")) {
                        continue;
                    }
                    // 只有两级地址
                    return new String[]{addrs[0], addrs[0], addrs[1], addrInfo};
                } else if ((addrs.length - 1) == 3) {
                    // 防止区域数据出现小区 扰乱数据
                    if (addrs[2].contains("小区")) {
                        continue;
                    }
                    // 三级地址
                    return new String[]{addrs[0], addrs[1], addrs[2], addrInfo};
                }
            }
        }

        return addrs;
    }

    public static void main(String[] args) throws IOException {
        String[] add = splitAddress("海南省 临高县 博厚镇衍宏海港小镇（303县道西）衍宏海港小镇37栋602");
        if (add == null) {
            System.out.println("切割失败...");
        }
        for (String i : add) {
            System.out.println(i);
        }
    }

    private static SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");

    public static String getValue(Cell cell) {
        if (cell == null) return null;
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING: //文本
                return cell.getStringCellValue();
            case Cell.CELL_TYPE_NUMERIC: //数字、日期
                if (DateUtil.isCellDateFormatted(cell)) {
                    return fmt.format(cell.getDateCellValue()); //日期型
                }
                DecimalFormat df = new DecimalFormat("#");
                return df.format(new Double(cell.getNumericCellValue())); //数字
            case Cell.CELL_TYPE_BOOLEAN: //布尔型
                return String.valueOf(cell.getBooleanCellValue());
            case Cell.CELL_TYPE_BLANK: //空白
                return cell.getStringCellValue();
            default: //错误
                return "ERROR";
        }
    }

    /**
     * excel 强制获取text文本
     * @param cell
     * @return
     */
    public static String getTextValue(Cell cell) {
        if (cell == null) return null;
        return cell.getStringCellValue();
    }

}