Example usage for org.apache.poi.ss.usermodel Cell getStringCellValue

List of usage examples for org.apache.poi.ss.usermodel Cell getStringCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. getStringCellValue

Introduction

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

Prototype

String getStringCellValue();

Source Link

Document

Get the value of the cell as a string

For numeric cells we throw an exception.

Usage

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

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

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

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

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

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

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

                int numColumna = 0;

                while (numColumna < row.getLastCellNum()) {

                    Cell cell = row.getCell(numColumna);

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

                    numColumna++;
                }
            }

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

From source file:com.rodrigodev.xgen4j_table_generator.test.common.assertion.excel.conditions.ExcelFile.java

License:Open Source License

@Override
public boolean matches(InputStream actualInputStream) {

    boolean result = true;

    try {/*from  w ww.  ja va 2s  . c  o m*/
        try (Workbook expectedWb = WorkbookFactory.create(expectedInputStream)) {
            Sheet expectedSheet = expectedWb.getSheetAt(0);

            try (Workbook actualWb = WorkbookFactory.create(actualInputStream)) {
                Sheet actualSheet = actualWb.getSheetAt(0);

                int expectedRowCount = expectedSheet.getLastRowNum();
                for (int r = 0; r <= expectedRowCount; r++) {
                    Row expectedRow = expectedSheet.getRow(r);
                    Row actualRow = actualSheet.getRow(r);
                    if (actualRow == null) {
                        actualRow = actualSheet.createRow(r);
                    }

                    int expectedCellCount = expectedRow.getLastCellNum();
                    for (int c = 0; c < expectedCellCount; c++) {
                        Cell expectedCell = expectedRow.getCell(c, Row.CREATE_NULL_AS_BLANK);
                        Cell actualCell = actualRow.getCell(c, Row.CREATE_NULL_AS_BLANK);

                        if (expectedCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                            assertThat(actualCell.getNumericCellValue())
                                    .isEqualTo(expectedCell.getNumericCellValue(), offset(0.00001));
                        } else {
                            expectedCell.setCellType(Cell.CELL_TYPE_STRING);
                            actualCell.setCellType(Cell.CELL_TYPE_STRING);
                            assertThat(actualCell.getStringCellValue())
                                    .isEqualTo(expectedCell.getStringCellValue());
                        }
                    }
                }
            }
        }
    } catch (AssertionError error) {
        describedAs(error.getMessage());
        result = false;
    } catch (Exception e) {
        throw new RuntimeException(e);
    }

    return result;
}

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

/**
 * @param filePath/*from w w w  . ja  v  a2  s.  co m*/
 * @return  list of Readed cells from xlsx
 */
public List<ReadedBean> readXLSFromFile(String filePath) {
    List<ReadedBean> data = new ArrayList();
    FileInputStream fis = null;
    try {
        fis = new FileInputStream(filePath);
        // Using XSSF for xlsx format, for xls use HSSF

        Workbook workbook = new XSSFWorkbook(fis);

        int numberOfSheets = workbook.getNumberOfSheets();

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

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

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

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

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

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

    return data;
}

From source file:com.schneider.tsm.process.addXPRDataManager.java

private String getManager() {
    String manager = "No_Manager_Assigned";

    try {//  w  w  w.j  a  va2 s. co  m
        FileInputStream file = new FileInputStream(new File("C:\\softwaretest\\LibraryTest.xls"));
        HSSFWorkbook workbook = new HSSFWorkbook(file);
        HSSFSheet sheet = workbook.getSheetAt(0);
        Cell cell = null;
        int sheetsize = sheet.getPhysicalNumberOfRows();
        for (int i = 1; i < sheetsize; i++) {
            cell = sheet.getRow(i).getCell(0);
            if (cell.getStringCellValue().equals(requestorID)) {
                cell = sheet.getRow(i).getCell(2);
                manager = cell.getStringCellValue();
            }
        }
        file.close();
        FileOutputStream outFile = new FileOutputStream(new File("C:\\softwaretest\\LibraryTest.xls"));
        workbook.write(outFile);
        outFile.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    return manager;
}

From source file:com.schneider.tsm.process.addXPRDataQuality.java

private String getManager() {
    String manager = null;//from  w  w w. j  a v a2s. c  o  m

    try {
        FileInputStream file = new FileInputStream(new File("C:\\softwaretest\\LibraryTest.xls"));
        HSSFWorkbook workbook = new HSSFWorkbook(file);
        HSSFSheet sheet = workbook.getSheetAt(0);
        Cell cell = null;
        int sheetsize = sheet.getPhysicalNumberOfRows();
        for (int i = 1; i < sheetsize; i++) {
            cell = sheet.getRow(i).getCell(0);
            if (cell.getStringCellValue().equals(requestorID)) {
                cell = sheet.getRow(i).getCell(2);
                manager = cell.getStringCellValue();
            }
        }
        file.close();
        FileOutputStream outFile = new FileOutputStream(new File("C:\\softwaretest\\LibraryTest.xls"));
        workbook.write(outFile);
        outFile.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    return manager;

}

From source file:com.schneider.tsm.process.addXPRDataQuality.java

private String getQuality() {
    String Quality = "No_Exist";

    try {/*from w ww  .java2 s . c o m*/
        FileInputStream file = new FileInputStream(new File("C:\\softwaretest\\LibraryTest.xls"));
        HSSFWorkbook workbook = new HSSFWorkbook(file);
        HSSFSheet sheet = workbook.getSheetAt(0);
        Cell cell = null;
        int sheetsize = sheet.getPhysicalNumberOfRows();
        for (int i = 1; i < sheetsize; i++) {
            cell = sheet.getRow(i).getCell(0);
            if (cell.getStringCellValue().equals(requestorID)) {
                cell = sheet.getRow(i).getCell(4);
                Quality = cell.getStringCellValue();
            }
        }
        file.close();
        FileOutputStream outFile = new FileOutputStream(new File("C:\\softwaretest\\LibraryTest.xls"));
        workbook.write(outFile);
        outFile.close();
    } catch (FileNotFoundException e) {
    } catch (IOException e) {
    }

    return Quality;
}

From source file:com.schneider.tsmteam.MainTSMWindow.java

private void ProcesaXLS() {

    final SwingWorker worker = new SwingWorker() {

        @Override/*from   w w  w  . ja v  a  2s .co m*/
        protected Object doInBackground() throws Exception {
            String contenido = "s";
            int unidadProgresBAR = contador / 100;
            for (int uy = 0; uy < contador; uy++) {
                jLabelState.setText(listadearchivos[uy] + " is in Processing");

                if (listadearchivos[uy].endsWith("all.xls")) {
                    System.out.println(listadearchivos[uy] + "Termina con All");

                    try {

                        FileInputStream file = new FileInputStream(
                                new File(jTextFieldDirectory.getText() + "\\" + listadearchivos[uy]));
                        HSSFWorkbook workbook = new HSSFWorkbook(file);
                        HSSFSheet sheet = workbook.getSheetAt(0);
                        Cell cell = null;
                        int sheetsize = sheet.getPhysicalNumberOfRows();

                        cell = sheet.getRow(0).getCell(21);
                        if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                            if (sheetsize > 1) {

                                cell = sheet.getRow(1).getCell(21);

                                for (int i = 1; i < sheetsize; i++) {
                                    cell = sheet.getRow(i).getCell(21);
                                    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                                        String cellContents = cell.getStringCellValue();
                                        cell = sheet.getRow(i).getCell(1);
                                        cell.setCellValue(cellContents);
                                        cell = sheet.getRow(i).getCell(21);
                                        cell.setCellType(Cell.CELL_TYPE_BLANK);
                                    }
                                }
                            }

                            cell = sheet.getRow(0).getCell(21);
                            cell.setCellType(Cell.CELL_TYPE_BLANK);
                        }
                        file.close();
                        // ORIGINAL FileOutputStream outFile =new FileOutputStream(new File(jTextFieldDirectory.getText()+ "\\" + listadearchivos[uy]));
                        /*  TEST*/ FileOutputStream outFile = new FileOutputStream(
                                new File(jTextFieldDirectory.getText() + "\\" + listadearchivos[uy]));
                        workbook.write(outFile);
                        outFile.close();
                    } catch (FileNotFoundException e) {
                        e.printStackTrace();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }

                } else {
                    System.out.println(listadearchivos[uy] + "Termina con Upd");

                    try {

                        FileInputStream file = new FileInputStream(
                                new File(jTextFieldDirectory.getText() + "\\" + listadearchivos[uy]));
                        HSSFWorkbook workbook = new HSSFWorkbook(file);
                        HSSFSheet sheet = workbook.getSheetAt(0);
                        Cell cell = null;
                        int sheetsize = sheet.getPhysicalNumberOfRows();
                        cell = sheet.getRow(0).getCell(22);
                        if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                            if (sheetsize > 1) {
                                cell = sheet.getRow(1).getCell(22);
                                for (int i = 1; i < sheetsize; i++) {
                                    cell = sheet.getRow(i).getCell(22);
                                    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                                        String cellContents = cell.getStringCellValue();
                                        cell = sheet.getRow(i).getCell(1);
                                        cell.setCellValue(cellContents);
                                        cell = sheet.getRow(i).getCell(22);
                                        cell.setCellType(Cell.CELL_TYPE_BLANK);
                                    }
                                }
                            }
                            cell = sheet.getRow(0).getCell(22);
                            cell.setCellType(Cell.CELL_TYPE_BLANK);
                        }
                        file.close();
                        FileOutputStream outFile = new FileOutputStream(
                                new File(jTextFieldDirectory.getText() + "\\" + listadearchivos[uy]));
                        workbook.write(outFile);

                        outFile.close();
                    } catch (FileNotFoundException e) {
                        e.printStackTrace();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }

                jLabelState.setText(listadearchivos[uy] + " is Completed");
                Thread.sleep(300);
            }
            JOptionPane hola = new JOptionPane();
            jButton2.setVisible(true);
            jButton1.setVisible(true);
            jLabelState.setText("Complete");
            JOptionPane.showMessageDialog(hola, "Complete");
            System.out.println(contador);
            return null;
        }

    };

    worker.execute();
}

From source file:com.sdfl.compiler.util.inputfile.impl.ImportInputFileLoaderFileSystemImpl.java

License:Open Source License

private ImportInputFileRow loadRow(Row lCurRow) {
    ImportInputFileRow lCurInputFileRow = new ImportInputFileRow();

    for (Cell lCurCell : lCurRow) {
        lCurInputFileRow.setColumn(lCurCell.getColumnIndex(), lCurCell.getStringCellValue());
    }//from www .  ja v a  2s.co  m

    return lCurInputFileRow;
}

From source file:com.sfs.ucm.controller.HelpContentAction.java

License:Open Source License

/**
 * Extract help content// www. ja  v a  2 s  .  c  o m
 * 
 * @param buf
 *            byte array representing help content file
 * @throws UCMException
 */
private void extractHelpContent(byte[] buf) throws UCMException {

    try {
        InputStream inp = new ByteArrayInputStream(buf);

        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(0);
        Iterator<Row> iter = sheet.iterator();
        Cell cell = null;
        Row row = null;

        // header rows
        if (iter.hasNext()) {
            row = iter.next(); // table title
            row = iter.next(); // column headers
        }

        if (iter.hasNext()) {
            while (iter.hasNext()) {

                // process records
                row = iter.next();

                // help key
                cell = row.getCell(0);

                if (cell != null) {
                    String key = cell.getStringCellValue();

                    // help content
                    cell = row.getCell(1);
                    String contents = cell.getStringCellValue();

                    // log it
                    Object[] values = new Object[3];
                    values[0] = row.getRowNum() + 1; // display as one-based
                    values[1] = key;
                    values[2] = StringUtils.abbreviate(contents, 20);
                    logger.info("Processing row {}; contents: {};{}", values);

                    // construct the help content object
                    Help theHelpItem = new Help(key, contents);

                    // if help item already exists then just update its contents otherwise add record
                    int ndx = this.helpItems.indexOf(theHelpItem);
                    if (ndx == -1) {
                        this.helpItems.add(theHelpItem);
                        logger.info("Added Help Item {}", theHelpItem.getKeyword());

                        // persist the object
                        em.persist(theHelpItem);

                    } else {
                        Help tmp = this.helpItems.get(ndx);
                        tmp.setContent(contents);

                        // persist the object
                        em.persist(tmp);
                        logger.info("Updated Help Item {}", tmp.getKeyword());

                    }
                }
            }
        }

        // done
        inp.close();
    } catch (InvalidFormatException e) {
        logger.error(e.getMessage());
        throw new UCMException(e);
    } catch (IOException e) {
        logger.error(e.getMessage());
        throw new UCMException(e);
    }
}

From source file:com.siacra.beans.GrupoBean.java

public void archivoXlsx(String path, FileUploadEvent archivo) {
    excelResponse = new ArrayList<>();
    List<Horario> horas = new ArrayList<>();

    try {/*from www . j  a va2 s. c o m*/
        FileInputStream file = new FileInputStream(new File(path + "\\" + archivo.getFile().getFileName()));

        // Crear el objeto que tendra el libro de Excel
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        /*
         * Obtenemos la primera pestaa a la que se quiera procesar indicando el indice.
         * Una vez obtenida la hoja excel con las filas que se quieren leer obtenemos el iterator
         * que nos permite recorrer cada una de las filas que contiene.
        */

        XSSFSheet sheet = workbook.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.iterator();
        Row row;

        // Recorremos todas las filas para mostrar el contenido de cada celda

        int cantidad = 0;
        int cantidad2 = 0;

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

            if (cantidad2 != 0) {
                Horario h = new Horario();
                UpploadGrupos grupo = new UpploadGrupos();
                // Obtenemos el iterator que permite recorres todas las celdas de una fila
                Iterator<Cell> cellIterator = row.cellIterator();
                Cell celda;

                cantidad = 1;

                while (cellIterator.hasNext()) {
                    celda = cellIterator.next();

                    //                        if((cantidad%10)==0)
                    //                        {
                    //                            System.out.print(grupo.toString());
                    //                            excelResponse.add(grupo);
                    //                            grupo = new UpploadGrupos();
                    //                            cantidad=1;
                    //                        }

                    // Dependiendo del formato de la celda el valor se debe mostrar como String, Fecha, boolean, entero...
                    switch (celda.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        if (DateUtil.isCellDateFormatted(celda)) {

                            SimpleDateFormat f = new SimpleDateFormat("HH:mm:ss");
                            String fecha = f.format(celda.getDateCellValue());
                            System.out.print(":::::::: " + fecha);
                            Date dos = f.parse(fecha);

                            if (cantidad == 4) {
                                h.setHinicio1(dos);
                                grupo.setInicio1(dos);
                                cantidad++;
                            } else if (cantidad == 5) {
                                h.setHfin1(dos);
                                grupo.setFin1(dos);
                                cantidad++;
                            } else if (cantidad == 7) {
                                h.setHinicio2(dos);
                                grupo.setInicio2(dos);
                                cantidad++;
                            } else if (cantidad == 8) {
                                h.setHfin2(dos);
                                grupo.setFin2(dos);
                                cantidad++;
                            }

                            System.out.println(dos);
                        }
                        //                                else
                        //                                {
                        //                                   double numero = celda.getNumericCellValue();
                        //                                   System.out.println(celda.getNumericCellValue());
                        //                                }
                        break;

                    case Cell.CELL_TYPE_STRING:
                        if (cantidad == 1) {
                            grupo.setAsignatura(celda.getStringCellValue());
                            cantidad++;
                        } else if (cantidad == 2) {
                            grupo.setTipoGrupo(celda.getStringCellValue());
                            cantidad++;
                        } else if (cantidad == 3) {
                            h.setDia1(celda.getStringCellValue());
                            grupo.setDia1(celda.getStringCellValue());
                            cantidad++;
                        } else if (cantidad == 6) {
                            h.setDia2(celda.getStringCellValue());
                            grupo.setDia2(celda.getStringCellValue());
                            cantidad++;
                        } else if (cantidad == 9) {
                            grupo.setNumeroGrupo(celda.getStringCellValue());
                            cantidad++;
                        } else if (cantidad == 10) {
                            grupo.setCupos(celda.getStringCellValue());
                            cantidad++;
                        }
                        String texto = celda.getStringCellValue();
                        System.out.println(celda.getStringCellValue());
                        break;

                    //                        case Cell.CELL_TYPE_BOOLEAN:
                    //                            System.out.println(celda.getBooleanCellValue());
                    //                            break;

                    }//fin if que obtiene valor de celda
                } //fin while que recorre celdas
                System.out.print("objeto:::" + grupo.toString());
                System.out.print("objeto:::" + h.toString());
                horas.add(h);
                excelResponse.add(grupo);
            } // fin if primera iteracion

            cantidad2++;
        } // fin while que recorre filas
          // cerramos el libro excel
        workbook.close();
    } catch (Exception e) {
        e.printStackTrace();
    }

    procesarListaCargada();

}