package cn.com.goldenwater.dcproj.utils;

import cn.com.goldenwater.dcproj.dto.ExcelInspLevelDto;
import cn.com.goldenwater.dcproj.dto.ExcelInspPblm;
import cn.com.goldenwater.dcproj.dto.ExcelInspPersDto;
import cn.com.goldenwater.dcproj.dto.ExcelInspRsDto;
import cn.com.goldenwater.dcproj.model.*;
import cn.com.goldenwater.dcproj.vo.PblmObj;
import cn.com.goldenwater.dcproj.vo.RsvrObj;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.*;

public class LoadExcel {


    /**
     * 导入稽察数据
     */
    public static List<TacObjPblmstb> getTacExcel(String filePath, List<TacObjSubject> objSubjects) throws Exception {
        List<TacObjPblmstb> pblmstbs = new ArrayList<>();
        InputStream is = new FileInputStream(filePath);
        // 构造 XSSFWorkbook 对象，strPath 传入文件路径
        Workbook workbook = WorkbookFactory.create(is);
        // 读取第一章表格内容
        Sheet sheet = workbook.getSheetAt(0);
        // 定义 row、cell
        Row row;
        String cell;
        // 循环输出表格中的内容
        row = sheet.getRow(1);
        String spfsnlType = row.getCell(1).toString();
        System.out.println("所属专业--->" + spfsnlType);
        List<TacExcel> tacExcels = new ArrayList<>();
        for (int i = sheet.getFirstRowNum() + 4; i < sheet.getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            int total = 12;
            TacExcel tacExcel = new TacExcel();
            for (int j = row.getFirstCellNum(); j < total; j++) {
                System.out.println(i + "j-->" + row.getCell(j));
                if (row.getCell(j) == null) {
                    break;
                }
                cell = row.getCell(j).toString();

                if (j == 0) {
                    tacExcel.setCell0(cell);
                }
                if (j == 1) {
                    tacExcel.setCell1(cell);
                }
                if (j == 2) {
                    tacExcel.setCell2(cell);
                }
                if (j == 3) {
                    tacExcel.setCell3(cell);
                }
                if (j == 4) {
                    if (StringUtils.isNotBlank(cell)) {
                        tacExcel.setCell4("1");
                    } else {
                        tacExcel.setCell4("0");
                    }
                    if (!"√".equals(cell)) {
                        tacExcel.setCell4Desc(cell);
                    }
                }
                if (j == 5) {
                    if (StringUtils.isNotBlank(cell)) {
                        tacExcel.setCell5("1");
                    } else {
                        tacExcel.setCell5("0");
                    }
                    if (!"√".equals(cell)) {
                        tacExcel.setCell5Desc(cell);
                    }

                }
                if (j == 6) {
                    if (StringUtils.isNotBlank(cell)) {
                        tacExcel.setCell6("1");
                    } else {
                        tacExcel.setCell6("0");
                    }
                    if (!"√".equals(cell)) {
                        tacExcel.setCell6Desc(cell);
                    }
                }
                if (j == 7) {
                    tacExcel.setCell7(cell);
                }
                if (j == 8) {
                    tacExcel.setCell8(cell);
                }
                if (j == 9) {
                    tacExcel.setCell9(cell);
                }
                if (j == 10) {
                    tacExcel.setCell10(cell);
                }
                if (j == 11) {
                    tacExcel.setCell11(cell);
                }
            }
            tacExcels.add(tacExcel);
        }
        pblmstbs = convertTacExcel(tacExcels, spfsnlType, objSubjects);
        return pblmstbs;
    }

    private static List<TacObjPblmstb> convertTacExcel(List<TacExcel> tacExcels, String spfsnlType, List<TacObjSubject> objSubjects) {
        List<TacObjPblmstb> pblmstbs = new ArrayList<>();
        List<TacExcel> class1Excel = new ArrayList<>();
        List<TacExcel> class2Excel = new ArrayList<>();
        List<TacExcel> class3Excel = new ArrayList<>();
        /**分离数据*/
        for (TacExcel excel : tacExcels) {
            if (StringUtils.isBlank(excel.getCell0())) {

                continue;
            }
            if (StringUtils.isBlank(excel.getCell2()) && StringUtils.isBlank(excel.getCell3())) {
                if (!excel.getCell0().contains(".") || excel.getCell0().contains(".0")) {
                    if (excel.getCell0().contains(".0")) {
                        String cell0 = excel.getCell0().substring(0, excel.getCell0().indexOf("."));
                        excel.setCell0(cell0);
                    }
                    class1Excel.add(excel);
                } else {
                    class2Excel.add(excel);
                }
            } else {
                class3Excel.add(excel);
            }
        }
        for (TacExcel excel : class3Excel) {
            TacObjPblmstb objPblmstb = new TacObjPblmstb();
            objPblmstb.setSpfsnlType(spfsnlType);
            for (TacExcel excel1 : class1Excel) {
                if (excel.getCell0().startsWith(excel1.getCell0() + ".")) {
                    objPblmstb.setClass1(excel1.getCell0());
                    objPblmstb.setClass1Name(excel1.getCell1());
                }
            }
            for (TacExcel excel2 : class2Excel) {
                if (excel.getCell0().startsWith(excel2.getCell0() + ".")) {
                    objPblmstb.setClass2(excel2.getCell0());
                    objPblmstb.setClass2Name(excel2.getCell1());
                }
            }
            objPblmstb.setCate0(excel.getCell4());
            objPblmstb.setCate1(excel.getCell5());
            objPblmstb.setCate2(excel.getCell6());
            objPblmstb.setCate0Desc(excel.getCell4Desc());
            objPblmstb.setCate1Desc(excel.getCell5Desc());
            objPblmstb.setCate2Desc(excel.getCell6Desc());
            objPblmstb.setIntm(new Date());

            objPblmstb.setLawContent(excel.getCell3());
            objPblmstb.setRelativeLaw(excel.getCell2());
            objPblmstb.setPblmsDesc(excel.getCell1());
            String subjectIds = "";
            if (StringUtils.isNotBlank(excel.getCell7())) {
                subjectIds = getSubjectId(excel.getCell7(), objSubjects);
            }
            if (StringUtils.isNotBlank(excel.getCell8())) {
                subjectIds += "," + getSubjectId(excel.getCell8(), objSubjects) + "";
            }
            if (StringUtils.isNotBlank(excel.getCell9())) {
                subjectIds += "," + getSubjectId(excel.getCell9(), objSubjects) + "";
            }
            if (StringUtils.isNotBlank(excel.getCell10())) {
                subjectIds += "," + getSubjectId(excel.getCell10(), objSubjects) + "";
            }
            if (subjectIds.startsWith(",")) {
                subjectIds = subjectIds.substring(1, subjectIds.length());
            }
            objPblmstb.setSubjectIds(subjectIds);
            objPblmstb.setSn(excel.getCell0());
            objPblmstb.setNote(excel.getCell11());
            pblmstbs.add(objPblmstb);
        }
        return pblmstbs;
    }

    private static String getSubjectId(String cell7, List<TacObjSubject> objSubjects) {
        if (StringUtils.isBlank(cell7.trim())) {
            return "";
        }
        cell7 = cell7.replace(" ", "");
        for (TacObjSubject subject : objSubjects) {
            if (cell7.trim().startsWith(subject.getSubName())) {
                return subject.getId();
            }
            if (StringUtils.isNotBlank(cell7.trim()) && subject.getSubName().contains(cell7.trim())) {
                return subject.getId();
            }
        }
        return "";
    }

    public static List<ExcelInspPblm> getExcelEmpwtrun(String filePath) throws Exception {
        List<ExcelInspPblm> inspPblmList = new ArrayList<>();
        InputStream is = new FileInputStream(filePath);
        // 构造 XSSFWorkbook 对象，strPath 传入文件路径
        XSSFWorkbook xwb = new XSSFWorkbook(is);
        // 读取第一章表格内容
        XSSFSheet sheet = xwb.getSheetAt(1);
        // 定义 row、cell
        XSSFRow row;
        String cell;
        // 循环输出表格中的内容
        for (int i = sheet.getFirstRowNum() + 2; i < sheet.getPhysicalNumberOfRows() - 1; i++) {
            row = sheet.getRow(i);
            ExcelInspPblm inspPblm = new ExcelInspPblm();
            System.out.println("数为" + i);
            int total = 28;
            for (int j = row.getFirstCellNum(); j < total; j++) {
                cell = String.valueOf(row.getCell(j));
                if (cell == null) {
                    continue;
                }
                if (j == 0) {
                    inspPblm.setNumber(cell);
                }
                if (j == 1) {
                    inspPblm.setProvince(cell);
                }
                if (j == 2) {
                    inspPblm.setArea(cell);
                }
                if (j == 3) {
                    inspPblm.setProjectNm(cell);
                }
                //开始添加管理单位
                if (j == 4) {
                    inspPblm.setFirOrg(cell);
                }
                if (j == 5) {
                    inspPblm.setSecOrg(cell);
                }
                if (j == 6) {
                    inspPblm.setThiOrg(cell);
                }
                if (j == 7) {
                    inspPblm.setInspPblmDesc(cell);
                }
                if (j == 8) {
                    inspPblm.setQualityAttachNo(cell);
                }
                if (j == 9) {
                    inspPblm.setQualitySn(cell);
                }
                if (j == 10) {
                    inspPblm.setHetongNo(cell);
                }
                if (j == 11) {
                    inspPblm.setHetongSN(cell);
                }
                if (j == 12) {
                    inspPblm.setInspCate(cell);
                }
                if (j == 13) {
                    inspPblm.setExp1(cell);
                }
                if (j == 14) {
                    inspPblm.setExp2(cell);
                }
                if (j == 15) {
                    inspPblm.setExp3(cell);
                }
                if (j == 16) {
                    inspPblm.setExp4(cell);
                }
                if (j == 17) {
                    inspPblm.setExp5(cell);
                }
                if (j == 18) {
                    inspPblm.setRevicon(cell);
                }
                if (j == 19) {
                    inspPblm.setReviDetail(cell);
                }
                if (j == 20) {
                    continue;
                }
                if (j == 21) {
                    inspPblm.setCheckLeader(cell);
                }
                if (j == 22) {
                    inspPblm.setCheckPerson(cell);
                }
                if (j == 23) {
                    inspPblm.setFaxianTime(cell);
                }
                if (j == 24) {
                    inspPblm.setRechLeader(cell);
                }
                if (j == 25) {
                    inspPblm.setRechPerson(cell);
                }
                if (j == 26) {
                    inspPblm.setRechTime(cell);
                }
                if (j == 27) {
                    inspPblm.setNote(cell);
                }
            }
            inspPblmList.add(inspPblm);
        }
        return inspPblmList;
    }

    public static List<ExcelInspPblm> getExcelFx(String filePath) throws Exception {
        List<ExcelInspPblm> inspPblmList = new ArrayList<>();
        InputStream is = new FileInputStream(filePath);
        // 构造 XSSFWorkbook 对象，strPath 传入文件路径
        XSSFWorkbook xwb = new XSSFWorkbook(is);
        // 读取第一章表格内容
        XSSFSheet sheet = xwb.getSheetAt(2);
        // 定义 row、cell
        XSSFRow row;
        String cell;
        // 循环输出表格中的内容
        for (int i = sheet.getFirstRowNum() + 2; i < sheet.getPhysicalNumberOfRows() - 1; i++) {
            row = sheet.getRow(i);
            ExcelInspPblm inspPblm = new ExcelInspPblm();
            System.out.println("数为" + i);
            int total = 9;
            for (int j = row.getFirstCellNum(); j < total; j++) {
                cell = row.getCell(j).toString();
                cell = String.valueOf(row.getCell(j));
                if (cell == null) {
                    continue;
                }
                if (j == 0) {
                    inspPblm.setNumber(cell);
                }
                //开始添加管理单位
                if (j == 1) {
                    inspPblm.setFirOrg(cell);
                }
                if (j == 2) {
                    inspPblm.setSecOrg(cell);
                }
                if (j == 3) {
                    inspPblm.setThiOrg(cell);
                }
                if (j == 4) {
                    inspPblm.setExp1(cell);
                }
                if (j == 5) {
                    inspPblm.setExp2(cell);
                }
                if (j == 6) {
                    inspPblm.setExp3(cell);
                }
                if (j == 7) {
                    inspPblm.setExp4(cell);
                }
                if (j == 8) {
                    inspPblm.setExp5(cell);
                }

            }
        }

        return inspPblmList;
    }

    public static List<ExcelInspPblm> getExcel(String filePath) throws Exception {
        List<ExcelInspPblm> inspPblmList = new ArrayList<>();
        InputStream is = new FileInputStream(filePath);
        // 构造 XSSFWorkbook 对象，strPath 传入文件路径
        XSSFWorkbook xwb = new XSSFWorkbook(is);
        // 读取第一章表格内容
        XSSFSheet sheet = xwb.getSheetAt(1);
        // 定义 row、cell
        XSSFRow row;
        String cell;
        // 循环输出表格中的内容
        for (int i = sheet.getFirstRowNum() + 2; i < sheet.getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            ExcelInspPblm inspPblm = new ExcelInspPblm();
            System.out.println("数为" + i);
            int total = 29;
            for (int j = row.getFirstCellNum(); j < total; j++) {
                cell = row.getCell(j).toString();
                if (j == 0) {
                    inspPblm.setNumber(cell);
                }
                if (j == 1) {
                    inspPblm.setProvince(cell);
                }
                if (j == 2) {
                    inspPblm.setArea(cell);
                }
                if (j == 3) {
                    inspPblm.setProjectNm(cell);
                }
                if (j == 4) {
                    inspPblm.setUnitNm(cell);
                }
                if (j == 5) {
                    inspPblm.setMoshi(cell);
                }
                if (j == 6) {
                    inspPblm.setOrgName(cell);
                }
                if (j == 7) {
                    inspPblm.setNature(cell);
                }
                if (j == 8) {
                    inspPblm.setInspPblmDesc(cell);
                }
                if (j == 9) {
                    inspPblm.setQualityAttachNo(cell);
                }
                if (j == 10) {
                    inspPblm.setQualitySn(cell);
                }
                if (j == 11) {
                    inspPblm.setHetongNo(cell);
                }
                if (j == 12) {
                    inspPblm.setHetongSN(cell);
                }
                if (j == 13) {
                    inspPblm.setInspCate(cell);
                }
                if (j == 14) {
                    inspPblm.setExp1(cell);
                }
                if (j == 15) {
                    inspPblm.setExp2(cell);
                }
                if (j == 16) {
                    inspPblm.setExp3(cell);
                }
                if (j == 17) {
                    inspPblm.setExp4(cell);
                }
                if (j == 18) {
                    inspPblm.setExp5(cell);
                }
                if (j == 19) {
                    inspPblm.setRevicon(cell);
                }
                if (j == 20) {
                    inspPblm.setReviDetail(cell);
                }
                if (j == 21) {
                    continue;
                }
                if (j == 22) {
                    inspPblm.setCheckLeader(cell);
                }
                if (j == 23) {
                    inspPblm.setCheckPerson(cell);
                }
                if (j == 24) {
                    inspPblm.setFaxianTime(cell);
                }
                if (j == 25) {
                    inspPblm.setRechLeader(cell);
                }
                if (j == 26) {
                    inspPblm.setRechPerson(cell);
                }
                if (j == 27) {
                    inspPblm.setRechTime(cell);
                }
                if (j == 28) {
                    inspPblm.setNote(cell);
                }
            }
            inspPblmList.add(inspPblm);
        }
        return inspPblmList;
    }

    public static List<ObjInspPblms> getValues(String type, String attach, String filePath) {


        try {
            InputStream is = new FileInputStream(filePath);
            // 构造 XSSFWorkbook 对象，strPath 传入文件路径
            XSSFWorkbook xwb = new XSSFWorkbook(is);
            // 读取第一章表格内容
            XSSFSheet sheet = xwb.getSheetAt(1);
            // 定义 row、cell
            XSSFRow row;
            String cell;
            // 循环输出表格中的内容
            List<ObjInspPblms> inspPblms = new ArrayList<>();
            String title = "";
            String checkPoint = "";
            int sort1 = 0;
            int sort2 = 0;
            for (int i = sheet.getFirstRowNum() + 4; i < sheet.getPhysicalNumberOfRows(); i++) {
                row = sheet.getRow(i);
                ObjInspPblms objInspPblms = new ObjInspPblms();
                int total = row.getPhysicalNumberOfCells();
                for (int j = row.getFirstCellNum(); j < total; j++) {

                    cell = row.getCell(j).toString();
                    if (j == 0) {
                        if (cell.contains(".")) {
                            cell = cell.substring(0, cell.length() - 2);
                        }
                    }
                    if (cell.startsWith("（")) {
                        title = cell.substring(cell.indexOf("）") + 1, cell.length());
                        sort1++;

                    } else {

                        if (cell.length() == 1 && cell.contains(".")) {
                            cell = "00" + cell;
                        }
                        if (cell.length() == 2 && cell.contains(".")) {
                            cell = "0" + cell;
                        }
                        if (j == 0) {
                            objInspPblms.setGuid(type + "000000000000000000000000000" + cell);
                            objInspPblms.setPguid(type + "000000000000000000000000000000");
                            objInspPblms.setAttach(attach);
                            objInspPblms.setType("6");
                            cell = row.getCell(j).toString();
                            cell = cell.substring(0, cell.length() - 2);
                            objInspPblms.setSn(Integer.parseInt(cell) + "");
                            objInspPblms.setInspPblmsName(title);

                        }

                        if (j == 1) {
                            if (!"".equals(cell)) {
                                checkPoint = cell;
                                sort2 = 0;

                            }

                            objInspPblms.setSort1(sort1 + "");
                            checkPoint = checkPoint.replace("\n", "");
                            objInspPblms.setCheckPoint(checkPoint);

                        }
                        if (j == 2) {
                            objInspPblms.setPblmDesc(cell);
                        }
                        if ("".equals(cell)) {
                            continue;
                        }
                        boolean input = true;
                        if (j == 3) {
                            objInspPblms.setInspPblmCate("0");
                            String cell2 = row.getCell(2).toString();
                            if (!"√".equals(cell)) {
                                objInspPblms.setPblmDesc("（" + cell + "）" + cell2);
                            } else {
                                objInspPblms.setPblmDesc(cell2);
                            }
                            sort2++;
                            objInspPblms.setSort2(sort2 + "");

                            inspPblms.add(objInspPblms);
                            input = false;
                        }
                        boolean input1 = true;
                        if (j == 4 && input) {
                            objInspPblms.setInspPblmCate("1");
                            String cell2 = row.getCell(2).toString();
                            if (!"√".equals(cell)) {
                                objInspPblms.setPblmDesc("（" + cell + "）" + cell2);
                            } else {
                                objInspPblms.setPblmDesc(cell2);
                            }
                            sort2++;

                            objInspPblms.setSort2(sort2 + "");
                            inspPblms.add(copyPblms(objInspPblms, sort2));
                            input1 = false;
                        }
                        boolean input2 = true;
                        if (j == 5 && input && input1) {
                            objInspPblms.setInspPblmCate("2");
                            String cell2 = row.getCell(2).toString();
                            if (!"√".equals(cell)) {
                                objInspPblms.setPblmDesc("（" + cell + "）" + cell2);
                            } else {
                                objInspPblms.setPblmDesc(cell2);
                            }
                            sort2++;
                            objInspPblms.setSort2(sort2 + "");
                            inspPblms.add(copyPblms(objInspPblms, sort2));
                            input2 = false;
                        }
                        if ("1".equals(attach)) {
                            if (j == 6 && input && input1 && input2) {
                                objInspPblms.setInspPblmCate("3");
                                String cell2 = row.getCell(2).toString();
                                if (!"√".equals(cell)) {
                                    objInspPblms.setPblmDesc("（" + cell + "）" + cell2);
                                } else {
                                    objInspPblms.setPblmDesc(cell2);
                                }
                                sort2++;
                                objInspPblms.setSort2(sort2 + "");
                                inspPblms.add(copyPblms(objInspPblms, sort2));
                            }
                        }

                    }
                }

            }

            return inspPblms;
        } catch (Exception e) {
            System.out.println("已运行xlRead() : " + e);
        }
        return null;
    }

    public static ObjInspPblms copyPblms(ObjInspPblms inspPblms, int sort2) {
        ObjInspPblms objInspPblms = new ObjInspPblms();
        objInspPblms.setCheckPoint(inspPblms.getCheckPoint());
        objInspPblms.setPblmDesc(inspPblms.getPblmDesc());
        objInspPblms.setInspPblmCate(inspPblms.getInspPblmCate());
        objInspPblms.setType(inspPblms.getType());
        objInspPblms.setSn(inspPblms.getSn());
        objInspPblms.setAttach(inspPblms.getAttach());
        objInspPblms.setSort1(inspPblms.getSort1());
//
        objInspPblms.setSort2(sort2 + "");
        sort2 = sort2 + 1;
        objInspPblms.setInspPblmsName(inspPblms.getInspPblmsName());
        String end = "6" + inspPblms.getGuid().substring(inspPblms.getGuid().length() - 3);
        String pguid = inspPblms.getGuid().substring(0, inspPblms.getGuid().length() - 4);
        objInspPblms.setGuid(pguid + end);
        objInspPblms.setPguid(inspPblms.getPguid());

        return objInspPblms;
    }

    public static List<RsvrObj> getRsvrObjList(String filePath) throws Exception {
        List<RsvrObj> rsvrObjList = new ArrayList<>();
        InputStream is = new FileInputStream(filePath);
        // 构造 XSSFWorkbook 对象，strPath 传入文件路径
        XSSFWorkbook xwb = new XSSFWorkbook(is);
        // 读取第一章表格内容
        XSSFSheet sheet = xwb.getSheetAt(1);
        // 定义 row、cell
        XSSFRow row;
        String cell;
        // 循环输出表格中的内容

        for (int i = sheet.getFirstRowNum() + 3; i < sheet.getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            RsvrObj rsvrObj = new RsvrObj();
            int total = row.getPhysicalNumberOfCells();
            for (int j = row.getFirstCellNum(); j < total; j++) {
                cell = row.getCell(j).toString();
                if (j == 1) {
                    rsvrObj.setProvince(cell);
                }
                if (j == 2) {
                    if (cell.contains("市辖区")) {
                        rsvrObj.setProvince(rsvrObj.getProvince() + "");
                    } else {
                        if (rsvrObj.getProvince().contains("省")) {
                            rsvrObj.setProvince(rsvrObj.getProvince());
                        } else {
                            rsvrObj.setProvince(rsvrObj.getProvince());
                        }
                    }
                    rsvrObj.setCity(cell);
                }
                if (j == 3) {
                    rsvrObj.setCountry(cell);
                }
                if (j == 4) {
                    rsvrObj.setRsvrNm(cell);
                }
                if (j == 5) {
                    rsvrObj.setGuimo(encagl(cell));
                }
                if (j == 6) {
                    rsvrObj.setTop(cell);
                }
                if (j == 7) {
                    rsvrObj.setPoor(isPoor(cell));
                }
                if (j == 8) {
                    rsvrObj.setRepeat(cell);
                }
                if (j == 9) {
                    rsvrObj.setNote(cell);
                }
                if (j == 10) {
                    rsvrObj.setOrgNm(cell);
                    if ("长委".equals(cell)) {
                        rsvrObj.setOrgNm("长江委");
                    }
                }
            }
            rsvrObjList.add(rsvrObj);
        }
        return rsvrObjList;
    }

    private static String isPoor(String cell) {
        if (StringUtils.isBlank(cell) && "否".equals(cell)) {
            return "2";
        }
        return "1";
    }


    private static String encagl(String desc) {
        if ("小Ⅱ".equals(desc)) {
            return "5";
        }
        if ("小Ⅰ".equals(desc)) {
            return "4";
        }

        return "";
    }

    public static List<PblmObj> getPblmObjList(String filePath) throws Exception {
        List<PblmObj> rsvrObjList = new ArrayList<>();
        InputStream is = new FileInputStream(filePath);
        // 构造 XSSFWorkbook 对象，strPath 传入文件路径
        XSSFWorkbook xwb = new XSSFWorkbook(is);
        // 读取第一章表格内容
        XSSFSheet sheet = xwb.getSheetAt(3);
        // 定义 row、cell
        XSSFRow row;
        String cell;
        // 循环输出表格中的内容

        for (int i = sheet.getFirstRowNum() + 3; i < sheet.getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            PblmObj rsvrObj = new PblmObj();
            int total = row.getPhysicalNumberOfCells();
            for (int j = row.getFirstCellNum(); j < total; j++) {
                cell = row.getCell(j).toString();
                if (j == 1) {
                    rsvrObj.setProvince(cell);
                }
                if (j == 2) {
                    if (cell.contains("市辖区")) {
                        rsvrObj.setProvince(rsvrObj.getProvince() + "");
                    } else {
                        if (rsvrObj.getProvince().contains("省")) {
                            rsvrObj.setProvince(rsvrObj.getProvince());
                        } else {
                            rsvrObj.setProvince(rsvrObj.getProvince() + "");
                        }
                    }
                    rsvrObj.setCity(cell);
                }
                if (j == 3) {
                    rsvrObj.setCountry(cell);
                }
                if (j == 4) {
                    rsvrObj.setRsvrNm(cell);
                }
                if (j == 5) {
                    rsvrObj.setGuimo(encagl(cell));
                }
                if (j == 6) {
                    rsvrObj.setOrgNm(cell);
                }
                if (j == 7) {
                    rsvrObj.setPblmName(isPoor(cell));
                }
                if (j == 8) {
                    rsvrObj.setCheckPoint(cell);
                }
                if (j == 9) {
                    rsvrObj.setPblmDesc(cell);
                }
                if (j == 10) {
                    rsvrObj.setPblmCate(setCate(cell));

                }
                if (j == 11) {
                    rsvrObj.setAttach(cell);
                }
                if (j == 12) {
                    rsvrObj.setSnNum(cell);
                }
            }
            rsvrObjList.add(rsvrObj);
        }
        return rsvrObjList;
    }

    public static List<ImpPblmInfo> getPblmObjListByFile(MultipartFile file) {
        List<ImpPblmInfo> rsvrObjList = new ArrayList<>();
        try {
            InputStream is = file.getInputStream();
            // 构造 XSSFWorkbook 对象，strPath 传入文件路径
            XSSFWorkbook xwb = new XSSFWorkbook(is);
            // 读取第一章表格内容
            XSSFSheet sheet = xwb.getSheetAt(3);
            // 定义 row、cell
            XSSFRow row;
            String cell = "";
            // 循环输出表格中的内容

            for (int i = sheet.getFirstRowNum() + 3; i < sheet.getPhysicalNumberOfRows(); i++) {
                row = sheet.getRow(i);
                ImpPblmInfo rsvrObj = new ImpPblmInfo();
                int total = row.getPhysicalNumberOfCells();
                for (int j = row.getFirstCellNum(); j < total; j++) {
                    if (row != null) {
                        if (row.getCell(j) != null) {
                            cell = row.getCell(j).toString();
                            if (StringUtils.isNotBlank(cell)) {
                                cell = cell.trim();
                            }
                        }
                    }
                    if (j == 0) {
                        rsvrObj.setSn(cell);
                    }
                    if (j == 1) {
                        rsvrObj.setProName(cell);
                    }
                    if (j == 2) {
                        if (cell.contains("市辖区")) {
                            rsvrObj.setProName(rsvrObj.getProName() + "");
                        } else {
                            if (rsvrObj.getProName().contains("省")) {
                                rsvrObj.setProName(rsvrObj.getProName());
                            } else {
                                rsvrObj.setProName(rsvrObj.getProName() + "");
                            }
                        }
                        rsvrObj.setCityName(cell);
                    }
                    if (j == 3) {
                        rsvrObj.setCountyName(cell);
                    }
                    if (j == 4) {
                        rsvrObj.setRsName(cell);
                    }
                    if (j == 5) {
                        rsvrObj.setEngScal(encagl(cell));
                    }
                    if (j == 6) {
                        rsvrObj.setUnit(cell);
                    }
                    if (j == 7) {
                        rsvrObj.setPblmType(isPoor(cell));
                    }
                    if (j == 8) {
                        rsvrObj.setPblmOption(cell);
                    }
                    if (j == 9) {
                        rsvrObj.setPblmDesc(cell);
                    }
                    if (j == 10) {
                        rsvrObj.setPblmLevl(setCate(cell));

                    }
                    if (j == 11) {
                        rsvrObj.setAttach(cell);
                    }
                    if (j == 12) {
                        rsvrObj.setPblmSn(cell);
                    }
                    if (j == 13) {
                        rsvrObj.setNote(cell);
                    }
                }
                rsvrObjList.add(rsvrObj);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return rsvrObjList;
    }

    private static String setCate(String cell) {
        if ("一般".equals(cell)) {
            return "0";
        }
        if ("较重".equals(cell)) {
            return "1";
        }
        if ("严重".equals(cell)) {
            return "2";

        }
        return "3";
    }


    public static List<ExcelInspLevelDto> getExcelLevel(String filePath) throws Exception {
        InputStream is = new FileInputStream(filePath);
        // 构造 XSSFWorkbook 对象，strPath 传入文件路径
        XSSFWorkbook xwb = new XSSFWorkbook(is);
        int sheetSize = xwb.getNumberOfSheets();
        int id = 0;
        int disLv = 1;
        List<ExcelInspLevelDto> list = new ArrayList<>();
        for (int i = 0; i < sheetSize; i++) {
            XSSFSheet sheet = xwb.getSheetAt(i);
            XSSFRow row = sheet.getRow(sheet.getFirstRowNum());
            Map<String, Integer> map = getIdAndDisLv(row);
            id = map.get("ID");
            disLv = map.get("DISLV");
            for (int j = sheet.getFirstRowNum() + 1; j < sheet.getPhysicalNumberOfRows() - 1; j++) {
                ExcelInspLevelDto level = new ExcelInspLevelDto();
                XSSFRow con = sheet.getRow(j);
                level.setId(con.getCell(id).toString());
                level.setDisPlayLevel(getCellCon(con.getCell(disLv)));
                list.add(level);
            }
        }
        return list;
    }

    private static String getCellCon(XSSFCell cell) {
        if (cell == null) {
            return "10";
        }
        if (StringUtils.isBlank(cell.toString())) {
            return "10";
        }
        String con = String.valueOf(cell);
        if (con.indexOf("级") > 0) {
            return con.substring(0, con.indexOf("级"));
        }
        return con;
    }

    private static Map<String, Integer> getIdAndDisLv(XSSFRow row) {
        Map<String, Integer> map = new HashMap<>();
        if ("ID".equalsIgnoreCase(String.valueOf(row.getCell(0)))) {
            map.put("ID", 0);
            map.put("DISLV", 1);
        } else {
            map.put("ID", 1);
            map.put("DISLV", 0);
        }
        return map;
    }

    public static List<ExcelInspPersDto> getExcelPers(String filePath) throws Exception {
        InputStream is = new FileInputStream(filePath);
        // 构造 XSSFWorkbook 对象，strPath 传入文件路径
        XSSFWorkbook xwb = new XSSFWorkbook(is);
        XSSFSheet sheet = xwb.getSheetAt(0);
        List<ExcelInspPersDto> list = new ArrayList<>();
        for (int i = sheet.getFirstRowNum() + 3; i < sheet.getPhysicalNumberOfRows(); i++) {
            XSSFRow row = sheet.getRow(i);
            int total = row.getPhysicalNumberOfCells();
            String cell = null;
            ExcelInspPersDto pers = new ExcelInspPersDto();
            for (int j = row.getFirstCellNum(); j < total; j++) {
                cell = String.valueOf(row.getCell(j));
                if (j == 0) {
                    pers.setId(cell);
                }
                if (j == 1) {
                    pers.setOrgName(cell);
                    String orgId = "041";
                    if ("长江委".equals(cell)) {
                        orgId = "020";
                    } else if ("黄委".equals(cell)) {
                        orgId = "021";
                    } else if ("淮委".equals(cell)) {
                        orgId = "022";
                    } else if ("海委".equals(cell)) {
                        orgId = "023";
                    } else if ("珠委".equals(cell)) {
                        orgId = "024";
                    } else if ("松辽委".equals(cell)) {
                        orgId = "025";
                    } else if ("太湖局".equals(cell)) {
                        orgId = "026";
                    } else if ("部督查办".equals(cell)) {
                        orgId = "027";
                    }
                    pers.setOrgId(orgId);
                }
                if (j == 2) {
                    pers.setGroName(cell);
                }
                if (j == 3) {
                    pers.setHeadman(cell);
                }
                if (j == 4) {
                    pers.setHeadNm(cell);
                }
                if (j == 5) {
                    pers.setMembers(cell);
                }
                if (j == 6) {
                    pers.setMemNm(cell);
                }
                if (j == 7) {
                    pers.setConPers(cell);
                }
                if (j == 8) {
                    pers.setConNm(cell);
                }
            }
            list.add(pers);
        }
        return list;
    }

    public static List<ExcelInspPersDto> getExcelPersByFile(MultipartFile file) throws Exception {
        InputStream is = file.getInputStream();
        // 构造 XSSFWorkbook 对象，strPath 传入文件路径
        XSSFWorkbook xwb = new XSSFWorkbook(is);
        XSSFSheet sheet = xwb.getSheetAt(0);
        List<ExcelInspPersDto> list = new ArrayList<>();
        String orgName = "";
        String proName = "";
        for (int i = sheet.getFirstRowNum() + 3; i < sheet.getPhysicalNumberOfRows(); i++) {
            XSSFRow row = sheet.getRow(i);
            int total = row.getPhysicalNumberOfCells();
            String cell = null;
            ExcelInspPersDto pers = new ExcelInspPersDto();
            for (int j = row.getFirstCellNum(); j < total; j++) {
                cell = String.valueOf(row.getCell(j));
                if (j == 0) {
                    pers.setId(cell);
                }
                if (j == 1) {
                    if (StringUtils.isEmpty(cell)) {
                        cell = orgName;
                    } else {
                        orgName = cell;
                    }
                    pers.setOrgName(cell);
                    String orgId = "041";
                    if ("长江委".equals(cell)) {
                        orgId = "020";
                    } else if ("黄委".equals(cell)) {
                        orgId = "021";
                    } else if ("淮委".equals(cell)) {
                        orgId = "022";
                    } else if ("海委".equals(cell)) {
                        orgId = "023";
                    } else if ("珠委".equals(cell)) {
                        orgId = "024";
                    } else if ("松辽委".equals(cell)) {
                        orgId = "025";
                    } else if ("太湖局".equals(cell)) {
                        orgId = "026";
                    } else if ("部督查办".equals(cell)) {
                        orgId = "027";
                    } else {
                        orgId = "";
                    }
                    pers.setOrgId(orgId);
                }
                if (j == 2) {
                    if (StringUtils.isEmpty(cell)) {
                        cell = proName;
                    } else {
                        proName = cell;
                    }
                    pers.setGroName(cell);
                }
                if (j == 3) {
                    pers.setHeadman(cell);
                }
                if (j == 4) {
                    pers.setHeadNm(cell);
                }
                if (j == 5) {
                    pers.setMembers(cell);
                }
                if (j == 6) {
                    pers.setMemNm(cell);
                }
                if (j == 7) {
                    pers.setConPers(cell);
                }
                if (j == 8) {
                    pers.setConNm(cell);
                }
            }
            list.add(pers);
        }
        return list;
    }

    public static List<ExcelInspRsDto> getRsvrObjListByFile(MultipartFile file) throws Exception {
        List<ExcelInspRsDto> rsvrObjList = new ArrayList<>();
        InputStream is = file.getInputStream();
        // 构造 XSSFWorkbook 对象，strPath 传入文件路径
        XSSFWorkbook xwb = new XSSFWorkbook(is);
        // 读取第一章表格内容
        XSSFSheet sheet = xwb.getSheetAt(1);
        // 定义 row、cell
        XSSFRow row;
        String cell;
        // 循环输出表格中的内容

        for (int i = sheet.getFirstRowNum() + 3; i < sheet.getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            ExcelInspRsDto rsvrObj = new ExcelInspRsDto();
            int total = row.getPhysicalNumberOfCells();
            for (int j = row.getFirstCellNum(); j < total; j++) {
                cell = row.getCell(j).toString();
                cell = cell.trim();
                if (j == 1) {
                    rsvrObj.setProvince(cell);
                }
                if (j == 2) {
                    if (cell.contains("市辖区")) {
                        rsvrObj.setProvince(rsvrObj.getProvince() + "");
                    } else {
                        if (rsvrObj.getProvince().contains("省")) {
                            rsvrObj.setProvince(rsvrObj.getProvince());
                        } else {
                            rsvrObj.setProvince(rsvrObj.getProvince());
                        }
                    }
                    rsvrObj.setCity(cell);
                }
                if (j == 3) {
                    rsvrObj.setCountry(cell);
                }
                if (j == 4) {
                    rsvrObj.setRsvrNm(cell);
                }
                if (j == 5) {
                    rsvrObj.setGuimo(encagl(cell));
                }
                if (j == 6) {
                    rsvrObj.setTop(cell);
                }
                if (j == 7) {
                    rsvrObj.setPoor(isPoor(cell));
                }
                if (j == 8) {
                    rsvrObj.setRepeat(isPoor(cell));
                }
                if (j == 9) {
                    rsvrObj.setNote(cell);
                }
                if (j == 10) {
                    rsvrObj.setOrgNm(cell);
                    if ("长委".equals(cell)) {
                        rsvrObj.setOrgNm("长江委");
                    }
                }
            }
            rsvrObjList.add(rsvrObj);
        }
        return rsvrObjList;
    }

    public static List<String> findYongshuihu(String filePath) throws Exception {
        InputStream is = new FileInputStream(filePath);
        List<String> arrays = new ArrayList<>();
        List<String> modelarrays = new ArrayList<>();
        // 构造 XSSFWorkbook 对象，strPath 传入文件路径
        XSSFWorkbook xwb = new XSSFWorkbook(is);
        XSSFSheet sheet = xwb.getSheetAt(0);
        for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) {
            XSSFRow row = sheet.getRow(i);
            String cell = String.valueOf(row.getCell(0));
            arrays.add(cell);
        }
        for (String str : arrays) {
            String[] stres = str.split("、");
            for (int i = 0; i < stres.length; i++) {
                if (StringUtils.isBlank(stres[i])) {
                    continue;
                }
                String cell = stres[i].replace("*", "");
                if (cell.contains(".")) {
                    cell = cell.substring(cell.indexOf(".") + 1, cell.length());
                }
                modelarrays.add(cell);
            }
        }
        return modelarrays;
    }

    public static Set<String> getTacExcelList(String absolutePath) throws Exception {
        InputStream is = new FileInputStream(absolutePath);
        // 构造 XSSFWorkbook 对象，strPath 传入文件路径
        Workbook workbook = WorkbookFactory.create(is);
        // 读取第一章表格内容
        Sheet sheet = workbook.getSheetAt(0);
        // 定义 row、cell
        Row row;
        String cell;
        // 循环输出表格中的内容
        row = sheet.getRow(1);
        Set<String> set = new HashSet<>();
        for (int i = sheet.getFirstRowNum() + 4; i < sheet.getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            int total = 12;
            for (int j = row.getFirstCellNum(); j < total; j++) {
                if (row.getCell(j) == null) {
                    break;
                }
                cell = row.getCell(j).toString();
                if (j == 7) {
                    set.add(cell.trim());
                }
                if (j == 8) {
                    set.add(cell.trim());
                }
                if (j == 9) {
                    set.add(cell.trim());
                }
                if (j == 10) {
                    set.add(cell.trim());
                }
            }
        }
        return set;
    }
}