Example usage for org.apache.poi.ss.usermodel Sheet 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:bad.robot.excel.row.Row.java

License:Apache License

public void insertAt(Workbook workbook, SheetIndex sheetIndex, RowIndex rowIndex) {
    Sheet sheet = workbook.getSheetAt(sheetIndex.value());
    sheet.shiftRows(rowIndex.value(), sheet.getLastRowNum(), shiftDownAmount);
    org.apache.poi.ss.usermodel.Row row = sheet.createRow(rowIndex.value());
    copyCellsTo(row, workbook);/*  ww w.j ava  2 s.  c  om*/
}

---

From source file:bad.robot.excel.row.Row.java

License:Apache License

private static org.apache.poi.ss.usermodel.Row createRow(Sheet sheet) {
    if (sheet.getPhysicalNumberOfRows() == 0)
        return sheet.createRow(0);
    return sheet.createRow(sheet.getLastRowNum() + 1);
}

---

From source file:biz.webgate.dominoext.poi.component.kernel.WorkbookProcessor.java

License:Apache License

public void findAndReplaceAll(Sheet sheet, String find, Object replace) {
    if (replace == null) {
        replace = "";
    }/*from  w w  w  .  j a v a2 s  .com*/
    int iLastRow = sheet.getLastRowNum();
    for (int i1 = 0; i1 < iLastRow; i1++) {
        Row currentRow = sheet.getRow(i1);
        if (currentRow != null) {
            int iLastCell = currentRow.getLastCellNum();
            for (int i = 0; i < iLastCell; i++) {
                Cell currentCell = currentRow.getCell(i);
                if (currentCell != null && currentCell.getCellType() == Cell.CELL_TYPE_STRING) {
                    if (currentCell.getStringCellValue().contains(find)) {
                        currentCell.setCellValue(currentCell.getStringCellValue().replace(find, "" + replace));
                    }
                }
            }
        }
    }
}

---

From source file:blanco.commons.calc.parser.AbstractBlancoCalcParser.java

License:Open Source License

/**
 * ???// w  ww . ja v a 2  s . c o m
 * 
 * @param sheet
 *            
 * @throws SAXException
 *             SAX????
 */
private final void parseSheet(final Sheet sheet) throws SAXException {
    // ?????
    AttributesImpl attrImpl = new AttributesImpl();
    attrImpl.addAttribute("", "name", "name", "CDATA", sheet.getSheetName());
    getContentHandler().startElement("", (String) getProperty(URI_PROPERTY_NAME_SHEET),
            (String) getProperty(URI_PROPERTY_NAME_SHEET), attrImpl);

    startSheet(sheet.getSheetName());

    //getLastRowNum()??0???? +1?
    int maxRows = sheet.getLastRowNum() + 1;

    for (int row = 0; row < maxRows; row++) {
        startRow(row + 1);
        Row line = sheet.getRow(row);
        if (line != null) {
            for (int column = 0; column < line.getLastCellNum(); column++) {

                startColumn(column + 1);
                Cell cell = line.getCell(column);
                // ?trim()??????????????
                String value = getCellValue(cell);
                fireCell(column + 1, row + 1, value);
                endColumn(column + 1);
            }
        }
        endRow(row + 1);
    }

    endSheet(sheet);

    // ?????
    getContentHandler().endElement("", (String) getProperty(URI_PROPERTY_NAME_SHEET),
            (String) getProperty(URI_PROPERTY_NAME_SHEET));
}

---

From source file:br.com.gartech.nfse.integrador.util.ExcelHelper.java

public Workbook bindXml(Document document, Workbook workbook) throws XPathExpressionException {

    XPath xPath = XPathFactory.newInstance().newXPath();
    NodeList cellValueList = (NodeList) xPath.evaluate("//cellValue", document, XPathConstants.NODESET);
    NodeList rowNodeList = (NodeList) xPath.evaluate("//row", document, XPathConstants.NODESET);
    Node rowsNode = (Node) xPath.evaluate("//rows", document, XPathConstants.NODE);

    Sheet sheet = workbook.getSheetAt(0);

    for (int i = 0; i < cellValueList.getLength(); i++) {
        Node cellValue = cellValueList.item(i);
        String cellName = cellValue.getAttributes().getNamedItem("ref").getTextContent();
        String type = cellValue.getAttributes().getNamedItem("type").getTextContent();
        String value = cellValue.getTextContent();
        CellReference cellRef = new CellReference(cellName);
        Row row = sheet.getRow(cellRef.getRow());
        Cell cell = row.getCell(cellRef.getCol());

        if ("number".equals(type)) {
            double doubleValue = Double.valueOf(value);
            cell.setCellValue(doubleValue);
        } else if ("date".equals(type)) {
            Date dateValue = new Date(Long.valueOf(value));
            cell.setCellValue(dateValue);
        } else if ("bool".equals(type)) {
            boolean boolValue = Boolean.valueOf(value);
            cell.setCellValue(boolValue);
        } else if ("formula".equals(type)) {
            cell.setCellFormula(value);/*from  w  w w.j a v  a2 s .com*/
        } else {
            cell.setCellValue(value);
        }
    }

    if (rowsNode != null && rowNodeList != null && rowNodeList.getLength() > 0) {
        CellReference startCellRef = new CellReference(
                rowsNode.getAttributes().getNamedItem("startRef").getTextContent());
        CellReference endCellRef = new CellReference(
                rowsNode.getAttributes().getNamedItem("endRef").getTextContent());
        int startRowIndex = startCellRef.getRow();
        int startColIndex = startCellRef.getCol();
        int endColIndex = endCellRef.getCol();
        CellStyle[] cellStyles = new CellStyle[endColIndex + 1];
        Row firstRow = sheet.getRow(startRowIndex);

        for (int i = startColIndex; i <= endColIndex; i++) {
            cellStyles[i] = firstRow.getCell(i).getCellStyle();
        }

        for (int i = startRowIndex; i <= sheet.getLastRowNum(); i++) {
            Row templeteRow = sheet.getRow(i);

            if (templeteRow != null) {
                sheet.removeRow(templeteRow);
            }
        }

        int rowNodeIndex = 0;

        for (int i = startRowIndex; i < startRowIndex + rowNodeList.getLength(); i++) {

            Row row = sheet.createRow(i);
            int cellNodeIndex = 0;
            Node rowNode = rowNodeList.item(rowNodeIndex);
            NodeList rowValueNodeList = rowNode.getChildNodes();
            ArrayList<Node> nodes = new ArrayList<Node>();

            for (int idx = 0; idx < rowValueNodeList.getLength(); idx++) {
                Node currentNode = rowValueNodeList.item(idx);
                if (currentNode.getNodeType() == Node.ELEMENT_NODE) {
                    nodes.add(currentNode);
                }
            }

            for (int j = startColIndex; j <= endColIndex; j++) {
                Cell cell = row.createCell(j);
                Node cellNode = nodes.get(cellNodeIndex);
                String type = cellNode.getAttributes().getNamedItem("type").getTextContent();
                String value = cellNode.getTextContent();
                CellStyle cellStyle = cellStyles[j];

                cell.setCellStyle(cellStyle);

                if ("number".equals(type)) {
                    double doubleValue = Double.valueOf(value);
                    cell.setCellValue(doubleValue);
                } else if ("date".equals(type)) {
                    Date dateValue = new Date(Long.valueOf(value));
                    cell.setCellValue(dateValue);
                } else if ("bool".equals(type)) {
                    boolean boolValue = Boolean.valueOf(value);
                    cell.setCellValue(boolValue);
                } else if ("formula".equals(type)) {
                    cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
                    cell.setCellFormula(value);
                } else if ("string".equals(type)) {
                    if (value != null && value.length() > 0) {
                        cell.setCellValue(value);
                    } else {
                        cell.setCellValue("");
                    }
                } else {
                    cell.setCellValue("");
                }

                cellNodeIndex++;
            }
            rowNodeIndex++;
        }
    }

    return workbook;
}

---

From source file:br.ufal.cideei.util.count.SummaryBuilder.java

License:Open Source License

public static void buildSummary(String splShortName)
        throws InvalidFormatException, FileNotFoundException, IOException {

    // final String userHomeFolder = System.getProperty("user.home").substring(3);
    String userHomeFolder = "C:\\tst";
    final String output = userHomeFolder + File.separator + "summ.xls";
    File outputFile = new File(output);
    Workbook outputWorkbook;//from   w w w . ja  va2s.  co  m
    if (!outputFile.exists()) {
        outputFile.createNewFile();
        outputWorkbook = new HSSFWorkbook();
    } else {
        FileInputStream inputFileStream = new FileInputStream(outputFile);
        outputWorkbook = WorkbookFactory.create(inputFileStream);
    }

    {
        List<String> referencesForRDA3 = new ArrayList<String>();
        List<String> referencesForUVA3 = new ArrayList<String>();
        List<String> referencesForRDA2 = new ArrayList<String>();
        List<String> referencesForUVA2 = new ArrayList<String>();
        String fileName = "fs-" + splShortName + ".xls";
        String filePath = userHomeFolder + File.separator;
        String fullFileName = filePath + File.separator + "fs-" + splShortName + ".xls";
        Workbook workbook = WorkbookFactory.create(new FileInputStream(new File(fullFileName)));
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            Sheet sheet = workbook.getSheetAt(i);
            Row headerRow = sheet.getRow(0);
            for (Cell cell : headerRow) {
                String stringCellValue = cell.getStringCellValue();
                if (stringCellValue.equals("rd")) {
                    Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1);
                    Cell sumCell = sumRow.getCell(i);
                    CellReference sumCellRef = new CellReference(sumCell);
                    String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!"
                            + sumCellRef.formatAsString();
                    referencesForRDA2.add(cellRefForAnotherSheet);
                } else if (stringCellValue.equals("uv")) {
                    Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1);
                    Cell sumCell = sumRow.getCell(i);
                    CellReference sumCellRef = new CellReference(sumCell);
                    String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!"
                            + sumCellRef.formatAsString();
                    referencesForUVA2.add(cellRefForAnotherSheet);
                } else if (stringCellValue.equals("rd (a3)")) {
                    Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1);
                    Cell sumCell = sumRow.getCell(i);
                    CellReference sumCellRef = new CellReference(sumCell);
                    String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!"
                            + sumCellRef.formatAsString();
                    referencesForRDA3.add(cellRefForAnotherSheet);
                } else if (stringCellValue.equals("uv (a3)")) {
                    Row sumRow = sheet.getRow(sheet.getLastRowNum() - 1);
                    Cell sumCell = sumRow.getCell(i);
                    CellReference sumCellRef = new CellReference(sumCell);
                    String cellRefForAnotherSheet = "\'" + filePath + "[" + fileName + "]Sheet" + i + "\'!"
                            + sumCellRef.formatAsString();
                    referencesForUVA3.add(cellRefForAnotherSheet);
                }
            }
        }
        if (outputWorkbook.getSheet(splShortName) != null) {
            outputWorkbook.removeSheetAt(outputWorkbook.getSheetIndex(splShortName));
        }
        Sheet outputSheet = outputWorkbook.createSheet(splShortName);
        Row RDA2Row = outputSheet.createRow(0);
        RDA2Row.createCell(0).setCellValue("RD A2");
        for (int i = 0; i < referencesForRDA2.size(); i++) {
            Cell createdCell = RDA2Row.createCell(i + 1);
            System.out.println(referencesForRDA2.get(i));
            createdCell.setCellType(Cell.CELL_TYPE_FORMULA);
            createdCell.setCellValue(referencesForRDA2.get(i));
        }
        Row UVA2Row = outputSheet.createRow(1);
        UVA2Row.createCell(0).setCellValue("UV A2");
        for (int i = 0; i < referencesForUVA2.size(); i++) {
            Cell createdCell = UVA2Row.createCell(i + 1);
            createdCell.setCellFormula(referencesForUVA2.get(i));
        }
        Row RDA3Row = outputSheet.createRow(2);
        RDA3Row.createCell(0).setCellValue("RD A3");
        for (int i = 0; i < referencesForRDA3.size(); i++) {
            Cell createdCell = RDA3Row.createCell(i + 1);
            createdCell.setCellFormula(referencesForRDA3.get(i));
        }
        Row UVA3Row = outputSheet.createRow(3);
        UVA3Row.createCell(0).setCellValue("UV A3");
        for (int i = 0; i < referencesForUVA3.size(); i++) {
            Cell createdCell = UVA3Row.createCell(i + 1);
            createdCell.setCellFormula(referencesForUVA3.get(i));
        }
    }
    FileOutputStream fileOutputStream = new FileOutputStream(outputFile);
    outputWorkbook.write(fileOutputStream);
    fileOutputStream.close();
}

---

From source file:Categorization.CategoriesLoader.java

License:Open Source License

public static void echoAsCSV(Sheet sheet) throws IOException {
    Row row;/*  w  w  w  . ja v  a  2 s . com*/
    int startingRow = 2;
    Category category;
    boolean breakNow = false;
    for (int i = startingRow; i <= sheet.getLastRowNum(); i++) {
        if (breakNow) {
            break;
        }
        row = sheet.getRow(i);
        if (row == null) {
            break;
        }
        category = new Category();

        for (int j = 1; j < row.getLastCellNum(); j++) {

            //label of the category
            if (j == 1) {
                if (row.getCell(j).getStringCellValue().isEmpty()
                        || row.getCell(j).getStringCellValue() == null) {
                    breakNow = true;
                    break;
                }
                category.setCategoryName("CAT_" + row.getCell(j).getStringCellValue());
            }
            //if a cell is null, the row is empty.
            if (row.getCell(j) == null) {
                continue;
            }

            //check the keywords
            if (j > 1 & j <= maxColKeyWords) {
                System.out.println(row.getCell(j).getStringCellValue());
                if (row.getCell(j).getStringCellValue().startsWith("NOT ")) {
                    category.addExclusionKeyword(
                            row.getCell(j).getStringCellValue().toLowerCase().substring(4).trim());
                } else {
                    category.addKeyword(row.getCell(j).getStringCellValue().toLowerCase().trim());
                }
            }
            //check the min number of keywords that should be present in the text to match a classification
            if (j == maxColKeyWords + 1) {
                System.out.println("min Words: " + row.getCell(j).getStringCellValue());
                category.setMinNumberKeywords(Integer.parseInt(row.getCell(j).getStringCellValue()));
            }

            //check if single terms can lead to a direct classification
            if (j == maxColKeyWords + 2) {
                String[] directWords = row.getCell(j).getStringCellValue().split(";");
                for (String string : directWords) {
                    category.addDecisiveKeyword(string.toLowerCase().trim());
                }
            }
            //what supercategory the category belongs to
            if (j == maxColKeyWords + 3) {
                category.setSuperCategory(row.getCell(j).getStringCellValue());
            }

        }
        if (!category.getMinNumberKeywords().equals(0)) {
            categories.add(category);
        }
    }
}

---

From source file:ch.oakmountain.tpa.parser.TpaParser.java

License:Apache License

private void emptyWorksheetColumns(String wsName, int rowsFrom, List<Integer> colsToDelete) {
    String trainpathsNoOperatingDayMarker = getPropertyValue(tpaProps.TRAINPATHS_NO_OPERATING_DAY_MARKER);

    Sheet sheet = wb.getSheet(wsName);
    for (int i = rowsFrom; i <= sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);//  w ww .  j av a2s.  co  m
        for (Integer j : colsToDelete) {
            Cell cell = row.getCell(j);
            if (cell != null && !formatter.formatCellValue(cell).equals(trainpathsNoOperatingDayMarker)) {
                cell.setCellValue((String) null);
            }
        }
    }
}

---

From source file:ch.oakmountain.tpa.parser.TpaParser.java

License:Apache License

/**
 * @param wsName/*w  w w  .  j a v  a2  s .  co m*/
 * @param rowsFrom
 * @param colsToDelete
 */
private void resetRequestAllocations(String wsName, int rowsFrom, List<Integer> colsToDelete) {
    String requestsAllocatedDayMarker = getPropertyValue(tpaProps.REQUESTS_ALLOCATED_DAY_MARKER);
    String requestsRequestedDayMarker = getPropertyValue(tpaProps.REQUESTS_REQUESTED_DAY_MARKER);

    Sheet sheet = wb.getSheet(wsName);
    for (int i = rowsFrom; i <= sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);
        for (Integer j : colsToDelete) {
            Cell cell = row.getCell(j);
            if (cell != null && formatter.formatCellValue(cell).equals(requestsAllocatedDayMarker)) {
                cell.setCellValue(requestsRequestedDayMarker);
            }
        }
    }
}

---

From source file:ch.oakmountain.tpa.parser.TpaParser.java

License:Apache License

/**
 * Put the train path ids in the format <train_path_slot_id><hour_of_day_>-<three_digit_sequence_number_within_hour>
 *
 * @param wsName/*  ww w  .jav a 2  s. c o  m*/
 * @param rowsFrom
 * @param cols
 */
private void correctTrainPathIds(String wsName, int rowsFrom, Map<ColumnIdentifier, Integer> cols) {

    Sheet sheet = wb.getSheet(wsName);
    for (int i = rowsFrom; i <= sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);
        if (row == null) {
            continue;
        }

        Map<ColumnIdentifier, String> line = getWorksheetPointerStringMap(cols, row);
        String uncorrectedSlotName = line.get(trainPathLayout.ID);
        if (StringUtils.isBlank(uncorrectedSlotName)) {
            continue;
        }
        try {
            LocalTime startTime = LocalTime.parse(line.get(trainPathLayout.DEPTIME));

            String correctedSlotName = getNextSlotId(wsName, startTime.getHourOfDay());
            if (!correctedSlotName.equals(uncorrectedSlotName)) {
                LOGGER.warn("Correcting slot name " + uncorrectedSlotName + " => " + correctedSlotName);
                row.getCell(cols.get(trainPathLayout.ID)).setCellValue(correctedSlotName);
            }
        } catch (IllegalArgumentException e) {
            LOGGER.warn(corrupt_input,
                    "Illegal start time \"" + line.get(trainPathLayout.DEPTIME) + "\" for slot "
                            + uncorrectedSlotName + " in sheet " + wsName + " found; skipping this slot.",
                    e);
            continue;
        }
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »