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

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

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. getFirstCellNum

Introduction

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

Prototype

short getFirstCellNum();

Source Link

Document

Get the number of the first cell contained in this row.

Usage

From source file:org.ramadda.data.docs.TabularOutputHandler.java

License:Apache License

/**
 * _more_/*from   w  w w. ja  v  a2 s.c  o m*/
 *
 * @param request _more_
 * @param entry _more_
 * @param suffix _more_
 * @param inputStream _more_
 * @param visitInfo _more_
 * @param visitor _more_
 *
 * @throws Exception _more_
 */
private void visitXls(Request request, Entry entry, String suffix, InputStream inputStream,
        TextReader visitInfo, TabularVisitor visitor) throws Exception {
    //        System.err.println("visitXls: making workbook");
    Workbook wb = makeWorkbook(suffix, inputStream);
    //        System.err.println("visitXls:" + visitInfo.getSkip() + " max rows:" + visitInfo.getMaxRows()+ " #sheets:" + wb.getNumberOfSheets());
    int maxRows = visitInfo.getMaxRows();
    for (int sheetIdx = 0; sheetIdx < wb.getNumberOfSheets(); sheetIdx++) {
        if (!visitInfo.okToShowSheet(sheetIdx)) {
            continue;
        }
        Sheet sheet = wb.getSheetAt(sheetIdx);
        //            System.err.println("\tsheet:" + sheet.getSheetName() + " #rows:" + sheet.getLastRowNum());
        List<List<Object>> rows = new ArrayList<List<Object>>();
        int sheetSkip = visitInfo.getSkip();
        for (int rowIdx = sheet.getFirstRowNum(); (rows.size() < maxRows)
                && (rowIdx <= sheet.getLastRowNum()); rowIdx++) {
            if (sheetSkip-- > 0) {
                continue;
            }

            Row row = sheet.getRow(rowIdx);
            if (row == null) {
                continue;
            }
            List<Object> cols = new ArrayList<Object>();
            short firstCol = row.getFirstCellNum();
            for (short col = firstCol; (col < MAX_COLS) && (col < row.getLastCellNum()); col++) {
                Cell cell = row.getCell(col);
                if (cell == null) {
                    break;
                }
                Object value = null;
                int type = cell.getCellType();
                if (type == cell.CELL_TYPE_NUMERIC) {
                    value = new Double(cell.getNumericCellValue());
                } else if (type == cell.CELL_TYPE_BOOLEAN) {
                    value = new Boolean(cell.getBooleanCellValue());
                } else if (type == cell.CELL_TYPE_ERROR) {
                    value = "" + cell.getErrorCellValue();
                } else if (type == cell.CELL_TYPE_BLANK) {
                    value = "";
                } else if (type == cell.CELL_TYPE_FORMULA) {
                    value = cell.getCellFormula();
                } else {
                    value = cell.getStringCellValue();
                }
                cols.add(value);
            }

            /**
             * ** TODO
             * org.ramadda.util.text.Row row = new Row(cols);
             *
             * if ( !visitInfo.rowOk(row)) {
             *   if (rows.size() == 0) {
             *       //todo: check for the header line
             *   } else {
             *       continue;
             *   }
             * }
             */
            rows.add(cols);
        }
        if (!visitor.visit(visitInfo, sheet.getSheetName(), rows)) {
            break;
        }
    }
}

From source file:org.ramadda.data.docs.TabularOutputHandler.java

License:Apache License

/**
 * _more_/*from   w ww.  j a v a 2  s .  co  m*/
 *
 * @param args _more_
 *
 * @throws Exception _more_
 */
public static void main(String[] args) throws Exception {
    Workbook wb = makeWorkbook(IOUtil.getFileExtension(args[0]), new FileInputStream(args[0]));
    for (int sheetIdx = 0; sheetIdx < wb.getNumberOfSheets(); sheetIdx++) {
        Sheet sheet = wb.getSheetAt(sheetIdx);
        System.err.println(sheet.getSheetName());
        for (int rowIdx = sheet.getFirstRowNum(); rowIdx <= sheet.getLastRowNum(); rowIdx++) {
            Row row = sheet.getRow(rowIdx);
            if (row == null) {
                continue;
            }
            short firstCol = row.getFirstCellNum();
            int colCnt = 0;
            for (short col = firstCol; col < row.getLastCellNum(); col++) {
                Cell cell = row.getCell(col);
                if (cell == null) {
                    break;
                }
                Object value = null;
                int type = cell.getCellType();
                if (type == cell.CELL_TYPE_NUMERIC) {
                    value = new Double(cell.getNumericCellValue());
                } else if (type == cell.CELL_TYPE_BOOLEAN) {
                    value = new Boolean(cell.getBooleanCellValue());
                } else if (type == cell.CELL_TYPE_ERROR) {
                    value = "" + cell.getErrorCellValue();
                } else if (type == cell.CELL_TYPE_BLANK) {
                    value = "";
                } else if (type == cell.CELL_TYPE_FORMULA) {
                    value = cell.getCellFormula();
                } else {
                    value = cell.getStringCellValue();
                }
                if (colCnt++ > 0)
                    System.out.print(",");
                System.out.print(value);
            }
            System.out.println("");
        }
    }
}

From source file:org.ramadda.plugins.media.TabularOutputHandler.java

License:Open Source License

/**
 * _more_/*from  w ww.j  a v  a  2 s.c  o  m*/
 *
 * @param request _more_
 * @param entry _more_
 * @param suffix _more_
 * @param inputStream _more_
 * @param visitInfo _more_
 * @param visitor _more_
 *
 * @throws Exception _more_
 */
private void visitXls(Request request, Entry entry, String suffix, InputStream inputStream, Visitor visitInfo,
        TabularVisitor visitor) throws Exception {
    //        System.err.println("visitXls: making workbook");
    Workbook wb = makeWorkbook(suffix, inputStream);
    //        System.err.println("visitXls:" + visitInfo.getSkip() + " max rows:" + visitInfo.getMaxRows()+ " #sheets:" + wb.getNumberOfSheets());
    int maxRows = visitInfo.getMaxRows();
    for (int sheetIdx = 0; sheetIdx < wb.getNumberOfSheets(); sheetIdx++) {
        if (!visitInfo.okToShowSheet(sheetIdx)) {
            continue;
        }
        Sheet sheet = wb.getSheetAt(sheetIdx);
        //            System.err.println("\tsheet:" + sheet.getSheetName() + " #rows:" + sheet.getLastRowNum());
        List<List<Object>> rows = new ArrayList<List<Object>>();
        int sheetSkip = visitInfo.getSkip();
        for (int rowIdx = sheet.getFirstRowNum(); (rows.size() < maxRows)
                && (rowIdx <= sheet.getLastRowNum()); rowIdx++) {
            if (sheetSkip-- > 0) {
                continue;
            }

            Row row = sheet.getRow(rowIdx);
            if (row == null) {
                continue;
            }
            List<Object> cols = new ArrayList<Object>();
            short firstCol = row.getFirstCellNum();
            for (short col = firstCol; (col < MAX_COLS) && (col < row.getLastCellNum()); col++) {
                Cell cell = row.getCell(col);
                if (cell == null) {
                    break;
                }
                Object value = null;
                int type = cell.getCellType();
                if (type == cell.CELL_TYPE_NUMERIC) {
                    value = new Double(cell.getNumericCellValue());
                } else if (type == cell.CELL_TYPE_BOOLEAN) {
                    value = new Boolean(cell.getBooleanCellValue());
                } else if (type == cell.CELL_TYPE_ERROR) {
                    value = "" + cell.getErrorCellValue();
                } else if (type == cell.CELL_TYPE_BLANK) {
                    value = "";
                } else if (type == cell.CELL_TYPE_FORMULA) {
                    value = cell.getCellFormula();
                } else {
                    value = cell.getStringCellValue();
                }
                cols.add(value);
            }

            /**
             * ** TODO
             * org.ramadda.util.text.Row row = new Row(cols);
             *
             * if ( !visitInfo.rowOk(row)) {
             *   if (rows.size() == 0) {
             *       //todo: check for the header line
             *   } else {
             *       continue;
             *   }
             * }
             */
            rows.add(cols);
        }
        if (!visitor.visit(visitInfo, sheet.getSheetName(), rows)) {
            break;
        }
    }
}

From source file:org.spdx.spdxspreadsheet.AbstractSheet.java

License:Apache License

/**
 * @param workbook/* ww w.ja va2  s . co m*/
 * @param sheetName
 */
public AbstractSheet(Workbook workbook, String sheetName) {
    sheet = workbook.getSheet(sheetName);
    if (sheet != null) {
        firstRowNum = sheet.getFirstRowNum();
        Row firstRow = sheet.getRow(firstRowNum);
        if (firstRow == null) {
            firstCellNum = 1;
        } else {
            firstCellNum = firstRow.getFirstCellNum();
        }
        findLastRow();
    } else {
        firstRowNum = 0;
        lastRowNum = 0;
        firstCellNum = 0;
    }
    createStyles(workbook);
}

From source file:org.specrunner.source.excel.SourceFactoryExcel.java

License:Open Source License

/**
 * Read content of a table, using the rows iterator and the number of
 * columns.// www. j  ava  2 s. com
 * 
 * @param table
 *            The table.
 * @param caption
 *            The table caption.
 * @param spanMap
 *            Map of span cells.
 * @param ignore
 *            Set of cells to ignore.
 * @param ite
 *            The row iterator.
 * @param columns
 *            The number of columns to read.
 */
protected void readBody(Element table, Element caption, Map<String, Dimension> spanMap, Set<String> ignore,
        Iterator<Row> ite, int columns) {
    Element tbody = new Element("tbody");
    table.appendChild(tbody);
    {
        while (ite.hasNext()) {
            Element tr = new Element("tr");
            tbody.appendChild(tr);
            {
                Row row = ite.next();
                // invalid lines return -1 in row.getFirstCellNum().
                if (row.getFirstCellNum() < 0) {
                    continue;
                }
                for (int k = 0; k < columns; k++) {
                    Cell cell = row.getCell(k);
                    String key = null;
                    if (cell != null) {
                        key = cell.getRowIndex() + "," + cell.getColumnIndex();
                        if (ignore.contains(key)) {
                            continue;
                        }
                    }
                    Element td = new Element("td");
                    tr.appendChild(td);
                    td.appendChild(String.valueOf(extractVal(cell)));
                    if (cell != null) {
                        addAttributes(table, caption, tr, td, cell, spanMap.get(key));
                    }
                }
            }
        }
    }
}

From source file:org.spionen.james.jamesfile.ExcelJamesFile.java

License:Open Source License

public Map<Long, Subscriber> readFile(File file) throws IOException {
    Map<Long, Subscriber> subscribers = new TreeMap<Long, Subscriber>();
    try {//w w  w.  java 2s.  c o m
        Workbook wb = WorkbookFactory.create(file);
        Sheet s = wb.getSheetAt(0);

        // Take first row, use to check order of fields
        Row firstRow = s.getRow(s.getFirstRowNum());
        FieldType[] order = new FieldType[firstRow.getLastCellNum() - firstRow.getFirstCellNum()];
        int j = 0;
        for (int i = firstRow.getFirstCellNum(); i < firstRow.getLastCellNum(); i++, j++) {
            Cell c = firstRow.getCell(i);
            if (c != null) {
                if (c.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    String val = new Integer(new Double(c.getNumericCellValue()).intValue()).toString();
                    order[j] = FieldType.getFieldType(val);
                } else {
                    order[j] = FieldType.getFieldType(c.getStringCellValue());
                }
            }
        }
        // Then iterate through the rest of the rows
        if (s.getLastRowNum() > 0) {
            // LastRowNum is 0-indexed, so add 1
            for (int i = s.getFirstRowNum() + 1; i < s.getLastRowNum() + 1; i++) {
                Row r = s.getRow(i);
                Subscriber sub = new Subscriber();
                j = 0;
                // LastCellNum is also 0-indexed
                for (int k = r.getFirstCellNum(); k < r.getLastCellNum() + 1; k++, j++) {
                    Cell c = r.getCell(k);
                    if (c != null) {
                        if (c.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                            String val = new Integer(new Double(c.getNumericCellValue()).intValue()).toString();
                            sub.setByField(order[j], val);
                        } else {
                            sub.setByField(order[j], c.getStringCellValue());
                        }
                    }
                }
                subscribers.put(sub.getAbNr(), sub);
            }
        }
        return subscribers;
    } catch (InvalidFormatException ife) {
        // If the file was badly formatted
        throw new IOException(ife);
    }
}

From source file:org.teiid.translator.excel.BaseExcelExecution.java

License:Apache License

public Row nextRow() throws TranslatorException, DataNotAvailableException {
    while (true) {
        Row row = nextRowInternal();
        if (row == null) {
            return null;
        }/*from   w w  w .  j  a  v  a  2s. c  o  m*/

        // when the first cell number is -1, then it is empty row, skip it
        if (row.getFirstCellNum() == -1) {
            continue;
        }

        if (!this.visitor.allows(row.getRowNum())) {
            continue;
        }
        return row;
    }
}

From source file:org.teiid.translator.excel.ExcelExecution.java

License:Open Source License

@Override
public List<?> next() throws TranslatorException, DataNotAvailableException {
    while (hasNext()) {
        Row row = nextRow();
        // when the first cell number is -1, then it is empty row, skip it
        if (row.getFirstCellNum() == -1) {
            continue;
        }//from w w  w .jav a  2s.  c  om

        if (!this.visitor.allows(row.getRowNum())) {
            continue;
        }
        return projectRow(row);
    }
    return null;
}

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 a 2 s  . c  o 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.teiid.translator.excel.ExcelUpdateExecution.java

License:Apache License

private void handleDelete() throws TranslatorException {
    while (true) {
        Row row = nextRow();
        if (row == null) {
            break;
        }//w ww.  j  ava 2s  . c  o m
        this.rowIterator = null;
        int start = row.getRowNum();
        Sheet sheet = row.getSheet();
        int end = sheet.getLastRowNum();
        //a different iteration style is needed, which will not perform as well for sparse documents
        for (int i = start; i <= end; i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            if (row.getFirstCellNum() == -1) {
                continue;
            }

            if (!this.visitor.allows(row.getRowNum())) {
                continue;
            }
            sheet.removeRow(row);
            result++;
            modified = true;
        }
    }
}