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:magicware.scm.redmine.tools.IssueSyncApp.java

License:Apache License

public void execute(SyncItem syncItem) throws IOException, InvalidFormatException {

    FileInputStream in = null;// w  ww . j av  a2 s . c  o m

    try {

        // ?JSON??
        String issueTemplate = FileUtils.readFileAsString(syncItem.getJsonTemplate());

        // ???
        Matcher m = Pattern.compile(Constants.ISSUE_FIELD_VALUE_EXP).matcher(issueTemplate);

        List<MatchResult> mrList = new ArrayList<MatchResult>();

        while (m.find()) {
            MatchResult mr = m.toMatchResult();
            mrList.add(mr);
        }

        // ????
        in = new FileInputStream(syncItem.getFilePath());
        Workbook wb = WorkbookFactory.create(in);

        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

        Sheet sheet = wb.getSheet(syncItem.getSheetName());
        Row row = null;
        Cell cell = null;

        List<String> issues = new ArrayList<String>();

        // ?????
        for (int i = sheet.getLastRowNum(); i >= (syncItem.getKeyRowBeginIdx() > 0
                ? (syncItem.getKeyRowBeginIdx() - 1)
                : 0); i--) {
            // ????
            row = sheet.getRow(i);

            if (row != null) {

                String keyNo = ExcelUtils.getCellContent(row.getCell(syncItem.getKeyColumnIdx() - 1),
                        evaluator);

                // ??????????
                if (StringUtils.isBlank(keyNo)) {
                    break;
                }

                // ????
                if (redmineClient.queryIssue(syncItem.getProjectId(), syncItem.getKeyFiledId(), keyNo) == 0) {
                    StringBuilder newIssue = new StringBuilder();
                    int eolIdx = 0;
                    for (MatchResult matchResult : mrList) {

                        newIssue.append(issueTemplate.substring(eolIdx, matchResult.start()));

                        int cellIndex = Integer.valueOf(matchResult.group(1)) - 1;
                        cell = row.getCell(cellIndex);
                        String cellvalue = ExcelUtils.getCellContent(cell, evaluator);

                        // ?
                        String valueMapStr = matchResult.group(3);
                        Map<String, String> valueMap = null;
                        if (valueMapStr != null) {
                            valueMap = JSON.decode(valueMapStr);
                            if (StringUtils.isNotEmpty(cellvalue) && valueMap.containsKey(cellvalue)) {
                                cellvalue = valueMap.get(cellvalue);
                            } else {
                                cellvalue = valueMap.get("default");
                            }
                        }

                        if (StringUtils.isNotEmpty(cellvalue)) {
                            cellvalue = StringEscapeUtils.escapeJavaScript(cellvalue);
                            newIssue.append(cellvalue);
                        }
                        eolIdx = matchResult.end();
                    }
                    newIssue.append(issueTemplate.substring(eolIdx));
                    issues.add(newIssue.toString());
                } else {
                    // ???
                    break;
                }
            }
        }

        for (int i = issues.size() - 1; i >= 0; i--) {
            Map<String, Issue> issueMap = JSON.decode(issues.get(i));
            log.debug("create new issue >>>");
            log.debug(JSON.encode(issueMap, true));
            redmineClient.createNewIssue(issues.get(i));
        }

    } finally {
        if (in != null) {
            in.close();
            in = null;
        }
    }
}

From source file:manager.supplier.service.SupplierFileService.java

/**
 * @Description:?EXCEL/* ww w  .j  av  a 2  s. c  o m*/
 * SupplierFileService
 * getListFromExcel
 * @param finalFile
 * @param json
 * @param request
 * @return
 * @throws Exception List<SupplierFile>
 * @author yukai
 * 2016-10-13 ?4:11:14
 */
private List<SupplierFile> getSupplierListFromExcel(File finalFile, JSONObject json, HttpServletRequest request)
        throws Exception {
    List<SupplierFile> items = new ArrayList<SupplierFile>();
    String fileName = finalFile.getName();

    boolean isE2007 = false; //?excel2007?  
    if (fileName.endsWith("xlsx")) {
        isE2007 = true;
    }
    InputStream input = new FileInputStream(finalFile);
    Workbook wb = null;
    //??(20032007)??  
    if (isE2007) {
        wb = new XSSFWorkbook(input);
    } else {
        wb = new HSSFWorkbook(input);
    }
    Sheet sheet = wb.getSheetAt(0); //?  
    if (!sheet.iterator().hasNext()) {
        json.put("success", false);
        json.put("message", "excel");
        return items;
    }
    int trLength = sheet.getLastRowNum();
    if (trLength <= 2) {
        json.put("success", false);
        json.put("message", "?");
    }
    for (Row row : sheet) { //?
        //
        if (row.getRowNum() > 2) {
            SupplierFile item = new SupplierFile();
            if ((row.getCell(0) == null || row.getCell(0).toString().equals(""))
                    && (row.getCell(1) == null || row.getCell(1).toString().equals(""))
                    && (row.getCell(2) == null || row.getCell(2).toString().equals(""))
                    && (row.getCell(3) == null || row.getCell(3).toString().equals(""))
                    && (row.getCell(4) == null || row.getCell(4).toString().equals(""))
                    && (row.getCell(5) == null || row.getCell(5).toString().equals(""))
                    && (row.getCell(6) == null || row.getCell(6).toString().equals(""))
                    && (row.getCell(7) == null || row.getCell(7).toString().equals(""))
                    && (row.getCell(8) == null || row.getCell(8).toString().equals(""))
                    && (row.getCell(9) == null || row.getCell(9).toString().equals(""))
                    && (row.getCell(10) == null || row.getCell(10).toString().equals(""))
                    && (row.getCell(11) == null || row.getCell(11).toString().equals(""))) {
                if (row.getRowNum() == 3) {
                    json.put("success", false);
                    json.put("message", "?");
                }
                break;//?
            }
            if (row.getCell(0) == null || row.getCell(1) == null || row.getCell(2) == null
                    || row.getCell(3) == null || row.getCell(4) == null || row.getCell(5) == null
                    || row.getCell(6) == null || row.getCell(7) == null || row.getCell(8) == null
                    || row.getCell(9) == null || row.getCell(10) == null) {
                json.put("success", false);
                json.put("message", "" + (row.getRowNum() + 1) + "??");
                break;
            }
            row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
            row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
            row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
            row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
            row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
            row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
            row.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
            row.getCell(8).setCellType(Cell.CELL_TYPE_STRING);
            //if(row.getCell(9)!=null){
            row.getCell(9).setCellType(Cell.CELL_TYPE_STRING);
            //    }
            //if(row.getCell(10)!=null){
            row.getCell(10).setCellType(Cell.CELL_TYPE_STRING);
            //   }
            if (row.getCell(11) != null) {
                row.getCell(11).setCellType(Cell.CELL_TYPE_STRING);
            }
            item.setCpyname_cn((row.getCell(0) == null) ? "" : row.getCell(0).getStringCellValue());
            item.setCorporation((row.getCell(1) == null) ? "" : row.getCell(1).getStringCellValue());
            item.setContact_addr((row.getCell(2) == null) ? "" : row.getCell(2).getStringCellValue());
            item.setContacts((row.getCell(3) == null) ? "" : row.getCell(3).getStringCellValue());
            item.setM_phone((row.getCell(4) == null) ? "" : row.getCell(4).getStringCellValue());
            item.setClass_name((row.getCell(5) == null) ? "" : row.getCell(5).getStringCellValue());
            item.setNature_name((row.getCell(6) == null) ? "" : row.getCell(6).getStringCellValue());
            item.setReg_fund((row.getCell(7) == null) ? 0 : row.getCell(7).getNumericCellValue());
            item.setCurrency_name((row.getCell(8) == null) ? "" : row.getCell(8).getStringCellValue());
            item.setReg_account((row.getCell(9) == null) ? "" : row.getCell(9).getStringCellValue());
            item.setPassword((row.getCell(10) == null) ? "" : row.getCell(10).getStringCellValue());
            item.setReg_email((row.getCell(11) == null) ? "" : row.getCell(11).getStringCellValue());
            items.add(item);
        } else if (row.getRowNum() == 1) {//
            String name = (row.getCell(0) == null) ? "" : row.getCell(0).toString().trim();
            if (!"".equals(name)) {
                json.put("success", false);
                json.put("message", "?");
                break;
            }
        }
    }
    input.close();
    return items;
}

From source file:mask.logging.XLSLogger.java

protected void writeRow(String sheetName, Serializable... values) {
    Sheet sheet = sheetMap.get(sheetName);
    Row row = sheet.createRow(sheet.getLastRowNum() + 1);
    Cell cell;//from  w w  w  .  j  a  v a 2 s . c o  m
    for (int colnum = 0; colnum < values.length; colnum++) {
        cell = row.createCell(colnum);
        cell.setCellValue(values[colnum].toString());
    }

    if (sheet.getLastRowNum() % 100 == 0) {
        try {
            //((SXSSFSheet) sh).flushRows(100); // retain 100 last rows and flush all others

            ((SXSSFSheet) sheet).flushRows(1);
            //is a shortcut for ((SXSSFSheet)sh).flushRows(0),
            // this method flushes all rows
        } catch (IOException ex) {
            Logger.getLogger(LocalExecutor.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

From source file:massiveanalyserxryv2.ServiceTaskSearch.java

@Override
protected Task createTask() {
    Task task;//from w w  w .  j  av a 2s. c o  m
    task = new Task() {
        @Override
        protected Object call() throws Exception {
            // rcupration de la liste des mots cls
            // si il s'agit d'une base de donne slectionn dans la liste
            String path;
            if (modelDataSearch.getAbosoluthPathDb() == null) {
                path = System.getProperty("user.dir");
                path = path + "/db/";
                path = path + modelDataSearch.getNameDb();
            } else {
                // sinon on cre le path avec le chemin absolu (fichier import)
                path = modelDataSearch.getAbosoluthPathDb();
            }

            ArrayList<String> keyWords = new ArrayList<String>();
            keyWords.clear();
            // lecture
            for (String line : Files.readAllLines(Paths.get(path))) {
                keyWords.add(line);
            }

            // rcupration de la liste des contents du tableau excel
            Workbook book = WorkbookFactory.create(modelDataSearch.getFile());
            // rcupration du sheet
            Sheet sheet = book.getSheet(modelDataSearch.getNameSheet());
            // rcupration de la colonne
            int top = sheet.getFirstRowNum();
            int down = sheet.getLastRowNum();
            Row row = sheet.getRow(top);
            // on parse les column jusqu'a ce que le nom soit le meme que celui dans le modele
            short start = row.getFirstCellNum();
            short end = row.getLastCellNum();
            int indiceColumn = -1;
            for (short i = start; i <= end; i++) {
                if (row.getCell(i).getStringCellValue().equals(modelDataSearch.getNameColumn())) {
                    // on connait l'indice de column
                    indiceColumn = i;
                    break;
                }
            }

            // cration de la liste des contents
            ArrayList<DataContent> listContent = new ArrayList<DataContent>();
            listContent.clear();
            for (int j = top; j <= down; j++) {
                if (sheet.getRow(j) != null) {
                    if ((sheet.getRow(j).getCell(indiceColumn).getCellType() == CellType.STRING.getCode())) {
                        DataContent data = new DataContent(j,
                                sheet.getRow(j).getCell(indiceColumn).getStringCellValue());
                        listContent.add(data);
                    }
                }
            }

            // recherches
            for (DataContent content : listContent) {
                for (String key : keyWords) {
                    if (key.isEmpty())
                        continue;

                    int res = content.getContent().toLowerCase().indexOf(key.toLowerCase());
                    if (res != -1) {
                        DataResultat data = new DataResultat(content.getNumRow() + 1, content.getContent(),
                                key); // +1 car dans le fichie excel les row commence  1 et pas  0
                        ob.add(data);
                    }
                }
            }

            // Fermeture du workbook
            if (book != null)
                book.close();
            //tableauResultat.setItems(ob);
            return (Object) ob;

        }

    };

    return task;
}

From source file:math.page.KnapsackTest.java

License:Apache License

public static void test3() throws InvalidFormatException, IOException {
    String path = "d:" + File.separator + "price.xlsx";
    File file = new File(path);
    Workbook workbook = WorkbookFactory.create(file);
    Sheet sheet = workbook.getSheetAt(0);
    List<Knapsack> bags = new ArrayList<Knapsack>();
    try {//w  ww .j  av a 2  s. c  o  m
        for (int row = 1; row <= sheet.getLastRowNum(); row++) {
            Row row2 = sheet.getRow(row);
            Cell cell0 = row2.getCell(0);
            Cell cell1 = row2.getCell(1);
            // 
            // System.out.print(cell.toString() + "  ");
            // System.out.println(cell0.getCellComment().toString());

            // Integer integer = Double.valueOf(cell0.getNumericCellValue())
            // .intValue();
            Integer integer = null;
            switch (cell0.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell0)) {

                } else {
                    cell0.setCellType(Cell.CELL_TYPE_STRING);
                    String temp = cell0.getStringCellValue();
                    // ??????????Double
                    if (temp.indexOf(".") > -1) {
                        integer = Double.valueOf(temp).intValue();
                    } else {
                        integer = Integer.valueOf(temp).intValue();
                    }
                }
                break;
            case Cell.CELL_TYPE_STRING:
                integer = Integer.valueOf(cell0.getStringCellValue()).intValue();
                break;
            default:
                break;
            }
            Knapsack knapsack = new Knapsack(integer, integer);
            knapsack.setNo(Double.valueOf(cell1.getNumericCellValue()).intValue());

            bags.add(knapsack);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    List<Total> list = test4();

    write(list, bags);

}

From source file:math.page.KnapsackTest.java

License:Apache License

public static List<Total> test4() throws InvalidFormatException, IOException {
    String path = "d:" + File.separator + "total.xlsx";
    File file = new File(path);
    // Workbook workbook = Workbook.getWorkbook(file);
    Workbook workbook = WorkbookFactory.create(file);
    // Sheet sheet = workbook.getSheet(0);
    Sheet sheet = workbook.getSheetAt(0);
    List<Total> bags = new ArrayList<Total>();
    try {/*w  ww .j  a v a2s  . c  o  m*/

        for (int row = 1; row <= sheet.getLastRowNum(); row++) {
            // Cell[] cells = sheet.getRow(row);
            // System.out.println(cells[0].getContents());
            Row row2 = sheet.getRow(row);
            Cell cell0 = row2.getCell(0);
            Cell cell1 = row2.getCell(1);
            Integer integer = null;
            switch (cell0.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell0)) {

                } else {
                    cell0.setCellType(Cell.CELL_TYPE_STRING);
                    String temp = cell0.getStringCellValue();
                    // ??????????Double
                    if (temp.indexOf(".") > -1) {
                        integer = Double.valueOf(temp).intValue();
                    } else {
                        integer = Integer.valueOf(temp).intValue();
                    }
                }
                break;
            case Cell.CELL_TYPE_STRING:
                integer = Integer.valueOf(cell0.getStringCellValue()).intValue();
                break;
            default:
                break;
            }

            Total total = new Total();
            total.setNo(Double.valueOf(cell1.getNumericCellValue()).intValue());
            total.setTotal(integer);
            bags.add(total);
        }
        Arrays.sort(bags.toArray());
    } catch (Exception e) {
        e.printStackTrace();
    }

    return bags;
}

From source file:misuExcel.excelAdd.java

License:Open Source License

private void addTarget() {
    if (target != null) {
        if (saveType == 3) {
            Log.info(" ");
            Sheet sheet = target.getSheetAt(sheetNum_target);
            names = new ArrayList<String>();
            for (int i = addJpanel.ignore_Rowtar; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    Cell cell = row.getCell(cellNum_target);
                    if (cell != null) {
                        names.add(getCellString(cell));
                        Log.info(" " + getCellString(cell));
                    }//from  w w  w  . j  a  v  a2s .co  m
                }
            } //end for
        } else if (saveType == 4) {
            Log.info(" ");
            Sheet sheet = target.getSheetAt(sheetNum_target);
            names = new ArrayList<String>();
            Row row = sheet.getRow(cellNum_target);
            if (row != null) {
                for (int i = addJpanel.ignore_Celltar; i < row.getLastCellNum(); i++) {
                    Cell cell = row.getCell(i);
                    if (cell != null) {
                        names.add(getCellString(cell));
                        Log.info(" " + getCellString(cell));
                    }
                } //end for
            }
        }
        Log.info("names size:" + names.size());
        Log.info("splitTarget is already");
    }
}

From source file:misuExcel.excelAdd.java

License:Open Source License

private void examExcel() {
    Log.info("excamExcel");
    if (excel != null) {
        if (names != null && names.size() > 0) {
            Sheet sheet = excel.getSheetAt(sheetNum);
            initList(names.size());//w  w w  .j  av a  2  s  .com
            nones = new ArrayList<Integer>();
            Boolean isAdd = false;
            for (int j = addJpanel.ignore_Row; j <= sheet.getLastRowNum(); j++) {
                Row row = sheet.getRow(j);
                if (row != null) {
                    Cell cell = row.getCell(cellNum);
                    String str = getCellString(cell);
                    Log.info(" " + str);
                    for (int i = 0; i < names.size(); i++) {
                        if (str != null && str.equals(names.get(i))) {
                            isAdd = true;
                            addList.get(i).add(row.getRowNum());
                            break;
                        }
                    } //end names for   
                    if (!isAdd) {
                        nones.add(row.getRowNum());
                    }
                    isAdd = false;
                }
            } //end for
        } else {
            Log.info("target is none");
        }
    } else {
        Log.info("excel is not exit");
    }
}

From source file:misuExcel.excelRead.java

License:Open Source License

public int getSCellNum(int i) {
    if (i > -1) {
        Sheet sheet = (Sheet) wb.getSheetAt(i);
        int max = 0;
        for (int j = 0; j < sheet.getLastRowNum(); j++) {
            Row row = sheet.getRow(j);//  w  ww. ja v a2s .  com
            int r = row.getLastCellNum();
            if (r > max) {
                max = r;
            }
        }
        return max;
    }
    return 0;
}

From source file:misuExcel.excelRead.java

License:Open Source License

public int getSRowNum(int i) {
    if (i > -1) {
        Sheet sheet = (Sheet) wb.getSheetAt(i);
        if (sheet != null) {
            return (sheet.getLastRowNum() + 1);
        }//from   ww w. j  ava  2 s  .com
    }
    return 0;
}