Example usage for org.apache.poi.hssf.usermodel HSSFRow cellIterator

Introduction

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

Prototype

@Override
public Iterator<Cell> cellIterator() 

Source Link

Usage

From source file:no.trank.openpipe.parse.ms.ExcelParser.java

License:Apache License

private String getText(final HSSFWorkbook doc) {
    StringBuilder text = new StringBuilder();

    for (int sheetNumber = 0; sheetNumber < doc.getNumberOfSheets(); ++sheetNumber) {
        if (sheetNumber > 0) {
            text.append('\n');
        }/* www  . j  a v a  2 s.c o  m*/

        HSSFSheet sheet = doc.getSheetAt(sheetNumber);

        for (Iterator<?> rowIterator = sheet.rowIterator(); rowIterator.hasNext();) {
            HSSFRow row = (HSSFRow) rowIterator.next();

            boolean firstCell = true;
            for (Iterator<?> cellIterator = row.cellIterator(); cellIterator.hasNext();) {
                HSSFCell cell = (HSSFCell) cellIterator.next();
                String cellText = getCellText(cell);
                if (cellText != null) {
                    if (!firstCell) {
                        text.append(' ');
                    }
                    firstCell = false;

                    text.append(cellText);
                }
            }
            if (!firstCell) {
                text.append('\n');
            }
        }
    }

    return text.toString();
}

---

From source file:npv.importer.XlsImporter.java

private Double[] parseFile() throws IOException {
    InputStream inputStream = new FileInputStream(file);
    POIFSFileSystem fs = new POIFSFileSystem(inputStream);
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFSheet sheet = wb.getSheetAt(0);//from   w ww .  ja v a2s . c  o m
    System.out.println("Testing.First row num=" + sheet.getFirstRowNum());
    findTag(sheet, tag);

    //reading an array of Ri values after '#Ri' tag
    HSSFRow row = sheet.getRow(rPosition[0]);
    ArrayList<Double> cellValues = new ArrayList<Double>();

    Iterator<Cell> cellIterator = row.cellIterator();
    while (cellIterator.hasNext()) {
        Cell cell = cellIterator.next();
        if (cell.getColumnIndex() >= rPosition[1] + 1) {
            cellValues.add(cell.getNumericCellValue());
        }
    }

    rValues = new Double[cellValues.size()];
    rValues = cellValues.toArray(new Double[rValues.length]);
    System.out.println("Values from sheet:");
    for (int i = 0; i < rValues.length; i++) {
        System.out.println(rValues[i]);
    }

    return this.rValues;
}

---

From source file:org.ActSrnv03.core.ResidencialTopComponent.java

public void leerArchivoExel() throws IOException {
    JFileChooser jfc = new JFileChooser();
    jfc.showOpenDialog(this);
    File abre = jfc.getSelectedFile();

    JOptionPane.showMessageDialog(null, "\nEl nombre del archivo es:" + abre, "AVISO",
            JOptionPane.WARNING_MESSAGE);

    this.nombre = abre;
    // An excel file name. You can create a file name with a full
    // path information.
    ///*from ww  w  . j av  a2 s . c  o m*/
    //       String filename = "test.xls";
    String filename = this.nombre.getAbsolutePath();
    //
    // Create an ArrayList to store the data read from excel sheet.
    //
    List sheetData = new ArrayList();
    FileInputStream fis = null;
    try {
        //
        // Create a FileInputStream that will be use to read the
        // excel file.
        //
        fis = new FileInputStream(filename);
        //
        // Create an excel workbook from the file system.
        //
        HSSFWorkbook workbook = new HSSFWorkbook(fis);
        //
        // Get the first sheet on the workbook.
        //
        HSSFSheet sheet = workbook.getSheetAt(0);
        //
        // When we have a sheet object in hand we can iterator on
        // each sheet's rows and on each row's cells. We store the
        // data read on an ArrayList so that we can printed the
        // content of the excel to the console.
        //
        Iterator rows = sheet.rowIterator();
        while (rows.hasNext()) {
            HSSFRow row = (HSSFRow) rows.next();

            Iterator cells = row.cellIterator();
            List data = new ArrayList();
            while (cells.hasNext()) {
                HSSFCell cell = (HSSFCell) cells.next();
                //  System.out.println("Aadiendo Celda: " + cell.toString());
                data.add(cell);
            }
            sheetData.add(data);
        }
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        if (fis != null) {
            fis.close();
        }
    }
    //    showExelData(sheetData);
    procesaCertificaciones(sheetData);
}

---

From source file:org.amanzi.splash.importer.ExcelImporter.java

License:Open Source License

/**
 * Creates a child spreadsheet inside parent spreadsheet
 *
 * @param sheet sheet to import//  w  w  w  .  j ava2s  .co m
 * @param sheetName name of sheet
 * @param monitor monitor
 */
@SuppressWarnings(value = { "deprecation", "unchecked" })
private void createSheet(HSSFSheet sheet, String sheetName, Transaction transaction) {
    spreadsheetNode = null;
    spreadsheetName = sheetName;

    createSpreadsheet(rootSpreadsheet);
    try {
        Iterator<HSSFRow> rows = sheet.rowIterator();

        while (rows.hasNext()) {
            HSSFRow row = rows.next();

            //display row number in the console.
            LOGGER.debug("Row No.: " + row.getRowNum());

            //once get a row its time to iterate through cells.
            Iterator<HSSFCell> cells = row.cellIterator();

            int R = row.getRowNum();

            if ((R % 20) == 0) {
                updateTransaction(transaction);
            }

            while (cells.hasNext()) {
                HSSFCell cell = cells.next();

                LOGGER.debug("Cell No.: " + cell.getCellNum());

                int C = cell.getCellNum();

                /*
                 * Now we will get the cell type and display the values
                 * accordingly.
                 */
                switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC: {

                    // cell type numeric.
                    LOGGER.debug("====================================================");
                    LOGGER.debug("Numeric value: " + cell.getNumericCellValue());
                    LOGGER.debug("====================================================");
                    String def = Double.toString(cell.getNumericCellValue());

                    Cell c = new Cell(R, C, def, def, new CellFormat());
                    //TODO: interpet!!!!!!
                    //Cell c = model.interpret(def, R, C);

                    saveCell(c);
                    break;
                }

                case HSSFCell.CELL_TYPE_STRING: {
                    // cell type string.
                    HSSFRichTextString richTextString = cell.getRichStringCellValue();
                    LOGGER.debug("====================================================");
                    LOGGER.debug("String value: " + richTextString.getString());
                    LOGGER.debug("====================================================");
                    Cell c = new Cell(R, C, richTextString.getString(), richTextString.getString(),
                            new CellFormat());
                    saveCell(c);
                    break;
                }

                case HSSFCell.CELL_TYPE_FORMULA:
                    // cell type string.
                    String cellFormula = "=" + cell.getCellFormula().toLowerCase();

                    Cell c = new Cell(R, C, cellFormula, cellFormula, new CellFormat());
                    //TODO: interpet!!!!!!
                    //Cell c = model.interpret(def, R, C);

                    saveCell(c);

                    LOGGER.debug("====================================================");
                    LOGGER.debug("Formula value: " + cellFormula);
                    LOGGER.debug("====================================================");

                    break;

                default: {

                    // types other than String and Numeric.
                    LOGGER.debug("Type not supported.");

                    break;
                }
                }
            }
        }
    } finally {
        updateTransaction(transaction);
    }
}

---

From source file:org.apache.cocoon.generation.HSSFGenerator.java

License:Apache License

/**
 * Writes out the workbook data as XML, without formatting information
 *///w  ww.  j a va2 s  .c  o m
private void writeXML(HSSFWorkbook workbook) throws SAXException {
    this.contentHandler.startDocument();
    start("Workbook");
    start("SheetNameIndex");
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        start("SheetName");
        data(workbook.getSheetName(i));
        end("SheetName");
    }
    end("SheetNameIndex");
    start("Sheets");
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        HSSFSheet sheet = workbook.getSheetAt(i);
        start("Sheet");
        start("Name");
        data(workbook.getSheetName(i));
        end("Name");
        start("MaxCol");
        data(Integer.toString(getMaxCol(sheet)));
        end("MaxCol");
        start("MaxRow");
        data(Integer.toString(sheet.getLastRowNum()));
        end("MaxRow");
        if (formatting) {
            writeStyles(workbook, sheet);
        }

        start("Cells");
        final Iterator rows = sheet.rowIterator();
        while (rows.hasNext()) {
            final HSSFRow row = (HSSFRow) rows.next();
            final Iterator cells = row.cellIterator();
            while (cells.hasNext()) {
                final HSSFCell cell = (HSSFCell) cells.next();
                attribute("Row", Integer.toString(row.getRowNum()));
                attribute("Col", Short.toString(cell.getCellNum()));
                attribute("ValueType", getValueType(cell.getCellType()));
                start("Cell");
                data(getValue(cell));
                end("Cell");
            }
        }
        end("Cells");

        end("Sheet");
    }
    end("Sheets");
    end("Workbook");
    this.contentHandler.endDocument();
}

---

From source file:org.apache.cocoon.generation.HSSFGenerator.java

License:Apache License

/**
 * Writes out the workbook data as XML, with formatting information
 *///  w ww  . j av a 2s .c  om
private void writeStyles(HSSFWorkbook workbook, HSSFSheet sheet) throws SAXException {
    start("Styles");
    HSSFRow row = null;
    HSSFCell cell = null;
    Iterator cells = null;
    Iterator rows = sheet.rowIterator();
    while (rows.hasNext()) {
        row = (HSSFRow) rows.next();
        cells = row.cellIterator();
        while (cells.hasNext()) {
            cell = (HSSFCell) cells.next();
            attribute("startRow", Integer.toString(row.getRowNum()));
            attribute("endRow", Integer.toString(row.getRowNum()));
            attribute("startCol", Short.toString(cell.getCellNum()));
            attribute("endCol", Short.toString(cell.getCellNum()));
            start("StyleRegion");
            HSSFCellStyle style = cell.getCellStyle();
            attribute("HAlign", Integer.toString(style.getAlignment()));
            attribute("VAlign", Integer.toString(style.getVerticalAlignment()));
            attribute("WrapText", ((style.getWrapText()) ? "1" : "0"));
            attribute("Orient", Integer.toString(style.getRotation()));
            attribute("Indent", Integer.toString(style.getIndention()));
            attribute("Locked", ((style.getLocked()) ? "1" : "0"));
            attribute("Hidden", ((style.getHidden()) ? "1" : "0"));
            attribute("Fore",
                    workbook.getCustomPalette().getColor(style.getFillForegroundColor()).getHexString());
            attribute("Back",
                    workbook.getCustomPalette().getColor(style.getFillBackgroundColor()).getHexString());
            attribute("PatternColor", Integer.toString(style.getFillPattern())); // TODO
            attribute("Format", "General"); // TODO
            start("Style");
            HSSFFont font = workbook.getFontAt(style.getFontIndex());
            attribute("Unit", Short.toString(font.getFontHeightInPoints()));
            attribute("Bold", Short.toString(font.getBoldweight()));
            attribute("Italic", ((font.getItalic()) ? "1" : "0"));
            attribute("Unterline", Integer.toString(font.getUnderline()));
            attribute("StrikeThrough", ((font.getStrikeout()) ? "1" : "0"));
            start("Font");
            data(font.getFontName());
            end("Font");
            end("Style");
            end("StyleRegion");
        }
    }
    end("Styles");
}

---

From source file:org.apache.jackrabbit.extractor.MsExcelTextExtractor.java

License:Apache License

/**
 * {@inheritDoc}/*from  w  w  w .  j  a  v  a2s  . c  om*/
 */
public Reader extractText(InputStream stream, String type, String encoding) throws IOException {
    CharArrayWriter writer = new CharArrayWriter();
    try {
        POIFSFileSystem fs = new POIFSFileSystem(stream);
        HSSFWorkbook workbook = new HSSFWorkbook(fs);

        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            HSSFSheet sheet = workbook.getSheetAt(i);

            Iterator rows = sheet.rowIterator();
            while (rows.hasNext()) {
                HSSFRow row = (HSSFRow) rows.next();

                Iterator cells = row.cellIterator();
                while (cells.hasNext()) {
                    HSSFCell cell = (HSSFCell) cells.next();
                    switch (cell.getCellType()) {
                    case HSSFCell.CELL_TYPE_NUMERIC:
                        String num = Double.toString(cell.getNumericCellValue()).trim();
                        if (num.length() > 0) {
                            writer.write(num + " ");
                        }
                        break;
                    case HSSFCell.CELL_TYPE_STRING:
                        String text = cell.getStringCellValue().trim();
                        if (text.length() > 0) {
                            writer.write(text + " ");
                        }
                        break;
                    }
                }
            }
        }

        return new CharArrayReader(writer.toCharArray());
    } catch (RuntimeException e) {
        logger.warn("Failed to extract Excel text content", e);
        return new StringReader("");
    } finally {
        stream.close();
    }
}

---

From source file:org.apache.slide.extractor.MSExcelExtractor.java

License:Apache License

public Reader extract(InputStream content) throws ExtractorException {
    try {//from  ww  w .  j ava2s.c om
        CharArrayWriter writer = new CharArrayWriter();

        POIFSFileSystem fs = new POIFSFileSystem(content);
        HSSFWorkbook workbook = new HSSFWorkbook(fs);

        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            HSSFSheet sheet = workbook.getSheetAt(i);

            Iterator rows = sheet.rowIterator();
            while (rows.hasNext()) {
                HSSFRow row = (HSSFRow) rows.next();

                Iterator cells = row.cellIterator();
                while (cells.hasNext()) {
                    HSSFCell cell = (HSSFCell) cells.next();
                    switch (cell.getCellType()) {
                    case HSSFCell.CELL_TYPE_NUMERIC:
                        String num = Double.toString(cell.getNumericCellValue()).trim();
                        if (num.length() > 0)
                            writer.write(num + " ");
                        break;
                    case HSSFCell.CELL_TYPE_STRING:
                        String text = cell.getStringCellValue().trim();
                        if (text.length() > 0)
                            writer.write(text + " ");
                        break;
                    }
                }
            }
        }

        return new CharArrayReader(writer.toCharArray());
    } catch (Exception e) {
        throw new ExtractorException(e.getMessage());
    }
}

---

From source file:org.encuestame.business.search.IndexerFile.java

License:Apache License

/**
 * Extract spreadsheets content./*from  www  .  j a va  2 s  .c o  m*/
 * @param workBook
 * @return
 * @throws Exception
 */
public static String extractContentSpreadsheetsDocument(final HSSFWorkbook workBook) throws Exception {
    StringBuilder contents = new StringBuilder();
    for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
        HSSFSheet sheet = workBook.getSheetAt(i);
        Iterator<Row> rows = sheet.rowIterator();
        while (rows.hasNext()) {
            HSSFRow row = (HSSFRow) rows.next();
            // Display the row number
            log.debug(row.getRowNum());
            Iterator<Cell> cells = row.cellIterator();
            while (cells.hasNext()) {
                HSSFCell cell = (HSSFCell) cells.next();
                // Display the cell number of the current Row
                switch (cell.getCellType()) {

                case HSSFCell.CELL_TYPE_NUMERIC: {
                    log.debug(String.valueOf(cell.getNumericCellValue()));
                    contents.append(String.valueOf(cell.getNumericCellValue())).append(" ");
                    break;
                }

                case HSSFCell.CELL_TYPE_STRING: {
                    HSSFRichTextString richTextString = cell.getRichStringCellValue();
                    log.debug(richTextString.toString());
                    contents.append(richTextString.toString()).append(" ");
                    break;
                }

                case HSSFCell.CELL_TYPE_BOOLEAN: {
                    contents.append(String.valueOf(cell.getBooleanCellValue())).append(" ");
                    break;
                }
                }
            }
        }
    }
    return contents.toString();
}

---

From source file:org.encuestame.business.search.SearchUtils.java

License:Apache License

/**
* Create Spreadsheets Document.//from  ww  w.j  ava  2 s  .c  o m
* @param file Spreadsheet {@link File}.
* @param Long attachmentId.
* @return {@link Document}
* @throws FileNotFoundException
*/
public static Document createSpreadsheetsDocument(final File file) throws Exception {
    InputStream is = new FileInputStream(file);
    StringBuilder contents = new StringBuilder();
    POIFSFileSystem fileSystem = new POIFSFileSystem(is);
    HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
    for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
        HSSFSheet sheet = workBook.getSheetAt(i);
        Iterator<Row> rows = sheet.rowIterator();
        while (rows.hasNext()) {
            HSSFRow row = (HSSFRow) rows.next();
            // Display the row number
            log.debug(row.getRowNum());
            Iterator<Cell> cells = row.cellIterator();
            while (cells.hasNext()) {
                HSSFCell cell = (HSSFCell) cells.next();
                // Display the cell number of the current Row
                switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC: {
                    log.debug(String.valueOf(cell.getNumericCellValue()));
                    contents.append(String.valueOf(cell.getNumericCellValue())).append(" ");
                    break;
                }

                case HSSFCell.CELL_TYPE_STRING: {
                    HSSFRichTextString richTextString = cell.getRichStringCellValue();
                    log.debug(richTextString.toString());
                    contents.append(richTextString.toString()).append(" ");
                    break;
                }

                case HSSFCell.CELL_TYPE_BOOLEAN: {
                    contents.append(String.valueOf(cell.getBooleanCellValue())).append(" ");
                    break;
                }
                }
            }
        }
    }
    Document doc = SearchUtils.addFields(file, contents.toString());
    return doc;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• »