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:org.squashtest.tm.service.internal.importer.ExcelTestCaseParserImpl.java

License:Open Source License

@Override
public TestCase parseFile(Workbook workbook, ImportSummaryImpl summary) throws SheetCorruptedException {

    PseudoTestCase pseudoTestCase = new PseudoTestCase();

    Sheet sheet = workbook.getSheetAt(0);

    for (int r = 0; r <= sheet.getLastRowNum(); r++) {
        Row row = sheet.getRow(r);//from w  w  w.  j av  a  2s . co  m
        parseRow(row, pseudoTestCase);
    }

    return generateTestCase(pseudoTestCase, summary);

}

---

From source file:org.strasa.middleware.manager.CreateFieldBookManagerImpl.java

License:Open Source License

/**
 * Generate variate.// www . j  ava2 s  .  co m
 * 
 * @param sheet
 *            the sheet
 * @param lstSiteInfo
 *            the lst site info
 * @throws CreateFieldBookException
 *             the create field book exception
 * @throws Exception
 *             the exception
 */
public void generateVariate(Sheet sheet, List<SiteInformationModel> lstSiteInfo)
        throws CreateFieldBookException, Exception {

    HashSet<String> lstSet = new HashSet<String>();
    for (SiteInformationModel siteInfo : lstSiteInfo) {
        for (StudyVariable var : siteInfo.lstStudyVariable) {
            lstSet.add(var.getVariablecode());
        }
    }

    List<StudyVariable> lstVar = new StudyVariableManagerImpl()
            .getVariateVariable(Arrays.asList(lstSet.toArray(new String[lstSet.size()])));

    int col = sheet.getLastRowNum() + 2;
    writeRowFromList(
            new ArrayList<String>(Arrays.asList("VARIATE", "DESCRIPTION", "PROPERTY", "SCALE", "METHOD",
                    "DATATYPE", "       ")),
            sheet, col++,
            formatCell(IndexedColors.VIOLET.getIndex(), IndexedColors.WHITE.getIndex(), (short) 200, true));
    for (StudyVariable variable : lstVar) {
        writeRowFromList(new ArrayList<String>(
                Arrays.asList(variable.getVariablecode(), variable.getDescription(), variable.getProperty(),
                        variable.getScale(), variable.getMethod(), variable.getDatatype(), "    ")),
                sheet, col++, null);

    }
}

---

From source file:org.strasa.middleware.manager.CreateFieldBookManagerImpl.java

License:Open Source License

/**
 * Generate factor./*  ww w . ja  v a 2  s  . com*/
 * 
 * @param sheet
 *            the sheet
 * @param lstSiteInfo
 *            the lst site info
 * @throws CreateFieldBookException
 *             the create field book exception
 * @throws Exception
 *             the exception
 */
public void generateFactor(Sheet sheet, List<SiteInformationModel> lstSiteInfo)
        throws CreateFieldBookException, Exception {

    HashSet<String> lstSet = new HashSet<String>();
    lstSet.add("SITE");
    lstSet.add("LOCATION");
    lstSet.add("YEAR");
    lstSet.add("SEASON");

    for (SiteInformationModel siteInfo : lstSiteInfo) {
        Sheet shGenotype = getExcelSheet(siteInfo.getFileGenotype(), 0);
        Sheet shLayout = getExcelSheet(siteInfo.getFileLayout(), 0);
        lstSet.addAll(readParticularRowInExcelSheet(shGenotype, 0));
        lstSet.addAll(readParticularRowInExcelSheet(shLayout, 0));
    }

    List<StudyVariable> lstVar = new StudyVariableManagerImpl()
            .getFactorVariable(Arrays.asList(lstSet.toArray(new String[lstSet.size()])));

    int col = sheet.getLastRowNum() + 2;
    writeRowFromList(
            new ArrayList<String>(Arrays.asList("FACTOR", "DESCRIPTION", "PROPERTY", "SCALE", "METHOD",
                    "DATATYPE", "       ")),
            sheet, col++,
            formatCell(IndexedColors.GREEN.getIndex(), IndexedColors.WHITE.getIndex(), (short) 200, true));
    for (StudyVariable variable : lstVar) {
        writeRowFromList(new ArrayList<String>(
                Arrays.asList(variable.getVariablecode(), variable.getDescription(), variable.getProperty(),
                        variable.getScale(), variable.getMethod(), variable.getDatatype(), "    ")),
                sheet, col++, null);

    }
}

---

From source file:org.strasa.middleware.manager.CreateFieldBookManagerImpl.java

License:Open Source License

/**
 * Generate condition.//from   www. j a  v  a 2 s . co  m
 * 
 * @param sheet
 *            the sheet
 */
public void generateCondition(Sheet sheet) {

    int col = sheet.getLastRowNum() + 2;
    writeRowFromList(
            new ArrayList<String>(Arrays.asList("CONDITION", "DESCRIPTION", "PROPERTY", "SCALE", "METHOD",
                    "DATATYPE", "VALUE")),
            sheet, col++,
            formatCell(IndexedColors.GREEN.getIndex(), IndexedColors.WHITE.getIndex(), (short) 200, true));
    HashMap<String, StudyVariable> lstVars = new StudyVariableManagerImpl().getConditionVariable(
            new ArrayList<String>(Arrays.asList("StudyDescription", "StudyProgram", "StudyProject")));

    StudyVariable variable = lstVars.get("StudyDescription");

    writeRowFromList(new ArrayList<String>(
            Arrays.asList(variable.getVariablecode(), variable.getDescription(), variable.getProperty(),
                    variable.getScale(), variable.getMethod(), variable.getDatatype(), study.getDescription())),
            sheet, col++, null);

    variable = lstVars.get("StudyProgram");
    writeRowFromList(
            new ArrayList<String>(Arrays.asList(variable.getVariablecode(), variable.getDescription(),
                    variable.getProperty(), variable.getScale(), variable.getMethod(), variable.getDatatype(),
                    new ProgramManagerImpl().getProgramById(study.getProgramid()).getName())),
            sheet, col++, null);
    variable = lstVars.get("StudyProject");
    writeRowFromList(
            new ArrayList<String>(Arrays.asList(variable.getVariablecode(), variable.getDescription(),
                    variable.getProperty(), variable.getScale(), variable.getMethod(), variable.getDatatype(),
                    new ProjectManagerImpl().getProjectById(study.getProjectid()).getName())),
            sheet, col++, null);

}

---

From source file:org.strasa.middleware.manager.CreateFieldBookManagerImpl.java

License:Open Source License

/**
 * Populate sheet from site.//from  w w  w.java  2 s.  co  m
 * 
 * @param siteInfo
 *            the site info
 * @param sheet
 *            the sheet
 * @throws CreateFieldBookException
 *             the create field book exception
 * @throws Exception
 *             the exception
 */
public void generateSheetFromSite(SiteInformationModel siteInfo, Sheet sheet)
        throws CreateFieldBookException, Exception {
    Sheet shGenotype = getExcelSheet(siteInfo.getFileGenotype(), 0);
    Sheet shLayout = getExcelSheet(siteInfo.getFileLayout(), 0);
    Integer colGenotype;
    Integer colLayout;
    String autoHeaderMatch = null;

    if (siteInfo.isHeaderAutoMatch()) {
        autoHeaderMatch = getFirstCommonString(readParticularRowInExcelSheet(shGenotype, 0),
                readParticularRowInExcelSheet(shLayout, 0));
        colGenotype = getHeaderColumnNumber(autoHeaderMatch, shGenotype);
        colLayout = getHeaderColumnNumber(autoHeaderMatch, shLayout);

    } else {
        colGenotype = getHeaderColumnNumber(siteInfo.getHeaderGenotype(), shGenotype);
        colLayout = getHeaderColumnNumber(siteInfo.getHeaderLayout(), shGenotype);
    }

    // Write the header column for Observation Header if its blank or has
    // not been created yet
    if (sheet.getRow(0) == null) {
        ArrayList<String> lstGenotype = readParticularRowInExcelSheet(shGenotype, 0);
        ArrayList<String> lstLayout = readParticularRowInExcelSheet(shLayout, 0);
        if (autoHeaderMatch != null) {
            lstGenotype.remove(autoHeaderMatch);
            lstLayout.remove(autoHeaderMatch);
        } else {
            lstGenotype.remove(colGenotype);
            lstLayout.remove(colLayout);
        }
        lstGenotype.add("SITE");
        lstGenotype.add("LOCATION");
        lstGenotype.add("YEAR");
        lstGenotype.add("SEASON");
        lstGenotype.addAll(lstLayout);
        writeRowFromList(lstGenotype, sheet, 0,
                formatCell(IndexedColors.GREEN.getIndex(), HSSFColor.WHITE.index, (short) 210, true));
    }

    // Generate the observation sheet;
    HashMap<String, ArrayList<String>> hmGenotype = parseExcelSheetToHMap(shGenotype, colGenotype, 1, true);

    ArrayList<ArrayList<String>> hmLayout = readExcelSheet(shLayout, 1);
    // printMap(hmGenotype);
    for (ArrayList<String> lstLayout : hmLayout) {

        String key = lstLayout.get(colLayout).trim();
        lstLayout.remove((int) colLayout);
        if (!hmGenotype.containsKey(key.trim()))
            throw new CreateFieldBookException("Key '" + key + "' not found in genotype sheet");

        ArrayList<String> lstNewRow = new ArrayList<String>(hmGenotype.get(key));

        lstNewRow.add(siteInfo.getSitename());
        lstNewRow.add(siteInfo.getLocation().getLocationname());
        lstNewRow.add(siteInfo.getYear());
        lstNewRow.add(siteInfo.getSeason());

        lstNewRow.addAll(lstLayout);
        writeRowFromList(lstNewRow, sheet, sheet.getLastRowNum() + 1, null);
    }

}

---

From source file:org.talend.dataprep.schema.xls.serialization.XlsRunnable.java

License:Open Source License

private void serializeColumns(Workbook workbook, JsonGenerator generator, Sheet sheet,
        List<ColumnMetadata> columns) throws IOException {

    for (int i = 0, size = sheet.getLastRowNum(); i <= size; i++) {
        if (limit > 0 && i > limit) {
            break;
        }/*from w  w  w. ja  va  2  s  . c om*/
        // is header line?
        Row row = sheet.getRow(i);
        if (isHeaderLine(i, columns) || row == null) {
            continue;
        }

        generator.writeStartObject();
        for (ColumnMetadata columnMetadata : columns) {

            // do not write the values if this has been detected as an header
            if (i < columnMetadata.getHeaderSize()) {
                continue;
            }

            int colId = Integer.parseInt(columnMetadata.getId());
            String cellValue = getCellValueAsString(row.getCell(colId),
                    workbook.getCreationHelper().createFormulaEvaluator());
            LOG.trace("cellValue for {}/{}: {}", i, colId, cellValue);
            generator.writeFieldName(columnMetadata.getId());
            if (cellValue != null) {
                generator.writeString(cellValue);
            } else {
                generator.writeNull();
            }
        }
        generator.writeEndObject();
    }
}

---

From source file:org.talend.dataprep.schema.xls.XlsSchemaParser.java

License:Open Source License

/**
 * Parse all xls sheets for old excel document type
 *
 * @param request the xls request./*from  w w  w . j  av a 2  s  .  c  om*/
 * @return The parsed sheets request.
 */
private List<Schema.SheetContent> parseAllSheetsOldFormat(Request request) {

    final Marker marker = Markers.dataset(request.getMetadata().getId());

    try {
        InputStream inputStream = request.getContent();
        if (!inputStream.markSupported()) {
            inputStream = new PushbackInputStream(inputStream, 8);
        }
        Workbook hssfWorkbook = WorkbookFactory.create(inputStream);

        List<Schema.SheetContent> schemas;
        try {
            if (hssfWorkbook == null) {
                throw new IOException("could not open " + request.getMetadata().getId() + " as an excel file");
            }
            int sheetNumber = hssfWorkbook.getNumberOfSheets();
            if (sheetNumber < 1) {
                LOGGER.debug(marker, "has not sheet to read");
                return Collections.emptyList();
            }
            schemas = new ArrayList<>();
            for (int i = 0; i < sheetNumber; i++) {
                Sheet sheet = hssfWorkbook.getSheetAt(i);
                if (sheet.getLastRowNum() < 1) {
                    LOGGER.debug(marker, "sheet '{}' do not have rows skip ip", sheet.getSheetName());
                    continue;
                }
                List<ColumnMetadata> columnsMetadata = parsePerSheet(sheet, //
                        request.getMetadata().getId(), //
                        hssfWorkbook.getCreationHelper().createFormulaEvaluator());
                String sheetName = sheet.getSheetName();
                // update XlsSerializer if this default sheet naming change!!!
                schemas.add(
                        new Schema.SheetContent(sheetName == null ? "sheet-" + i : sheetName, columnsMetadata));
            }
        } finally {
            hssfWorkbook.close();
        }
        return schemas;
    } catch (Exception e) {
        LOGGER.debug(marker, "Exception during parsing xls request :" + e.getMessage(), e);
        throw new TDPException(CommonErrorCodes.UNEXPECTED_EXCEPTION, e);
    }
}

---

From source file:org.talend.dataprep.schema.xls.XlsSchemaParser.java

License:Open Source License

/**
 * We store (cell types per row) per column.
 *
 * @param sheet key is the column number, value is a Map with key row number and value Type
 * @return A Map<colId, Map<rowId, type>>
 *//*  ww  w  .j  a v a 2  s  .  c  o m*/
private SortedMap<Integer, SortedMap<Integer, String>> collectSheetTypeMatrix(Sheet sheet,
        FormulaEvaluator formulaEvaluator) {

    int firstRowNum = sheet.getFirstRowNum();
    int lastRowNum = sheet.getLastRowNum();

    LOGGER.debug("firstRowNum: {}, lastRowNum: {}", firstRowNum, lastRowNum);

    SortedMap<Integer, SortedMap<Integer, String>> cellsTypeMatrix = new TreeMap<>();

    // we start analysing rows
    for (int rowCounter = firstRowNum; rowCounter <= lastRowNum; rowCounter++) {

        int cellCounter = 0;

        Row row = sheet.getRow(rowCounter);
        if (row == null) {
            continue;
        }

        Iterator<Cell> cellIterator = row.cellIterator();

        String currentType;

        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();

            int xlsType = Cell.CELL_TYPE_STRING;

            try {
                xlsType = cell.getCellType() == Cell.CELL_TYPE_FORMULA ? //
                        formulaEvaluator.evaluate(cell).getCellType() : cell.getCellType();
            } catch (Exception e) {
                // ignore formula error evaluation get as a String with the formula
            }
            switch (xlsType) {
            case Cell.CELL_TYPE_BOOLEAN:
                currentType = BOOLEAN.getName();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                currentType = getTypeFromNumericCell(cell);
                break;
            case Cell.CELL_TYPE_BLANK:
                currentType = BLANK;
                break;
            case Cell.CELL_TYPE_FORMULA:
            case Cell.CELL_TYPE_STRING:
                currentType = STRING.getName();
                break;
            case Cell.CELL_TYPE_ERROR:
                // we cannot really do anything with an error
            default:
                currentType = ANY.getName();
            }

            SortedMap<Integer, String> cellInfo = cellsTypeMatrix.get(cellCounter);

            if (cellInfo == null) {
                cellInfo = new TreeMap<>();
            }
            cellInfo.put(rowCounter, currentType);

            cellsTypeMatrix.put(cellCounter, cellInfo);
            cellCounter++;
        }
    }

    LOGGER.trace("cellsTypeMatrix: {}", cellsTypeMatrix);
    return cellsTypeMatrix;
}

---

From source file:org.talend.dataprep.transformation.format.XlsWriterTest.java

License:Open Source License

@Test
public void write_simple_xls_file() throws Exception {
    // given//from  ww w. ja v  a  2s.co  m
    SchemaParser.Request request = createSchemaParser("export_dataset.json");

    Workbook workbook = WorkbookFactory.create(request.getContent());
    assertThat(workbook).isNotNull();
    assertThat(workbook.getNumberOfSheets()).isEqualTo(1);

    Sheet sheet = workbook.getSheetAt(0);
    assertThat(sheet).isNotNull().isNotEmpty();
    assertThat(sheet.getFirstRowNum()).isEqualTo(0);
    assertThat(sheet.getLastRowNum()).isEqualTo(6);

    // assert header content
    Row row = sheet.getRow(0);
    /*
     * "columns": [ { "id": "id", "type": "string" }, { "id": "firstname", "type": "string" }, { "id": "lastname",
     * "type": "string" }, { "id": "age", "type": "integer" }, { "id": "date-of-birth", "type": "date" }, { "id":
     * "alive", "type": "boolean" }, { "id": "city", "type": "string" } ]
     */
    assertThat(row.getCell(0).getRichStringCellValue().getString()).isEqualTo("id");
    assertThat(row.getCell(1).getRichStringCellValue().getString()).isEqualTo("firstname");
    assertThat(row.getCell(2).getRichStringCellValue().getString()).isEqualTo("lastname");
    assertThat(row.getCell(3).getRichStringCellValue().getString()).isEqualTo("age");
    assertThat(row.getCell(4).getRichStringCellValue().getString()).isEqualTo("date-of-birth");
    assertThat(row.getCell(5).getRichStringCellValue().getString()).isEqualTo("alive");
    assertThat(row.getCell(6).getRichStringCellValue().getString()).isEqualTo("city");

    // assert first content
    row = sheet.getRow(1);
    /*
     * { "id" : "1", "firstname" : "Clark", "lastname" : "Kent", "age" : "42", "date-of-birth" : "10/09/1940",
     * "alive" : "false", "city" : "Smallville" }
     */

    assertThat(row.getCell(0).getNumericCellValue()).isEqualTo(1);
    assertThat(row.getCell(1).getStringCellValue()).isEqualTo("Clark");
    assertThat(row.getCell(2).getStringCellValue()).isEqualTo("Kent");
    assertThat(row.getCell(3).getNumericCellValue()).isEqualTo((double) 42);
    assertThat(row.getCell(4).getStringCellValue()).isEqualTo("10/09/1940");
    assertThat(row.getCell(5).getBooleanCellValue()).isFalse();
    assertThat(row.getCell(6).getStringCellValue()).isEqualTo("Smallville");

    // assert last content
    row = sheet.getRow(sheet.getLastRowNum());
    /*
     * { "id" : "6", "firstname" : "Ray", "lastname" : "Palmer", "age" : "93", "date-of-birth" : "01/05/1951",
     * "alive" : "true", "city" : "Star city" }
     */
    assertThat(row.getCell(0).getNumericCellValue()).isEqualTo(6);
    assertThat(row.getCell(1).getStringCellValue()).isEqualTo("Ray");
    assertThat(row.getCell(2).getStringCellValue()).isEqualTo("Palmer");
    assertThat(row.getCell(3).getNumericCellValue()).isEqualTo((double) 93);
    assertThat(row.getCell(4).getStringCellValue()).isEqualTo("01/05/1951");
    assertThat(row.getCell(5).getBooleanCellValue()).isTrue();
    assertThat(row.getCell(6).getStringCellValue()).isEqualTo("Star city");
}

---

From source file:org.talend.dataprep.transformation.format.XlsWriterTest.java

License:Open Source License

/**
 * Please have a look at <a href="https://jira.talendforge.org/browse/TDP-1528">TDP-1528</a>.
 *//*ww  w. j a  v  a  2 s  . co  m*/
@Test
public void TDP_1528_export_of_backslash() throws Exception {

    // given
    SchemaParser.Request request = createSchemaParser("tdp_1528_backslash_not_exported.json");

    Workbook workbook = WorkbookFactory.create(request.getContent());
    assertThat(workbook).isNotNull();
    assertThat(workbook.getNumberOfSheets()).isEqualTo(1);

    Sheet sheet = workbook.getSheetAt(0);
    assertThat(sheet).isNotNull().isNotEmpty();
    assertThat(sheet.getFirstRowNum()).isEqualTo(0);
    assertThat(sheet.getLastRowNum()).isEqualTo(2);

    // assert header content
    Row row = sheet.getRow(0);
    /*
     * [ {"id": "0", "name": "column1", "type": "string"}, {"id": "1", "name": "column2", "type": "string"},
     * {"id": "2", "name": "column2", "type": "string"} ]
     */
    assertThat(row.getCell(0).getRichStringCellValue().getString()).isEqualTo("column1");
    assertThat(row.getCell(1).getRichStringCellValue().getString()).isEqualTo("column2");
    assertThat(row.getCell(2).getRichStringCellValue().getString()).isEqualTo("column3");

    // assert first content
    row = sheet.getRow(1);
    /*
     * { "0": "BEAUTIFUL ITEM DESC W\BAG", "1": "Hello", "2": "Yo" }
     */

    assertThat(row.getCell(0).getStringCellValue()).isEqualTo("BEAUTIFUL ITEM DESC W\\BAG");
    assertThat(row.getCell(1).getStringCellValue()).isEqualTo("Hello");
    assertThat(row.getCell(2).getStringCellValue()).isEqualTo("Yo");

    // assert last content
    row = sheet.getRow(sheet.getLastRowNum());
    /*
     * { "0": "Konishiwa", "1": "Na nga def", "2": "Hola" }
     */
    assertThat(row.getCell(0).getStringCellValue()).isEqualTo("Konishiwa");
    assertThat(row.getCell(1).getStringCellValue()).isEqualTo("Na nga def");
    assertThat(row.getCell(2).getStringCellValue()).isEqualTo("Hola");
}

---

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