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:at.mukprojects.exclycore.dao.XLSXReader.java

License:Open Source License

private ExclyLong readLong(Cell cell, int type) throws Exception {
    ExclyLong output = null;//from w w  w .  jav a  2  s .c o m

    if (type == Cell.CELL_TYPE_STRING) {
        String data = cell.getStringCellValue();
        if (isNumericGerman(data)) {
            Number number = NumberFormat.getNumberInstance(Locale.GERMAN).parse(data);
            output = new ExclyLong(number.intValue());
        } else if (isNumericUK(data)) {
            Number number = NumberFormat.getNumberInstance(Locale.UK).parse(data);
            output = new ExclyLong(number.intValue());
        } else if (data.equals("") || data.equals(" ") || data.equals("-")) {
            output = new ExclyLongBlank();
        } else {
            output = new ExclyLongError();
            log.warn("The reader has expected a numeric value, but found a string value. [Row, Column] ("
                    + cell.getRowIndex() + ", " + cell.getColumnIndex() + ")");
        }
    } else if (type == Cell.CELL_TYPE_BLANK) {
        output = new ExclyLongBlank();
    } else if (type == Cell.CELL_TYPE_FORMULA) {
        int formulaType = cell.getCachedFormulaResultType();
        output = readLong(cell, formulaType);
    } else if (type == Cell.CELL_TYPE_BOOLEAN) {
        Boolean data = cell.getBooleanCellValue();
        if (data) {
            output = new ExclyLong(1);
        } else {
            output = new ExclyLong(0);
        }
    } else if (type == Cell.CELL_TYPE_NUMERIC) {
        double data = cell.getNumericCellValue();
        output = new ExclyLong(data);
    } else if (type == Cell.CELL_TYPE_ERROR) {
        output = new ExclyLongError();
    } else {
        log.warn("The reader was unable to find a valid parser for the cell [Row, Column] ("
                + cell.getRowIndex() + ", " + cell.getColumnIndex() + ")");
        output = new ExclyLongError();
    }

    return output;
}

From source file:at.mukprojects.exclycore.dao.XLSXReader.java

License:Open Source License

private ExclyDate readDate(Cell cell, int type) throws Exception {
    ExclyDate output = null;// ww w  .  j a  va 2 s .c om

    if (type == Cell.CELL_TYPE_STRING) {
        String data = cell.getStringCellValue();
        if (isNumericGerman(data)) {
            Number number = NumberFormat.getNumberInstance(Locale.GERMAN).parse(data);
            output = new ExclyDate(DateUtil.getJavaDate(number.intValue()));
        } else if (isNumericUK(data)) {
            Number number = NumberFormat.getNumberInstance(Locale.UK).parse(data);
            output = new ExclyDate(DateUtil.getJavaDate(number.intValue()));
        } else if (data.equals("") || data.equals(" ") || data.trim().equals("-")) {
            output = new ExclyDateBlank();
        } else {
            ExclyDate parsedDate = parse(cell.getStringCellValue());
            output = parsedDate;
        }
    } else if (type == Cell.CELL_TYPE_BLANK) {
        output = new ExclyDateBlank();
    } else if (type == Cell.CELL_TYPE_FORMULA) {
        int formulaType = cell.getCachedFormulaResultType();
        output = readDate(cell, formulaType);
    } else if (DateUtil.isCellDateFormatted(cell)) {
        Date data = cell.getDateCellValue();
        output = new ExclyDate(data);
    } else if (type == Cell.CELL_TYPE_NUMERIC) {
        double data = cell.getNumericCellValue();
        output = new ExclyDate(DateUtil.getJavaDate(data));
    } else if (type == Cell.CELL_TYPE_ERROR) {
        output = new ExclyDateError();
    } else {
        log.warn("The reader was unable to find a valid parser for the cell [Row, Column] ("
                + cell.getRowIndex() + ", " + cell.getColumnIndex() + ")");
        output = new ExclyDateError();
    }

    return output;
}

From source file:at.nhmwien.schema_mapping_tool.fileProcessors.XlsxProcessor.java

License:Apache License

public void prepareFileRead() throws Exception {
    this.fieldNames = new HashMap<String, Integer>();

    this.readBook = new XSSFWorkbook(new FileInputStream(this.operateFile));
    this.readSheet = this.readBook.getSheetAt(0);

    Iterator<Cell> cellIt = this.readSheet.getRow(0).cellIterator();
    while (cellIt.hasNext()) {
        Cell currCell = cellIt.next();

        if (currCell.getCellType() != Cell.CELL_TYPE_STRING)
            continue;

        this.fieldNames.put(currCell.getStringCellValue(), currCell.getColumnIndex());
    }/*from ww  w  . ja v a  2s .  c  o  m*/

    this.currRow = 1;
}

From source file:at.nhmwien.schema_mapping_tool.schemaReaders.XlsxConverter.java

License:Apache License

public LinkedHashMap<String, LinkedHashMap> parseFile(InputStream inputFile) {
    LinkedHashMap<String, LinkedHashMap> fields = null;

    try {//from   w  w  w.j  a  va  2  s  . c  o  m

        XSSFWorkbook workbook = new XSSFWorkbook(inputFile);
        XSSFSheet sheet = workbook.getSheetAt(0);
        XSSFRow row = sheet.getRow(0);

        Iterator<Cell> cellIt = row.cellIterator();

        fields = new LinkedHashMap();

        while (cellIt.hasNext()) {
            Cell currCell = cellIt.next();

            // Ignore any non-string cells (because they are not suitable as headers)
            if (currCell.getCellType() != Cell.CELL_TYPE_STRING)
                continue;

            LinkedHashMap fieldInfo = new LinkedHashMap();
            fieldInfo.put("name", currCell.getStringCellValue());
            fieldInfo.put("subfields", null);

            fields.put(currCell.getStringCellValue(), fieldInfo);
        }

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

    return fields;
}

From source file:attandance.standalone.utils.ExcelUtils.java

public static List<AttandanceRecord> readDataFromExcel(String fileName) {
    List<AttandanceRecord> result = new ArrayList<>();
    try {/*from w  w  w.j  av  a2  s . c  o  m*/
        FileInputStream file = new FileInputStream(new File(fileName));
        //Get the workbook instance for XLS file 
        HSSFWorkbook workbook = new HSSFWorkbook(file);
        //Get first sheet from the workbook
        HSSFSheet sheet = workbook.getSheetAt(0);
        //Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();
        int lineIndex = 1;
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            //For each row, iterate through each columns
            Iterator<Cell> cellIterator = row.cellIterator();
            int columnIndex = 1;
            AttandanceRecord lineBean = new AttandanceRecord();
            //skip header
            if (lineIndex == 1) {
                lineIndex += 1;
                continue;
            }
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                String cellValue = "";
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_BOOLEAN:
                    cellValue = "" + cell.getBooleanCellValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    cellValue = "" + cell.getNumericCellValue();
                    break;
                case Cell.CELL_TYPE_STRING:
                    cellValue = cell.getStringCellValue();
                    break;
                default:
                    cellValue = cell.getStringCellValue();
                }
                lineBean.setAttribute(columnIndex, cellValue);
                columnIndex = columnIndex + 1;
            }
            result.add(lineBean);
        }
        file.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return result;
}

From source file:attendance.CheckIn.java

private static void checkName() throws FileNotFoundException, IOException {
    idNum = jInputField.getText() + ".0";
    jInputField.setText("");

    for (Sheet sheet : sheets) {
        for (Row row : sheet) {
            String cell1 = row.getCell(ID_COL).toString();

            if (cell1.equals(idNum)) {
                Cell name = row.getCell(NAME_COL);
                addingName = name.getStringCellValue();

                String paidStatus = row.getCell(PAIDSTATUS_COL).toString();

                if (row.getCell(CHECKED_IN_COL).toString().equals("Checked In")) {
                    jCheckedInTextArea.append(addingName + "\n");

                    flashColor(Color.red, jCheckedInTextArea);
                } else if (!paidStatus.equals("")) {
                    jPaidTextArea.append(addingName + "\n");
                    row.getCell(CHECKED_IN_COL).setCellValue("Checked In");
                    alreadyPaidAL.add(addingName);
                    flashColor(Color.GREEN, jPaidTextArea);
                } else {
                    jNotPaidTextArea.append(addingName + "\n");
                    row.getCell(CHECKED_IN_COL).setCellValue("Checked In");
                    flashColor(Color.red, jNotPaidTextArea);
                }//  w w w  .  ja va 2 s.  com
            }
        }
    }
}

From source file:automatedhgl.AutomatedHGL.java

public static void main(String[] args) {

    try {/*  w  w w  . j a v a2s  .c  o  m*/

        FileInputStream excelFile = new FileInputStream(new File(INFILE_NAME));

        //create workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(excelFile);

        //get first desired sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(0);

        //create workbook instance to output excel file
        XSSFWorkbook workbookHGL = new XSSFWorkbook();

        //create sheet in output excel file
        XSSFSheet sheetHGL = workbookHGL.createSheet("HGL");

        //iterate through each row one by one
        Iterator<Row> rowiterator = sheet.iterator();

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

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

            while (cellIterator.hasNext()) {

                Cell cell = cellIterator.next();

                if (row.getRowNum() > 7 && count < 23) //to filter column headings
                {

                    //check the cell type and format accordingly
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        count++;

                        //assign get value to correct variable
                        if (count == 1) {
                            InletStr = cell.getNumericCellValue();
                        } else if (count == 2) {
                            OutWSE = cell.getNumericCellValue();
                        }

                        System.out.print(cell.getNumericCellValue() + " (" + count + ") ");
                        break;

                    case Cell.CELL_TYPE_STRING:
                        count++;

                        /*//assign get value to correct variable
                        if( count == 1 ){InletStr = cell.getStringCellValue();}*/

                        System.out.print(cell.getStringCellValue() + " (" + count + ") ");
                        break;

                    case Cell.CELL_TYPE_FORMULA:
                        count++;

                        /*//assign get value to correct variable
                        if( count == 1 ){InletStr = cell.getCachedFormulaResultType();}*/

                        System.out.print(cell.getCachedFormulaResultType() + " (" + count + ") ");
                        break;
                    }
                }

                else {
                    count = 0; //reset the count at the end of the row
                }

            }

            System.out.println("return");
        }

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

    //Output Excel file

    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("Datatypes in Java");
    Object[][] datatypes = { { "Datatype", "Type", "Size(in bytes)" }, { "int", "Primitive", 2 },
            { "float", "Primitive", 4 }, { "double", "Primitive", 8 }, { "char", "Primitive", 1 },
            { "String", "Non-Primitive", "No fixed size" } };

    int rowNum = 0;
    System.out.println("Creating excel");

    for (Object[] datatype : datatypes) {
        Row row = sheet.createRow(rowNum++);
        int colNum = 0;
        for (Object field : datatype) {
            Cell cell = row.createCell(colNum++);
            if (field instanceof String) {
                cell.setCellValue((String) field);
            } else if (field instanceof Integer) {
                cell.setCellValue((Integer) field);
            }
        }
    }

    try {
        FileOutputStream outputStream = new FileOutputStream(FILE_NAME);
        workbook.write(outputStream);
        workbook.close();

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

    System.out.print(InletStr + " ");
    System.out.print(OutWSE + " ");
    System.out.println("HGL Done");

}

From source file:bad.robot.excel.row.CopyRow.java

License:Apache License

private static void setCellDataValue(Cell oldCell, Cell newCell) {
    switch (oldCell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        newCell.setCellValue(oldCell.getStringCellValue());
        break;//w  w  w  .jav  a2 s . c  o  m
    case Cell.CELL_TYPE_BOOLEAN:
        newCell.setCellValue(oldCell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_ERROR:
        newCell.setCellErrorValue(oldCell.getErrorCellValue());
        break;
    case Cell.CELL_TYPE_FORMULA:
        newCell.setCellFormula(oldCell.getCellFormula());
        break;
    case Cell.CELL_TYPE_NUMERIC:
        newCell.setCellValue(oldCell.getNumericCellValue());
        break;
    case Cell.CELL_TYPE_STRING:
        newCell.setCellValue(oldCell.getRichStringCellValue());
        break;
    }
}

From source file:bandaru_excelreadwrite.ReadfromExcel.java

public List getSongsListFromExcel() {
    List songList = new ArrayList();
    FileInputStream fis = null;//from   w w w.  j av a2  s  .com

    try {
        fis = new FileInputStream(FILE_PATH);

        /*
          Use 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()) {

                Song song = new Song();
                Row row = (Row) rowIterator.next();

                Iterator cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {

                    Cell cell = (Cell) cellIterator.next();

                    /*
                    checking if the cell is having a String value .
                    */
                    if (Cell.CELL_TYPE_STRING == cell.getCellType()) {

                        /*
                        Cell with index 1 contains Album name 
                        */
                        if (cell.getColumnIndex() == 1) {
                            song.setAlbumname(cell.getStringCellValue());
                        }

                        /*
                        Cell with index 2 contains Genre
                        */
                        if (cell.getColumnIndex() == 2) {
                            song.setGenre(cell.getStringCellValue());
                        }

                        /*
                        Cell with index 3 contains Artist name
                        */
                        if (cell.getColumnIndex() == 3) {
                            song.setArtist(cell.getStringCellValue());
                        }

                    }

                    /*
                     checking if the cell is having a numeric value
                    */
                    else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {

                        /*
                        Cell with index 0 contains Sno
                        */
                        if (cell.getColumnIndex() == 0) {
                            song.setSno((int) cell.getNumericCellValue());
                        }

                        /*
                        Cell with index 5 contains Critic score.
                        */
                        else if (cell.getColumnIndex() == 5) {
                            song.setCriticscore((int) cell.getNumericCellValue());
                        }

                        /*
                        Cell with index 4 contains Release date
                        */
                        else if (cell.getColumnIndex() == 4) {
                            Date dateValue = null;

                            if (DateUtil.isCellDateFormatted(cell)) {
                                dateValue = cell.getDateCellValue();
                            }
                            song.setReleasedate(dateValue);
                        }

                    }

                }

                /*
                end iterating a row, add all the elements of a row in list
                */
                songList.add(song);
            }
        }

        fis.close();

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return songList;
}

From source file:be.thomasmore.controller.FileController.java

private void leesExcel() {
    try {// w  w w. j a va 2 s.c o m
        //Excelbestand in RAM steken voor Apache POI
        InputStream fileInputStream = part.getInputStream();
        XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
        XSSFSheet worksheet = workbook.getSheet("Blad1");
        EntityManagerFactory emf = Persistence.createEntityManagerFactory("ScoreTrackerPU");
        EntityManager em = emf.createEntityManager();
        //Iterator om door de worksheets te gaan (enkel nodig om het eerste worksheet door te gaan)
        Iterator<Row> rowIterator = worksheet.iterator();

        //Klas zoeken en persisten
        //Door de rijen itereren
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            //Over de kolommen van deze rij itereren
            Iterator<Cell> cellIterator = row.cellIterator();
            if (row.getRowNum() == 0) {
                while (cellIterator.hasNext()) {
                    //Cell vastnemen
                    Cell cell = cellIterator.next();
                    //Kijken of er in de eerste cell 'klas' staat
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        if (cell.getStringCellValue().equalsIgnoreCase("klas")) {
                            //breaken zodat hij doorgaat naar de volgende cell
                            break;
                            //Checken of de cell niet leeg is
                        } else if (!cell.getStringCellValue().equalsIgnoreCase("")) {
                            if (klas.getNummer() == null) {
                                //Klas werkt
                                Query q = em.createNamedQuery("Klas.findByNummer");
                                q.setParameter("nummer", cell.getStringCellValue());
                                if (q.getResultList().size() == 0) {
                                    klas.setNummer(cell.getStringCellValue());
                                    defaultService.addKlas(klas);
                                } else {
                                    klas = (Klas) q.getSingleResult();
                                }
                            }
                        }
                        break;
                    }
                } //Einde van celliterator
            } else if (row.getRowNum() == 1) {
                while (cellIterator.hasNext()) {
                    //Cell vastnemen
                    Cell cell = cellIterator.next();
                    //Kijken of in de allereerste cel 'vak' staat
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        if (cell.getStringCellValue().equalsIgnoreCase("vak")) {
                            //breaken zodat hij doorgaat naar de volgende cell
                            break;
                        } else if (!cell.getStringCellValue().equalsIgnoreCase("")) {
                            if (vak.getNaam() == null) {
                                Query q = em.createNamedQuery("Vak.findByNaam");
                                q.setParameter("naam", cell.getStringCellValue());
                                if (q.getResultList().size() == 0) {
                                    vak.setNaam(cell.getStringCellValue());
                                    defaultService.addVak(vak);
                                } else {
                                    vak = (Vak) q.getSingleResult();
                                }
                            }
                        }
                    }
                } //Einde van celliterator
            } else if (row.getRowNum() == 2) {
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    if (cell.getColumnIndex() == 1) {
                        test.setBeschrijving(cell.getStringCellValue());
                    }
                }
            } else if (row.getRowNum() == 3) {
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    if (cell.getCellType() == Cell.CELL_TYPE_STRING
                            && cell.getStringCellValue().equalsIgnoreCase("totaal")) {

                    }
                    if (cell.getColumnIndex() == 1) {
                        test.setTotaalScore((int) cell.getNumericCellValue());
                        test.setVakId(vak);
                        ///
                        Query q = em.createNamedQuery("Test.findByBeschrijving");
                        q.setParameter("beschrijving", test.getBeschrijving());
                        if (q.getResultList().size() == 0) {
                            defaultService.addTest(test);
                        } else {
                            test = (Test) q.getSingleResult();
                        }
                        ///

                        klasTest.setKlasId(klas);
                        klasTest.setTestId(test);
                        Query q2 = em.createNamedQuery("Klastest.findByKlasIdTestId");
                        q2.setParameter("klasId", klasTest.getKlasId());
                        q2.setParameter("testId", klasTest.getTestId());
                        if (q2.getResultList().size() == 0) {
                            if (klasTest.getKlasId().getId() != null) {

                                defaultService.addKlastest(klasTest);
                            }
                        } else {
                            klasTest = (Klastest) q2.getSingleResult();
                        }
                    }
                }
            } else if (row.getRowNum() > 5) {
                Student student = new Student();
                Score score = new Score();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();

                    if (cell.getCellType() == Cell.CELL_TYPE_STRING
                            && cell.getStringCellValue().equalsIgnoreCase("zit al in de DB")) {
                        break;
                    }
                    if (cell.getColumnIndex() == 0) {
                        if (cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                            student.setStudentenNr((int) cell.getNumericCellValue());
                        }
                    }
                    if (cell.getColumnIndex() == 1) {
                        String[] voorenachternaam = cell.getStringCellValue().split("\\s+");
                        student.setVoornaam(voorenachternaam[0]);
                        if (voorenachternaam.length >= 3) {
                            if (voorenachternaam.length >= 4) {
                                student.setNaam(
                                        voorenachternaam[1] + voorenachternaam[2] + voorenachternaam[3]);
                                student.setEmail(voorenachternaam[0] + "." + voorenachternaam[1]
                                        + voorenachternaam[2] + voorenachternaam[3] + "@student.thomasmore.be");
                            } else {
                                student.setNaam(voorenachternaam[1] + voorenachternaam[2]);
                                student.setEmail(voorenachternaam[0] + "." + voorenachternaam[1]
                                        + voorenachternaam[2] + "@student.thomasmore.be");
                            }
                        } else {
                            student.setNaam(voorenachternaam[1]);
                            student.setEmail(
                                    voorenachternaam[0] + "." + voorenachternaam[1] + "@student.thomasmore.be");
                        }
                        student.setKlasId(klas);
                    }
                    if (cell.getColumnIndex() == 2) {
                        score.setScore((int) cell.getNumericCellValue());
                        score.setTestId(test);
                        score.setStudentId(student);
                        break;
                    }
                }

                if (student.getStudentenNr() != null) {
                    studenten.add(student);
                }
                if (score.getTestId() != null && score.getStudentId().getStudentenNr() != null) {
                    scores.add(score);
                }

            }
        } //einde van rowiterator
        for (Student student : studenten) {
            Query q = em.createNamedQuery("Student.findByStudentenNr");
            q.setParameter("studentenNr", student.getStudentenNr());
            if (q.getResultList().size() == 0) {
                defaultService.addStudent(student);
            } else {
                Student st = (Student) q.getSingleResult();
                student.setId(st.getId());
            }
        }
        for (Score score : scores) {
            Query q = em.createNamedQuery("Score.findByTestIdStudentIdScore");
            q.setParameter("testId", score.getTestId());
            q.setParameter("studentId", score.getStudentId());
            q.setParameter("score", score.getScore());
            if (q.getResultList().size() == 0) {
                defaultService.addScore(score);
            } else {
                score = (Score) q.getSingleResult();
            }
        }
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}