List of usage examples for org.apache.poi.ss.usermodel Cell getColumnIndex
int getColumnIndex();
From source file:com.asakusafw.testdriver.excel.ExcelSheetDataModelSource.java
License:Apache License
@Override public DataModelReflection next() throws IOException { while (nextRowNumber <= sheet.getLastRowNum()) { Row row = sheet.getRow(nextRowNumber++); if (row == null) { LOG.warn(MessageFormat.format(Messages.getString("ExcelSheetDataModelSource.warnSkipEmptyRow"), //$NON-NLS-1$ id, nextRowNumber)); continue; }// ww w.ja v a2s. c o m boolean sawFilled = false; ExcelDataDriver driver = new ExcelDataDriver(definition, id); for (Map.Entry<PropertyName, Integer> entry : names.entrySet()) { Cell cell = row.getCell(entry.getValue(), Row.CREATE_NULL_AS_BLANK); int type = cell.getCellType(); if (type == Cell.CELL_TYPE_FORMULA) { evaluateInCell(cell); type = cell.getCellType(); } if (type == Cell.CELL_TYPE_ERROR) { throw new IOException( MessageFormat.format(Messages.getString("ExcelSheetDataModelSource.errorErroneousCell"), //$NON-NLS-1$ id, row.getRowNum() + 1, cell.getColumnIndex() + 1)); } sawFilled |= (type != Cell.CELL_TYPE_BLANK); driver.process(entry.getKey(), cell); } if (sawFilled) { return driver.getReflection(); } else { LOG.warn(MessageFormat.format(Messages.getString("ExcelSheetDataModelSource.warnSkipEmptyRow"), //$NON-NLS-1$ id, row.getRowNum() + 1)); } } return null; }
From source file:com.asakusafw.testdriver.excel.ExcelSheetDataModelSource.java
License:Apache License
private void evaluateInCell(Cell cell) throws IOException { try {/*from www. ja va 2 s . c o m*/ Workbook workbook = cell.getSheet().getWorkbook(); FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator(); formulaEvaluator.evaluateInCell(cell); } catch (RuntimeException e) { throw new IOException(MessageFormat.format( Messages.getString("ExcelSheetDataModelSource.errorFailedToResolveFormulaCell"), //$NON-NLS-1$ id, cell.getRowIndex() + 1, cell.getColumnIndex() + 1), e); } }
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 w w w .j a v a2 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 ww w . ja v a2s. c o m*/ 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 ww w .jav a 2 s .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.assentisk.controller.OrganizationController.java
private ModelAndView importAssets(HttpServletRequest req, HttpServletResponse res) throws Exception { 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);// w w w . j a v a2 s . c o m fout.flush(); fout.close(); try { String Assets = "", category = "", location = "", application = "", business = ""; String initial = "", quantity = "", country = "", state = "", city = "", contact = "", email = ""; List<String> dataStatus = new ArrayList<String>(); int count = 0; int notcount = 0; FileInputStream file = new FileInputStream(new File(fileName)); boolean flag = false; // 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(); System.out.println("check this...hsrow." + hsrow.getRowNum()); if (hsrow.getRowNum() != 0) { if (hsrow instanceof HSSFRow) { try { Assets = hsrow.getCell((short) 0).getStringCellValue(); } catch (Exception e) { Assets = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": AssetName - " + e.getMessage()); } } try { category = hsrow.getCell((short) 1).getStringCellValue(); } catch (Exception e) { category = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Category - " + e.getMessage()); } } try { application = hsrow.getCell((short) 2).getStringCellValue(); } catch (Exception e) { application = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Application - " + e.getMessage()); } } try { business = hsrow.getCell((short) 3).getStringCellValue(); } catch (Exception e) { business = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": BusinessProcess - " + e.getMessage()); } } try { initial = String.valueOf(hsrow.getCell((short) 4).getNumericCellValue()); initial = String.valueOf(initial).split("\\.")[0]; } catch (Exception e) { initial = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": InitialValue - " + e.getMessage()); } } try { quantity = String.valueOf(hsrow.getCell((short) 5).getNumericCellValue()); quantity = String.valueOf(quantity).split("\\.")[0]; } catch (Exception e) { quantity = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Quantity - " + e.getMessage()); } } try { location = hsrow.getCell((short) 6).getStringCellValue(); } catch (Exception e) { location = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location - " + e.getMessage()); } } try { country = hsrow.getCell((short) 7).getStringCellValue(); } catch (Exception e) { country = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Country - " + e.getMessage()); } } try { state = hsrow.getCell((short) 8).getStringCellValue(); } catch (Exception e) { state = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location State - " + e.getMessage()); } } try { city = hsrow.getCell((short) 9).getStringCellValue(); } catch (Exception e) { city = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location City - " + e.getMessage()); } } try { contact = hsrow.getCell((short) 10).getStringCellValue(); } catch (Exception e) { contact = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Contact Name - " + e.getMessage()); } } try { email = hsrow.getCell((short) 11).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(email); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Email- " + e.getMessage()); } } catch (Exception e) { email = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Email - " + e.getMessage()); } } if (!location.equals("")) { location = organizationDao.getLocIdByName(location, currentTime, Integer.parseInt(userId), country, state, city, contact, email); } else { location = "0"; } if (!application.equals("")) { application = organizationDao.getAppByName(application, currentTime, Integer.parseInt(userId)); } else { application = "0"; } if (!business.equals("")) { business = organizationDao.getBusinessByName(business, currentTime, Integer.parseInt(userId)); } else { business = "0"; } if (!category.equals("")) { category = organizationDao.getCategoryByName(category, currentTime, Integer.parseInt(userId)); } else { category = ""; } if (!category.equals("") && !Assets.equals("")) { String duplicate = organizationDao.saveAssetData(Assets, category, location, application, business, "", currentTime, Integer.parseInt(userId), currentTime, Integer.parseInt(userId), "", "", initial, quantity); masterDao.userAuditTrail("assentisk_assets", "1", "Insert", currentTime, Integer.parseInt(userId), ip.getHostAddress()); 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 Asset Name : " + Assets); notcount = notcount + 1; } } } } } else { dataStatus.add("Please import valid Data file"); } if (count > 0) { dataStatus.add("Succesfully inserted Row : " + count); } 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 { Assets = c.toString(); } catch (Exception e) { Assets = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": AssetName - " + e.getMessage()); } } } if (c.getColumnIndex() == 1) { try { category = c.toString(); } catch (Exception e) { category = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Category - " + e.getMessage()); } } } if (c.getColumnIndex() == 2) { try { application = c.toString(); } catch (Exception e) { application = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Application - " + e.getMessage()); } } } if (c.getColumnIndex() == 3) { try { business = c.toString(); } catch (Exception e) { business = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": BusinessProcess - " + e.getMessage()); } } } if (c.getColumnIndex() == 4) { try { initial = c.toString(); } catch (Exception e) { initial = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": InitialValue - " + e.getMessage()); } } } if (c.getColumnIndex() == 5) { try { quantity = c.toString(); } catch (Exception e) { quantity = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Quantity - " + e.getMessage()); } } } if (c.getColumnIndex() == 6) { try { location = c.toString(); } catch (Exception e) { location = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location - " + e.getMessage()); } } } if (c.getColumnIndex() == 7) { try { country = c.toString(); } catch (Exception e) { country = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location Country - " + e.getMessage()); } } } if (c.getColumnIndex() == 8) { try { state = c.toString(); } catch (Exception e) { state = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location State - " + e.getMessage()); } } } if (c.getColumnIndex() == 9) { try { city = c.toString(); } catch (Exception e) { city = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location City - " + e.getMessage()); } } } if (c.getColumnIndex() == 10) { try { contact = c.toString(); } catch (Exception e) { contact = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location Contact Name - " + e.getMessage()); } } } if (c.getColumnIndex() == 11) { try { email = c.toString(); boolean isValid = false; try { // // Create InternetAddress object and // validated the supplied // address which is this case is an // email address. InternetAddress internetAddress = new InternetAddress(email); internetAddress.validate(); isValid = true; } catch (AddressException e) { if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location Email- " + e.getMessage()); } } } catch (Exception e) { email = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location Email- " + e.getMessage()); } } } } if (!location.equals("")) { location = organizationDao.getLocIdByName(location, currentTime, Integer.parseInt(userId), country, state, city, contact, email); } else { location = "0"; } if (!application.equals("")) { application = organizationDao.getAppByName(application, currentTime, Integer.parseInt(userId)); } else { application = "0"; } if (!business.equals("")) { business = organizationDao.getBusinessByName(business, currentTime, Integer.parseInt(userId)); } else { business = "0"; } if (!category.equals("")) { category = organizationDao.getCategoryByName(category, currentTime, Integer.parseInt(userId)); } else { category = ""; } if (!category.equals("") && !Assets.equals("")) { String duplicate = organizationDao.saveAssetData(Assets, category, location, application, business, "", currentTime, Integer.parseInt(userId), currentTime, Integer.parseInt(userId), "", "", initial, quantity); masterDao.userAuditTrail("assentisk_assets", "1", "Insert", currentTime, Integer.parseInt(userId), ip.getHostAddress()); 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 Asset Name : " + Assets); } } } if (count > 0) { System.out.println("Succesfully inserted Row :" + count); dataStatus.add("Succesfully inserted Row : " + count); } if (notcount > 0) { if (notcount == 1) { dataStatus.add(notcount + " Row is not inserted"); } else { 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("assests/assets", map); }
From source file:com.aurel.track.exchange.excel.ExcelFieldMatchBL.java
License:Open Source License
/** * Returns the first row headers (field names) mapped to the column indexes * @return Map<ColumnNumber, ColumnHeader> */// w ww. j a va 2 s .c o m static SortedMap<Integer, String> getFirstRowHeaders(Workbook hSSFWorkbook, Integer sheetID) { SortedMap<Integer, String> firstRowMap = new TreeMap<Integer, String>(); if (hSSFWorkbook == null || sheetID == null) { return firstRowMap; } //first search for duplicate columns Set<String> sameColumnNames = new HashSet<String>(); Set<String> columnNames = new HashSet<String>(); Sheet sheet = hSSFWorkbook.getSheetAt(sheetID.intValue()); Row firstRow = sheet.getRow(0); if (firstRow != null) { for (Cell cell : firstRow) { String columnHeader = ExcelImportBL.getStringCellValue(cell); if (columnHeader != null && !"".equals(columnHeader)) { if (columnNames.contains(columnHeader)) { sameColumnNames.add(columnHeader); } else { columnNames.add(columnHeader); } } } } sheet = hSSFWorkbook.getSheetAt(sheetID.intValue()); firstRow = sheet.getRow(0); if (firstRow != null) { for (Cell cell : firstRow) { String columnHeader = ExcelImportBL.getStringCellValue(cell); if (columnHeader != null && !"".equals(columnHeader)) { if (sameColumnNames.contains(columnHeader)) { //for duplicate columns add also the column index columnHeader += " (" + cell.getColumnIndex() + ")"; } firstRowMap.put(Integer.valueOf(cell.getColumnIndex()), columnHeader); } } } return firstRowMap; }
From source file:com.aurel.track.exchange.excel.ExcelFieldMatchBL.java
License:Open Source License
/** * Returns the first row of a sheet where there is the fields name * @return Map<ColumnNumber, FieldLabelName> */// w ww . j ava2 s .c om static SortedMap<Integer, String> getFirstRowNumericToLetter(Workbook hSSFWorkbook, Integer sheetID) { SortedMap<Integer, String> firstRowMap = new TreeMap<Integer, String>(); if (hSSFWorkbook == null || sheetID == null) { return firstRowMap; } Sheet sheet = hSSFWorkbook.getSheetAt(sheetID.intValue()); Row firstRow = sheet.getRow(0); if (firstRow != null) { for (Cell cell : firstRow) { firstRowMap.put(Integer.valueOf(cell.getColumnIndex()), colNumericToLetter(cell.getColumnIndex())); } } return firstRowMap; }
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// w ww.j av a 2 s . com * * @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.beyondb.io.ExcelControl.java
@Override public Object[][] readTableContent() throws IOException, InvalidFormatException, Exception { try {/* www . j a v a 2 s . com*/ //OPCPackage pkg = OPCPackage.open(file); // InputStream m_InputStream = new FileInputStream(m_File); Sheet sheet = null; // if (!m_InputStream.markSupported()) { // m_InputStream = new PushbackInputStream(m_InputStream, 8); // } // if (POIFSFileSystem.hasPOIFSHeader(m_InputStream)) { // HSSFWorkbook hSSFWorkbook = new HSSFWorkbook(m_InputStream); // sheet = (Sheet)hSSFWorkbook.getSheetAt(0); // // } else if (POIXMLDocument.hasOOXMLHeader(m_InputStream)) { // XSSFWorkbook xSSFWorkbook = new XSSFWorkbook(OPCPackage.open(m_File)); // sheet = (Sheet)xSSFWorkbook.getSheetAt(0); // } // else { // throw new IllegalArgumentException("excel?poi??"); // } sheet = getSheet(); if (sheet != null) { if (sheet.getLastRowNum() == 0) { throw new Exception("Excel"); } //? m_RowNum = sheet.getLastRowNum() + 1; // m_ColumnNum = sheet.getRow(0).getPhysicalNumberOfCells(); m_ColumnNum = sheet.getRow(0).getLastCellNum(); m_TableStr = new Object[m_RowNum][m_ColumnNum]; for (int rindex = 0; rindex < m_RowNum; rindex++) { Row row = sheet.getRow(rindex); for (int cindex = 0; cindex < m_ColumnNum; cindex++) { Cell cell = row.getCell(cindex); if (cell == null) { m_TableStr[rindex][cindex] = ""; } else { String value = ""; switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: // System.out.println(cell.getRichStringCellValue().getString()); value = cell.getRichStringCellValue().getString().replace("\n", ""); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { // System.out.println(cell.getDateCellValue()); value = cell.getDateCellValue().toString(); } else { DecimalFormat df = new DecimalFormat("#"); value = String.valueOf(cell.getNumericCellValue()); double d = cell.getNumericCellValue(); int dInt = (int) d; BigDecimal b1 = new BigDecimal(value); BigDecimal b2 = new BigDecimal(Integer.toString(dInt)); double dPoint = b1.subtract(b2).doubleValue(); if (dPoint == 0) { //? value = df.format(cell.getNumericCellValue()); } } break; case Cell.CELL_TYPE_BOOLEAN: // System.out.println(cell.getBooleanCellValue()); value = cell.getBooleanCellValue() + ""; break; case Cell.CELL_TYPE_FORMULA: // System.out.println(cell.getCellFormula()); value = cell.getCellFormula(); break; case Cell.CELL_TYPE_BLANK: value = ""; default: // System.out.println(); value = ""; } m_TableStr[row.getRowNum()][cell.getColumnIndex()] = value; } } } } } catch (IOException | InvalidFormatException e) { Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "excel??", e); throw e; } catch (Exception ex) { Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "excel??", ex); throw ex; } finally { m_InputStream.close(); } return m_TableStr; }