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:generate.CopyRow.java

/**
 * Copies a row from a row index on the given workbook and sheet to another row index. If the destination row is
 * already occupied, shift all rows down to make room.
 *
 * @param workbook/* w  ww .  j av  a 2 s  .  c o  m*/
 * @param worksheet
 * @param from
 * @param to
 */
public static void copyRow(Workbook workbook, Sheet worksheet, Integer from, Integer to) {
    Row sourceRow = worksheet.getRow(from);
    Row newRow = worksheet.getRow(to);

    if (alreadyExists(newRow))
        worksheet.shiftRows(to, worksheet.getLastRowNum(), 1, true, true);
    else {
        newRow = worksheet.createRow(to);
    }
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        Cell oldCell = sourceRow.getCell(i);
        Cell newCell = newRow.createCell(i);
        if (oldCell != null) {
            copyCellStyle(workbook, oldCell, newCell);
            copyCellComment(oldCell, newCell);
            copyCellHyperlink(oldCell, newCell);
            copyCellDataTypeAndValue(oldCell, newCell);
        }
    }
    copyAnyMergedRegions(worksheet, sourceRow, newRow);
}

---

From source file:gov.nih.nci.cananolab.util.ExcelParser.java

License:BSD License

/**
 * Parse secondary StanShaw Excel spreadsheet and store data in a 3-layer map.
 * 1st layer: sample map, key is sample name (261-13-4), value is the 2nd layer map.
 * 2nd layer: assay map, key is assay name (Aorta 1), value is the 3rd layer map.
 * 3rd layer: datum map, there are always 3 entries in this map, for example,  
 *            key is datum name Median (M), value is 9.02194E-08.
 *            key is datum name Mean (M), value is 7.96025E-08.
 *            key is datum name SEM (M), value is 6.12968E-09.
 *  /*  w ww. jav a 2s  .  c  om*/
 * @param fileName
 * @return a 3-layer map
 * @throws IOException
 */
public SortedMap<String, SortedMap<String, SortedMap<String, Double>>> twoWayParse(String fileName)
        throws IOException {
    InputStream inputStream = null;
    SortedMap<String, SortedMap<String, SortedMap<String, Double>>> dataMatrix = new TreeMap<String, SortedMap<String, SortedMap<String, Double>>>();
    try {
        inputStream = new BufferedInputStream(new FileInputStream(fileName));
        POIFSFileSystem fs = new POIFSFileSystem(inputStream);
        Workbook wb = new HSSFWorkbook(fs);
        Sheet sheet1 = wb.getSheetAt(0);
        //printSheet(sheet1);
        // Sheet must contain >= 2 rows (header + data).
        if (sheet1.getLastRowNum() < 1) {
            return dataMatrix;
        }
        // Sheet must contain >= 5 columns (assay, sample + 3 datums).   
        Row firstRow = sheet1.getRow(0);
        if (firstRow.getLastCellNum() < 4) {
            return dataMatrix;
        }
        // Iterate sheet from 2nd row and populate the data matrix.
        for (int rowIndex = 1; rowIndex <= sheet1.getLastRowNum(); rowIndex++) {
            Row row = sheet1.getRow(rowIndex);

            //1.get sampleName key for 1st layer map, assayName key for 2 layer map.
            String sampleName = row.getCell(1).getStringCellValue();
            String assayName = row.getCell(0).getStringCellValue();

            //2.find sampleMap in dataMatrix, if null create & store new sampleMap.
            SortedMap<String, SortedMap<String, Double>> sampleMap = dataMatrix.get(sampleName);
            if (sampleMap == null) {
                sampleMap = new TreeMap<String, SortedMap<String, Double>>();
                dataMatrix.put(sampleName, sampleMap);
            }

            //3.find assayMap in sampleMap, if null create & store new assayMap.
            SortedMap<String, Double> assayMap = sampleMap.get(assayName);
            if (assayMap == null) {
                assayMap = new TreeMap<String, Double>();
                sampleMap.put(assayName, assayMap);
            }

            //4.iterate row from col-2 to last column, store datum value.
            for (int colIndex = 2; colIndex <= row.getLastCellNum(); colIndex++) {
                Cell cell = row.getCell(colIndex);
                if (cell != null && cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    String datumName = firstRow.getCell(colIndex).getStringCellValue();
                    assayMap.put(datumName, cell.getNumericCellValue());
                }
            }
        }
    } finally {
        if (inputStream != null) {
            try {
                inputStream.close();
            } catch (Exception e) {
            }
        }
        //this.print2ndMatrix(dataMatrix);
    }
    return dataMatrix;
}

---

From source file:gov.nih.nci.evs.app.neopl.ExcelToCSV.java

License:Open Source License

private void convertToCSV() {
    Sheet sheet = null;
    Row row = null;/*from  www.  ja  v  a2s.  c  om*/
    int lastRowNum = 0;
    this.csvData = new ArrayList<ArrayList<String>>();

    System.out.println("Converting files contents to CSV format.");

    int numSheets = this.workbook.getNumberOfSheets();
    for (int i = 0; i < numSheets; i++) {
        sheet = this.workbook.getSheetAt(i);
        if (sheet.getPhysicalNumberOfRows() > 0) {
            lastRowNum = sheet.getLastRowNum();
            /*
            for(int j = 0; j <= lastRowNum; j++) {
            row = sheet.getRow(j);
            this.rowToCSV(row);
            }
            */
            for (int j = 1; j <= lastRowNum; j++) {
                row = sheet.getRow(j);
                this.rowToCSV(row);
            }
        }
        //sheet.createFreezePane(0,1); // this will freeze the header row
    }

}

---

From source file:gov.nij.er.ui.EntityResolutionDemo.java

License:Apache License

private void loadExcelData(File file) throws Exception {

    LOG.debug("Loading Excel data file " + file.getAbsolutePath());

    InputStream inp = new FileInputStream(file);
    Workbook wb = WorkbookFactory.create(inp);

    // note that we read all the data out of the spreadsheet first, then
    // update the models. this way if there is
    // an error, we don't wipe out what the user already has.

    Sheet sheet = wb.getSheetAt(0);
    Row parametersRow = sheet.getRow(0);
    List<String> parameterNames = new ArrayList<String>();
    for (Cell cell : parametersRow) {
        String v = cell.getStringCellValue();
        if (parameterNames.contains(v)) {
            error("Duplicate field: " + v);
            return;
        }//from  w ww  . j a  v a  2  s  .c  om
        parameterNames.add(v);
        LOG.debug("Adding parameter " + v);
    }

    int parameterCount = parameterNames.size();

    LOG.debug("Excel loading read " + parameterCount + " parameters");

    List<ExternallyIdentifiableRecord> records = new ArrayList<ExternallyIdentifiableRecord>();

    int rowCount = sheet.getLastRowNum();
    LOG.debug("Loading " + (rowCount - 1) + " rows from " + sheet.getSheetName());

    int digits = (int) (Math.floor(Math.log10(rowCount)) + 1);

    DataFormatter dataFormatter = new DataFormatter();

    for (int rowIndex = 1; rowIndex <= rowCount; rowIndex++) {
        List<Attribute> attributes = new ArrayList<Attribute>(parameterCount);
        Row row = sheet.getRow(rowIndex);
        for (int i = 0; i < parameterCount; i++) {
            Cell cell = row.getCell(i);
            String v = dataFormatter.formatCellValue(cell);
            String parameterName = parameterNames.get(attributes.size());
            attributes.add(new Attribute(parameterName, v));
            // LOG.debug("Adding attribute, name=" + parameterName + ", v="
            // + (v==null ? "null" : "'" + v + "'"));
        }
        records.add(new ExternallyIdentifiableRecord(makeAttributes(attributes.toArray(new Attribute[] {})),
                String.format("%0" + digits + "d", rowIndex)));
    }

    LOG.debug("Read " + records.size() + " records from Excel");

    List<RecordWrapper> recordWrappers = EntityResolutionConversionUtils.convertRecords(records);
    rawDataTreeModel.init(recordWrappers);

    parametersTableModel.clear();
    parametersTableModel.addParameters(parameterNames);

}

---

From source file:gov.va.isaac.isaacDbProcessingRules.spreadsheet.SpreadsheetReader.java

License:Apache License

public List<RuleDefinition> readSpreadSheet(InputStream is) throws IOException {
    XSSFWorkbook ss = new XSSFWorkbook(is);

    Sheet sheet = ss.getSheetAt(1);

    int i = readHeaders(sheet);

    for (; i <= sheet.getLastRowNum(); i++) {
        Row r = sheet.getRow(i);/*  w w  w. ja v a  2  s . co m*/

        if (r != null) {
            ArrayList<Cell> values = new ArrayList<>();

            for (int col = 0; col < columnHeaders.size(); col++) {
                values.add(r.getCell(col));
            }
            data.add(values);
        }
    }

    //Have read the entire spreadsheet - now process into our 'rule' format

    ArrayList<RuleDefinition> result = new ArrayList<>();

    for (int rowNum = 0; rowNum <= data.size(); rowNum++) {
        RuleDefinition rd = new RuleDefinition();
        Integer id = readIntColumn(rowNum, "ID");
        if (id == null) {
            //blank row?
            continue;
        }
        rd.id = id;
        rd.date = readDateColumn(rowNum, version == 1 ? "Date" : "Timestamp");

        rd.action = Action.parse(readStringColumn(rowNum, "Action"));
        rd.sctFSN = readStringColumn(rowNum, version == 1 ? "SCT FSN" : "FSN");
        try {
            rd.sctID = readLongColumn(rowNum, version == 1 ? "SCT ID" : "SCT_ID");
        } catch (IllegalStateException e) {
            String temp = readStringColumn(rowNum, version == 1 ? "SCT ID" : "SCT_ID");
            if (temp != null) {
                rd.sctID = Long.parseLong(temp);
            }
        }
        rd.author = readStringColumn(rowNum, "Author");
        if (version == 1) {
            rd.comments = readStringColumn(rowNum, "Comments");
        }

        ArrayList<SelectionCriteria> criteria = new ArrayList<>();

        while (true) {
            SelectionCriteria sc = new SelectionCriteria();
            if (version == 1) {
                sc.operand = readOperand(rowNum);
                sc.type = SelectionCriteriaType.parse(readStringColumn(rowNum, "Type"));
            } else {
                sc.type = SelectionCriteriaType.RXCUI;
            }
            try {
                //If we read a long, as a string, we get an extra .0 on the end - so read as a long first, if it is one.
                sc.value = readLongColumn(rowNum, version == 1 ? "Value" : "RXCUI").toString();
            } catch (IllegalStateException e) {
                sc.value = readStringColumn(rowNum, version == 1 ? "Value" : "RXCUI");
            }
            if (version == 1) {
                sc.valueId = readStringColumn(rowNum, "Value ID");
            }

            criteria.add(sc);
            //peak at the next row, see if it is an additional criteria, or a new rule
            Integer nextId = readIntColumn(rowNum + 1, "ID"); //if the next row has an id, its a new rule
            String nextType = readStringColumn(rowNum + 1, "Type"); //check to see if we hit the end of the rows
            if (nextId != null || nextType == null) {
                break;
            } else //more criteria for this rule
            {
                rowNum++;
            }
        }

        rd.criteria = criteria;
        result.add(rd);
    }
    ss.close();
    return result;
}

---

From source file:gov.va.isaac.request.uscrs.USCRSBatchTemplate.java

License:Apache License

/**
 * Generate the enums and constants from a template file for use at the top of this class
 *///from ww  w . j a v a 2 s  .c  om
public static void main(String[] args) throws IOException {
    //USCRSBatchTemplate b = new USCRSBatchTemplate(USCRSBatchTemplate.class.getResourceAsStream("/USCRS_Batch_Template-2015-01-27.xls"));

    Workbook wb = new HSSFWorkbook(
            USCRSBatchTemplate.class.getResourceAsStream("/USCRS_Batch_Template-2015-01-27.xls"));

    ArrayList<String> sheets = new ArrayList<>();
    HashSet<String> columns = new HashSet<>();

    LinkedHashMap<String, ArrayList<String>> pickLists = new LinkedHashMap<>();

    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);
        String sheetName = sheet.getSheetName();
        sheets.add(sheetName);
        if (sheetName.equals("Help")) {
            continue;
        }

        sheet.getRow(0).forEach(headerCell -> {
            if (sheetName.equals("metadata") && headerCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                //SKIP - the metadata tab has a cell that is just a number - likely the release date
            } else {
                String stringValue = toString(headerCell);
                columns.add(stringValue);
                if (sheetName.equals("metadata")) {
                    pickLists.put(stringValue, new ArrayList<>());
                    for (int row = 1; row < sheet.getLastRowNum(); row++) {
                        Cell valueCell = sheet.getRow(row).getCell(headerCell.getColumnIndex());
                        if (valueCell != null) {
                            String s = toString(valueCell);
                            if (s.length() > 0) {
                                pickLists.get(stringValue).add(s);
                            }
                        }
                    }
                }
            }
        });
    }

    String eol = System.getProperty("line.separator");

    StringBuilder sb = new StringBuilder();
    int i = 0;
    sb.append("public enum SHEET {");
    for (String s : sheets) {
        sb.append(enumSafeCharExchange(s));
        sb.append(", ");
        i++;
        if (i % 8 == 0) {
            i = 0;
            sb.append(eol);
        }
    }
    sb.setLength(sb.length() - (i == 0 ? 3 : 2));
    sb.append("};");
    System.out.println(sb);
    System.out.println();

    sb.setLength(0);
    i = 0;
    sb.append("public enum COLUMN {");
    for (String c : columns) {
        sb.append(enumSafeCharExchange(c));
        sb.append(", ");
        i++;
        if (i % 8 == 0) {
            i = 0;
            sb.append(eol);
        }
    }
    sb.setLength(sb.length() - (i == 0 ? 3 : 2));
    sb.append("};");
    System.out.println(sb);
    sb.setLength(0);
    i = 0;

    for (Entry<String, ArrayList<String>> x : pickLists.entrySet()) {
        sb.append("public enum PICKLIST_");
        sb.append(enumSafeCharExchange(x.getKey()));
        sb.append(" {");
        for (String s : x.getValue()) {
            sb.append(enumSafeCharExchange(s));
            sb.append("(\"");
            sb.append(s);
            sb.append("\")");
            sb.append(", ");
            i++;
            if (i % 2 == 0) {
                i = 0;
                sb.append(eol);
            }
        }
        sb.setLength(sb.length() - (i == 0 ? 3 : 2));
        sb.append(";" + eol);
        sb.append("\tprivate String value;" + eol + eol);
        sb.append("\tprivate PICKLIST_" + enumSafeCharExchange(x.getKey()) + " (String pickListValue)" + eol);
        sb.append("\t{" + eol);
        sb.append("\t\tvalue = pickListValue;" + eol);
        sb.append("\t}" + eol);

        sb.append("" + eol);
        sb.append("\t@Override" + eol);
        sb.append("\tpublic String toString()" + eol);
        sb.append("\t{" + eol);
        sb.append("\t\treturn value;" + eol);
        sb.append("\t}" + eol);

        sb.append("" + eol);
        sb.append("\tpublic static PICKLIST_" + enumSafeCharExchange(x.getKey()) + " find(String value)" + eol);
        sb.append("\t{" + eol);
        sb.append("\t\treturn PICKLIST_" + enumSafeCharExchange(x.getKey())
                + ".valueOf(enumSafeCharExchange(value));" + eol);
        sb.append("\t}" + eol);
        sb.append("};");

        System.out.println(sb);
        sb.setLength(0);
        i = 0;
        System.out.println();
    }
}

---

From source file:gridgrid.Web.java

License:Apache License

private synchronized void load(File file) throws IOException {
    if (file.lastModified() > lastModified) {
        map = new HashMap<>();
        InputStream is = new FileInputStream(file);
        Workbook book = new XSSFWorkbook(is);
        Sheet sheet = book.getSheetAt(0);
        int pathCelNum = -1;
        int scriptCellNum = -1;
        int viewCellNum = -1;

        for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row != null) {
                if (row.getLastCellNum() >= 1 && pathCelNum == -1) {
                    for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
                        Cell cell = row.getCell(cellIndex);
                        if (cell != null) {
                            switch (cell.getStringCellValue()) {
                            case "":
                                pathCelNum = cellIndex;
                                break;
                            case "JavaScript":
                                scriptCellNum = cellIndex;
                                break;
                            case "":
                                viewCellNum = cellIndex;
                                break;
                            }//from   www.  j a  v a2 s . c om
                        }
                    }

                }

                if (pathCelNum != -1 && row.getCell(pathCelNum) != null && row.getCell(scriptCellNum) != null
                        && row.getCell(viewCellNum) != null) {
                    Cell code = row.getCell(scriptCellNum);
                    String codeStr = code != null ? code.toString() : "";
                    Cell view = row.getCell(viewCellNum);
                    String viewStr = view != null ? view.toString() : "";
                    String path = row.getCell(pathCelNum).toString();
                    map.put(path, new CodeView(codeStr, viewStr));
                }
            }
        }
        is.close();
        lastModified = file.lastModified();
    }
}

---

From source file:guru.qas.martini.report.DefaultState.java

License:Apache License

@Override
public void updateSuites(Sheet sheet) {
    int lastRowNum = sheet.getLastRowNum();
    Row row = sheet.createRow(0 == lastRowNum ? 0 : lastRowNum + 1);
    row.createCell(0, CellType.STRING).setCellValue("ID");
    row.createCell(1, CellType.STRING).setCellValue("Date");
    row.createCell(2, CellType.STRING).setCellValue("Name");
    row.createCell(3, CellType.STRING).setCellValue("Hostname");
    row.createCell(4, CellType.STRING).setCellValue("IP");
    row.createCell(5, CellType.STRING).setCellValue("Username");
    row.createCell(6, CellType.STRING).setCellValue("Profiles");
    row.createCell(7, CellType.STRING).setCellValue("Environment Variables");

    for (Map.Entry<String, JsonObject> mapEntry : suites.entrySet()) {
        row = sheet.createRow(sheet.getLastRowNum() + 1);

        String id = mapEntry.getKey();
        row.createCell(0, CellType.STRING).setCellValue(id);

        JsonObject suite = mapEntry.getValue();
        JsonPrimitive primitive = suite.getAsJsonPrimitive("startTimestamp");
        Long timestamp = null == primitive ? null : primitive.getAsLong();

        Cell cell = row.createCell(1);/*w w w. j a  va2  s .c  om*/
        if (null != timestamp) {
            Workbook workbook = sheet.getWorkbook();
            CellStyle cellStyle = workbook.createCellStyle();
            CreationHelper creationHelper = workbook.getCreationHelper();
            cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("m/d/yy h:mm"));
            cellStyle.setVerticalAlignment(VerticalAlignment.TOP);
            cell.setCellValue(new Date(timestamp));
            cell.setCellStyle(cellStyle);
        }

        cell = row.createCell(2);
        primitive = suite.getAsJsonPrimitive("name");
        cell.setCellValue(null == primitive ? "" : primitive.getAsString());

        cell = row.createCell(3);
        JsonObject host = suite.getAsJsonObject("host");
        primitive = null == host ? null : host.getAsJsonPrimitive("name");
        cell.setCellValue(null == primitive ? "" : primitive.getAsString());

        cell = row.createCell(4);
        primitive = null == host ? null : host.getAsJsonPrimitive("ip");
        cell.setCellValue(null == primitive ? "" : primitive.getAsString());

        cell = row.createCell(5);
        primitive = null == host ? null : host.getAsJsonPrimitive("username");
        cell.setCellValue(null == primitive ? "" : primitive.getAsString());

        cell = row.createCell(6);
        JsonArray array = suite.getAsJsonArray("profiles");
        List<String> profiles = Lists.newArrayList();
        if (null != array) {
            int size = array.size();
            for (int i = 0; i < size; i++) {
                JsonElement element = array.get(i);
                String profile = null == element ? null : element.getAsString();
                profiles.add(profile);
            }
            String profilesValue = Joiner.on('\n').skipNulls().join(profiles);
            cell.setCellValue(profilesValue);
        }

        cell = row.createCell(7);
        JsonObject environmentVariables = suite.getAsJsonObject("environment");
        Map<String, String> index = new TreeMap<>();
        if (null != environmentVariables) {
            Set<Map.Entry<String, JsonElement>> entries = environmentVariables.entrySet();
            for (Map.Entry<String, JsonElement> environmentEntry : entries) {
                String key = environmentEntry.getKey();
                JsonElement element = environmentEntry.getValue();
                String value = null == element ? "" : element.getAsString();
                index.put(key, value);
            }

            String variablesValue = Joiner.on('\n').withKeyValueSeparator('=').useForNull("").join(index);
            cell.setCellValue(variablesValue);
        }
    }

    for (int i = 0; i < 8; i++) {
        sheet.autoSizeColumn(i, false);
    }
}

---

From source file:guru.qas.martini.report.DefaultTraceabilityMatrix.java

License:Apache License

protected void addResult(State state, Sheet sheet, JsonObject object) {
    int index = sheet.getLastRowNum();
    Row row = sheet.createRow(index + 1);

    for (int i = 0; i < columns.size(); i++) {
        Cell cell = row.createCell(i);// w  w  w. j a  v a2 s .co  m
        CellStyle cellStyle = cell.getCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.TOP);
        TraceabilityColumn column = columns.get(i);
        column.addResult(state, cell, object);
    }
}

---

From source file:hu.webhejj.commons.io.table.excel.ExcelSheet.java

License:Apache License

@Override
public List<Row> getRows() {
    return new AbstractList<TableReader.Row>() {

        @Override//from w w  w .j a va  2  s . c o m
        public Row get(int index) {
            return getRow(index);
        }

        @Override
        public int size() {
            Sheet sheet = getSheet();
            int i = sheet.getLastRowNum();
            if (i == 0) {
                return sheet.getPhysicalNumberOfRows() == 0 ? 0 : 1;
            }
            return i + 1;
        }
    };
}

---

• «
• 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
• »