解析Excel单元格，上传到后台

/**
* 数据导入
*/
@SuppressWarnings(“unchecked”)
@Syslog(optType = OptType.IMPORT, isLogParams = true, moduleName = “assetsImport”)
@RequestMapping(value = “/importData”, method = RequestMethod.POST)
public void importData(HttpSession sesstion, HttpServletRequest request,
HttpServletResponse response) {
response.setCharacterEncoding(“UTF-8”);
response.setContentType(“text/html; charset=utf-8”);
try {
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
CommonsMultipartFile uploadFj = (CommonsMultipartFile) multipartRequest.getFile(“file”);
if (uploadFj == null || uploadFj == null) {
log.debug(“文件不存在”);
this.addResultInfo(FAILURE, “文件不存在”, false);
} else {
//user
ShiroUser user = this.getUser();
String userCn = registerService.getDeptNameCn(user) + “-” + user.fullName;
//备注
String remark = request.getParameter(“remark”);
//common select // 数据来源
List dataSourceList = registerIndicatorsDao.getOption(“dataSource”, “”);
Map<String, String> venderMap = getMapByList(“DATASOURCE_ID”, “DATASOURCE_NAME”, dataSourceList);
// 主题域
List timeLevelList = registerIndicatorsDao.getOption(“theme”, “9B48B30986374483BB2445EC9E000114”);
Map<String, String> timeLevelMap = getMapByList(“DIM_ID”, “DIM_NAME”, timeLevelList);
// *业务分类
List relatedList = registerIndicatorsDao.getOption(“theme”, “67AD02244A834A3FA7061056B3F963F6”);
Map<String, String> relatedMap = getMapByList(“DIM_ID”, “DIM_NAME”, relatedList);
// 指标单位
List demandDeptList = registerIndicatorsDao.getOption(“units”, “”);
Map<String, String> demandDeptMap = getMapByList(“UNIT_ID”, “UNIT_NAME”, demandDeptList);
// 指标需求部门 维护部门 提供部门
List deptList = registerIndicatorsDao.getOption(“dept”, “”);
Map<String, String> deptMap = getMapByList(“DEPT_ID”, “DEPT_NAME”, deptList);
// 开发厂家，维护厂家
List venderList = registerIndicatorsDao.getOption(“vender”, “”);
Map<String, String> venderMapT = getMapByList(“VENDER_ID”, “VENDER_NAME”, venderList);

            List<JSONObject> kpiUuidList = new ArrayList<JSONObject>();
            Map<String, String> kpiUuidMap = new HashMap<String, String>();
            //网络分类、网络二级分类、数据源分类、数据源二级分类、专题分类、专题二级分类、业务分类、业务二级分类
            //1-b869167f2c024abc99b89b937e6abd60-90a28aa3696f4e25aa7cc49f4cf806fb	4G网络-故障指标
            List<JSONObject> fourTypeList = service.queryCommonList("fourType");
            Map<String, String> fourTypeMap = getMapByList("VALUE", "TEXT", fourTypeList);
            //一级、二级分类分开
            List<JSONObject> oneLevelTypeList = service.queryCommonList("oneLevelType");
            //columns
            String[] columnsAry = {
                    "指标英文名称,指标名称,指标简称,单位,业务负责人,技术负责人,指标路径,指标需求部门,指标维护部门,指标提供部门,指标考核类型,指标重要级别,指标类型,开发厂家,开发人员,维护厂家,维护人员,主题域ID,主题域子域ID,业务分类,业务子分类,所属分层,所属业务域,数据来源,业务口径,技术口径,指标状态,资产注册人,资产密级,资产状态,备注"

            };
            String[] columnsIndexAry = {
                    "KPI_ID,KPI_NAME,KPI_SIMP_NAME,UNIT_ID,BUSI_DIRECTOR,TECH_DIRECTOR,KPI_PATH,BUSI_DEPT_ID,MAINT_DEPT_ID,PROVIDE_DEPT_ID,CHECK_TYPE,VITAL_LEVEL,KPI_TYPE,DEVELOP_VENDER,DEVELOP_USER,MAINT_VENDER,MAINT_USER,THEME_ID,SUB_THEME_ID,BUSI_TYPE,SUB_BUSI_TYPE,LAYER_ID,SUB_LAYER_ID,DATA_SOURCES_ID,BUSI_CALIBER,TECH_CALIBER,KPI_STATUS,ASSET_REG_USER,ASSET_LEVEL,ASSET_STATUS,REMARK"

            };
            //start
            String[] flagAry = {"base"};
            InputStream is = uploadFj.getInputStream();
            Workbook wb = WorkbookFactory.create(is);
            //EXCEPITON
            //联合主键（指标ID、指标名称、数据源分类）为空的行号/指标ID、关联指标名称/指标ID、关联报表名称
            String notExistRowNumThreeStr = "";//sheet2的uuid在sheet0中不存在的行号
            String failRowNumOneStr = "";//sheet0失败的行
            String notExistRowNumOneStr = "";//sheet2 8大类不符合维表内容记录行号
            String notExistRowNumFourStr = "";//sheet3的uuid在sheet0中不存在的行号

// String notExistRowNumFourLevelStr = “”;//sheet3的时间粒度不存在
//失败条数
int sheetOneSumCount = 0, sheetOneSuccessCount = 0;
//入库
List msgList = new ArrayList();
// String result = null;
// 总条数
int totalCount = 0;
for (int f = 0; f < flagAry.length; f++) {
String[] columnsList = columnsAry[f].split(",");
String[] columnsIndexList = columnsIndexAry[f].split(",");
List<Map<String, String>> dataList = new ArrayList<Map<String, String>>();
Sheet sheet = wb.getSheetAt(f);
int sheetcount = sheet.getLastRowNum();// 数据总条数
totalCount = sheet.getLastRowNum();
if (f == 0) {
sheetOneSumCount = sheetcount;
}
if (!“指标英文名称”.equals(sheet.getRow(0).getCell(0).getStringCellValue().toUpperCase())) {
log.debug(“导入失败，导入模版与系统模版不一致！”);
this.addResultInfo(FAILURE, “导入失败，导入模版与系统模版不一致！”, false);
break;
}
for (int j = 1; j <= sheetcount; j++) {
Map<String, String> dataMap = new HashMap<String, String>();
Row row = sheet.getRow(j);
// 判断必填项是否为空，为空不插入
if (WebTool.isNull(row.getCell(0).getStringCellValue().toUpperCase()) ||
WebTool.isNull(row.getCell(1).getStringCellValue().toUpperCase()) ||
WebTool.isNull(row.getCell(3).getStringCellValue().toUpperCase()) ||
WebTool.isNull(row.getCell(4).getStringCellValue().toUpperCase()) ||
WebTool.isNull(row.getCell(5).getStringCellValue().toUpperCase()) ||
WebTool.isNull(row.getCell(7).getStringCellValue().toUpperCase()) ||
WebTool.isNull(row.getCell(8).getStringCellValue().toUpperCase()) ||
WebTool.isNull(row.getCell(9).getStringCellValue().toUpperCase()) ||
WebTool.isNull(row.getCell(10).getStringCellValue().toUpperCase()) ||
WebTool.isNull(row.getCell(11).getStringCellValue().toUpperCase()) ||
WebTool.isNull(row.getCell(12).getStringCellValue().toUpperCase()) ||
WebTool.isNull(row.getCell(13).getStringCellValue().toUpperCase()) ||
WebTool.isNull(row.getCell(14).getStringCellValue().toUpperCase()) ||
WebTool.isNull(row.getCell(15).getStringCellValue().toUpperCase()) ||
WebTool.isNull(row.getCell(16).getStringCellValue().toUpperCase()) ||
WebTool.isNull(row.getCell(17).getStringCellValue().toUpperCase()) ||
WebTool.isNull(row.getCell(18).getStringCellValue().toUpperCase()) ||
WebTool.isNull(row.getCell(19).getStringCellValue().toUpperCase()) ||
WebTool.isNull(row.getCell(20).getStringCellValue().toUpperCase()) ||
WebTool.isNull(row.getCell(21).getStringCellValue().toUpperCase()) ||
WebTool.isNull(row.getCell(22).getStringCellValue().toUpperCase())) {
dataMap.clear();
failRowNumOneStr += 1;
continue;
}

                    for (int q = 0; q < columnsList.length; q++) {
                        Cell currCell = row.getCell(q);
                        if (currCell == null) {
                            currCell = row.createCell(q);
                        }
                        currCell.setCellType(Cell.CELL_TYPE_STRING);
                        if (f == 0) {
                            //根据数据来源查找指标ID
                            if (q == 23) {
                                dataMap.put(columnsIndexList[q], venderMap.get(row.getCell(q).getStringCellValue().toUpperCase()));
                            } else if (q == 3) {
                                // 单位
                                dataMap.put(columnsIndexList[q], demandDeptMap.get(row.getCell(q).getStringCellValue().toUpperCase()));
                            } else if (q == 8 || q == 9 || q == 7) {
                                // 部门
                                dataMap.put(columnsIndexList[q], deptMap.get(row.getCell(q).getStringCellValue().toUpperCase()));
                            } else if (q == 13 || q == 15) {
                                // 厂家
                                dataMap.put(columnsIndexList[q], venderMapT.get(row.getCell(q).getStringCellValue().toUpperCase()));
                            } else if (q == 11) {
                                // 指标重要级别
                                if ("高".equals(row.getCell(q).getStringCellValue().toUpperCase())) {
                                    dataMap.put(columnsIndexList[q], "0");
                                } else if ("一般".equals(row.getCell(q).getStringCellValue().toUpperCase())) {
                                    dataMap.put(columnsIndexList[q], "1");
                                } else if ("低".equals(row.getCell(q).getStringCellValue().toUpperCase())) {
                                    dataMap.put(columnsIndexList[q], "2");
                                }
                            } else if (q == 12) {
                                // 指标类型
                                if ("基础指标".equals(row.getCell(q).getStringCellValue().toUpperCase())) {
                                    dataMap.put(columnsIndexList[q], "0");
                                } else if ("汇总指标".equals(row.getCell(q).getStringCellValue().toUpperCase())) {
                                    dataMap.put(columnsIndexList[q], "1");
                                }
                            } else if (q == 17) {
                                // 主题域
                                dataMap.put(columnsIndexList[q], timeLevelMap.get(row.getCell(q).getStringCellValue().toUpperCase()));
                            } else if (q == 19) {
                                // 主题域
                                dataMap.put(columnsIndexList[q], relatedMap.get(row.getCell(q).getStringCellValue().toUpperCase()));
                            } else {
                                dataMap.put(columnsIndexList[q], row.getCell(q).getStringCellValue());
                            }
                        }
                    }
                    if (!dataMap.isEmpty()) {
                        // 根据 指标名称’+‘指标编码’+‘主题域’+‘主题子域’+‘所属分层 判断是否存在，存在则不插入
                        RegisterIndicatorReq req = new RegisterIndicatorReq();
                        req.setKpi_name(dataMap.get("KPI_NAME"));
                        req.setKpi_id(dataMap.get("KPI_ID"));
                        req.setTheme_id(dataMap.get("THEME_ID"));
                        req.setLayer_id(dataMap.get("LAYER_ID"));
                        List indicator = registerIndicatorsDao.getIndicator(req);
                        if (indicator.size() > 0) {
                            failRowNumOneStr += 1;
                            dataMap.clear();
                            continue;
                        }
                        dataList.add(dataMap);
                    }
                    //最终入库
                    if (dataList != null) {
                        if (f == 0) {
                            sheetOneSuccessCount = dataList.size();
                        }
                        String result = service.addBatchData(userCn, flagAry[f], dataList);
                        msgList.add(result);
                    }
                }
            }
            //导入是否正常
            String resultMsg = msgList.size() > 0 ? "指标资产 导入成功" : "指标资产 导入失败";
            resultMsg += ":" + failRowNumOneStr + ":" + sheetOneSuccessCount + ":" + totalCount;
            if (StringUtils.isBlank(resultMsg)) {//导入正常
                this.addResultInfo(SUCCESS, "导入成功", resultMsg);
            } else {
                this.addResultInfo(SUCCESS, "导入失败", resultMsg);
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
        this.addResultInfo(FAILURE, "导入失败，" + e.getMessage(), null);
    }
    this.responseResult(response, this.getResultJSONStr());
}