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:FilesManager.ExcelParser.java

public static List<HardCopy> readExcelData(String fileName) {
    List<HardCopy> fileList = new ArrayList<>();

    try {//from  w ww .  ja  va  2 s .co m
        //Create the input stream from the xlsx/xls file
        FileInputStream fis = new FileInputStream(fileName);

        //Create Workbook instance for xlsx/xls file input stream
        Workbook workbook = null;
        if (fileName.toLowerCase().endsWith("xlsx")) {
            workbook = new XSSFWorkbook(fis);
        } else if (fileName.toLowerCase().endsWith("xls")) {
            workbook = new HSSFWorkbook(fis);
        }

        //Get the number of sheets in the xlsx file
        int numberOfSheets = workbook.getNumberOfSheets();

        //loop through each of the sheets
        for (int i = 0; i < numberOfSheets; i++) {

            //Get the nth sheet from the workbook
            Sheet sheet = workbook.getSheetAt(i);

            //every sheet has rows, iterate over them
            Iterator<Row> rowIterator = sheet.iterator();
            int index = 0;
            while (rowIterator.hasNext()) {
                index++;
                if (index == 1) {

                    rowIterator.next();
                    continue;
                }

                //Get the row object
                Row row = rowIterator.next();

                //Every row has columns, get the column iterator and iterate over them
                Iterator<Cell> cellIterator = row.cellIterator();

                HardCopy f = null;
                try {

                    List<String> listStrings = new LinkedList<>();

                    for (int j = 0; j < 6; j++) {
                        Cell c = row.getCell(j);
                        if (c != null)
                            listStrings.add(c.toString());
                        else
                            listStrings.add("");
                    }

                    int s = listStrings.size();

                    f = new HardCopy(listStrings.get(0), listStrings.get(1), listStrings.get(2),
                            listStrings.get(3), (s > 4) ? listStrings.get(4) : "",
                            (s > 5) ? listStrings.get(5) : "");
                } catch (Exception e) {
                    e.printStackTrace();
                }

                fileList.add(f);

            } //end of rows iterator

        } //end of sheets for loop

        //close file input stream
        fis.close();

    } catch (IOException e) {
        e.printStackTrace();
    }
    int k = 1;
    for (HardCopy file : fileList) {
        file.key = k++;
    }
    return fileList;
}

From source file:fr.paris.lutece.plugins.appointment.service.ClosingDayService.java

License:Open Source License

/**
 * Import the closing dates of a given file
 * //from  w  ww .ja v  a2 s.  c  o  m
 * @param item
 *            the file in input
 * @return the list of the closing dates in the file
 * @throws IOException
 *             if error during reading file
 */
public static List<LocalDate> getImportClosingDays(FileItem item) throws IOException {
    HashSet<LocalDate> listDays = new HashSet<LocalDate>();
    FileInputStream fis = null;
    Workbook workbook = null;
    String strExtension = FilenameUtils.getExtension(item.getName());
    if (StringUtils.equals(MARK_EXCEL_EXTENSION_XLSX, strExtension)) {
        try {
            fis = (FileInputStream) item.getInputStream();
            // Using XSSF for xlsx format, for xls use HSSF
            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<Row> rowIterator = sheet.iterator();
                // iterating over each row
                while (rowIterator.hasNext()) {
                    Row row = (Row) rowIterator.next();
                    if (row.getRowNum() > 1) {
                        Iterator<Cell> cellIterator = row.cellIterator();
                        // Iterating over each cell (column wise) in a
                        // particular row.
                        while (cellIterator.hasNext()) {
                            Cell cell = (Cell) cellIterator.next();
                            // The Cell Containing String will is name.
                            if (cell.getColumnIndex() == 3) {
                                String strdate = StringUtils.EMPTY;
                                if (cell.getCellType() == 0) {
                                    Instant instant = cell.getDateCellValue().toInstant();
                                    LocalDate localDate = instant.atZone(ZoneId.systemDefault()).toLocalDate();
                                    strdate = localDate.format(Utilities.getFormatter());
                                }
                                if (StringUtils.isNotEmpty(strdate)
                                        && strdate.matches(MARK_FORMAT_DATE_REGEX)) {
                                    LocalDate date = LocalDate.parse(strdate, Utilities.getFormatter());
                                    listDays.add(date);
                                }
                            }
                        }
                    }
                }
            }
        } finally {
            if (fis != null) {
                fis.close();
            }
            if (workbook != null) {
                workbook.close();
            }
        }
    }
    return new ArrayList<LocalDate>(listDays);
}

From source file:fr.unice.i3s.rockflows.experiments.automatictest.ExcelUtils.java

public static void moveColumns(XSSFSheet sheet) throws Exception {

    Row first = sheet.iterator().next();
    Cell firstCell = first.cellIterator().next();
    int firstIndex = firstCell.getColumnIndex();
    if (firstIndex > 2) {
        int difference = firstIndex - 2;
        for (int i = 0; i < difference; i++) {
            deleteColumn(sheet, 0);/*from   w  w  w. j av a  2s.  c o m*/
        }
    }
}

From source file:Funcionalidad.LeerExcel.java

public boolean leer(File archivo, Contenedor almacenamiento) {
    boolean ok = true;
    try {/*from w  ww. j a  va2  s.  com*/
        FileInputStream fs = new FileInputStream(archivo);
        XSSFWorkbook workbook = new XSSFWorkbook(fs);
        for (int i = 0; i < 5; i++) {
            XSSFSheet sheet = null;
            sheet = workbook.getSheetAt(i);
            Iterator<Row> rowIterator = sheet.iterator();
            Row row;
            while (rowIterator.hasNext()) {
                row = rowIterator.next();
                Iterator<Cell> cellIterator = row.cellIterator();
                Cell celda;
                ArrayList<String> nombres = new ArrayList<>();
                ArrayList<Integer> numeros = new ArrayList<>();
                while (cellIterator.hasNext()) {
                    celda = cellIterator.next();
                    switch (celda.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        nombres.add(celda.getStringCellValue());
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        Double d = celda.getNumericCellValue();
                        numeros.add(d.intValue());
                        break;
                    }
                }
                switch (i) {
                case 0:
                    almacenamiento.anadirProfesor(
                            new Profesor(nombres.get(0), nombres.get(1), nombres.get(2), numeros.get(0)));
                    break;
                case 1:
                    almacenamiento
                            .anadidTitulacion(new Titulacion(numeros.get(0), nombres.get(0), numeros.get(1)));
                    break;
                case 2:
                    almacenamiento.anadirAsignatura(new Asignatura(nombres.get(0),
                            almacenamiento.getProfesorPorId(numeros.get(0)), numeros.get(1),
                            almacenamiento.getTitulacionPorId(numeros.get(2)), numeros.get(3), numeros.get(4)));
                    break;
                case 3:
                    almacenamiento.anadirAula(new Aula(numeros.get(0), nombres.get(0)));
                    break;
                case 4:
                    almacenamiento.anadirGrupo(new Grupo(numeros.get(0),
                            almacenamiento.getAulaPorId(numeros.get(1)),
                            almacenamiento.getTitulacionPorId(numeros.get(2)), numeros.get(3), nombres.get(0)));
                    break;
                }
            }
        }

    } catch (Exception ex) {
        //Logger.getLogger(LeerExcel.class.getName()).log(Level.SEVERE, null, ex);
        ok = false;
    }
    return ok;
}

From source file:gda.hrpd.data.ExcelReader.java

License:Open Source License

/**
 * load data from spreadsheet to the map, or initialise the multimap
 *///from  w  w  w .j a v a 2s  .c o m
public void readData() {
    mvm.clear();

    int i = 0;
    for (Iterator<Row> rit = sheet.rowIterator(); rit.hasNext();) {
        Row row = rit.next();
        for (Iterator<Cell> cit = row.cellIterator(); cit.hasNext();) {
            Cell cell = cit.next();
            if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                mvm.put(i, cell.getRichStringCellValue().toString());
            } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                mvm.put(i, String.valueOf(cell.getNumericCellValue()));
            } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                mvm.put(i, "");
            }
        }
        i++;
    }
    logger.debug("Read row {}", i);
}

From source file:github.srlee309.lessWrongBookCreator.excelReader.SummaryFileReader.java

License:Open Source License

private ArrayList<BookSummarySection> getBookSummarySections(Workbook wb) {
    ArrayList<BookSummarySection> bookSummarySections = new ArrayList<BookSummarySection>();
    HashSet<String> bookNames = new HashSet<String>();
    HashSet<String> sequenceNames = new HashSet<String>();
    Sheet postsSheet = wb.getSheetAt(0);
    Iterator<Row> rowIterator = postsSheet.iterator();
    String currBook = "";
    String currSequence = "";
    if (rowIterator.hasNext()) {
        rowIterator.next(); // skip first row with column headers

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

            Iterator<Cell> cellIterator = row.cellIterator();
            int column = 0;
            // increment the column we are looking for the value from if the book, sequence or title are not provided
            column += Math.abs(row.getPhysicalNumberOfCells() - row.getLastCellNum());
            PostSummarySection postSummarySection = new PostSummarySection();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                column++;/*from ww w  . j  a  v  a  2 s .c om*/
                if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    switch (column) {
                    case 1:
                        currBook = cell.getStringCellValue();
                        break;
                    case 2:
                        currSequence = cell.getStringCellValue();
                        break;
                    case 3:
                        postSummarySection.setTitle(cell.getStringCellValue());
                        break;
                    case 4:
                        postSummarySection.setUrl(cell.getStringCellValue());
                        break;
                    case 5:
                        postSummarySection.setSummary(cell.getStringCellValue());
                        break;
                    }
                }
            }
            if (!bookNames.contains(currBook)) {
                BookSummarySection bookSummarySection = new BookSummarySection(currBook);
                bookSummarySections.add(bookSummarySection);
                bookNames.add(currBook);
            }
            if (sequenceNames.contains(currSequence)) {
                for (BookSummarySection bookSummarySection : bookSummarySections) {
                    SequenceSummarySection sequenceSummarySection = bookSummarySection
                            .getSequenceSummarySection(currSequence);

                    if (sequenceSummarySection != null) {
                        if (!postSummarySection.getUrl().isEmpty()) {
                            sequenceSummarySection.addPostSummarySection(postSummarySection);
                        }
                    }
                }
            } else {
                if (!postSummarySection.getUrl().isEmpty()) {
                    SequenceSummarySection sequenceSummarySection = new SequenceSummarySection(currSequence);

                    sequenceSummarySection.addPostSummarySection(postSummarySection);
                    for (BookSummarySection bookSummarySection : bookSummarySections) {
                        if (bookSummarySection.getTitle().equals(currBook)) {
                            bookSummarySection.addSequenceSummarySection(sequenceSummarySection);
                        }
                    }
                    sequenceNames.add(currSequence);
                }
            }
        }
        HashMap<String, String> sequenceTitleAndSummaries = new HashMap<String, String>();
        HashMap<String, String> bookTitlesAndSummaries = new HashMap<String, String>();
        if (wb.getNumberOfSheets() == 1) {
            logger.info(
                    "There is no second sheet or third sheet found. Therefore, there are no sequence or book summaries found. Perhaps, the excel file is not in the proper format."
                            + newLine);
        } else if (wb.getNumberOfSheets() == 2) {
            logger.info(
                    "There is no third sheet found. Therefore, there are no book summaries found. Perhaps, the excel file is not in the proper format."
                            + newLine);
            sequenceTitleAndSummaries = getTitlesAndSummaries(wb.getSheetAt(1));
        } else {
            sequenceTitleAndSummaries = getTitlesAndSummaries(wb.getSheetAt(1));
            bookTitlesAndSummaries = getTitlesAndSummaries(wb.getSheetAt(2));
        }

        for (BookSummarySection bookSummarySection : bookSummarySections) {
            String bookSummary = bookTitlesAndSummaries.get(bookSummarySection.getTitle());
            if (bookSummary != null) {
                bookSummarySection.setSummary(bookSummary);
            }
            for (SequenceSummarySection sequenceSummarySection : bookSummarySection
                    .getSequenceSummarySections()) {
                String sequenceSummary = sequenceTitleAndSummaries.get(sequenceSummarySection.getTitle());
                if (sequenceSummary != null) {
                    sequenceSummarySection.setSummary(sequenceSummary);
                }
            }
        }
    } else {
        logger.info(
                "There were no rows found in the first sheet. Therefore, no posts were found. Perhaps, the excel file is not in the proper format"
                        + newLine);
    }
    return bookSummarySections;
}

From source file:github.srlee309.lessWrongBookCreator.excelReader.SummaryFileReader.java

License:Open Source License

private HashMap<String, String> getTitlesAndSummaries(Sheet sheet) {
    if (sheet == null) {
        return new HashMap<String, String>();
    }// ww  w.  ja v a 2  s.  co m
    HashMap<String, String> contentSummaries = new HashMap<String, String>();
    Iterator<Row> rowIterator = sheet.iterator();
    if (rowIterator.hasNext()) {
        rowIterator.next(); // skip first row with column headers

        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            //For each row, iterate through each of the columns    
            Iterator<Cell> cellIterator = row.cellIterator();

            int column = 0;
            String key = "";
            String value = "";
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                column++;
                if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    switch (column) {
                    case 1:
                        key = cell.getStringCellValue();
                        break;
                    case 2:
                        value = cell.getStringCellValue();
                        break;
                    }
                }
            }
            if (!key.isEmpty() && !value.isEmpty()) {
                contentSummaries.put(key, value);
            }
        }
    } else {

    }
    return contentSummaries;
}

From source file:gob.dp.sid.registro.controller.ImportarController.java

private void importar(File archivo) {
    List<Object[]> listaObjetos = new ArrayList<>();
    try {// w  w w.  ja  va  2s  .c  o m
        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[7];

            int indiceColumna = -1;
            while (columnaIterator.hasNext()) {
                indiceColumna++;
                Cell celda = (Cell) columnaIterator.next();
                if (indiceFila == 0) {

                } else {
                    if (celda != null && indiceColumna < 7) {
                        switch (celda.getCellType()) {
                        case Cell.CELL_TYPE_NUMERIC:
                            //listaColumna[indiceColumna]= (int)Math.round(celda.getNumericCellValue());
                            listaColumna[indiceColumna] = celda.getDateCellValue();
                            break;
                        case Cell.CELL_TYPE_STRING:
                            listaColumna[indiceColumna] = celda.getStringCellValue();
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            listaColumna[indiceColumna] = celda.getBooleanCellValue();
                            break;
                        default:
                            listaColumna[indiceColumna] = null;
                            break;
                        }
                    }
                }
            }
            if (indiceFila != 0) {
                listaObjetos.add(listaColumna);
            }

        }
        cargarGestiones(listaObjetos);
    } catch (IOException | InvalidFormatException | EncryptedDocumentException e) {
        log.error("importar" + e);
    }
}

From source file:gov.nih.nci.evs.app.neopl.XLStoXLSX.java

License:Open Source License

/**
 * @param args//www  .j  a  va 2  s.c  om
 * @throws InvalidFormatException
 * @throws IOException
 */

public static void run(String inputfile, String outputfile) throws IOException {
    InputStream in = new BufferedInputStream(new FileInputStream(inputfile));
    try {
        Workbook wbIn = new HSSFWorkbook(in);
        File outFn = new File(outputfile);
        if (outFn.exists()) {
            outFn.delete();
        }

        Workbook wbOut = new XSSFWorkbook();
        int sheetCnt = wbIn.getNumberOfSheets();
        for (int i = 0; i < sheetCnt; i++) {
            Sheet sIn = wbIn.getSheetAt(0);
            Sheet sOut = wbOut.createSheet(sIn.getSheetName());
            Iterator<Row> rowIt = sIn.rowIterator();
            while (rowIt.hasNext()) {
                Row rowIn = rowIt.next();
                Row rowOut = sOut.createRow(rowIn.getRowNum());

                Iterator<Cell> cellIt = rowIn.cellIterator();
                while (cellIt.hasNext()) {
                    Cell cellIn = cellIt.next();
                    Cell cellOut = rowOut.createCell(cellIn.getColumnIndex(), cellIn.getCellType());

                    switch (cellIn.getCellType()) {
                    case Cell.CELL_TYPE_BLANK:
                        break;

                    case Cell.CELL_TYPE_BOOLEAN:
                        cellOut.setCellValue(cellIn.getBooleanCellValue());
                        break;

                    case Cell.CELL_TYPE_ERROR:
                        cellOut.setCellValue(cellIn.getErrorCellValue());
                        break;

                    case Cell.CELL_TYPE_FORMULA:
                        cellOut.setCellFormula(cellIn.getCellFormula());
                        break;

                    case Cell.CELL_TYPE_NUMERIC:
                        cellOut.setCellValue(cellIn.getNumericCellValue());
                        break;

                    case Cell.CELL_TYPE_STRING:
                        cellOut.setCellValue(cellIn.getStringCellValue());
                        break;
                    }

                    {
                        CellStyle styleIn = cellIn.getCellStyle();
                        CellStyle styleOut = cellOut.getCellStyle();
                        styleOut.setDataFormat(styleIn.getDataFormat());
                    }
                    cellOut.setCellComment(cellIn.getCellComment());

                    // HSSFCellStyle cannot be cast to XSSFCellStyle
                    // cellOut.setCellStyle(cellIn.getCellStyle());
                }
            }
        }
        OutputStream out = new BufferedOutputStream(new FileOutputStream(outFn));
        try {
            wbOut.write(out);
        } finally {
            out.close();
        }
    } finally {
        in.close();
    }
}

From source file:graphene.hts.file.ExcelXSSFToJSONConverter.java

License:Apache License

private List internalConvert(final Iterator<Row> rowIter) {
    final List<Map<String, String>> excelSheetConversion = new ArrayList<Map<String, String>>();
    final List<String> headerRow = new ArrayList<String>();
    if (rowIter.hasNext()) {
        final Row row = rowIter.next();
        final Iterator<Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext()) {
            final Cell cell = cellIterator.next();
            String cellString = cell.getStringCellValue();
            if (!ValidationUtils.isValid(cellString)) {
                cellString = "Column " + cell.getColumnIndex();
            }/*from   w ww.jav a  2 s .  c om*/
            headerRow.add(cellString);
            System.out.println("Header Column: " + cellString);
        }

    }
    while (rowIter.hasNext()) {
        final Row row = rowIter.next();
        final Map<String, String> kvMap = new TreeMap<String, String>();
        final Iterator<org.apache.poi.ss.usermodel.Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext()) {
            final org.apache.poi.ss.usermodel.Cell cell = cellIterator.next();
            // System.out.println("Header Columns: " + headerRow);
            final int ci = cell.getColumnIndex();
            String key = "Column " + ci;
            if (ci < headerRow.size()) {
                key = headerRow.get(cell.getColumnIndex());
            }
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                if (ValidationUtils.isValid(key, cell.getBooleanCellValue())) {
                    kvMap.put(key, new Boolean(cell.getBooleanCellValue()).toString());
                }
                break;
            case Cell.CELL_TYPE_STRING:
                if (ValidationUtils.isValid(key, cell.getStringCellValue())) {
                    kvMap.put(key, cell.getStringCellValue());
                }
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (ValidationUtils.isValid(key, cell.getNumericCellValue())) {
                    kvMap.put(key, new Double(cell.getNumericCellValue()).toString());
                }
                break;
            case Cell.CELL_TYPE_BLANK:
                break;
            default:
                break;

            }

        }
        excelSheetConversion.add(kvMap);
    }
    logger.debug("Added sheet to conversion.");
    return excelSheetConversion;
}