package cn.com.goldenwater.dcproj.controller.importex;

import cn.com.goldenwater.core.web.BaseController;
import cn.com.goldenwater.core.web.BaseResponse;
import cn.com.goldenwater.dcproj.dao.*;
import cn.com.goldenwater.dcproj.model.*;
import cn.com.goldenwater.dcproj.param.AttAdXBaseParam;
import cn.com.goldenwater.dcproj.param.AttWintBaseBParam;
import cn.com.goldenwater.id.util.UuidUtil;
import org.apache.commons.lang.math.NumberUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ooxml.POIXMLDocument;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import java.io.*;
import java.util.*;
import java.util.regex.Pattern;

/**
 * 取水excel读取准备入库
 */
@RestController
@RequestMapping(value = "/qs")
public class QsReadExcelController extends BaseController {

    @Autowired
    private AttWintBaseBDao wintBaseBDao;
    @Autowired
    private AttWiuBaseDao wiuBaseDao;
    @Autowired
    private AttWiuIntPlDao wiuIntPlDao;
    @Autowired
    private AttWiuMeasDwtDao measDwtDao;
    @Autowired
    private AttWiuWatConsDao wiuWatConsDao;
    @Autowired
    private AttWiuWatLicDao wiuWatLicDao;
    @Autowired
    private AttAdXBaseDao attAdXBaseDao;

    //统计读取excel文件的个数
    public static int countDealExcelNum = 0;
    //统计是否有多用户公用取水口的情况
    public static int countIsHasSheet3 = 0;
    //用list存一下发生空指针时的坐标
    public static List<String> zbs = new ArrayList<>();
    public static List<String> NullList = new ArrayList<>();
    public static List<String> qushuikou = new ArrayList<>();
    //存一下文件名
    public static String doDealIngFileName = "";
    //存一下是多用户的文件名
    public static List<String> dyhFileNames = new ArrayList<>();


    //非长江流域的
//    public static String originalPath = "C:\\Users\\81229\\Desktop\\全国重点监管取水口汇总20190725\\全国重点监管取水口--初始台账\\珠江委审批全国重点监管取水口初始台账"; //非多用户
//    public static String originalPath = "C:\\Users\\81229\\Desktop\\全国重点监管取水口汇总20190725\\全国重点监管取水口--初始台账\\湖南"; //非多用户 贵州,一级区名称不同于其他excel这里没值
//    public static String originalPath = "C:\\Users\\81229\\Desktop\\全国重点监管取水口汇总20190725\\全国重点监管取水口--初始台账\\河北省"; //非多用户 海委,虽然写了多用户取水,但是没填其他sheet
//    public static String originalPath = "C:\\Users\\81229\\Desktop\\全国重点监管取水口汇总20190725\\全国重点监管取水口--初始台账\\江苏省"; //黑龙江,有多用户的情况,34,77,78,79,89
//    public static String originalPath = "C:\\Users\\81229\\Desktop\\全国重点监管取水口汇总20190725\\全国重点监管取水口--初始台账\\内蒙古自治区"; //黑龙江,有多用户的情况,34,77,78,79,89
//    public static String originalPath = "C:\\Users\\81229\\Desktop\\全国重点监管取水口汇总20190725"; //黑龙江,有多用户的情况,34,77,78,79,89
    public static String originalPath = "D:\\home\\全国重点监管取水口汇总20190903复核后更新"; //黑龙江,有多用户的情况,34,77,78,79,89
//    public static String originalPath = "D:\\home\\无法解析"; //黑龙江,有多用户的情况,34,77,78,79,89
//    public static String originalPath = "C:\\Users\\81229\\Desktop\\全国重点监管取水口汇总2019072777\\全国重点监管取水口--初始台账"; //黑龙江,有多用户的情况,34,77,78,79,89

    static Pattern pattern = Pattern.compile("([1-9]\\d*\\.?\\d*)|(0\\.\\d*[1-9])");

    @RequestMapping(value = "/index", method = RequestMethod.GET)
    public BaseResponse index() throws Exception {
        //加载数据库链接
        //循环读取,处理文件夹的情况
        readFileAndGetDataByCycle(originalPath);
        System.out.println("发生空指针异常时的坐标有:" + Arrays.toString(zbs.toArray()));
        System.out.println("异常文件" + Arrays.toString(NullList.toArray()));
        System.out.println("名字为空--" + Arrays.toString(qushuikou.toArray()));
        return buildSuccessResponse();
    }

    /**
     * 根据路径读取excel并获取数据
     */
    private void readFileAndGetDataByCycle(String originalPath) throws Exception {
        //根据path读取文件
        File file = new File(originalPath);
        //递归处理文件
        dealFile(file);
    }

    /**
     * 循环处理file
     *
     * @param file
     */
    private void dealFile(File file) throws Exception {
        //先判断file是否是文件
        if (file.isDirectory()) {
            File[] files = file.listFiles();
            if (null != files && 0 < files.length) {
                for (File f : files) {
                    //递归处理
                    dealFile(f);
                }
            }
        } else {
            countDealExcelNum++;
            //是excel,处理数据

            Workbook wb = readExcel(file);
            if (wb == null) {
                return;
            }
            doDealIngFileName = file.getName();
            //暂时是输出一句话
//            dealDataAndToConsole(wb);
            System.out.println("当前处理文件--->" + doDealIngFileName);
            //处理wb,也就是excel
            if (file.getName().contains("对应名录") || !file.getName().contains("名录")) {
                Map<String, String> map = dealDataAndSaveInMap(wb);
                System.out.println("处理的文件名是 :" + file.getName());
                int qskcstzb = wb.getSheetIndex("取水口初始台账表");
                System.out.println("获取数据的sheet的索引是:" + wb.getSheetIndex("取水口初始台账表"));
                System.out.println("处理的excel文件个数 :" + countDealExcelNum);
                System.out.println("是否有多用户公用取水口的情况 :" + countIsHasSheet3);
                System.out.println("多用户取水的文件名 :" + Arrays.toString(dyhFileNames.toArray()));
                //插入数据库

                ArrayList<String> params1 = new ArrayList<>();
                ArrayList<String> params2 = new ArrayList<>();
                ArrayList<String> params3 = new ArrayList<>();
                ArrayList<String> params4 = new ArrayList<>(); //对应多取水户2
                ArrayList<String> params5 = new ArrayList<>(); //对应多取水户2
                ArrayList<String> params6 = new ArrayList<>(); //对应多取水户3
                ArrayList<String> params7 = new ArrayList<>(); //对应多取水户3
                ArrayList<String> params8 = new ArrayList<>(); //对应多取水户4
                ArrayList<String> params9 = new ArrayList<>(); //对应多取水户4
                ArrayList<String> params10 = new ArrayList<>(); //对应多取水户5
                ArrayList<String> params11 = new ArrayList<>(); //对应多取水户5
                String idA = UUID.randomUUID().toString().replace("-", "");
                String idB = UUID.randomUUID().toString().replace("-", "");
                String idC = UUID.randomUUID().toString().replace("-", "");
                String id4 = UUID.randomUUID().toString().replace("-", ""); //多用户2 上半部分
                String id5 = UUID.randomUUID().toString().replace("-", ""); //多用户2 下半部分
                String id6 = UUID.randomUUID().toString().replace("-", ""); //多用户3 上半部分
                String id7 = UUID.randomUUID().toString().replace("-", ""); //多用户3 下半部分
                String id8 = UUID.randomUUID().toString().replace("-", ""); //多用户4 上半部分
                String id9 = UUID.randomUUID().toString().replace("-", ""); //多用户4 下半部分
                String id10 = UUID.randomUUID().toString().replace("-", ""); //多用户5 上半部分
                String id11 = UUID.randomUUID().toString().replace("-", ""); //多用户5 下半部分
                params1.add(idA);
                params1.add(file.getName());
                params2.add(idB);
                params2.add(file.getName());
                params3.add(idC);
                params3.add(file.getName());
                params4.add(id4);
                params4.add(file.getName());
                params5.add(id5);
                params5.add(file.getName());
                params6.add(id6);
                params6.add(file.getName());
                params7.add(id7);
                params7.add(file.getName());
                params8.add(id8);
                params8.add(file.getName());
                params9.add(id9);
                params9.add(file.getName());
                params10.add(id10);
                params10.add(file.getName());
                params11.add(id11);
                params11.add(file.getName());
                //遍历map 输出值
                int index = 0;
                for (Map.Entry<String, String> entry : map.entrySet()) {
                    index++;
                    System.out.print(entry.getKey() + "=" + entry.getValue() + "\t");
                    //System.out.println(entry.getKey() + "=" + entry.getValue());
                    if (22 > params1.size()) {
                        params1.add(entry.getValue());
                    }
                    //组织234部分的数据
                    if (21 <= index && 55 >= index) {
                        params2.add(entry.getValue());
                    }
                    //组织5部分
                    if (56 <= index && 81 >= index) {
                        params3.add(entry.getValue());
                    }
                    //多用户2 上半部分
                    if (82 <= index && 116 >= index) {
                        params4.add(entry.getValue());
                    }
                    //多用户2 下半部分
                    if (117 <= index && 142 >= index) {
                        params5.add(entry.getValue());
                    }
                    //多用户3 上半部分
                    if (143 <= index && 177 >= index) {
                        params6.add(entry.getValue());
                    }
                    //多用户3 下半部分
                    if (178 <= index && 203 >= index) {
                        params7.add(entry.getValue());
                    }
                    //多用户4 上半部分
                    if (204 <= index && 238 >= index) {
                        params8.add(entry.getValue());
                    }
                    //多用户4 下半部分
                    if (239 <= index && 264 >= index) {
                        params9.add(entry.getValue());
                    }
                    //多用户5 上半部分
                    if (265 <= index && 299 >= index) {
                        params10.add(entry.getValue());
                    }
                    //多用户5 下半部分
                    if (300 <= index && 325 >= index) {
                        params11.add(entry.getValue());
                    }

                }
                params2.add(idA);
                params3.add(idB);
                params4.add(idA); //多用户2上半部分
                params5.add(id4); //多用户2下半部分
                params6.add(idA); //多用户3上半部分
                params7.add(id6); //多用户3上半部分
                params8.add(idA); //多用户4上半部分
                params9.add(id8); //多用户4上半部分
                params10.add(idA); //多用户5上半部分
                params11.add(id10); //多用户5上半部分
                //System.out.println("取水口下半部分表的参数:" + params2);
                //System.out.println("参数2的参数长度:" + params2.size());
//                取水口基本情况表--ATT_WINT_BASE_B
//                取用水户基础信息表--ATT_WIU_BASE
//                取水户计量及取用水情况--ATT_WIU_MEAS_DWT
//                取水户实际取水量--ATT_WIU_WAT_CONS
//                取水许可审批情况--ATT_WIU_WAT_LIC
//                取水户取水计划情况--ATT_WIU_INT_PL
                String wintCode = "";
                String wintId = params1.get(0);
                if (params1.size() > 0) {
//                    template.update(sql1.toString(),params1.toArray()); //表a
                    //包含取水口基本情况
                    AttWintBaseB wintBaseB = new AttWintBaseB();
                    wintBaseB.setId(params1.get(0));
                    try {
                        wintBaseB.setWintName(params1.get(2));
                    } catch (Exception e) {
                        System.out.println("读取失败问文件" + doDealIngFileName);
                        NullList.add(doDealIngFileName);
                        return;
                    }

                    AttWintBaseBParam wintBaseBParam = new AttWintBaseBParam();
                    wintBaseBParam.setWintName(params1.get(2));
                    List<AttWintBaseB> wintBaseB1 = wintBaseBDao.findList(wintBaseBParam);
                    if (wintBaseB1 != null && !wintBaseB1.isEmpty()) {
                        return;
                    }
                    wintBaseB.setAdName(params1.get(3) + "-" + params1.get(4) + "-" + params1.get(5));
                    AttAdXBaseParam xBaseParam = new AttAdXBaseParam();
                    List<AttAdXBase> attAdXBaseList = null;
                    if (StringUtils.isNotBlank(params1.get(5))) {
                        String xian = params1.get(5);
                        if (xian.contains("（")) {
                            xian = xian.replace("（", "").replace("）", "");
                        }
                        if (xian.length() > 3) {
                            xian = xian.substring(0, 3);
                        }
                        xBaseParam.setAdName(xian);
                        attAdXBaseList = attAdXBaseDao.findList(xBaseParam);
                    }

                    AttAdXBase attAdXBase = null;
                    if (attAdXBaseList == null || attAdXBaseList.size() == 0 || attAdXBaseList.size() > 1) {
                        String xian = params1.get(4);
                        if (xian.contains("（")) {
                            xian = xian.replace("（", "").replace("）", "");
                        }
                        xBaseParam.setAdName(xian);
                        attAdXBaseList = attAdXBaseDao.findList(xBaseParam);
                        if (attAdXBaseList == null || attAdXBaseList.isEmpty()) {
                            if (StringUtils.isNotBlank(params1.get(5))) {
                                xian = params1.get(5).substring(0, 2);
                            } else {
                                xian = params1.get(4).substring(0, params1.get(4).indexOf("市"));
                            }
                            xBaseParam.setAdName(xian);
                            attAdXBaseList = attAdXBaseDao.findList(xBaseParam);
                            if (attAdXBaseList != null && attAdXBaseList.size() == 1) {
                                attAdXBase = attAdXBaseList.get(0);
                            }
                            for (AttAdXBase xBase : attAdXBaseList) {
                                if (xBase.getAdFullName().contains(params1.get(3))) {
                                    attAdXBase = xBase;
                                }
                            }
                            if (attAdXBase == null && attAdXBaseList != null) {
                                attAdXBase = attAdXBaseList.get(0);
                            }
                        } else {
                            attAdXBase = attAdXBaseList.get(0);
                        }
                    } else {
                        attAdXBase = attAdXBaseList.get(0);
                    }

                    wintBaseB.setAdCode(attAdXBase.getAdCode());
                    wintBaseB.setCharDepNm(params1.get(11));
                    wintBaseB.setCharDepPers(params1.get(12));
                    wintBaseB.setCharDepTel(params1.get(13));
                    if (!"填写".equals(params1.get(20))) {
                        if (params1.get(20).contains("（")) {
                            String desyQ = params1.get(20).substring(0, params1.get(20).indexOf("（"));
                            wintBaseB.setDesDayQ(Double.parseDouble(desyQ));
                        } else {
                            if (params1.get(20).contains("×")) {
                                String[] arrays = params1.get(20).split("×");
                                double desq = Double.parseDouble(arrays[0]) * Integer.parseInt(arrays[1]);
                                wintBaseB.setDesDayQ(desq);
                            } else {
                                if (StringUtils.isNotBlank(params1.get(20)) && !"填写".equals(params1.get(20))) {
                                    if (params1.get(20).contains("万")) {
                                        String desc = params1.get(20).substring(0, params1.get(20).length() - 1);
                                        wintBaseB.setDesDayQ(Double.parseDouble(desc));
                                    } else {
                                        if (isNumeric(params1.get(20))) {
                                            wintBaseB.setDesDayQ(Double.parseDouble(params1.get(20)));
                                        }
                                    }
                                }
                            }
                        }
                    }
                    wintBaseB.setInTm(new Date());
                    if (params1.get(21).contains("万")) {
                        String desc = params1.get(21).substring(0, params1.get(21).indexOf("万"));
                        wintBaseB.setDesYearQ(Double.parseDouble(desc));
                    } else {
                        if (StringUtils.isNotBlank(params1.get(21)) && !"填写".equals(params1.get(21))) {
                            if (params1.get(21).contains("-")) {
                                String[] arrays = params1.get(21).split("-");
                                wintBaseB.setDesYearQ(Double.parseDouble(arrays[0]));
                            } else {
                                if (isNumeric(params1.get(21))) {
                                    wintBaseB.setDesYearQ(Double.parseDouble(params1.get(21)));
                                }
                            }
                        }
                    }

                    wintBaseB.setLocWrz1rdName(params1.get(9));
                    if (StringUtils.isNotBlank(params1.get(19)) && params1.get(19).contains("（")) {
                        String desyQ = params1.get(19).replace("m³/s", "");
                        desyQ = desyQ.substring(0, desyQ.indexOf("（"));
                        wintBaseB.setDesQ(Double.parseDouble(desyQ));
                    } else {
                        if (StringUtils.isNotBlank(params1.get(19)) && params1.get(19).contains("设计")) {
                            String desc = params1.get(19).replace("设计", "");
                            if (desc.contains("(")) {
                                desc = desc.substring(0, desc.indexOf("("));
                                wintBaseB.setDesQ(Double.parseDouble(desc));
                            }
                        } else if (StringUtils.isNotBlank(params1.get(19)) && !"填写".equals(params1.get(19))) {
                            if (isNumeric(params1.get(19))) {
                                wintBaseB.setDesQ(Double.parseDouble(params1.get(19)));
                            }
                        }

                    }

                    wintBaseB.setLocWrz2rdName("");
                    wintBaseB.setLocWrz3rdName("");
                    wintBaseB.setUpTm(new Date());
                    wintBaseB.setWainIntName(params1.get(14));
                    wintBaseB.setWainIntType(getType(params1.get(15)));
                    if (StringUtils.isNotBlank(params1.get(8)) && !"填写".equals(params1.get(8)) && !"/".equals(params1.get(8))) {
                        wintBaseB.setWainLat(DuFenMiaoToLat(params1.get(8)));
                    }
                    if (StringUtils.isNotBlank(params1.get(7)) && !"填写".equals(params1.get(7)) && !"/".equals(params1.get(7))) {
                        wintBaseB.setWainLong(DuFenMiaoToLon(params1.get(7)));
                    }
                    if ("单一用户取水口".equals(params1.get(10))) {
                        wintBaseB.setWainType(1 + "");
                    } else {
                        wintBaseB.setWainType(2 + "");
                    }
                    //1:河道内的水库;2:湖泊;3:河流;4:跨省引调水工程;9:其他
                    wintBaseB.setWainWasoName(params1.get(18));
                    wintBaseB.setWainWasoType(getWasoType(params1.get(16)));
                    wintBaseB.setWintPos(params1.get(6));
                    System.out.println(params1);
                    String time = (System.currentTimeMillis() + "");
                    time = time.substring(time.length() - 4, time.length());

                    wintCode = attAdXBase.getAdCode() + time + "";
                    wintBaseB.setWintCode(wintCode);

                    wintBaseBDao.insert(wintBaseB);
                }
                if (params2.size() > 3) {
                    //包含取水用户信息到计量到收费审核
                    addUsers(params1, params2, wintId);

                }
                if (params3.size() > 3) {
                    //取水计划
                    addPlan(params2, params3);
                    System.out.println(params3);
                }
                if (params4.size() > 3) {
//                    template.update(sql2.toString(),params4.toArray()); //表b
                    addUsers(params1, params4, wintId);
                }
                if (params5.size() > 3) {
//                    template.update(sql3.toString(),params5.toArray()); //表c
                    addPlan(params2, params5);
                }
                if (params6.size() > 3) {
//                    template.update(sql2.toString(),params6.toArray()); //表b
                    addUsers(params1, params6, wintId);
                }
                if (params7.size() > 3) {
//                    template.update(sql3.toString(),params7.toArray()); //表c
                    addPlan(params2, params7);
                }
                if (params8.size() > 3) {
//                    template.update(sql2.toString(),params8.toArray()); //表b
                    addUsers(params1, params8, wintId);
                }
                if (params9.size() > 3) {
//                    template.update(sql3.toString(),params9.toArray()); //表c
                    addPlan(params2, params9);
                }
                if (params10.size() > 3) {
//                    template.update(sql2.toString(),params10.toArray()); //表b
                    addUsers(params1, params10, wintId);
                }
                if (params11.size() > 3) {
//                    template.update(sql3.toString(),params11.toArray()); //表c
                    addPlan(params2, params11);
                }

            }

        }

    }

    private void addPlan(ArrayList<String> params2, ArrayList<String> params3) {
        AttWiuIntPl wiuIntPl = new AttWiuIntPl();
        AttWiuIntPl wiuIntPl2 = new AttWiuIntPl();
        wiuIntPl.setId(UuidUtil.uuid());
        wiuIntPl.setWiuId(params2.get(0));
        wiuIntPl.setYr("2019");
        wiuIntPl.setYrTp("1");
        wiuIntPl2.setId(UuidUtil.uuid());
        wiuIntPl2.setWiuId(params2.get(0));
        wiuIntPl2.setYr("2019");
        wiuIntPl2.setYrTp("2");


        if (StringUtils.isNotBlank(params3.get(2)) && isNumeric(params3.get(2))) {
            wiuIntPl.setYrWw(Double.parseDouble(params3.get(2)));
        }
        if (StringUtils.isNotBlank(params3.get(3)) && isNumeric(params3.get(3))) {
            wiuIntPl.setJanWw(Double.parseDouble(params3.get(3)));
        }
        if (StringUtils.isNotBlank(params3.get(4)) && isNumeric(params3.get(4))) {
            wiuIntPl.setFebWw(Double.parseDouble(params3.get(4)));
        }
        if (StringUtils.isNotBlank(params3.get(5)) && isNumeric(params3.get(5))) {
            wiuIntPl.setMarWw(Double.parseDouble(params3.get(5)));
        }
        if (StringUtils.isNotBlank(params3.get(6)) && isNumeric(params3.get(6))) {
            wiuIntPl.setAprWw(Double.parseDouble(params3.get(6)));
        }
        if (StringUtils.isNotBlank(params3.get(7)) && isNumeric(params3.get(7))) {
            wiuIntPl.setMayWw(Double.parseDouble(params3.get(7)));
        }
        if (StringUtils.isNotBlank(params3.get(8)) && isNumeric(params3.get(8))) {
            wiuIntPl.setJunWw(Double.parseDouble(params3.get(8)));
        }
        if (StringUtils.isNotBlank(params3.get(9)) && isNumeric(params3.get(9))) {
            wiuIntPl.setJulWw(Double.parseDouble(params3.get(9)));
        }
        if (StringUtils.isNotBlank(params3.get(10)) && isNumeric(params3.get(10))) {
            wiuIntPl.setAugWw(Double.parseDouble(params3.get(10)));
        }

        if (StringUtils.isNotBlank(params3.get(11)) && isNumeric(params3.get(11))) {
            wiuIntPl.setSepWw(Double.parseDouble(params3.get(11)));
        }

        if (StringUtils.isNotBlank(params3.get(12)) && isNumeric(params3.get(12))) {
            wiuIntPl.setOctWw(Double.parseDouble(params3.get(12)));
        }
        if (StringUtils.isNotBlank(params3.get(13)) && isNumeric(params3.get(13))) {
            wiuIntPl.setNovWw(Double.parseDouble(params3.get(13)));
        }

        if (StringUtils.isNotBlank(params3.get(14)) && isNumeric(params3.get(14))) {
            wiuIntPl.setDecWw(Double.parseDouble(params3.get(14)));
        }
        //------------------
        if (StringUtils.isNotBlank(params3.get(15)) && isNumeric(params3.get(15))) {
            wiuIntPl2.setYrWw(Double.parseDouble(params3.get(15)));
        }
        if (StringUtils.isNotBlank(params3.get(16)) && isNumeric(params3.get(16))) {
            wiuIntPl2.setJanWw(Double.parseDouble(params3.get(16)));
        }
        if (StringUtils.isNotBlank(params3.get(17)) && isNumeric(params3.get(17))) {
            wiuIntPl2.setFebWw(Double.parseDouble(params3.get(17)));
        }
        if (StringUtils.isNotBlank(params3.get(18)) && isNumeric(params3.get(18))) {
            wiuIntPl2.setMarWw(Double.parseDouble(params3.get(18)));
        }
        if (StringUtils.isNotBlank(params3.get(19)) && isNumeric(params3.get(19))) {
            wiuIntPl2.setAprWw(Double.parseDouble(params3.get(19)));
        }
        if (StringUtils.isNotBlank(params3.get(20)) && isNumeric(params3.get(20))) {
            wiuIntPl2.setMayWw(Double.parseDouble(params3.get(20)));
        }
        if (StringUtils.isNotBlank(params3.get(21)) && isNumeric(params3.get(21))) {
            wiuIntPl2.setJunWw(Double.parseDouble(params3.get(21)));
        }
        if (StringUtils.isNotBlank(params3.get(22)) && isNumeric(params3.get(22))) {
            wiuIntPl2.setJulWw(Double.parseDouble(params3.get(22)));
        }
        if (StringUtils.isNotBlank(params3.get(23)) && isNumeric(params3.get(23))) {
            wiuIntPl2.setAugWw(Double.parseDouble(params3.get(23)));
        }

        if (StringUtils.isNotBlank(params3.get(24)) && isNumeric(params3.get(24))) {
            wiuIntPl2.setSepWw(Double.parseDouble(params3.get(24)));
        }

        if (StringUtils.isNotBlank(params3.get(25)) && isNumeric(params3.get(25))) {
            wiuIntPl2.setOctWw(Double.parseDouble(params3.get(25)));
        }
        if (StringUtils.isNotBlank(params3.get(26)) && isNumeric(params3.get(26))) {
            wiuIntPl2.setNovWw(Double.parseDouble(params3.get(26)));
        }

        if (StringUtils.isNotBlank(params3.get(27)) && isNumeric(params3.get(27))) {
            wiuIntPl2.setDecWw(Double.parseDouble(params3.get(27)));
        }
        wiuIntPl.setInTm(new Date());
        wiuIntPl2.setInTm(new Date());
        wiuIntPl2.setUpTm(new Date());
        wiuIntPl.setUpTm(new Date());
        if (!"填写".equals(params3.get(2)) && !"".equals(params3.get(2))) {
            wiuIntPlDao.insert(wiuIntPl);
        }
        if (!"填写".equals(params3.get(15)) && !"".equals(params3.get(15))) {
            wiuIntPlDao.insert(wiuIntPl2);
        }
    }

    private void addUsers(ArrayList<String> params1, ArrayList<String> params2, String wintCode) {
        AttWiuBase wiuBase = new AttWiuBase();
        wiuBase.setId(params2.get(0));
        wiuBase.setWiuName(params2.get(2));
        if ("填写".equals(params2.get(2)) || "".equals(params2.get(2))) {
            return;
        }
        wiuBase.setCodeVal(params2.get(8));

//                    wiuBase.setGdX();
//                    wiuBase.setGdY();
        if (params2.get(8).length() == 18) {
            wiuBase.setCodeType("2");
        } else {
            wiuBase.setCodeType("1");
        }
        if ("法定代表人".equals(params1.get(3))) {
            wiuBase.setIdtType("1");
        } else {
            wiuBase.setIdtType("2");
        }
        wiuBase.setWiuNat(getIdtType(params2.get(5)));

        wiuBase.setInTm(new Date());
        wiuBase.setUpTm(new Date());
        wiuBase.setName(params2.get(4));
//                    wiuBase.setObjId();
        wiuBase.setState("2");
        wiuBase.setWintCode(params1.get(0));
        wiuBase.setWintName(params2.get(2));
        wiuBase.setWintPos(params1.get(6));
        wiuBase.setWiuContact(params2.get(9));
        wiuBase.setWiuContactTel(params2.get(10));
        wiuBase.setWiuNatName(params2.get(5));
        if (StringUtils.isNotBlank(params1.get(8)) && !"填写".equals(params1.get(8)) && !"/".equals(params1.get(7))) {
            wiuBase.setWiuLat(DuFenMiaoToLat(params1.get(8)));
        }
        if (StringUtils.isNotBlank(params1.get(7)) && !"填写".equals(params1.get(7)) && !"/".equals(params1.get(7))) {
            wiuBase.setWiuLong(DuFenMiaoToLon(params1.get(7)));
        }
        AttWiuMeasDwt wiuMeasDwt = new AttWiuMeasDwt();
        wiuMeasDwt.setInTm(new Date());
        wiuMeasDwt.setUpTm(new Date());
        wiuMeasDwt.setId(UuidUtil.uuid());
        wiuMeasDwt.setWainUse1(getWainUseType(params2.get(15)));
        wiuMeasDwt.setWainUse2(getWainUseType(params2.get(16)));
        wiuMeasDwt.setWainUse3(getWainUseType(params2.get(17)));
        wiuMeasDwt.setWainUse4(getWainUseType(params2.get(18)));
        wiuMeasDwt.setWainUse5(getWainUseType(params2.get(19)));
        wiuMeasDwt.setWiuId(wiuBase.getId());
        if ("全部接入".equals(params2.get(14))) {
            wiuMeasDwt.setWiuIsAtlonwms("1");
        } else {
            wiuMeasDwt.setWiuIsAtlonwms("2");
        }

        if ("能".equals(params2.get(13))) {
            wiuMeasDwt.setWiustRunCond("1");
        } else {
            wiuMeasDwt.setWiustRunCond("2");
        }
        wiuMeasDwt.setWwCond(getComd(params2.get(11)));
        wiuMeasDwt.setWiustTp(getWiustTp(params2.get(12)));
        //TODO
        AttWiuWatCons wiuWatCons16 = new AttWiuWatCons();
        AttWiuWatCons wiuWatCons17 = new AttWiuWatCons();
        AttWiuWatCons wiuWatCons18 = new AttWiuWatCons();
        if ("计量".equals(params2.get(27))) {
            wiuWatCons16.setDataType("1");//1:自然年;2:调度年
        } else {
            wiuWatCons16.setDataType("2");//1:自然年;2:调度年
        }
        wiuWatCons16.setId(UuidUtil.uuid());
        wiuWatCons16.setInTm(new Date());
        wiuWatCons16.setUpTm(new Date());
        if (StringUtils.isNotBlank(params2.get(24)) && params2.get(24).contains("万")) {
            String dun = params2.get(24).substring(0, params2.get(24).indexOf("万"));
            wiuWatCons16.setWatCons(Double.parseDouble(dun));
        } else {
            if (StringUtils.isNotBlank(params2.get(24)) && isNumeric(params2.get(24))) {
                wiuWatCons16.setWatCons(Double.parseDouble(params2.get(24)));
            }
        }

        wiuWatCons16.setWiuId(wiuBase.getId());
        wiuWatCons16.setYear("2016");
        wiuWatCons16.setYearType("1");
        //------------------------------
        if ("计量".equals(params2.get(28))) {
            wiuWatCons17.setDataType("1");//1:自然年;2:调度年
        } else {
            wiuWatCons17.setDataType("2");//1:自然年;2:调度年
        }
        wiuWatCons17.setId(UuidUtil.uuid());
        wiuWatCons17.setInTm(new Date());
        wiuWatCons17.setUpTm(new Date());
        if (StringUtils.isNotBlank(params2.get(25)) && params2.get(25).contains("万")) {
            String dun = params2.get(25).substring(0, params2.get(25).indexOf("万"));
            wiuWatCons17.setWatCons(Double.parseDouble(dun));
        } else {
            if (StringUtils.isNotBlank(params2.get(25)) && isNumeric(params2.get(25))) {
                wiuWatCons17.setWatCons(Double.parseDouble(params2.get(25)));
            }
        }
        wiuWatCons17.setWiuId(wiuBase.getId());
        wiuWatCons17.setYear("2017");
        wiuWatCons17.setYearType("1");
        //------------------------------
        if ("计量".equals(params2.get(28))) {
            wiuWatCons18.setDataType("1");//1:自然年;2:调度年
        } else {
            wiuWatCons18.setDataType("2");//1:自然年;2:调度年
        }
        wiuWatCons18.setId(UuidUtil.uuid());
        wiuWatCons18.setInTm(new Date());
        wiuWatCons18.setUpTm(new Date());
        if (StringUtils.isNotBlank(params2.get(26)) && params2.get(26).contains("万")) {
            String dun = params2.get(26).substring(0, params2.get(26).indexOf("万"));
            wiuWatCons18.setWatCons(Double.parseDouble(dun));
        } else {
            if (StringUtils.isNotBlank(params2.get(26)) && isNumeric(params2.get(26))) {
                wiuWatCons18.setWatCons(Double.parseDouble(params2.get(26)));
            }
        }
        wiuWatCons18.setWiuId(wiuBase.getId());
        wiuWatCons18.setYear("2018");
        wiuWatCons18.setYearType("1");
        AttWiuWatLic wiuWatLic = new AttWiuWatLic();
        wiuWatLic.setId(UuidUtil.uuid());
        wiuWatLic.setReplSn(params2.get(31));
        wiuWatLic.setApprOrg(params2.get(32));
        if (StringUtils.isNotBlank(params2.get(35)) && isNumeric(params2.get(35))) {
            wiuWatLic.setApprWw(Double.parseDouble(params2.get(35)));
        }
        wiuWatLic.setInTm(new Date());
        wiuWatLic.setUpTm(new Date());
        if ("填写".equals(params2.get(30))) {
            wiuWatLic.setQsxkspqklx("2");
        } else {
            wiuWatLic.setQsxkspqklx("1");
            wiuWatLic.setWatLicCode(params2.get(30));
        }
        if (!"填写".equals(params2.get(36)) || !"".equals(params2.get(36))) {
            wiuWatLic.setQsxkspqklx("3");
            wiuWatLic.setFtafawdp(params2.get(36));
        }
        wiuWatLic.setReplSn(params2.get(35));
        //1:已申领取水许可证;2:已办理取水许可但未申领取水许可证;3:未办理取水许可

        wiuWatLic.setWrpNm(params2.get(33));
//                    wiuWatLic.setWiuDl();
//                    wiuWatLic.setWiuBd(params2.get(32));
        wiuWatLic.setWiuId(wiuBase.getId());
        if (StringUtils.isNotBlank(params2.get(34)) && isNumeric(params2.get(34))) {
            if (isNumeric(params2.get(34))) {
                wiuWatLic.setWiuWw(Double.parseDouble(params2.get(34)));
            }
        }
        System.out.println(params2);
        wiuBase.setWintCode(params2.get(3));
        wiuBase.setWiuCode(wintCode);
        wiuBaseDao.insert(wiuBase);
        wiuWatCons16.setWiuId(wiuBase.getId());
        wiuWatConsDao.insert(wiuWatCons16);
        wiuWatCons17.setWiuId(wiuBase.getId());
        wiuWatConsDao.insert(wiuWatCons17);
        wiuWatCons18.setWiuId(wiuBase.getId());
        wiuWatConsDao.insert(wiuWatCons18);
        if (StringUtils.isNotBlank(wiuWatLic.getWatLicCode())) {
            wiuWatLic.setQsxkspqklx("1");
        }
        wiuWatLicDao.insert(wiuWatLic);

        measDwtDao.insert(wiuMeasDwt);
    }

    public static boolean isNumeric(String str) {

        return pattern.matcher(str).matches();
    }

    private String getWainUseType(String s) {
        //1:城镇供水（自来水）;2:生活用水（自备）;3:火（核）电直流冷却用水;4:火（核）电循环冷却用水;5:非火（核）电用水;
        // 6:农业用水;7:人工生态环境补水；8：水资源配置调度取水；9：其他用水
        if ("空".equals(s)) {
            return "";
        }
        if ("城镇供水（自来水）".equals(s)) {
            return "1";
        }
        if ("生活用水（自备）".equals(s)) {
            return "2";
        }
        if ("火（核）电直流冷却用水".equals(s)) {
            return "3";
        }
        if ("火（核）电循环冷却用水".equals(s)) {
            return "4";
        }
        if ("非火（核）电用水".equals(s)) {
            return "5";
        }
        if ("农业用水".equals(s)) {
            return "6";
        }
        if ("人工生态环境补水".equals(s)) {
            return "7";
        }
        if ("水资源配置调度取水".equals(s)) {
            return "8";
        }
        return "9";
    }

    private String getWiustTp(String s) {
        //1:超声波流量计;2:电磁流量计;3:水表;4:水位计;5:水尺;9:其他
        if ("超声波流量计".equals(s)) {
            return "1";
        }
        if ("电磁流量计".equals(s)) {
            return "2";
        }
        if ("水表".equals(s)) {
            return "3";
        }
        if ("水位计".equals(s)) {
            return "4";
        }
        if ("水尺".equals(s)) {
            return "5";
        }
        return "9";
    }

    private String getComd(String s) {
        //1:全部计量;2:部分计量;3:无计量
        if ("全部计量".equals(s)) {
            return "1";
        }
        if ("部分计量".equals(s)) {
            return "2";
        }
        return "3";
    }

    private String getIdtType(String s) {
        //1:法人;2:其他组织;3:法人的分支机构;4:自然人
        if ("法人".equals(s)) {
            return "1";
        }
        if ("其他组织".equals(s)) {
            return "2";
        }
        if ("法人的分支机构".equals(s)) {
            return "3";
        }
        return "4";
    }


    public static double DuFenMiaoToLat(String latlng) {

        latlng = latlng.trim().replace("填写", "");
        if (latlng.contains("、")) {
            latlng = latlng.substring(0, latlng.indexOf("、"));
        }
        latlng = latlng.replace("东经", "").replace("北纬：", "").replace("东经：", "").replace("。", "°").replace("N", "")
                .replace("E", "").replace("北纬", "").replace("\"", "").replace("゜", "°").replace(" ", "").replace("º", "°")
                .replace("´", "′").replace("’", "′").replace("'", "′")
                .replace("'", "′").replace("度", "°").replace("分", "′");
        if (latlng.contains("（")) {
            latlng = latlng.substring(latlng.indexOf("（") + 1, latlng.indexOf("）"));
        }
        if (!latlng.contains("′") && latlng.contains("°")) {
            return Double.parseDouble(latlng.substring(0, latlng.length() - 1));
        }
        if (isNumeric(latlng)) {
            return Double.parseDouble(latlng);
        }

        if (latlng.endsWith("′")) {
            latlng = latlng + "00秒";
        }
        latlng = latlng.replace("'", "′");
        double du = Double.parseDouble(latlng.substring(0, latlng.indexOf("°")));
        double fen = Double.parseDouble(latlng.substring(latlng.indexOf("°") + 1, latlng.indexOf("′")));
        double miao = Double.parseDouble(latlng.substring(latlng.indexOf("′") + 1, latlng.length() - 1));

        if (du < 0) {
            return -(Math.abs(du) + (fen + (miao / 60)) / 60);
        }
        return du + (fen + (miao / 60)) / 60;
    }

    public static double DuFenMiaoToLon(String latlng) {//"
        latlng = latlng.trim().replace("填写", "");
        if (latlng.contains("、")) {
            latlng = latlng.substring(0, latlng.indexOf("、"));
        }
        latlng = latlng.replace("东经", "").replace("北纬：", "").replace("'", "′").replace("东经：", "").replace("北纬", "").replace("。", "°").replace("N", "").replace("E", "").replace("\"", "").replace(" ", "").replace("゜", "°").replace("º", "°")
                .replace("´", "′").replace("'", "′").replace("’", "′").replace("度", "°").replace("分", "′");
        ;
        if (latlng.contains("（")) {
            latlng = latlng.substring(latlng.indexOf("（") + 1, latlng.indexOf("）"));
        }
        if (!latlng.contains("′") && latlng.contains("°")) {
            return Double.parseDouble(latlng.substring(0, latlng.length() - 1));
        }
        if (isNumeric(latlng)) {
            return Double.parseDouble(latlng);
        }
        if (latlng.endsWith("′")) {
            latlng = latlng + "00分";
        }
        latlng = latlng.replace("'", "′");
        double du = Double.parseDouble(latlng.substring(0, latlng.indexOf("°")));
        double fen = Double.parseDouble(latlng.substring(latlng.indexOf("°") + 1, latlng.indexOf("′")));
        double miao = Double.parseDouble(latlng.substring(latlng.indexOf("′") + 1, latlng.length() - 1));
        if (du < 0) {
            return -(Math.abs(du) + (fen + (miao / 60)) / 60);
        }
        return du + (fen + (miao / 60)) / 60;
    }

    private static String getWasoType(String s) {
        //1:河道内的水库;2:湖泊;3:河流;4:跨省引调水工程;9:其他
        if ("河道内的水库".equals(s)) {
            return "1";
        }
        if ("湖泊".equals(s)) {
            return "2";
        }
        if ("河流".equals(s)) {
            return "3";
        }
        if ("跨省引调水工程".equals(s)) {
            return "4";
        }
        return "9";
    }

    private static String getType(String s) {
        //1:渠道;2:人工河道;3:虹吸管;4:泵站;5:闸;6:从江河湖泊饮水的水库;7:其他;
        if ("渠道".equals(s)) {
            return "1";
        }
        if ("人工河道".equals(s)) {
            return "2";
        }
        if ("虹吸管".equals(s)) {
            return "3";
        }
        if ("泵站".equals(s)) {
            return "4";
        }
        if ("闸".equals(s)) {
            return "5";
        }
        if ("从江河湖泊饮水的水库".equals(s)) {
            return "6";
        }
        return "7";
    }

    /**
     * 处理数据并将数据存进map集合
     */
    private static Map<String, String> dealDataAndSaveInMap(Workbook wb) {
        //Sheet sheet = wb.getSheetAt(1);
        //确定下改处理哪个sheet是要读取数据的,暂时试试通过名字
        //Sheet sheet = wb.getSheet("取水口初始台账表");
        //主业务sheet
        Sheet sheet = null;
        Iterator<Sheet> sheetIterator = wb.sheetIterator();
        while (sheetIterator.hasNext()) {
            Sheet next = sheetIterator.next();
            if (null != next.getRow(8) && null != getStringValByZb(next, "C8") && getStringValByZb(next, "C8").contains("取水口类型") && null != getStringValByZb(next, "D4")) {

                if (!"填写".equalsIgnoreCase(getStringValByZb(next, "D4"))) {
                    sheet = next;
                } else {
                    qushuikou.add(doDealIngFileName);
                }
            }
            //明确多用户情况
            if (null != getStringValByZb(next, "B2") && getStringValByZb(next, "B2").contains("多用户公用取水口初始台账表") && !"填表说明".equalsIgnoreCase(next.getSheetName()) && null != next.getRow(5) && null != getStringValByZb(next, "C5") && "取水户名称".equalsIgnoreCase(getStringValByZb(next, "C5")) && null != getStringValByZb(next, "D5") && !"".equalsIgnoreCase(getStringValByZb(next, "D5").trim()) && !"填写".equalsIgnoreCase(getStringValByZb(next, "D5"))) {
                System.out.println("看看sheet的名字和作为比较的值:" + next.getSheetName() + "@" + getStringValByZb(next, "D5") + "依据是坐标取出的值是 :" + getStringValByZb(next, "C5") + "看看c8是啥:" + getStringValByZb(next, "C8"));
                countIsHasSheet3++;
                dyhFileNames.add(doDealIngFileName);
            }
        }
        if (null == sheet) {
            System.out.println("发生异常的文件是 :" + doDealIngFileName);
            LinkedHashMap<String, String> tempMap = new LinkedHashMap<>();
            //tempMap.put("张三",111);
            return tempMap;
        }
        LinkedHashMap<String, String> map = new LinkedHashMap<>();
        map.put("v1", getStringValByZb(sheet, "D4"));
        map.put("v2", getStringValByZb(sheet, "D5"));
        map.put("v3", getStringValByZb(sheet, "F5"));
        map.put("v4", getStringValByZb(sheet, "H5"));
        map.put("v5", getStringValByZb(sheet, "D6"));
        map.put("v6", getStringValByZb(sheet, "G6"));
        map.put("v7", getStringValByZb(sheet, "I6"));
        map.put("v8", getStringValByZb(sheet, "E7"));
        map.put("v9", getStringValByZb(sheet, "E8")); //这是确定是否读取sheet2的标准 如果值为 多用户公用取水口,则需要读后面的sheet
        //判断上面的值,以确定是否有多用户公用取水口
//        if ("多用户公用取水口".equalsIgnoreCase(getStringValByZb(sheet, "E8"))) {
//            countIsHasSheet3++;
//            dyhFileNames.add(doDealIngFileName);
//        }
        map.put("v10", getStringValByZb(sheet, "D9"));
        map.put("v11", getStringValByZb(sheet, "G9")); //可能发生空指针的两处
        map.put("v12", getStringValByZb(sheet, "I9")); //可能发生空指针的两处
        map.put("v13", getStringValByZb(sheet, "D10"));
        map.put("v14", getStringValByZb(sheet, "E11"));
        map.put("v15", getStringValByZb(sheet, "E12"));
        map.put("v16", getStringValByZb(sheet, "I12"));
        //map.put("v16", "取水水源为其他时，在此处填写具体水源名称".equalsIgnoreCase(getStringValByZb(sheet, "I12")) ? null : getStringValByZb(sheet, "I12"));
        map.put("v17", getStringValByZb(sheet, "D13"));
        map.put("v18", getStringValByZb(sheet, "E14"));
        map.put("v19", getStringValByZb(sheet, "G14"));
        map.put("v20", getStringValByZb(sheet, "I14"));
        //以上是取水口相关信息
        //以下是取水口下面的234信息
        map.put("v21", getStringValByZb(sheet, "D17"));
        map.put("v22", getStringValByZb(sheet, "G17"));
        map.put("v23", getStringValByZb(sheet, "I17"));
        map.put("v24", getStringValByZb(sheet, "F18"));
        map.put("v25", getStringValByZb(sheet, "G18"));
        map.put("v26", getStringValByZb(sheet, "F19"));
        map.put("v27", getStringValByZb(sheet, "G19"));
        map.put("v28", getStringValByZb(sheet, "D20"));
        map.put("v29", getStringValByZb(sheet, "G20"));
        map.put("v30", getStringValByZb(sheet, "E24"));
        map.put("v31", getStringValByZb(sheet, "H24"));
        map.put("v32", getStringValByZb(sheet, "E25"));
        map.put("v33", getStringValByZb(sheet, "H25"));
        map.put("v34", getStringValByZb(sheet, "D27"));
        //map.put("v35", "空".equalsIgnoreCase(getStringValByZb(sheet, "F27")) ? null : getStringValByZb(sheet, "F27"));
        //map.put("v36", "空".equalsIgnoreCase(getStringValByZb(sheet, "H27")) ? null : getStringValByZb(sheet, "H27"));
        map.put("v35", getStringValByZb(sheet, "F27"));
        map.put("v36", getStringValByZb(sheet, "H27"));
//        map.put("v37", "空".equalsIgnoreCase(getStringValByZb(sheet, "D28")) ? null : getStringValByZb(sheet, "D28"));
//        map.put("v38", "空".equalsIgnoreCase(getStringValByZb(sheet, "F28")) ? null : getStringValByZb(sheet, "F28"));
//        map.put("v39", "空".equalsIgnoreCase(getStringValByZb(sheet, "H28")) ? null : getStringValByZb(sheet, "H28"));
//        map.put("v40", "空".equalsIgnoreCase(getStringValByZb(sheet, "D29")) ? null : getStringValByZb(sheet, "D29"));
//        map.put("v41", "空".equalsIgnoreCase(getStringValByZb(sheet, "F29")) ? null : getStringValByZb(sheet, "F29"));
//        map.put("v42", "空".equalsIgnoreCase(getStringValByZb(sheet, "H29")) ? null : getStringValByZb(sheet, "H29"));
        map.put("v37", getStringValByZb(sheet, "D28"));
        map.put("v38", getStringValByZb(sheet, "F28"));
        map.put("v39", getStringValByZb(sheet, "H28"));
        map.put("v40", getStringValByZb(sheet, "D29"));
        map.put("v41", getStringValByZb(sheet, "F29"));
        map.put("v42", getStringValByZb(sheet, "H29"));
        map.put("v43", getStringValByZb(sheet, "E30"));
        map.put("v44", getStringValByZb(sheet, "G30"));
        map.put("v45", getStringValByZb(sheet, "I30"));
        map.put("v46", getStringValByZb(sheet, "E31"));
        map.put("v47", getStringValByZb(sheet, "G31"));
        map.put("v48", getStringValByZb(sheet, "I31"));
        map.put("v49", getStringValByZb(sheet, "E36"));
        map.put("v50", getStringValByZb(sheet, "E40"));
        map.put("v51", getStringValByZb(sheet, "G40"));
        map.put("v52", getStringValByZb(sheet, "E41"));
        map.put("v53", getStringValByZb(sheet, "E42"));
        map.put("v54", getStringValByZb(sheet, "E43"));
        map.put("v55", getStringValByZb(sheet, "E44"));
        //取水口页234信息

        //取水口页5部分
        map.put("v56", getStringValByZb(sheet, "E48"));
        map.put("v57", getStringValByZb(sheet, "D50"));
        map.put("v58", getStringValByZb(sheet, "E50"));
        map.put("v59", getStringValByZb(sheet, "F50"));
        map.put("v60", getStringValByZb(sheet, "G50"));
        map.put("v61", getStringValByZb(sheet, "H50"));
        map.put("v62", getStringValByZb(sheet, "I50"));
        map.put("v63", getStringValByZb(sheet, "D52"));
        map.put("v64", getStringValByZb(sheet, "E52"));
        map.put("v65", getStringValByZb(sheet, "F52"));
        map.put("v66", getStringValByZb(sheet, "G52"));
        map.put("v67", getStringValByZb(sheet, "H52"));
        map.put("v68", getStringValByZb(sheet, "I52"));
        map.put("v69", getStringValByZb(sheet, "E53"));
        map.put("v70", getStringValByZb(sheet, "D55"));
        map.put("v71", getStringValByZb(sheet, "E55"));
        map.put("v72", getStringValByZb(sheet, "F55"));
        map.put("v73", getStringValByZb(sheet, "G55"));
        map.put("v74", getStringValByZb(sheet, "H55"));
        map.put("v75", getStringValByZb(sheet, "I55"));
        map.put("v76", getStringValByZb(sheet, "D57"));
        map.put("v77", getStringValByZb(sheet, "E57"));
        map.put("v78", getStringValByZb(sheet, "F57"));
        map.put("v79", getStringValByZb(sheet, "G57"));
        map.put("v80", getStringValByZb(sheet, "H57"));
        map.put("v81", getStringValByZb(sheet, "I57"));
        //取水口页5部分

        //读取多用户那些sheet
        Map<String, String> dyhMap = readDyhSheet(wb);
        for (Map.Entry<String, String> entry : dyhMap.entrySet()) {
            map.put(entry.getKey(), entry.getValue());
            //System.out.println("Key = " + entry.getKey() + ", Value = " + entry.getValue());
        }
        return map;

    }

    /**
     * 读取多用户sheet
     *
     * @param wb
     * @return
     */
    private static Map<String, String> readDyhSheet(Workbook wb) {
        LinkedHashMap<String, String> dyhMap = new LinkedHashMap<>();
        Sheet sheet2 = wb.getSheet("多用户公用取水口初始台账表2");
        if (null != sheet2) {
            dyhMap = readDyh2(dyhMap, sheet2);
        }
        Sheet sheet3 = wb.getSheet("多用户公用取水口初始台账表3");
        if (null != sheet3) {
            dyhMap = readDyh3(dyhMap, sheet3);
        }
        Sheet sheet4 = wb.getSheet("多用户公用取水口初始台账表4");
        if (null != sheet4) {
            dyhMap = readDyh4(dyhMap, sheet4);
        }
        Sheet sheet5 = wb.getSheet("多用户公用取水口初始台账表5");
        if (null != sheet5) {
            dyhMap = readDyh5(dyhMap, sheet5);
        }
        return dyhMap;
    }

    /**
     * 读取第一个多用户sheet页
     *
     * @param dyhMap
     * @param sheet2
     * @return
     */
    private static LinkedHashMap<String, String> readDyh5(LinkedHashMap<String, String> dyhMap, Sheet sheet2) {
        String str = "d5";
        dyhMap.put(str + "v1", getStringValByZb(sheet2, "D5"));
        dyhMap.put(str + "v2", getStringValByZb(sheet2, "G5"));
        dyhMap.put(str + "v3", getStringValByZb(sheet2, "I5"));
        dyhMap.put(str + "v4", getStringValByZb(sheet2, "F6"));
        dyhMap.put(str + "v5", getStringValByZb(sheet2, "G6"));
        dyhMap.put(str + "v6", getStringValByZb(sheet2, "F7"));
        dyhMap.put(str + "v7", getStringValByZb(sheet2, "G7"));
        dyhMap.put(str + "v8", getStringValByZb(sheet2, "D8"));
        dyhMap.put(str + "v9", getStringValByZb(sheet2, "G8"));
        dyhMap.put(str + "v10", getStringValByZb(sheet2, "E12"));
        dyhMap.put(str + "v11", getStringValByZb(sheet2, "H12"));
        dyhMap.put(str + "v12", getStringValByZb(sheet2, "E13"));
        dyhMap.put(str + "v13", getStringValByZb(sheet2, "H13"));
        dyhMap.put(str + "v14", getStringValByZb(sheet2, "D15"));
        dyhMap.put(str + "v15", getStringValByZb(sheet2, "F15"));
        dyhMap.put(str + "v16", getStringValByZb(sheet2, "H15"));
        dyhMap.put(str + "v17", getStringValByZb(sheet2, "D16"));
        dyhMap.put(str + "v18", getStringValByZb(sheet2, "F16"));
        dyhMap.put(str + "v19", getStringValByZb(sheet2, "H16"));
        dyhMap.put(str + "v20", getStringValByZb(sheet2, "D17"));
        dyhMap.put(str + "v21", getStringValByZb(sheet2, "F17"));
        dyhMap.put(str + "v22", getStringValByZb(sheet2, "H17"));
        dyhMap.put(str + "v23", getStringValByZb(sheet2, "E18"));
        dyhMap.put(str + "v24", getStringValByZb(sheet2, "G18"));
        dyhMap.put(str + "v25", getStringValByZb(sheet2, "I18"));
        dyhMap.put(str + "v26", getStringValByZb(sheet2, "E19"));
        dyhMap.put(str + "v27", getStringValByZb(sheet2, "G19"));
        dyhMap.put(str + "v28", getStringValByZb(sheet2, "I19"));
        dyhMap.put(str + "v29", getStringValByZb(sheet2, "E24"));
        dyhMap.put(str + "v30", getStringValByZb(sheet2, "E28"));
        dyhMap.put(str + "v31", getStringValByZb(sheet2, "G28"));
        dyhMap.put(str + "v32", getStringValByZb(sheet2, "E29"));
        dyhMap.put(str + "v33", getStringValByZb(sheet2, "E30"));
        dyhMap.put(str + "v34", getStringValByZb(sheet2, "E31"));
        dyhMap.put(str + "v35", getStringValByZb(sheet2, "E32"));
        dyhMap.put(str + "v36", getStringValByZb(sheet2, "E36"));
        dyhMap.put(str + "v37", getStringValByZb(sheet2, "D38"));
        dyhMap.put(str + "v38", getStringValByZb(sheet2, "E38"));
        dyhMap.put(str + "v39", getStringValByZb(sheet2, "F38"));
        dyhMap.put(str + "v40", getStringValByZb(sheet2, "G38"));
        dyhMap.put(str + "v41", getStringValByZb(sheet2, "H38"));
        dyhMap.put(str + "v42", getStringValByZb(sheet2, "I38"));
        dyhMap.put(str + "v43", getStringValByZb(sheet2, "D40"));
        dyhMap.put(str + "v44", getStringValByZb(sheet2, "E40"));
        dyhMap.put(str + "v45", getStringValByZb(sheet2, "F40"));
        dyhMap.put(str + "v46", getStringValByZb(sheet2, "G40"));
        dyhMap.put(str + "v47", getStringValByZb(sheet2, "H40"));
        dyhMap.put(str + "v48", getStringValByZb(sheet2, "H40"));
        dyhMap.put(str + "v49", getStringValByZb(sheet2, "D43"));
        dyhMap.put(str + "v50", getStringValByZb(sheet2, "E43"));
        dyhMap.put(str + "v51", getStringValByZb(sheet2, "F43"));
        dyhMap.put(str + "v52", getStringValByZb(sheet2, "G43"));
        dyhMap.put(str + "v53", getStringValByZb(sheet2, "H43"));
        dyhMap.put(str + "v54", getStringValByZb(sheet2, "I43"));
        dyhMap.put(str + "v55", getStringValByZb(sheet2, "D45"));
        dyhMap.put(str + "v56", getStringValByZb(sheet2, "E45"));
        dyhMap.put(str + "v57", getStringValByZb(sheet2, "E45"));
        dyhMap.put(str + "v58", getStringValByZb(sheet2, "F45"));
        dyhMap.put(str + "v59", getStringValByZb(sheet2, "G45"));
        dyhMap.put(str + "v60", getStringValByZb(sheet2, "H45"));
        dyhMap.put(str + "v61", getStringValByZb(sheet2, "I45"));
        return dyhMap;
    }

    /**
     * 读取第一个多用户sheet页
     *
     * @param dyhMap
     * @param sheet2
     * @return
     */
    private static LinkedHashMap<String, String> readDyh4(LinkedHashMap<String, String> dyhMap, Sheet sheet2) {
        String str = "d4";
        dyhMap.put(str + "v1", getStringValByZb(sheet2, "D5"));
        dyhMap.put(str + "v2", getStringValByZb(sheet2, "G5"));
        dyhMap.put(str + "v3", getStringValByZb(sheet2, "I5"));
        dyhMap.put(str + "v4", getStringValByZb(sheet2, "F6"));
        dyhMap.put(str + "v5", getStringValByZb(sheet2, "G6"));
        dyhMap.put(str + "v6", getStringValByZb(sheet2, "F7"));
        dyhMap.put(str + "v7", getStringValByZb(sheet2, "G7"));
        dyhMap.put(str + "v8", getStringValByZb(sheet2, "D8"));
        dyhMap.put(str + "v9", getStringValByZb(sheet2, "G8"));
        dyhMap.put(str + "v10", getStringValByZb(sheet2, "E12"));
        dyhMap.put(str + "v11", getStringValByZb(sheet2, "H12"));
        dyhMap.put(str + "v12", getStringValByZb(sheet2, "E13"));
        dyhMap.put(str + "v13", getStringValByZb(sheet2, "H13"));
        dyhMap.put(str + "v14", getStringValByZb(sheet2, "D15"));
        dyhMap.put(str + "v15", getStringValByZb(sheet2, "F15"));
        dyhMap.put(str + "v16", getStringValByZb(sheet2, "H15"));
        dyhMap.put(str + "v17", getStringValByZb(sheet2, "D16"));
        dyhMap.put(str + "v18", getStringValByZb(sheet2, "F16"));
        dyhMap.put(str + "v19", getStringValByZb(sheet2, "H16"));
        dyhMap.put(str + "v20", getStringValByZb(sheet2, "D17"));
        dyhMap.put(str + "v21", getStringValByZb(sheet2, "F17"));
        dyhMap.put(str + "v22", getStringValByZb(sheet2, "H17"));
        dyhMap.put(str + "v23", getStringValByZb(sheet2, "E18"));
        dyhMap.put(str + "v24", getStringValByZb(sheet2, "G18"));
        dyhMap.put(str + "v25", getStringValByZb(sheet2, "I18"));
        dyhMap.put(str + "v26", getStringValByZb(sheet2, "E19"));
        dyhMap.put(str + "v27", getStringValByZb(sheet2, "G19"));
        dyhMap.put(str + "v28", getStringValByZb(sheet2, "I19"));
        dyhMap.put(str + "v29", getStringValByZb(sheet2, "E24"));
        dyhMap.put(str + "v30", getStringValByZb(sheet2, "E28"));
        dyhMap.put(str + "v31", getStringValByZb(sheet2, "G28"));
        dyhMap.put(str + "v32", getStringValByZb(sheet2, "E29"));
        dyhMap.put(str + "v33", getStringValByZb(sheet2, "E30"));
        dyhMap.put(str + "v34", getStringValByZb(sheet2, "E31"));
        dyhMap.put(str + "v35", getStringValByZb(sheet2, "E32"));
        dyhMap.put(str + "v36", getStringValByZb(sheet2, "E36"));
        dyhMap.put(str + "v37", getStringValByZb(sheet2, "D38"));
        dyhMap.put(str + "v38", getStringValByZb(sheet2, "E38"));
        dyhMap.put(str + "v39", getStringValByZb(sheet2, "F38"));
        dyhMap.put(str + "v40", getStringValByZb(sheet2, "G38"));
        dyhMap.put(str + "v41", getStringValByZb(sheet2, "H38"));
        dyhMap.put(str + "v42", getStringValByZb(sheet2, "I38"));
        dyhMap.put(str + "v43", getStringValByZb(sheet2, "D40"));
        dyhMap.put(str + "v44", getStringValByZb(sheet2, "E40"));
        dyhMap.put(str + "v45", getStringValByZb(sheet2, "F40"));
        dyhMap.put(str + "v46", getStringValByZb(sheet2, "G40"));
        dyhMap.put(str + "v47", getStringValByZb(sheet2, "H40"));
        dyhMap.put(str + "v48", getStringValByZb(sheet2, "H40"));
        dyhMap.put(str + "v49", getStringValByZb(sheet2, "D43"));
        dyhMap.put(str + "v50", getStringValByZb(sheet2, "E43"));
        dyhMap.put(str + "v51", getStringValByZb(sheet2, "F43"));
        dyhMap.put(str + "v52", getStringValByZb(sheet2, "G43"));
        dyhMap.put(str + "v53", getStringValByZb(sheet2, "H43"));
        dyhMap.put(str + "v54", getStringValByZb(sheet2, "I43"));
        dyhMap.put(str + "v55", getStringValByZb(sheet2, "D45"));
        dyhMap.put(str + "v56", getStringValByZb(sheet2, "E45"));
        dyhMap.put(str + "v57", getStringValByZb(sheet2, "E45"));
        dyhMap.put(str + "v58", getStringValByZb(sheet2, "F45"));
        dyhMap.put(str + "v59", getStringValByZb(sheet2, "G45"));
        dyhMap.put(str + "v60", getStringValByZb(sheet2, "H45"));
        dyhMap.put(str + "v61", getStringValByZb(sheet2, "I45"));
        return dyhMap;
    }

    /**
     * 读取第一个多用户sheet页
     *
     * @param dyhMap
     * @param sheet2
     * @return
     */
    private static LinkedHashMap<String, String> readDyh3(LinkedHashMap<String, String> dyhMap, Sheet sheet2) {
        String str = "d3";
        dyhMap.put(str + "v1", getStringValByZb(sheet2, "D5"));
        dyhMap.put(str + "v2", getStringValByZb(sheet2, "G5"));
        dyhMap.put(str + "v3", getStringValByZb(sheet2, "I5"));
        dyhMap.put(str + "v4", getStringValByZb(sheet2, "F6"));
        dyhMap.put(str + "v5", getStringValByZb(sheet2, "G6"));
        dyhMap.put(str + "v6", getStringValByZb(sheet2, "F7"));
        dyhMap.put(str + "v7", getStringValByZb(sheet2, "G7"));
        dyhMap.put(str + "v8", getStringValByZb(sheet2, "D8"));
        dyhMap.put(str + "v9", getStringValByZb(sheet2, "G8"));
        dyhMap.put(str + "v10", getStringValByZb(sheet2, "E12"));
        dyhMap.put(str + "v11", getStringValByZb(sheet2, "H12"));
        dyhMap.put(str + "v12", getStringValByZb(sheet2, "E13"));
        dyhMap.put(str + "v13", getStringValByZb(sheet2, "H13"));
        dyhMap.put(str + "v14", getStringValByZb(sheet2, "D15"));
        dyhMap.put(str + "v15", getStringValByZb(sheet2, "F15"));
        dyhMap.put(str + "v16", getStringValByZb(sheet2, "H15"));
        dyhMap.put(str + "v17", getStringValByZb(sheet2, "D16"));
        dyhMap.put(str + "v18", getStringValByZb(sheet2, "F16"));
        dyhMap.put(str + "v19", getStringValByZb(sheet2, "H16"));
        dyhMap.put(str + "v20", getStringValByZb(sheet2, "D17"));
        dyhMap.put(str + "v21", getStringValByZb(sheet2, "F17"));
        dyhMap.put(str + "v22", getStringValByZb(sheet2, "H17"));
        dyhMap.put(str + "v23", getStringValByZb(sheet2, "E18"));
        dyhMap.put(str + "v24", getStringValByZb(sheet2, "G18"));
        dyhMap.put(str + "v25", getStringValByZb(sheet2, "I18"));
        dyhMap.put(str + "v26", getStringValByZb(sheet2, "E19"));
        dyhMap.put(str + "v27", getStringValByZb(sheet2, "G19"));
        dyhMap.put(str + "v28", getStringValByZb(sheet2, "I19"));
        dyhMap.put(str + "v29", getStringValByZb(sheet2, "E24"));
        dyhMap.put(str + "v30", getStringValByZb(sheet2, "E28"));
        dyhMap.put(str + "v31", getStringValByZb(sheet2, "G28"));
        dyhMap.put(str + "v32", getStringValByZb(sheet2, "E29"));
        dyhMap.put(str + "v33", getStringValByZb(sheet2, "E30"));
        dyhMap.put(str + "v34", getStringValByZb(sheet2, "E31"));
        dyhMap.put(str + "v35", getStringValByZb(sheet2, "E32"));
        dyhMap.put(str + "v36", getStringValByZb(sheet2, "E36"));
        dyhMap.put(str + "v37", getStringValByZb(sheet2, "D38"));
        dyhMap.put(str + "v38", getStringValByZb(sheet2, "E38"));
        dyhMap.put(str + "v39", getStringValByZb(sheet2, "F38"));
        dyhMap.put(str + "v40", getStringValByZb(sheet2, "G38"));
        dyhMap.put(str + "v41", getStringValByZb(sheet2, "H38"));
        dyhMap.put(str + "v42", getStringValByZb(sheet2, "I38"));
        dyhMap.put(str + "v43", getStringValByZb(sheet2, "D40"));
        dyhMap.put(str + "v44", getStringValByZb(sheet2, "E40"));
        dyhMap.put(str + "v45", getStringValByZb(sheet2, "F40"));
        dyhMap.put(str + "v46", getStringValByZb(sheet2, "G40"));
        dyhMap.put(str + "v47", getStringValByZb(sheet2, "H40"));
        dyhMap.put(str + "v48", getStringValByZb(sheet2, "H40"));
        dyhMap.put(str + "v49", getStringValByZb(sheet2, "D43"));
        dyhMap.put(str + "v50", getStringValByZb(sheet2, "E43"));
        dyhMap.put(str + "v51", getStringValByZb(sheet2, "F43"));
        dyhMap.put(str + "v52", getStringValByZb(sheet2, "G43"));
        dyhMap.put(str + "v53", getStringValByZb(sheet2, "H43"));
        dyhMap.put(str + "v54", getStringValByZb(sheet2, "I43"));
        dyhMap.put(str + "v55", getStringValByZb(sheet2, "D45"));
        dyhMap.put(str + "v56", getStringValByZb(sheet2, "E45"));
        dyhMap.put(str + "v57", getStringValByZb(sheet2, "E45"));
        dyhMap.put(str + "v58", getStringValByZb(sheet2, "F45"));
        dyhMap.put(str + "v59", getStringValByZb(sheet2, "G45"));
        dyhMap.put(str + "v60", getStringValByZb(sheet2, "H45"));
        dyhMap.put(str + "v61", getStringValByZb(sheet2, "I45"));
        return dyhMap;
    }

    /**
     * 读取第一个多用户sheet页
     *
     * @param dyhMap
     * @param sheet2
     * @return
     */
    private static LinkedHashMap<String, String> readDyh2(LinkedHashMap<String, String> dyhMap, Sheet sheet2) {
        String str = "d2";
        dyhMap.put(str + "v1", getStringValByZb(sheet2, "D5"));
        dyhMap.put(str + "v2", getStringValByZb(sheet2, "G5"));
        dyhMap.put(str + "v3", getStringValByZb(sheet2, "I5"));
        dyhMap.put(str + "v4", getStringValByZb(sheet2, "F6"));
        dyhMap.put(str + "v5", getStringValByZb(sheet2, "G6"));
        dyhMap.put(str + "v6", getStringValByZb(sheet2, "F7"));
        dyhMap.put(str + "v7", getStringValByZb(sheet2, "G7"));
        dyhMap.put(str + "v8", getStringValByZb(sheet2, "D8"));
        dyhMap.put(str + "v9", getStringValByZb(sheet2, "G8"));
        dyhMap.put(str + "v10", getStringValByZb(sheet2, "E12"));
        dyhMap.put(str + "v11", getStringValByZb(sheet2, "H12"));
        dyhMap.put(str + "v12", getStringValByZb(sheet2, "E13"));
        dyhMap.put(str + "v13", getStringValByZb(sheet2, "H13"));
        dyhMap.put(str + "v14", getStringValByZb(sheet2, "D15"));
        dyhMap.put(str + "v15", getStringValByZb(sheet2, "F15"));
        dyhMap.put(str + "v16", getStringValByZb(sheet2, "H15"));
        dyhMap.put(str + "v17", getStringValByZb(sheet2, "D16"));
        dyhMap.put(str + "v18", getStringValByZb(sheet2, "F16"));
        dyhMap.put(str + "v19", getStringValByZb(sheet2, "H16"));
        dyhMap.put(str + "v20", getStringValByZb(sheet2, "D17"));
        dyhMap.put(str + "v21", getStringValByZb(sheet2, "F17"));
        dyhMap.put(str + "v22", getStringValByZb(sheet2, "H17"));
        dyhMap.put(str + "v23", getStringValByZb(sheet2, "E18"));
        dyhMap.put(str + "v24", getStringValByZb(sheet2, "G18"));
        dyhMap.put(str + "v25", getStringValByZb(sheet2, "I18"));
        dyhMap.put(str + "v26", getStringValByZb(sheet2, "E19"));
        dyhMap.put(str + "v27", getStringValByZb(sheet2, "G19"));
        dyhMap.put(str + "v28", getStringValByZb(sheet2, "I19"));
        dyhMap.put(str + "v29", getStringValByZb(sheet2, "E24"));
        dyhMap.put(str + "v30", getStringValByZb(sheet2, "E28"));
        dyhMap.put(str + "v31", getStringValByZb(sheet2, "G28"));
        dyhMap.put(str + "v32", getStringValByZb(sheet2, "E29"));
        dyhMap.put(str + "v33", getStringValByZb(sheet2, "E30"));
        dyhMap.put(str + "v34", getStringValByZb(sheet2, "E31"));
        dyhMap.put(str + "v35", getStringValByZb(sheet2, "E32"));
        dyhMap.put(str + "v36", getStringValByZb(sheet2, "E36"));
        dyhMap.put(str + "v37", getStringValByZb(sheet2, "D38"));
        dyhMap.put(str + "v38", getStringValByZb(sheet2, "E38"));
        dyhMap.put(str + "v39", getStringValByZb(sheet2, "F38"));
        dyhMap.put(str + "v40", getStringValByZb(sheet2, "G38"));
        dyhMap.put(str + "v41", getStringValByZb(sheet2, "H38"));
        dyhMap.put(str + "v42", getStringValByZb(sheet2, "I38"));
        dyhMap.put(str + "v43", getStringValByZb(sheet2, "D40"));
        dyhMap.put(str + "v44", getStringValByZb(sheet2, "E40"));
        dyhMap.put(str + "v45", getStringValByZb(sheet2, "F40"));
        dyhMap.put(str + "v46", getStringValByZb(sheet2, "G40"));
        dyhMap.put(str + "v47", getStringValByZb(sheet2, "H40"));
        dyhMap.put(str + "v48", getStringValByZb(sheet2, "H40"));
        dyhMap.put(str + "v49", getStringValByZb(sheet2, "D43"));
        dyhMap.put(str + "v50", getStringValByZb(sheet2, "E43"));
        dyhMap.put(str + "v51", getStringValByZb(sheet2, "F43"));
        dyhMap.put(str + "v52", getStringValByZb(sheet2, "G43"));
        dyhMap.put(str + "v53", getStringValByZb(sheet2, "H43"));
        dyhMap.put(str + "v54", getStringValByZb(sheet2, "I43"));
        dyhMap.put(str + "v55", getStringValByZb(sheet2, "D45"));
        dyhMap.put(str + "v56", getStringValByZb(sheet2, "E45"));
        dyhMap.put(str + "v57", getStringValByZb(sheet2, "E45"));
        dyhMap.put(str + "v58", getStringValByZb(sheet2, "F45"));
        dyhMap.put(str + "v59", getStringValByZb(sheet2, "G45"));
        dyhMap.put(str + "v60", getStringValByZb(sheet2, "H45"));
        dyhMap.put(str + "v61", getStringValByZb(sheet2, "I45"));
        return dyhMap;
    }

    /**
     * 处理excel,并输出下数据看看
     *
     * @param wb
     */
    private static void dealDataAndToConsole(Workbook wb) {
        Sheet sheet = wb.getSheetAt(1);
        StringBuilder sb = new StringBuilder();
        sb.append("输出的参数内容是:\n");
        //Iterator<Row> iterator = sheetAt.iterator();
//        sheet.getRow(2).getCell(2).setCellType(CellType.STRING);
//        String v1 = sheet.getRow(2).getCell(2).getStringCellValue();
        //sheet.getRow(3).getCell(3).setCellType(CellType.STRING);
        //String v1 = sheet.getRow(3).getCell(3).getStringCellValue();
        String v1 = getStringCellValByZb(sheet, "D4").getStringCellValue();
        sb.append("取水口名称是:" + v1 + "\n");
//        sheet.getRow(4).getCell(3).setCellType(CellType.STRING);
//        String v2 = sheet.getRow(4).getCell(3).getStringCellValue();
        String v2 = getStringCellValByZb(sheet, "D5").getStringCellValue();
        sb.append("取水口所在行政区:" + v2 + "\n");
//        sheet.getRow(4).getCell(5).setCellType(CellType.STRING);
//        String v3 = sheet.getRow(4).getCell(5).getStringCellValue();
        String v3 = getStringCellValByZb(sheet, "F5").getStringCellValue();
        sb.append("取水口所在行政区:" + v3 + "\n");
//        sheet.getRow(3).getCell(6).setCellType(CellType.STRING);
//        String v4 = sheet.getRow(3).getCell(6).getStringCellValue();
        String v4 = getStringCellValByZb(sheet, "H5").getStringCellValue();
        sb.append("取水口所在行政区:" + v4 + "\n");
//        sheet.getRow(4).getCell(2).setCellType(CellType.STRING);
//        String v5 = sheet.getRow(4).getCell(2).getStringCellValue();
        String v5 = getStringCellValByZb(sheet, "D6").getStringCellValue();
        sb.append("取水口具体地点:" + v5 + "\n");
//        sheet.getRow(4).getCell(5).setCellType(CellType.STRING);
//        String v6 = sheet.getRow(4).getCell(5).getStringCellValue();
        String v6 = getStringCellValByZb(sheet, "G6").getStringCellValue();
        sb.append("经纬度坐标东经:" + v6 + "\n");
//        sheet.getRow(4).getCell(7).setCellType(CellType.STRING);
//        String v7 = sheet.getRow(4).getCell(7).getStringCellValue();
        String v7 = getStringCellValByZb(sheet, "I6").getStringCellValue();
        sb.append("经纬度坐标北纬:" + v7 + "\n");
//        sheet.getRow(5).getCell(3).setCellType(CellType.STRING);
//        String v8 = sheet.getRow(5).getCell(3).getStringCellValue();
        String v8 = getStringCellValByZb(sheet, "E7").getStringCellValue();
        sb.append("一级区名称:" + v8 + "\n");
//        sheet.getRow(6).getCell(3).setCellType(CellType.STRING);
//        String v9 = sheet.getRow(6).getCell(3).getStringCellValue();
        String v9 = getStringCellValByZb(sheet, "E8").getStringCellValue();
        sb.append("取水口类型:" + v9 + "\n");
        System.out.println(sb.toString());
    }

    /**
     * 根据坐标获取cell的string类型的值
     *
     * @param sheet
     * @param xy
     * @return
     */
    private static String getStringValByZb(Sheet sheet, String xy) {
        Cell cell = getStringCellValByZb(sheet, xy);
        if (null == cell) {
            return null;
        }
//        if ("填写".equalsIgnoreCase(cell.getStringCellValue())) {
//            return null;
//        }
        return dealCellType(cell);
        //return cell.getStringCellValue();
    }

    /**
     * 根据坐标获取要读取的设置成string的单元格
     *
     * @param sheet
     * @param xy
     * @return
     */
    private static Cell getStringCellValByZb(Sheet sheet, String xy) {
        if (null == sheet) {
            zbs.add(doDealIngFileName + "!" + xy);
            return null;
        }
        char[] chars = xy.toCharArray();
        String lie = String.valueOf(chars[0]);
        //处理下D11这种多数值的情况
        int row = 0;
        if (2 >= chars.length) {
            row = NumberUtils.toInt(String.valueOf(chars[1]));
        } else {
            String rowStr = "";
            for (int i = 1; i < chars.length; i++) {
                rowStr += String.valueOf(chars[i]);
            }
            row = NumberUtils.toInt(String.valueOf(rowStr));
        }

        int lieNum = 0;
        switch (lie.toUpperCase()) {
            case "A":
                lieNum = 0;
                break;
            case "B":
                lieNum = 1;
                break;
            case "C":
                lieNum = 2;
                break;
            case "D":
                lieNum = 3;
                break;
            case "E":
                lieNum = 4;
                break;
            case "F":
                lieNum = 5;
                break;
            case "G":
                lieNum = 6;
                break;
            case "H":
                lieNum = 7;
                break;
            case "I":
                lieNum = 8;
                break;
            case "J":
                lieNum = 9;
                break;
            case "K":
                lieNum = 10;
                break;
            case "L":
                lieNum = 11;
                break;
            case "M":
                lieNum = 12;
                break;
            case "N":
                lieNum = 13;
                break;
            case "O":
                lieNum = 14;
                break;
        }
        Row row1 = sheet.getRow(row - 1);
//        CellStyle cellStyle;
//        String extString = doDealIngFileName.substring(doDealIngFileName.lastIndexOf("."));
//        if (".xls".equals(extString)) {
//            cellStyle = new HSSFCellStyle();
//        } else if (".xlsx".equals(extString)) {
//            wb = new XSSFWorkbook(is);
//        }
        //row1.setRowStyle(HSSFCellStyle.class);
        if (null == row1) {
            zbs.add(doDealIngFileName + "@" + xy);
            return null;
        }
        if (null == sheet.getRow(row - 1).getCell(lieNum)) {
            return null;
        } else {
            return sheet.getRow(row - 1).getCell(lieNum);
//            sheet.getRow(row - 1).getCell(lieNum).setCellType(CellType.STRING);
//            sheet.getRow(row - 1).getCell(lieNum).setCellType(1);
//            return sheet.getRow(row - 1).getCell(lieNum);
        }
    }

    /**
     * 细节处理下cell
     *
     * @param cell
     */
    private static String dealCellType(Cell cell) {

        if (cell.getCellType() == CellType.NUMERIC) {
            String tempStr = cell.toString();
            if (getScientific(tempStr)) {
                cell.setCellType(CellType.STRING);
                return cell.getStringCellValue().trim();
            }
            if (!tempStr.startsWith("0") && tempStr.endsWith("0") && tempStr.indexOf(".") != -1) {
                cell.setCellType(CellType.STRING);
                return cell.getStringCellValue().trim();
            }
            return cell.toString().trim();
//            return String.valueOf(cell.getNumericCellValue());
        } else {
            cell.setCellType(CellType.STRING);
            return cell.getStringCellValue().trim();
        }

    }

    /**
     * 正则判断是否为科学计数法
     *
     * @param input
     * @return
     */
    public static boolean getScientific(String input) {
        String regx = "^((-?\\d+.?\\d*)[Ee]{1}(-?\\d+))$";//科学计数法正则表达式
        Pattern pattern = Pattern.compile(regx);
        return pattern.matcher(input).matches();
    }

    /**
     * 将文件读取成excel
     *
     * @param file
     * @return
     */
    private static Workbook readExcel(File file) throws Exception {
        //System.out.println("file.getPath()获取到的路径是这样的: " + file.getPath());
        if (file.getName().startsWith("~$")) {
            System.out.println("特殊文件--》" + file.getPath());
            return null;
        }
        Workbook wb = readExcel(file.getPath());
        return wb;
    }

    /**
     * 读excel
     *
     * @param path
     * @return
     * @throws IOException
     */
    private static Workbook readExcel(String path) throws Exception {
        String extString = path.substring(path.lastIndexOf("."));

        InputStream is = new FileInputStream(path);
        Workbook wb = null;
        if (".xls".equals(extString)) {
            wb = new HSSFWorkbook(is);
        } else if (".xlsx".equals(extString)) {
//            wb = create(is);
            wb = new XSSFWorkbook(is);
        }
        return wb;
    }

    public static Workbook create(InputStream in) throws
            IOException, InvalidFormatException {
        return WorkbookFactory.create(in);

        // poi版本升级后，下面老方法已不可用
//        if (!in.markSupported()) {
//            in = new PushbackInputStream(in, 8);
//        }
//        if (POIFSFileSystem.hasPOIFSHeader(in)) {
//            return new HSSFWorkbook(in);
//        }
//        if (POIXMLDocument.hasOOXMLHeader(in)) {
//            return new XSSFWorkbook(OPCPackage.open(in));
//        }
//        throw new IllegalArgumentException("你的excel版本目前poi解析不了");
    }

}