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.blackducksoftware.tools.commonframework.standard.workbook.CsvWriter.java

License:Apache License

@Override
public void write(Workbook wb) throws IOException {

    int numSheets = wb.getNumberOfSheets();
    for (int i = 0; i < numSheets; i++) {

        File curOutputFile = getCurrentOutputFile(filePath, numSheets, i);

        CSVWriter pw = new CSVWriter(new OutputStreamWriter(new FileOutputStream(curOutputFile)),
                CSVWriter.DEFAULT_SEPARATOR, CSVWriter.DEFAULT_QUOTE_CHARACTER, "\r\n");

        try {/*from  ww w.  j  a  v  a2s .c o  m*/
            Sheet sheet = wb.getSheetAt(i);
            for (Row row : sheet) {
                List<String> cells = new ArrayList<String>();
                String cellValue = "";
                for (Cell cell : row) {
                    int cellType = cell.getCellType();
                    switch (cellType) {
                    case Cell.CELL_TYPE_BLANK:
                        cellValue = "";
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        boolean cellValueBoolean = cell.getBooleanCellValue();
                        cellValue = cellValueBoolean ? "true" : "false";
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        cellValue = "<error: " + cell.getErrorCellValue() + ">";
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        cellValue = cell.getCellFormula();
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        double cellValueDouble = cell.getNumericCellValue();
                        cellValue = Double.toString(cellValueDouble);
                        break;
                    case Cell.CELL_TYPE_STRING:
                        cellValue = cell.getStringCellValue();
                        break;
                    default:
                        break;
                    }

                    cells.add(cellValue);
                }
                String[] typeExample = new String[cells.size()];
                String[] cellArray = cells.toArray(typeExample);
                pw.writeNext(cellArray);
            }
        } finally {
            pw.close();
        }
    }
}

From source file:com.blackducksoftware.tools.commonframework.test.TestUtils.java

License:Apache License

private static String getCellValueString(Cell cell) {
    String cellValueString;/* w  w w.  j a va 2  s  . c o m*/

    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        cellValueString = String.valueOf(cell.getNumericCellValue());
    } else {
        cellValueString = cell.getStringCellValue();
    }
    return cellValueString.trim();
}

From source file:com.br.uepb.bsc7.www.UI.ManipulaXLS.java

public /*static*/ void leXLS(String filename) throws IOException {
    System.out.println("Mtodo leXLS chamado!");
    FileInputStream fileInputStream = new FileInputStream(filename);
    try {/*w w  w. j  a  v a  2s.  c  o m*/
        //Obtem acesso  pasta de trabalho
        wb = new HSSFWorkbook(fileInputStream);
        //Obtem acesso  planilha Plan1
        HSSFSheet s = wb.getSheet("Plan1");

        Iterator<Row> rowIterator = s.rowIterator();

        while (rowIterator.hasNext()) {
            System.out.println("Nova linha!");
            //Obtem acesso a cada linha de Plan1
            Row linha = rowIterator.next();

            Iterator<Cell> cellIterator = linha.cellIterator();

            while (cellIterator.hasNext()) {
                //Obtem acesso a cada clula de cada linha de Plan1
                Cell celula = cellIterator.next();
                //System.out.println(celula.getStringCellValue());

                //Adiciona o valor de cada clula ao ArrayList que ser passado a DAO
                dados.add(celula.getStringCellValue());
                //
            }

            //Chamada ao mtodo do BD que recebe o ArrayList (Deve estar em DAO)
            new TesteInsereBD().insereLinha(dados, dados.size());
            //Limpa o ArrayLista para preench-lo novamente
            dados.clear();
        }
        //Corrigir este catch com algo mais eficiente
    } catch (IOException ex) {
        System.out.println("Teste");
    }
}

From source file:com.camel.action.location.CityAction.java

public void handleFileUpload(FileUploadEvent event) {
    try {/*from ww  w .ja v a2  s  .  c  o  m*/
        List<City> citiesList = new ArrayList<City>();

        //Create the input stream from the xlsx/xls file
        String fileName = event.getFile().getFileName();
        String cityCode = "";
        String cityName = "";
        String countryCode = "";
        String countryName = "";

        InputStream fis = event.getFile().getInputstream();

        //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);
        }

        Sheet sheet = workbook.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.iterator();
        Row row = null;
        Iterator<Cell> cellIterator = null;
        Cell cell = null;
        City city = null;
        while (rowIterator.hasNext()) {
            cityCode = "";
            cityName = "";
            countryCode = "";
            countryName = "";

            row = rowIterator.next();
            cellIterator = row.cellIterator();

            if (row.getRowNum() == 0)
                continue;

            while (cellIterator.hasNext()) {
                cell = cellIterator.next();
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    if (cityCode.equalsIgnoreCase("")) {
                        cityCode = cell.getStringCellValue().trim();
                    } else if (cityName.equalsIgnoreCase("")) {
                        cityName = cell.getStringCellValue().trim();
                    } else if (countryCode.equalsIgnoreCase("")) {
                        countryCode = cell.getStringCellValue().trim();
                    }
                    break;
                }
            } //end of cell iterator
            if (countryCode.equals("#N/A"))
                continue;

            country = findCountry(countryCode);

            if (country != null) {
                city = new City();
                city.setCityCode(cityCode);
                city.setCityName(cityName);
                city.setCountry(country);

                citiesList.add(city);
            }

        } //end of rows iterator

        //close file input stream
        fis.close();
        for (City c : citiesList) {
            super.setInstance(c);
            super.save();
        }
    } catch (IOException e) {
        e.printStackTrace();
    }

    FacesMessage message = new FacesMessage("Succesful", event.getFile().getFileName() + " is uploaded.");
    FacesContext.getCurrentInstance().addMessage(null, message);
    country = null;
}

From source file:com.camel.action.location.CountryAction.java

public void handleFileUpload(FileUploadEvent event) {
    String errorMessage = "";
    try {//  ww  w  .j a v  a2s. c  o m
        List<Country> countriesList = new ArrayList<Country>();

        //Create the input stream from the xlsx/xls file
        String fileName = event.getFile().getFileName();
        String name = "";
        String shortCode = "";
        String cont = "";

        InputStream fis = event.getFile().getInputstream();

        //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);
        }

        Sheet sheet = workbook.getSheetAt(0);

        Iterator<Row> rowIterator = sheet.iterator();
        Row row = null;
        Iterator<Cell> cellIterator = null;
        Cell cell = null;
        Country country = null;
        while (rowIterator.hasNext()) {
            name = "";
            shortCode = "";
            cont = "";

            row = rowIterator.next();

            cellIterator = row.cellIterator();

            if (row.getRowNum() == 0) {
                continue;
            }

            while (cellIterator.hasNext()) {

                cell = cellIterator.next();
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    if (shortCode.equalsIgnoreCase("")) {
                        shortCode = cell.getStringCellValue().trim();
                    } else if (name.equalsIgnoreCase("")) {
                        name = cell.getStringCellValue().trim();
                    } else if (cont.equalsIgnoreCase("")) {
                        cont = cell.getStringCellValue().trim();
                    }
                    break;

                }

            } //end of cell iterator
            if (cont != null && cont.length() > 3) {
                country = new Country();
                country.setContinet(Continent.valueOf(cont));
                country.setCountryCode(shortCode);
                country.setCountryName(name);
                countriesList.add(country);
            }
        } //end of rows iterator

        fis.close();
        for (Country c : countriesList) {
            super.setInstance(c);
            super.save();
        }
    } catch (IOException e) {
        errorMessage = e.getMessage();
    }
    System.out.println("eerrromessage..:" + errorMessage);
    FacesMessage message = null;
    if (errorMessage != null && errorMessage.length() > 3) {
        message = new FacesMessage("ERROR..:", "Country dosn't uploaded![" + errorMessage + "]");
    } else {
        message = new FacesMessage("Succesful", event.getFile().getFileName() + " is uploaded.");
    }
    FacesContext.getCurrentInstance().addMessage(null, message);

}

From source file:com.camel.action.location.PortAction.java

public void handleFileUpload(FileUploadEvent event) {
    try {/*from   w  w  w  . j a v a 2s  .c o m*/
        List<Port> portsList = new ArrayList<Port>();

        //Create the input stream from the xlsx/xls file
        String fileName = event.getFile().getFileName();
        String portCode = "";
        String portName = "";
        String cityCode = "";
        String cityName = "";
        String countryCode = "";
        String countryName = "";
        InputStream fis = event.getFile().getInputstream();

        //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);
        }

        Sheet sheet = workbook.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.iterator();
        Row row = null;
        Iterator<Cell> cellIterator = null;
        Cell cell = null;
        City city = null;
        Port port = null;
        while (rowIterator.hasNext()) {
            portCode = "";
            portName = "";
            cityCode = "";
            cityName = "";
            countryCode = "";
            countryName = "";

            row = rowIterator.next();
            cellIterator = row.cellIterator();

            if (row.getRowNum() == 0)
                continue;

            while (cellIterator.hasNext()) {
                cell = cellIterator.next();
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    if (portCode.equalsIgnoreCase("")) {
                        portCode = cell.getStringCellValue().trim();
                    } else if (portName.equalsIgnoreCase("")) {
                        portName = cell.getStringCellValue().trim();
                    } else if (cityCode.equalsIgnoreCase("")) {
                        cityCode = cell.getStringCellValue().trim();
                    } else if (cityName.equalsIgnoreCase("")) {
                        cityName = cell.getStringCellValue().trim();
                    } else if (countryCode.equalsIgnoreCase("")) {
                        countryCode = cell.getStringCellValue().trim();
                    } else if (countryName.equalsIgnoreCase("")) {
                        countryName = cell.getStringCellValue().trim();
                    }
                    break;
                }
            } //end of cell iterator
            if (countryCode.equals("#N/A"))
                continue;

            country = findCountry(countryCode);
            city = findCity(cityCode);
            if (country != null && city != null) {
                port = new Port();
                port.setPortType(PortType.SEAPORT);
                port.setPortCode(portCode);
                port.setPortName(portName);
                port.setCity(city);
                port.setCountry(country);
                portsList.add(port);
            }

        } //end of rows iterator

        //close file input stream
        fis.close();
        for (Port c : portsList) {
            super.setInstance(c);
            super.save();
        }
    } catch (IOException e) {
        e.printStackTrace();
    }

    FacesMessage message = new FacesMessage("Succesful", event.getFile().getFileName() + " is uploaded.");
    FacesContext.getCurrentInstance().addMessage(null, message);

    country = null;

}

From source file:com.celtris.exparse.parser.ExcelReader.java

License:Apache License

public List<SheetData<T>> readExcel(String absolutePath, Class<T> excelModelClass, boolean headerExtraction)
        throws IOException, InstantiationException, IllegalAccessException {

    FileInputStream file = new FileInputStream(new File(absolutePath));

    // Create Workbook instance holding reference to .xlsx file
    XSSFWorkbook workbook = new XSSFWorkbook(file);
    DataFormatter objDefaultFormat = new DataFormatter();
    FormulaEvaluator objFormulaEvaluator = new XSSFFormulaEvaluator(workbook);

    Iterator<Sheet> sheetIterator = workbook.iterator();
    List<SheetData<T>> sheetDataList = new ArrayList<SheetData<T>>(workbook.getNumberOfSheets());
    int sheetCount = 0;
    while (sheetIterator.hasNext()) {
        sheetCount++;/* w w w.j a v a2  s  .c om*/

        ExcelParser<T> excelParser = new ExcelParser<T>(headerExtraction, excelModelClass);
        Sheet sheet = sheetIterator.next();
        Iterator<Row> rowIterator = sheet.iterator();

        int rowCount = 0;

        // Evaluating header
        if (headerExtraction) {
            if (rowIterator.hasNext()) {

                rowCount++;

                Field[] fields = excelModelClass.getFields();
                List<String> heaaderStr = new ArrayList<String>(fields.length);

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

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

                    heaaderStr.add(cellStrValue);
                }
                excelParser.processFieldAccordingToHeader(heaaderStr, sheet.getSheetName());
            }
        }

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

            List<String> rowStr = new ArrayList<String>(excelParser.parameterCount());
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                String cellStrValue = "";
                switch (cell.getCellTypeEnum()) {
                case STRING:
                    cellStrValue = cell.getStringCellValue();
                    break;
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        objFormulaEvaluator.evaluate(cell);
                        cellStrValue = objDefaultFormat.formatCellValue(cell, objFormulaEvaluator);
                    } else {

                        cellStrValue = Double.toString(cell.getNumericCellValue());
                    }
                    break;
                case BOOLEAN:
                    cellStrValue = Boolean.toString(cell.getBooleanCellValue());
                    break;
                case FORMULA:
                    cellStrValue = cell.getStringCellValue();
                    break;
                case BLANK:

                default:
                    break;
                }
                rowStr.add(cellStrValue);
            }

            excelParser.processRow(rowStr, rowCount, sheet.getSheetName());
        }

        SheetData<T> sheetData = new SheetData<T>(excelParser.getParsedObject(), sheet.getSheetName(),
                sheetCount);
        sheetDataList.add(sheetData);
    }

    file.close();
    workbook.close();
    return sheetDataList;
}

From source file:com.citrix.g2w.webdriver.util.ReadExcelReport.java

License:Open Source License

/**
 * Method to get the cell value and convert to String.
 * //from   ww w.jav a 2 s  . c o m
 * @param cell
 * @return String
 */
private String getValue(Cell cell) {

    switch (cell.getCellType()) {

    case Cell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue() + "";

    case Cell.CELL_TYPE_NUMERIC:
        return ((int) cell.getNumericCellValue()) + "";

    case Cell.CELL_TYPE_STRING:
        return cell.getStringCellValue();

    case Cell.CELL_TYPE_BLANK:
        return "";

    default:
        return "";
    }
}

From source file:com.clican.pluto.dataprocess.engine.processes.ExcelProcessor.java

License:LGPL

public void readExcel(ProcessorContext context, ExcelExecBean execBean) throws Exception {
    InputStream is = new AutoDecisionResource(execBean.getResource()).getInputStream();
    try {// w  w  w . j  a  v  a  2  s.c  o  m
        Workbook book = WorkbookFactory.create(is);
        Sheet sheet = book.getSheet(execBean.getSheetName());
        List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
        List<String> names = new ArrayList<String>();
        Map<String, String> typeMap = execBean.getTypeMap();
        int firstRow = sheet.getFirstRowNum(), lastRow = sheet.getLastRowNum();
        for (int rowIdx = firstRow; rowIdx < lastRow; rowIdx++) {
            Row excelRow = sheet.getRow(rowIdx);

            short minColIx = excelRow.getFirstCellNum();
            short maxColIx = excelRow.getLastCellNum();

            Map<String, Object> row = new HashMap<String, Object>();

            for (int colIdx = minColIx; colIdx < maxColIx; colIdx++) {
                Cell cell = excelRow.getCell(colIdx, Row.CREATE_NULL_AS_BLANK);

                if (rowIdx == 0) {
                    names.add(cell.getStringCellValue());
                } else {
                    String type = null;
                    if (names.size() > colIdx) {
                        type = typeMap.get(names.get(colIdx));
                    }
                    if (StringUtils.isNotEmpty(type)) {
                        if (type.equals("string")) {
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            row.put(names.get(colIdx), cell.getStringCellValue().trim());
                        } else if (type.equals("double")) {
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            row.put(names.get(colIdx), cell.getNumericCellValue());
                        } else if (type.equals("int")) {
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            row.put(names.get(colIdx), (int) cell.getNumericCellValue());
                        } else if (type.equals("date")) {
                            row.put(names.get(colIdx), cell.getDateCellValue());
                        } else {
                            throw new DataProcessException("??Excel?");
                        }
                    }
                }
            }
            if (rowIdx != 0) {
                result.add(row);
            }
        }
        context.setAttribute(execBean.getResultName(), result);
    } finally {
        if (is != null) {
            is.close();
        }
    }

}

From source file:com.cloudera.sa.ExcelRecordReader.java

License:Apache License

private Text getCellValue(Cell cell) {
    Text out = new Text();
    CellType cellType = cell.getCellTypeEnum();

    if (cellType == CellType.STRING) {
        out.set(cell.getStringCellValue());
    } else if (cellType == CellType.NUMERIC) {
        out.set(String.valueOf(cell.getNumericCellValue()));
    } else if (cellType == CellType.FORMULA) {
        out.set(cell.getCellFormula());//from  w ww. ja  v a  2 s .  c  o m
    } else if (cellType == CellType.ERROR) {
        out.set(String.valueOf(cell.getErrorCellValue()));
    } else if (cellType == CellType.BOOLEAN) {
        out.set(String.valueOf(cell.getBooleanCellValue()));
    } else {
        out.set("");
    }

    return out;
}