List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getLastRowNum
@Override
public int getLastRowNum()
From source file:com.viettel.hqmc.DAO.FilesDAO.java
/** * import du lieu tu excel/* w ww. j a va 2 s . c om*/ * * @return */ public String importFileFromExcel() throws FileNotFoundException, IOException, ParseException { List fileInfo = new ArrayList(); String strReturn = ERROR_PERMISSION; // TechnicalStandard ts = new TechnicalStandard(); TechnicalStandardDAOHE tshe = new TechnicalStandardDAOHE(); String err = ""; Long attachId = Long.parseLong(getRequest().getParameter("attachId"));//get attactId VoAttachs att = (VoAttachs) getSession().get("com.viettel.voffice.database.BO.VoAttachs", attachId);//Attachs BO if (att == null) { fileInfo.add("File not found"); err += "File not found"; } else { Category item; ResourceBundle rb = ResourceBundle.getBundle("config");//get link tuong doi String dir = rb.getString("directoryExcel"); String linkFile = att.getAttachPath(); linkFile = dir + linkFile; createForm.setPath(linkFile); InputStream myxls = new FileInputStream(linkFile);//get file excel XSSFWorkbook wb = new XSSFWorkbook(myxls); XSSFRow row = null; String matchingTarget = null; XSSFCell productName = null; XSSFCell businessTaxCode = null; XSSFCell manufactorAddress = null; XSSFCell manufactorName = null; XSSFCell manufactorTel = null; XSSFCell manufactorFax = null; XSSFCell manufactorEmail = null; XSSFCell nationName = null; XSSFCell signer = null; XSSFCell assessmentMethod = null; XSSFCell annoucementNo = null; XSSFCell pushlishDate = null; XSSFCell nationCompanyName = null; XSSFCell nationCompanyAddress = null; try { XSSFSheet sheet = wb.getSheetAt(0); try { // XSSFSheet sheet1 = wb.getSheetAt(1); } catch (Exception ex) { LogUtil.addLog(ex);//binhnt sonar a160901 // log.error(e.getMessage()); err += "Khng tm thy Sheet Chi tit sn phm, "; } try { // XSSFSheet sheet2 = wb.getSheetAt(2); } catch (Exception ex) { // log.error(e.getMessage()); LogUtil.addLog(ex);//binhnt sonar a160901 err += "Khng tm thy Sheet Ch tiu cht lng ch yu, "; } try { // XSSFSheet sheet3 = wb.getSheetAt(3); } catch (Exception ex) { LogUtil.addLog(ex);//binhnt sonar a160901 // log.error(e.getMessage()); err += "Khng tm thy Sheet Ch tiu vi sinh vt, "; } try { // XSSFSheet sheet4 = wb.getSheetAt(4); } catch (Exception ex) { LogUtil.addLog(ex);//binhnt sonar a160901 // log.error(e.getMessage()); err += "Khng tm thy Sheet Hm lng kim loi nng, "; } try { // XSSFSheet sheet5 = wb.getSheetAt(5); } catch (Exception ex) { LogUtil.addLog(ex);//binhnt sonar a160901 // log.error(e.getMessage()); err += "Khng tm thy Sheet Hm lng ha cht, "; } try { // XSSFSheet sheet6 = wb.getSheetAt(6); } catch (Exception ex) { LogUtil.addLog(ex);//binhnt sonar a160901 // log.error(e.getMessage()); err += "Khng tm thy Sheet K hoch kim sot cht lng, "; } if (sheet == null) { err += "Khng tm thy Sheet Bn cng b, "; } else { String sheetName = sheet.getSheetName(); if (!"Ban_Cong_bo".equals(sheetName)) { err += "Sai tn sheet Bn cng b, "; } } // XSSFRow firstRow = sheet.getRow(1); int rowNums = sheet.getLastRowNum(); // UsersDAOHE sdhe = new UsersDAOHE(); // SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); row = sheet.getRow(1); businessTaxCode = row.getCell((short) 1); productName = row.getCell((short) 3); row = sheet.getRow(4); manufactorName = row.getCell((short) 1); manufactorAddress = row.getCell((short) 3); row = sheet.getRow(5); manufactorTel = row.getCell((short) 1); manufactorFax = row.getCell((short) 3); row = sheet.getRow(6); manufactorEmail = row.getCell((short) 1); nationName = row.getCell((short) 3); row = sheet.getRow(7); nationCompanyName = row.getCell((short) 1); nationCompanyAddress = row.getCell((short) 3); row = sheet.getRow(10); annoucementNo = row.getCell((short) 1); pushlishDate = row.getCell((short) 3); row = sheet.getRow(11); signer = row.getCell((short) 1); assessmentMethod = row.getCell((short) 3); matchingTarget = ""; String standardCode; for (int i = 12; i < rowNums; i++) { row = sheet.getRow(i); if (row.getCell((short) 1).toString() != "") { XSSFCell standardCodeCell = row.getCell((short) 1); standardCode = standardCodeCell.getRichStringCellValue().toString(); if (tshe.findStandardByCode(standardCode)) { XSSFCell matchingTargetCell = row.getCell((short) 2); matchingTarget += matchingTargetCell.getRichStringCellValue() + ";"; } else { err += "Quy chun (quy nh) " + standardCode + " khng chnh xc ! "; break; } } else { break; } } } catch (Exception ex) { LogUtil.addLog(ex);//binhnt sonar a160901 // log.error(e.getMessage()); err += "li tab bn cng b hp quy "; } if (matchingTarget != "" && matchingTarget != null) { matchingTarget = matchingTarget.substring(0, matchingTarget.length() - 1); } //tab chi tiet san pham XSSFCell productNo = null; XSSFCell productStatus = null; XSSFCell productColor = null; XSSFCell productSmell = null; XSSFCell productOtherstatus = null; XSSFCell productType = null; XSSFCell otherTarget = null; XSSFCell component = null; XSSFCell timeinuse = null; XSSFCell useage = null; XSSFCell objectInUse = null; XSSFCell guideline = null; //XSSFCell packageRecipe = null; XSSFCell packageMaterial = null; XSSFCell productProcess = null; XSSFCell counterfeitDistinctive = null; XSSFCell origin = null; XSSFCell signDate = null; XSSFCell signer_productdetails = null; XSSFCell chemicalTargetUnwanted = null; try { XSSFSheet sheet1 = wb.getSheetAt(1); if (sheet1 == null) { err += "Khng tm thy Sheet Chi tit sn phm, "; } else { String sheetName = sheet1.getSheetName(); if (!"Chi_tiet_san_pham".equals(sheetName)) { err += "Sai tn Sheet Chi tit sn phm, "; } } row = sheet1.getRow(1); productType = row.getCell((short) 1); productNo = row.getCell((short) 3); row = sheet1.getRow(4); productStatus = row.getCell((short) 1); productColor = row.getCell((short) 3); row = sheet1.getRow(5); productSmell = row.getCell((short) 1); productOtherstatus = row.getCell((short) 3); row = sheet1.getRow(13); otherTarget = row.getCell((short) 1); row = sheet1.getRow(14); component = row.getCell((short) 1); timeinuse = row.getCell((short) 3); row = sheet1.getRow(15); useage = row.getCell((short) 1); objectInUse = row.getCell((short) 3); row = sheet1.getRow(16); guideline = row.getCell((short) 1); packageMaterial = row.getCell((short) 3); row = sheet1.getRow(17); productProcess = row.getCell((short) 3); //packageRecipe = row.getCell((short) 1); row = sheet1.getRow(18); counterfeitDistinctive = row.getCell((short) 1); origin = row.getCell((short) 3); row = sheet1.getRow(19); signDate = row.getCell((short) 1); signer_productdetails = row.getCell((short) 3); // bo sung ham luong hoa chat khong mong muon XSSFSheet sheet5 = wb.getSheetAt(5); int rowNums = sheet5.getLastRowNum(); do { row = sheet5.getRow(rowNums); chemicalTargetUnwanted = row.getCell((short) 2); rowNums--; } while (chemicalTargetUnwanted == null); // chemicalTargetUnwanted = row.getCell((short) 2); } catch (Exception ex) { LogUtil.addLog(ex);//binhnt sonar a160901 // log.error(e.getMessage()); err += "Li tab chi tit sn phm "; } // do du lieu vao form Long fileId = getRequest().getParameter("fileId") == null ? 0L : Long.parseLong(getRequest().getParameter("fileId")); Long fileType = getRequest().getParameter("fileType") == null ? 0L : Long.parseLong(getRequest().getParameter("fileType")); if (fileType > 0L && fileId > 0L) { createForm = new FilesForm(); createForm.setFileType(fileType); createForm.setFileId(fileId); } UsersDAOHE udhe = new UsersDAOHE(); Users user = udhe.findById(getUserId()); BusinessDAOHE bdhe = new BusinessDAOHE(); Business bus = bdhe.findById(user.getBusinessId()); if (createForm.getFileId() != null && createForm.getFileId() > 0l) { FilesDAOHE fdhe = new FilesDAOHE(); createForm = fdhe.getFilesDetail(createForm.getFileId()); if (!createForm.getFileType().equals(0L)) { ProcedureDAOHE cdhe = new ProcedureDAOHE(); List lstTTHC = cdhe.getProcedureForChange(createForm.getFileType()); lstCategory = new ArrayList(); lstCategory.addAll(lstTTHC); lstCategory.add(0, new Procedure(Constants.COMBOBOX_HEADER_VALUE, Constants.COMBOBOX_HEADER_TEXT_SELECT)); getRequest().setAttribute("lstFileType", lstCategory); } } if (createForm.getFileType() != null && createForm.getFileType() > 0l) { ProcedureDAOHE pdhe = new ProcedureDAOHE(); CategoryDAOHE cdhe = new CategoryDAOHE(); TechnicalStandardDAOHE tdhe = new TechnicalStandardDAOHE(); FilesDAOHE fdhe = new FilesDAOHE(); if (!fileType.equals(0L)) { createForm.setFileType(fileType); } Procedure tthc = pdhe.findById(createForm.getFileType()); if (tthc != null) { lstProductType = cdhe.findAllCategory("SP"); lstUnit = cdhe.findAllCategory("DVI"); lstStandard = tdhe.findAllStandard(); String lstDepts = convertToJSONData(lstStandard, "vietnameseName", "vietnameseName"); getRequest().setAttribute("lstStandard", lstDepts); UserAttachsDAOHE uahe = new UserAttachsDAOHE(); lstUserAttach = uahe.findAllUserAttach(getUserId()); String lstUserAttachs = convertToJSONData(lstUserAttach, "attachName", "attachName"); getRequest().setAttribute("lstUserAttach", lstUserAttachs); if (lstUserAttachs.trim().length() > 0) { createForm.setCountUA(1L); } else { createForm.setCountUA(0L); } getRequest().setAttribute("lstProductType", lstProductType); getRequest().setAttribute("lstUnit", lstUnit); String fileLst = tthc.getFileList(); getRequest().setAttribute("fileList", com.viettel.common.util.StringUtils.removeHTML(fileLst)); getRequest().setAttribute("agencyName", getDepartmentName()); getRequest().setAttribute("fileNameFull", tthc.getName()); strReturn = tthc.getDescription(); if (createForm.getAnnouncement() != null) { if (createForm.getAnnouncement().getAnnouncementNo() != null && createForm.getAnnouncement().getAnnouncementNo().length() > 0l) { return strReturn; } } if (strReturn.equals(Constants.FILE_DESCRIPTION.ANNOUNCEMENT_FILE01) || strReturn.equals(Constants.FILE_DESCRIPTION.ANNOUNCEMENT_FILE03) || strReturn.equals(Constants.FILE_DESCRIPTION.CONFIRM_FUNC_IMP) || strReturn.equals(Constants.FILE_DESCRIPTION.CONFIRM_FUNC_VN) || strReturn.equals(Constants.FILE_DESCRIPTION.CONFIRM_NORMAL_IMP) || strReturn.equals(Constants.FILE_DESCRIPTION.CONFIRM_NORMAL_VN) || strReturn.equals(Constants.FILE_DESCRIPTION.REC_CONFIRM_NORMAL_IMP) || strReturn.equals(Constants.FILE_DESCRIPTION.RE_ANNOUNCEMENT) || strReturn.equals(Constants.FILE_DESCRIPTION.RE_CONFIRM_FUNC_IMP) || strReturn.equals(Constants.FILE_DESCRIPTION.RE_CONFIRM_FUNC_VN) || strReturn.equals(Constants.FILE_DESCRIPTION.RE_CONFIRM_NORMAL_VN)) { String announcementNoStr = fdhe.getReceiptNoNew(getUserId(), getUserLogin(), createForm.getFileType()); createForm.setAnnouncement(new AnnouncementForm()); createForm.getAnnouncement().setAnnouncementNo(announcementNoStr); // thong tin doanh nghiep createForm.getAnnouncement().setBusinessAddress(bus.getBusinessAddress()); createForm.getAnnouncement().setBusinessFax(bus.getBusinessFax()); createForm.getAnnouncement().setBusinessName(bus.getBusinessName()); createForm.getAnnouncement().setBusinessTelephone(bus.getBusinessTelephone()); createForm.getAnnouncement().setBusinessEmail(bus.getUserEmail()); createForm.getAnnouncement().setBusinessLicence(bus.getBusinessLicense()); // ho so cap lai 7-11 createForm.setReIssueForm(new ReIssueFormForm()); createForm.getReIssueForm().setBusinessName(bus.getBusinessName()); createForm.getReIssueForm().setIdentificationNumber(bus.getBusinessLicense()); createForm.getReIssueForm().setAddress(bus.getBusinessAddress()); createForm.getReIssueForm().setEmail(bus.getUserEmail()); createForm.getReIssueForm().setTelephone(bus.getBusinessTelephone()); createForm.getReIssueForm().setFax(bus.getBusinessFax()); //set thong tin tu excel try { if (businessTaxCode != null && user.getUserName().equals(businessTaxCode.toString())) { if (matchingTarget != "" && matchingTarget != null) { createForm.getAnnouncement().setMatchingTarget(matchingTarget.toString()); } createForm.getAnnouncement().setProductName(productName.toString()); createForm.getAnnouncement().setManufactureAddress(manufactorAddress.toString()); createForm.getAnnouncement().setManufactureName(manufactorName.toString()); createForm.getAnnouncement().setManufactureTel(manufactorTel.toString()); createForm.getAnnouncement().setManufactureFax(manufactorFax.toString()); createForm.getAnnouncement().setManufactureEmail(manufactorEmail.toString()); createForm.getAnnouncement().setNationName(nationName.toString()); createForm.getAnnouncement().setSigner(signer.toString()); createForm.getAnnouncement() .setNationCompanyAddress(nationCompanyAddress.toString()); createForm.getAnnouncement().setNationCompanyName(nationCompanyName.toString()); createForm.getAnnouncement().setAssessmentMethod(assessmentMethod.toString()); if (pushlishDate.toString() != null && pushlishDate.toString().length() > 0) { createForm.getAnnouncement().setPublishDate(DateTimeUtils .convertStringToTime(pushlishDate.toString(), "dd/MM/yyyy")); } createForm.getAnnouncement().setAnnouncementNo(annoucementNo.toString()); //tab thong tin chi tiet createForm.setDetailProduct(new DetailProductForm()); createForm.getDetailProduct().setProductNo(productNo.toString()); createForm.getDetailProduct().setProductStatus(productStatus.toString()); createForm.getDetailProduct().setProductColor(productColor.toString()); createForm.getDetailProduct().setProductSmell(productSmell.toString()); createForm.getDetailProduct().setProductOtherStatus(productOtherstatus.toString()); item = cdhe.findCategoryByName("SP", productType.toString()); if (item != null) { createForm.getDetailProduct().setProductType(item.getCategoryId()); } else { err += "Danh mc " + productType.toString() + " khng chnh xc, "; } createForm.getDetailProduct().setOtherTarget(otherTarget.toString()); createForm.getDetailProduct().setComponents(component.toString()); createForm.getDetailProduct().setTimeInUse(timeinuse.toString()); createForm.getDetailProduct().setUseage(useage.toString()); createForm.getDetailProduct().setObjectUse(objectInUse.toString()); createForm.getDetailProduct().setGuideline(guideline.toString()); //createForm.getDetailProduct().setPackageRecipe(packageRecipe.toString()); createForm.getDetailProduct().setPackateMaterial(packageMaterial.toString()); createForm.getDetailProduct().setProductionProcess(productProcess.toString()); createForm.getDetailProduct() .setCounterfeitDistinctive(counterfeitDistinctive.toString()); createForm.getDetailProduct().setOrigin(origin.toString()); if (signDate.toString() != null && signDate.toString().length() > 0) { createForm.getDetailProduct().setSignDate( DateTimeUtils.convertStringToTime(signDate.toString(), "dd/MM/yyyy")); } createForm.getDetailProduct().setSigner(signer_productdetails.toString()); createForm.getDetailProduct() .setChemicalTargetUnwanted(chemicalTargetUnwanted.toString()); createForm.setStatusExcel( err += "Thm mi bn cng b hp quy thnh cng "); } else { createForm.setStatusExcel(err += "M s thu khng chnh xc "); } } catch (Exception ex) { // log.error(parseException); LogUtil.addLog(ex);//binhnt sonar a160901 createForm.setStatusExcel( err += "Thm mi bn cng b hp quy khng thnh cng "); } } } } } CategoryDAOHE ctdhe = new CategoryDAOHE(); Category cate = ctdhe.findCategoryByTypeAndCode("SP", "TPCN"); Category cateTL = ctdhe.findCategoryByTypeAndCode("SP", "TL"); List<Category> cate1 = ctdhe.findCategoryByTypeAndCodeNew("SP", "DBT"); String dbtId = ""; for (int i = 0; i < cate1.size(); i++) { dbtId += cate1.get(i).getCategoryId().toString() + ";"; } Long tpcnId = cate.getCategoryId(); Long tlId = cateTL.getCategoryId(); FeeDAOHE fdhe1 = new FeeDAOHE(); Fee findfee1 = fdhe1.findFeeByCode("TPDB"); Long priceTPDB = findfee1.getPrice(); Fee findfee2 = fdhe1.findFeeByCode("TPCN"); Long priceTPCN = findfee2.getPrice(); Fee findfee3 = fdhe1.findFeeByCode("TPK"); Long priceETC = findfee3.getPrice(); getRequest().setAttribute("dbtId", dbtId); getRequest().setAttribute("tpcnId", tpcnId); getRequest().setAttribute("tlId", tlId); getRequest().setAttribute("priceTPCN", priceTPCN); getRequest().setAttribute("priceTPDB", priceTPDB); getRequest().setAttribute("priceETC", priceETC); return strReturn; }
From source file:com.viettel.hqmc.DAO.FilesDAO.java
/** * load mainly target tu excel//w w w. j a v a2s . c om * * @return */ public String loadMainlyTargetExcel() throws FileNotFoundException, IOException { // mainly target Category item = new Category(); String linkFile = getRequest().getParameter("path"); String fileError = ""; List customInfo = new ArrayList(); InputStream myxls = new FileInputStream(linkFile);//get file excel XSSFWorkbook wb = new XSSFWorkbook(myxls); List<MainlyTarget> lstMainlyTarget = null; try { XSSFSheet sheet2 = wb.getSheetAt(2); if (sheet2 == null) { fileError += "Khng tm thy Sheet Ch tiu cht lng ch yu, "; } else { String sheetName = sheet2.getSheetName(); if (!"Chi_tieu_chat_luong_chu_yeu".equals(sheetName)) { fileError += "Sai tn Sheet Ch tiu cht lng ch yu, "; } } XSSFRow row; int rowNums2 = sheet2.getLastRowNum(); lstMainlyTarget = new ArrayList<MainlyTarget>(); CategoryDAOHE cdhed = new CategoryDAOHE(); for (int i = 2; i < rowNums2; i++) { row = sheet2.getRow(i); if (row.getCell((short) 1) != null && row.getCell((short) 1).toString().trim() != "") { MainlyTarget temp = new MainlyTarget(); XSSFCell targetName = row.getCell((short) 1); XSSFCell unitId = row.getCell((short) 2); XSSFCell publishLevel = row.getCell((short) 3); XSSFCell meetLevel = row.getCell((short) 4); item = cdhed.findCategoryByName("DVI", unitId.toString()); if (item != null) { temp.setMeetLevel(meetLevel.toString()); temp.setTargetName(targetName.toString()); temp.setUnitId(item.getCategoryId().toString()); temp.setPublishLevel(publishLevel.toString()); lstMainlyTarget.add(temp); } else { fileError += "Danh mc n v " + unitId.toString() + " khng chnh xc, "; } } else { break; } } fileError += "Thm mi cc ch tiu cht lng ch yu thnh cng "; customInfo.add(fileError); } catch (Exception ex) { fileError += "Thm mi cc ch tiu cht lng ch yu khng thnh cng "; customInfo.add(fileError); // log.error(e.getMessage()); LogUtil.addLog(ex);//binhnt sonar a160901 } jsonDataGrid.setCustomInfo(customInfo); jsonDataGrid.setItems(lstMainlyTarget); return GRID_DATA; }
From source file:com.viettel.hqmc.DAO.FilesDAO.java
/** * load product target tu excel/*from w w w.j av a 2s. co m*/ * * @return */ public String loadProductTargetExcel() throws FileNotFoundException, IOException { Category item = new Category(); String linkFile = getRequest().getParameter("path"); String fileError = ""; List customInfo = new ArrayList(); InputStream myxls = new FileInputStream(linkFile);//get file excel XSSFWorkbook wb = new XSSFWorkbook(myxls); List<ProductTarget> lstProductTarget = null; try { XSSFSheet sheet3 = wb.getSheetAt(3); if (sheet3 == null) { fileError += "Khng tm thy Sheet Ch tiu vi sinh vt, "; } else { String sheetName = sheet3.getSheetName(); if (!"Chi_tieu_vi_sinh_vat".equals(sheetName)) { fileError += "Sai tn Sheet Ch tiu vi sinh vt, "; } } XSSFRow row; int rowNums3 = sheet3.getLastRowNum(); lstProductTarget = new ArrayList<ProductTarget>(); CategoryDAOHE cdhed = new CategoryDAOHE(); // vi sinh vat for (int i = 2; i < rowNums3; i++) { row = sheet3.getRow(i); if (row.getCell((short) 1) != null && !"".equals(row.getCell((short) 1).toString().trim())) { ProductTarget temp = new ProductTarget(); XSSFCell targetName = row.getCell((short) 1); XSSFCell unitId = row.getCell((short) 2); XSSFCell maxLevel = row.getCell((short) 3); item = cdhed.findCategoryByName("DVI", unitId.toString().trim()); if (item != null) { temp.setTargetName(targetName.toString()); temp.setUnitId(item.getCategoryId().toString()); temp.setMaxLevel(maxLevel.toString()); temp.setTargetType(1l); lstProductTarget.add(temp); } else { fileError += "Danh mc n v " + unitId.toString() + " khng chnh xc, "; } } else { break; } } //kim loai nang XSSFSheet sheet4 = wb.getSheetAt(4); if (sheet4 == null) { fileError += "Khng tm thy Sheet Hm lng kim loi nng, "; } else { String sheetName2 = sheet4.getSheetName(); if (!"Ham_luong_kim_loai_nang".equals(sheetName2)) { fileError += "Sai tn Sheet Hm lng kim loi nng, "; } } int rowNums4 = sheet4.getLastRowNum(); for (int i = 2; i < rowNums4; i++) { row = sheet4.getRow(i); if (row.getCell((short) 1) != null && !"".equals(row.getCell((short) 1).toString().trim())) { ProductTarget temp = new ProductTarget(); XSSFCell targetName = row.getCell((short) 1); XSSFCell unitId = row.getCell((short) 2); XSSFCell maxLevel = row.getCell((short) 3); item = cdhed.findCategoryByName("DVI", unitId.toString().trim()); if (item != null) { temp.setTargetName(targetName.toString()); temp.setUnitId(item.getCategoryId().toString()); temp.setMaxLevel(maxLevel.toString()); temp.setTargetType(2l); lstProductTarget.add(temp); } else { fileError += "Danh mc n v " + unitId.toString() + " khng chnh xc, "; } } else { break; } } //hoa chat khong mong muon XSSFSheet sheet5 = wb.getSheetAt(5); if (sheet5 == null) { fileError += "Khng tm thy Sheet Hm lng ha cht khng mong mun, "; } else { String sheetName1 = sheet5.getSheetName(); if (!"Ham_luong_hoa_chat".equals(sheetName1)) { fileError += "Sai tn Sheet Hm lng ha cht khng mong mun, "; } } int rowNums5 = sheet5.getLastRowNum(); for (int i = 2; i < rowNums5; i++) { row = sheet5.getRow(i); if (row.getCell((short) 1) != null && !"".equals(row.getCell((short) 1).toString().trim())) { ProductTarget temp = new ProductTarget(); XSSFCell targetName = row.getCell((short) 1); XSSFCell unitId = row.getCell((short) 2); XSSFCell maxLevel = row.getCell((short) 3); item = cdhed.findCategoryByName("DVI", unitId.toString().trim()); if (item != null) { temp.setTargetName(targetName.toString()); temp.setUnitId(item.getCategoryId().toString()); temp.setMaxLevel(maxLevel.toString()); temp.setTargetType(3l); lstProductTarget.add(temp); } else { fileError += "Danh mc n v " + unitId.toString() + " khng chnh xc, "; } } else { break; } } fileError += "Thm mi cc ch tiu vi sinh vt, hm lng kim loi nng, hm lng ha cht khng mong mun thnh cng "; customInfo.add(fileError); } catch (Exception ex) { fileError += "Thm mi cc ch tiu vi sinh vt, hm lng kim loi nng, hm lng ha cht khng mong mun khng thnh cng "; customInfo.add(fileError); // log.error(e.getMessage()); LogUtil.addLog(ex);//binhnt sonar a160901 } jsonDataGrid.setCustomInfo(customInfo); jsonDataGrid.setItems(lstProductTarget); return GRID_DATA; }
From source file:com.viettel.hqmc.DAO.FilesDAO.java
/** * load quality control plan excel// ww w . j av a 2 s.c o m * * @return */ public String loadQualityControlsExcel() throws FileNotFoundException, IOException { // mainly target // Category item = new Category(); String linkFile = getRequest().getParameter("path"); String fileError = ""; List customInfo = new ArrayList(); InputStream myxls = new FileInputStream(linkFile);//get file excel XSSFWorkbook wb = new XSSFWorkbook(myxls); List<QualityControlPlan> lstQualityControl = null; try { XSSFSheet sheet6 = wb.getSheetAt(6); if (sheet6 == null) { fileError += "Khng tm thy Sheet K hoch kim sot, "; } else { String sheetName1 = sheet6.getSheetName(); if (!"Ke_Hoach_Kiem_soat".equals(sheetName1)) { fileError += "Khng tm thy Sheet K hoch kim sot, "; } } XSSFRow row; int rowNums6 = sheet6.getLastRowNum(); lstQualityControl = new ArrayList<QualityControlPlan>(); // CategoryDAOHE cdhed = new CategoryDAOHE(); for (int i = 2; i < rowNums6; i++) { row = sheet6.getRow(i); if (row.getCell((short) 1).toString().trim() != "") { QualityControlPlan temp = new QualityControlPlan(); XSSFCell processDetails = row.getCell((short) 1); XSSFCell controlTarget = row.getCell((short) 2); XSSFCell technicalRegulation = row.getCell((short) 3); XSSFCell patternFrequence = row.getCell((short) 4); XSSFCell testDevice = row.getCell((short) 5); XSSFCell testMethod = row.getCell((short) 6); // XSSFCell noteForm = row.getCell((short) 7); XSSFCell note = row.getCell((short) 8); temp.setProductProcessDetail(processDetails.toString()); temp.setControlTarget(controlTarget.toString()); temp.setTechnicalRegulation(technicalRegulation.toString()); temp.setPatternFrequence(patternFrequence.toString()); temp.setTestDevice(testDevice.toString()); temp.setTestMethod(testMethod.toString()); temp.setNote(note.toString()); temp.setNoteForm(note.toString()); lstQualityControl.add(temp); } else { break; } } fileError = "Thm mi k hoch kim sot cht lng thnh cng "; customInfo.add(fileError); } catch (Exception ex) { fileError = "Thm mi k hoch kim sot cht lng khng thnh cng "; customInfo.add(fileError); // log.error(e.getMessage()); LogUtil.addLog(ex);//binhnt sonar a160901 } jsonDataGrid.setCustomInfo(customInfo); jsonDataGrid.setItems(lstQualityControl); return GRID_DATA; }
From source file:com.yanglb.utilitys.codegen.core.reader.impl.HashMapReaderImpl.java
License:Apache License
/** * ?Sheet// ww w.ja v a2 s.c o m */ @Override protected HashMap<String, String> onReader(XSSFSheet sheet) { HashMap<String, String> result = new HashMap<String, String>(); for (int row = this.startRowNo; row < sheet.getLastRowNum(); row++) { XSSFRow xssfRow = sheet.getRow(row); String key = this.getCellStringValue(xssfRow.getCell(this.startColNo)); // key?Map if (xssfRow.getCell(this.startColNo).getCellType() == XSSFCell.CELL_TYPE_BLANK || StringUtility.isNullOrEmpty(key)) { continue; } String value = this.getCellStringValue(xssfRow.getCell(this.startColNo + 1)); result.put(key, value); } return result; }
From source file:com.yanglb.utilitys.codegen.core.reader.impl.TableReaderImpl.java
License:Apache License
/** * ?DBSheet//from ww w. j a v a 2 s . c o m * @throws CodeGenException */ @Override protected TableModel onReader(XSSFSheet sheet) throws CodeGenException { TableModel model = super.onReader(sheet, TableModel.class); // KEY String[] keys = null; List<String> listKey = new ArrayList<String>(); Map<String, String> curValue = null; // ? for (int rowNo = this.startRowNo; rowNo <= sheet.getLastRowNum(); rowNo++) { XSSFRow row = sheet.getRow(rowNo); // KEY if (rowNo == this.startRowNo) { keys = new String[row.getLastCellNum()]; } else { curValue = new HashMap<String, String>(); } // ? ?? boolean allBlank = true; // ? if (row == null) { throw new CodeGenException(String.format("error: sheet(%s), row(%d)", sheet.getSheetName(), rowNo)); } for (int colNo = this.startColNo; colNo < row.getLastCellNum(); colNo++) { XSSFCell cell = row.getCell(colNo); if (cell == null) { throw new CodeGenException(String.format("error: sheet(%s), row(%d), col(%d)", sheet.getSheetName(), rowNo, colNo)); } if (cell.getCellType() != XSSFCell.CELL_TYPE_BLANK && cell.getCellType() != XSSFCell.CELL_TYPE_ERROR) { allBlank = false; } String value = this.getCellStringValue(cell); // KEY if (rowNo == this.startRowNo) { if (StringUtility.isNullOrEmpty(value) || cell.getCellType() == XSSFCell.CELL_TYPE_BLANK || "-".equals(value)) { // / continue; } keys[colNo] = value; listKey.add(value); } else { if (colNo >= keys.length) continue; // String key = keys[colNo]; if (!StringUtility.isNullOrEmpty(key)) { curValue.put(keys[colNo], value); } } } // ? if (!allBlank && curValue != null) { model.insert(curValue); } } // model.setColumns((String[]) listKey.toArray(new String[0])); return model; }
From source file:com.yyl.common.utils.excel.ExcelTools.java
private static List<List<Object>> readXLSXWithHeader(InputStream inputStream) throws IOException { // InputStream is = new FileInputStream(file); XSSFWorkbook wb = new XSSFWorkbook(inputStream); XSSFSheet sheet = wb.getSheetAt(0); List<List<Object>> o = new ArrayList(); List<Object> temp = null; if (sheet == null) { return null; }//ww w . j a v a2s . c om for (int row_index = 0; row_index <= sheet.getLastRowNum(); row_index++) { XSSFRow row = sheet.getRow(row_index); if (row == null) { continue; } temp = new ArrayList(); for (int col_index = 0; col_index <= row.getLastCellNum(); col_index++) { temp.add(getCellValue(row.getCell(col_index))); } o.add(temp); } return o; }
From source file:com.yyl.common.utils.excel.ExcelTools.java
private static List<List<Object>> readXLSX(InputStream inputStream) throws IOException { // InputStream is = new FileInputStream(file); XSSFWorkbook wb = new XSSFWorkbook(inputStream); XSSFSheet sheet = wb.getSheetAt(0); List<List<Object>> o = new ArrayList(); List<Object> temp = null; if (sheet == null) { return null; }//from w w w . jav a2 s .c o m for (int row_index = 1; row_index <= sheet.getLastRowNum(); row_index++) { XSSFRow row = sheet.getRow(row_index); if (row == null) { continue; } temp = new ArrayList(); for (int col_index = 0; col_index <= row.getLastCellNum(); col_index++) { temp.add(getCellValue(row.getCell(col_index))); } o.add(temp); } return o; }
From source file:com.yyl.common.utils.excel.ExcelTools.java
private static List<Map<String, String>> readXLSX(InputStream inputStream, Map<String, Integer> keyMaps) throws IOException { // InputStream is = new FileInputStream(file); XSSFWorkbook wb = new XSSFWorkbook(inputStream); List<Map<String, String>> list = new ArrayList(); Map<String, String> temp = null; for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) { XSSFSheet sheet = wb.getSheetAt(sheetIndex); if (sheet == null) { continue; }/*from w ww . j a v a 2s . c om*/ for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) { XSSFRow row = sheet.getRow(rowIndex); temp = new HashMap(); if (row != null) { for (Entry<String, Integer> entry : keyMaps.entrySet()) { int index = entry.getValue(); XSSFCell cell = row.getCell(index); temp.put(entry.getKey(), getCellValue(cell)); } } list.add(temp); } } return list; }
From source file:comparator.Comparator.java
public static void delta_MVC_MTC() throws IOException { //Get the input files //FileInputStream mtcFile = new FileInputStream(new File("\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\Catalogues\\workingMTC.xlsx")); //FileInputStream mvcFile = new FileInputStream(new File("\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\Catalogues\\Informal_epSOS-MVC_V1_9.xlsx")); FileInputStream mtcFile = new FileInputStream(new File( "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\MTC_2.0.xlsx")); FileInputStream mtcFile2 = new FileInputStream(new File( "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\MTC_2.0.xlsx")); FileInputStream mvcFile = new FileInputStream(new File( "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\Informal_epSOS-MVC_V2_0_(DRAFT)_03.xlsx")); //Prepare the output file //Writer csvW = new BufferedWriter(new OutputStreamWriter(new FileOutputStream("\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\Catalogues\\delta_Mtc-Mvc.csv"), "UTF-8")); Writer csvW = new BufferedWriter(new OutputStreamWriter(new FileOutputStream( "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\delta_Mtc-Mvc2.1.csv"), "UTF-8")); csvW.write('\ufeff'); csvW.write("Expand Project;"); csvW.write("\n\n"); //Get the workbook instance for XLS file XSSFWorkbook mtcWorkbook = new XSSFWorkbook(mtcFile); XSSFWorkbook mtcWorkbook2 = new XSSFWorkbook(mtcFile2); XSSFWorkbook mvcWorkbook = new XSSFWorkbook(mvcFile); //Output/*from w w w .j a v a2s . com*/ csvW.write("One MTC sheet is missing in MVC : VS16_epSOSErrorCodes;"); csvW.write("\n"); csvW.write("********************;"); csvW.write("\n"); csvW.write("Set name;"); csvW.write("\n"); csvW.write("MTC mismatches;List of the codes missing in MVC"); csvW.write("\n"); csvW.write("MVC mismatches;List of the codes missing in MTC"); csvW.write("\n"); csvW.write("********************;"); XSSFSheet mtcSheet; XSSFSheet mtcSheet2; Iterator<Row> mtcRowIterator; Iterator<Row> mtcRowIterator2; Iterator<Row> mvcRowIterator; Iterator<Cell> mtcCellIterator; Iterator<Cell> mvcCellIterator; int mtcCol; int mvcCol; boolean mtcColFound; boolean mvcColFound; ArrayList mtcCodes; ArrayList mvcCodes; ArrayList mtcEnglishNames; ArrayList mvcEnglishNames; ArrayList englishNamesdifferences; Row mtcRow; Row mtcRow2; Row mvcRow; Row mvcRow2; Row newRow; Cell newCell; CellStyle myStyle; String mtcSplit[]; String mvcSplit[]; String mtcSheetName; String mvcSheetName; //Get the sheet from the MTC workbook for (int i = 0; i < mtcWorkbook.getNumberOfSheets(); i++) { mtcSheet = mtcWorkbook.getSheetAt(i); mtcSheet2 = mtcWorkbook2.getSheetAt(i); //Get iterator to all the rows in current MTC sheet mtcRowIterator = mtcSheet.iterator(); mtcRowIterator2 = mtcSheet2.iterator(); //Get the sheet from the MVC workbook for (int j = 0; j < mvcWorkbook.getNumberOfSheets(); j++) { XSSFSheet mvcSheet = mvcWorkbook.getSheetAt(j); //Get iterator to all the rows in current MVC sheet mvcRowIterator = mvcSheet.iterator(); //Get the name of MTC sheet and MVC sheet, compare them if they contain data //MTC data files are called "VSX_sheetName" //MVC data files are called "epSOSsheetName" mtcSplit = mtcSheet.getSheetName().split("_"); mvcSplit = mvcSheet.getSheetName().split("SOS"); mtcSheetName = mtcSplit[mtcSplit.length - 1]; mvcSheetName = mvcSplit[mvcSplit.length - 1]; //And process the file matching or throw out the file that has no equivalent if (mtcSheetName.equals(mvcSheetName)) { mtcCol = 0; mvcCol = 0; mtcColFound = false; mvcColFound = false; mtcCodes = new ArrayList(); mvcCodes = new ArrayList(); mtcEnglishNames = new ArrayList(); mvcEnglishNames = new ArrayList(); englishNamesdifferences = new ArrayList(); //For each row, iterate through each columns //Get iterator to all cells of current row //In MTC while (mtcRowIterator.hasNext()) { mtcRow = mtcRowIterator.next(); mtcRow2 = mtcRow; if (mtcColFound == false) { mtcCellIterator = mtcRow.cellIterator(); while (mtcCellIterator.hasNext()) { Cell mtcCell = mtcCellIterator.next(); if (mtcCell.getCellType() == 1 && (mtcCell.getStringCellValue().equals("Code") || mtcCell.getStringCellValue().equals("epSOS Code"))) { mtcCol = mtcCell.getColumnIndex(); mtcColFound = true; break; } } } else { mtcRow.getCell(mtcCol, Row.CREATE_NULL_AS_BLANK).setCellType(Cell.CELL_TYPE_STRING); mtcRow2.getCell(mtcCol + 1, Row.CREATE_NULL_AS_BLANK) .setCellType(Cell.CELL_TYPE_STRING); mtcCodes.add(mtcRow.getCell(mtcCol).getStringCellValue().trim()); mtcEnglishNames.add(mtcRow2.getCell(mtcCol + 1).getStringCellValue().trim()); } } //In MVC while (mvcRowIterator.hasNext()) { mvcRow = mvcRowIterator.next(); mvcRow2 = mvcRow; if (mvcColFound == false) { mvcCellIterator = mvcRow.cellIterator(); while (mvcCellIterator.hasNext()) { Cell mvcCell = mvcCellIterator.next(); if (mvcCell.getCellType() == 1 && (mvcCell.getStringCellValue().equals("epSOS Code") || mvcCell.getStringCellValue().equals("Code"))) { mvcCol = mvcCell.getColumnIndex(); mvcColFound = true; break; } } } else { mvcRow.getCell(mvcCol, Row.CREATE_NULL_AS_BLANK).setCellType(Cell.CELL_TYPE_STRING); mvcRow2.getCell(mvcCol + 1, Row.CREATE_NULL_AS_BLANK) .setCellType(Cell.CELL_TYPE_STRING); mvcCodes.add(mvcRow.getCell(mvcCol).getStringCellValue().trim()); mvcEnglishNames.add(mvcRow2.getCell(mvcCol + 1).getStringCellValue().trim()); } } //Processing colCompare(mtcCodes, mvcCodes, mvcEnglishNames, mtcEnglishNames, englishNamesdifferences); //Output //if((!mtcCodes.isEmpty()) || (!mvcCodes.isEmpty())) {} csvW.write("\n\n"); csvW.write(mtcSheetName + ";"); csvW.write("\n"); csvW.write("MTC mismatches;"); for (int a = 0; a < mtcCodes.size(); a++) { csvW.write(mtcCodes.get(a) + ";"); } csvW.write("\n"); csvW.write("MVC mismatches\n"); for (int b = 0; b < mvcCodes.size(); b++) { csvW.write(mvcCodes.get(b) + ";" + mvcEnglishNames.get(b) + "\n"); } csvW.write("english names differences\n"); if (!englishNamesdifferences.isEmpty()) { csvW.write("code;MTC 2.0;MVC 2.0.1\n"); for (int c = 0; c < englishNamesdifferences.size(); c = c + 3) { csvW.write(englishNamesdifferences.get(c) + ";" + englishNamesdifferences.get(c + 1) + ";" + englishNamesdifferences.get(c + 2) + "\n"); } } /* work on currents MTC2.0 sheet */ mtcColFound = false; mtcCol = 0; List<Integer> delRows = new ArrayList(); //recreate iterator to all the rows in current MTC sheet while (mtcRowIterator2.hasNext()) { mtcRow = mtcRowIterator2.next(); mtcRow2 = mtcRow; if (mtcColFound == false) { mtcCellIterator = mtcRow.cellIterator(); while (mtcCellIterator.hasNext()) { Cell mtcCell = mtcCellIterator.next(); if (mtcCell.getCellType() == 1 && (mtcCell.getStringCellValue().equals("Code") || mtcCell.getStringCellValue().equals("epSOS Code"))) { mtcCol = mtcCell.getColumnIndex(); mtcColFound = true; break; } } } else { mtcRow.getCell(mtcCol, Row.RETURN_NULL_AND_BLANK).setCellType(Cell.CELL_TYPE_STRING); mtcRow2.getCell(mvcCol + 1, Row.CREATE_NULL_AS_BLANK) .setCellType(Cell.CELL_TYPE_STRING); for (int a = 0; a < mtcCodes.size(); a++) { if (mtcRow.getCell(mtcCol).getStringCellValue().trim().equals(mtcCodes.get(a))) { // delete row corresponding to useless code delRows.add(mtcRow.getRowNum()); break; } } if (!englishNamesdifferences.isEmpty()) { for (int c = 0; c < englishNamesdifferences.size(); c = c + 3) { if (mtcRow2.getCell(mtcCol + 1).getStringCellValue().trim() .equals(englishNamesdifferences.get(c + 1))) { mtcRow2.getCell(mtcCol + 1) .setCellValue(englishNamesdifferences.get(c + 2).toString()); break; } } } } } for (int d = delRows.size() - 1; d >= 0; d--) { mtcSheet2.shiftRows(delRows.get(d) + 1, mtcSheet2.getLastRowNum() + 1, -1); } myStyle = mtcSheet2.getRow(0).getCell(0).getCellStyle(); for (int b = 0; b < mvcCodes.size(); b++) { newRow = mtcSheet2.createRow(mtcSheet2.getLastRowNum() + 1); for (int bb = 0; bb < mtcSheet2.getRow(0).getLastCellNum(); bb++) { newCell = newRow.createCell(bb); newCell.setCellStyle(myStyle); if (bb == mtcCol) { newCell.setCellValue(mvcCodes.get(b).toString()); } else if (bb == mtcCol + 1) { newCell.setCellValue(mvcEnglishNames.get(b).toString()); } } } } } } //close InputStream mtcFile.close(); mtcFile2.close(); mvcFile.close(); //close OutputStream csvW.close(); //Open FileOutputStream to write updates FileOutputStream output_file = new FileOutputStream(new File( "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\MTC_2.0_new.xlsx")); //write changes mtcWorkbook2.write(output_file); //close the stream output_file.close(); }