Example usage for org.apache.poi.ss.usermodel Row 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:mindbodymerge.Parser.java

/**
 * Parses the membership.XLSX file to make a dictionary of student names and student numbers
 * Format is membershipDictionary.get(i) is the student number and membershipDictionary.get(i+1) is the student name for that student number
 * This is only because in the excel file the student number comes first, if this changes you will have to change the logic in the parseMemberList method
 * Almost the exact same logic as parseSales method, so look at that if this is confusing
 *
 * @param membershipDictionary: dictionary of student numbers/names where the student number membershipDictionary.get(i) correlates to the student name membershipDictionary.get(i+1)
*/// w w  w. ja  v  a 2s  .co m
private void parseMembership(ArrayList<String> membershipDictionary) {
    //IOException
    try {
        int iterationCount;
        Double studentNumber;
        String studentString;

        FileInputStream fis = new FileInputStream(membership);

        XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);
        XSSFSheet mySheet = myWorkBook.getSheetAt(0);

        Iterator<Row> rowIterator = mySheet.iterator();
        rowIterator.next();

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

            iterationCount = 0;

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

                //This is the only part in which this is different from parseSales method
                //In the current format of the excel sheet, there are some rows in the middle of the sheet which contains column headers, not the data we want
                //In order to get rid of this data, we check if the data in column A is a number,
                //If it isn't, it's column header data and we skip over it by setting the iterationCount to 2 (which is past any data we need)
                if (iterationCount == 0) {
                    //Check if the cellType is numeric by comparing it to 0 because Cell.CELL_TYPE_NUMERIC == 0)
                    if (cell.getCellType() == 0) {
                        studentNumber = cell.getNumericCellValue();
                        studentString = String.format("%.0f", studentNumber);
                        membershipDictionary.add(studentString);
                    } else
                        iterationCount = 2;
                }
                //Get the student name that correlates to the student number we just parsed
                else if (iterationCount == 1) {
                    //Have to replace some super weird unicode garbage from the data
                    membershipDictionary
                            .add(cell.getStringCellValue().replaceAll("\u00a0", "").replaceAll(" ", ""));
                }

                iterationCount++;
            }
        }
        //Testing block, should have a student number followed by a student name for each person in the excel sheet
        //            System.out.println(membershipDictionary.size());
        //            System.out.println(membershipDictionary);
    } catch (IOException e) {

    }
}

---

From source file:Model.AccountImport.java

public String excelReader(String path_file, List excelColumn) {
    String extension = FilenameUtils.getExtension(path_file);

    if (extension.equalsIgnoreCase(".xlsx")) {
        try {//  w w w  .  ja  va2 s  . c  o m
            FileInputStream file = new FileInputStream(new File(path_file));
            XSSFWorkbook workbook = new XSSFWorkbook(file);
            XSSFSheet sheet = workbook.getSheetAt(0);
            Iterator<Row> rowIterator = sheet.iterator();
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                Iterator<Cell> cellIterator = row.cellIterator();

                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.print(cell.getNumericCellValue() + " | ");
                        break;
                    case Cell.CELL_TYPE_STRING:
                        System.out.print(cell.getStringCellValue() + " | ");
                        break;
                    }
                }
                System.out.println("");
            }
            file.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    } else if (extension.equalsIgnoreCase(".xls")) {
        try {
            FileInputStream file = new FileInputStream(new File(path_file));
            HSSFWorkbook workbook = new HSSFWorkbook(file);

            HSSFSheet sheet = workbook.getSheetAt(0);

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

                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.print(cell.getNumericCellValue() + " | ");
                        break;
                    case Cell.CELL_TYPE_STRING:
                        System.out.print(cell.getStringCellValue() + " | ");
                        break;
                    }
                }
                System.out.println("");
            }
            file.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    return null;
}

---

From source file:model.converter.ConvertToInstances.java

public void convertXlsxToCSV(File inputFile, File outputFile) {
    // For storing data into CSV files
    StringBuffer data = new StringBuffer();

    try {// w  w  w  .  j a  v a2 s .co  m
        FileOutputStream fos = new FileOutputStream(outputFile);
        // Get the workbook object for XLSX file
        XSSFWorkbook wBook = new XSSFWorkbook(new FileInputStream(inputFile));
        // Get first sheet from the workbook
        XSSFSheet sheet = wBook.getSheetAt(0);
        Row row;
        Cell cell;
        // Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();

        while (rowIterator.hasNext()) {
            row = rowIterator.next();

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

            while (cellIterator.hasNext()) {

                cell = cellIterator.next();

                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_BOOLEAN:
                    data.append(cell.getBooleanCellValue() + ",");

                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    data.append(cell.getNumericCellValue() + ",");

                    break;
                case Cell.CELL_TYPE_STRING:
                    data.append(cell.getStringCellValue() + ",");
                    break;

                case Cell.CELL_TYPE_BLANK:
                    data.append("" + ",");
                    break;
                default:
                    data.append(cell + ",");

                }

            }
            data.append("\r\n");
        }

        String csvData = data.toString();
        csvData = csvData.replaceAll(",\r\n", "\r\n");
        fos.write(csvData.getBytes());
        fos.close();

    } catch (Exception ioe) {
        ioe.printStackTrace();
    }
}

---

From source file:Model.ExcelModel.java

public String ImportFile(File fileImported, JTable dataTable) {
    String importResult = "System couldn't import the file";

    DefaultTableModel loadModel = new DefaultTableModel();
    dataTable.setModel(loadModel);//from w w w  .j  a v  a 2  s.  c o m
    dataTable.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);

    try {
        innerWorkbook = WorkbookFactory.create(new FileInputStream(fileImported));
        Sheet sheetTemporal = innerWorkbook.getSheetAt(0);
        Iterator rowIterator = sheetTemporal.rowIterator();

        int rowIndex = -1;
        while (rowIterator.hasNext()) {
            rowIndex++;

            Row temporalRow = (Row) rowIterator.next();
            Iterator columnIterator = temporalRow.cellIterator();

            Object[] columnList = new Object[12];

            int columnIndex = -1;
            while (columnIterator.hasNext()) {
                columnIndex++;
                Cell temporalCell = (Cell) columnIterator.next();
                if (rowIndex == 0)
                    loadModel.addColumn(temporalCell.getStringCellValue());
                else {
                    if (temporalCell != null) {
                        switch (temporalCell.getCellType()) {
                        case Cell.CELL_TYPE_NUMERIC:
                            columnList[columnIndex] = (int) Math.round(temporalCell.getNumericCellValue());
                            break;
                        case Cell.CELL_TYPE_STRING:
                            columnList[columnIndex] = temporalCell.getStringCellValue();
                            break;
                        default:
                            columnList[columnIndex] = temporalCell.getDateCellValue();
                            break;
                        }//End Switch/Case get.CellType
                    } //End temporalCell!=null condition
                } //End Else rowIndex==0
            } //End columnIterator
            if (rowIndex != 0)
                loadModel.addRow(columnList);
        } //End rowIterator

        importResult = "Successful Import";

    } catch (Exception e) {
    }
    return importResult;

}

---

From source file:Modelo.ModeloExcel.java

public String Importar(File archivo, JTable tablaD) {
    String respuesta = "No se pudo realizar la importacin.";
    DefaultTableModel modeloT = new DefaultTableModel();
    tablaD.setModel(modeloT);// w w w .j  a  va 2 s. c o m
    try {
        wb = WorkbookFactory.create(new FileInputStream(archivo));
        Sheet hoja = wb.getSheetAt(0);
        Iterator filaIterator = hoja.rowIterator();
        int indiceFila = -1;
        while (filaIterator.hasNext()) {
            indiceFila++;
            Row fila = (Row) filaIterator.next();
            Iterator columnaIterator = fila.cellIterator();
            Object[] listaColumna = new Object[5];
            int indiceColumna = -1;
            while (columnaIterator.hasNext()) {
                indiceColumna++;
                Cell celda = (Cell) columnaIterator.next();
                if (indiceFila == 0) {
                    modeloT.addColumn(celda.getStringCellValue());
                } else {
                    if (celda != null) {
                        switch (celda.getCellType()) {
                        case Cell.CELL_TYPE_NUMERIC:
                            listaColumna[indiceColumna] = (int) Math.round(celda.getNumericCellValue());
                            break;
                        case Cell.CELL_TYPE_STRING:
                            listaColumna[indiceColumna] = celda.getStringCellValue();
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            listaColumna[indiceColumna] = celda.getBooleanCellValue();
                            break;
                        default:
                            listaColumna[indiceColumna] = celda.getDateCellValue();
                            break;
                        }
                    }
                }
            }
            if (indiceFila != 0)
                modeloT.addRow(listaColumna);
        }
        respuesta = "Importacin exitosa";
    } catch (Exception e) {
    }
    return respuesta;
}

---

From source file:mpqq.MPQQ.java

private static XSSFWorkbook procTab1(XSSFWorkbook referenceWB, XSSFWorkbook mpqqWB, int referenceFirstRow) {

    XSSFSheet trackerTab = referenceWB.getSheetAt(USE_FOR_TAB1);
    XSSFSheet tab1 = mpqqWB.getSheetAt(1);
    //Iterator<Row> rowIterator = trackerTab.iterator();
    DataFormatter df = new DataFormatter();

    //MPQQ first row    
    int rowIdx = 11;

    for (int refCurRow = referenceFirstRow; refCurRow <= trackerTab.getLastRowNum(); refCurRow++) {
        Row row = trackerTab.getRow(refCurRow);

        //Check if row is visible
        if (!row.getZeroHeight() || (row.isFormatted() && row.getRowStyle().getHidden())) {

            int colIdx = 1;
            //Iterate trough the Columns
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                switch (cell.getColumnIndex()) {
                case 3:
                    Cell currentCell = checkRowCellExists(tab1, rowIdx, colIdx);
                    currentCell.setCellValue(df.formatCellValue(row.getCell(T2PEPSICO_STOCK_CODE)));

                    //Go to next Column
                    colIdx++;//  ww w  . j  a  v  a 2s  .c o m
                    break;
                case 4:

                    break;
                default:
                }
            }
            //Jump Next Row
            rowIdx++;
        }
    }
    return mpqqWB;
}

---

From source file:mpqq.MPQQ.java

private static void printsheet(XSSFSheet sheet) {
    //Iterate through each rows from first sheet
    Iterator<Row> rowIterator = sheet.iterator();
    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:
                System.out.print(cell.getBooleanCellValue() + "\t\t");
                break;
            case Cell.CELL_TYPE_NUMERIC:
                System.out.print(cell.getNumericCellValue() + "\t\t");
                break;
            case Cell.CELL_TYPE_STRING:
                System.out.print(cell.getStringCellValue() + "\t\t");
                break;
            }/*from www . jav a 2s.c om*/
        }
        System.out.println("");
    }
}

---

From source file:mvp.presenter.OpenDataDialogPresenter.java

private void parseFile(String excelFilePath, boolean colHeader) throws IOException {
    if (view.shapeFilePathField.getText().isEmpty()
            || view.shapeFilePathField.getText().equalsIgnoreCase("Enter the shapefile path here")) {
        data.setShpPath("empty");
    }/*from ww w  . j av a2  s.  c o  m*/
    try (FileInputStream inputStream = new FileInputStream(new File(excelFilePath))) {
        GridBase grid = new GridBase(1000, 100);
        ObservableList<String> listHeader = FXCollections.observableArrayList();

        try (Workbook workbook = new XSSFWorkbook(inputStream)) {
            Sheet firstSheet = workbook.getSheetAt(0);
            data.setRowNumber(firstSheet.getLastRowNum());
            data.setColumnNumber(firstSheet.getRow(0).getLastCellNum());
            Iterator<Row> iterator = firstSheet.iterator();

            ObservableList<ObservableList<SpreadsheetCell>> rows = FXCollections.observableArrayList();
            for (int row = 0; row < grid.getRowCount(); row++) {
                final ObservableList<SpreadsheetCell> list = FXCollections.observableArrayList();
                for (int column = 0; column < grid.getColumnCount(); column++) {
                    list.add(SpreadsheetCellType.STRING.createCell(row, column, 1, 1, ""));
                }
                rows.add(list);
            }

            if (colHeader) {
                if (iterator.hasNext()) {
                    Row headerRow = iterator.next();
                    Iterator<Cell> cellIterator = headerRow.cellIterator();
                    while (cellIterator.hasNext()) {
                        Cell cell = cellIterator.next();
                        listHeader.add(cell.getStringCellValue());
                    }
                }

                ObservableList<String> variableType = FXCollections.observableArrayList();
                for (int i = 0; i < listHeader.size(); i++) {
                    variableType.add(null);
                }

                while (iterator.hasNext()) {
                    Row nextRow = iterator.next();
                    Iterator<Cell> cellIterator = nextRow.cellIterator();
                    while (cellIterator.hasNext()) {
                        Cell cell = cellIterator.next();
                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            variableType.set(cell.getColumnIndex(), "String");
                            rows.get(cell.getRowIndex() - 1).set(cell.getColumnIndex(),
                                    SpreadsheetCellType.STRING.createCell(cell.getRowIndex() - 1,
                                            cell.getColumnIndex(), 1, 1, cell.getStringCellValue()));
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            variableType.set(cell.getColumnIndex(), "Boolean");
                            rows.get(cell.getRowIndex() - 1).set(cell.getColumnIndex(),
                                    SpreadsheetCellType.STRING.createCell(cell.getRowIndex() - 1,
                                            cell.getColumnIndex(), 1, 1,
                                            String.valueOf(cell.getBooleanCellValue())));
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            variableType.set(cell.getColumnIndex(), "Double");
                            rows.get(cell.getRowIndex() - 1).set(cell.getColumnIndex(),
                                    SpreadsheetCellType.DOUBLE.createCell(cell.getRowIndex() - 1,
                                            cell.getColumnIndex(), 1, 1, cell.getNumericCellValue()));
                            break;
                        }
                    }
                }

                ObservableList<Variable> variables = FXCollections.observableArrayList();
                for (int i = 0; i < listHeader.size() && i < variableType.size(); i++) {
                    Variable variable = new Variable(listHeader.get(i), variableType.get(i));
                    variables.add(variable);
                }
                data.setVariables(variables);
            } else if (!colHeader) {
                while (iterator.hasNext()) {
                    Row nextRow = iterator.next();
                    Iterator<Cell> cellIterator = nextRow.cellIterator();
                    while (cellIterator.hasNext()) {
                        Cell cell = cellIterator.next();
                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            rows.get(cell.getRowIndex()).set(cell.getColumnIndex(),
                                    SpreadsheetCellType.STRING.createCell(cell.getRowIndex(),
                                            cell.getColumnIndex(), 1, 1, cell.getStringCellValue()));
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            rows.get(cell.getRowIndex()).set(cell.getColumnIndex(),
                                    SpreadsheetCellType.STRING.createCell(cell.getRowIndex(),
                                            cell.getColumnIndex(), 1, 1,
                                            String.valueOf(cell.getBooleanCellValue())));
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            rows.get(cell.getRowIndex()).set(cell.getColumnIndex(),
                                    SpreadsheetCellType.DOUBLE.createCell(cell.getRowIndex(),
                                            cell.getColumnIndex(), 1, 1, cell.getNumericCellValue()));
                            break;
                        }
                    }
                }
            }
            grid.setRows(rows);
            mwview.drawTable(listHeader, grid);
        }
    }
    view.closeStage();
}

---

From source file:negocio.parser.ExcelReader.java

@Override
public IExcelContent leerArchivo(String ruta) throws Exception {
    java.util.Date date = new java.util.Date();
    Date entrada = new Date(date.getTime());
    IExcelContent ec = ExcelContent.getInstantiateExcelContent();
    try {/*w  w  w. j  av a 2s  .  com*/
        LogDAO dao = new LogDAO();
        LogDTO dto = new LogDTO("Leer archivo", "Comienzo de lectura de archivo", entrada.toString(),
                entrada.toString());
        dao.registrarLog(dto);
        File archivo = new File(ruta);
        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(archivo)); //crear un libro excel
        XSSFSheet sheet = workbook.getSheetAt(0); //acceder a la primera hoja
        Iterator<Row> rowIterator = sheet.iterator();
        Row row;
        boolean sw = true;
        ArrayList<List<String>> datos = new ArrayList<>();
        while (rowIterator.hasNext()) {

            row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();
            Cell celda;
            List<String> fila = new ArrayList<>();
            while (cellIterator.hasNext()) {
                celda = cellIterator.next();
                String dato = "";
                switch (celda.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    if (HSSFDateUtil.isCellDateFormatted(celda)) {
                        dato = celda.getDateCellValue().toString();
                    } else {
                        dato = celda.getNumericCellValue() + "";
                    }
                    break;
                case Cell.CELL_TYPE_STRING:
                    dato = celda.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    dato = celda.getBooleanCellValue() + "";
                    break;
                }
                fila.add(dato);
            }
            if (sw) {
                sw = false;
                ec.setTitulos(fila);
            } else {
                datos.add(fila);
            }
        }
        ec.setDatos(datos);
        workbook.close();
        return ec;
    } catch (IOException ex) {
        Logger.getLogger(ExcelReader.class.getName()).log(Level.SEVERE, null, ex);
    }
    return null;

}

---

From source file:net.cpollet.jixture.fixtures.transformers.ExcelFileFixtureTransformer.java

License:Apache License

@SuppressWarnings("MethodWithTooManyParameters")
private Object buildMappingForRow(Helpers helpers, Parameters parameters, Row rowColumnValues,
        Row rowColumnNames) {//from   w  w  w .ja v a  2  s  .c  om
    MappingBuilder mappingBuilder = mappingBuilderFactory.create(parameters.tableName);

    Iterator<Cell> valuesIterator = rowColumnValues.cellIterator();
    Iterator<Cell> columnsIterator = rowColumnNames.cellIterator();

    while (valuesIterator.hasNext() && columnsIterator.hasNext()) {
        Cell columnNameCell = columnsIterator.next();
        Cell columnValueCell = valuesIterator.next();

        helpers.evaluator.evaluateFormulaCell(columnValueCell);

        String columnName = columnNameCell.getStringCellValue();
        String columnValue = helpers.dataFormatter.formatCellValue(columnValueCell, helpers.evaluator);

        if ((escapeChar + modeColumnName).equals(columnName)) {
            if (!parameters.mode.getMarkers().contains(columnValue)) {
                return null;
            }
        } else {
            mappingBuilder.withValue(columnName, columnValue);
        }
    }

    return mappingBuilder.build();
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• »