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

List of usage examples for org.apache.poi.ss.usermodel Row getLastCellNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. 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.teiid.translator.excel.ExcelMetadataProcessor.java

License:Open Source License

private void addTable(MetadataFactory mf, Sheet sheet, String xlsName) {
    int firstRowNumber = sheet.getFirstRowNum();
    Row headerRow = null;
    int firstCellNumber = -1;
    if (this.hasHeader) {
        headerRow = sheet.getRow(this.headerRowNumber);
        if (headerRow != null) {
            firstRowNumber = this.headerRowNumber;
            firstCellNumber = headerRow.getFirstCellNum();
            if (firstCellNumber == -1) {
                LogManager.logInfo(LogConstants.CTX_CONNECTOR,
                        ExcelPlugin.Util.gs(ExcelPlugin.Event.TEIID23006, xlsName));
                return;
            }//from   w w w  . j a v a2s.  co m
        }
    }

    if (headerRow == null) {
        while (firstCellNumber == -1) {
            headerRow = sheet.getRow(firstRowNumber++);
            // check if this is a empty sheet; the data must be present in first 10000 rows
            if (headerRow == null && firstRowNumber > 10000) {
                return;
            }
            if (headerRow == null) {
                continue;
            }
            firstCellNumber = headerRow.getFirstCellNum();
        }
    }

    // create a table for each sheet
    AtomicInteger columnCount = new AtomicInteger();
    Table table = mf.addTable(sheet.getSheetName());
    table.setNameInSource(sheet.getSheetName());
    table.setProperty(ExcelMetadataProcessor.FILE, xlsName);

    // add implicit row_id column based on row number from excel sheet 
    Column column = mf.addColumn(ROW_ID, TypeFacility.RUNTIME_NAMES.INTEGER, table);
    column.setSearchType(SearchType.All_Except_Like);
    column.setProperty(CELL_NUMBER, ROW_ID);
    mf.addPrimaryKey("PK0", Arrays.asList(ROW_ID), table); //$NON-NLS-1$
    column.setUpdatable(false);

    Row dataRow = null;
    int lastCellNumber = headerRow.getLastCellNum();

    if (this.hasDataRowNumber) {
        // adjust for zero index
        table.setProperty(ExcelMetadataProcessor.FIRST_DATA_ROW_NUMBER, String.valueOf(this.dataRowNumber + 1));
        dataRow = sheet.getRow(this.dataRowNumber);
    } else if (this.hasHeader) {
        // +1 zero based, +1 to skip header
        table.setProperty(ExcelMetadataProcessor.FIRST_DATA_ROW_NUMBER, String.valueOf(firstRowNumber + 2));
        dataRow = sheet.getRow(firstRowNumber + 1);
    } else {
        //+1 already occurred because of the increment above
        table.setProperty(ExcelMetadataProcessor.FIRST_DATA_ROW_NUMBER, String.valueOf(firstRowNumber));
        dataRow = sheet.getRow(firstRowNumber);
    }

    if (firstCellNumber != -1) {
        for (int j = firstCellNumber; j < lastCellNumber; j++) {
            Cell headerCell = headerRow.getCell(j);
            Cell dataCell = dataRow.getCell(j);
            // if the cell value is null; then advance the data row cursor to to find it 
            if (dataCell == null) {
                for (int rowNo = firstRowNumber + 1; rowNo < firstRowNumber + 10000; rowNo++) {
                    Row row = sheet.getRow(rowNo);
                    dataCell = row.getCell(j);
                    if (dataCell != null) {
                        break;
                    }
                }
            }
            column = mf.addColumn(cellName(headerCell, columnCount), cellType(headerCell, dataCell), table);
            column.setSearchType(SearchType.Unsearchable);
            column.setProperty(ExcelMetadataProcessor.CELL_NUMBER, String.valueOf(j + 1));
        }
    }
}

From source file:org.tiefaces.components.websheet.configuration.ConfigurationHandler.java

License:MIT License

/**
 * Gets the sheet configuration./*from   ww  w .j  a  v a2  s . c  o  m*/
 *
 * @param sheet
 *            the sheet
 * @param formName
 *            the form name
 * @param sheetRightCol
 *            the sheet right col
 * @return the sheet configuration
 */
private SheetConfiguration getSheetConfiguration(final Sheet sheet, final String formName,
        final int sheetRightCol) {

    SheetConfiguration sheetConfig = new SheetConfiguration();
    sheetConfig.setFormName(formName);
    sheetConfig.setSheetName(sheet.getSheetName());
    int leftCol = sheet.getLeftCol();
    int lastRow = sheet.getLastRowNum();
    int firstRow = sheet.getFirstRowNum();
    int rightCol = 0;
    int maxRow = 0;
    for (Row row : sheet) {
        if (row.getRowNum() > TieConstants.TIE_WEB_SHEET_MAX_ROWS) {
            break;
        }
        maxRow = row.getRowNum();
        int firstCellNum = row.getFirstCellNum();
        if (firstCellNum >= 0 && firstCellNum < leftCol) {
            leftCol = firstCellNum;
        }
        if ((row.getLastCellNum() - 1) > rightCol) {
            int verifiedcol = verifyLastCell(row, rightCol, sheetRightCol);
            if (verifiedcol > rightCol) {
                rightCol = verifiedcol;
            }
        }
    }
    if (maxRow < lastRow) {
        lastRow = maxRow;
    }
    // header range row set to 0 while column set to first column to
    // max
    // column (FF) e.g. $A$0 : $FF$0
    String tempStr = TieConstants.CELL_ADDR_PRE_FIX + WebSheetUtility.getExcelColumnName(leftCol)
            + TieConstants.CELL_ADDR_PRE_FIX + "0 : " + TieConstants.CELL_ADDR_PRE_FIX
            + WebSheetUtility.getExcelColumnName(rightCol) + TieConstants.CELL_ADDR_PRE_FIX + "0";
    sheetConfig.setFormHeaderRange(tempStr);
    sheetConfig.setHeaderCellRange(new CellRange(tempStr));
    // body range row set to first row to last row while column set
    // to
    // first column to max column (FF) e.g. $A$1 : $FF$1000
    tempStr = TieConstants.CELL_ADDR_PRE_FIX + WebSheetUtility.getExcelColumnName(leftCol)
            + TieConstants.CELL_ADDR_PRE_FIX + (firstRow + 1) + " : " + TieConstants.CELL_ADDR_PRE_FIX
            + WebSheetUtility.getExcelColumnName(rightCol) + TieConstants.CELL_ADDR_PRE_FIX + (lastRow + 1);
    sheetConfig.setFormBodyRange(tempStr);
    sheetConfig.setBodyCellRange(new CellRange(tempStr));
    sheetConfig.setFormBodyType(org.tiefaces.common.TieConstants.FORM_TYPE_FREE);
    sheetConfig.setCellFormAttributes(new HashMap<String, List<CellFormAttributes>>());

    // check it's a hidden sheet
    int sheetIndex = parent.getWb().getSheetIndex(sheet);
    if (parent.getWb().isSheetHidden(sheetIndex) || parent.getWb().isSheetVeryHidden(sheetIndex)) {
        sheetConfig.setHidden(true);
    }

    return sheetConfig;

}

From source file:org.tiefaces.components.websheet.configuration.ConfigurationHandler.java

License:MIT License

/**
 * Create sheet configuration from form command.
 *
 * @param sheet//from   w  w  w  .  j  av a  2 s .  c  o m
 *            sheet.
 * @param fcommand
 *            form command.
 * @param sheetRightCol
 *            the sheet right col
 * @return sheet configuration.
 */
private SheetConfiguration getSheetConfigurationFromConfigCommand(final Sheet sheet, final FormCommand fcommand,
        final int sheetRightCol) {

    SheetConfiguration sheetConfig = new SheetConfiguration();
    sheetConfig.setFormName(fcommand.getName());
    sheetConfig.setSheetName(sheet.getSheetName());
    int leftCol = fcommand.getLeftCol();
    int lastRow = fcommand.getLastRow();
    int rightCol = 0;
    int maxRow = 0;
    for (Row row : sheet) {
        if (row.getRowNum() > TieConstants.TIE_WEB_SHEET_MAX_ROWS) {
            break;
        }
        maxRow = row.getRowNum();
        if ((row.getLastCellNum() - 1) > rightCol) {
            int verifiedcol = verifyLastCell(row, rightCol, sheetRightCol);
            if (verifiedcol > rightCol) {
                rightCol = verifiedcol;
            }
        }
    }
    if (maxRow < lastRow) {
        lastRow = maxRow;
    }
    // header range row set to 0 while column set to first column to
    // max
    // column (FF) e.g. $A$0 : $FF$0
    setHeaderOfSheetConfiguration(fcommand, sheetConfig, leftCol, rightCol);
    // body range row set to first row to last row while column set
    // to
    // first column to max column (FF) e.g. $A$1 : $FF$1000
    setBodyOfSheetConfiguration(fcommand, sheetConfig, leftCol, lastRow, rightCol);

    // footer range row set to 0 while column set to first column to
    // max
    // column (FF) e.g. $A$0 : $FF$0
    setFooterOfSheetConfiguration(fcommand, sheetConfig, leftCol, rightCol);

    String hidden = fcommand.getHidden();
    if ((hidden != null) && (Boolean.parseBoolean(hidden))) {
        sheetConfig.setHidden(true);
    }
    String fixedWidthStyle = fcommand.getFixedWidthStyle();
    if ((fixedWidthStyle != null) && (Boolean.parseBoolean(fixedWidthStyle))) {
        sheetConfig.setFixedWidthStyle(true);
    }
    sheetConfig.setFormCommand(fcommand);
    return sheetConfig;

}

From source file:org.tiefaces.components.websheet.utility.CellUtility.java

License:MIT License

/**
 * Copy single row.//from  w  w w  . j  ava  2s. com
 *
 * @param srcSheet
 *            the src sheet
 * @param destSheet
 *            the dest sheet
 * @param sourceRowNum
 *            the source row num
 * @param destinationRowNum
 *            the destination row num
 * @param checkLock
 *            the check lock
 * @param setHiddenColumn
 *            the set hidden column
 */
private static void copySingleRow(final Sheet srcSheet, final Sheet destSheet, final int sourceRowNum,
        final int destinationRowNum, final boolean checkLock, final boolean setHiddenColumn) {
    // Get the source / new row
    Row newRow = destSheet.getRow(destinationRowNum);
    Row sourceRow = srcSheet.getRow(sourceRowNum);

    if (newRow == null) {
        newRow = destSheet.createRow(destinationRowNum);
    }
    newRow.setHeight(sourceRow.getHeight());
    // Loop through source columns to add to new row
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        // Grab a copy of the old/new cell
        copyCell(destSheet, sourceRow, newRow, i, checkLock);
    }
    if (setHiddenColumn) {
        ConfigurationUtility.setOriginalRowNumInHiddenColumn(newRow, sourceRow.getRowNum());
    }
    return;

}

From source file:org.tiefaces.components.websheet.utility.WebSheetUtility.java

License:MIT License

/**
 * Clear hidden columns.// ww  w  .j  a va  2  s.c  o m
 *
 * @param sheet
 *            the sheet
 */
public static void clearHiddenColumns(final Sheet sheet) {

    for (Row row : sheet) {
        if (row.getLastCellNum() > TieConstants.MAX_COLUMNS_IN_SHEET) {
            deleteHiddenColumnsInRow(row);
        }
    }

}

From source file:org.tuxedoberries.transformo.excel.XLSXDataReader.java

License:Open Source License

private RowData generateData(Row row) {
    if (row == null) {
        Logger.Error("Row is null");
        return null;
    }/* w ww.j  a  v a  2  s . com*/

    int total = row.getLastCellNum();
    RowData rdata = new RowData();

    for (int i = 0; i < total; ++i) {
        FieldMeta fmeta = _tmeta.GetOrCreateField(i);
        Cell cell = row.getCell(i);
        if (cell == null) {
            continue;
        }

        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_NUMERIC:
            rdata.addData(fmeta, cell.getNumericCellValue());
            break;

        case Cell.CELL_TYPE_STRING:
            rdata.addData(fmeta, cell.getStringCellValue());
            break;

        case Cell.CELL_TYPE_BOOLEAN:
            rdata.addData(fmeta, cell.getBooleanCellValue());
            break;

        case Cell.CELL_TYPE_BLANK:
            Logger.Warning("Empty Data Field. Expected a [%s]", fmeta.DataType);
            break;

        case Cell.CELL_TYPE_FORMULA:
            Logger.Warning("Data Type is not supported [FORMULA]");
            break;

        case Cell.CELL_TYPE_ERROR:
            Logger.Error("Error Reading Cell");
            break;
        }
    }

    return rdata;
}

From source file:org.tuxedoberries.transformo.excel.XLSXTableMetaReader.java

License:Open Source License

private void generateNames(Row row, TableMeta tmeta) {
    int total = row.getLastCellNum();
    for (int i = 0; i < total; ++i) {
        FieldMeta fmeta = tmeta.GetOrCreateField(i);
        Cell cell = row.getCell(i);//  w ww.  j ava  2s  .com
        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            String name = cell.getStringCellValue();
            // Clean
            name = name.trim();
            if (name.endsWith("@")) {
                name = name.replace("@", "");
                fmeta.IsKey = true;
            }

            fmeta.FieldName = name;
        } else {
            Logger.Error("Bad Cell Type [%s]. String is expected.", cell.getCellType());
        }
    }
}

From source file:org.tuxedoberries.transformo.excel.XLSXTableMetaReader.java

License:Open Source License

private void generateShortNames(Row row, TableMeta tmeta) {
    int total = row.getLastCellNum();
    for (int i = 0; i < total; ++i) {
        FieldMeta fmeta = tmeta.GetOrCreateField(i);
        Cell cell = row.getCell(i);//from   w w w  . j a  v  a  2s.  c om
        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            fmeta.FieldShortName = cell.getStringCellValue();
        } else {
            Logger.Error("Bad Cell Type [%s]. String is expected.", cell.getCellType());
        }
    }
}

From source file:org.tuxedoberries.transformo.excel.XLSXTableMetaReader.java

License:Open Source License

private void generateTypes(Row row, TableMeta tmeta) {
    int total = row.getLastCellNum();
    for (int i = 0; i < total; ++i) {
        FieldMeta fmeta = tmeta.GetOrCreateField(i);
        fmeta.FieldIndex = i;//from www  .j a  va  2  s  .  c o  m
        Cell cell = row.getCell(i);
        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            setDataType(cell, fmeta);
        } else {
            Logger.Error("Bad Cell Type [%s]. String is expected.", cell.getCellType());
        }
    }
}

From source file:org.ujmp.poi.AbstractMatrixExcelImporter.java

License:Open Source License

public DenseObjectMatrix2D importFromSheet(final Sheet sheet) throws InvalidFormatException, IOException {
    final int rowCount = sheet.getLastRowNum();
    int columnCount = 0;

    Iterator<Row> rowIterator = sheet.rowIterator();
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        if (row.getLastCellNum() > columnCount) {
            columnCount = row.getLastCellNum();
        }/*from  ww w .j  a  v  a 2 s . c  o m*/
    }

    final DefaultDenseObjectMatrix2D matrix = new DefaultDenseObjectMatrix2D(rowCount, columnCount);
    matrix.setLabel(sheet.getSheetName());

    for (int r = 0; r < rowCount; r++) {
        Row row = sheet.getRow(r);
        if (row != null) {
            for (int c = 0; c < columnCount; c++) {
                Cell cell = row.getCell(c);
                if (cell != null) {
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_BLANK:
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        matrix.setAsBoolean(cell.getBooleanCellValue(), r, c);
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        matrix.setAsString(cell.getCellFormula(), r, c);
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        matrix.setAsDouble(cell.getNumericCellValue(), r, c);
                        break;
                    case Cell.CELL_TYPE_STRING:
                        matrix.setAsString(cell.getStringCellValue(), r, c);
                        break;
                    default:
                        break;
                    }

                }
            }
        }
    }

    return matrix;
}