List of usage examples for org.apache.poi.ss.usermodel Row getCell
Cell getCell(int cellnum);
From source file:com.asakusafw.testdriver.excel.ExcelSheetSinkTest.java
License:Apache License
/** * many columns.//from w w w. ja v a 2 s . c om * @throws Exception if occur */ @Test public void many_columns() throws Exception { Object[] value = new Object[256]; Map<PropertyName, PropertyType> map = new TreeMap<>(); for (int i = 0; i < value.length; i++) { map.put(PropertyName.newInstance(String.format("p%04x", i)), PropertyType.INT); value[i] = i; } ArrayModelDefinition def = new ArrayModelDefinition(map); File file = folder.newFile("temp.xls"); ExcelSheetSinkFactory factory = new ExcelSheetSinkFactory(file); try (DataModelSink sink = factory.createSink(def, new TestContext.Empty())) { sink.put(def.toReflection(value)); } try (InputStream in = new FileInputStream(file)) { Workbook workbook = Util.openWorkbookFor(file.getPath(), in); Sheet sheet = workbook.getSheetAt(0); Row title = sheet.getRow(0); assertThat(title.getLastCellNum(), is((short) 256)); Row content = sheet.getRow(1); for (int i = 0; i < title.getLastCellNum(); i++) { assertThat(content.getCell(i).getNumericCellValue(), is((double) (Integer) value[i])); } } }
From source file:com.asakusafw.testdriver.excel.legacy.LegacyExcelRuleExtractor.java
License:Apache License
private String getStringCell(Sheet sheet, int rowIndex, int colIndex) { assert sheet != null; Row row = sheet.getRow(rowIndex); if (row == null) { return null; }// w w w .ja v a 2 s . c om Cell cell = row.getCell(colIndex); if (cell == null || cell.getCellType() != Cell.CELL_TYPE_STRING) { return null; } return cell.getStringCellValue(); }
From source file:com.asakusafw.testdriver.excel.legacy.LegacyExcelRuleExtractor.java
License:Apache License
@Override public String extractName(Row row) throws FormatException { if (row == null) { throw new IllegalArgumentException("row must not be null"); //$NON-NLS-1$ }/*from ww w . j av a 2 s . c o m*/ // strict checking for cell type Cell cell = row.getCell(ConditionSheetItem.COLUMN_NAME.getCol()); if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) { return null; } else if (cell.getCellType() != Cell.CELL_TYPE_STRING) { throw new FormatException(MessageFormat.format( Messages.getString("LegacyExcelRuleExtractor.errorInvalidNameType"), //$NON-NLS-1$ ConditionSheetItem.COLUMN_NAME.getName(), cell.getRowIndex() + 1, cell.getColumnIndex() + 1)); } String name = cell.getStringCellValue(); if (name.isEmpty()) { return null; } return name.toLowerCase(); }
From source file:com.asakusafw.testdriver.excel.legacy.LegacyExcelRuleExtractor.java
License:Apache License
private String getStringCell(Row row, ConditionSheetItem item) throws FormatException { assert row != null; assert item != null; Cell cell = row.getCell(item.getCol()); if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) { return ""; //$NON-NLS-1$ } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { return cell.getStringCellValue(); }/*from w w w. j a v a 2s. com*/ throw new FormatException( MessageFormat.format(Messages.getString("LegacyExcelRuleExtractor.errorInvalidStringCell"), //$NON-NLS-1$ item.getName(), cell.getRowIndex() + 1, cell.getColumnIndex() + 1)); }
From source file:com.assentisk.controller.OrganizationController.java
private ModelAndView importEmploye(HttpServletRequest req, HttpServletResponse res) throws Exception { System.out.println("hiiii heree......."); MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) req; MultipartFile multipartFile = multipartRequest.getFile("myexcel"); String fileName = multipartFile.getOriginalFilename(); map = new ModelMap(); String name = req.getParameter("fileName"); int status = 0; java.util.Date dt = new java.util.Date(); java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String currentTime = sdf.format(dt); InetAddress ip = InetAddress.getLocalHost(); String userId = (String) req.getSession().getAttribute("UserRecordID"); byte[] b = multipartFile.getBytes(); FileOutputStream fout = new FileOutputStream(new File(fileName)); fout.write(b);//from w ww . j av a 2s .c o m fout.flush(); fout.close(); try { String DepartmentID = "", LocationID = "", EmpName = "", Email1 = "", Email2 = "", address = ""; String city = "", zip = "", phone1 = "", locCoun = "", locstate = "", loccity = "", locContact = "", locEmail = ""; String phone2 = "", divisions = "", email2 = "", username = "", reporting = "", photo = ""; int isLicense = 0; int isactive = 1; int count = 0; int notcount = 0; FileInputStream file = new FileInputStream(new File(fileName)); boolean flag = false; String[] data = null; List<String> dataStatus = new ArrayList<String>(); int val = 0; //Reading .xls files if (fileName.toLowerCase().endsWith(".xls")) { // Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(file); // Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); sheet.setColumnHidden((short) 14, false); // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.rowIterator(); if (rowIterator.hasNext()) { while (rowIterator.hasNext()) { short cellIndex = 0; HSSFRow hsrow = (HSSFRow) rowIterator.next(); if (hsrow.getRowNum() != 0) { if (hsrow instanceof HSSFRow) { try { EmpName = hsrow.getCell((short) 0).getStringCellValue(); } catch (Exception e) { EmpName = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": EmpName - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": EmpName - " + e.getMessage()); val++; } try { DepartmentID = hsrow.getCell((short) 1).getStringCellValue(); } catch (Exception e) { DepartmentID = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": Department - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Department - " + e.getMessage()); val++; } try { LocationID = hsrow.getCell((short) 2).getStringCellValue(); } catch (Exception e) { LocationID = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": Location -" + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location - " + e.getMessage()); val++; } try { locCoun = hsrow.getCell((short) 3).getStringCellValue(); } catch (Exception e) { locCoun = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": location Country - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Country- " + e.getMessage()); val++; } try { locstate = hsrow.getCell((short) 4).getStringCellValue(); } catch (Exception e) { locstate = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": location State - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location State- " + e.getMessage()); val++; } try { loccity = hsrow.getCell((short) 5).getStringCellValue(); } catch (Exception e) { loccity = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": location City -" + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location City- " + e.getMessage()); val++; } try { locContact = hsrow.getCell((short) 6).getStringCellValue(); } catch (Exception e) { locContact = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": location Contact Name -" + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Contact Name- " + e.getMessage()); val++; } try { locEmail = hsrow.getCell((short) 7).getStringCellValue(); boolean isValid = false; try { // // Create InternetAddress object and validated the supplied // address which is this case is an email address. InternetAddress internetAddress = new InternetAddress(locEmail); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Email- " + e.getMessage()); } } catch (Exception e) { locEmail = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": location Email -" + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Email- " + e.getMessage()); val++; } try { divisions = hsrow.getCell((short) 8).getStringCellValue(); } catch (Exception e) { divisions = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": Divisions - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Divisions - " + e.getMessage()); val++; } try { address = hsrow.getCell((short) 9).getStringCellValue(); } catch (Exception e) { address = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": address - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": address - " + e.getMessage()); val++; } try { city = hsrow.getCell((short) 10).getStringCellValue(); } catch (Exception e) { city = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": city - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": city - " + e.getMessage()); val++; } try { zip = String.valueOf(hsrow.getCell((short) 11).getNumericCellValue()); zip = String.valueOf(zip).split("\\.")[0]; } catch (Exception e) { zip = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": zip - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": zip - " + e.getMessage()); val++; } try { DataFormatter formatter = new DataFormatter(); phone1 = String.valueOf(hsrow.getCell((short) 12).getNumericCellValue()); String df2 = formatter.formatCellValue(hsrow.getCell((short) 12)); phone1 = df2; phone1 = String.valueOf(phone1).split("\\.")[0]; } catch (Exception e) { phone1 = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": phone1 - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": phone1 - " + e.getMessage()); val++; } try { DataFormatter formatter = new DataFormatter(); phone2 = String.valueOf(hsrow.getCell((short) 13).getNumericCellValue()); String df2 = formatter.formatCellValue(hsrow.getCell((short) 13)); phone2 = df2; System.out.println("check phone1" + phone2); phone2 = String.valueOf(phone2).split("\\.")[0]; } catch (Exception e) { phone2 = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": phone2 -" + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": phone2 - " + e.getMessage()); val++; } try { boolean isValid = false; Email1 = hsrow.getCell((short) 14).getStringCellValue(); try { InternetAddress internetAddress = new InternetAddress(Email1); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Email1 - " + e.getMessage()); } } catch (Exception e) { Email1 = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": Email1 - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Email1 - " + e.getMessage()); val++; } try { boolean isValid = false; Email2 = hsrow.getCell((short) 15).getStringCellValue(); try { InternetAddress internetAddress = new InternetAddress(Email2); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Email2 - " + e.getMessage()); } } catch (Exception e) { Email2 = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": Email2 - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Email2 - " + e.getMessage()); val++; } int userdata = 0; String groupdata = ""; if (username.equals("")) { userdata = 0; reporting = "0"; groupdata = "0"; } if (!LocationID.equals("")) { LocationID = organizationDao.getLocIdByName(LocationID, currentTime, Integer.parseInt(userId), locCoun, locstate, loccity, locContact, locEmail); if (LocationID.equals("0")) { LocationID = ""; } } if (!DepartmentID.equals("")) { DepartmentID = organizationDao.getDeparmentIdByName(DepartmentID, currentTime, Integer.parseInt(userId)); if (DepartmentID.equals("0")) { DepartmentID = ""; } } if (!divisions.equals("")) { divisions = organizationDao.getDivisionByName(divisions, currentTime, Integer.parseInt(userId)); if (divisions.equals("0")) { divisions = ""; } } if (!EmpName.equals("") && !DepartmentID.equals("") && !divisions.equals("") && !LocationID.equals("")) { String duplicate = ""; try { duplicate = organizationDao.saveEmployeedata(0, DepartmentID, LocationID, EmpName, Email1, reporting, groupdata, currentTime, currentTime, Integer.parseInt(userId), currentTime, Integer.parseInt(userId), status, address, city, "", "", zip, photo, phone1, phone2, divisions, Email2); masterDao.userAuditTrail("assentisk_employees", "1", "add", currentTime, Integer.parseInt(userId), ip.getHostAddress()); } catch (Exception e) { //dataStatus.add("Error on the data : "+hsrow.getRowNum()); req.setAttribute("message", "Invalid Data File"); return new ModelAndView("organization/employees", map); } if (duplicate.equals("true")) { notcount = notcount + 1; dataStatus.add("Duplicate Row : " + hsrow.getRowNum()); } else { count = count + 1; } } else { System.out.println( "Error Row : " + hsrow.getRowNum() + "Not Inserted Row : " + EmpName); notcount = notcount + 1; } } } } } else { dataStatus.add("Please import valid Data file"); } if (count > 0) { dataStatus.add("Succesfully inserted Row : " + count); val++; } if (notcount > 0) { if (notcount == 1) { dataStatus.add(notcount + " Row is not inserted"); } else { dataStatus.add(notcount + " Rows are not inserted"); } } } //Reading .xlsx files else if (fileName.toLowerCase().endsWith(".xlsx")) { // Get the workbook instance for XLS file XSSFWorkbook wBook = new XSSFWorkbook(file); // Get first sheet from the workbook XSSFSheet sheet = wBook.getSheetAt(0); sheet.setColumnHidden((short) 14, false); // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cite = row.cellIterator(); if (row.getRowNum() != 0) { while (cite.hasNext()) { Cell c = cite.next(); if (c.getColumnIndex() == 0) { try { EmpName = c.toString(); } catch (Exception e) { EmpName = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": EmpName - " + e.getMessage()); } } if (c.getColumnIndex() == 1) { try { DepartmentID = c.toString(); } catch (Exception e) { DepartmentID = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Department - " + e.getMessage()); } } if (c.getColumnIndex() == 2) { try { LocationID = c.toString(); } catch (Exception e) { LocationID = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Location -" + e.getMessage()); } } if (c.getColumnIndex() == 3) { try { locCoun = c.toString(); } catch (Exception e) { locCoun = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": location Country - " + e.getMessage()); } } if (c.getColumnIndex() == 4) { try { locstate = c.toString(); } catch (Exception e) { locstate = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": location State - " + e.getMessage()); } } if (c.getColumnIndex() == 5) { try { loccity = c.toString(); } catch (Exception e) { loccity = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": location City -" + e.getMessage()); } } if (c.getColumnIndex() == 6) { try { locContact = c.toString(); } catch (Exception e) { locContact = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": location Contact Name -" + e.getMessage()); } } if (c.getColumnIndex() == 7) { try { locEmail = c.toString(); boolean isValid = false; try { InternetAddress internetAddress = new InternetAddress(locEmail); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": locEmail - " + e.getMessage()); } } catch (Exception e) { locEmail = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": location Email -" + e.getMessage()); } } if (c.getColumnIndex() == 8) { try { divisions = c.toString(); } catch (Exception e) { divisions = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Divisions - " + e.getMessage()); } } if (c.getColumnIndex() == 9) { try { address = c.toString(); } catch (Exception e) { address = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": address - " + e.getMessage()); } } if (c.getColumnIndex() == 10) { try { city = c.toString(); } catch (Exception e) { city = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": city - " + e.getMessage()); } } if (c.getColumnIndex() == 11) { try { zip = c.toString(); } catch (Exception e) { zip = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": zip - " + e.getMessage()); } } if (c.getColumnIndex() == 12) { try { DataFormatter formatter = new DataFormatter(); phone1 = c.toString(); String df2 = formatter.formatCellValue(row.getCell((short) 12)); phone1 = df2; } catch (Exception e) { phone1 = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": phone1 - " + e.getMessage()); } } if (c.getColumnIndex() == 13) { try { DataFormatter formatter = new DataFormatter(); phone2 = c.toString(); String df2 = formatter.formatCellValue(row.getCell((short) 12)); phone2 = df2; } catch (Exception e) { phone2 = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": phone2 -" + e.getMessage()); } } if (c.getColumnIndex() == 3) { try { Email1 = c.toString(); boolean isValid = false; try { InternetAddress internetAddress = new InternetAddress(Email1); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Email1 - " + e.getMessage()); } } catch (Exception e) { Email1 = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Email1 - " + e.getMessage()); } } if (c.getColumnIndex() == 10) { try { Email2 = c.toString(); boolean isValid = false; try { InternetAddress internetAddress = new InternetAddress(Email2); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Email2 - " + e.getMessage()); } } catch (Exception e) { Email2 = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Email2 - " + e.getMessage()); } } } int userdata = 0; String groupdata = ""; if (username.equals("")) { userdata = 0; reporting = "0"; groupdata = "0"; } if (!LocationID.equals("")) { LocationID = organizationDao.getLocIdByName(LocationID, currentTime, Integer.parseInt(userId), locCoun, locstate, loccity, locContact, locEmail); if (LocationID.equals("0")) { LocationID = ""; } } if (!DepartmentID.equals("")) { DepartmentID = organizationDao.getDeparmentIdByName(DepartmentID, currentTime, Integer.parseInt(userId)); if (DepartmentID.equals("0")) { DepartmentID = ""; } } if (!divisions.equals("")) { divisions = organizationDao.getDivisionByName(divisions, currentTime, Integer.parseInt(userId)); if (divisions.equals("0")) { divisions = ""; } } if (!EmpName.equals("") && !DepartmentID.equals("") && !divisions.equals("") && !LocationID.equals("")) { String duplicate = ""; try { duplicate = organizationDao.saveEmployeedata(0, DepartmentID, LocationID, EmpName, Email1, reporting, groupdata, currentTime, currentTime, Integer.parseInt(userId), currentTime, Integer.parseInt(userId), status, address, city, "", "", zip, photo, phone1, phone2, divisions, Email2); masterDao.userAuditTrail("assentisk_employees", "1", "add", currentTime, Integer.parseInt(userId), ip.getHostAddress()); } catch (Exception e) { //dataStatus.add("Error on the data : "+hsrow.getRowNum()); req.setAttribute("message", "Invalid Data File"); return new ModelAndView("organization/employees", map); } if (duplicate.equals("true")) { notcount = notcount + 1; dataStatus.add("Duplicate Row : " + row.getRowNum()); } else { count = count + 1; } } else { System.out.println( "Error Row : " + row.getRowNum() + "Not Inserted Row EmpName : " + EmpName); notcount = notcount + 1; } } } if (count > 0) { System.out.println("Succesfully inserted Row :" + count); dataStatus.add("Succesfully inserted Row : " + count); val++; } if (notcount > 0) { dataStatus.add(notcount + " Rows are not inserted"); } file.close(); File f = new File(fileName); if (f.exists()) { f.delete(); } } if (dataStatus.size() > 0) { map.addObject("dataStatus", dataStatus); map.addObject("datasize", "true"); } else { map.addObject("datasize", "false"); } file.close(); File f = new File(fileName); if (f.exists()) { f.delete(); } } catch (FileNotFoundException e) { e.printStackTrace(); req.setAttribute("message", "Invalid data file"); } catch (IOException e) { e.printStackTrace(); req.setAttribute("message", "Invalid data file"); } catch (Exception e) { e.printStackTrace(); req.setAttribute("message", "Invalid data file"); } return new ModelAndView("organization/employees", map); }
From source file:com.aurel.track.exchange.excel.ExcelImportBL.java
License:Open Source License
/** * Whether an uniqueIdentifierField is specified for any row If not the * corresponding row will be considered new and then we should verify * whether all required fields are present * /* w ww.jav a 2 s. com*/ * @param workbook * @param selectedSheet * @param columnIndex * @return */ private static boolean anyFieldCellsSpecified(Workbook workbook, Integer selectedSheet, Integer columnIndex) { Sheet sheet = workbook.getSheetAt(selectedSheet.intValue()); for (Row row : sheet) { int rowNum = row.getRowNum(); if (rowNum == 0) { // only the data rows are processed (the header row is not // important now) continue; } // not very rigorous but here suffice Object attribute = getStringCellValue(row.getCell(columnIndex)); if (attribute != null && !"".equals(attribute)) { return true; } } return false; }
From source file:com.aurel.track.exchange.excel.ExcelImportBL.java
License:Open Source License
/** * Get the workItems list and validate if all the fields of the excel sheet * are correct/*from w w w . ja va 2 s. co m*/ * * @param workbook * @param selectedSheet * @param personID * @param locale * @param columnIndexToFieldIDMap * @param fieldIDToColumnIndexMap * @param lastSavedIdentifierFieldIDIsSet * @param defaultValuesMap * @param invalidValueHandlingMap * @param gridErrorsMap * @param rowErrorsMap * @return */ static SortedMap<Integer, TWorkItemBean> getAndValidateGridData(Workbook workbook, Integer selectedSheet, Integer personID, Locale locale, Map<Integer, Integer> columnIndexToFieldIDMap, Map<Integer, Integer> fieldIDToColumnIndexMap, Set<Integer> lastSavedIdentifierFieldIDIsSet, Map<Integer, Integer> defaultValuesMap, Map<Integer, Integer> invalidValueHandlingMap, Map<Integer, Map<Integer, List<Integer>>> rowNoToPseudoFieldsOriginal, Map<Integer, Map<Integer, List<Integer>>> rowNoToPseudoFieldsExcel, Map<Integer, SortedMap<Integer, SortedMap<String, ErrorData>>> gridErrorsMap, Map<Integer, SortedSet<Integer>> rowErrorsMap, Map<Integer, SortedSet<Integer>> requiredFieldErrorsMap, Map<Integer, Integer> rowToParentRow) { SortedMap<Integer, TWorkItemBean> workItemBeansMap = new TreeMap<Integer, TWorkItemBean>(); Sheet sheet = workbook.getSheetAt(selectedSheet.intValue()); // get the column indexes for project and issueType Integer projectColumn = fieldIDToColumnIndexMap.get(SystemFields.INTEGER_PROJECT); Integer issueTypeColumn = fieldIDToColumnIndexMap.get(SystemFields.INTEGER_ISSUETYPE); // Maps to spare additional database accesses for default values Map<Integer, String> defaultShowValuesMap = new HashMap<Integer, String>(); Map<Integer, String> defaultLocalizedFieldLabels = new HashMap<Integer, String>(); Integer originalProject = null; Integer originalIssueType = null; Set<Integer> mandatoryIdentifierFields = ExcelFieldMatchBL.getMandatoryIdentifierFields(); Map<Integer, Map<String, ILabelBean>> systemLookups = loadBaseLookups(personID, locale); Map<String, ILabelBean> projectLookups = systemLookups.get(SystemFields.INTEGER_PROJECT); Map<Integer, Map<Integer, Map<String, ILabelBean>>> projectSpecificLookups = null; if (projectLookups != null) { projectSpecificLookups = loadProjectLookups(GeneralUtils .createIntegerListFromBeanList(GeneralUtils.createListFromCollection(projectLookups.values()))); } boolean projectSpecificIDsActive = ApplicationBean.getInstance().getSiteBean().getProjectSpecificIDsOn(); Map<Integer, TProjectBean> projectBeansMap = new HashMap<Integer, TProjectBean>(); if (projectSpecificIDsActive) { List<TProjectBean> projectBeans = ProjectBL.loadUsedProjectsFlat(personID); if (projectBeans != null) { for (TProjectBean projectBean : projectBeans) { Integer projectID = projectBean.getObjectID(); projectBeansMap.put(projectID, projectBean); String label = projectBean.getLabel(); String projectPrefix = projectBean.getPrefix(); if (projectPrefix == null || "".equals(projectPrefix)) { LOGGER.info("The project " + label + " with ID " + projectID + " has no prefix, consquently project specific item numbers might not be recognized"); } } } } /** * Process the rows only to gather the projects to issueTypes to get the * roles and restrictions once for all issues */ Map<Integer, Set<Integer>> projectToIssueTypesMap = new HashMap<Integer, Set<Integer>>(); for (Row row : sheet) { int rowNum = row.getRowNum(); if (rowNum == 0) { // only the data rows are processed (the header row is not // important now) continue; } SerializableBeanAllowedContext serializableBeanAllowedContext = new SerializableBeanAllowedContext(); serializableBeanAllowedContext.setPersonID(personID); serializableBeanAllowedContext.setNew(true); // get the project and issueType first because the other fields // could depend on these issueTypes // process the project column Integer projectID = null; if (projectColumn != null) { try { projectID = (Integer) getAttributeValue(row.getCell(projectColumn), SystemFields.INTEGER_PROJECT, null, serializableBeanAllowedContext, locale, invalidValueHandlingMap, systemLookups, projectSpecificLookups); } catch (Exception e) { } } if (projectID == null) { // no project column exists on the sheet: take the default value // which is // surely specified, otherwise it would fail at // validateRequiredColumns() projectID = defaultValuesMap.get(SystemFields.INTEGER_PROJECT); } if (projectID != null) { serializableBeanAllowedContext.setProjectID(projectID); } // process the issueType column Integer issueTypeID = null; if (issueTypeColumn != null) { try { issueTypeID = (Integer) getAttributeValue(row.getCell(issueTypeColumn), SystemFields.INTEGER_ISSUETYPE, null, serializableBeanAllowedContext, locale, invalidValueHandlingMap, systemLookups, projectSpecificLookups); } catch (Exception e) { } } if (issueTypeID == null) { // no issue type column exists on the sheet: take the default // value which is // surely specified, otherwise it would fail at // validateRequiredColumns() issueTypeID = defaultValuesMap.get(SystemFields.INTEGER_ISSUETYPE); } if (projectID != null) { Set<Integer> issueTypes = projectToIssueTypesMap.get(projectID); if (issueTypes == null) { issueTypes = new HashSet<Integer>(); projectToIssueTypesMap.put(projectID, issueTypes); } if (issueTypeID != null) { issueTypes.add(issueTypeID); } } } Map<Integer, Map<Integer, Map<Integer, TFieldConfigBean>>> projectsToIssueTypesToFieldConfigsMapForBottomUpFields = null; Map<Integer, Map<Integer, Map<String, Object>>> projectsIssueTypesFieldSettingsMapForBottomUpFields = null; Map<Integer, Map<Integer, Map<Integer, Integer>>> fieldRestrictions = AccessBeans .getFieldRestrictions(personID, projectToIssueTypesMap, null, true); Set<Integer> possibleBottomUpFields = FieldRuntimeBL.getPossibleBottomUpFields(); for (Iterator<Integer> iterator = possibleBottomUpFields.iterator(); iterator.hasNext();) { if (!fieldIDToColumnIndexMap.containsKey(iterator.next())) { // remove possible bottom up field if not mapped iterator.remove(); } if (!possibleBottomUpFields.isEmpty()) { // at least one bottom up date was mapped projectsToIssueTypesToFieldConfigsMapForBottomUpFields = FieldRuntimeBL .loadFieldConfigsInContextsAndTargetProjectAndIssueType(projectToIssueTypesMap, possibleBottomUpFields, locale, null, null); projectsIssueTypesFieldSettingsMapForBottomUpFields = FieldRuntimeBL .getFieldSettingsForFieldConfigs(projectsToIssueTypesToFieldConfigsMapForBottomUpFields); } } /** * now process the rows in detail one by one */ Stack<Integer> parentStack = new Stack<Integer>(); Map<Integer, Integer> rowToIndent = new HashMap<Integer, Integer>(); for (Row row : sheet) { int rowNum = row.getRowNum(); if (rowNum == 0) { // only the data rows are processed (the header row is not // important now) continue; } boolean excelValueFound = false; // whether the project column is mapped and excel value if found for // project boolean mappedProject = false; SerializableBeanAllowedContext serializableBeanAllowedContext = new SerializableBeanAllowedContext(); serializableBeanAllowedContext.setPersonID(personID); serializableBeanAllowedContext.setNew(true); // get the project and issueType first because the other fields // could depend on these issueTypes // process the project column Integer projectID = null; if (projectColumn != null) { try { projectID = (Integer) getAttributeValue(row.getCell(projectColumn), SystemFields.INTEGER_PROJECT, null, serializableBeanAllowedContext, locale, invalidValueHandlingMap, systemLookups, projectSpecificLookups); if (projectID != null) { mappedProject = true; excelValueFound = true; } } catch (ExcelImportNotExistingCellValueException e) { addGridError(gridErrorsMap, NOT_EXISTING_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(projectColumn), SystemFields.INTEGER_PROJECT, e.getMessage()); } catch (ExcelImportNotAllowedCellValueException e) { addGridError(gridErrorsMap, NOT_ALLOWED_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(projectColumn), SystemFields.INTEGER_PROJECT, e.getMessage()); } catch (ExcelImportInvalidCellValueException e) { addGridError(gridErrorsMap, INVALID_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(projectColumn), SystemFields.INTEGER_PROJECT, e.getMessage()); } } if (projectID == null) { // no project column exists on the sheet: take the default value // which is // surely specified, otherwise it would fail at // validateRequiredColumns() projectID = defaultValuesMap.get(SystemFields.INTEGER_PROJECT); } if (projectID != null) { serializableBeanAllowedContext.setProjectID(projectID); } // process the issueType column Integer issueTypeID = null; if (issueTypeColumn != null) { try { issueTypeID = (Integer) getAttributeValue(row.getCell(issueTypeColumn), SystemFields.INTEGER_ISSUETYPE, null, serializableBeanAllowedContext, locale, invalidValueHandlingMap, systemLookups, projectSpecificLookups); if (issueTypeID != null) { excelValueFound = true; } } catch (ExcelImportNotExistingCellValueException e) { addGridError(gridErrorsMap, NOT_EXISTING_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(issueTypeColumn), SystemFields.INTEGER_ISSUETYPE, e.getMessage()); } catch (ExcelImportNotAllowedCellValueException e) { addGridError(gridErrorsMap, NOT_ALLOWED_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(issueTypeColumn), SystemFields.INTEGER_ISSUETYPE, e.getMessage()); } catch (ExcelImportInvalidCellValueException e) { addGridError(gridErrorsMap, INVALID_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(issueTypeColumn), SystemFields.INTEGER_ISSUETYPE, e.getMessage()); } } if (issueTypeID == null) { // no issue type column exists on the sheet: take the default // value which is // surely specified, otherwise it would fail at // validateRequiredColumns() issueTypeID = defaultValuesMap.get(SystemFields.INTEGER_ISSUETYPE); } if (issueTypeID != null) { serializableBeanAllowedContext.setIssueTypeID(issueTypeID); } /* * gather the values for the identifier fields and try to get an * existing workItem by these fields */ Map<Integer, Object> identifierFieldValues = new HashMap<Integer, Object>(); if (lastSavedIdentifierFieldIDIsSet != null && !lastSavedIdentifierFieldIDIsSet.isEmpty()) { for (Integer fieldID : lastSavedIdentifierFieldIDIsSet) { Integer attributeFieldID = fieldID; if (SystemFields.INTEGER_ISSUENO.equals(fieldID) && projectSpecificIDsActive) { attributeFieldID = SystemFields.INTEGER_PROJECT_SPECIFIC_ISSUENO; } Object attributeValue = null; Integer columnIndex = null; try { columnIndex = fieldIDToColumnIndexMap.get(fieldID); attributeValue = getAttributeValue(row.getCell(columnIndex), attributeFieldID, null, serializableBeanAllowedContext, locale, invalidValueHandlingMap, systemLookups, projectSpecificLookups); if (attributeValue != null) { identifierFieldValues.put(fieldID, attributeValue); excelValueFound = true; } } catch (ExcelImportNotExistingCellValueException e) { if (!SystemFields.INTEGER_PROJECT.equals(fieldID) && !SystemFields.INTEGER_ISSUETYPE.equals(fieldID)) { // if project or issueType are set as identifier // fields and // have grid error they should be already collected // in gridErrorsMap addGridError(gridErrorsMap, NOT_EXISTING_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, e.getMessage()); } } catch (ExcelImportNotAllowedCellValueException e) { if (!SystemFields.INTEGER_PROJECT.equals(fieldID) && !SystemFields.INTEGER_ISSUETYPE.equals(fieldID)) { // if project or issueType are set as identifier // fields and // have grid error they should be already collected // in gridErrorsMap addGridError(gridErrorsMap, NOT_ALLOWED_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, e.getMessage()); } } catch (ExcelImportInvalidCellValueException e) { if (!SystemFields.INTEGER_PROJECT.equals(fieldID) && !SystemFields.INTEGER_ISSUETYPE.equals(fieldID)) { // if project or issueType are set as identifier // fields and // have grid error they should be already collected // in gridErrorsMap addGridError(gridErrorsMap, INVALID_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, e.getMessage()); } } } } // always initialize the next workItem to null TWorkItemBean workItemBean = null; boolean itemIsNew = false; if (!identifierFieldValues.isEmpty()) { if (identifierFieldValues.get(SystemFields.INTEGER_ISSUENO) != null) { // is issueNo field mapped? if (projectSpecificIDsActive) { // get by project specific itemID String projectSpecificID = null; try { projectSpecificID = (String) identifierFieldValues.get(SystemFields.INTEGER_ISSUENO); } catch (Exception e) { } if (projectSpecificID != null) { // it should be trimmed because in excel the child // issues are indented workItemBean = ItemBL.loadWorkItemByProjectSpecificID(projectID, mappedProject, projectBeansMap, projectSpecificID.trim()); if (workItemBean != null && LOGGER.isDebugEnabled()) { LOGGER.debug("WorkItem " + projectSpecificID + " from row " + rowNum + " found by projectSpecificID"); } } } else { // get by "global" workItemID Integer workItemID = null; try { workItemID = (Integer) identifierFieldValues.get(SystemFields.INTEGER_ISSUENO); } catch (Exception e) { } if (workItemID != null) { workItemBean = ItemBL.loadWorkItemSystemAttributes(workItemID); } if (workItemBean != null && LOGGER.isDebugEnabled()) { LOGGER.debug("WorkItem " + workItemID + " from row " + rowNum + " found by workItemID"); } } if (workItemBean == null) { // the issueNo field is set as identifier and the // corresponding issue does't exist, report as error addGridError(gridErrorsMap, WORKITEM_NOTEXIST_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter( fieldIDToColumnIndexMap.get(SystemFields.INTEGER_ISSUENO)), SystemFields.INTEGER_ISSUENO, identifierFieldValues.get(SystemFields.INTEGER_ISSUENO).toString()); continue; } } if (workItemBean == null) { // workItem was not found by issueNo // (issueNo field was not mapped or issueNo value is missing // from excel or // the issue's project is not accessible if // projectSpecificIDsActive) // try with user defined identifier fields try { workItemBean = ItemBL.loadWorkItemSystemAttributes(identifierFieldValues); } catch (ExcelImportNotUniqueIdentifiersException e) { addRowError(rowErrorsMap, WORKITEM_MORE_THAN_ONE_EXIST, rowNum); continue; } if (workItemBean != null && LOGGER.isDebugEnabled()) { LOGGER.debug("WorkItem from row " + rowNum + " found by user defined identifier fields"); } } if (workItemBean != null) { // existing workItem originalProject = workItemBean.getProjectID(); originalIssueType = workItemBean.getListTypeID(); // is it editable by the current person? if (!AccessBeans.isAllowedToChange(workItemBean, personID)) { addRowError(rowErrorsMap, WORKITEM_NO_EDIT_RIGHT, rowNum); continue; } // load also the custom attributes because when the workItem // will be updated // the custom attributes will also be compared to the // original value ItemBL.loadWorkItemCustomAttributes(workItemBean); serializableBeanAllowedContext.setWorkItemBeanOriginal(workItemBean); serializableBeanAllowedContext.setNew(false); // LOGGER.debug("WorkItem " + workItemBean.getObjectID() + // " from row " + rowNum + " found"); } } boolean missingRequiredFound = false; if (workItemBean == null) { // not existing found by identifier fields, create a new one workItemBean = new TWorkItemBean(); if (identifierFieldValues != null) { // preset the new workItem with the processed identifier // values for (Map.Entry<Integer, Object> identifierEntry : identifierFieldValues.entrySet()) { workItemBean.setAttribute(identifierEntry.getKey(), identifierEntry.getValue()); } } itemIsNew = true; LOGGER.debug("WorkItem from row " + rowNum + " not found. A new one will be created."); } if (projectID != null) { workItemBean.setAttribute(SystemFields.INTEGER_PROJECT, null, projectID); } else { if (itemIsNew) { // project column not mapped addRowError(requiredFieldErrorsMap, SystemFields.INTEGER_PROJECT, rowNum); missingRequiredFound = true; } } if (issueTypeID != null) { workItemBean.setAttribute(SystemFields.INTEGER_ISSUETYPE, null, issueTypeID); } else { if (itemIsNew) { // project column not mapped addRowError(requiredFieldErrorsMap, SystemFields.INTEGER_ISSUETYPE, rowNum); missingRequiredFound = true; } } if (missingRequiredFound) { continue; } Map<Integer, Integer> restrictedFields = null; projectID = workItemBean.getProjectID(); issueTypeID = workItemBean.getListTypeID(); if (projectID != null && issueTypeID != null) { Map<Integer, Map<Integer, Integer>> issueTypeRestrictions = fieldRestrictions.get(projectID); if (issueTypeRestrictions != null) { restrictedFields = issueTypeRestrictions.get(issueTypeID); } if (restrictedFields == null) { // no project or issue type mapped get the restriction now restrictedFields = AccessBeans.getFieldRestrictions(personID, projectID, issueTypeID, true); issueTypeRestrictions = new HashMap<Integer, Map<Integer, Integer>>(); issueTypeRestrictions.put(issueTypeID, restrictedFields); fieldRestrictions.put(projectID, issueTypeRestrictions); } // new values exist if (originalProject != null && originalIssueType != null) { // workItem existed if (!projectID.equals(originalProject) || !issueTypeID.equals(originalIssueType)) { if (!AccessBeans.isAllowedToChange(workItemBean, personID)) { // move not allowed addRowError(rowErrorsMap, WORKITEM_NO_EDIT_RIGHT, rowNum); continue; } } } else { // new workItem if (!AccessBeans.isAllowedToCreate(personID, projectID, issueTypeID)) { // create not allowed addRowError(rowErrorsMap, WORKITEM_NO_CREATE_RIGHT, rowNum); continue; } } } // process the remaining cells Map<Integer, Integer> rowNoToIndentLevel = new HashMap<Integer, Integer>(); for (Cell cell : row) { boolean attributeChanged = false; int columnIndex = cell.getColumnIndex(); Integer fieldID = columnIndexToFieldIDMap.get(columnIndex); Integer fieldForRestriction = fieldID; if (fieldID == null) { // LOGGER.debug("No mapping found for column " + // columnIndex); continue; } if (fieldID.equals(SystemFields.INTEGER_PROJECT) || fieldID.equals(SystemFields.INTEGER_ISSUETYPE) || identifierFieldValues.containsKey(fieldID) || mandatoryIdentifierFields.contains(fieldID)) { // these values are already read continue; } if (fieldID.intValue() < 0) { // pseudo field: now only watchers if (fieldID.intValue() == TReportLayoutBean.PSEUDO_COLUMNS.INFORMANT_LIST || fieldID.intValue() == TReportLayoutBean.PSEUDO_COLUMNS.CONSULTANT_LIST) { fieldForRestriction = FieldsRestrictionsToRoleBL.PSEUDO_COLUMNS.WATCHERS; String watcherValue = getStringCellValue(cell); if (watcherValue == null || "".equals(watcherValue.trim())) { continue; } Map<Integer, List<Integer>> watcherMapOriginal = rowNoToPseudoFieldsOriginal.get(rowNum); if (watcherMapOriginal == null) { watcherMapOriginal = new HashMap<Integer, List<Integer>>(); rowNoToPseudoFieldsOriginal.put(rowNum, watcherMapOriginal); } List<Integer> watcherListOriginal = null; TWorkItemBean workItemBeanOriginal = serializableBeanAllowedContext .getWorkItemBeanOriginal(); if (workItemBeanOriginal != null) { if (fieldID.intValue() == TReportLayoutBean.PSEUDO_COLUMNS.INFORMANT_LIST) { watcherListOriginal = GeneralUtils.createIntegerListFromBeanList( PersonBL.getDirectInformants(workItemBeanOriginal.getObjectID())); } else { watcherListOriginal = GeneralUtils.createIntegerListFromBeanList( PersonBL.getDirectConsultants(workItemBeanOriginal.getObjectID())); } watcherMapOriginal.put(fieldID, watcherListOriginal); } List<Integer> watcherListExcel = new LinkedList<Integer>(); String[] watcherNames = watcherValue .split("\\" + ConsultedInformedLoaderBL.WATCHER_SPLITTER_VALUES_STRING); if (watcherNames != null) { Map<Integer, List<Integer>> watcherMapExcel = rowNoToPseudoFieldsExcel.get(rowNum); if (watcherMapExcel == null) { watcherMapExcel = new HashMap<Integer, List<Integer>>(); rowNoToPseudoFieldsExcel.put(rowNum, watcherMapExcel); } watcherMapExcel.put(fieldID, watcherListExcel); for (int i = 0; i < watcherNames.length; i++) { String watcherName = watcherNames[i]; Integer objectID = null; try { objectID = getWatcherValue(watcherName, fieldID, systemLookups, watcherListOriginal, serializableBeanAllowedContext, locale); } catch (ExcelImportNotExistingCellValueException e) { addGridError(gridErrorsMap, NOT_EXISTING_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, e.getMessage()); } catch (ExcelImportNotAllowedCellValueException e) { addGridError(gridErrorsMap, NOT_ALLOWED_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, e.getMessage()); } if (objectID != null) { watcherListExcel.add(objectID); excelValueFound = true; } } } attributeChanged = ConsInfBL.watcherChanged(watcherListOriginal, watcherListExcel); } else { if (fieldID.intValue() == ExcelFieldMatchBL.LOCAL_PARENT_PSEUDO_COLUMN) { // local parent - child hierarchy (for new items) Integer pseudoHierarchyColumn = fieldIDToColumnIndexMap .get(ExcelFieldMatchBL.LOCAL_PARENT_PSEUDO_COLUMN); if (pseudoHierarchyColumn != null) { String hierarchyColumn = getStringCellValue(row.getCell(pseudoHierarchyColumn)); if (hierarchyColumn != null && hierarchyColumn.length() > 0) { int previousIndent = 0; if (!parentStack.isEmpty()) { Integer previousRow = parentStack.peek(); if (rowToIndent.get(previousRow) != null) { previousIndent = rowToIndent.get(previousRow).intValue(); } } int actualIndent = hierarchyColumn.length(); rowToIndent.put(rowNum, actualIndent); rowNoToIndentLevel.put(rowNum, actualIndent); if (previousIndent == actualIndent) { // sibling: same parent as the sibling's // parent if (!parentStack.isEmpty()) { // remove the sibling from stack parentStack.pop(); if (!parentStack.isEmpty()) { // if the stack is still not // empty then the peek is teh // parent Integer parentRow = parentStack.peek(); rowToParentRow.put(rowNum, parentRow); } } } else { if (actualIndent > previousIndent) { // child of the previous row if (actualIndent - previousIndent > 1) { // jump more than one in deep is // error addGridError(gridErrorsMap, INCONSISTENT_HIERARCHY_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, hierarchyColumn); } if (!parentStack.isEmpty()) { // add previous row as parent Integer parentRow = parentStack.peek(); rowToParentRow.put(rowNum, parentRow); } } else { // new hierarchy: nothing to do with // the previous row int difference = previousIndent - actualIndent; for (int i = 0; i <= difference; i++) { // pop to find the parent if (!parentStack.isEmpty()) { parentStack.pop(); } } if (!parentStack.isEmpty()) { Integer parentRow = parentStack.peek(); rowToParentRow.put(rowNum, parentRow); } } } } else { // no hierarchy string: top level item while (!parentStack.isEmpty()) { // empty the stack parentStack.pop(); } } // add row to stack for possible children parentStack.push(rowNum); } } } } else { IFieldTypeRT fieldTypeRT = FieldTypeManager.getFieldTypeRT(fieldID); Object attributeValue = null; if (fieldTypeRT.isComposite() || fieldTypeRT.isMultipleValues()) { String compositeOrMultipleValue = getStringCellValue(cell); if (compositeOrMultipleValue == null || "".equals(compositeOrMultipleValue.trim())) { workItemBean.setAttribute(fieldID, null, null); continue; } // we suppose that all composite and multiple values are // lookup values // TODO refactor if that is not true String[] parts; if (fieldTypeRT.isMultipleValues()) { parts = compositeOrMultipleValue .split(CustomSelectBaseRT.OPTION_SPLITTER_VALUES_STRING); List<Integer> multipleValues = new ArrayList<Integer>(); for (int i = 0; i < parts.length; i++) { String part = parts[i]; Integer objectID = null; try { objectID = getLookupValue(part, fieldTypeRT, fieldID, systemLookups, projectSpecificLookups, serializableBeanAllowedContext, null, invalidValueHandlingMap, locale); } catch (ExcelImportNotExistingCellValueException e) { addGridError(gridErrorsMap, NOT_EXISTING_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, e.getMessage()); } catch (ExcelImportNotAllowedCellValueException e) { addGridError(gridErrorsMap, NOT_ALLOWED_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, e.getMessage()); } if (objectID != null) { multipleValues.add(objectID); } } if (!multipleValues.isEmpty()) { attributeValue = multipleValues.toArray(); excelValueFound = true; } } else { int numberOfParts = ((CustomCompositeBaseRT) fieldTypeRT).getNumberOfParts(); parts = compositeOrMultipleValue .split("\\" + CustomCompositeBaseRT.PART_SPLITTER_VALUES_STRING); if (parts != null && parts.length > numberOfParts) { addGridError(gridErrorsMap, WRONG_COMPOSITE_SIZE, rowNum, ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, compositeOrMultipleValue); } Map<Integer, Integer> componentPartsMap = new HashMap<Integer, Integer>(); attributeValue = new HashMap<Integer, Object>(); if (parts != null) { for (int i = 0; i < parts.length; i++) { String part = parts[i]; Integer objectID = null; IFieldTypeRT componentFieldType = ((CustomCompositeBaseRT) fieldTypeRT) .getCustomFieldType(i + 1); if (componentFieldType != null) { try { objectID = getLookupValue(part, componentFieldType, fieldID, systemLookups, projectSpecificLookups, serializableBeanAllowedContext, componentPartsMap, invalidValueHandlingMap, locale); } catch (ExcelImportNotExistingCellValueException e) { addGridError(gridErrorsMap, NOT_EXISTING_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, e.getMessage()); } catch (ExcelImportNotAllowedCellValueException e) { addGridError(gridErrorsMap, NOT_ALLOWED_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, e.getMessage()); } if (objectID == null) { // workItemBean.setAttribute(fieldID, // Integer.valueOf(i+1), null); ((Map<Integer, Object>) attributeValue).put(Integer.valueOf(i + 1), null); } else { componentPartsMap.put(Integer.valueOf(i + 1), objectID); // workItemBean.setAttribute(fieldID, // Integer.valueOf(i+1), new // Object[] {objectID}); ((Map<Integer, Object>) attributeValue).put(Integer.valueOf(i + 1), new Object[] { objectID }); excelValueFound = true; } } } } } } else { // simple field // Object attributeValue = null; try { attributeValue = getAttributeValue(cell, fieldID, null, serializableBeanAllowedContext, locale, invalidValueHandlingMap, systemLookups, projectSpecificLookups); } catch (ExcelImportNotExistingCellValueException e) { addGridError(gridErrorsMap, NOT_EXISTING_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, e.getMessage()); } catch (ExcelImportNotAllowedCellValueException e) { addGridError(gridErrorsMap, NOT_ALLOWED_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, e.getMessage()); } catch (ExcelImportInvalidCellValueException e) { addGridError(gridErrorsMap, INVALID_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, e.getMessage()); } if (attributeValue != null) { excelValueFound = true; if (possibleBottomUpFields.contains(fieldID)) { TFieldConfigBean fieldConfigBean = FieldRuntimeBL .getFieldConfigForProjectIssueTypeField( projectsToIssueTypesToFieldConfigsMapForBottomUpFields, projectID, issueTypeID, fieldID); Object fieldSettings = FieldRuntimeBL.getFieldSettingsForProjectIssueTypeField( projectsIssueTypesFieldSettingsMapForBottomUpFields, projectID, issueTypeID, fieldID); if (fieldTypeRT.getHierarchicalBehavior(fieldID, fieldConfigBean, fieldSettings) == HIERARCHICAL_BEHAVIOR_OPTIONS.COMPUTE_BOTTOM_UP && ItemBL.hasChildren(workItemBean.getObjectID())) { Date trackPlusAttributeValue = (Date) workItemBean.getAttribute(fieldID); if (EqualUtils.notEqual(trackPlusAttributeValue, (Date) attributeValue)) { // add read only restrictions for start // and end date for non leaf workItems LOGGER.debug("Parent change restriction for bottom up date " + fieldID); addGridError(gridErrorsMap, NOT_EDITABLE_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, getStringCellValue(cell)); } } /* * if * (ApplicationBean.getInstance().getSiteBean * ().getSummaryItemsBehavior() && * ItemBL2.hasChildren * (workItemBean.getObjectID())) { Date * trackPlusAttributeValue = * (Date)workItemBean.getAttribute(fieldID); if * (EqualUtils.notEqual(trackPlusAttributeValue, * (Date)attributeValue)) { //add read only * restrictions for start and end date for non * leaf workItems LOGGER.debug( * "Summary parent change restriction for date " * + fieldID); addGridError(gridErrorsMap, * NOT_EDITABLE_ERRORS, rowNum, * ExcelFieldMatchBL * .colNumericToLetter(columnIndex), fieldID, * getStringCellValue(cell)); } } */ } } } attributeChanged = fieldTypeRT.valueModified(attributeValue, workItemBean.getAttribute(fieldID)); workItemBean.setAttribute(fieldID, null, attributeValue); } if (attributeChanged) { try { verifyFieldRestrictions(fieldForRestriction, restrictedFields, cell, locale); } catch (ExcelImportNotModifiableCellValueException e) { addGridError(gridErrorsMap, NOT_EDITABLE_ERRORS, rowNum, ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, e.getMessage()); } } } if (!excelValueFound) { // not a single excel value found in any cell from the row // simply neglect this row. // expanded row count can be greater than the number of real // workItem rows // for example when the content of some rows is deleted but the // rows are not deleted // and empty rows may remain in the excel LOGGER.info("The row number " + (rowNum + 1) + " contains only empty cells and will be neglected"); continue; } // add the default values for those fields which didn't have column // in // excel sheet or have column but the value is empty or not valid Iterator<Integer> itrDefaultValueFields = defaultValuesMap.keySet().iterator(); while (itrDefaultValueFields.hasNext()) { Integer fieldID = itrDefaultValueFields.next(); if (/*!fieldIDToColumnIndexMap.containsKey(fieldID) ||*/workItemBean.getAttribute(fieldID, null) == null) { if (invalidValueHandlingMap.containsKey(fieldID)) { if (DEFAULT_IF_NOT_EXIST_OR_EMPTY.equals(invalidValueHandlingMap.get(fieldID))) { IFieldTypeRT fieldTypeRT = FieldTypeManager.getFieldTypeRT(fieldID, null); ILookup lookup = (ILookup) fieldTypeRT; Integer defaultObjectID = defaultValuesMap.get(fieldID); if (defaultObjectID != null) { boolean allowed = lookup.lookupBeanAllowed(defaultObjectID, serializableBeanAllowedContext); if (allowed) { workItemBean.setAttribute(fieldID, null, defaultObjectID); } else { // for example when no default project // and/or issue type is specified the // default manager and responsible // lists contain the users which are manager // or responsible in any of the projects // (but maybe not in all) LOGGER.debug("The default value is not allowed for field " + fieldID + " on row " + rowNum); // cache the show values and localized // labels to spare additional database // accesses String showValue; if (defaultShowValuesMap.containsKey(fieldID)) { showValue = defaultShowValuesMap.get(fieldID); } else { showValue = fieldTypeRT.getShowValue(defaultObjectID, locale); defaultShowValuesMap.put(fieldID, showValue); } String localizedLabel; if (defaultLocalizedFieldLabels.containsKey(fieldID)) { localizedLabel = defaultLocalizedFieldLabels.get(fieldID); } else { localizedLabel = FieldRuntimeBL.getLocalizedDefaultFieldLabel(fieldID, locale); defaultLocalizedFieldLabels.put(fieldID, localizedLabel); } addGridError(gridErrorsMap, NOT_ALLOWED_DEFAULT_VALUES_ERRORS, rowNum, localizedLabel, fieldID, showValue); } } } } } } workItemBeansMap.put(rowNum, workItemBean); } return workItemBeansMap; }
From source file:com.avaya.plds.excel.ExcelRead.java
public List<String> getPoeticFeatureLoad(String value1, String value2, int sheetNo, int headers) { System.out.println(" Inside of getPoeticFeatureLoad method ..."); sheet = xssfWorkbook.getSheetAt(sheetNo); boolean read = false; List<String> dataList = new ArrayList<String>(); rowIterator = sheet.iterator();// www. j a v a 2 s . c om while (rowIterator.hasNext()) { StringBuilder builder = new StringBuilder(); Row row = rowIterator.next(); int rowNumber = row.getRowNum(); if (row != null) { for (short i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) { if (row.getCell(i) != null && (row.getCell(i).getCellType() == row.getCell(i).CELL_TYPE_STRING) && row.getCell(i).getStringCellValue().contains(value1) && i == 0) { read = true; break; //builder.append(value1).append("\t"); } else if (row.getCell(i) != null && (row.getCell(i).getCellType() == row.getCell(i).CELL_TYPE_STRING) && row.getCell(i).getStringCellValue().contains(value2)) { read = false; } else if (read) { // System.out.println("rowNumber "+ rowNumber); maxCellIndex = (row.getLastCellNum() > maxCellIndex && rowNumber > 0) ? row.getLastCellNum() : maxCellIndex; // System.out.println("maxCellIndex "+ maxCellIndex); Cell cell = row.getCell(i); if (cell != null) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { //if(i >0) builder.append( cell != null ? Double.valueOf(cell.getNumericCellValue()).longValue() : "") .append("\t"); } else { //if(i >0 ) builder.append(cell != null ? cell.getStringCellValue() : "").append("\t"); } } else { //if(i >0) builder.append("").append("\t"); } } if (headers == rowNumber) { //if(i>0) builder.append(row.getCell(i).getStringCellValue()).append("\t"); } } if (!builder.toString().equals("") && !builder.toString().matches("^ null.*")) dataList.add(builder.toString().replaceFirst(",", "")); } } return dataList; }
From source file:com.axelor.apps.admin.service.AsciiDocExportService.java
License:Open Source License
private void processRow(Iterator<Row> rowIterator, FileWriter fw) throws IOException { if (!rowIterator.hasNext()) { return;//w ww . j a va 2s . c om } Row row = rowIterator.next(); String type = ViewDocExportService.getCellValue(row.getCell(3)); if (type != null) { String menu = ViewDocExportService.getCellValue(row.getCell(menuIndex)); if (type.equals("MENU")) { String doc = ViewDocExportService.getCellValue(row.getCell(docIndex)); if (menu != null && doc != null) { menuMap.put(menu, doc); } } else if (!Strings.isNullOrEmpty(menu) && type.equals("general") && !menu.contains("-form(")) { menu = processMenu(menu, fw); } else { menu = null; } if (hasMenu) { processView(row, type, menu, fw); } } processRow(rowIterator, fw); }
From source file:com.axelor.apps.admin.service.AsciiDocExportService.java
License:Open Source License
private void processView(Row row, String type, String menu, FileWriter fw) throws IOException { String modelVal = ViewDocExportService.getCellValue(row.getCell(1)); String viewVal = ViewDocExportService.getCellValue(row.getCell(2)); if (Strings.isNullOrEmpty(modelVal) || Strings.isNullOrEmpty(viewVal)) { return;//from w ww. jav a2s.c o m } String doc = ViewDocExportService.getCellValue(row.getCell(docIndex)); if (menu != null && !processedMenu.contains(menu)) { processedMenu.add(menu); fw.write("\nimage::" + viewVal + ".png[" + menu + ", align=\"center\"]"); if (type.equals("general") && !Strings.isNullOrEmpty(doc)) { fw.write("\n" + doc); } firstRow = true; return; } if (Strings.isNullOrEmpty(doc)) { return; } String title = ViewDocExportService.getCellValue(row.getCell(titleIndex)); if (Strings.isNullOrEmpty(title)) { title = ViewDocExportService.getCellValue(row.getCell(4)); } if (Strings.isNullOrEmpty(title)) { title = type.replace("tip", "TIP"); title = type.replace("general", "CAUTION"); title = title.replace("warn", "WARNING"); type = title; } if (type.contains("(")) { type = type.substring(0, type.indexOf("(")).replace("-", "_"); } if (viewDocExportService.fieldTypes.contains(type) || firstRow) { if (firstRow) { fw.write("\n\n[horizontal]"); } firstRow = false; fw.write("\n" + title + ":: " + doc); } else { fw.write("\n" + title + ": " + doc + " +"); } }