Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. getSheetAt

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt.

Prototype

@Override
public XSSFSheet getSheetAt(int index)

Source Link

Document

Get the XSSFSheet object at the given index.

Usage

From source file:cn.comgroup.tzmedia.server.report.CustomerOrderReport.java

public CustomerOrderReportResult runOrderReport(String deployPath, List<CustomerOrder> orders)
        throws ParseException, FileNotFoundException, IOException {
    String reportTemplate = deployPath + File.separator + "template" + File.separator
            + "CustomerOrderReport.xlsx";
    final XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(reportTemplate));
    DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
    XSSFSheet sheet = workbook.getSheetAt(0);
    int startRow = 1;
    for (CustomerOrder co : orders) {
        XSSFRow row = sheet.getRow(startRow);
        if (row == null) {
            row = sheet.createRow(startRow);
        }/*from   ww  w  .j a  v  a  2 s  .  c o  m*/
        XSSFCell cellOrderNumber = row.getCell(0);
        if (cellOrderNumber == null) {
            cellOrderNumber = row.createCell(0);
        }

        XSSFCell cellOrderDate = row.getCell(1);
        if (cellOrderDate == null) {
            cellOrderDate = row.createCell(1);
        }
        XSSFCell cellOrderType = row.getCell(2);
        if (cellOrderType == null) {
            cellOrderType = row.createCell(2);
        }
        XSSFCell cellOrderStatus = row.getCell(3);
        if (cellOrderStatus == null) {
            cellOrderStatus = row.createCell(3);
        }
        XSSFCell cellETN = row.getCell(4);
        if (cellETN == null) {
            cellETN = row.createCell(4);
        }
        XSSFCell cellShop = row.getCell(5);
        if (cellShop == null) {
            cellShop = row.createCell(5);
        }
        XSSFCell cellUserId = row.getCell(6);
        if (cellUserId == null) {
            cellUserId = row.createCell(6);
        }
        XSSFCell cellUserName = row.getCell(7);
        if (cellUserName == null) {
            cellUserName = row.createCell(7);
        }
        XSSFCell cellPaymentTerm = row.getCell(8);
        if (cellPaymentTerm == null) {
            cellPaymentTerm = row.createCell(8);
        }
        XSSFCell cellOrderAmount = row.getCell(9);
        if (cellOrderAmount == null) {
            cellOrderAmount = row.createCell(9);
        }
        XSSFCell cellCouponAmount = row.getCell(10);
        if (cellCouponAmount == null) {
            cellCouponAmount = row.createCell(10);
        }
        cellOrderNumber.setCellValue(co.getOrderNumber());
        if (co.getOrderDate() != null) {
            cellOrderDate.setCellValue(dateFormat.format(co.getOrderDate().getTime()));
        } else {
            cellOrderDate.setCellValue(dateFormat.format(co.getOrderTime().getTime()));
        }

        cellOrderType.setCellValue(co.getOrderType().toString());
        cellOrderStatus.setCellValue(co.getOrderStatus().toString());
        if (co.getExternalTransactionNumber() != null) {
            cellETN.setCellValue(co.getExternalTransactionNumber());
        }

        cellShop.setCellValue(co.getShopName());
        cellUserId.setCellValue(co.getUserId());
        cellUserName.setCellValue(co.getUserName());
        cellPaymentTerm.setCellValue(co.getPaymentTerm().toString());
        cellOrderAmount.setCellValue(co.getOrderAmount());
        cellCouponAmount.setCellValue(co.getCouponAmount());
        startRow++;
        System.out.println(startRow + " orders in the report");
    }
    DateFormat dateTimeFormat = new SimpleDateFormat("yyyy-MM-dd-HHMMSS");
    String reportName = "CustomerOrderReport" + dateTimeFormat.format(new Date()) + ".xlsx";
    String reportPath = deployPath + File.separator + reportName;
    FileOutputStream fos = new FileOutputStream(reportPath);
    try (BufferedOutputStream bout = new BufferedOutputStream(fos)) {
        workbook.write(bout);
        bout.flush();
    }
    return new CustomerOrderReportResult(reportName);
}

From source file:co.com.runt.runistac.logica.ReporteLogica.java

public static byte[] generar(InputStream plantilla, Map<String, String> parametros, List<Object[]> datos)
        throws Exception {
    XSSFWorkbook wb = new XSSFWorkbook(plantilla);
    XSSFSheet mySheet = wb.getSheetAt(0);
    for (int i = 0; i < mySheet.getLastRowNum(); i++) {
        Row row = mySheet.getRow(i);/*  w w w.  ja va2s. com*/
        if (row != null && row.getCell(0) != null && row.getCell(0).getCellTypeEnum() == CellType.STRING) {
            for (String key : parametros.keySet()) {
                String valor = row.getCell(0).getStringCellValue();
                valor = valor.replaceAll("\\{" + key + "\\}", parametros.get(key));
                row.getCell(0).setCellValue(valor);
            }
        }
    }

    int rows = mySheet.getLastRowNum();
    int i = 0;
    Row base = mySheet.getRow(rows);
    CellStyle[] cs = null;
    if (!datos.isEmpty()) {
        int cant = datos.get(0).length;
        cs = new CellStyle[cant];
        for (int j = 0; j < cant; j++) {
            cs[j] = base.getCell(j).getCellStyle();
        }
    }
    for (Object[] o : datos) {
        Row row = mySheet.createRow(rows + i);
        for (int j = 0; j < o.length; j++) {
            Cell c = row.createCell(j);
            String value = "";
            if (o[j] != null) {
                if (o[j] instanceof String) {
                    value = (String) o[j];
                    c.setCellValue(value);
                } else if (o[j] instanceof Integer) {//integer
                    c.setCellValue((Integer) o[j]);
                } else if (o[j] instanceof Double) {
                    c.setCellValue((Double) o[j]);
                } else if (o[j] instanceof Float) {
                    c.setCellValue((Float) o[j]);
                } else if (o[j] instanceof BigDecimal) {
                    c.setCellValue(((BigDecimal) o[j]).doubleValue());
                } else if (o[j] instanceof Date) {
                    c.setCellValue(((Date) o[j]));
                } else if (o[j] instanceof BigInteger) {
                    c.setCellValue(((BigInteger) o[j]).intValue());
                } else {
                    c.setCellValue(o[j].toString());
                    System.out.println("No se encontro tipo: " + j + "-" + o[j].getClass());
                }
            }
            c.setCellStyle(cs[j]);
        }
        i++;
    }
    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    wb.write(baos);

    return baos.toByteArray();
}

From source file:CODIGOS.Planilha.java

public static void lerPlanilha(String arquivo) {

    FileInputStream fisPlanilha = null;

    try {//from w ww  . ja  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.accenture.bean.PlanoExcel.java

public void extraiPlanilha() {
    try {/*from   w w w  .  j  av  a2 s. c  om*/
        //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  w w  . ja  v a  2 s.c  om
        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   w  ww .  j a v  a  2s. com
        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 List<CasoTesteTemp> getCTExistentes(String planilha) throws FileNotFoundException, IOException {
    List<CasoTesteTemp> listCT = new ArrayList<CasoTesteTemp>();

    //criando variavel que recebe a planilha  selecionada
    FileInputStream arquivo = new FileInputStream(new File(planilha));
    XSSFWorkbook workbook = new XSSFWorkbook(arquivo);
    XSSFSheet sheetPlano = workbook.getSheetAt(1);

    //nmero da linha que deve comear a leitura da clula -- primeira linha = 0
    int linha = 7;
    //criando variavel row que receber o numero da da linha
    Row row = sheetPlano.getRow(linha);// w ww  .j  av  a  2  s  .  com
    //Criando varialvel do tipo Cell onde recebe a variavel row 
    Cell celCasoTeste = row.getCell(12);
    //variavel para identificar a posicao da lista onde sera inserido o objeto
    int indice = 0;
    int countAdd = 0;

    //loop onde  verificado se a celula do CT est vazia, caso nao sera armazenada
    while (!celCasoTeste.getStringCellValue().equals("")) {
        //instacia um objeto CasoTesteTemp 
        CasoTesteTemp ct = new CasoTesteTemp();
        //atribui valor da celula para o objeto ct 
        ct.setCasoTeste(celCasoTeste.getStringCellValue());
        //numero da celula recebe valor da linha
        int numeroLinhaCel = linha;
        //atribui valor recbe referencia da celula
        ct.setCelula(numeroLinhaCel);
        //lista de CT recebe novo CT
        if (!testCaseAlreadyExists(listCT, ct)) {
            listCT.add(countAdd, ct);
            countAdd++;
        }

        //linha recebe mais 25 para ir para proximo CT da planilha
        linha = 25 + linha;
        //variavel row recebe nova linha
        row = sheetPlano.getRow(linha);
        //variavel cell recebe nova linha
        celCasoTeste = row.getCell(12);

        //incrementa 1 na variavel 
        indice++;

    }

    return listCT;
}

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  a v a2 s .c om*/

    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   w  w  w. ja v a  2s  .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;
}

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

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

    String msg;//from   w  w  w . ja  va 2s . com

    Plano p = new Plano();

    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());
    p.setCasoTeste(nomeCT);
    p = bdLite.getPorCasoTeste(p);

    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.updatePlano(p);
    celStep = row.getCell(16);
    int linhaStep = linha;
    linha = 25 + linha;
    row = sheetCTs.getRow(linha);
    celCasoTeste = row.getCell(12);

    System.out.println("ID" + p.getId());
    bdLite.deleteStep(p);

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