excel解析

package com.alipay.utils;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;


import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson.JSON;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * by wanghai
 * 2018/9/20
 */
public class ParsingExcelUtil {

    /**
     * 文件路径
     */
    private static final String filePath = "D:\\贫困户信息.xls";

    /**
     * 起始行
     */
    private static final int startRow = 3;

    /**
     * 最后一行
     */
    private static final int endRow = 50;

    public static void main(String[] args) {

        Map<String, String> appropriateMap = getAppropriateMap(filePath, startRow, endRow);
        System.out.println("合适的人群编号和姓名：" + JSON.toJSONString(appropriateMap));
    }

    /**
     * 获取合适的人群编号和姓名
     *
     * @param filePath 文件路径
     * @param startRow 起始行  从哪一行开始
     * @param endRow   结束行  从哪一行结束
     * @return Map<String, String>
     */
    public static Map<String, String> getAppropriateMap(String filePath, int startRow, int endRow) {
        //姓名 第五列
        Map<String, String> excelMap01 = getColumnMap(filePath, 5, startRow, endRow);

        Map<String, String> appropriateMap = new LinkedHashMap<>();

        //合适的人群 编号
        List<List<String>> appropriates = getAppropriates(filePath, startRow, endRow);
        for (List<String> appropriate : appropriates) {
            for (String str : appropriate) {
                if (excelMap01.containsKey(str)) {
                    appropriateMap.put(str, excelMap01.get(str));
                }
            }
        }
        System.out.println("");
        System.out.println("");
        System.out.println("");
        System.out.println("合适的人群编号:" + JSON.toJSONString(appropriates));

        return appropriateMap;
    }

    /**
     * 获取合适的人群编号
     *
     * @param filePath 文件路径
     * @param startRow 起始行
     * @param endRow   结束行
     * @return 人群编号
     */
    public static List<List<String>> getAppropriates(String filePath, int startRow, int endRow) {

        List<List<String>> relations = getRelations(filePath, startRow, endRow);
        System.out.println("户主关系编号集合：" + JSON.toJSONString(relations));

        List<List<String>> appropriates = new ArrayList<>();
        List<String> numbers = getNumbers(filePath, startRow, endRow);
        for (String number : numbers) {
            for (List<String> relation : relations) {
                if (relation.contains(number)) {
                    appropriates.add(relation);
                }
            }
        }
        List<List<String>> appropriateList = new ArrayList<>();
        Iterator<List<String>> iterator = appropriates.iterator();
        while (iterator.hasNext()) {
            List<String> next = iterator.next();
            if (!appropriateList.contains(next)) {
                appropriateList.add(next);
            }
        }

        return appropriateList;

    }

    /**
     * 获取符合情况的编号集合
     *
     * @return Set<String>
     */
    public static List<String> getNumbers(String filePath, int startRow, int endRow) {
        //在校生状况为空 编号
        List<String> students = getStudents(filePath, startRow, endRow);
        System.out.println("符合标准的在校生状况编号" + JSON.toJSONString(students));

        //劳动技能 编号
        List<String> labors = getLabors(filePath, startRow, endRow);
        System.out.println("符合标准的劳动技能编号" + JSON.toJSONString(labors));

        //务工状况 编号
        List<String> workers = getWorkers(filePath, startRow, endRow);
        System.out.println("符合务工状况的编号" + JSON.toJSONString(workers));

        //年龄段 17<=age<=64   编号
        List<String> ages = getAges(filePath, startRow, endRow);
        System.out.println("符合年龄段的编号" + JSON.toJSONString(ages));

        List<String> numbers = new ArrayList<>();
        if (CollectionUtil.isNotEmpty(ages)) {
            for (String age : ages) {
                if (workers.contains(age) && labors.contains(age) && students.contains(age)) {
                    numbers.add(age);
                }
            }
        }

        return numbers;
    }

    /**
     * 获取符合年龄的集合 编号
     *
     * @param filePath
     * @param startRow
     * @param endRow
     * @return
     */
    public static List<String> getAges(String filePath, int startRow, int endRow) {
        //年龄
        Map<String, String> excelMap02 = getColumnMap(filePath, 8, startRow, endRow);

        List<String> ages = new ArrayList<>();
        excelMap02.forEach((key, value) -> {
            if (!StrUtil.isBlank(value)) {
                int age = Integer.parseInt(value);
                if (age >= 17 && age <= 64) {
                    ages.add(key);
                }
            }

        });
        return ages;
    }

    /**
     * 务工状况
     *
     * @param filePath
     * @param startRow
     * @param endRow
     * @return
     */
    public static List<String> getWorkers(String filePath, int startRow, int endRow) {
        //务工状况
        Map<String, String> excelMap05 = getColumnMap(filePath, 18, startRow, endRow);

        List<String> workers = new ArrayList<>();
        excelMap05.forEach((key, value) -> {
            if (!StrUtil.equals("省外务工", value) && !StrUtil.equals("县外省内务工", value)) {
                workers.add(key);
            }
        });

        return workers;
    }

    /**
     * 获取劳动技能符合合适的集合
     *
     * @return Set<String>
     */
    public static List<String> getLabors(String filePath, int startRow, int endRow) {
        //劳动技能
        Map<String, String> excelMap04 = getColumnMap(filePath, 17, startRow, endRow);
        List<String> labors = new ArrayList<>();
        excelMap04.forEach((key, value) -> {
            if (StrUtil.equals("技能劳动力", value) || StrUtil.equals("普通劳动力", value)
                    || StrUtil.equals("弱劳动力或半劳动力", value)) {
                labors.add(key);
            }
        });
        return labors;
    }

    /**
     * 获取在校生情况集合
     *
     * @return Set<String>  无序，不重复
     */
    public static List<String> getStudents(String filePath, int startRow, int endRow) {
        //在校生状况
        Map<String, String> excelMap06 = getColumnMap(filePath, 15, startRow, endRow);
        List<String> students = new ArrayList<>();
        excelMap06.forEach((key, value) -> {
            if (StrUtil.isBlank(value)) {
                students.add(key);
            }
        });
        return students;
    }

    /**
     * 获取户主关系 集合
     *
     * @return Set<List < String>>
     */
    public static List<List<String>> getRelations(String filePath, int startRow, int endRow) {

        //户主关系
        Map<String, String> excelMap03 = getColumnMap(filePath, 10, startRow, endRow);

        //获取户主关系范围 第几行到第几行属于一个户主名下
        List<String> relations = new ArrayList();
        //副本
        List<String> relations01 = new ArrayList<>();

        List<List<String>> relationLists = new ArrayList<>();

        AtomicInteger atomicInteger = new AtomicInteger(0);
        for (Map.Entry<String, String> entry : excelMap03.entrySet()) {
            atomicInteger.addAndGet(1);
            if (!StrUtil.equals("户主", entry.getValue())) {
                relations.add(entry.getKey());
            }

            //第一个户主
            if (StrUtil.equals("户主", entry.getValue()) && CollectionUtil.isEmpty(relations)) {
                relations.add(entry.getKey());
                continue;

            }


            //非第一个户主
            if (StrUtil.equals("户主", entry.getValue())) {
                relations01 = relations;
                relationLists.add(relations01);
                relations = new ArrayList<>();
                relations.add(entry.getKey());

            }


            //最后一次
            if (excelMap03.size() == atomicInteger.get()) {
                relationLists.add(relations);
            }
        }

        return relationLists;

    }

    /**
     * @param filePath 需要读取的文件路径
     * @param column   指定需要获取的列数，例如第一列 1
     * @param startRow 指定从第几行开始读取数据
     * @param endRow   指定结束行
     * @return 返回读取列数据的Map
     */
    public static Map<String, String> getColumnMap(String filePath, int column, int startRow, int endRow) {
        Workbook wb = readExcel(filePath); //文件
        Sheet sheet = wb.getSheetAt(0); //sheet
        int rownum = sheet.getPhysicalNumberOfRows(); //行数
        Row row = null;
        Map<String, String> excelMap = new LinkedHashMap<>();
        String cellData = null;
        if (wb != null) {
            for (int i = startRow - 1; i < endRow; i++) {
                row = sheet.getRow(i);
                if (row != null) {
                    cellData = (String) getCellFormatValue(row.getCell(column - 1));
                    String replaceValue = cellData.replaceAll(" ", "");
                    excelMap.put(String.valueOf(i + 1), replaceValue);
                } else {
                    break;
                }
            }
        }
        return excelMap;
    }

    /**
     * @param filePath 需要读取的文件路径
     * @param column   指定需要获取的列数，例如第一列 1
     * @param startRow 指定从第几行开始读取数据
     * @return 返回读取列数据的Map
     */
    public static Map<String, String> getColumnMap(String filePath, int column, int startRow) {
        Workbook wb = readExcel(filePath); //文件
        Sheet sheet = wb.getSheetAt(0); //sheet
        int rownum = sheet.getPhysicalNumberOfRows(); //行数
        System.out.println("sumrows " + rownum);

        return getColumnMap(filePath, column, startRow, rownum - 1);
    }


    //读取excel
    public static Workbook readExcel(String filePath) {
        Workbook wb = null;
        if (filePath == null) {
            return null;
        }
        String extString = filePath.substring(filePath.lastIndexOf("."));
        InputStream is = null;
        try {
            is = new FileInputStream(filePath);
            if (".xls".equals(extString)) {
                return wb = new HSSFWorkbook(is);
            } else if (".xlsx".equals(extString)) {
                return wb = new XSSFWorkbook(is);
            } else {
                return wb = null;
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

    public static Object getCellFormatValue(Cell cell) {
        Object cellValue = null;
        if (cell != null) {
            //判断cell类型
            switch (cell.getCellType()) {
                case NUMERIC: {
                    cell.setCellType(CellType.STRING);  //将数值型cell设置为string型
                    cellValue = cell.getStringCellValue();
                    break;
                }
                case FORMULA: {
                    //判断cell是否为日期格式
                    if (DateUtil.isCellDateFormatted(cell)) {
                        //转换为日期格式YYYY-mm-dd
                        cellValue = cell.getDateCellValue();
                    } else {
                        //数字
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                }
                case STRING: {
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                }
                default:
                    cellValue = "";
            }
        } else {
            cellValue = "";
        }
        return cellValue;
    }

}

pom文件依赖：

        <!-- poi -->
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.0</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.0</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.4</version>
        </dependency>

        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.3.2</version>
        </dependency>