Example usage for org.apache.poi.ss.usermodel Sheet rowIterator

List of usage examples for org.apache.poi.ss.usermodel Sheet rowIterator

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. rowIterator

Introduction

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

Prototype

Iterator<Row> rowIterator();

Source Link

Document

Returns an iterator of the physical rows

Usage

From source file:com.streamsets.pipeline.lib.parser.excel.WorkbookParser.java

License:Apache License

public WorkbookParser(WorkbookParserSettings settings, Context context, Workbook workbook, String offsetId)
        throws DataParserException {
    this.settings = requireNonNull(settings);
    this.context = requireNonNull(context);
    this.workbook = requireNonNull(workbook);
    this.rowIterator = iterate(this.workbook);
    this.offset = requireNonNull(offsetId);
    this.evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    this.currentSheet = null; // default to blank.   Used to figure out when sheet changes and get new field names from header row

    if (!rowIterator.hasNext()) {
        throw new DataParserException(Errors.EXCEL_PARSER_04);
    }// w  w w.ja v  a  2 s. co  m

    headers = new HashMap<>();

    // If Headers are expected, go through and get them from each sheet
    if (settings.getHeader() == ExcelHeader.WITH_HEADER) {
        Sheet sheet;
        String sheetName;
        Row hdrRow;
        for (int s = 0; s < workbook.getNumberOfSheets(); s++) {
            sheet = workbook.getSheetAt(s);
            sheetName = sheet.getSheetName();
            hdrRow = sheet.rowIterator().next();
            List<Field> sheetHeaders = new ArrayList<>();
            // if the table happens to have blank columns in front of it, loop through and artificially add those as headers
            // This helps in the matching of headers to data later as the indexes will line up properly.
            for (int columnNum = 0; columnNum < hdrRow.getFirstCellNum(); columnNum++) {
                sheetHeaders.add(Field.create(""));
            }
            for (int columnNum = hdrRow.getFirstCellNum(); columnNum < hdrRow.getLastCellNum(); columnNum++) {
                Cell cell = hdrRow.getCell(columnNum);
                try {
                    sheetHeaders.add(Cells.parseCell(cell, this.evaluator));
                } catch (ExcelUnsupportedCellTypeException e) {
                    throw new DataParserException(Errors.EXCEL_PARSER_05, cell.getCellTypeEnum());
                }
            }
            headers.put(sheetName, sheetHeaders);
        }
    }

    Offsets.parse(offsetId).ifPresent(offset -> {
        String startSheetName = offset.getSheetName();
        int startRowNum = offset.getRowNum();

        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            int rowNum = row.getRowNum();
            String sheetName = row.getSheet().getSheetName();
            // if a sheet has blank rows at the top then the starting row number may be higher than a default offset of zero or one, thus the >= compare
            if (startSheetName.equals(sheetName) && rowNum >= startRowNum) {
                if (rowIterator.hasPrevious()) {
                    row = rowIterator.previous();
                    this.currentSheet = row.getRowNum() == row.getSheet().getFirstRowNum() ? null
                            : row.getSheet().getSheetName(); // used in comparison later to see if we've moved to new sheet
                } else {
                    this.currentSheet = null;
                }
                break;
            }
        }
    });
}

From source file:com.vaadin.addon.spreadsheet.CellValueManager.java

License:Open Source License

/**
 * Method for updating cells that are marked for update and formula cells.
 *
 * Iterates over the whole sheet (existing rows and columns) and updates
 * client side cache for all sent formula cells, and cells that have been
 * marked for updating./*w  w  w  .ja v a2 s.  c  o m*/
 *
 */
protected void updateMarkedCellValues() {
    final ArrayList<CellData> updatedCellData = new ArrayList<CellData>();
    Sheet sheet = spreadsheet.getActiveSheet();
    // it is unnecessary to worry about having custom components in the cell
    // because the client side handles it -> it will not replace a custom
    // component with a cell value

    // update all cached formula cell values on client side, because they
    // might have changed. also make sure all marked cells are updated
    Iterator<Row> rows = sheet.rowIterator();
    while (rows.hasNext()) {
        final Row r = rows.next();
        final Iterator<Cell> cells = r.cellIterator();
        while (cells.hasNext()) {
            final Cell cell = cells.next();
            int rowIndex = cell.getRowIndex();
            int columnIndex = cell.getColumnIndex();
            final String key = SpreadsheetUtil.toKey(columnIndex + 1, rowIndex + 1);
            CellData cd = createCellDataForCell(cell);
            // update formula cells
            if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                if (cd != null) {
                    if (sentFormulaCells.contains(key) || markedCells.contains(key)) {
                        sentFormulaCells.add(key);
                        updatedCellData.add(cd);
                    }
                } else if (sentFormulaCells.contains(key)) {
                    // in case the formula cell value has changed to null or
                    // empty; this case is probably quite rare, formula cell
                    // pointing to a cell that was removed or had its value
                    // cleared ???
                    sentFormulaCells.add(key);
                    cd = new CellData();
                    cd.col = columnIndex + 1;
                    cd.row = rowIndex + 1;
                    cd.cellStyle = "" + cell.getCellStyle().getIndex();
                    updatedCellData.add(cd);
                }
            } else if (markedCells.contains(key)) {
                sentCells.add(key);
                updatedCellData.add(cd);
            }
        }
    }
    if (!changedFormulaCells.isEmpty()) {
        fireFormulaValueChangeEvent(changedFormulaCells);
        changedFormulaCells = new HashSet<CellReference>();
    }
    // empty cells have cell data with just col and row
    updatedCellData.addAll(removedCells);
    if (!updatedCellData.isEmpty()) {
        spreadsheet.getRpcProxy().cellsUpdated(updatedCellData);
        spreadsheet.getRpcProxy().refreshCellStyles();
    }
    markedCells.clear();
    removedCells.clear();
}

From source file:com.vermeg.convertisseur.service.ConvServiceImpl.java

/**
 *
 * @param file/*from w ww .j av  a  2 s.  c o m*/
 * @return
 * @throws FileNotFoundException
 * @throws InvalidFormatException
 * @throws IOException
 */
/*this method convert a multipart file to json object */
@Override
public JSONObject convert(MultipartFile file, String name)
        throws FileNotFoundException, InvalidFormatException, IOException {

    // File file = new File("C:\\Users\\Ramzi\\Documents\\PFE\\developpement\\avancement.xlsx");

    File filez = File.createTempFile("fichier", "xslx");
    file.transferTo(filez);
    FileInputStream inp = new FileInputStream(filez);
    Workbook workbook = WorkbookFactory.create(inp);
    //Sheet sheet = workbook.getSheetAt( 0 );
    Sheet sheet = workbook.getSheet(name);
    // Start constructing JSON.
    JSONObject json = new JSONObject();

    // Iterate through the rows.
    JSONArray rows = new JSONArray();
    for (Iterator<Row> rowsIT = sheet.rowIterator(); rowsIT.hasNext();) {
        Row row = rowsIT.next();
        JSONObject jRow = new JSONObject();

        // Iterate through the cells.
        JSONArray cells = new JSONArray();
        for (Iterator<Cell> cellsIT = row.cellIterator(); cellsIT.hasNext();) {
            Cell cell = cellsIT.next();
            // System.out.println(cell.getCellType());
            //           cells.put(cell.getDateCellValue());
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                cells.put(cell.getRichStringCellValue().getString());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    cells.put(cell.getDateCellValue());
                } else {
                    cells.put(cell.getNumericCellValue());
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                cells.put(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                cells.put(cell.getCellFormula());
                break;
            default:
                System.out.println();
            }
        }
        jRow.put("cell", cells);
        rows.put(cells);
        //rows.put( jRow );
    }

    // Create the JSON.
    json.put("rows", rows);
    System.out.println(json.toString());
    return json;
}

From source file:com.vermeg.convertisseur.service.ConvServiceImpl.java

@Override
public JSONObject convert(String file, String name)
        throws FileNotFoundException, InvalidFormatException, IOException {

    // File file = new File("C:\\Users\\Ramzi\\Documents\\PFE\\developpement\\avancement.xlsx");

    File filez = File.createTempFile("fichier", "xslx");
    byte[] data = Base64.decodeBase64(file);
    FileOutputStream fos = new FileOutputStream(filez);
    fos.write(data);/*from ww  w  .  java  2  s. c o  m*/
    fos.close();
    //file.transferTo(filez);
    FileInputStream inp = new FileInputStream(filez);
    Workbook workbook = WorkbookFactory.create(inp);
    //Sheet sheet = workbook.getSheetAt( 0 );
    Sheet sheet = workbook.getSheet(name);
    // Start constructing JSON.
    JSONObject json = new JSONObject();

    // Iterate through the rows.
    JSONArray rows = new JSONArray();
    for (Iterator<Row> rowsIT = sheet.rowIterator(); rowsIT.hasNext();) {
        Row row = rowsIT.next();
        JSONObject jRow = new JSONObject();

        // Iterate through the cells.
        JSONArray cells = new JSONArray();
        for (Iterator<Cell> cellsIT = row.cellIterator(); cellsIT.hasNext();) {
            Cell cell = cellsIT.next();

            if (cell.getCellType() == CELL_TYPE_NUMERIC) {
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    cells.put(cell.getDateCellValue());
                } else
                    cells.put(cell.getNumericCellValue());
            } else
                cells.put(cell.getStringCellValue());
        }
        jRow.put("cell", cells);
        rows.put(cells);
        //rows.put( jRow );
    }

    // Create the JSON.
    json.put("rows", rows);
    System.out.println(json.toString());
    return json;
}

From source file:com.xandrev.altafitcalendargenerator.XLSExtractor.java

public HashMap<Integer, ArrayList<TimeTrack>> importExcelSheet(String fileName) {
    HashMap<Integer, String> tmpHours = new HashMap<Integer, String>();
    HashMap<Integer, ArrayList<TimeTrack>> out = new HashMap<Integer, ArrayList<TimeTrack>>();
    init(out);/*from www  .j  a va 2 s.c o  m*/
    try {
        Workbook workBook = WorkbookFactory.create(new FileInputStream(fileName));
        Sheet sheet = workBook.getSheetAt(0);
        Iterator rowIter = sheet.rowIterator();
        int rowIdx = 0;
        boolean started = false;
        boolean finished = false;
        while (rowIter.hasNext() && !finished) {
            XSSFRow row = (XSSFRow) rowIter.next();
            if (row != null && !started) {
                XSSFCell cell = row.getCell(0);
                if (cell != null) {
                    String value = cell.getStringCellValue();
                    if (value == null || value.isEmpty() || !"HORA".equals(value)) {
                        rowIdx++;
                        started = true;
                        continue;
                    }
                }
                row = (XSSFRow) rowIter.next();
            }

            Iterator<Cell> cellIter = row.cellIterator();
            int cellIndex = 0;
            while (cellIter.hasNext()) {
                XSSFCell cell = (XSSFCell) cellIter.next();
                if (cell != null) {
                    String value = cell.getStringCellValue();
                    installHashMap(tmpHours, out, cellIndex, rowIdx, value);
                }
                cellIndex++;
            }
            rowIdx++;
        }

    } catch (Exception e) {
        System.out.println(e.getMessage());
    }
    return out;
}

From source file:Compras.ComparaCotizacion.java

void importaDatos(Workbook wb, int col) {
     Sheet hoja = wb.getSheetAt(0);
     Iterator rowIterator = hoja.rowIterator();
     List renglones = new ArrayList();
     while (rowIterator.hasNext()) {
         HSSFRow hssfRow = (HSSFRow) rowIterator.next();
         List celdas = new ArrayList();
         Iterator iterator = hssfRow.cellIterator();
         while (iterator.hasNext()) {
             HSSFCell hssfCell = (HSSFCell) iterator.next();
             celdas.add(hssfCell);/* w w  w.  j av a  2 s .c o m*/
         }
         renglones.add(celdas);
     }
     for (int r = 8; r < renglones.size(); r++) {
         List aux = (List) renglones.get(r);
         HSSFCell auxCell = (HSSFCell) aux.get(0);
         HSSFCell auxCell1 = (HSSFCell) aux.get(1);
         int res = busca(auxCell.getNumericCellValue(), auxCell1.getNumericCellValue());
         if (res != -1) {
             for (int c = 0; c < aux.size(); c++) {
                 HSSFCell valor = (HSSFCell) aux.get(c);

                 switch (valor.getColumnIndex()) {
                 case 4:
                     switch (valor.getCellType()) {
                     case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
                         t_datos.setValueAt((int) valor.getNumericCellValue(), res, col);
                         break;
                     case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
                         t_datos.setValueAt(valor.getStringCellValue(), res, col);
                         break;
                     case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK:
                         t_datos.setValueAt("", res, col);
                         break;
                     default:
                         t_datos.setValueAt("", res, col);
                         break;
                     }
                     break;

                 case 7:
                     switch (valor.getCellType()) {
                     case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
                         t_datos.setValueAt(valor.getNumericCellValue(), res, col + 1);
                         break;
                     case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
                         t_datos.setValueAt(valor.getStringCellValue(), res, col + 1);
                         break;
                     default:
                         t_datos.setValueAt("", res, col + 1);
                         break;
                     }
                     if (t_datos.getValueAt(res, col + 1).toString().compareTo("") != 0) {
                         model.setCeldaEditable(res, col + 3, true);
                         model.setCeldaEditable(res, col + 4, true);
                     }
                     break;

                 case 10:
                     switch (valor.getCellType()) {
                     case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
                         t_datos.setValueAt((int) valor.getNumericCellValue(), res, col + 2);
                         break;
                     case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
                         t_datos.setValueAt(valor.getStringCellValue(), res, col + 2);
                         break;
                     default:
                         t_datos.setValueAt("", res, col + 2);
                         break;
                     }
                     break;
                 }
                 t_datos.setValueAt(false, res, col + 3);
                 t_datos.setValueAt(false, res, col + 4);
             }
         }
     }
 }

From source file:Contabilidad.FacturarOrden.java

private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton3ActionPerformed
    // TODO add your handling code here:
    aviso.setFileFilter(new ExtensionFileFilter("Excel document (*.xls)", new String[] { "xls" }));
    int resp = aviso.showOpenDialog(null);
    if (resp == aviso.APPROVE_OPTION) {
        File archivoXLS = aviso.getSelectedFile();
        if (archivoXLS.exists() == true) {
            Session session = HibernateUtil.getSessionFactory().openSession();
            try {
                FileInputStream archivo = new FileInputStream(archivoXLS);
                POIFSFileSystem fsFileSystem = new POIFSFileSystem(archivo);
                Workbook libro1 = new HSSFWorkbook(fsFileSystem);
                Sheet datos = libro1.getSheetAt(0);
                Iterator rowIterator;
                int r = 0;

                //**********cargamos datos************************
                //DefaultTableModel temp = (DefaultTableModel) t_datos.getModel();
                t_datos.setModel(ModeloTablaReporte(0, columnas));
                rowIterator = datos.rowIterator();
                r = 0;// w w  w. ja  va  2s .  c om
                while (rowIterator.hasNext()) {
                    HSSFRow renglon = (HSSFRow) rowIterator.next();
                    if (r > 0) {
                        double cant = renglon.getCell(0).getNumericCellValue();
                        double cu = renglon.getCell(3).getNumericCellValue();
                        double descuento = renglon.getCell(4).getNumericCellValue();
                        double suma = cant * cu;
                        double desc = descuento / 100;
                        double total = suma - (suma * desc);
                        Object nuevo[] = { cant, renglon.getCell(1).getStringCellValue(),
                                renglon.getCell(2).getStringCellValue(), "", cu, descuento, total };
                        model.addRow(nuevo);
                    } else
                        r = 1;
                }
                formatoTabla();
                this.sumaTotales();
                t_datos.setRowSelectionInterval(t_datos.getRowCount() - 1, t_datos.getRowCount() - 1);
                t_datos.setColumnSelectionInterval(0, 0);
                t_datos.requestFocus();
            } catch (Exception e) {
                e.printStackTrace();
                JOptionPane.showMessageDialog(this, "No se pudo abrir el archivo");
            }
            if (session != null)
                if (session.isOpen() == true)
                    session.close();
        }
    }
}

From source file:Contabilidad.FacturarOrdenLogis.java

private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton3ActionPerformed
    // TODO add your handling code here:
    aviso.setFileFilter(new ExtensionFileFilter("Excel document (*.xls)", new String[] { "xls" }));
    int resp = aviso.showOpenDialog(null);
    if (resp == aviso.APPROVE_OPTION) {
        File archivoXLS = aviso.getSelectedFile();
        if (archivoXLS.exists() == true) {
            Session session = HibernateUtil.getSessionFactory().openSession();
            try {
                FileInputStream archivo = new FileInputStream(archivoXLS);
                POIFSFileSystem fsFileSystem = new POIFSFileSystem(archivo);
                Workbook libro1 = new HSSFWorkbook(fsFileSystem);
                Sheet datos = libro1.getSheetAt(0);
                Iterator rowIterator;
                int r = 0;

                //**********cargamos datos************************
                //DefaultTableModel temp = (DefaultTableModel) t_datos.getModel();
                t_datos.setModel(ModeloTablaReporte(0, columnas));
                rowIterator = datos.rowIterator();
                r = 0;/*from ww  w  .j a  v  a2s.  c  o m*/
                while (rowIterator.hasNext()) {
                    HSSFRow renglon = (HSSFRow) rowIterator.next();
                    if (r > 0) {
                        System.out.println(renglon.getCell(0).getNumericCellValue());
                        double cant = renglon.getCell(0).getNumericCellValue();
                        double cu = renglon.getCell(3).getNumericCellValue();
                        double descuento = renglon.getCell(4).getNumericCellValue();
                        double suma = cant * cu;
                        double desc = descuento / 100;
                        double total = suma - (suma * desc);
                        Object nuevo[] = { cant, renglon.getCell(1).getStringCellValue(),
                                renglon.getCell(2).getStringCellValue(), "", cu, descuento, total };
                        model.addRow(nuevo);
                    } else
                        r = 1;
                }
                formatoTabla();
                this.sumaTotales();
                t_datos.setRowSelectionInterval(t_datos.getRowCount() - 1, t_datos.getRowCount() - 1);
                t_datos.setColumnSelectionInterval(0, 0);
                t_datos.requestFocus();
            } catch (Exception e) {
                e.printStackTrace();
                JOptionPane.showMessageDialog(this, "No se pudo abrir el archivo");
            }
            if (session != null)
                if (session.isOpen() == true)
                    session.close();
        }
    }
}

From source file:Contabilidad.NuevaFactura.java

private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton3ActionPerformed
    // TODO add your handling code here:
    aviso.setFileFilter(new ExtensionFileFilter("Excel document (*.xls)", new String[] { "xls" }));
    int resp = aviso.showOpenDialog(null);
    if (resp == aviso.APPROVE_OPTION) {
        File archivoXLS = aviso.getSelectedFile();
        if (archivoXLS.exists() == true) {
            Session session = HibernateUtil.getSessionFactory().openSession();
            try {
                FileInputStream archivo = new FileInputStream(archivoXLS);
                POIFSFileSystem fsFileSystem = new POIFSFileSystem(archivo);
                Workbook libro1 = new HSSFWorkbook(fsFileSystem);
                Sheet datos = libro1.getSheetAt(0);
                Iterator rowIterator;
                int r = 0;

                //**********cargamos datos************************
                //DefaultTableModel temp = (DefaultTableModel) t_datos.getModel();
                t_datos.setModel(ModeloTablaReporte(0, columnas));
                rowIterator = datos.rowIterator();
                r = 0;//ww  w  .j av a2  s.  c  o m
                while (rowIterator.hasNext()) {
                    HSSFRow renglon = (HSSFRow) rowIterator.next();
                    if (r > 0) {
                        System.out.println(renglon.getCell(0).getNumericCellValue());
                        double cant = renglon.getCell(0).getNumericCellValue();
                        double cu = renglon.getCell(3).getNumericCellValue();
                        double descuento = renglon.getCell(4).getNumericCellValue();
                        double suma = cant * cu;
                        double desc = descuento / 100;
                        double total = suma - (suma * desc);
                        Object nuevo[] = { cant, renglon.getCell(1).getStringCellValue(),
                                renglon.getCell(2).getStringCellValue(), cu, descuento, total };
                        model.addRow(nuevo);
                    } else
                        r = 1;
                }
                formatoTabla();
                this.sumaTotales();
                t_datos.setRowSelectionInterval(t_datos.getRowCount() - 1, t_datos.getRowCount() - 1);
                t_datos.setColumnSelectionInterval(0, 0);
                t_datos.requestFocus();
            } catch (Exception e) {
                e.printStackTrace();
                JOptionPane.showMessageDialog(this, "No se pudo abrir el archivo");
            }
            if (session != null)
                if (session.isOpen() == true)
                    session.close();
        }
    }
}

From source file:coolmap.application.io.external.ImportCOntologyFromXLS.java

private void importSIF(Sheet sheet, COntology ontology) throws Exception {

    System.out.println("rowStart + columnStart:" + rowStart + " " + columnStart);

    int rowCounter = 0;
    Iterator<Row> rowIterator = sheet.rowIterator();
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();//from  ww w  .  ja va 2  s  .  c  om
        if (rowCounter < rowStart) {
            rowCounter++;
            continue;
        }

        if (columnStart + 2 > row.getLastCellNum()) {
            continue;
        }

        Cell parentCell = row.getCell(columnStart);
        Cell childCell = row.getCell(columnStart + 1);

        System.out.println(parentCell + " " + childCell);

        if (parentCell == null || childCell == null || parentCell.toString().trim().length() == 0
                || childCell.toString().trim().length() == 0) {
            continue;
        }

        ontology.addRelationshipNoUpdateDepth(parentCell.toString().trim(), childCell.toString().trim());
        rowCounter++;
    }
}