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

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

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

Introduction

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

Prototype

double getNumericCellValue();

Source Link

Document

Get the value of the cell as a number.

Usage

From source file:cn.study.innerclass.PoiUtil.java

License:Open Source License

public static Object getCellData(Cell cell, FormulaEvaluator formula) {
    if (cell == null) {
        return null;
    }//w  w w  . ja  va 2 s  .  com
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        System.out.println(cell.getRichStringCellValue().getString());
        return cell.getRichStringCellValue().getString();
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            System.out.println(cell.getDateCellValue());
            return cell.getDateCellValue();
        } else {
            System.out.println(cell.getNumericCellValue());
            return cell.getNumericCellValue();
        }
    case Cell.CELL_TYPE_BOOLEAN:
        System.out.println(cell.getBooleanCellValue());
        return cell.getBooleanCellValue();
    case Cell.CELL_TYPE_FORMULA:
        System.out.println(cell.getStringCellValue());

        switch (formula.evaluate(cell).getCellType()) {
        case Cell.CELL_TYPE_STRING:
            System.out.println(formula.evaluate(cell).getStringValue());
            return formula.evaluate(cell).getStringValue();
        case Cell.CELL_TYPE_NUMERIC:
            System.out.println(formula.evaluate(cell).getNumberValue());
            return formula.evaluate(cell).getNumberValue();
        case Cell.CELL_TYPE_BOOLEAN:
            System.out.println(formula.evaluate(cell).getBooleanValue());
            return formula.evaluate(cell);

        }
    default:
        return null;
    }
}

From source file:co.com.smartcode.bitcom.managedbeans.crud.utils.ExcelUtils.java

private static String getString(Row row, int i) {
    Cell cell = row.getCell(i);
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_NUMERIC:
        DecimalFormat format = new DecimalFormat("#");
        return format.format(cell.getNumericCellValue());
    case Cell.CELL_TYPE_STRING:
        return cell.getStringCellValue();
    }//from w ww .  ja v  a  2 s.co m
    return null;
}

From source file:co.foldingmap.data.ExcelDataConnector.java

License:Open Source License

/**
 * Returns a cell value as a DataCell object.
 * //from w  w w  .  j a v  a  2 s .co m
 * @param cell
 * @return 
 */
public DataCell getCellText(Cell cell) {
    DataCell cellText;

    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        cellText = new DataCell(cell.getRichStringCellValue().getString());
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            cellText = new DataCell(cell.getDateCellValue().toString());
        } else {
            cellText = new DataCell(Double.toString(cell.getNumericCellValue()));
        }

        break;
    case Cell.CELL_TYPE_BOOLEAN:
        cellText = new DataCell(Boolean.toString(cell.getBooleanCellValue()));
        break;
    case Cell.CELL_TYPE_FORMULA:
        cellText = new DataCell(cell.getCellFormula());
        break;
    default:
        cellText = new DataCell("");
    }

    return cellText;
}

From source file:CODIGOS.Planilha.java

public static void lerPlanilha(String arquivo) {

    FileInputStream fisPlanilha = null;

    try {//from w w  w . j  a v  a  2 s.c om

        File dir = new File("C:\\GA6");
        File file = new File(dir, arquivo + ".xlsx");
        fisPlanilha = new FileInputStream(file);

        /*CRIA UM WORKBOOK = PLANILHA TODA COM TODAS AS ABAS*/
        XSSFWorkbook workbook = new XSSFWorkbook(fisPlanilha);

        /*RECUPERAMOS APENAS A PRIMEIRA ABA OU PRIMEIRA PLANILHA*/
        XSSFSheet sheet = workbook.getSheetAt(0);

        /*RETORNA TODAS AS LINHAS DA PLANILHA 0 */
        Iterator<Row> rowIterator = sheet.iterator();

        /*VARRE TODAS AS LINHAS DA PLANILHA 0*/
        while (rowIterator.hasNext()) {

            //recebe cada linha da planilha
            Row row = rowIterator.next();

            //pegamos todas as celulas desta linha
            Iterator<Cell> cellIterator = row.iterator();

            //varremos todas as celulas da linha atual
            while (cellIterator.hasNext()) {

                //criamos uma celula
                Cell cell = cellIterator.next();

                switch (cell.getCellType()) {

                case Cell.CELL_TYPE_STRING:
                    PS[count] = "" + cell.getStringCellValue();
                    count++;
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    PS[count] = "" + cell.getNumericCellValue();
                    PS[count] = PS[count].replace(".0", "");
                    count++;
                    break;

                }

            }

        }

    } catch (FileNotFoundException ex) {
        JOptionPane.showMessageDialog(null, "Arquivo " + arquivo + " no encontrado.", "Warning",
                JOptionPane.WARNING_MESSAGE);
        System.exit(0);
    } catch (IOException ex) {
        Logger.getLogger(Planilha.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        try {
            fisPlanilha.close();
        } catch (IOException ex) {
            Logger.getLogger(Planilha.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

}

From source file:com.abixen.platform.service.businessintelligence.multivisualisation.service.impl.parser.ExcelParserServiceImpl.java

License:Open Source License

private DataValue parseAsDouble(Cell cell) {
    DataValueDouble dataValueDouble = new DataValueDouble();
    dataValueDouble.setValue(cell.getNumericCellValue());
    return dataValueDouble;
}

From source file:com.accenture.bean.PlanoExcel.java

public void extraiPlanilha() {
    try {//from w  w  w  .j a v  a2 s.  c  o  m
        //Leitura
        FileInputStream arquivo = new FileInputStream(new File(fileName));

        // Carregando workbook
        XSSFWorkbook wb = new XSSFWorkbook(arquivo);
        // Selecionando a primeira aba
        XSSFSheet s = wb.getSheetAt(1);

        // Caso queira pegar valor por referencia
        CellReference cellReference = new CellReference("M8");
        Row row = s.getRow(cellReference.getRow());
        Cell cell = row.getCell(cellReference.getCol());
        System.out.println("Valor Refe:" + cell.getStringCellValue());

        // Fazendo um loop em todas as linhas
        for (Row rowFor : s) {
            // FAzendo loop em todas as colunas
            for (Cell cellFor : rowFor) {
                try {
                    // Verifica o tipo de dado
                    if (cellFor.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        // Na coluna 6 tenho um formato de data
                        if (cellFor.getColumnIndex() == 6) {
                            // Se estiver no formato de data
                            if (DateUtil.isCellDateFormatted(cellFor)) {
                                // Formatar para o padrao brasileiro
                                Date d = cellFor.getDateCellValue();
                                DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
                                System.out.println(df.format(d));
                            }
                        } else {
                            // Mostrar numerico
                            System.out.println(cellFor.getNumericCellValue());
                        }
                    } else {
                        // Mostrar String
                        System.out.println(cellFor.getStringCellValue());
                    }
                } catch (Exception e) {
                    // Mostrar Erro
                    System.out.println(e.getMessage());
                }
            }
            // Mostrar pulo de linha
            System.out.println("------------------------");
        }

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

From source file:com.accenture.control.ExcelDAO.java

public String[] carregaPlanilhaFuncionalidade() throws IOException, ClassNotFoundException, SQLException {

    Plano plano = new Plano();
    ManipulaDadosSQLite banco = new ManipulaDadosSQLite();
    String[] funcionalidade = null;
    try {/*from w  ww. j  a  va  2  s  .co m*/
        FileInputStream arquivo = new FileInputStream(new File(ExcelDAO.fileName));
        XSSFWorkbook workbook = new XSSFWorkbook(arquivo);
        //setado a planilha de configuraes
        XSSFSheet sheetPlano = workbook.getSheetAt(2);
        //linha pa
        int linha = 1;
        int coluna = 4;

        funcionalidade = new String[sheetPlano.getLastRowNum()];
        int index = 0;
        for (int count = 1; count < sheetPlano.getLastRowNum(); count++) {
            Row row = sheetPlano.getRow(count);
            for (int countColuna = 0; countColuna < 1; countColuna++) {
                Cell cell = row.getCell(coluna, Row.CREATE_NULL_AS_BLANK);
                System.out.println(cell.getColumnIndex() + "-" + cell.getRowIndex());
                if (cell.getCellType() == CELL_TYPE_BLANK) {
                    System.out.println("Campo vazio");
                } else if (cell.getCellType() == CELL_TYPE_NUMERIC) {
                    double valor = cell.getNumericCellValue();

                    System.out.println(valor);
                } else {
                    String valor = cell.getStringCellValue();
                    System.out.println(valor);
                    funcionalidade[index] = valor;
                    System.out.println(funcionalidade[index]);
                    banco.insertTabelaConf("TB_FUNCIONALIDADE", "DESC_FUNCIONALIDADE", valor);
                    index++;
                }
            }
        }

    } catch (FileNotFoundException ex) {
        Logger.getLogger(ExcelDAO.class.getName()).log(Level.SEVERE, null, ex);

    }
    return funcionalidade;
}

From source file:com.accenture.control.ExcelDAO.java

public String[] carregaPlanilhaSistemaMaster() throws IOException {

    Plano plano = new Plano();

    String[] sistemaMaster = null;
    try {//from   www.j  a  v  a 2 s  .c o m
        FileInputStream arquivo = new FileInputStream(new File(ExcelDAO.fileName));
        XSSFWorkbook workbook = new XSSFWorkbook(arquivo);
        //setado a planilha de configuraes
        XSSFSheet sheetPlano = workbook.getSheetAt(2);
        //linha pa
        int linha = 1;
        int coluna = 2;

        sistemaMaster = new String[sheetPlano.getLastRowNum()];
        int index = 0;
        for (int count = 1; count < sheetPlano.getLastRowNum(); count++) {
            Row row = sheetPlano.getRow(count);
            for (int countColuna = 0; countColuna < 1; countColuna++) {
                Cell cell = row.getCell(coluna, Row.CREATE_NULL_AS_BLANK);
                System.out.println(cell.getColumnIndex() + "-" + cell.getRowIndex());
                if (cell.getCellType() == CELL_TYPE_BLANK) {
                    System.out.println("Campo vazio");
                } else if (cell.getCellType() == CELL_TYPE_NUMERIC) {
                    double valor = cell.getNumericCellValue();
                    System.out.println(valor);
                } else {
                    String valor = cell.getStringCellValue();
                    System.out.println(valor);
                    sistemaMaster[index] = valor;
                    System.out.println(sistemaMaster[index]);
                    index++;
                }
            }
        }

    } catch (FileNotFoundException ex) {
        Logger.getLogger(ExcelDAO.class.getName()).log(Level.SEVERE, null, ex);
    }
    return sistemaMaster;
}

From source file:com.accenture.control.ExtraiPlanilha.java

public String extraiPlanilhaPlanos(String planilha)
        throws SQLException, ClassNotFoundException, FileNotFoundException, IOException {

    String msg;/*from  w  w w .j  ava2  s.  c  o  m*/

    Plano p = new Plano();

    ManipulaDados md = new ManipulaDados();
    ManipulaDadosSQLite bdLite = new ManipulaDadosSQLite();
    //capturando arquivo recebido 
    FileInputStream arquivo = new FileInputStream(new File(planilha));
    System.out.println(planilha);
    XSSFWorkbook workbook = new XSSFWorkbook(arquivo);

    XSSFSheet sheetAlunos = workbook.getSheetAt(1);
    int linha = 7;
    int numCelula = 1;

    Row row = sheetAlunos.getRow(linha);
    Cell celCadeia = row.getCell(1);
    Cell celSegmento = row.getCell(2);
    Cell ccelProduto = row.getCell(3);
    Cell celFuncionalidade = row.getCell(4);
    Cell celCenarioIntegracao = row.getCell(5);
    Cell celSistemaMaster = row.getCell(6);
    Cell celSistemaEnvolvidos = row.getCell(7);
    Cell celFornecedor = row.getCell(8);
    Cell celTpRequisito = row.getCell(9);
    Cell celRequisito = row.getCell(10);
    Cell celCenario = row.getCell(11);
    Cell celCasoTeste = row.getCell(12);
    Cell celDescricao = row.getCell(13);
    Cell celQtdSistemas = row.getCell(18);
    Cell celCenarioAuto = row.getCell(20);
    Cell celType = row.getCell(21);
    Cell celTrg = row.getCell(22);
    Cell celSubject = row.getCell(23);
    Cell celCriacao = row.getCell(24);

    Cell celStep = row.getCell(15);

    //                       Plano p = new Plano();
    Step steps = new Step();

    while (!celCasoTeste.getStringCellValue().equals("")) {
        row = sheetAlunos.getRow(linha);
        celCadeia = row.getCell(1);
        celSegmento = row.getCell(2);
        ccelProduto = row.getCell(3);
        celFuncionalidade = row.getCell(4);
        celCenarioIntegracao = row.getCell(5);
        celSistemaMaster = row.getCell(6);
        celSistemaEnvolvidos = row.getCell(7);
        celFornecedor = row.getCell(8);
        celTpRequisito = row.getCell(9);
        celRequisito = row.getCell(10);
        celCenario = row.getCell(11);
        celCasoTeste = row.getCell(12);
        celDescricao = row.getCell(13);
        celQtdSistemas = row.getCell(18);
        celCenarioAuto = row.getCell(20);
        celType = row.getCell(21);
        celTrg = row.getCell(22);
        celSubject = row.getCell(23);
        celCriacao = row.getCell(24);

        System.out.println("Caso de Teste: " + celCasoTeste.getStringCellValue());

        msg = "Extraindo caso de teste: " + celCasoTeste.getStringCellValue();
        //                           form.setAreaTextExtracao(msg);

        p.setCadeia(celCadeia.getStringCellValue());
        p.setSegmento(celSegmento.getStringCellValue());
        p.setProduto(ccelProduto.getStringCellValue());
        p.setFuncionalidade(celFuncionalidade.getStringCellValue());
        p.setCenarioIntegrado(celCenarioIntegracao.getStringCellValue());
        p.setSistemaMaster(celSistemaMaster.getStringCellValue());
        p.setSistemasEnvolvidos(celSistemaEnvolvidos.getStringCellValue());
        p.setFornecedor(celFornecedor.getStringCellValue());
        p.setTpRequisito(celTpRequisito.getStringCellValue());
        p.setRequisito(celRequisito.getStringCellValue());
        p.setCenarioTeste(celCenario.getStringCellValue());
        p.setCasoTeste(celCasoTeste.getStringCellValue());
        p.setDescCasoTeste(celDescricao.getStringCellValue());
        p.setQtdSistemas((int) celQtdSistemas.getNumericCellValue());
        p.setCenarioAutomatizavel(celCenarioAuto.getStringCellValue());
        p.setType(celType.getStringCellValue());
        p.setTrg(celTrg.getStringCellValue());
        p.setSubject(celSubject.getStringCellValue());
        p.setCriacaoAlteracao(celCriacao.getStringCellValue());

        bdLite.insertPlano(p);
        celStep = row.getCell(15);
        int linhaStep = linha;
        linha = 25 + linha;
        row = sheetAlunos.getRow(linha);
        celCasoTeste = row.getCell(12);

        while (!celStep.getStringCellValue().equals("")) {

            row = sheetAlunos.getRow(linhaStep);
            Cell celNomeStep = row.getCell(15);
            Cell celDescStep = row.getCell(16);
            Cell celResultadoStep = row.getCell(17);

            //                              steps.setIdPlano(md.getIdPlanoBanco(p));
            steps.setIdPlano(bdLite.getIdPlanoBanco(p));
            steps.setNomeStep(celNomeStep.getStringCellValue());
            steps.setDescStep(celDescStep.getStringCellValue());
            steps.setResultadoStep(celResultadoStep.getStringCellValue());
            p.setStep(steps);
            //                              bd.insertStep(steps.getNomeStep(), steps.getDescStep(), steps.getResultadoStep(), steps.getIdPlano());

            //                              md.insertStep(steps);
            bdLite.insertStep(p);
            linhaStep = linhaStep + 1;

            row = sheetAlunos.getRow(linhaStep);
            celStep = row.getCell(15);
        }
    }

    arquivo.close();

    msg = "Concludo";
    //          form.setAreaTextExtracao(msg);
    return msg;
}

From source file:com.accenture.control.ExtraiPlanilha.java

public String importaCTNovosPlanilha(String planilha, int linha)
        throws SQLException, ClassNotFoundException, FileNotFoundException, IOException {

    String msg;/*from  www. j av a2 s  . c  o  m*/

    Plano p = new Plano();

    ManipulaDados md = new ManipulaDados();
    ManipulaDadosSQLite bdLite = new ManipulaDadosSQLite();
    //capturando arquivo recebido 
    FileInputStream arquivo = new FileInputStream(new File(planilha));
    System.out.println(planilha);
    XSSFWorkbook workbook = new XSSFWorkbook(arquivo);

    XSSFSheet sheetCTs = workbook.getSheetAt(1);
    //        int linha = 7;

    Row row = sheetCTs.getRow(linha);
    Cell celCadeia = row.getCell(1);
    Cell celSegmento = row.getCell(2);
    Cell ccelProduto = row.getCell(3);
    Cell celFuncionalidade = row.getCell(4);
    Cell celCenarioIntegracao = row.getCell(5);
    Cell celSistemaMaster = row.getCell(6);
    Cell celSistemaEnvolvidos = row.getCell(7);
    Cell celFornecedor = row.getCell(8);
    Cell celTpRequisito = row.getCell(9);
    Cell celRequisito = row.getCell(10);
    Cell celCenario = row.getCell(11);
    Cell celCasoTeste = row.getCell(12);
    Cell celDescricao = row.getCell(13);
    Cell celQtdSistemas = row.getCell(19);
    Cell celCenarioAuto = row.getCell(21);
    Cell celType = row.getCell(22);
    Cell celTrg = row.getCell(23);
    Cell celSubject = row.getCell(24);
    Cell celCriacao = row.getCell(25);

    Cell celStep = row.getCell(16);

    //                       Plano p = new Plano();
    Step steps = new Step();

    row = sheetCTs.getRow(linha);
    celCadeia = row.getCell(1);
    celSegmento = row.getCell(2);
    ccelProduto = row.getCell(3);
    celFuncionalidade = row.getCell(4);
    celCenarioIntegracao = row.getCell(5);
    celSistemaMaster = row.getCell(6);
    celSistemaEnvolvidos = row.getCell(7);
    celFornecedor = row.getCell(8);
    celTpRequisito = row.getCell(9);
    celRequisito = row.getCell(10);
    celCenario = row.getCell(11);
    celCasoTeste = row.getCell(12);
    celDescricao = row.getCell(13);
    celQtdSistemas = row.getCell(19);
    celCenarioAuto = row.getCell(21);
    celType = row.getCell(22);
    celTrg = row.getCell(23);
    celSubject = row.getCell(24);
    celCriacao = row.getCell(25);

    System.out.println("Caso de Teste: " + celCasoTeste.getStringCellValue());

    msg = "Extraindo caso de teste: " + celCasoTeste.getStringCellValue();
    //                           form.setAreaTextExtracao(msg);

    p.setCadeia(celCadeia.getStringCellValue());
    p.setSegmento(celSegmento.getStringCellValue());
    p.setProduto(ccelProduto.getStringCellValue());
    p.setFuncionalidade(celFuncionalidade.getStringCellValue());
    p.setCenarioIntegrado(celCenarioIntegracao.getStringCellValue());
    p.setSistemaMaster(celSistemaMaster.getStringCellValue());
    p.setSistemasEnvolvidos(celSistemaEnvolvidos.getStringCellValue());
    p.setFornecedor(celFornecedor.getStringCellValue());
    p.setTpRequisito(celTpRequisito.getStringCellValue());
    p.setRequisito(celRequisito.getStringCellValue());
    p.setCenarioTeste(celCenario.getStringCellValue());
    p.setCasoTeste(celCasoTeste.getStringCellValue());
    p.setDescCasoTeste(celDescricao.getStringCellValue());
    p.setQtdSistemas((int) celQtdSistemas.getNumericCellValue());
    p.setCenarioAutomatizavel(celCenarioAuto.getStringCellValue());
    p.setType(celType.getStringCellValue());
    p.setTrg(celTrg.getStringCellValue());
    p.setSubject(celSubject.getStringCellValue());
    p.setCriacaoAlteracao(celCriacao.getStringCellValue());

    bdLite.insertPlano(p);
    celStep = row.getCell(16);
    int linhaStep = linha;
    linha = 25 + linha;
    row = sheetCTs.getRow(linha);
    celCasoTeste = row.getCell(12);

    while (!celStep.getStringCellValue().equals("")) {

        row = sheetCTs.getRow(linhaStep);
        Cell celNomeStep = row.getCell(16);
        Cell celDescStep = row.getCell(17);
        Cell celResultadoStep = row.getCell(18);

        //                              steps.setIdPlano(md.getIdPlanoBanco(p));
        steps.setIdPlano(bdLite.getIdPlanoBanco(p));
        steps.setNomeStep(celNomeStep.getStringCellValue());
        steps.setDescStep(celDescStep.getStringCellValue());
        steps.setResultadoStep(celResultadoStep.getStringCellValue());
        p.setStep(steps);
        //                              bd.insertStep(steps.getNomeStep(), steps.getDescStep(), steps.getResultadoStep(), steps.getIdPlano());

        //                              md.insertStep(steps);
        bdLite.insertStep(p);
        linhaStep = linhaStep + 1;

        row = sheetCTs.getRow(linhaStep);
        celStep = row.getCell(16);
    }

    arquivo.close();

    msg = "Concludo";
    //          form.setAreaTextExtracao(msg);
    return msg;
}