解析Excel - genscript-lz/manufacturing GitHub Wiki

        List<ArrayList<String>> excelList = new ArrayList<ArrayList<String>>();
        List<List<String>> errorExcelList = new ArrayList<List<String>>();
        if (upload != null) {
            String fileNameSuffix = uploadFileName.substring(uploadFileName.lastIndexOf(".") + 1);
            // The upload file type is Excel type
            if (fileNameSuffix.equalsIgnoreCase("xls") || fileNameSuffix.equalsIgnoreCase("xlsx")) {
                StringBuffer msg = new StringBuffer();
                excelList = excelUtil.readTool(upload, uploadFileName);
                Integer userId = SessionUtil.getUserId();
                Date now = new Date();
                for (int i = 1; i < excelList.size(); i++) {
                    List<String> strList = excelList.get(i);
                    try {
                        
                        String vectorName = strList.get(1);
                        String orderNo = strList.get(2);
                        String productionTeam = strList.size() >3?strList.get(3):"";
                        String sequence = strList.size() > 4 ?strList.get(4) :"";
                        String resistance = strList.size() > 5 ? strList.get(5) : "";
                        String length = strList.size() >6?strList.get(6):"";
                        String replicon = strList.size() >7?strList.get(7):"";
                        String copyNo = strList.size()>8?strList.get(8):"";
                        String functionalElement = strList.size() >9?strList.get(9):"";
                        String cultureCondition = strList.size() >10?strList.get(10):"";
                        String cloningSite = strList.size() >11?strList.get(11):"";
                        String appliedOrder = strList.size() > 12?strList.get(12):"";
                        String comment = strList.size()>13?strList.get(13):"";
                        VectorArkInfo info = new VectorArkInfo();
                        if(StringUtils.isBlank(vectorName)) {
                            strList.add("Vector name is empty");
                            errorExcelList.add(strList);
                            continue;
                        }
                        if(StringUtils.isNotBlank(orderNo) && this.toolService.getVectorByOrderNo(orderNo) != null) {
                            strList.add("order no is exist in system.");
                            errorExcelList.add(strList);
                            continue;
                        }
                        if(StringUtils.isBlank(orderNo) && this.toolService.getVectorByVectorName(vectorName) != null) {
                            strList.add("Vector name is exist in system.");
                            errorExcelList.add(strList);
                            continue;
                        }
                        info.setVectorName(vectorName.trim());
                        if(StringUtils.isNotBlank(sequence)) {
                            info.setSequence(sequence.trim());
                        } else {
                            info.setSequence(""); 
                        }
                        info.setAppliedOrder(appliedOrder);
                        info.setCloningSite(cloningSite);
                        info.setComment(comment);
                        info.setCopyNo(copyNo);
                        info.setCreatedBy(userId);
                        info.setCreationDate(now);
                        info.setCultureCondition(cultureCondition);
                        info.setFunctionalElement(functionalElement);
                        info.setModifiedBy(userId);
                        info.setModifyDate(now);
                        info.setOrderNo(orderNo);
                        info.setProductionTeam(productionTeam);
                        info.setReplicon(replicon);
                        info.setResistance(resistance);
                        if(StringUtils.isNotBlank(length.trim()) && StringUtils.isNumeric(length.trim())) {
                            info.setSequenceLength(Integer.parseInt(length.trim()));
                        } else {
                            info.setSequenceLength(info.getSequence().length());
                        }
                       this.toolService.saveVectorInfo(info);
                    } catch (Exception e) {
                        e.printStackTrace();
                        strList.add(e.getMessage());
                        errorExcelList.add(strList);
                        continue;
                    }
                    
                }
                if(errorExcelList.size() > 0) {
                    File file = this.toolService.createExcelForVector(errorExcelList);
                    String html = Struts2Util.conventJavaObjToJson(file.getPath()+";"+file.getName());
                    Struts2Util.renderHtml(html);
                    return null;
                    
                }
                
            }
        }
    } catch (Exception e) {
        String html = Struts2Util.conventJavaObjToJson("Error");
        Struts2Util.renderHtml(html);
        e.printStackTrace();
        mimeMailService.sendMail("[email protected]", "Batch Update Vector Error", DateUtils.formatDate2Str(new Date()) + "===" + e.getMessage(), "[email protected]");
    }
    String html = Struts2Util.conventJavaObjToJson("Success");
    Struts2Util.renderHtml(html);
    return null;
}