Example usage for org.apache.poi.ss.usermodel Cell getColumnIndex

List of usage examples for org.apache.poi.ss.usermodel Cell getColumnIndex

Introduction

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

Prototype

int getColumnIndex();

Source Link

Document

Returns column index of this cell

Usage

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

License:Apache License

@Override
public DataModelReflection next() throws IOException {
    while (nextRowNumber <= sheet.getLastRowNum()) {
        Row row = sheet.getRow(nextRowNumber++);
        if (row == null) {
            LOG.warn(MessageFormat.format(Messages.getString("ExcelSheetDataModelSource.warnSkipEmptyRow"), //$NON-NLS-1$
                    id, nextRowNumber));
            continue;
        }// ww w.ja v  a2s.  c  o m
        boolean sawFilled = false;
        ExcelDataDriver driver = new ExcelDataDriver(definition, id);
        for (Map.Entry<PropertyName, Integer> entry : names.entrySet()) {
            Cell cell = row.getCell(entry.getValue(), Row.CREATE_NULL_AS_BLANK);
            int type = cell.getCellType();
            if (type == Cell.CELL_TYPE_FORMULA) {
                evaluateInCell(cell);
                type = cell.getCellType();
            }
            if (type == Cell.CELL_TYPE_ERROR) {
                throw new IOException(
                        MessageFormat.format(Messages.getString("ExcelSheetDataModelSource.errorErroneousCell"), //$NON-NLS-1$
                                id, row.getRowNum() + 1, cell.getColumnIndex() + 1));
            }
            sawFilled |= (type != Cell.CELL_TYPE_BLANK);
            driver.process(entry.getKey(), cell);
        }
        if (sawFilled) {
            return driver.getReflection();
        } else {
            LOG.warn(MessageFormat.format(Messages.getString("ExcelSheetDataModelSource.warnSkipEmptyRow"), //$NON-NLS-1$
                    id, row.getRowNum() + 1));
        }
    }
    return null;
}

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

License:Apache License

private void evaluateInCell(Cell cell) throws IOException {
    try {/*from www. ja va  2 s . c  o m*/
        Workbook workbook = cell.getSheet().getWorkbook();
        FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
        formulaEvaluator.evaluateInCell(cell);
    } catch (RuntimeException e) {
        throw new IOException(MessageFormat.format(
                Messages.getString("ExcelSheetDataModelSource.errorFailedToResolveFormulaCell"), //$NON-NLS-1$
                id, cell.getRowIndex() + 1, cell.getColumnIndex() + 1), e);
    }
}

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

License:Apache License

@Override
public String extractName(Row row) throws FormatException {
    if (row == null) {
        throw new IllegalArgumentException("row must not be null"); //$NON-NLS-1$
    }//from w  w  w .j a  v a2  s .c o m
    // strict checking for cell type
    Cell cell = row.getCell(ConditionSheetItem.COLUMN_NAME.getCol());
    if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
        return null;
    } else if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
        throw new FormatException(MessageFormat.format(
                Messages.getString("LegacyExcelRuleExtractor.errorInvalidNameType"), //$NON-NLS-1$
                ConditionSheetItem.COLUMN_NAME.getName(), cell.getRowIndex() + 1, cell.getColumnIndex() + 1));
    }
    String name = cell.getStringCellValue();
    if (name.isEmpty()) {
        return null;
    }
    return name.toLowerCase();
}

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

License:Apache License

private String getStringCell(Row row, ConditionSheetItem item) throws FormatException {
    assert row != null;
    assert item != null;
    Cell cell = row.getCell(item.getCol());
    if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
        return ""; //$NON-NLS-1$
    } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
        return cell.getStringCellValue();
    }/*from  ww  w .  ja v  a2s. c  o  m*/
    throw new FormatException(
            MessageFormat.format(Messages.getString("LegacyExcelRuleExtractor.errorInvalidStringCell"), //$NON-NLS-1$
                    item.getName(), cell.getRowIndex() + 1, cell.getColumnIndex() + 1));
}

From source file:com.assentisk.controller.OrganizationController.java

private ModelAndView importEmploye(HttpServletRequest req, HttpServletResponse res) throws Exception {
    System.out.println("hiiii heree.......");
    MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) req;
    MultipartFile multipartFile = multipartRequest.getFile("myexcel");
    String fileName = multipartFile.getOriginalFilename();
    map = new ModelMap();
    String name = req.getParameter("fileName");
    int status = 0;
    java.util.Date dt = new java.util.Date();
    java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    String currentTime = sdf.format(dt);

    InetAddress ip = InetAddress.getLocalHost();
    String userId = (String) req.getSession().getAttribute("UserRecordID");

    byte[] b = multipartFile.getBytes();
    FileOutputStream fout = new FileOutputStream(new File(fileName));
    fout.write(b);/*from  ww w  .jav  a  2 s  .c o  m*/
    fout.flush();
    fout.close();
    try {
        String DepartmentID = "", LocationID = "", EmpName = "", Email1 = "", Email2 = "", address = "";
        String city = "", zip = "", phone1 = "", locCoun = "", locstate = "", loccity = "", locContact = "",
                locEmail = "";
        String phone2 = "", divisions = "", email2 = "", username = "", reporting = "", photo = "";
        int isLicense = 0;
        int isactive = 1;
        int count = 0;
        int notcount = 0;
        FileInputStream file = new FileInputStream(new File(fileName));
        boolean flag = false;
        String[] data = null;
        List<String> dataStatus = new ArrayList<String>();
        int val = 0;
        //Reading .xls files

        if (fileName.toLowerCase().endsWith(".xls")) {
            // Get the workbook instance for XLS file
            HSSFWorkbook workbook = new HSSFWorkbook(file);
            // Get first sheet from the workbook
            HSSFSheet sheet = workbook.getSheetAt(0);
            sheet.setColumnHidden((short) 14, false);
            // Iterate through each rows from first sheet
            Iterator<Row> rowIterator = sheet.rowIterator();

            if (rowIterator.hasNext()) {

                while (rowIterator.hasNext()) {
                    short cellIndex = 0;
                    HSSFRow hsrow = (HSSFRow) rowIterator.next();
                    if (hsrow.getRowNum() != 0) {
                        if (hsrow instanceof HSSFRow) {
                            try {
                                EmpName = hsrow.getCell((short) 0).getStringCellValue();

                            } catch (Exception e) {
                                EmpName = "";
                                System.out.println("Wrong Data in  Row " + hsrow.getRowNum() + ": EmpName - "
                                        + e.getMessage());
                                dataStatus.add("Wrong Data in  Row " + hsrow.getRowNum() + ": EmpName - "
                                        + e.getMessage());
                                val++;
                            }
                            try {
                                DepartmentID = hsrow.getCell((short) 1).getStringCellValue();
                            } catch (Exception e) {
                                DepartmentID = "";
                                System.out.println("Wrong Data in  Row " + hsrow.getRowNum()
                                        + ":  Department - " + e.getMessage());
                                dataStatus.add("Wrong Data in  Row " + hsrow.getRowNum() + ": Department - "
                                        + e.getMessage());
                                val++;
                            }
                            try {
                                LocationID = hsrow.getCell((short) 2).getStringCellValue();
                            } catch (Exception e) {
                                LocationID = "";
                                System.out.println("Wrong Data in  Row " + hsrow.getRowNum() + ": Location -"
                                        + e.getMessage());
                                dataStatus.add("Wrong Data in  Row " + hsrow.getRowNum() + ": Location - "
                                        + e.getMessage());
                                val++;

                            }

                            try {
                                locCoun = hsrow.getCell((short) 3).getStringCellValue();
                            } catch (Exception e) {
                                locCoun = "";
                                System.out.println("Wrong Data in  Row " + hsrow.getRowNum()
                                        + ": location Country - " + e.getMessage());
                                dataStatus.add("Wrong Data in  Row " + hsrow.getRowNum()
                                        + ": Location Country- " + e.getMessage());
                                val++;
                            }

                            try {
                                locstate = hsrow.getCell((short) 4).getStringCellValue();
                            } catch (Exception e) {
                                locstate = "";
                                System.out.println("Wrong Data in  Row " + hsrow.getRowNum()
                                        + ": location State - " + e.getMessage());
                                dataStatus.add("Wrong Data in  Row " + hsrow.getRowNum() + ": Location State- "
                                        + e.getMessage());
                                val++;
                            }

                            try {
                                loccity = hsrow.getCell((short) 5).getStringCellValue();
                            } catch (Exception e) {
                                loccity = "";
                                System.out.println("Wrong Data in  Row " + hsrow.getRowNum()
                                        + ": location City -" + e.getMessage());
                                dataStatus.add("Wrong Data in  Row " + hsrow.getRowNum() + ": Location City- "
                                        + e.getMessage());
                                val++;
                            }
                            try {
                                locContact = hsrow.getCell((short) 6).getStringCellValue();
                            } catch (Exception e) {
                                locContact = "";
                                System.out.println("Wrong Data in  Row " + hsrow.getRowNum()
                                        + ": location Contact Name -" + e.getMessage());
                                dataStatus.add("Wrong Data in  Row " + hsrow.getRowNum()
                                        + ": Location Contact Name- " + e.getMessage());
                                val++;
                            }

                            try {
                                locEmail = hsrow.getCell((short) 7).getStringCellValue();
                                boolean isValid = false;
                                try {
                                    //
                                    // Create InternetAddress object and validated the supplied
                                    // address which is this case is an email address.
                                    InternetAddress internetAddress = new InternetAddress(locEmail);
                                    internetAddress.validate();
                                    isValid = true;
                                } catch (AddressException e) {
                                    dataStatus.add("Wrong Data in  Row " + hsrow.getRowNum()
                                            + ": Location Email- " + e.getMessage());
                                }
                            } catch (Exception e) {
                                locEmail = "";
                                System.out.println("Wrong Data in  Row " + hsrow.getRowNum()
                                        + ": location Email -" + e.getMessage());
                                dataStatus.add("Wrong Data in  Row " + hsrow.getRowNum() + ": Location Email- "
                                        + e.getMessage());
                                val++;
                            }

                            try {
                                divisions = hsrow.getCell((short) 8).getStringCellValue();
                            } catch (Exception e) {
                                divisions = "";
                                System.out.println("Wrong Data in  Row " + hsrow.getRowNum() + ": Divisions - "
                                        + e.getMessage());
                                dataStatus.add("Wrong Data in  Row " + hsrow.getRowNum() + ": Divisions - "
                                        + e.getMessage());
                                val++;
                            }
                            try {
                                address = hsrow.getCell((short) 9).getStringCellValue();
                            } catch (Exception e) {
                                address = "";
                                System.out.println("Wrong Data in  Row " + hsrow.getRowNum() + ": address - "
                                        + e.getMessage());
                                dataStatus.add("Wrong Data in  Row " + hsrow.getRowNum() + ": address - "
                                        + e.getMessage());
                                val++;
                            }
                            try {
                                city = hsrow.getCell((short) 10).getStringCellValue();
                            } catch (Exception e) {
                                city = "";
                                System.out.println("Wrong Data in  Row " + hsrow.getRowNum() + ": city - "
                                        + e.getMessage());
                                dataStatus.add("Wrong Data in  Row " + hsrow.getRowNum() + ": city - "
                                        + e.getMessage());
                                val++;
                            }

                            try {

                                zip = String.valueOf(hsrow.getCell((short) 11).getNumericCellValue());
                                zip = String.valueOf(zip).split("\\.")[0];

                            } catch (Exception e) {
                                zip = "";
                                System.out.println("Wrong Data in  Row " + hsrow.getRowNum() + ": zip - "
                                        + e.getMessage());
                                dataStatus.add("Wrong Data in  Row " + hsrow.getRowNum() + ": zip - "
                                        + e.getMessage());
                                val++;
                            }

                            try {

                                DataFormatter formatter = new DataFormatter();

                                phone1 = String.valueOf(hsrow.getCell((short) 12).getNumericCellValue());
                                String df2 = formatter.formatCellValue(hsrow.getCell((short) 12));
                                phone1 = df2;
                                phone1 = String.valueOf(phone1).split("\\.")[0];

                            } catch (Exception e) {
                                phone1 = "";
                                System.out.println("Wrong Data in  Row " + hsrow.getRowNum() + ": phone1 - "
                                        + e.getMessage());
                                dataStatus.add("Wrong Data in  Row " + hsrow.getRowNum() + ": phone1 - "
                                        + e.getMessage());
                                val++;
                            }
                            try {
                                DataFormatter formatter = new DataFormatter();
                                phone2 = String.valueOf(hsrow.getCell((short) 13).getNumericCellValue());
                                String df2 = formatter.formatCellValue(hsrow.getCell((short) 13));
                                phone2 = df2;
                                System.out.println("check phone1" + phone2);
                                phone2 = String.valueOf(phone2).split("\\.")[0];

                            } catch (Exception e) {
                                phone2 = "";
                                System.out.println("Wrong Data in  Row " + hsrow.getRowNum() + ": phone2 -"
                                        + e.getMessage());
                                dataStatus.add("Wrong Data in  Row " + hsrow.getRowNum() + ": phone2 - "
                                        + e.getMessage());
                                val++;
                            }
                            try {
                                boolean isValid = false;
                                Email1 = hsrow.getCell((short) 14).getStringCellValue();
                                try {
                                    InternetAddress internetAddress = new InternetAddress(Email1);
                                    internetAddress.validate();
                                    isValid = true;
                                } catch (AddressException e) {
                                    dataStatus.add("Wrong Data in  Row " + hsrow.getRowNum() + ": Email1 - "
                                            + e.getMessage());
                                }
                            } catch (Exception e) {
                                Email1 = "";
                                System.out.println("Wrong Data in  Row " + hsrow.getRowNum() + ": Email1 - "
                                        + e.getMessage());
                                dataStatus.add("Wrong Data in  Row " + hsrow.getRowNum() + ": Email1 - "
                                        + e.getMessage());
                                val++;
                            }
                            try {

                                boolean isValid = false;
                                Email2 = hsrow.getCell((short) 15).getStringCellValue();
                                try {
                                    InternetAddress internetAddress = new InternetAddress(Email2);
                                    internetAddress.validate();
                                    isValid = true;
                                } catch (AddressException e) {
                                    dataStatus.add("Wrong Data in  Row " + hsrow.getRowNum() + ": Email2 - "
                                            + e.getMessage());
                                }
                            } catch (Exception e) {
                                Email2 = "";
                                System.out.println("Wrong Data in  Row " + hsrow.getRowNum() + ": Email2 - "
                                        + e.getMessage());
                                dataStatus.add("Wrong Data in  Row " + hsrow.getRowNum() + ": Email2 - "
                                        + e.getMessage());
                                val++;
                            }

                            int userdata = 0;

                            String groupdata = "";

                            if (username.equals("")) {
                                userdata = 0;
                                reporting = "0";
                                groupdata = "0";
                            }

                            if (!LocationID.equals("")) {
                                LocationID = organizationDao.getLocIdByName(LocationID, currentTime,
                                        Integer.parseInt(userId), locCoun, locstate, loccity, locContact,
                                        locEmail);
                                if (LocationID.equals("0")) {
                                    LocationID = "";
                                }
                            }
                            if (!DepartmentID.equals("")) {
                                DepartmentID = organizationDao.getDeparmentIdByName(DepartmentID, currentTime,
                                        Integer.parseInt(userId));
                                if (DepartmentID.equals("0")) {
                                    DepartmentID = "";
                                }
                            }
                            if (!divisions.equals("")) {

                                divisions = organizationDao.getDivisionByName(divisions, currentTime,
                                        Integer.parseInt(userId));
                                if (divisions.equals("0")) {
                                    divisions = "";
                                }
                            }

                            if (!EmpName.equals("") && !DepartmentID.equals("") && !divisions.equals("")
                                    && !LocationID.equals("")) {
                                String duplicate = "";
                                try {
                                    duplicate = organizationDao.saveEmployeedata(0, DepartmentID, LocationID,
                                            EmpName, Email1, reporting, groupdata, currentTime, currentTime,
                                            Integer.parseInt(userId), currentTime, Integer.parseInt(userId),
                                            status, address, city, "", "", zip, photo, phone1, phone2,
                                            divisions, Email2);
                                    masterDao.userAuditTrail("assentisk_employees", "1", "add", currentTime,
                                            Integer.parseInt(userId), ip.getHostAddress());
                                } catch (Exception e) {
                                    //dataStatus.add("Error on the data : "+hsrow.getRowNum());

                                    req.setAttribute("message", "Invalid Data File");
                                    return new ModelAndView("organization/employees", map);
                                }

                                if (duplicate.equals("true")) {
                                    notcount = notcount + 1;
                                    dataStatus.add("Duplicate Row : " + hsrow.getRowNum());
                                } else {
                                    count = count + 1;
                                }
                            } else {
                                System.out.println(
                                        "Error Row : " + hsrow.getRowNum() + "Not Inserted Row  : " + EmpName);
                                notcount = notcount + 1;
                            }
                        }
                    }

                }
            } else {
                dataStatus.add("Please import valid Data file");
            }
            if (count > 0) {
                dataStatus.add("Succesfully inserted Row : " + count);
                val++;
            }
            if (notcount > 0) {
                if (notcount == 1) {
                    dataStatus.add(notcount + " Row is not inserted");
                } else {
                    dataStatus.add(notcount + " Rows are not inserted");
                }

            }

        } //Reading .xlsx files
        else if (fileName.toLowerCase().endsWith(".xlsx")) {

            // Get the workbook instance for XLS file
            XSSFWorkbook wBook = new XSSFWorkbook(file);
            // Get first sheet from the workbook
            XSSFSheet sheet = wBook.getSheetAt(0);
            sheet.setColumnHidden((short) 14, false);
            // Iterate through each rows from first sheet
            Iterator<Row> rowIterator = sheet.rowIterator();

            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                Iterator<Cell> cite = row.cellIterator();
                if (row.getRowNum() != 0) {
                    while (cite.hasNext()) {
                        Cell c = cite.next();
                        if (c.getColumnIndex() == 0) {
                            try {
                                EmpName = c.toString();
                            } catch (Exception e) {
                                EmpName = "";
                                dataStatus.add("Wrong Data in  Row " + rowIterator.hasNext() + ": EmpName - "
                                        + e.getMessage());
                            }
                        }
                        if (c.getColumnIndex() == 1) {
                            try {
                                DepartmentID = c.toString();
                            } catch (Exception e) {
                                DepartmentID = "";
                                dataStatus.add("Wrong Data in  Row " + rowIterator.hasNext()
                                        + ":  Department - " + e.getMessage());
                            }
                        }
                        if (c.getColumnIndex() == 2) {
                            try {
                                LocationID = c.toString();
                            } catch (Exception e) {
                                LocationID = "";
                                dataStatus.add("Wrong Data in  Row " + rowIterator.hasNext() + ": Location -"
                                        + e.getMessage());
                            }
                        }
                        if (c.getColumnIndex() == 3) {
                            try {
                                locCoun = c.toString();
                            } catch (Exception e) {
                                locCoun = "";
                                dataStatus.add("Wrong Data in  Row " + rowIterator.hasNext()
                                        + ": location Country - " + e.getMessage());

                            }
                        }
                        if (c.getColumnIndex() == 4) {
                            try {
                                locstate = c.toString();
                            } catch (Exception e) {
                                locstate = "";
                                dataStatus.add("Wrong Data in  Row " + rowIterator.hasNext()
                                        + ": location State - " + e.getMessage());
                            }
                        }
                        if (c.getColumnIndex() == 5) {
                            try {
                                loccity = c.toString();
                            } catch (Exception e) {
                                loccity = "";
                                dataStatus.add("Wrong Data in  Row " + rowIterator.hasNext()
                                        + ": location City -" + e.getMessage());

                            }
                        }
                        if (c.getColumnIndex() == 6) {
                            try {
                                locContact = c.toString();
                            } catch (Exception e) {
                                locContact = "";
                                dataStatus.add("Wrong Data in  Row " + rowIterator.hasNext()
                                        + ": location Contact Name -" + e.getMessage());
                            }
                        }
                        if (c.getColumnIndex() == 7) {
                            try {
                                locEmail = c.toString();
                                boolean isValid = false;
                                try {
                                    InternetAddress internetAddress = new InternetAddress(locEmail);
                                    internetAddress.validate();
                                    isValid = true;
                                } catch (AddressException e) {
                                    dataStatus.add("Wrong Data in  Row " + rowIterator.hasNext()
                                            + ": locEmail - " + e.getMessage());
                                }

                            } catch (Exception e) {
                                locEmail = "";
                                dataStatus.add("Wrong Data in  Row " + rowIterator.hasNext()
                                        + ": location Email -" + e.getMessage());
                            }
                        }
                        if (c.getColumnIndex() == 8) {
                            try {
                                divisions = c.toString();
                            } catch (Exception e) {
                                divisions = "";
                                dataStatus.add("Wrong Data in  Row " + rowIterator.hasNext() + ": Divisions - "
                                        + e.getMessage());
                            }
                        }
                        if (c.getColumnIndex() == 9) {
                            try {
                                address = c.toString();
                            } catch (Exception e) {
                                address = "";
                                dataStatus.add("Wrong Data in  Row " + rowIterator.hasNext() + ": address - "
                                        + e.getMessage());
                            }
                        }
                        if (c.getColumnIndex() == 10) {
                            try {
                                city = c.toString();
                            } catch (Exception e) {
                                city = "";
                                dataStatus.add("Wrong Data in  Row " + rowIterator.hasNext() + ": city - "
                                        + e.getMessage());
                            }
                        }
                        if (c.getColumnIndex() == 11) {
                            try {
                                zip = c.toString();
                            } catch (Exception e) {
                                zip = "";
                                dataStatus.add("Wrong Data in  Row " + rowIterator.hasNext() + ": zip - "
                                        + e.getMessage());
                            }
                        }
                        if (c.getColumnIndex() == 12) {
                            try {
                                DataFormatter formatter = new DataFormatter();
                                phone1 = c.toString();
                                String df2 = formatter.formatCellValue(row.getCell((short) 12));
                                phone1 = df2;
                            } catch (Exception e) {
                                phone1 = "";
                                dataStatus.add("Wrong Data in  Row " + rowIterator.hasNext() + ": phone1 - "
                                        + e.getMessage());
                            }
                        }
                        if (c.getColumnIndex() == 13) {
                            try {
                                DataFormatter formatter = new DataFormatter();
                                phone2 = c.toString();
                                String df2 = formatter.formatCellValue(row.getCell((short) 12));
                                phone2 = df2;
                            } catch (Exception e) {
                                phone2 = "";
                                dataStatus.add("Wrong Data in  Row " + rowIterator.hasNext() + ": phone2 -"
                                        + e.getMessage());
                            }
                        }
                        if (c.getColumnIndex() == 3) {
                            try {
                                Email1 = c.toString();
                                boolean isValid = false;
                                try {
                                    InternetAddress internetAddress = new InternetAddress(Email1);
                                    internetAddress.validate();
                                    isValid = true;
                                } catch (AddressException e) {
                                    dataStatus.add("Wrong Data in  Row " + rowIterator.hasNext() + ": Email1 - "
                                            + e.getMessage());
                                }
                            } catch (Exception e) {
                                Email1 = "";
                                dataStatus.add("Wrong Data in  Row " + rowIterator.hasNext() + ": Email1 - "
                                        + e.getMessage());
                            }
                        }
                        if (c.getColumnIndex() == 10) {
                            try {
                                Email2 = c.toString();
                                boolean isValid = false;
                                try {
                                    InternetAddress internetAddress = new InternetAddress(Email2);
                                    internetAddress.validate();
                                    isValid = true;
                                } catch (AddressException e) {
                                    dataStatus.add("Wrong Data in  Row " + rowIterator.hasNext() + ": Email2 - "
                                            + e.getMessage());
                                }
                            } catch (Exception e) {
                                Email2 = "";
                                dataStatus.add("Wrong Data in  Row " + rowIterator.hasNext() + ": Email2 - "
                                        + e.getMessage());
                            }
                        }

                    }

                    int userdata = 0;

                    String groupdata = "";

                    if (username.equals("")) {
                        userdata = 0;
                        reporting = "0";
                        groupdata = "0";
                    }

                    if (!LocationID.equals("")) {
                        LocationID = organizationDao.getLocIdByName(LocationID, currentTime,
                                Integer.parseInt(userId), locCoun, locstate, loccity, locContact, locEmail);
                        if (LocationID.equals("0")) {
                            LocationID = "";
                        }
                    }
                    if (!DepartmentID.equals("")) {
                        DepartmentID = organizationDao.getDeparmentIdByName(DepartmentID, currentTime,
                                Integer.parseInt(userId));
                        if (DepartmentID.equals("0")) {
                            DepartmentID = "";
                        }
                    }
                    if (!divisions.equals("")) {

                        divisions = organizationDao.getDivisionByName(divisions, currentTime,
                                Integer.parseInt(userId));
                        if (divisions.equals("0")) {
                            divisions = "";
                        }
                    }

                    if (!EmpName.equals("") && !DepartmentID.equals("") && !divisions.equals("")
                            && !LocationID.equals("")) {
                        String duplicate = "";
                        try {
                            duplicate = organizationDao.saveEmployeedata(0, DepartmentID, LocationID, EmpName,
                                    Email1, reporting, groupdata, currentTime, currentTime,
                                    Integer.parseInt(userId), currentTime, Integer.parseInt(userId), status,
                                    address, city, "", "", zip, photo, phone1, phone2, divisions, Email2);
                            masterDao.userAuditTrail("assentisk_employees", "1", "add", currentTime,
                                    Integer.parseInt(userId), ip.getHostAddress());

                        } catch (Exception e) {
                            //dataStatus.add("Error on the data : "+hsrow.getRowNum());
                            req.setAttribute("message", "Invalid Data File");

                            return new ModelAndView("organization/employees", map);
                        }

                        if (duplicate.equals("true")) {
                            notcount = notcount + 1;
                            dataStatus.add("Duplicate Row : " + row.getRowNum());
                        } else {
                            count = count + 1;
                        }
                    } else {
                        System.out.println(
                                "Error Row : " + row.getRowNum() + "Not Inserted Row EmpName : " + EmpName);
                        notcount = notcount + 1;
                    }
                }
            }

            if (count > 0) {
                System.out.println("Succesfully inserted Row :" + count);
                dataStatus.add("Succesfully inserted Row : " + count);
                val++;
            }
            if (notcount > 0) {
                dataStatus.add(notcount + " Rows are not inserted");
            }

            file.close();

            File f = new File(fileName);

            if (f.exists()) {
                f.delete();
            }
        }

        if (dataStatus.size() > 0) {
            map.addObject("dataStatus", dataStatus);
            map.addObject("datasize", "true");

        } else {
            map.addObject("datasize", "false");
        }
        file.close();
        File f = new File(fileName);
        if (f.exists()) {
            f.delete();
        }

    } catch (FileNotFoundException e) {
        e.printStackTrace();
        req.setAttribute("message", "Invalid data file");
    } catch (IOException e) {
        e.printStackTrace();
        req.setAttribute("message", "Invalid data file");
    } catch (Exception e) {
        e.printStackTrace();
        req.setAttribute("message", "Invalid data file");
    }
    return new ModelAndView("organization/employees", map);
}

From source file:com.assentisk.controller.OrganizationController.java

private ModelAndView importAssets(HttpServletRequest req, HttpServletResponse res) throws Exception {
    MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) req;
    MultipartFile multipartFile = multipartRequest.getFile("myexcel");
    String fileName = multipartFile.getOriginalFilename();
    map = new ModelMap();
    String name = req.getParameter("fileName");
    int status = 0;
    java.util.Date dt = new java.util.Date();
    java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    String currentTime = sdf.format(dt);

    InetAddress ip = InetAddress.getLocalHost();
    String userId = (String) req.getSession().getAttribute("UserRecordID");

    byte[] b = multipartFile.getBytes();
    FileOutputStream fout = new FileOutputStream(new File(fileName));
    fout.write(b);// w w  w .  j a  v  a2  s  .  c  o m
    fout.flush();
    fout.close();
    try {
        String Assets = "", category = "", location = "", application = "", business = "";
        String initial = "", quantity = "", country = "", state = "", city = "", contact = "", email = "";
        List<String> dataStatus = new ArrayList<String>();
        int count = 0;
        int notcount = 0;

        FileInputStream file = new FileInputStream(new File(fileName));
        boolean flag = false;

        // Reading .xls files

        if (fileName.toLowerCase().endsWith(".xls")) {
            // Get the workbook instance for XLS file
            HSSFWorkbook workbook = new HSSFWorkbook(file);
            // Get first sheet from the workbook
            HSSFSheet sheet = workbook.getSheetAt(0);
            sheet.setColumnHidden((short) 14, false);
            // Iterate through each rows from first sheet            
            Iterator<Row> rowIterator = sheet.rowIterator();
            if (rowIterator.hasNext()) {

                while (rowIterator.hasNext()) {
                    short cellIndex = 0;
                    HSSFRow hsrow = (HSSFRow) rowIterator.next();
                    System.out.println("check this...hsrow." + hsrow.getRowNum());
                    if (hsrow.getRowNum() != 0) {
                        if (hsrow instanceof HSSFRow) {
                            try {
                                Assets = hsrow.getCell((short) 0).getStringCellValue();
                            } catch (Exception e) {
                                Assets = "";
                                System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage());

                                if (e.getMessage() != null) {
                                    dataStatus.add("Wrong Data in  Row " + hsrow.getRowNum() + ": AssetName - "
                                            + e.getMessage());
                                }
                            }
                            try {
                                category = hsrow.getCell((short) 1).getStringCellValue();
                            } catch (Exception e) {
                                category = "";
                                System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage());
                                if (e.getMessage() != null) {
                                    dataStatus.add("Wrong Data in  Row " + hsrow.getRowNum() + ": Category - "
                                            + e.getMessage());
                                }
                            }

                            try {
                                application = hsrow.getCell((short) 2).getStringCellValue();
                            } catch (Exception e) {
                                application = "";
                                System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage());
                                if (e.getMessage() != null) {
                                    dataStatus.add("Wrong Data in  Row " + hsrow.getRowNum()
                                            + ": Application - " + e.getMessage());
                                }
                            }
                            try {
                                business = hsrow.getCell((short) 3).getStringCellValue();
                            } catch (Exception e) {
                                business = "";
                                System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage());
                                if (e.getMessage() != null) {
                                    dataStatus.add("Wrong Data in  Row " + hsrow.getRowNum()
                                            + ": BusinessProcess - " + e.getMessage());
                                }

                            }
                            try {
                                initial = String.valueOf(hsrow.getCell((short) 4).getNumericCellValue());
                                initial = String.valueOf(initial).split("\\.")[0];
                            } catch (Exception e) {
                                initial = "";
                                System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage());
                                if (e.getMessage() != null) {
                                    dataStatus.add("Wrong Data in  Row " + hsrow.getRowNum()
                                            + ": InitialValue - " + e.getMessage());
                                }

                            }
                            try {

                                quantity = String.valueOf(hsrow.getCell((short) 5).getNumericCellValue());
                                quantity = String.valueOf(quantity).split("\\.")[0];

                            } catch (Exception e) {
                                quantity = "";
                                System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage());
                                if (e.getMessage() != null) {
                                    dataStatus.add("Wrong Data in  Row " + hsrow.getRowNum() + ": Quantity - "
                                            + e.getMessage());
                                }

                            }

                            try {
                                location = hsrow.getCell((short) 6).getStringCellValue();
                            } catch (Exception e) {
                                location = "";
                                System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage());
                                if (e.getMessage() != null) {
                                    dataStatus.add("Wrong Data in  Row " + hsrow.getRowNum() + ": Location - "
                                            + e.getMessage());
                                }

                            }
                            try {
                                country = hsrow.getCell((short) 7).getStringCellValue();
                            } catch (Exception e) {
                                country = "";
                                System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage());
                                if (e.getMessage() != null) {
                                    dataStatus.add("Wrong Data in  Row " + hsrow.getRowNum()
                                            + ": Location Country - " + e.getMessage());
                                }

                            }
                            try {
                                state = hsrow.getCell((short) 8).getStringCellValue();
                            } catch (Exception e) {
                                state = "";
                                System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage());
                                if (e.getMessage() != null) {
                                    dataStatus.add("Wrong Data in  Row " + hsrow.getRowNum()
                                            + ": Location State - " + e.getMessage());
                                }

                            }
                            try {
                                city = hsrow.getCell((short) 9).getStringCellValue();
                            } catch (Exception e) {
                                city = "";
                                System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage());
                                if (e.getMessage() != null) {
                                    dataStatus.add("Wrong Data in  Row " + hsrow.getRowNum()
                                            + ": Location City - " + e.getMessage());
                                }

                            }
                            try {
                                contact = hsrow.getCell((short) 10).getStringCellValue();
                            } catch (Exception e) {
                                contact = "";
                                System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage());
                                if (e.getMessage() != null) {
                                    dataStatus.add("Wrong Data in  Row " + hsrow.getRowNum()
                                            + ": Location Contact Name - " + e.getMessage());
                                }

                            }
                            try {
                                email = hsrow.getCell((short) 11).getStringCellValue();
                                boolean isValid = false;
                                try {
                                    //
                                    // Create InternetAddress object and
                                    // validated the supplied
                                    // address which is this case is an email
                                    // address.
                                    InternetAddress internetAddress = new InternetAddress(email);
                                    internetAddress.validate();
                                    isValid = true;
                                } catch (AddressException e) {

                                    dataStatus.add("Wrong Data in  Row " + hsrow.getRowNum()
                                            + ": Location Email- " + e.getMessage());
                                }
                            } catch (Exception e) {
                                email = "";
                                System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage());
                                if (e.getMessage() != null) {
                                    dataStatus.add("Wrong Data in  Row " + hsrow.getRowNum()
                                            + ": Location Email - " + e.getMessage());
                                }

                            }

                            if (!location.equals("")) {

                                location = organizationDao.getLocIdByName(location, currentTime,
                                        Integer.parseInt(userId), country, state, city, contact, email);
                            } else {
                                location = "0";
                            }

                            if (!application.equals("")) {
                                application = organizationDao.getAppByName(application, currentTime,
                                        Integer.parseInt(userId));
                            } else {
                                application = "0";
                            }

                            if (!business.equals("")) {
                                business = organizationDao.getBusinessByName(business, currentTime,
                                        Integer.parseInt(userId));
                            } else {
                                business = "0";
                            }
                            if (!category.equals("")) {
                                category = organizationDao.getCategoryByName(category, currentTime,
                                        Integer.parseInt(userId));
                            } else {
                                category = "";
                            }
                            if (!category.equals("") && !Assets.equals("")) {
                                String duplicate = organizationDao.saveAssetData(Assets, category, location,
                                        application, business, "", currentTime, Integer.parseInt(userId),
                                        currentTime, Integer.parseInt(userId), "", "", initial, quantity);
                                masterDao.userAuditTrail("assentisk_assets", "1", "Insert", currentTime,
                                        Integer.parseInt(userId), ip.getHostAddress());
                                if (duplicate.equals("true")) {
                                    notcount = notcount + 1;
                                    dataStatus.add("Duplicate Row : " + hsrow.getRowNum());
                                } else {
                                    count = count + 1;

                                }
                            } else {
                                System.out.println("Error Row : " + hsrow.getRowNum()
                                        + "Not Inserted Row Asset Name : " + Assets);
                                notcount = notcount + 1;
                            }

                        }
                    }

                }
            } else {
                dataStatus.add("Please import valid Data file");
            }
            if (count > 0) {
                dataStatus.add("Succesfully inserted Row : " + count);

            }
            if (notcount > 0) {
                if (notcount == 1) {
                    dataStatus.add(notcount + " Row is not inserted");
                } else {
                    dataStatus.add(notcount + " Rows are not inserted");
                }

            }

        } // Reading .xlsx files
        else if (fileName.toLowerCase().endsWith(".xlsx")) {

            // Get the workbook instance for XLS file
            XSSFWorkbook wBook = new XSSFWorkbook(file);
            // Get first sheet from the workbook
            XSSFSheet sheet = wBook.getSheetAt(0);
            sheet.setColumnHidden((short) 14, false);
            // Iterate through each rows from first sheet
            Iterator<Row> rowIterator = sheet.rowIterator();

            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                Iterator<Cell> cite = row.cellIterator();
                if (row.getRowNum() != 0) {
                    while (cite.hasNext()) {
                        Cell c = cite.next();
                        if (c.getColumnIndex() == 0) {
                            try {
                                Assets = c.toString();
                            } catch (Exception e) {
                                Assets = "";
                                System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage());
                                if (e.getMessage() != null) {
                                    dataStatus.add("Wrong Data in  Row " + row.getRowNum() + ": AssetName - "
                                            + e.getMessage());
                                }

                            }
                        }
                        if (c.getColumnIndex() == 1) {
                            try {
                                category = c.toString();
                            } catch (Exception e) {
                                category = "";
                                System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage());
                                if (e.getMessage() != null) {
                                    dataStatus.add("Wrong Data in  Row " + row.getRowNum() + ": Category - "
                                            + e.getMessage());
                                }
                            }
                        }
                        if (c.getColumnIndex() == 2) {
                            try {
                                application = c.toString();
                            } catch (Exception e) {
                                application = "";
                                System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage());
                                if (e.getMessage() != null) {
                                    dataStatus.add("Wrong Data in  Row " + row.getRowNum() + ": Application - "
                                            + e.getMessage());
                                }

                            }
                        }
                        if (c.getColumnIndex() == 3) {
                            try {
                                business = c.toString();
                            } catch (Exception e) {
                                business = "";
                                System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage());
                                if (e.getMessage() != null) {
                                    dataStatus.add("Wrong Data in  Row " + row.getRowNum()
                                            + ": BusinessProcess - " + e.getMessage());
                                }

                            }
                        }
                        if (c.getColumnIndex() == 4) {
                            try {
                                initial = c.toString();
                            } catch (Exception e) {
                                initial = "";
                                System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage());
                                if (e.getMessage() != null) {
                                    dataStatus.add("Wrong Data in  Row " + row.getRowNum() + ": InitialValue - "
                                            + e.getMessage());
                                }

                            }
                        }
                        if (c.getColumnIndex() == 5) {
                            try {
                                quantity = c.toString();
                            } catch (Exception e) {
                                quantity = "";
                                System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage());
                                if (e.getMessage() != null) {
                                    dataStatus.add("Wrong Data in  Row " + row.getRowNum() + ": Quantity - "
                                            + e.getMessage());
                                }

                            }
                        }
                        if (c.getColumnIndex() == 6) {
                            try {
                                location = c.toString();
                            } catch (Exception e) {
                                location = "";
                                System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage());
                                if (e.getMessage() != null) {
                                    dataStatus.add("Wrong Data in  Row " + row.getRowNum() + ": Location - "
                                            + e.getMessage());
                                }

                            }
                        }
                        if (c.getColumnIndex() == 7) {
                            try {
                                country = c.toString();
                            } catch (Exception e) {
                                country = "";
                                System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage());
                                if (e.getMessage() != null) {
                                    dataStatus.add("Wrong Data in  Row " + row.getRowNum()
                                            + ": Location Country - " + e.getMessage());
                                }

                            }
                        }
                        if (c.getColumnIndex() == 8) {
                            try {
                                state = c.toString();
                            } catch (Exception e) {
                                state = "";
                                System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage());
                                if (e.getMessage() != null) {
                                    dataStatus.add("Wrong Data in  Row " + row.getRowNum()
                                            + ": Location State - " + e.getMessage());
                                }

                            }
                        }
                        if (c.getColumnIndex() == 9) {
                            try {
                                city = c.toString();
                            } catch (Exception e) {
                                city = "";
                                System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage());
                                if (e.getMessage() != null) {
                                    dataStatus.add("Wrong Data in  Row " + row.getRowNum()
                                            + ": Location City - " + e.getMessage());
                                }

                            }
                        }
                        if (c.getColumnIndex() == 10) {
                            try {
                                contact = c.toString();
                            } catch (Exception e) {
                                contact = "";
                                System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage());
                                if (e.getMessage() != null) {
                                    dataStatus.add("Wrong Data in  Row " + row.getRowNum()
                                            + ": Location Contact Name - " + e.getMessage());
                                }

                            }
                        }
                        if (c.getColumnIndex() == 11) {
                            try {
                                email = c.toString();
                                boolean isValid = false;
                                try {
                                    //
                                    // Create InternetAddress object and
                                    // validated the supplied
                                    // address which is this case is an
                                    // email address.
                                    InternetAddress internetAddress = new InternetAddress(email);
                                    internetAddress.validate();
                                    isValid = true;
                                } catch (AddressException e) {
                                    if (e.getMessage() != null) {
                                        dataStatus.add("Wrong Data in  Row " + row.getRowNum()
                                                + ": Location Email- " + e.getMessage());
                                    }
                                }
                            } catch (Exception e) {
                                email = "";
                                System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage());
                                if (e.getMessage() != null) {
                                    dataStatus.add("Wrong Data in  Row " + row.getRowNum()
                                            + ": Location Email- " + e.getMessage());
                                }

                            }
                        }
                    }

                    if (!location.equals("")) {
                        location = organizationDao.getLocIdByName(location, currentTime,
                                Integer.parseInt(userId), country, state, city, contact, email);
                    } else {
                        location = "0";
                    }

                    if (!application.equals("")) {
                        application = organizationDao.getAppByName(application, currentTime,
                                Integer.parseInt(userId));
                    } else {
                        application = "0";
                    }

                    if (!business.equals("")) {
                        business = organizationDao.getBusinessByName(business, currentTime,
                                Integer.parseInt(userId));
                    } else {
                        business = "0";
                    }
                    if (!category.equals("")) {
                        category = organizationDao.getCategoryByName(category, currentTime,
                                Integer.parseInt(userId));
                    } else {
                        category = "";
                    }
                    if (!category.equals("") && !Assets.equals("")) {
                        String duplicate = organizationDao.saveAssetData(Assets, category, location,
                                application, business, "", currentTime, Integer.parseInt(userId), currentTime,
                                Integer.parseInt(userId), "", "", initial, quantity);
                        masterDao.userAuditTrail("assentisk_assets", "1", "Insert", currentTime,
                                Integer.parseInt(userId), ip.getHostAddress());
                        if (duplicate.equals("true")) {
                            notcount = notcount + 1;
                            dataStatus.add("Duplicate Row : " + row.getRowNum());
                        } else {
                            count = count + 1;

                        }
                    } else {
                        System.out.println(
                                "Error Row : " + row.getRowNum() + "Not Inserted Row Asset Name : " + Assets);

                    }
                }
            }

            if (count > 0) {
                System.out.println("Succesfully inserted Row :" + count);
                dataStatus.add("Succesfully inserted Row : " + count);

            }
            if (notcount > 0) {
                if (notcount == 1) {
                    dataStatus.add(notcount + " Row is not inserted");
                } else {
                    dataStatus.add(notcount + " Rows are not inserted");
                }
            }

            file.close();

            File f = new File(fileName);

            if (f.exists()) {
                f.delete();
            }
        }

        if (dataStatus.size() > 0) {
            map.addObject("dataStatus", dataStatus);
            map.addObject("datasize", "true");

        } else {
            map.addObject("datasize", "false");
        }
        file.close();
        File f = new File(fileName);
        if (f.exists()) {
            f.delete();
        }
    } catch (FileNotFoundException e) {
        e.printStackTrace();
        req.setAttribute("message", "Invalid data file");
    } catch (IOException e) {
        e.printStackTrace();
        req.setAttribute("message", "Invalid data file");
    } catch (Exception e) {
        e.printStackTrace();
        req.setAttribute("message", "Invalid data file");
    }
    return new ModelAndView("assests/assets", map);
}

From source file:com.aurel.track.exchange.excel.ExcelFieldMatchBL.java

License:Open Source License

/**
 * Returns the first row headers (field names) mapped to the column indexes
 * @return  Map<ColumnNumber, ColumnHeader>
 *///  w  ww. j a  va  2  s .c o m
static SortedMap<Integer, String> getFirstRowHeaders(Workbook hSSFWorkbook, Integer sheetID) {
    SortedMap<Integer, String> firstRowMap = new TreeMap<Integer, String>();
    if (hSSFWorkbook == null || sheetID == null) {
        return firstRowMap;
    }
    //first search for duplicate columns
    Set<String> sameColumnNames = new HashSet<String>();
    Set<String> columnNames = new HashSet<String>();
    Sheet sheet = hSSFWorkbook.getSheetAt(sheetID.intValue());
    Row firstRow = sheet.getRow(0);
    if (firstRow != null) {
        for (Cell cell : firstRow) {
            String columnHeader = ExcelImportBL.getStringCellValue(cell);
            if (columnHeader != null && !"".equals(columnHeader)) {
                if (columnNames.contains(columnHeader)) {
                    sameColumnNames.add(columnHeader);
                } else {
                    columnNames.add(columnHeader);
                }
            }
        }
    }
    sheet = hSSFWorkbook.getSheetAt(sheetID.intValue());
    firstRow = sheet.getRow(0);
    if (firstRow != null) {
        for (Cell cell : firstRow) {
            String columnHeader = ExcelImportBL.getStringCellValue(cell);
            if (columnHeader != null && !"".equals(columnHeader)) {
                if (sameColumnNames.contains(columnHeader)) {
                    //for duplicate columns add also the column index
                    columnHeader += " (" + cell.getColumnIndex() + ")";
                }
                firstRowMap.put(Integer.valueOf(cell.getColumnIndex()), columnHeader);
            }
        }
    }
    return firstRowMap;
}

From source file:com.aurel.track.exchange.excel.ExcelFieldMatchBL.java

License:Open Source License

/**
 * Returns the first row of a sheet where there is the fields name
 * @return  Map<ColumnNumber, FieldLabelName>
 *///  w  ww . j ava2 s  .c  om
static SortedMap<Integer, String> getFirstRowNumericToLetter(Workbook hSSFWorkbook, Integer sheetID) {
    SortedMap<Integer, String> firstRowMap = new TreeMap<Integer, String>();
    if (hSSFWorkbook == null || sheetID == null) {
        return firstRowMap;
    }
    Sheet sheet = hSSFWorkbook.getSheetAt(sheetID.intValue());
    Row firstRow = sheet.getRow(0);
    if (firstRow != null) {
        for (Cell cell : firstRow) {
            firstRowMap.put(Integer.valueOf(cell.getColumnIndex()), colNumericToLetter(cell.getColumnIndex()));
        }
    }
    return firstRowMap;
}

From source file:com.aurel.track.exchange.excel.ExcelImportBL.java

License:Open Source License

/**
 * Get the workItems list and validate if all the fields of the excel sheet
 * are correct//  w  ww.j  av a 2 s .  com
 * 
 * @param workbook
 * @param selectedSheet
 * @param personID
 * @param locale
 * @param columnIndexToFieldIDMap
 * @param fieldIDToColumnIndexMap
 * @param lastSavedIdentifierFieldIDIsSet
 * @param defaultValuesMap
 * @param invalidValueHandlingMap
 * @param gridErrorsMap
 * @param rowErrorsMap
 * @return
 */
static SortedMap<Integer, TWorkItemBean> getAndValidateGridData(Workbook workbook, Integer selectedSheet,
        Integer personID, Locale locale, Map<Integer, Integer> columnIndexToFieldIDMap,
        Map<Integer, Integer> fieldIDToColumnIndexMap, Set<Integer> lastSavedIdentifierFieldIDIsSet,
        Map<Integer, Integer> defaultValuesMap, Map<Integer, Integer> invalidValueHandlingMap,
        Map<Integer, Map<Integer, List<Integer>>> rowNoToPseudoFieldsOriginal,
        Map<Integer, Map<Integer, List<Integer>>> rowNoToPseudoFieldsExcel,
        Map<Integer, SortedMap<Integer, SortedMap<String, ErrorData>>> gridErrorsMap,
        Map<Integer, SortedSet<Integer>> rowErrorsMap, Map<Integer, SortedSet<Integer>> requiredFieldErrorsMap,
        Map<Integer, Integer> rowToParentRow) {
    SortedMap<Integer, TWorkItemBean> workItemBeansMap = new TreeMap<Integer, TWorkItemBean>();
    Sheet sheet = workbook.getSheetAt(selectedSheet.intValue());
    // get the column indexes for project and issueType
    Integer projectColumn = fieldIDToColumnIndexMap.get(SystemFields.INTEGER_PROJECT);
    Integer issueTypeColumn = fieldIDToColumnIndexMap.get(SystemFields.INTEGER_ISSUETYPE);
    // Maps to spare additional database accesses for default values
    Map<Integer, String> defaultShowValuesMap = new HashMap<Integer, String>();
    Map<Integer, String> defaultLocalizedFieldLabels = new HashMap<Integer, String>();
    Integer originalProject = null;
    Integer originalIssueType = null;
    Set<Integer> mandatoryIdentifierFields = ExcelFieldMatchBL.getMandatoryIdentifierFields();
    Map<Integer, Map<String, ILabelBean>> systemLookups = loadBaseLookups(personID, locale);
    Map<String, ILabelBean> projectLookups = systemLookups.get(SystemFields.INTEGER_PROJECT);
    Map<Integer, Map<Integer, Map<String, ILabelBean>>> projectSpecificLookups = null;
    if (projectLookups != null) {
        projectSpecificLookups = loadProjectLookups(GeneralUtils
                .createIntegerListFromBeanList(GeneralUtils.createListFromCollection(projectLookups.values())));
    }
    boolean projectSpecificIDsActive = ApplicationBean.getInstance().getSiteBean().getProjectSpecificIDsOn();
    Map<Integer, TProjectBean> projectBeansMap = new HashMap<Integer, TProjectBean>();
    if (projectSpecificIDsActive) {
        List<TProjectBean> projectBeans = ProjectBL.loadUsedProjectsFlat(personID);
        if (projectBeans != null) {
            for (TProjectBean projectBean : projectBeans) {
                Integer projectID = projectBean.getObjectID();
                projectBeansMap.put(projectID, projectBean);
                String label = projectBean.getLabel();
                String projectPrefix = projectBean.getPrefix();
                if (projectPrefix == null || "".equals(projectPrefix)) {
                    LOGGER.info("The project " + label + " with ID " + projectID
                            + " has no prefix, consquently project specific item numbers might not be recognized");
                }
            }
        }
    }
    /**
     * Process the rows only to gather the projects to issueTypes to get the
     * roles and restrictions once for all issues
     */
    Map<Integer, Set<Integer>> projectToIssueTypesMap = new HashMap<Integer, Set<Integer>>();
    for (Row row : sheet) {
        int rowNum = row.getRowNum();
        if (rowNum == 0) {
            // only the data rows are processed (the header row is not
            // important now)
            continue;
        }
        SerializableBeanAllowedContext serializableBeanAllowedContext = new SerializableBeanAllowedContext();
        serializableBeanAllowedContext.setPersonID(personID);
        serializableBeanAllowedContext.setNew(true);
        // get the project and issueType first because the other fields
        // could depend on these issueTypes
        // process the project column
        Integer projectID = null;
        if (projectColumn != null) {
            try {
                projectID = (Integer) getAttributeValue(row.getCell(projectColumn),
                        SystemFields.INTEGER_PROJECT, null, serializableBeanAllowedContext, locale,
                        invalidValueHandlingMap, systemLookups, projectSpecificLookups);
            } catch (Exception e) {
            }
        }
        if (projectID == null) {
            // no project column exists on the sheet: take the default value
            // which is
            // surely specified, otherwise it would fail at
            // validateRequiredColumns()
            projectID = defaultValuesMap.get(SystemFields.INTEGER_PROJECT);
        }
        if (projectID != null) {
            serializableBeanAllowedContext.setProjectID(projectID);
        }
        // process the issueType column
        Integer issueTypeID = null;
        if (issueTypeColumn != null) {
            try {
                issueTypeID = (Integer) getAttributeValue(row.getCell(issueTypeColumn),
                        SystemFields.INTEGER_ISSUETYPE, null, serializableBeanAllowedContext, locale,
                        invalidValueHandlingMap, systemLookups, projectSpecificLookups);
            } catch (Exception e) {
            }
        }
        if (issueTypeID == null) {
            // no issue type column exists on the sheet: take the default
            // value which is
            // surely specified, otherwise it would fail at
            // validateRequiredColumns()
            issueTypeID = defaultValuesMap.get(SystemFields.INTEGER_ISSUETYPE);
        }
        if (projectID != null) {
            Set<Integer> issueTypes = projectToIssueTypesMap.get(projectID);
            if (issueTypes == null) {
                issueTypes = new HashSet<Integer>();
                projectToIssueTypesMap.put(projectID, issueTypes);
            }
            if (issueTypeID != null) {
                issueTypes.add(issueTypeID);
            }
        }
    }
    Map<Integer, Map<Integer, Map<Integer, TFieldConfigBean>>> projectsToIssueTypesToFieldConfigsMapForBottomUpFields = null;
    Map<Integer, Map<Integer, Map<String, Object>>> projectsIssueTypesFieldSettingsMapForBottomUpFields = null;
    Map<Integer, Map<Integer, Map<Integer, Integer>>> fieldRestrictions = AccessBeans
            .getFieldRestrictions(personID, projectToIssueTypesMap, null, true);
    Set<Integer> possibleBottomUpFields = FieldRuntimeBL.getPossibleBottomUpFields();
    for (Iterator<Integer> iterator = possibleBottomUpFields.iterator(); iterator.hasNext();) {
        if (!fieldIDToColumnIndexMap.containsKey(iterator.next())) {
            // remove possible bottom up field if not mapped
            iterator.remove();
        }
        if (!possibleBottomUpFields.isEmpty()) {
            // at least one bottom up date was mapped
            projectsToIssueTypesToFieldConfigsMapForBottomUpFields = FieldRuntimeBL
                    .loadFieldConfigsInContextsAndTargetProjectAndIssueType(projectToIssueTypesMap,
                            possibleBottomUpFields, locale, null, null);
            projectsIssueTypesFieldSettingsMapForBottomUpFields = FieldRuntimeBL
                    .getFieldSettingsForFieldConfigs(projectsToIssueTypesToFieldConfigsMapForBottomUpFields);
        }
    }

    /**
     * now process the rows in detail one by one
     */
    Stack<Integer> parentStack = new Stack<Integer>();
    Map<Integer, Integer> rowToIndent = new HashMap<Integer, Integer>();
    for (Row row : sheet) {
        int rowNum = row.getRowNum();
        if (rowNum == 0) {
            // only the data rows are processed (the header row is not
            // important now)
            continue;
        }
        boolean excelValueFound = false;
        // whether the project column is mapped and excel value if found for
        // project
        boolean mappedProject = false;
        SerializableBeanAllowedContext serializableBeanAllowedContext = new SerializableBeanAllowedContext();
        serializableBeanAllowedContext.setPersonID(personID);
        serializableBeanAllowedContext.setNew(true);
        // get the project and issueType first because the other fields
        // could depend on these issueTypes
        // process the project column
        Integer projectID = null;
        if (projectColumn != null) {
            try {
                projectID = (Integer) getAttributeValue(row.getCell(projectColumn),
                        SystemFields.INTEGER_PROJECT, null, serializableBeanAllowedContext, locale,
                        invalidValueHandlingMap, systemLookups, projectSpecificLookups);
                if (projectID != null) {
                    mappedProject = true;
                    excelValueFound = true;
                }
            } catch (ExcelImportNotExistingCellValueException e) {
                addGridError(gridErrorsMap, NOT_EXISTING_ERRORS, rowNum,
                        ExcelFieldMatchBL.colNumericToLetter(projectColumn), SystemFields.INTEGER_PROJECT,
                        e.getMessage());
            } catch (ExcelImportNotAllowedCellValueException e) {
                addGridError(gridErrorsMap, NOT_ALLOWED_ERRORS, rowNum,
                        ExcelFieldMatchBL.colNumericToLetter(projectColumn), SystemFields.INTEGER_PROJECT,
                        e.getMessage());
            } catch (ExcelImportInvalidCellValueException e) {
                addGridError(gridErrorsMap, INVALID_ERRORS, rowNum,
                        ExcelFieldMatchBL.colNumericToLetter(projectColumn), SystemFields.INTEGER_PROJECT,
                        e.getMessage());
            }
        }
        if (projectID == null) {
            // no project column exists on the sheet: take the default value
            // which is
            // surely specified, otherwise it would fail at
            // validateRequiredColumns()
            projectID = defaultValuesMap.get(SystemFields.INTEGER_PROJECT);
        }
        if (projectID != null) {
            serializableBeanAllowedContext.setProjectID(projectID);
        }
        // process the issueType column
        Integer issueTypeID = null;
        if (issueTypeColumn != null) {
            try {
                issueTypeID = (Integer) getAttributeValue(row.getCell(issueTypeColumn),
                        SystemFields.INTEGER_ISSUETYPE, null, serializableBeanAllowedContext, locale,
                        invalidValueHandlingMap, systemLookups, projectSpecificLookups);
                if (issueTypeID != null) {
                    excelValueFound = true;
                }
            } catch (ExcelImportNotExistingCellValueException e) {
                addGridError(gridErrorsMap, NOT_EXISTING_ERRORS, rowNum,
                        ExcelFieldMatchBL.colNumericToLetter(issueTypeColumn), SystemFields.INTEGER_ISSUETYPE,
                        e.getMessage());
            } catch (ExcelImportNotAllowedCellValueException e) {
                addGridError(gridErrorsMap, NOT_ALLOWED_ERRORS, rowNum,
                        ExcelFieldMatchBL.colNumericToLetter(issueTypeColumn), SystemFields.INTEGER_ISSUETYPE,
                        e.getMessage());
            } catch (ExcelImportInvalidCellValueException e) {
                addGridError(gridErrorsMap, INVALID_ERRORS, rowNum,
                        ExcelFieldMatchBL.colNumericToLetter(issueTypeColumn), SystemFields.INTEGER_ISSUETYPE,
                        e.getMessage());
            }
        }
        if (issueTypeID == null) {
            // no issue type column exists on the sheet: take the default
            // value which is
            // surely specified, otherwise it would fail at
            // validateRequiredColumns()
            issueTypeID = defaultValuesMap.get(SystemFields.INTEGER_ISSUETYPE);
        }
        if (issueTypeID != null) {
            serializableBeanAllowedContext.setIssueTypeID(issueTypeID);
        }
        /*
         * gather the values for the identifier fields and try to get an
         * existing workItem by these fields
         */
        Map<Integer, Object> identifierFieldValues = new HashMap<Integer, Object>();
        if (lastSavedIdentifierFieldIDIsSet != null && !lastSavedIdentifierFieldIDIsSet.isEmpty()) {
            for (Integer fieldID : lastSavedIdentifierFieldIDIsSet) {
                Integer attributeFieldID = fieldID;
                if (SystemFields.INTEGER_ISSUENO.equals(fieldID) && projectSpecificIDsActive) {
                    attributeFieldID = SystemFields.INTEGER_PROJECT_SPECIFIC_ISSUENO;
                }
                Object attributeValue = null;
                Integer columnIndex = null;
                try {
                    columnIndex = fieldIDToColumnIndexMap.get(fieldID);
                    attributeValue = getAttributeValue(row.getCell(columnIndex), attributeFieldID, null,
                            serializableBeanAllowedContext, locale, invalidValueHandlingMap, systemLookups,
                            projectSpecificLookups);
                    if (attributeValue != null) {
                        identifierFieldValues.put(fieldID, attributeValue);
                        excelValueFound = true;
                    }
                } catch (ExcelImportNotExistingCellValueException e) {
                    if (!SystemFields.INTEGER_PROJECT.equals(fieldID)
                            && !SystemFields.INTEGER_ISSUETYPE.equals(fieldID)) {
                        // if project or issueType are set as identifier
                        // fields and
                        // have grid error they should be already collected
                        // in gridErrorsMap
                        addGridError(gridErrorsMap, NOT_EXISTING_ERRORS, rowNum,
                                ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, e.getMessage());
                    }
                } catch (ExcelImportNotAllowedCellValueException e) {
                    if (!SystemFields.INTEGER_PROJECT.equals(fieldID)
                            && !SystemFields.INTEGER_ISSUETYPE.equals(fieldID)) {
                        // if project or issueType are set as identifier
                        // fields and
                        // have grid error they should be already collected
                        // in gridErrorsMap
                        addGridError(gridErrorsMap, NOT_ALLOWED_ERRORS, rowNum,
                                ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, e.getMessage());
                    }
                } catch (ExcelImportInvalidCellValueException e) {
                    if (!SystemFields.INTEGER_PROJECT.equals(fieldID)
                            && !SystemFields.INTEGER_ISSUETYPE.equals(fieldID)) {
                        // if project or issueType are set as identifier
                        // fields and
                        // have grid error they should be already collected
                        // in gridErrorsMap
                        addGridError(gridErrorsMap, INVALID_ERRORS, rowNum,
                                ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, e.getMessage());
                    }
                }
            }
        }
        // always initialize the next workItem to null
        TWorkItemBean workItemBean = null;
        boolean itemIsNew = false;
        if (!identifierFieldValues.isEmpty()) {
            if (identifierFieldValues.get(SystemFields.INTEGER_ISSUENO) != null) {
                // is issueNo field mapped?
                if (projectSpecificIDsActive) {
                    // get by project specific itemID
                    String projectSpecificID = null;
                    try {
                        projectSpecificID = (String) identifierFieldValues.get(SystemFields.INTEGER_ISSUENO);
                    } catch (Exception e) {
                    }
                    if (projectSpecificID != null) {
                        // it should be trimmed because in excel the child
                        // issues are indented
                        workItemBean = ItemBL.loadWorkItemByProjectSpecificID(projectID, mappedProject,
                                projectBeansMap, projectSpecificID.trim());
                        if (workItemBean != null && LOGGER.isDebugEnabled()) {
                            LOGGER.debug("WorkItem " + projectSpecificID + " from row " + rowNum
                                    + " found by projectSpecificID");
                        }
                    }
                } else {
                    // get by "global" workItemID
                    Integer workItemID = null;
                    try {
                        workItemID = (Integer) identifierFieldValues.get(SystemFields.INTEGER_ISSUENO);
                    } catch (Exception e) {
                    }
                    if (workItemID != null) {
                        workItemBean = ItemBL.loadWorkItemSystemAttributes(workItemID);
                    }
                    if (workItemBean != null && LOGGER.isDebugEnabled()) {
                        LOGGER.debug("WorkItem " + workItemID + " from row " + rowNum + " found by workItemID");
                    }
                }
                if (workItemBean == null) {
                    // the issueNo field is set as identifier and the
                    // corresponding issue does't exist, report as error
                    addGridError(gridErrorsMap, WORKITEM_NOTEXIST_ERRORS, rowNum,
                            ExcelFieldMatchBL.colNumericToLetter(
                                    fieldIDToColumnIndexMap.get(SystemFields.INTEGER_ISSUENO)),
                            SystemFields.INTEGER_ISSUENO,
                            identifierFieldValues.get(SystemFields.INTEGER_ISSUENO).toString());
                    continue;
                }
            }
            if (workItemBean == null) {
                // workItem was not found by issueNo
                // (issueNo field was not mapped or issueNo value is missing
                // from excel or
                // the issue's project is not accessible if
                // projectSpecificIDsActive)
                // try with user defined identifier fields
                try {
                    workItemBean = ItemBL.loadWorkItemSystemAttributes(identifierFieldValues);
                } catch (ExcelImportNotUniqueIdentifiersException e) {
                    addRowError(rowErrorsMap, WORKITEM_MORE_THAN_ONE_EXIST, rowNum);
                    continue;
                }
                if (workItemBean != null && LOGGER.isDebugEnabled()) {
                    LOGGER.debug("WorkItem from row " + rowNum + " found by user defined identifier fields");
                }
            }
            if (workItemBean != null) {
                // existing workItem
                originalProject = workItemBean.getProjectID();
                originalIssueType = workItemBean.getListTypeID();
                // is it editable by the current person?
                if (!AccessBeans.isAllowedToChange(workItemBean, personID)) {
                    addRowError(rowErrorsMap, WORKITEM_NO_EDIT_RIGHT, rowNum);
                    continue;
                }
                // load also the custom attributes because when the workItem
                // will be updated
                // the custom attributes will also be compared to the
                // original value
                ItemBL.loadWorkItemCustomAttributes(workItemBean);
                serializableBeanAllowedContext.setWorkItemBeanOriginal(workItemBean);
                serializableBeanAllowedContext.setNew(false);
                // LOGGER.debug("WorkItem " + workItemBean.getObjectID() +
                // " from row " + rowNum + " found");
            }
        }
        boolean missingRequiredFound = false;
        if (workItemBean == null) {
            // not existing found by identifier fields, create a new one
            workItemBean = new TWorkItemBean();
            if (identifierFieldValues != null) {
                // preset the new workItem with the processed identifier
                // values
                for (Map.Entry<Integer, Object> identifierEntry : identifierFieldValues.entrySet()) {
                    workItemBean.setAttribute(identifierEntry.getKey(), identifierEntry.getValue());
                }
            }
            itemIsNew = true;
            LOGGER.debug("WorkItem from row " + rowNum + " not found. A new one will be created.");
        }
        if (projectID != null) {
            workItemBean.setAttribute(SystemFields.INTEGER_PROJECT, null, projectID);
        } else {
            if (itemIsNew) {
                // project column not mapped
                addRowError(requiredFieldErrorsMap, SystemFields.INTEGER_PROJECT, rowNum);
                missingRequiredFound = true;
            }
        }
        if (issueTypeID != null) {
            workItemBean.setAttribute(SystemFields.INTEGER_ISSUETYPE, null, issueTypeID);
        } else {
            if (itemIsNew) {
                // project column not mapped
                addRowError(requiredFieldErrorsMap, SystemFields.INTEGER_ISSUETYPE, rowNum);
                missingRequiredFound = true;
            }
        }
        if (missingRequiredFound) {
            continue;
        }
        Map<Integer, Integer> restrictedFields = null;
        projectID = workItemBean.getProjectID();
        issueTypeID = workItemBean.getListTypeID();
        if (projectID != null && issueTypeID != null) {
            Map<Integer, Map<Integer, Integer>> issueTypeRestrictions = fieldRestrictions.get(projectID);
            if (issueTypeRestrictions != null) {
                restrictedFields = issueTypeRestrictions.get(issueTypeID);
            }
            if (restrictedFields == null) {
                // no project or issue type mapped get the restriction now
                restrictedFields = AccessBeans.getFieldRestrictions(personID, projectID, issueTypeID, true);
                issueTypeRestrictions = new HashMap<Integer, Map<Integer, Integer>>();
                issueTypeRestrictions.put(issueTypeID, restrictedFields);
                fieldRestrictions.put(projectID, issueTypeRestrictions);
            }
            // new values exist
            if (originalProject != null && originalIssueType != null) {
                // workItem existed
                if (!projectID.equals(originalProject) || !issueTypeID.equals(originalIssueType)) {
                    if (!AccessBeans.isAllowedToChange(workItemBean, personID)) {
                        // move not allowed
                        addRowError(rowErrorsMap, WORKITEM_NO_EDIT_RIGHT, rowNum);
                        continue;
                    }
                }
            } else {
                // new workItem
                if (!AccessBeans.isAllowedToCreate(personID, projectID, issueTypeID)) {
                    // create not allowed
                    addRowError(rowErrorsMap, WORKITEM_NO_CREATE_RIGHT, rowNum);
                    continue;
                }
            }
        }
        // process the remaining cells
        Map<Integer, Integer> rowNoToIndentLevel = new HashMap<Integer, Integer>();
        for (Cell cell : row) {
            boolean attributeChanged = false;
            int columnIndex = cell.getColumnIndex();
            Integer fieldID = columnIndexToFieldIDMap.get(columnIndex);
            Integer fieldForRestriction = fieldID;
            if (fieldID == null) {
                // LOGGER.debug("No mapping found for column " +
                // columnIndex);
                continue;
            }
            if (fieldID.equals(SystemFields.INTEGER_PROJECT) || fieldID.equals(SystemFields.INTEGER_ISSUETYPE)
                    || identifierFieldValues.containsKey(fieldID)
                    || mandatoryIdentifierFields.contains(fieldID)) {
                // these values are already read
                continue;
            }
            if (fieldID.intValue() < 0) {
                // pseudo field: now only watchers
                if (fieldID.intValue() == TReportLayoutBean.PSEUDO_COLUMNS.INFORMANT_LIST
                        || fieldID.intValue() == TReportLayoutBean.PSEUDO_COLUMNS.CONSULTANT_LIST) {
                    fieldForRestriction = FieldsRestrictionsToRoleBL.PSEUDO_COLUMNS.WATCHERS;
                    String watcherValue = getStringCellValue(cell);
                    if (watcherValue == null || "".equals(watcherValue.trim())) {
                        continue;
                    }
                    Map<Integer, List<Integer>> watcherMapOriginal = rowNoToPseudoFieldsOriginal.get(rowNum);
                    if (watcherMapOriginal == null) {
                        watcherMapOriginal = new HashMap<Integer, List<Integer>>();
                        rowNoToPseudoFieldsOriginal.put(rowNum, watcherMapOriginal);
                    }
                    List<Integer> watcherListOriginal = null;
                    TWorkItemBean workItemBeanOriginal = serializableBeanAllowedContext
                            .getWorkItemBeanOriginal();
                    if (workItemBeanOriginal != null) {
                        if (fieldID.intValue() == TReportLayoutBean.PSEUDO_COLUMNS.INFORMANT_LIST) {
                            watcherListOriginal = GeneralUtils.createIntegerListFromBeanList(
                                    PersonBL.getDirectInformants(workItemBeanOriginal.getObjectID()));
                        } else {
                            watcherListOriginal = GeneralUtils.createIntegerListFromBeanList(
                                    PersonBL.getDirectConsultants(workItemBeanOriginal.getObjectID()));
                        }
                        watcherMapOriginal.put(fieldID, watcherListOriginal);
                    }
                    List<Integer> watcherListExcel = new LinkedList<Integer>();
                    String[] watcherNames = watcherValue
                            .split("\\" + ConsultedInformedLoaderBL.WATCHER_SPLITTER_VALUES_STRING);
                    if (watcherNames != null) {
                        Map<Integer, List<Integer>> watcherMapExcel = rowNoToPseudoFieldsExcel.get(rowNum);
                        if (watcherMapExcel == null) {
                            watcherMapExcel = new HashMap<Integer, List<Integer>>();
                            rowNoToPseudoFieldsExcel.put(rowNum, watcherMapExcel);
                        }
                        watcherMapExcel.put(fieldID, watcherListExcel);
                        for (int i = 0; i < watcherNames.length; i++) {
                            String watcherName = watcherNames[i];
                            Integer objectID = null;
                            try {
                                objectID = getWatcherValue(watcherName, fieldID, systemLookups,
                                        watcherListOriginal, serializableBeanAllowedContext, locale);
                            } catch (ExcelImportNotExistingCellValueException e) {
                                addGridError(gridErrorsMap, NOT_EXISTING_ERRORS, rowNum,
                                        ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID,
                                        e.getMessage());
                            } catch (ExcelImportNotAllowedCellValueException e) {
                                addGridError(gridErrorsMap, NOT_ALLOWED_ERRORS, rowNum,
                                        ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID,
                                        e.getMessage());
                            }
                            if (objectID != null) {
                                watcherListExcel.add(objectID);
                                excelValueFound = true;
                            }
                        }
                    }
                    attributeChanged = ConsInfBL.watcherChanged(watcherListOriginal, watcherListExcel);
                } else {
                    if (fieldID.intValue() == ExcelFieldMatchBL.LOCAL_PARENT_PSEUDO_COLUMN) {
                        // local parent - child hierarchy (for new items)
                        Integer pseudoHierarchyColumn = fieldIDToColumnIndexMap
                                .get(ExcelFieldMatchBL.LOCAL_PARENT_PSEUDO_COLUMN);
                        if (pseudoHierarchyColumn != null) {
                            String hierarchyColumn = getStringCellValue(row.getCell(pseudoHierarchyColumn));
                            if (hierarchyColumn != null && hierarchyColumn.length() > 0) {
                                int previousIndent = 0;
                                if (!parentStack.isEmpty()) {
                                    Integer previousRow = parentStack.peek();
                                    if (rowToIndent.get(previousRow) != null) {
                                        previousIndent = rowToIndent.get(previousRow).intValue();
                                    }
                                }
                                int actualIndent = hierarchyColumn.length();
                                rowToIndent.put(rowNum, actualIndent);
                                rowNoToIndentLevel.put(rowNum, actualIndent);
                                if (previousIndent == actualIndent) {
                                    // sibling: same parent as the sibling's
                                    // parent
                                    if (!parentStack.isEmpty()) {
                                        // remove the sibling from stack
                                        parentStack.pop();
                                        if (!parentStack.isEmpty()) {
                                            // if the stack is still not
                                            // empty then the peek is teh
                                            // parent
                                            Integer parentRow = parentStack.peek();
                                            rowToParentRow.put(rowNum, parentRow);
                                        }
                                    }
                                } else {
                                    if (actualIndent > previousIndent) {
                                        // child of the previous row
                                        if (actualIndent - previousIndent > 1) {
                                            // jump more than one in deep is
                                            // error
                                            addGridError(gridErrorsMap, INCONSISTENT_HIERARCHY_ERRORS, rowNum,
                                                    ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID,
                                                    hierarchyColumn);
                                        }
                                        if (!parentStack.isEmpty()) {
                                            // add previous row as parent
                                            Integer parentRow = parentStack.peek();
                                            rowToParentRow.put(rowNum, parentRow);
                                        }
                                    } else {
                                        // new hierarchy: nothing to do with
                                        // the previous row
                                        int difference = previousIndent - actualIndent;
                                        for (int i = 0; i <= difference; i++) {
                                            // pop to find the parent
                                            if (!parentStack.isEmpty()) {
                                                parentStack.pop();
                                            }
                                        }
                                        if (!parentStack.isEmpty()) {
                                            Integer parentRow = parentStack.peek();
                                            rowToParentRow.put(rowNum, parentRow);
                                        }
                                    }
                                }
                            } else {
                                // no hierarchy string: top level item
                                while (!parentStack.isEmpty()) {
                                    // empty the stack
                                    parentStack.pop();
                                }
                            }
                            // add row to stack for possible children
                            parentStack.push(rowNum);
                        }
                    }
                }
            } else {
                IFieldTypeRT fieldTypeRT = FieldTypeManager.getFieldTypeRT(fieldID);
                Object attributeValue = null;
                if (fieldTypeRT.isComposite() || fieldTypeRT.isMultipleValues()) {
                    String compositeOrMultipleValue = getStringCellValue(cell);
                    if (compositeOrMultipleValue == null || "".equals(compositeOrMultipleValue.trim())) {
                        workItemBean.setAttribute(fieldID, null, null);
                        continue;
                    }
                    // we suppose that all composite and multiple values are
                    // lookup values
                    // TODO refactor if that is not true
                    String[] parts;
                    if (fieldTypeRT.isMultipleValues()) {
                        parts = compositeOrMultipleValue
                                .split(CustomSelectBaseRT.OPTION_SPLITTER_VALUES_STRING);
                        List<Integer> multipleValues = new ArrayList<Integer>();
                        for (int i = 0; i < parts.length; i++) {
                            String part = parts[i];
                            Integer objectID = null;
                            try {
                                objectID = getLookupValue(part, fieldTypeRT, fieldID, systemLookups,
                                        projectSpecificLookups, serializableBeanAllowedContext, null,
                                        invalidValueHandlingMap, locale);
                            } catch (ExcelImportNotExistingCellValueException e) {
                                addGridError(gridErrorsMap, NOT_EXISTING_ERRORS, rowNum,
                                        ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID,
                                        e.getMessage());
                            } catch (ExcelImportNotAllowedCellValueException e) {
                                addGridError(gridErrorsMap, NOT_ALLOWED_ERRORS, rowNum,
                                        ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID,
                                        e.getMessage());
                            }
                            if (objectID != null) {
                                multipleValues.add(objectID);
                            }
                        }
                        if (!multipleValues.isEmpty()) {
                            attributeValue = multipleValues.toArray();
                            excelValueFound = true;
                        }
                    } else {
                        int numberOfParts = ((CustomCompositeBaseRT) fieldTypeRT).getNumberOfParts();
                        parts = compositeOrMultipleValue
                                .split("\\" + CustomCompositeBaseRT.PART_SPLITTER_VALUES_STRING);
                        if (parts != null && parts.length > numberOfParts) {
                            addGridError(gridErrorsMap, WRONG_COMPOSITE_SIZE, rowNum,
                                    ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID,
                                    compositeOrMultipleValue);
                        }
                        Map<Integer, Integer> componentPartsMap = new HashMap<Integer, Integer>();
                        attributeValue = new HashMap<Integer, Object>();
                        if (parts != null) {
                            for (int i = 0; i < parts.length; i++) {
                                String part = parts[i];
                                Integer objectID = null;
                                IFieldTypeRT componentFieldType = ((CustomCompositeBaseRT) fieldTypeRT)
                                        .getCustomFieldType(i + 1);
                                if (componentFieldType != null) {
                                    try {
                                        objectID = getLookupValue(part, componentFieldType, fieldID,
                                                systemLookups, projectSpecificLookups,
                                                serializableBeanAllowedContext, componentPartsMap,
                                                invalidValueHandlingMap, locale);
                                    } catch (ExcelImportNotExistingCellValueException e) {
                                        addGridError(gridErrorsMap, NOT_EXISTING_ERRORS, rowNum,
                                                ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID,
                                                e.getMessage());
                                    } catch (ExcelImportNotAllowedCellValueException e) {
                                        addGridError(gridErrorsMap, NOT_ALLOWED_ERRORS, rowNum,
                                                ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID,
                                                e.getMessage());
                                    }
                                    if (objectID == null) {
                                        // workItemBean.setAttribute(fieldID,
                                        // Integer.valueOf(i+1), null);
                                        ((Map<Integer, Object>) attributeValue).put(Integer.valueOf(i + 1),
                                                null);
                                    } else {
                                        componentPartsMap.put(Integer.valueOf(i + 1), objectID);
                                        // workItemBean.setAttribute(fieldID,
                                        // Integer.valueOf(i+1), new
                                        // Object[] {objectID});
                                        ((Map<Integer, Object>) attributeValue).put(Integer.valueOf(i + 1),
                                                new Object[] { objectID });
                                        excelValueFound = true;
                                    }
                                }
                            }
                        }
                    }
                } else {
                    // simple field
                    // Object attributeValue = null;
                    try {
                        attributeValue = getAttributeValue(cell, fieldID, null, serializableBeanAllowedContext,
                                locale, invalidValueHandlingMap, systemLookups, projectSpecificLookups);
                    } catch (ExcelImportNotExistingCellValueException e) {
                        addGridError(gridErrorsMap, NOT_EXISTING_ERRORS, rowNum,
                                ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, e.getMessage());
                    } catch (ExcelImportNotAllowedCellValueException e) {
                        addGridError(gridErrorsMap, NOT_ALLOWED_ERRORS, rowNum,
                                ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, e.getMessage());
                    } catch (ExcelImportInvalidCellValueException e) {
                        addGridError(gridErrorsMap, INVALID_ERRORS, rowNum,
                                ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, e.getMessage());
                    }
                    if (attributeValue != null) {
                        excelValueFound = true;
                        if (possibleBottomUpFields.contains(fieldID)) {
                            TFieldConfigBean fieldConfigBean = FieldRuntimeBL
                                    .getFieldConfigForProjectIssueTypeField(
                                            projectsToIssueTypesToFieldConfigsMapForBottomUpFields, projectID,
                                            issueTypeID, fieldID);
                            Object fieldSettings = FieldRuntimeBL.getFieldSettingsForProjectIssueTypeField(
                                    projectsIssueTypesFieldSettingsMapForBottomUpFields, projectID, issueTypeID,
                                    fieldID);
                            if (fieldTypeRT.getHierarchicalBehavior(fieldID, fieldConfigBean,
                                    fieldSettings) == HIERARCHICAL_BEHAVIOR_OPTIONS.COMPUTE_BOTTOM_UP
                                    && ItemBL.hasChildren(workItemBean.getObjectID())) {
                                Date trackPlusAttributeValue = (Date) workItemBean.getAttribute(fieldID);
                                if (EqualUtils.notEqual(trackPlusAttributeValue, (Date) attributeValue)) {
                                    // add read only restrictions for start
                                    // and end date for non leaf workItems
                                    LOGGER.debug("Parent change restriction for bottom up date " + fieldID);
                                    addGridError(gridErrorsMap, NOT_EDITABLE_ERRORS, rowNum,
                                            ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID,
                                            getStringCellValue(cell));
                                }
                            }
                            /*
                             * if
                             * (ApplicationBean.getInstance().getSiteBean
                             * ().getSummaryItemsBehavior() &&
                             * ItemBL2.hasChildren
                             * (workItemBean.getObjectID())) { Date
                             * trackPlusAttributeValue =
                             * (Date)workItemBean.getAttribute(fieldID); if
                             * (EqualUtils.notEqual(trackPlusAttributeValue,
                             * (Date)attributeValue)) { //add read only
                             * restrictions for start and end date for non
                             * leaf workItems LOGGER.debug(
                             * "Summary parent change restriction for date "
                             * + fieldID); addGridError(gridErrorsMap,
                             * NOT_EDITABLE_ERRORS, rowNum,
                             * ExcelFieldMatchBL
                             * .colNumericToLetter(columnIndex), fieldID,
                             * getStringCellValue(cell)); } }
                             */
                        }
                    }
                }
                attributeChanged = fieldTypeRT.valueModified(attributeValue,
                        workItemBean.getAttribute(fieldID));
                workItemBean.setAttribute(fieldID, null, attributeValue);
            }
            if (attributeChanged) {
                try {
                    verifyFieldRestrictions(fieldForRestriction, restrictedFields, cell, locale);
                } catch (ExcelImportNotModifiableCellValueException e) {
                    addGridError(gridErrorsMap, NOT_EDITABLE_ERRORS, rowNum,
                            ExcelFieldMatchBL.colNumericToLetter(columnIndex), fieldID, e.getMessage());
                }
            }
        }
        if (!excelValueFound) {
            // not a single excel value found in any cell from the row
            // simply neglect this row.
            // expanded row count can be greater than the number of real
            // workItem rows
            // for example when the content of some rows is deleted but the
            // rows are not deleted
            // and empty rows may remain in the excel
            LOGGER.info("The row number " + (rowNum + 1) + " contains only empty cells and will be neglected");
            continue;
        }
        // add the default values for those fields which didn't have column
        // in
        // excel sheet or have column but the value is empty or not valid
        Iterator<Integer> itrDefaultValueFields = defaultValuesMap.keySet().iterator();
        while (itrDefaultValueFields.hasNext()) {
            Integer fieldID = itrDefaultValueFields.next();
            if (/*!fieldIDToColumnIndexMap.containsKey(fieldID) ||*/workItemBean.getAttribute(fieldID,
                    null) == null) {
                if (invalidValueHandlingMap.containsKey(fieldID)) {
                    if (DEFAULT_IF_NOT_EXIST_OR_EMPTY.equals(invalidValueHandlingMap.get(fieldID))) {
                        IFieldTypeRT fieldTypeRT = FieldTypeManager.getFieldTypeRT(fieldID, null);
                        ILookup lookup = (ILookup) fieldTypeRT;
                        Integer defaultObjectID = defaultValuesMap.get(fieldID);
                        if (defaultObjectID != null) {
                            boolean allowed = lookup.lookupBeanAllowed(defaultObjectID,
                                    serializableBeanAllowedContext);
                            if (allowed) {
                                workItemBean.setAttribute(fieldID, null, defaultObjectID);
                            } else {
                                // for example when no default project
                                // and/or issue type is specified the
                                // default manager and responsible
                                // lists contain the users which are manager
                                // or responsible in any of the projects
                                // (but maybe not in all)
                                LOGGER.debug("The default value is not allowed for field " + fieldID
                                        + " on row " + rowNum);
                                // cache the show values and localized
                                // labels to spare additional database
                                // accesses
                                String showValue;
                                if (defaultShowValuesMap.containsKey(fieldID)) {
                                    showValue = defaultShowValuesMap.get(fieldID);
                                } else {
                                    showValue = fieldTypeRT.getShowValue(defaultObjectID, locale);
                                    defaultShowValuesMap.put(fieldID, showValue);
                                }
                                String localizedLabel;
                                if (defaultLocalizedFieldLabels.containsKey(fieldID)) {
                                    localizedLabel = defaultLocalizedFieldLabels.get(fieldID);
                                } else {
                                    localizedLabel = FieldRuntimeBL.getLocalizedDefaultFieldLabel(fieldID,
                                            locale);
                                    defaultLocalizedFieldLabels.put(fieldID, localizedLabel);
                                }
                                addGridError(gridErrorsMap, NOT_ALLOWED_DEFAULT_VALUES_ERRORS, rowNum,
                                        localizedLabel, fieldID, showValue);
                            }
                        }
                    }
                }
            }
        }
        workItemBeansMap.put(rowNum, workItemBean);
    }
    return workItemBeansMap;
}

From source file:com.beyondb.io.ExcelControl.java

@Override
public Object[][] readTableContent() throws IOException, InvalidFormatException, Exception {
    try {/* www .  j a v  a  2 s .  com*/
        //OPCPackage pkg = OPCPackage.open(file);
        //            InputStream m_InputStream = new FileInputStream(m_File);
        Sheet sheet = null;
        //            if (!m_InputStream.markSupported()) {
        //                m_InputStream = new PushbackInputStream(m_InputStream, 8);
        //            } 
        //            if (POIFSFileSystem.hasPOIFSHeader(m_InputStream)) {
        //                HSSFWorkbook hSSFWorkbook = new HSSFWorkbook(m_InputStream);
        //                 sheet  = (Sheet)hSSFWorkbook.getSheetAt(0);
        //            
        //             } else if (POIXMLDocument.hasOOXMLHeader(m_InputStream)) {
        //                XSSFWorkbook xSSFWorkbook = new XSSFWorkbook(OPCPackage.open(m_File));
        //               sheet  = (Sheet)xSSFWorkbook.getSheetAt(0);
        //             }
        //             else {
        //                throw new IllegalArgumentException("excel?poi??");
        //            }
        sheet = getSheet();
        if (sheet != null) {
            if (sheet.getLastRowNum() == 0) {
                throw new Exception("Excel");
            }
            //?
            m_RowNum = sheet.getLastRowNum() + 1;

            //                m_ColumnNum = sheet.getRow(0).getPhysicalNumberOfCells();
            m_ColumnNum = sheet.getRow(0).getLastCellNum();
            m_TableStr = new Object[m_RowNum][m_ColumnNum];

            for (int rindex = 0; rindex < m_RowNum; rindex++) {
                Row row = sheet.getRow(rindex);
                for (int cindex = 0; cindex < m_ColumnNum; cindex++) {
                    Cell cell = row.getCell(cindex);

                    if (cell == null) {
                        m_TableStr[rindex][cindex] = "";
                    } else {
                        String value = "";
                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            //                          System.out.println(cell.getRichStringCellValue().getString());                          
                            value = cell.getRichStringCellValue().getString().replace("\n", "");
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {
                                //                                System.out.println(cell.getDateCellValue());
                                value = cell.getDateCellValue().toString();
                            } else {

                                DecimalFormat df = new DecimalFormat("#");
                                value = String.valueOf(cell.getNumericCellValue());
                                double d = cell.getNumericCellValue();
                                int dInt = (int) d;
                                BigDecimal b1 = new BigDecimal(value);
                                BigDecimal b2 = new BigDecimal(Integer.toString(dInt));
                                double dPoint = b1.subtract(b2).doubleValue();
                                if (dPoint == 0) {
                                    //?
                                    value = df.format(cell.getNumericCellValue());
                                }
                            }
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            //                            System.out.println(cell.getBooleanCellValue());
                            value = cell.getBooleanCellValue() + "";
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            //                            System.out.println(cell.getCellFormula());
                            value = cell.getCellFormula();
                            break;
                        case Cell.CELL_TYPE_BLANK:
                            value = "";
                        default:
                            //                            System.out.println();
                            value = "";
                        }
                        m_TableStr[row.getRowNum()][cell.getColumnIndex()] = value;
                    }
                }
            }
        }

    } catch (IOException | InvalidFormatException e) {
        Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "excel??", e);
        throw e;

    } catch (Exception ex) {
        Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "excel??", ex);

        throw ex;
    } finally {
        m_InputStream.close();
    }

    return m_TableStr;
}