Example usage for org.apache.poi.ss.usermodel Row getRowNum

List of usage examples for org.apache.poi.ss.usermodel Row getRowNum

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Row getRowNum.

Prototype

int getRowNum();

Source Link

Document

Get row number this row represents

Usage

From source file:com.asakusafw.testdriver.excel.DefaultExcelRuleExtractor.java

License:Apache License

@Override
public ValueConditionKind extractValueCondition(Row row) throws FormatException {
    if (row == null) {
        throw new IllegalArgumentException("row must not be null"); //$NON-NLS-1$
    }/*from   w ww .j  ava 2 s .c  o m*/
    String cell = getStringCell(row, RuleSheetFormat.VALUE_CONDITION);
    ValueConditionKind condition = ValueConditionKind.fromOption(cell);
    if (condition == null) {
        throw new FormatException(
                MessageFormat.format(Messages.getString("DefaultExcelRuleExtractor.errorInvalidValueCondition"), //$NON-NLS-1$
                        RuleSheetFormat.VALUE_CONDITION.getTitle(), cell, row.getRowNum() + 1,
                        RuleSheetFormat.VALUE_CONDITION.getColumnIndex() + 1,
                        Arrays.asList(ValueConditionKind.getOptions())));
    }
    return condition;
}

From source file:com.asakusafw.testdriver.excel.DefaultExcelRuleExtractor.java

License:Apache License

@Override
public NullityConditionKind extractNullityCondition(Row row) throws FormatException {
    if (row == null) {
        throw new IllegalArgumentException("row must not be null"); //$NON-NLS-1$
    }/* w w w .  j  ava 2s.  c o m*/
    String cell = getStringCell(row, RuleSheetFormat.NULLITY_CONDITION);
    NullityConditionKind condition = NullityConditionKind.fromOption(cell);
    if (condition == null) {
        throw new FormatException(MessageFormat.format(
                Messages.getString("DefaultExcelRuleExtractor.errorInvalidNullityCondition"), //$NON-NLS-1$
                RuleSheetFormat.NULLITY_CONDITION.getTitle(), cell, row.getRowNum() + 1,
                RuleSheetFormat.NULLITY_CONDITION.getColumnIndex() + 1,
                Arrays.asList(NullityConditionKind.getOptions())));
    }
    return condition;
}

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  va 2s  .  com
        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.ExcelSheetRuleProvider.java

License:Apache License

private <T> void resolveRow(VerifyRuleBuilder builder, DataModelDefinition<T> definition, VerifyContext context,
        Row row, ExcelRuleExtractor extractor) throws ExcelRuleExtractor.FormatException {
    assert builder != null;
    assert definition != null;
    assert context != null;
    assert row != null;
    assert extractor != null;
    String name = extractor.extractName(row);
    if (name == null) {
        return;//from ww w . j  a  v a  2 s.co m
    }
    VerifyRuleBuilder.Property property;
    try {
        property = builder.property(name);
    } catch (IllegalArgumentException e) {
        throw new ExcelRuleExtractor.FormatException(
                MessageFormat.format(Messages.getString("ExcelSheetRuleProvider.errorMissingProperty"), //$NON-NLS-1$
                        row.getRowNum() + 1),
                e);
    }
    ValueConditionKind value = extractor.extractValueCondition(row);
    NullityConditionKind nullity = extractor.extractNullityCondition(row);
    if (buildNullity(property, value, nullity) == false) {
        return;
    }
    String extraOptions = extractor.extractOptions(row);
    buildValue(property, context, value, extraOptions);
}

From source file:com.asakusafw.testdriver.excel.legacy.LegacyExcelRuleExtractor.java

License:Apache License

@Override
public ValueConditionKind extractValueCondition(Row row) throws FormatException {
    if (row == null) {
        throw new IllegalArgumentException("row must not be null"); //$NON-NLS-1$
    }/*  ww  w .  j  a v a2 s.  c o  m*/
    if (isKeyProperty(row)) {
        return ValueConditionKind.KEY;
    }
    String cell = getStringCell(row, ConditionSheetItem.MATCHING_CONDITION);
    ColumnMatchingCondition condition = ColumnMatchingCondition.getConditonByJapanseName(cell);
    if (condition == null) {
        throw new FormatException(MessageFormat.format(
                Messages.getString("LegacyExcelRuleExtractor.errorInvalidColumnMatchingCondition"), //$NON-NLS-1$
                ConditionSheetItem.MATCHING_CONDITION.getName(), cell, row.getRowNum() + 1,
                ConditionSheetItem.MATCHING_CONDITION.getCol() + 1,
                Arrays.asList(RowMatchingCondition.getJapaneseNames())));
    }
    switch (condition) {
    case EXACT:
        return ValueConditionKind.EQUAL;
    case NONE:
        return ValueConditionKind.ANY;
    case NOW:
        return ValueConditionKind.NOW;
    case PARTIAL:
        return ValueConditionKind.CONTAIN;
    case TODAY:
        return ValueConditionKind.TODAY;
    default:
        throw new AssertionError(condition);
    }
}

From source file:com.asakusafw.testdriver.excel.legacy.LegacyExcelRuleExtractor.java

License:Apache License

@Override
public NullityConditionKind extractNullityCondition(Row row) throws FormatException {
    if (row == null) {
        throw new IllegalArgumentException("row must not be null"); //$NON-NLS-1$
    }/*from   w ww .  ja  v a  2  s .c om*/
    String cell = getStringCell(row, ConditionSheetItem.NULL_VALUE_CONDITION);
    NullValueCondition condition = NullValueCondition.getConditonByJapanseName(cell);
    if (condition == null) {
        throw new FormatException(MessageFormat.format(
                Messages.getString("LegacyExcelRuleExtractor.errorInvalidNullValueCondition"), //$NON-NLS-1$
                ConditionSheetItem.NULL_VALUE_CONDITION.getName(), cell, row.getRowNum() + 1,
                ConditionSheetItem.NULL_VALUE_CONDITION.getCol() + 1,
                Arrays.asList(RowMatchingCondition.getJapaneseNames())));
    }
    switch (condition) {
    case NORMAL:
        return NullityConditionKind.NORMAL;
    case NOT_NULL_IS_NG:
        return NullityConditionKind.DENY_PRESENT;
    case NOT_NULL_IS_OK:
        return NullityConditionKind.ACCEPT_PRESENT;
    case NULL_IS_NG:
        return NullityConditionKind.DENY_ABSENT;
    case NULL_IS_OK:
        return NullityConditionKind.ACCEPT_ABSENT;
    default:
        throw new AssertionError(condition);
    }
}

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. j  a  v a 2s  .  c  om
    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);/*from   w  ww.  ja v  a  2  s .  co  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.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
 * //from   w  w  w  .  j  av  a 2s . c  om
 * @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  ww  w. j a v  a  2  s .c o 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;
}