excel表格取列行数据

public class test  {

// dao层逻辑处理
    @Autowired
    private RpEstaffMapper rpEstaffMapper;


    static String valueQ;

    /**
     * 新增rp_e_staff
     * @param file 文件
     * @param path 路径
     */
    @Override
    @Transactional
    public void insertStaff(MultipartFile file, String path) {

        FileInputStream fis = null;
        if (!"".equals(path)) {
            //获取一个绝对地址的流
            try {
                fis = new FileInputStream(path);
            } catch (FileNotFoundException e) {
                System.out.println("路径");
                e.printStackTrace();
            }
        } else {
            //获取复杂表单的输入流
            try {
                fis = (FileInputStream) file.getInputStream();
            } catch (IOException e) {
                System.out.println("路径");
                e.printStackTrace();
            }
        }
        //InputStream fis=request.getInputStream();
        Workbook wookbook = null;
        try {
            wookbook = new XSSFWorkbook(fis);//得到工作簿
        } catch (IOException e) {
            e.printStackTrace();
        }
        //得到一个工作表
        Sheet sheet = wookbook.getSheetAt(0);
        //获得数据的总行数
        int totalRowNum = sheet.getLastRowNum();

        ArrayList<staffTable> tables = new ArrayList<>();
        staffTable staffTable = new staffTable();
        //要获得属性
        String name = "";
        String value = "";
        String date = null;

        int aStart = 0;
        int aEnd = 0;

        int aNaStart = 0;
        int aNaEnd = 0;

        int eStart = 0;
        int eEnd = 0;

        //获得第i行对象
        Row rowYMD = sheet.getRow(3);
        Cell cellYMD = rowYMD.getCell((short) 0);
        if (cellYMD != null) {
            name = cellYMD.getStringCellValue();
            if (!"".equals(name)) {
                String[] s = name.split(",")[0].split(" ");
                String ri = s[s.length - 1];
                String yue = s[s.length - 2];
                String ya = name.split(",")[1];
                System.out.println(ya);
                date = ya + recursionByValue(yue) + ri;
            }
        }
        // 删除当前时间存入的数据
        if (date != null) {
            staffTable.setDate(date);
            rpEstaffMapper.deleteCurrentTimeStaff(date);
        }

        //BCDFG 获得所有数据
        for (int i = 0; i <= totalRowNum; i++) {
            //获得第i行对象
            Row row = sheet.getRow(i);
            if (row != null) {
                //获得获得第i行第0列的 String类型对象
                Cell cell = row.getCell((short) 0);
                if (cell != null) {
                    cell.setCellType(CellType.STRING);
                    name = cell.getStringCellValue().toString();
                    if (aStart > 0) {
                        if ("".equals(name)) {
                            if (aEnd == 0) {
                                aEnd = i;
                            }
                        }
                    }
                    if (!"".equals(name)) {
                        if (name.contains("A.)")) {
                            aStart = i;
                        }
                        // TOTAL NUMBER OF FULL-TIME EMPLOYEES IN USA
                        if (name.contains("B.)") || name.contains("C.)") || name.contains("D.)") || name.contains("TOTAL NUMBER OF FULL-TIME EMPLOYEES IN USA")
                                || name.contains("TOTAL NUMBER OF EMPLOYEES IN MIDTOWN BRANCH")
                        ) {
                            //获得获得第i行第5列的 String类型对象
                            value = getTemp(row, (short) 4);
                            staffTable.setValue(value);
                            name = getKuoHaoDelete(name);
                            name = name.replace("…", "");
                            name = name.replace(".", "");
                            staffTable.setName(name);
                            staffTable.setDate(date);
                            tables.add(staffTable);
                            staffTable = new staffTable();
                        }
                        if (name.contains("ANALYSIS:")) {
                            aNaStart = i;
                        }
                        if (name.contains("E)  NON FULL-TIME  EMPLOYEES")) {
                            aNaEnd = i;
                            eStart = i;
                        }

                        if (name.contains("F)")) {
                            eEnd = i;
                            //获得获得第i行第6列的 String类型对象
                            staffTable.setValue(getTemp(row, (short) 5));
                            name = getKuoHaoDelete(name);
                            staffTable.setName(name);
                            staffTable.setDate(date);
                            tables.add(staffTable);
                            staffTable = new staffTable();
                        }
                        if (name.contains("G)")) {
                            //获得获得第i行第6列的 String类型对象
                            staffTable.setValue(getTemp(row, (short) 5));
                            staffTable.setName(name = getKuoHaoDelete(name));
                            staffTable.setDate(date);
                            tables.add(staffTable);
                            staffTable = new staffTable();
                        }
                    }
                }
            }
        }

        // A) 数据操作
        for (int i = aStart + 1; i < aEnd; i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                //获得获得第i行第0列的 String类型对象
                Cell cell = row.getCell((short) 0);
                if (cell != null) {
                    cell.setCellType(CellType.STRING);
                    name = cell.getStringCellValue().toString();
                    if (!"".equals(name) && !name.contains("A.) ")) {
                        name = name.split("\\.")[1];
                        staffTable.setName(getKuoHaoDelete(name));
                        staffTable.setValue(getTemp(row, (short) 4));
                        staffTable.setDate(date);
                        tables.add(staffTable);
                        staffTable = new staffTable();
                    }
                }
            }
        }

        // ANALYSIS:  数据操作
        for (int i = aNaStart; i < aNaEnd; i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                //获得获得第i行第0列的 String类型对象
                Cell cell = row.getCell((short) 1);
                if (cell != null) {
                    cell.setCellType(CellType.STRING);
                    name = cell.getStringCellValue().toString();
                    if (!"".equals(name)) {
                        name = getKuoHaoDelete(name);
                        name = name.replace("…", "");
                        name = name.replace(".", "");
                        staffTable.setName(name);
                        staffTable.setValue(getTemp(row, (short) 4));
                        staffTable.setDate(date);
                        tables.add(staffTable);
                        staffTable = new staffTable();
                    }
                }
            }
        }
        // E:  数据操作
        for (int i = eStart + 2; i < eEnd; i++) {

            Row row = sheet.getRow(i);
            if (row != null) {
                //获得获得第i行第0列的 String类型对象
                Cell cell = row.getCell((short) 0);
                if (cell != null && !"".equals(cell.getStringCellValue())) {
                    cell.setCellType(CellType.STRING);
                    name = cell.getStringCellValue().toString();
                    name = name.replace("         ", "");
                    if (!"".equals(name)) {

                        staffTable.setName(getKuoHaoDelete(name));
                        recursionByValue(sheet, i, value);
                        staffTable.setValue(valueQ);
                        staffTable.setDate(date);
                        staffTable.setDate(date);
                        tables.add(staffTable);
                        staffTable = new staffTable();
                    }
                }
            }
        }
        // 数据汇总添加
        rpEstaffMapper.insertStaff(tables);

    }

    private String getTemp(Row row, short i2) {
        String value;
        Cell cell4 = row.getCell(i2);
        cell4.setCellType(CellType.STRING);
        value = cell4.getStringCellValue().toString();
        return value;
    }

    /**
     * 递归获取 E)  NON FULL-TIME  EMPLOYEES : 的value值
     *
     * @param sheet 表格
     * @param i     那一列
     * @param value 值
     */
    public static void recursionByValue(Sheet sheet, int i, String value) {
        Cell cell = sheet.getRow(i).getCell((short) 4);
        cell.setCellType(CellType.STRING);
        valueQ = cell.getStringCellValue();
        if ("".equals(valueQ)) {
            recursionByValue(sheet, i + 1, value);
        }
    }

    public static String recursionByValue(String value) {
        HashMap<String, String> map = new HashMap<>();
        map.put("January", "01");
        map.put("February", "02");
        map.put("March", "03");
        map.put("April", "04");
        map.put("May", "05");
        map.put("June", "06");
        map.put("July", "07");
        map.put("August", "08");
        map.put("September", "09");
        map.put("October", "10");
        map.put("November", "11");
        map.put("December", "12");
        return map.get(value);
    }

    public static String getKuoHaoDelete(String value) {
        int stat = 0, end = 0;
        ArrayList<Integer> list = new ArrayList<>();
        //       - TEMPORARY (page 31) Full-Time Schedule (35 hrs per week per ee).
        for (int i = 0; i < value.length(); i++) {
            char c = value.charAt(i);
            if (c == '(') {
                list.add(i);//stat = i;
            }
            if (c == ')') {
                if (list.size() != 0) {
                    list.add(i);
                }
            }
        }
        ArrayList<String> strings = new ArrayList<>();
        StringBuilder stringBuilder = new StringBuilder();
        for (int i = 0; i < list.size(); i++) {
            String substring = value.substring(list.get(i), list.get(++i) + 1);
            strings.add(substring);
        }
        for (String string : strings) {
            value = value.replaceAll(string, "");
        }
        value = value.replaceAll("\\u0028\\u0029", "");
        value = value.replaceAll("…", "");
        value = value.replaceAll("\\.", "");
        return value;
    }
}

表格数据