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

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

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

Introduction

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

Prototype

Iterator<Cell> cellIterator();

Source Link

Usage

From source file:com.nikoo28.excel.parser.ExcelParser.java

License:Apache License

public String parseExcelData(InputStream is) {
    try {//from   w  w w  .jav  a 2  s .  co  m
        HSSFWorkbook workbook = new HSSFWorkbook(is);

        // Taking first sheet from the workbook
        HSSFSheet sheet = workbook.getSheetAt(0);

        // Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();
        currentString = new StringBuilder();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();

            // For each row, iterate through each columns
            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) {

                Cell cell = cellIterator.next();

                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_BOOLEAN:
                    bytesRead++;
                    currentString.append(cell.getBooleanCellValue() + "\t");
                    break;

                case Cell.CELL_TYPE_NUMERIC:
                    bytesRead++;
                    currentString.append(cell.getNumericCellValue() + "\t");
                    break;

                case Cell.CELL_TYPE_STRING:
                    bytesRead++;
                    currentString.append(cell.getStringCellValue() + "\t");
                    break;

                }
            }
            currentString.append("\n");
        }
        is.close();
    } catch (IOException e) {
        LOG.error("IO Exception : File not found " + e);
    }
    return currentString.toString();

}

From source file:com.pdf.GetPdf.java

public static void addXls(Document document, String url, String type) throws IOException, DocumentException {
    Iterator<Row> rowIterator;
    int colNo;/*ww  w  .ja va  2 s .  com*/
    if (type.equals("xls")) {
        HSSFWorkbook excelWorkbook = new HSSFWorkbook(new URL(url).openStream());
        HSSFSheet my_worksheet = excelWorkbook.getSheetAt(0);
        rowIterator = my_worksheet.iterator();
        colNo = my_worksheet.getRow(0).getLastCellNum();
    } else {
        XSSFWorkbook excelWorkbook1 = new XSSFWorkbook(new URL(url).openStream());
        XSSFSheet my_worksheet = excelWorkbook1.getSheetAt(0);
        rowIterator = my_worksheet.iterator();
        colNo = my_worksheet.getRow(0).getLastCellNum();
    }
    PdfPTable my_table = new PdfPTable(colNo);
    PdfPCell table_cell = null;
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next(); //Read Rows from Excel document       
        Iterator<Cell> cellIterator = row.cellIterator();//Read every column for every row that is READ
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next(); //Fetch CELL
            if (cell.getCellType() == (Cell.CELL_TYPE_NUMERIC)) {
                table_cell = new PdfPCell(new Phrase(new Double(cell.getNumericCellValue()).toString()));
                System.out.println(cell.getNumericCellValue());
                my_table.addCell(table_cell);
            } else if (cell.getCellType() == (Cell.CELL_TYPE_STRING)) {
                table_cell = new PdfPCell(new Phrase(cell.getStringCellValue()));
                System.out.println(cell.getStringCellValue());
                my_table.addCell(table_cell);
            } else if (cell.getCellType() == (Cell.CELL_TYPE_FORMULA)) {
                table_cell = new PdfPCell(new Phrase(cell.getCellFormula()));
                my_table.addCell(table_cell);
            } else if (cell.getCellType() == (Cell.CELL_TYPE_BLANK)) {
                table_cell = new PdfPCell(new Phrase(""));
                my_table.addCell(table_cell);
            } else {
                table_cell = new PdfPCell(new Phrase(""));
                my_table.addCell(table_cell);
            }
        }
    }
    document.add(my_table);
}

From source file:com.qihang.winter.poi.excel.imports.ExcelImportServer.java

License:Apache License

/**
 * ????/*from w  w w  .ja  v a  2 s. c  o  m*/
 * @param rows
 * @param params
 * @param excelCollection
 * @return
 */
private Map<Integer, String> getTitleMap(Iterator<Row> rows,
        com.qihang.winter.poi.excel.entity.ImportParams params,
        List<com.qihang.winter.poi.excel.entity.params.ExcelCollectionParams> excelCollection) {
    Map<Integer, String> titlemap = new HashMap<Integer, String>();
    Iterator<Cell> cellTitle;
    String collectionName = null;
    com.qihang.winter.poi.excel.entity.params.ExcelCollectionParams collectionParams = null;
    Row row = null;
    for (int j = 0; j < params.getHeadRows(); j++) {
        row = rows.next();
        if (row == null) {
            continue;
        }
        cellTitle = row.cellIterator();
        while (cellTitle.hasNext()) {
            Cell cell = cellTitle.next();
            String value = getKeyValue(cell);
            int i = cell.getColumnIndex();
            //????
            if (StringUtils.isNotEmpty(value)) {
                if (titlemap.containsKey(i)) {
                    collectionName = titlemap.get(i);
                    collectionParams = getCollectionParams(excelCollection, collectionName);
                    titlemap.put(i, collectionName + "_" + value);
                } else if (StringUtils.isNotEmpty(collectionName)
                        && collectionParams.getExcelParams().containsKey(collectionName + "_" + value)) {
                    titlemap.put(i, collectionName + "_" + value);
                } else {
                    collectionName = null;
                    collectionParams = null;
                }
                if (StringUtils.isEmpty(collectionName)) {
                    titlemap.put(i, value);
                }
            }
        }
    }
    return titlemap;
}

From source file:com.qualogy.qafe.service.DocumentServiceImpl.java

License:Apache License

private DocumentOutput handleExcelData(Sheet sheetData, boolean hasRowHeader) {
    DocumentOutput docOutput = new DocumentOutput();

    // Determine the column names
    List<String> columnNameList = new ArrayList<String>();
    if (sheetData.rowIterator().hasNext()) {
        Row row = sheetData.rowIterator().next();
        int emptyColCountChain = 0;
        String colName = null;//from   ww  w.jav a2 s  . c om
        for (Iterator<Cell> itr = row.cellIterator(); itr.hasNext();) {
            Cell cell = itr.next();
            boolean cellHasData = (cell.getCellType() != Cell.CELL_TYPE_BLANK);
            if (hasRowHeader && cellHasData) {
                colName = getCellValue(cell);
            } else {
                colName = DEFAULT_FIELD_NAME + cell.getColumnIndex();
            }
            columnNameList.add(colName);

            if (cellHasData) {
                emptyColCountChain = 0;
            } else {
                emptyColCountChain++;
            }
            if (emptyColCountChain > EMPTY_NUMCOLUMNS_TOLERANCE) {
                break;
            }
        }
    }

    // Get the data from sheet
    List<Map<String, String>> data = new ArrayList<Map<String, String>>();
    boolean[] columnsHaveData = new boolean[columnNameList.size()];
    for (Iterator<Row> itr = sheetData.rowIterator(); itr.hasNext();) {
        Row row = itr.next();
        if (hasRowHeader && (row.getRowNum() == 0)) {
            continue;
        }
        Map<String, String> rowData = new LinkedHashMap<String, String>();
        boolean rowHasData = false;
        for (Iterator<Cell> itr2 = row.cellIterator(); itr2.hasNext();) {
            Cell cell = itr2.next();
            if (cell.getColumnIndex() < columnNameList.size()) {
                String colName = columnNameList.get(cell.getColumnIndex());
                String cellValue = null;
                if (cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                    cellValue = getCellValue(cell);
                }
                boolean cellHasData = ((cellValue != null) && (cellValue.length() > 0));
                columnsHaveData[cell.getColumnIndex()] = columnsHaveData[cell.getColumnIndex()] || cellHasData;
                rowHasData = rowHasData || cellHasData;
                rowData.put(colName, cellValue);
            } else {
                break;
            }
        }
        if (rowHasData) {
            data.add(rowData);
        }
    }

    removeEmptyColumns(columnNameList, data, columnsHaveData);

    printData(data);
    docOutput.setData(data);
    return docOutput;
}

From source file:com.r573.enfili.common.doc.spreadsheet.SpreadsheetHelper.java

License:Apache License

public static void calculate(Sheet sheet) {
    FormulaEvaluator evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
    for (Iterator<Row> rowIterator = sheet.rowIterator(); rowIterator.hasNext();) {
        Row row = rowIterator.next();
        for (Iterator<Cell> cellIterator = row.cellIterator(); cellIterator.hasNext();) {
            Cell cell = cellIterator.next();
            if (cell != null && cell.getCellType() == XSSFCell.CELL_TYPE_FORMULA) {
                evaluator.evaluateInCell(cell);
            }/*w  w w  . j av  a2  s.  c o m*/
        }
    }
}

From source file:com.read.main.LeerPDF.java

/**
 * @param args the command line arguments
 *///w ww  .  j a v a  2 s  .c  o  m
public static void main(String[] args) throws IOException {
    try {

        FileInputStream file = new FileInputStream(new File("/home/aaron/Escritorio/Example.xlsx"));
        XSSFWorkbook workbook2 = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook2.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.iterator();

        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();

            System.out.println("Numero de Columnas: " + row.getLastCellNum());

            System.out.println(row.getRowNum());

            if (row.getRowNum() == 0) {
                System.out.println("Fila Cero");
            } else {

                int numColumna = 0;

                while (numColumna < row.getLastCellNum()) {

                    Cell cell = row.getCell(numColumna);

                    try {
                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_BOOLEAN:
                            System.out.print(numColumna + ".- BOOLEAN: ");
                            System.out.print(cell.getBooleanCellValue() + "\t\t");
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            System.out.print(numColumna + ".- NUMERIC: ");
                            System.out.print(cell.getNumericCellValue() + "\t\t");
                            break;
                        case Cell.CELL_TYPE_STRING:
                            System.out.print(numColumna + ".- STRING: ");
                            System.out.print(cell.getStringCellValue() + "\t\t");
                            break;
                        }
                    } catch (Exception e) {
                        System.err.println(e);
                    }
                    ;

                    numColumna++;
                }
            }

            System.out.println("");
        }
        file.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

From source file:com.runwaysdk.dataaccess.io.excel.ContextBuilder.java

License:Open Source License

@Override
public void configure(ImportContext context, Row typeRow, Row nameRow, Row labelRow) {
    // Copy the type, name, and label rows to the error sheet
    context.addErrorRow(typeRow);//from w ww .j  av a  2  s  .c o  m
    context.addErrorRow(nameRow);
    context.addErrorRow(labelRow);

    // To start, assume that everything is an extra column. We'll move expected
    // ones to the correct list soon
    Iterator<Cell> nameIterator = nameRow.cellIterator();
    Iterator<Cell> labelIterator = labelRow.cellIterator();
    while (nameIterator.hasNext()) {
        Cell name = nameIterator.next();
        Cell label = labelIterator.next();
        context.addExtraColumn(
                new ExcelColumn(ExcelUtil.getString(name), ExcelUtil.getString(label), name.getColumnIndex()));
    }

    // Build columns for all of the expected attributes
    List<? extends MdAttributeDAOIF> attributes = this.getAttributes(context);

    for (MdAttributeDAOIF mdAttribute : attributes) {
        this.buildAttributeColumn(context, mdAttribute);
    }

    // Map the index for the expected types
    Iterator<AttributeColumn> expectedIterator = context.getExpectedColumns().iterator();
    while (expectedIterator.hasNext()) {
        ExcelColumn expected = expectedIterator.next();
        boolean match = false;

        Iterator<ExcelColumn> extraIterator = context.getExtraColumns().iterator();
        while (extraIterator.hasNext()) {
            ExcelColumn extra = extraIterator.next();
            if (extra.equals(expected)) {
                extraIterator.remove();
                expected.setIndex(extra.getIndex());
                match = true;
                break;
            }
        }

        // No matches found for the expected column. We need to remove it.
        if (!match) {
            expectedIterator.remove();
        }
    }
    // At this point every column is either in the expected list or the extra
    // list.
}

From source file:com.runwaysdk.dataaccess.io.excel.ErrorSheet.java

License:Open Source License

public void addRow(Row _row) {
    Row row = this.errorSheet.createRow(count++);
    row.setZeroHeight(_row.getZeroHeight());
    row.setHeight(_row.getHeight());//from   ww w.j  a  va 2 s .c  o m

    CellStyle style = _row.getRowStyle();

    if (style != null) {
        Workbook workbook = row.getSheet().getWorkbook();

        CellStyle clone = workbook.createCellStyle();
        clone.cloneStyleFrom(style);

        row.setRowStyle(clone);
    }

    Iterator<Cell> cellIterator = _row.cellIterator();
    while (cellIterator.hasNext()) {
        Cell oldCell = cellIterator.next();
        Cell newCell = row.createCell(oldCell.getColumnIndex());

        int cellType = oldCell.getCellType();

        if (cellType == Cell.CELL_TYPE_FORMULA) {
            cellType = oldCell.getCachedFormulaResultType();
        }

        switch (cellType) {
        case Cell.CELL_TYPE_BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getRichStringCellValue());
            break;
        }
    }
}

From source file:com.runwaysdk.dataaccess.io.ExcelImporter.java

License:Open Source License

/**
 * Checks to see if the given row has specified at least one column with a value
 * /*from  w w  w .  j  a  v  a  2s . c o  m*/
 * @param row
 * @return
 */
private boolean rowHasValues(Row row) {
    Iterator<Cell> cellIterator = row.cellIterator();

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

        if (cellType == Cell.CELL_TYPE_FORMULA) {
            cellType = cell.getCachedFormulaResultType();
        }

        Object value = null;

        switch (cellType) {
        case Cell.CELL_TYPE_STRING:
            value = ExcelUtil.getString(cell);
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            value = ExcelUtil.getBoolean(cell);
            break;
        case Cell.CELL_TYPE_NUMERIC:
            value = cell.getNumericCellValue();
            break;
        }

        if (value == null) {
            continue;
        }

        if (value.toString().trim().length() > 0) {
            return true;
        }
    }
    return false;
}

From source file:com.salahatwa.randomme.ReadXLS.java

/**
 * @param filePath/*from  www.  j ava2s . c om*/
 * @return  list of Readed cells from xlsx
 */
public List<ReadedBean> readXLSFromFile(String filePath) {
    List<ReadedBean> data = new ArrayList();
    FileInputStream fis = null;
    try {
        fis = new FileInputStream(filePath);
        // Using XSSF for xlsx format, for xls use HSSF

        Workbook workbook = new XSSFWorkbook(fis);

        int numberOfSheets = workbook.getNumberOfSheets();

        //looping over each workbook sheet
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = workbook.getSheetAt(i);
            Iterator rowIterator = sheet.iterator();

            //iterating over each row
            while (rowIterator.hasNext()) {

                ReadedBean readedBean = new ReadedBean();
                Row row = (Row) rowIterator.next();
                Iterator cellIterator = row.cellIterator();
                //Iterating over each cell (column wise)  in a particular row.
                while (cellIterator.hasNext()) {
                    Cell cell = (Cell) cellIterator.next();

                    if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                        if (cell.getColumnIndex() == 0) {
                            readedBean.setCell(cell.getStringCellValue());
                        }

                    } else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {

                        if (cell.getColumnIndex() == 0) {
                            readedBean.setCell(String.valueOf((int) cell.getNumericCellValue()));
                        }
                        //                           
                    }
                }
                System.out.println(readedBean.getCell());
                data.add(readedBean);
            }
        }
        fis.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    return data;
}