Example usage for org.apache.poi.ss.usermodel Sheet getLastRowNum

List of usage examples for org.apache.poi.ss.usermodel Sheet getLastRowNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. getLastRowNum

Introduction

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

Prototype

int getLastRowNum();

Source Link

Document

Gets the last row on the sheet Note: rows which had content before and were set to empty later might still be counted as rows by Excel and Apache POI, so the result of this method will include such rows and thus the returned value might be higher than expected!

Usage

From source file:com.qihang.winter.poi.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

private void parseTemplate(Sheet sheet, Map<String, Object> map) throws Exception {
    deleteCell(sheet, map);//from   w  w  w  .ja  v a2s .c  o m
    Row row = null;
    int index = 0;
    while (index <= sheet.getLastRowNum()) {
        row = sheet.getRow(index++);
        if (row == null) {
            continue;
        }
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            if (row.getCell(i) != null
                    && !tempCreateCellSet.contains(row.getRowNum() + "_" + row.getCell(i).getColumnIndex())) {
                setValueForCellByMap(row.getCell(i), map);
            }
        }
    }
}

From source file:com.qihang.winter.poi.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * ,??/*from  ww  w . j av  a2 s  .  c  om*/
 * @param sheet
 * @param map
 * @throws Exception 
 */
private void deleteCell(Sheet sheet, Map<String, Object> map) throws Exception {
    Row row = null;
    Cell cell = null;
    int index = 0;
    while (index <= sheet.getLastRowNum()) {
        row = sheet.getRow(index++);
        if (row == null) {
            continue;
        }
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            cell = row.getCell(i);
            if (row.getCell(i) != null && (cell.getCellType() == Cell.CELL_TYPE_STRING
                    || cell.getCellType() == Cell.CELL_TYPE_NUMERIC)) {
                cell.setCellType(Cell.CELL_TYPE_STRING);
                String text = cell.getStringCellValue();
                if (text.contains(PoiElUtil.IF_DELETE)) {
                    if (Boolean.valueOf(PoiElUtil.eval(text
                            .substring(text.indexOf(PoiElUtil.START_STR) + 2, text.indexOf(PoiElUtil.END_STR))
                            .trim(), map).toString())) {
                        com.qihang.winter.poi.util.PoiSheetUtility.deleteColumn(sheet, i);
                    }
                    cell.setCellValue("");
                }
            }
        }
    }
}

From source file:com.qihang.winter.poi.excel.imports.ExcelImportServer.java

License:Apache License

private <T> List<T> importExcel(Collection<T> result, Sheet sheet, Class<?> pojoClass,
        com.qihang.winter.poi.excel.entity.ImportParams params, Map<String, PictureData> pictures)
        throws Exception {
    List collection = new ArrayList();
    Map<String, com.qihang.winter.poi.excel.entity.params.ExcelImportEntity> excelParams = new HashMap<String, com.qihang.winter.poi.excel.entity.params.ExcelImportEntity>();
    List<com.qihang.winter.poi.excel.entity.params.ExcelCollectionParams> excelCollection = new ArrayList<com.qihang.winter.poi.excel.entity.params.ExcelCollectionParams>();
    String targetId = null;//from   w  w w . j  a va2  s. c o  m
    if (!Map.class.equals(pojoClass)) {
        Field fileds[] = com.qihang.winter.poi.util.PoiPublicUtil.getClassFields(pojoClass);
        com.qihang.winter.poi.excel.annotation.ExcelTarget etarget = pojoClass
                .getAnnotation(com.qihang.winter.poi.excel.annotation.ExcelTarget.class);
        if (etarget != null) {
            targetId = etarget.value();
        }
        getAllExcelField(targetId, fileds, excelParams, excelCollection, pojoClass, null);
    }
    Iterator<Row> rows = sheet.rowIterator();
    for (int j = 0; j < params.getTitleRows(); j++) {
        rows.next();
    }
    Map<Integer, String> titlemap = getTitleMap(rows, params, excelCollection);
    Row row = null;
    Object object = null;
    String picId;
    while (rows.hasNext()
            && (row == null || sheet.getLastRowNum() - row.getRowNum() > params.getLastOfInvalidRow())) {
        row = rows.next();
        // ???,?,?
        if ((row.getCell(params.getKeyIndex()) == null
                || StringUtils.isEmpty(getKeyValue(row.getCell(params.getKeyIndex())))) && object != null) {
            for (com.qihang.winter.poi.excel.entity.params.ExcelCollectionParams param : excelCollection) {
                addListContinue(object, param, row, titlemap, targetId, pictures, params);
            }
        } else {
            object = com.qihang.winter.poi.util.PoiPublicUtil.createObject(pojoClass, targetId);
            try {
                for (int i = row.getFirstCellNum(), le = row.getLastCellNum(); i < le; i++) {
                    Cell cell = row.getCell(i);
                    String titleString = (String) titlemap.get(i);
                    if (excelParams.containsKey(titleString) || Map.class.equals(pojoClass)) {
                        if (excelParams.get(titleString) != null
                                && excelParams.get(titleString).getType() == 2) {
                            picId = row.getRowNum() + "_" + i;
                            saveImage(object, picId, excelParams, titleString, pictures, params);
                        } else {
                            saveFieldValue(params, object, cell, excelParams, titleString, row);
                        }
                    }
                }

                for (com.qihang.winter.poi.excel.entity.params.ExcelCollectionParams param : excelCollection) {
                    addListContinue(object, param, row, titlemap, targetId, pictures, params);
                }
                collection.add(object);
            } catch (com.qihang.winter.poi.exception.excel.ExcelImportException e) {
                if (!e.getType()
                        .equals(com.qihang.winter.poi.exception.excel.enums.ExcelImportEnum.VERIFY_ERROR)) {
                    throw new com.qihang.winter.poi.exception.excel.ExcelImportException(e.getType(), e);
                }
            }
        }
    }
    return collection;
}

From source file:com.qihang.winter.poi.util.PoiSheetUtility.java

License:Apache License

/**
 * Given a sheet, this method deletes a column from a sheet and moves
 * all the columns to the right of it to the left one cell.
 * //w  ww . java 2  s .c om
 * Note, this method will not update any formula references.
 * 
 * @param sheet
 * @param column
 */
public static void deleteColumn(Sheet sheet, int columnToDelete) {
    int maxColumn = 0;
    for (int r = 0; r < sheet.getLastRowNum() + 1; r++) {
        Row row = sheet.getRow(r);

        // if no row exists here; then nothing to do; next!
        if (row == null)
            continue;

        // if the row doesn't have this many columns then we are good; next!
        int lastColumn = row.getLastCellNum();
        if (lastColumn > maxColumn)
            maxColumn = lastColumn;

        if (lastColumn < columnToDelete)
            continue;

        for (int x = columnToDelete + 1; x < lastColumn + 1; x++) {
            Cell oldCell = row.getCell(x - 1);
            if (oldCell != null)
                row.removeCell(oldCell);

            Cell nextCell = row.getCell(x);
            if (nextCell != null) {
                Cell newCell = row.createCell(x - 1, nextCell.getCellType());
                cloneCell(newCell, nextCell);
            }
        }
    }

    // Adjust the column widths
    for (int c = 0; c < maxColumn; c++) {
        sheet.setColumnWidth(c, sheet.getColumnWidth(c + 1));
    }
}

From source file:com.rarediscovery.services.logic.WorkPad.java

/**
 * //w ww  . j  a v  a2 s .  c  o m
 * Add a column of data to a worksheet
 * 
 * @param sheetName
 * @param dataArray
 * 
 * @param startingRow 
 * @param dataColumn
 */
public void addColumnData(String sheetName, String[] dataArray, int startingRow, int dataColumn) {
    Sheet s = addWorksheet(sheetName);

    CellStyle style = applySelectedStyle();

    for (int r = 0; r < dataArray.length; r++) {
        Row row = null;
        // When item requested is out of range of available rows
        if (s.getLastRowNum() < startingRow + r) {
            row = s.createRow(startingRow + r);

        } else {
            row = s.getRow(startingRow + r);
        }

        row.createCell(dataColumn).setCellValue(dataArray[r]);
        row.setRowStyle(style);
    }
}

From source file:com.rarediscovery.services.logic.WorkPad.java

public void addRowData(String sheetName, String[] dataArray, int rowIndex, int column) {
    Sheet s = addWorksheet(sheetName);
    Row row = null;/*from w  w w  . j a  v  a  2s  . com*/

    // When item requested is out of range of available rows
    if (s.getLastRowNum() > rowIndex) {
        row = s.getRow(rowIndex);
    } else {
        row = s.createRow(rowIndex);
    }

    CellStyle style = applySelectedStyle();

    for (int r = 0; r < dataArray.length; r++) {
        row.createCell(column + r).setCellValue(dataArray[r]);
        row.getCell(column + r).setCellStyle(style);
    }
    //font.setBold(false);
}

From source file:com.rodrigodev.xgen4j_table_generator.test.common.assertion.excel.conditions.ExcelFile.java

License:Open Source License

@Override
public boolean matches(InputStream actualInputStream) {

    boolean result = true;

    try {//from  ww  w .j  ava  2s.  com
        try (Workbook expectedWb = WorkbookFactory.create(expectedInputStream)) {
            Sheet expectedSheet = expectedWb.getSheetAt(0);

            try (Workbook actualWb = WorkbookFactory.create(actualInputStream)) {
                Sheet actualSheet = actualWb.getSheetAt(0);

                int expectedRowCount = expectedSheet.getLastRowNum();
                for (int r = 0; r <= expectedRowCount; r++) {
                    Row expectedRow = expectedSheet.getRow(r);
                    Row actualRow = actualSheet.getRow(r);
                    if (actualRow == null) {
                        actualRow = actualSheet.createRow(r);
                    }

                    int expectedCellCount = expectedRow.getLastCellNum();
                    for (int c = 0; c < expectedCellCount; c++) {
                        Cell expectedCell = expectedRow.getCell(c, Row.CREATE_NULL_AS_BLANK);
                        Cell actualCell = actualRow.getCell(c, Row.CREATE_NULL_AS_BLANK);

                        if (expectedCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                            assertThat(actualCell.getNumericCellValue())
                                    .isEqualTo(expectedCell.getNumericCellValue(), offset(0.00001));
                        } else {
                            expectedCell.setCellType(Cell.CELL_TYPE_STRING);
                            actualCell.setCellType(Cell.CELL_TYPE_STRING);
                            assertThat(actualCell.getStringCellValue())
                                    .isEqualTo(expectedCell.getStringCellValue());
                        }
                    }
                }
            }
        }
    } catch (AssertionError error) {
        describedAs(error.getMessage());
        result = false;
    } catch (Exception e) {
        throw new RuntimeException(e);
    }

    return result;
}

From source file:com.smanempat.view.ExcelReading.java

public static void echoAsCSV(Sheet sheet) {
    Row row = null;//from   w w w .j  a v a2 s .  c  o  m
    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        row = sheet.getRow(i);
        for (int j = 0; j < row.getLastCellNum(); j++) {
            System.out.print("\"" + row.getCell(j) + "\";");
        }
        System.out.println();
    }
}

From source file:com.smegi.bonusi.model.Excel.java

public void getExcelBonuses(int month) {
    Calendar calendar = Calendar.getInstance();
    calendar.set(2016, month - 1, Calendar.DAY_OF_MONTH);
    try {/*from  w  ww  .  j  a v a2 s .com*/
        FileInputStream inputStream = new FileInputStream(new File(path));
        Workbook workbook = new XSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(month - 1);

        for (int i = 2; i < sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            String username = "";
            int bonuses = 0;
            for (int j = 1; j < row.getLastCellNum(); j++) {
                //getting username

                Cell cell = row.getCell(j);
                if (j == 1) {
                    username = cell.toString();
                    continue;
                }

                if (cell.toString() != "") {
                    bonuses++;
                }
            }

            // updating user in userslist
            for (User user : users) {
                if (user.getName().equalsIgnoreCase(username)) {
                    //                        System.out.println("Found username in base: " + username);
                    //                        System.out.println("    - adding bonuses: " + bonuses);
                    StringBuilder sb = new StringBuilder();
                    sb.append(calendar.get(Calendar.YEAR)).append("-").append((calendar.get(Calendar.MONTH)));
                    user.addExcelBonusi(sb.toString(), bonuses);
                    break;
                }
            }

        }

    } catch (FileNotFoundException ex) {
        Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:com.softtek.mdm.web.institution.UserController.java

/**
  * /*  w ww . ja  v  a 2  s. c  o  m*/
  * 
  * @param request
  * @param response
  * @return
  * @throws Exception
  */
@Log(operateType = "logs.usercontroller.member.type.import", operateContent = "logs.usercontroller.content.member.import")
@RequestMapping(value = "/importusers", method = RequestMethod.POST)
@ResponseBody
public Map<String, Object> importvirmember(MultipartFile files, HttpServletRequest request,
        HttpServletResponse response, HttpSession session) throws Exception {
    Map<String, Object> messages = new HashMap<String, Object>();
    // xlsxlsx
    Integer filetype = 0;
    if (files.getOriginalFilename().endsWith("xls")) {
        filetype = 03;
    }
    if (files.getOriginalFilename().endsWith("xlsx")) {
        filetype = 07;
    }
    // ?id
    OrganizationModel organization = (OrganizationModel) session
            .getAttribute(SessionStatus.SOFTTEK_ORGANIZATION.toString());
    Integer orgid = organization.getId();
    // ???
    ManagerModel managerModel = (ManagerModel) session.getAttribute(SessionStatus.SOFTTEK_MANAGER.toString());
    List<Integer> idlist = new ArrayList<Integer>();
    // createById
    Integer managerId = managerModel.getId();
    @SuppressWarnings("unchecked")
    List<StructureModel> list = (List<StructureModel>) session
            .getAttribute(SessionStatus.SOFTTEK_DEPARTMENT.toString());
    NodeModel nodes = treeManager.bulidTreeContainUser(list, organization.getId(), managerModel.getUser());
    // ??
    if (managerModel.getUser() != null) {
        @SuppressWarnings("unchecked")
        List<StructureModel> list1 = (List<StructureModel>) session
                .getAttribute(SessionStatus.SOFTTEK_DEPARTMENT.toString());
        for (StructureModel s : list1) {
            idlist.add(s.getId());
        }
    } else {
        idlist = null;
    }
    InputStream ins = files.getInputStream();
    Workbook wb = null;
    wb = WorkbookFactory.create(ins);
    ins.close();
    Sheet sheet = null;
    // 3.Excel
    if (filetype == 03) {
        sheet = (HSSFSheet) wb.getSheetAt(0);
    }
    if (filetype == 07) {
        sheet = (XSSFSheet) wb.getSheetAt(0);
    }
    // 
    int trLength = sheet.getLastRowNum();

    Row rowtest = sheet.getRow(0);
    Cell celltest1 = rowtest.getCell(0);
    Cell celltest2 = rowtest.getCell(1);
    Cell celltest3 = rowtest.getCell(2);
    if (celltest1 != null && celltest2 != null && celltest3 != null) {
        celltest1.setCellType(Cell.CELL_TYPE_STRING);
        celltest2.setCellType(Cell.CELL_TYPE_STRING);
        celltest3.setCellType(Cell.CELL_TYPE_STRING);
        if (trLength > 0) {

            if (celltest1.getStringCellValue()
                    .equals(messageSource.getMessage("web.institution.usercontroller.export.excel.label1", null,
                            LocaleContextHolder.getLocale()))
                    && celltest2.getStringCellValue()
                            .equals(messageSource.getMessage(
                                    "web.institution.usercontroller.export.excel.label2", null,
                                    LocaleContextHolder.getLocale()))
                    && celltest3.getStringCellValue()
                            .equals(messageSource.getMessage(
                                    "web.institution.usercontroller.export.excel.label3", null,
                                    LocaleContextHolder.getLocale()))) {
                List<ExcelInsertUserModel> excelList = new ArrayList<ExcelInsertUserModel>();
                List<String> usertlist = (List<String>) userService.findAllMember(orgid);
                String erromessages = "";

                int rownumber = 0;
                for (int i = 1; i <= trLength; i++) {
                    // Excel
                    Row row = sheet.getRow(i);
                    ExcelInsertUserModel excelInsertUserModel = new ExcelInsertUserModel();
                    for (int j = 0; j <= 10; j++) {
                        // Excel?
                        if (row != null) {
                            Cell newcell = row.getCell(j);
                            String cell = "";
                            if (newcell != null) {
                                newcell.setCellType(Cell.CELL_TYPE_STRING);
                                if (StringUtils.isNotBlank(StringUtils.trim(newcell.getStringCellValue()))) {
                                    // cell =
                                    // StringUtils.trim(newcell.getStringCellValue());
                                    cell = newcell.getStringCellValue().replaceAll(" ", "");
                                }
                            }

                            if (j == 0) {
                                // ???
                                if (StringUtils.isNotBlank(cell)) {
                                    String groupname[] = cell.split("/");
                                    if (null != groupname) {
                                        /*  */
                                        int deep = 0;
                                        List<NodeModel> nodeList = null;
                                        StructureModel structure = null;
                                        NodeModel temp = nodes;
                                        do {
                                            if (temp.getTags().getParent() == null) {
                                                nodeList = temp.getNodes();
                                                for (NodeModel n : nodeList) {
                                                    if (n.getTags().getName().equals(groupname[deep])) {
                                                        deep++;
                                                        temp = n;
                                                        break;
                                                    }
                                                }
                                                if (deep == 0) {
                                                    break;
                                                }
                                            } else {
                                                int deep1 = deep;
                                                for (NodeModel n : nodeList) {
                                                    if (n.getTags().getName().equals(groupname[deep])) {
                                                        deep++;
                                                        temp = n;
                                                        break;
                                                    }
                                                }
                                                if (deep1 == deep) {
                                                    break;
                                                }
                                            }
                                            if (deep == groupname.length) {
                                                structure = temp.getTags();
                                                break;
                                            }
                                        } while ((nodeList = temp.getNodes()) != null);
                                        if (structure == null) {
                                            rownumber = i + 1;
                                            Object[] args = { rownumber };
                                            erromessages = erromessages + messageSource.getMessage(
                                                    "web.institution.usercontroller.excel.erro.nullgroup", args,
                                                    LocaleContextHolder.getLocale());
                                        } else {
                                            if (idlist != null) {
                                                int idlistsize1 = idlist.size();
                                                idlist.remove(structure.getId());
                                                int idlistsize2 = idlist.size();
                                                if (idlistsize1 != idlistsize2) {
                                                    excelInsertUserModel.setGroup_id(structure.getId());
                                                } else {
                                                    rownumber = i + 1;
                                                    Object[] args = { rownumber };
                                                    erromessages = erromessages + messageSource.getMessage(
                                                            "web.institution.usercontroller.excel.erro.group2",
                                                            args, LocaleContextHolder.getLocale());
                                                }
                                                idlist.add(structure.getId());
                                            } else {
                                                excelInsertUserModel.setGroup_id(structure.getId());
                                                // excelList.add(excelInsertUserModel);
                                            }
                                        }
                                    }
                                } else {
                                    rownumber = i + 1;
                                    Object[] args = { rownumber };
                                    erromessages = erromessages + messageSource.getMessage(
                                            "web.institution.usercontroller.excel.erro.group3", args,
                                            LocaleContextHolder.getLocale());
                                }
                            }
                            // ??
                            if (j == 1) {

                                if (StringUtils.isNotBlank(cell)) {
                                    if (cell.length() < 20) {
                                        boolean reuser = usertlist.remove(cell);
                                        if (reuser == true) {
                                            excelInsertUserModel.setUser_name(cell);
                                            rownumber = i + 1;
                                            Object[] args = { rownumber };
                                            erromessages = erromessages + messageSource.getMessage(
                                                    "web.institution.usercontroller.excel.erro.username", args,
                                                    LocaleContextHolder.getLocale());
                                            usertlist.add(cell);
                                        } else {
                                            excelInsertUserModel.setUser_name(cell);
                                        }
                                    } else {
                                        rownumber = i + 1;
                                        Object[] args = { rownumber };
                                        erromessages = erromessages + messageSource.getMessage(
                                                "web.institution.usercontroller.excel.erro.username1", args,
                                                LocaleContextHolder.getLocale());
                                    }

                                } else {
                                    rownumber = i + 1;
                                    Object[] args = { rownumber };
                                    erromessages = erromessages + messageSource.getMessage(
                                            "web.institution.usercontroller.excel.erro.username2", args,
                                            LocaleContextHolder.getLocale());
                                }
                            }
                            // ??
                            if (j == 2) {
                                if (StringUtils.isNotBlank(cell)) {
                                    if (cell.length() < 20) {
                                        excelInsertUserModel.setReal_name(cell);
                                    } else {
                                        rownumber = i + 1;
                                        Object[] args = { rownumber };
                                        erromessages = erromessages + messageSource.getMessage(
                                                "web.institution.usercontroller.excel.erro.realname", args,
                                                LocaleContextHolder.getLocale());
                                    }

                                } else {
                                    rownumber = i + 1;
                                    Object[] args = { rownumber };
                                    erromessages = erromessages + messageSource.getMessage(
                                            "web.institution.usercontroller.excel.erro.realname1", args,
                                            LocaleContextHolder.getLocale());
                                }
                            }
                            // 
                            if (j == 3) {
                                if (StringUtils.isNotBlank(cell)) {
                                    String regex = "^\\s*\\w+(?:\\.{0,1}[\\w-]+)*@[a-zA-Z0-9]+(?:[-.][a-zA-Z0-9]+)*\\.[a-zA-Z]+\\s*$";
                                    if (cell.matches(regex) == true) {
                                        excelInsertUserModel.setEmail(cell);
                                    } else {
                                        rownumber = i + 1;
                                        Object[] args = { rownumber };
                                        erromessages = erromessages + messageSource.getMessage(
                                                "web.institution.usercontroller.excel.erro.email", args,
                                                LocaleContextHolder.getLocale());
                                    }
                                }
                            }
                            // 
                            if (j == 4) {
                                if (StringUtils.isNotBlank(cell)) {
                                    if (cell.length() < 30) {
                                        excelInsertUserModel.setMark(cell);
                                    } else {
                                        rownumber = i + 1;
                                        Object[] args = { rownumber };
                                        erromessages = erromessages + messageSource.getMessage(
                                                "web.institution.usercontroller.excel.erro.mark", args,
                                                LocaleContextHolder.getLocale());
                                    }

                                }
                            }
                            // ??
                            if (j == 5) {
                                if (StringUtils.isNotBlank(cell)) {
                                    if (cell.length() < 30) {
                                        excelInsertUserModel.setPhone((cell));
                                    } else {
                                        rownumber = i + 1;
                                        Object[] args = { rownumber };
                                        erromessages = erromessages + messageSource.getMessage(
                                                "web.institution.usercontroller.excel.erro.phone", args,
                                                LocaleContextHolder.getLocale());
                                    }

                                }
                            }
                            // 
                            if (j == 6) {
                                if (StringUtils.isNotBlank(cell)) {
                                    if (cell.equals(
                                            messageSource.getMessage("web.institution.usercontroller.sex.woman",
                                                    null, LocaleContextHolder.getLocale()))) {
                                        excelInsertUserModel.setGender(0);
                                    } else {
                                        excelInsertUserModel.setGender(1);
                                    }
                                } else {
                                    excelInsertUserModel.setGender(1);
                                }
                            }
                            // ??
                            if (j == 7) {
                                if (StringUtils.isNotBlank(cell)) {
                                    if (cell.length() < 30) {
                                        excelInsertUserModel.setSign(cell);
                                    } else {
                                        rownumber = i + 1;
                                        Object[] args = { rownumber };
                                        erromessages = erromessages + messageSource.getMessage(
                                                "web.institution.usercontroller.excel.erro.sign", args,
                                                LocaleContextHolder.getLocale());
                                    }
                                }
                            }
                            // ??
                            if (j == 8) {
                                if (StringUtils.isNotBlank(cell)) {
                                    if (cell.length() < 30) {
                                        excelInsertUserModel.setAddress(cell);
                                    } else {
                                        rownumber = i + 1;
                                        Object[] args = { rownumber };
                                        erromessages = erromessages + messageSource.getMessage(
                                                "web.institution.usercontroller.excel.erro.address", args,
                                                LocaleContextHolder.getLocale());
                                    }
                                }
                            }
                            // ?
                            if (j == 9) {
                                if (StringUtils.isNotBlank(cell)) {
                                    if (cell.length() < 30) {
                                        excelInsertUserModel.setOffice_phone(cell);
                                    } else {
                                        rownumber = i + 1;
                                        Object[] args = { rownumber };
                                        erromessages = erromessages + messageSource.getMessage(
                                                "web.institution.usercontroller.excel.erro.office_phone", args,
                                                LocaleContextHolder.getLocale());
                                    }
                                }
                            }
                            // ??
                            if (j == 10) {
                                if (StringUtils.isNotBlank(cell)) {
                                    if (cell.length() < 30) {
                                        excelInsertUserModel.setPosition(cell);
                                    } else {
                                        rownumber = i + 1;
                                        Object[] args = { rownumber };
                                        erromessages = erromessages + messageSource.getMessage(
                                                "web.institution.usercontroller.excel.erro.position", args,
                                                LocaleContextHolder.getLocale());
                                    }
                                }
                            }
                        }
                    }
                    excelList.add(excelInsertUserModel);
                }

                // ?? ????
                List<String> yqs = new ArrayList<String>();
                Set<String> testreusername = new HashSet<String>(yqs);
                for (int k = 0; k < excelList.size(); k++) {
                    if (excelList.get(k).getUser_name() != null) {
                        testreusername.add(excelList.get(k).getUser_name());
                        yqs.add(excelList.get(k).getUser_name());
                    }
                }
                if (testreusername.size() < yqs.size()) {
                    erromessages = messageSource.getMessage(
                            "web.institution.usercontroller.excel.erro.erromessages", null,
                            LocaleContextHolder.getLocale());
                    messages.put("messages", erromessages);
                } else {
                    // ?
                    if (erromessages.length() > 0) {
                        erromessages.substring(0, erromessages.length() - 1);
                        messages.put("messages", erromessages);
                    } else {
                        Md5PasswordEncoder md5PasswordEncoder = new Md5PasswordEncoder();
                        String password = md5PasswordEncoder.encodePassword("123456", null);
                        if (excelList.size() > 0) {
                            for (int q = 0; q < excelList.size(); q++) {
                                Integer policyId = structureService
                                        .queryPolicyIdById(excelList.get(q).getGroup_id());
                                excelList.get(q).setPolicy_id(policyId);
                                excelList.get(q).setOrgid(orgid);
                                excelList.get(q).setPassword(password);
                                excelList.get(q).setCreate_by(managerId);
                            }
                            userService.importUsers(excelList);
                            messages.put("success", "success");
                        }
                    }
                }
            } else {
                String nullmessages = messageSource.getMessage(
                        "web.institution.usercontroller.excel.erromodel.erromessages", null,
                        LocaleContextHolder.getLocale());
                messages.put("messages", nullmessages);
            }
        } else {
            String nullmessages = messageSource.getMessage(
                    "web.institution.usercontroller.excel.erro.nullmessages", null,
                    LocaleContextHolder.getLocale());
            messages.put("messages", nullmessages);
        }
    } else {
        String nullmessages = messageSource.getMessage(
                "web.institution.usercontroller.excel.erromodel.erromessages", null,
                LocaleContextHolder.getLocale());
        messages.put("messages", nullmessages);
    }
    return messages;
}