Example usage for org.apache.poi.ss.usermodel Row getLastCellNum

Introduction

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

Prototype

short getLastCellNum();

Source Link

Document

Gets the index of the last cell contained in this row PLUS ONE.

Usage

From source file:org.aludratest.util.ExcelUtil.java

License:Apache License

/** Searches a {@link Row} for a {@link Cell} with the provided text and returns its index.
 * @param text the text to search//w w w  .  j a  va 2  s  .c  o m
 * @param row the row in which to search
 * @return the index of the located cell or -1 if the text was not found in the row */
public static int findCellWithText(String text, Row row) {
    int lastCellIndex = row.getLastCellNum();
    for (int i = 0; i < lastCellIndex; i++) {
        Cell cell = row.getCell(i);
        if (cell != null && text.equals(cell.getStringCellValue())) {
            return i;
        }
    }
    return -1;
}

---

From source file:org.aludratest.util.ExcelUtil.java

License:Apache License

/** Inserts a column without cells into a sheet at the given index.
 * @param sheet the sheet in which to insert the column
 * @param insertionIndex the column index at which to insert the new column */
public static void insertEmptyColumn(Sheet sheet, int insertionIndex) {
    for (int i = sheet.getLastRowNum(); i >= 0; i--) {
        Row row = sheet.getRow(i);
        short lastCellNum = row.getLastCellNum();
        if (lastCellNum >= insertionIndex) {
            shiftCellsRight(row, insertionIndex, lastCellNum);
        }//  w  w w .  j ava  2s .  c o  m
    }
}

---

From source file:org.apache.any23.plugin.officescraper.ExcelExtractor.java

License:Apache License

private void writeRowMetadata(URI rowURI, Row row, ExtractionResult er) {
    final int firstCellNum = row.getFirstCellNum();
    final int lastCellNum = row.getLastCellNum();
    er.writeTriple(rowURI, excel.firstCell, RDFUtils.literal(firstCellNum));
    er.writeTriple(rowURI, excel.lastCell, RDFUtils.literal(lastCellNum));
}

---

From source file:org.apache.metamodel.excel.DefaultSpreadsheetReaderDelegate.java

License:Apache License

private MutableTable createTable(final Workbook wb, final Sheet sheet) {
    final MutableTable table = new MutableTable(sheet.getSheetName());

    if (sheet.getPhysicalNumberOfRows() <= 0) {
        // no physical rows in sheet
        return table;
    }/*from  ww w. j  ava2s .c o  m*/

    final Iterator<Row> rowIterator = ExcelUtils.getRowIterator(sheet, _configuration, false);

    if (!rowIterator.hasNext()) {
        // no physical rows in sheet
        return table;
    }

    Row row = null;

    if (_configuration.isSkipEmptyLines()) {
        while (row == null && rowIterator.hasNext()) {
            row = rowIterator.next();
        }
    } else {
        row = rowIterator.next();
    }

    final int columnNameLineNumber = _configuration.getColumnNameLineNumber();
    if (columnNameLineNumber == ExcelConfiguration.NO_COLUMN_NAME_LINE) {

        // get to the first non-empty line (no matter if lines are skipped
        // or not we need to read ahead to figure out how many columns there
        // are!)
        while (row == null && rowIterator.hasNext()) {
            row = rowIterator.next();
        }

        // build columns without any intrinsic column names
        final ColumnNamingStrategy columnNamingStrategy = _configuration.getColumnNamingStrategy();
        try (final ColumnNamingSession columnNamingSession = columnNamingStrategy.startColumnNamingSession()) {
            final int offset = getColumnOffset(row);
            for (int i = 0; i < offset; i++) {
                columnNamingSession.getNextColumnName(new ColumnNamingContextImpl(i));
            }

            for (int j = offset; j < row.getLastCellNum(); j++) {
                final ColumnNamingContext namingContext = new ColumnNamingContextImpl(table, null, j);
                final Column column = new MutableColumn(columnNamingSession.getNextColumnName(namingContext),
                        ColumnType.STRING, table, j, true);
                table.addColumn(column);
            }
        }

    } else {

        boolean hasColumns = true;

        // iterate to the column name line number (if above 1)
        for (int j = 1; j < columnNameLineNumber; j++) {
            if (rowIterator.hasNext()) {
                row = rowIterator.next();
            } else {
                hasColumns = false;
                break;
            }
        }

        if (hasColumns) {
            createColumns(table, wb, row);
        }
    }

    return table;
}

---

From source file:org.apache.metamodel.excel.DefaultSpreadsheetReaderDelegate.java

License:Apache License

/**
 * Builds columns based on row/cell values.
 * //from   w w w.  ja  v  a  2  s.  c om
 * @param table
 * @param wb
 * @param row
 */
private void createColumns(MutableTable table, Workbook wb, Row row) {
    if (row == null) {
        logger.warn("Cannot create columns based on null row!");
        return;
    }
    final short rowLength = row.getLastCellNum();

    final int offset = getColumnOffset(row);

    // build columns based on cell values.
    try (final ColumnNamingSession columnNamingSession = _configuration.getColumnNamingStrategy()
            .startColumnNamingSession()) {
        for (int j = offset; j < rowLength; j++) {
            final Cell cell = row.getCell(j);
            final String intrinsicColumnName = ExcelUtils.getCellValue(wb, cell);
            final ColumnNamingContext columnNamingContext = new ColumnNamingContextImpl(table,
                    intrinsicColumnName, j);
            final String columnName = columnNamingSession.getNextColumnName(columnNamingContext);
            final Column column = new MutableColumn(columnName, ColumnType.VARCHAR, table, j, true);
            table.addColumn(column);
        }
    }
}

---

From source file:org.argrr.extractor.excel.SpreadSheetTab.java

License:Open Source License

public SpreadSheetTab(XSSFSheet sheet) {
    this.sheet = sheet;
    columnNames = new ArrayList<String>();
    lines = new ArrayList<HashMap<String, String>>();

    //iterate throw the first line in order to have columns names
    Iterator<Row> rowIterator = sheet.iterator();
    Row curRow = rowIterator.next();
    for (int cn = 0; cn < curRow.getLastCellNum(); cn++) {

        Cell cell = curRow.getCell(cn, Row.CREATE_NULL_AS_BLANK);
        columnNames.add(cell.getStringCellValue());
    }/*  w  w w  . j  a v a 2  s  .  c  om*/

    //Iterate through each other rows in order to have datas
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        HashMap<String, String> curLine = new HashMap<String, String>();

        //For each row, iterate through all the columns
        for (int id = 0; id < columnNames.size(); id++) {

            //add empty cells names if there are more cols in values than header def
            if (id >= this.columnNames.size())
                this.columnNames.add("");

            Cell cell = row.getCell(id, Row.CREATE_NULL_AS_BLANK);
            if (id == 0 && cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                break;
            }

            String cellVal = "";
            //Check the cell type and format accordingly
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
                    cellVal = sdf.format(cell.getDateCellValue());
                } else {
                    cellVal = Integer.valueOf(Double.valueOf(cell.getNumericCellValue()).intValue()).toString();
                }
                break;
            case Cell.CELL_TYPE_STRING:
                cellVal = cell.getStringCellValue();
                break;
            }
            curLine.put(this.getColumnName(id), cellVal);
        }
        if (curLine.size() > 0)
            lines.add(curLine);
    }
}

---

From source file:org.bbreak.excella.core.SheetParser.java

License:Open Source License

/**
 * ??//from  w w  w.j a  v a2s  . c  o  m
 * 
 * @param sheet ?
 * @param data BookController?parseBook(), parseSheet()?
 *              SheetParser?parseSheet?????
 *              TagParser??????
 * @return ??
 * @throws ParseException ??????Throw?
 */
public SheetData parseSheet(Sheet sheet, Object data) throws ParseException {
    // ??
    String sheetName = PoiUtil.getSheetName(sheet);
    SheetData sheetData = new SheetData(sheetName);

    int firstRowNum = sheet.getFirstRowNum();

    // ?
    for (int rowCnt = firstRowNum; rowCnt <= sheet.getLastRowNum(); rowCnt++) {
        // ??
        Row row = sheet.getRow(rowCnt);
        if (row == null) {
            continue;
        }
        if (row != null) {
            for (int columnIdx = 0; columnIdx < row.getLastCellNum(); columnIdx++) {
                // ??
                Cell cell = row.getCell(columnIdx);
                if (cell == null) {
                    continue;
                }
                if (parseCell(sheet, data, sheetData, cell, row, columnIdx)) {
                    // ?
                    return sheetData;
                }
            }
        }
    }

    return sheetData;
}

---

From source file:org.bbreak.excella.core.tag.excel2java.ArraysParser.java

License:Open Source License

/**
 * ?//  ww w .j  ava 2 s  .c  om
 * 
 * @param sheet 
 * @param tagCell ???
 * @param data BookController?parseBook(), parseSheet()?<BR>
 *              SheetParser?parseSheet?????<BR>
 *              TagParser??????<BR>
 * @return ?
 * @throws ParseException 
 */
@Override
public List<Object[]> parse(Sheet sheet, Cell tagCell, Object data) throws ParseException {

    List<Object[]> resultList = new ArrayList<Object[]>();

    // 
    int tagRowIdx = tagCell.getRowIndex();
    // 
    int tagColIdx = tagCell.getColumnIndex();
    // 
    int valueRowFromIdx;
    // 
    int valueRowToIdx = sheet.getLastRowNum();
    // 
    int valueColumnFromIdx;
    // 
    int valueColumnToIdx = 0;

    // 
    boolean valueColumnToFlag = false;

    try {

        Map<String, String> paramDef = TagUtil.getParams(tagCell.getStringCellValue());

        // ?
        valueRowFromIdx = TagUtil.adjustValue(tagRowIdx, paramDef, PARAM_DATA_ROW_FROM,
                DEFAULT_VALUE_ROW_FROM_ADJUST);
        if (valueRowFromIdx < 0 || valueRowFromIdx > sheet.getLastRowNum()) {
            throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_FROM);
        }

        // ?
        valueRowToIdx = TagUtil.adjustValue(tagRowIdx, paramDef, PARAM_DATA_ROW_TO, valueRowToIdx - tagRowIdx);
        if (valueRowToIdx > sheet.getLastRowNum() || valueRowToIdx < 0) {
            throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_TO);
        }

        // ???
        if (valueRowFromIdx > valueRowToIdx) {
            throw new ParseException(tagCell,
                    "?" + PARAM_DATA_ROW_FROM + "," + PARAM_DATA_ROW_TO);
        }

        // ?
        valueColumnFromIdx = TagUtil.adjustValue(tagColIdx, paramDef, PARAM_DATA_CLOMUN_FROM,
                DEFAULT_VALUE_COLUMN_FROM_ADJUST);
        if (valueColumnFromIdx < 0 || valueColumnFromIdx > PoiUtil.getLastColNum(sheet)) {
            throw new ParseException(tagCell, "?" + PARAM_DATA_CLOMUN_FROM);
        }

        // 
        valueColumnToFlag = paramDef.containsKey(PARAM_DATA_CLOMUN_TO);
        if (valueColumnToFlag) {
            // ?
            valueColumnToIdx = tagColIdx + Integer.valueOf(paramDef.get(PARAM_DATA_CLOMUN_TO));
            if (valueColumnToIdx < 0 || valueColumnToIdx > PoiUtil.getLastColNum(sheet)) {
                throw new ParseException(tagCell, "?" + PARAM_DATA_CLOMUN_TO);
            }

            // ???
            if (valueColumnFromIdx > valueColumnToIdx) {
                throw new ParseException(tagCell,
                        "?" + PARAM_DATA_CLOMUN_FROM + "," + PARAM_DATA_CLOMUN_TO);
            }
        }
    } catch (Exception e) {
        if (e instanceof ParseException) {
            throw (ParseException) e;
        } else {
            throw new ParseException(tagCell, e);
        }
    }

    // ??
    for (int rowCnt = valueRowFromIdx; rowCnt <= valueRowToIdx; rowCnt++) {

        List<Object> objList = new ArrayList<Object>();

        Row row = sheet.getRow(rowCnt);
        if (row == null) {
            // ?null??
            continue;
        }
        if (!valueColumnToFlag) {
            // ?????
            // ????
            valueColumnToIdx = row.getLastCellNum() - 1;
        }
        for (int cellCnt = valueColumnFromIdx; cellCnt <= valueColumnToIdx; cellCnt++) {

            Cell cell = row.getCell(cellCnt);
            Object cellValue = PoiUtil.getCellValue(cell);
            objList.add(cellValue);
        }

        resultList.add(objList.toArray());
    }
    return resultList;
}

---

From source file:org.bbreak.excella.core.tag.excel2java.MapsParser.java

License:Open Source License

/**
 * ?//from   ww w. j  a v  a2 s  . co m
 * 
 * @param sheet 
 * @param tagCell ???
 * @param data BookController?parseBook(), parseSheet()?<BR>
 * SheetParser?parseSheet?????<BR>
 * TagParser??????<BR>
 * @return ?
 * @throws ParseException 
 */
@Override
public List<Map<?, ?>> parse(Sheet sheet, Cell tagCell, Object data) throws ParseException {

    List<Map<?, ?>> resultList = new ArrayList<Map<?, ?>>();

    // 
    int tagRowIdx = tagCell.getRowIndex();
    // 
    int keyRowIdx;
    // 
    int valueRowFromIdx;
    // 
    int valueRowToIdx = sheet.getLastRowNum();

    try {
        Map<String, String> paramDef = TagUtil.getParams(tagCell.getStringCellValue());

        // ?
        keyRowIdx = TagUtil.adjustValue(tagRowIdx, paramDef, PARAM_KEY_ROW, DEFAULT_KEY_ROW_ADJUST);
        if (keyRowIdx < 0 || keyRowIdx > sheet.getLastRowNum()) {
            throw new ParseException(tagCell, "?" + PARAM_KEY_ROW);
        }

        // ?
        valueRowFromIdx = TagUtil.adjustValue(tagRowIdx, paramDef, PARAM_DATA_ROW_FROM,
                DEFAULT_VALUE_ROW_FROM_ADJUST);
        if (valueRowFromIdx < 0 || valueRowFromIdx > sheet.getLastRowNum()) {
            throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_FROM);
        }

        // ?
        valueRowToIdx = TagUtil.adjustValue(tagRowIdx, paramDef, PARAM_DATA_ROW_TO, valueRowToIdx - tagRowIdx);
        if (valueRowToIdx > sheet.getLastRowNum() || valueRowToIdx < 0) {
            throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_TO);
        }

        // ???
        if (valueRowFromIdx > valueRowToIdx) {
            throw new ParseException(tagCell,
                    "?" + PARAM_DATA_ROW_FROM + "," + PARAM_DATA_ROW_TO);
        }

    } catch (Exception e) {
        if (e instanceof ParseException) {
            throw (ParseException) e;
        } else {
            throw new ParseException(tagCell, e);
        }
    }

    // ??
    List<Integer> targetColNums = new ArrayList<Integer>();
    Row keyRow = sheet.getRow(keyRowIdx);
    if (keyRow == null) {
        // ?null??
        return resultList;
    }
    int firstCellNum = keyRow.getFirstCellNum();
    int lastCellNum = keyRow.getLastCellNum();
    for (int cellCnt = firstCellNum; cellCnt < lastCellNum; cellCnt++) {
        Cell cell = keyRow.getCell(cellCnt);
        Object cellValue = PoiUtil.getCellValue(cell);
        if (cellValue instanceof String) {
            String keyName = (String) cellValue;
            if (keyName.startsWith(BookController.COMMENT_PREFIX)) {
                continue;
            }
        }
        if (cellValue != null) {
            targetColNums.add(cellCnt);
        }
    }

    if (targetColNums.size() > 0) {
        // ????

        // ??
        for (int rowCnt = valueRowFromIdx; rowCnt <= valueRowToIdx; rowCnt++) {
            Row dataRow = sheet.getRow(rowCnt);
            if (dataRow == null) {
                continue;
            }
            Map<Object, Object> map = new LinkedHashMap<Object, Object>();
            for (Integer colCnt : targetColNums) {
                Cell keyCell = keyRow.getCell(colCnt);
                Cell valueCell = dataRow.getCell(colCnt);

                Object key = PoiUtil.getCellValue(keyCell);
                Object value = PoiUtil.getCellValue(valueCell);

                map.put(key, value);
            }
            resultList.add(map);
        }
    }

    return resultList;
}

---

From source file:org.bbreak.excella.core.tag.excel2java.ObjectsParser.java

License:Open Source License

/**
 * ?// ww w.j  av a2  s . co m
 * 
 * @param sheet 
 * @param tagCell ???
 * @param data BookController?parseBook(), parseSheet()?<BR>
 *              SheetParser?parseSheet?????<BR>
 *              TagParser??????<BR>
 * @return ?
 * @throws ParseException 
 */
@Override
public List<Object> parse(Sheet sheet, Cell tagCell, Object data) throws ParseException {

    List<Object> resultList = new ArrayList<Object>();
    Class<?> clazz = null;

    // 
    int tagRowIdx = tagCell.getRowIndex();
    // 
    int propertyRowIdx;
    // 
    int valueRowFromIdx;
    // 
    int valueRowToIdx = sheet.getLastRowNum();

    try {
        Map<String, String> paramDef = TagUtil.getParams(tagCell.getStringCellValue());

        clazz = Class.forName(paramDef.get(PARAM_CLASS));

        // ?
        propertyRowIdx = TagUtil.adjustValue(tagRowIdx, paramDef, PARAM_PROPERTY_ROW,
                DEFAULT_PROPERTY_ROW_ADJUST);
        if (propertyRowIdx < 0 || propertyRowIdx > sheet.getLastRowNum()) {
            throw new ParseException(tagCell, "?" + PARAM_PROPERTY_ROW);
        }

        // ?
        valueRowFromIdx = TagUtil.adjustValue(tagRowIdx, paramDef, PARAM_DATA_ROW_FROM,
                DEFAULT_VALUE_ROW_FROM_ADJUST);
        if (valueRowFromIdx < 0 || valueRowFromIdx > sheet.getLastRowNum()) {
            throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_FROM);
        }

        // ?
        valueRowToIdx = TagUtil.adjustValue(tagRowIdx, paramDef, PARAM_DATA_ROW_TO, valueRowToIdx - tagRowIdx);
        if (valueRowToIdx > sheet.getLastRowNum() || valueRowToIdx < 0) {
            throw new ParseException(tagCell, "?" + PARAM_DATA_ROW_TO);
        }

        // ???
        if (valueRowFromIdx > valueRowToIdx) {
            throw new ParseException(tagCell,
                    "?" + PARAM_DATA_ROW_FROM + "," + PARAM_DATA_ROW_TO);
        }

    } catch (Exception e) {
        if (e instanceof ParseException) {
            throw (ParseException) e;
        } else {
            throw new ParseException(tagCell, e);
        }
    }

    // ???
    Map<Integer, Class<?>> propertyClassMap = new HashMap<Integer, Class<?>>();
    // ????
    Map<Integer, String> propertyNameMap = new HashMap<Integer, String>();
    // ??
    Map<String, List<ObjectsPropertyParser>> customPropertyParserMap = new HashMap<String, List<ObjectsPropertyParser>>();

    // ??
    List<Integer> targetColNums = new ArrayList<Integer>();
    Row propertyRow = sheet.getRow(propertyRowIdx);
    if (propertyRow == null) {
        // ?null??
        return resultList;
    }
    int firstCellNum = propertyRow.getFirstCellNum();
    int lastCellNum = propertyRow.getLastCellNum();
    for (int cellCnt = firstCellNum; cellCnt < lastCellNum; cellCnt++) {
        Cell cell = propertyRow.getCell(cellCnt);
        if (cell == null) {
            continue;
        }
        try {
            String propertyName = cell.getStringCellValue();
            if (propertyName.startsWith(BookController.COMMENT_PREFIX)) {
                continue;
            }

            Object obj = clazz.newInstance();
            Class<?> propertyClass = PropertyUtils.getPropertyType(obj, propertyName);
            if (propertyClass != null) {
                propertyClassMap.put(cellCnt, propertyClass);
                propertyNameMap.put(cellCnt, propertyName);
                targetColNums.add(cellCnt);
            } else {
                // ????
                for (ObjectsPropertyParser parser : customPropertyParsers) {
                    if (parser.isParse(sheet, cell)) {
                        List<ObjectsPropertyParser> propertyParsers = customPropertyParserMap.get(propertyName);
                        if (propertyParsers == null) {
                            propertyParsers = new ArrayList<ObjectsPropertyParser>();
                        }
                        // ???????
                        if (!propertyParsers.contains(parser)) {
                            propertyParsers.add(parser);
                        }
                        customPropertyParserMap.put(propertyName, propertyParsers);

                        if (!targetColNums.contains(cellCnt)) {
                            propertyNameMap.put(cellCnt, propertyName);
                            targetColNums.add(cellCnt);
                        }
                    }
                }
            }

        } catch (Exception e) {
            throw new ParseException(cell, e);
        }
    }

    if (targetColNums.size() > 0) {
        // ????

        // ??
        for (int rowCnt = valueRowFromIdx; rowCnt <= valueRowToIdx; rowCnt++) {
            Row dataRow = sheet.getRow(rowCnt);
            if (dataRow == null) {
                continue;
            }
            Object obj;
            try {
                obj = clazz.newInstance();
                for (Integer colCnt : targetColNums) {
                    Cell cell = dataRow.getCell(colCnt);

                    try {
                        Class<?> propertyClass = propertyClassMap.get(colCnt);
                        String propertyName = propertyNameMap.get(colCnt);
                        // ?
                        if (customPropertyParserMap.containsKey(propertyName)) {
                            List<ObjectsPropertyParser> propertyParsers = customPropertyParserMap
                                    .get(propertyName);
                            Map<String, String> params = TagUtil.getParams(propertyName);
                            Object cellValue = PoiUtil.getCellValue(cell);

                            // ??
                            for (ObjectsPropertyParser propertyParser : propertyParsers) {
                                propertyParser.parse(obj, cellValue, TagUtil.getTag(propertyName), params);
                            }
                        } else {
                            Object value = null;
                            if (cell != null) {
                                value = PoiUtil.getCellValue(cell, propertyClass);
                            }
                            PropertyUtils.setProperty(obj, propertyName, value);
                        }
                    } catch (Exception e) {
                        throw new ParseException(cell, e);
                    }
                }
            } catch (Exception e) {
                if (e instanceof ParseException) {
                    throw (ParseException) e;
                } else {
                    throw new ParseException(tagCell, e);
                }
            }
            resultList.add(obj);
        }
    }
    return resultList;
}

---

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