Example usage for org.apache.poi.ss.usermodel Row getRowNum

List of usage examples for org.apache.poi.ss.usermodel Row getRowNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. getRowNum

Introduction

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

Prototype

int getRowNum();

Source Link

Document

Get row number this row represents

Usage

From source file:bad.robot.excel.PoiToExcelCoercions.java

License:Apache License

public static int asExcelRow(Row row) {
    return row.getRowNum() + 1;
}

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

License:Apache License

private static void copyMergeRegion(Sheet worksheet, Row sourceRow, Row newRow, CellRangeAddress mergedRegion) {
    CellRangeAddress range = mergedRegion;
    if (range.getFirstRow() == sourceRow.getRowNum()) {
        int lastRow = newRow.getRowNum() + (range.getFirstRow() - range.getLastRow());
        CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), lastRow,
                range.getFirstColumn(), range.getLastColumn());
        worksheet.addMergedRegion(newCellRangeAddress);
    }/*from  w  w w  .jav a  2s  .  c o  m*/
}

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

private void leesExcel() {
    try {/*  w  w w .j  a v a 2 s .  co 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();
    }
}

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

private void leesExcel(String path) {
    try {//w ww . j  a  v a  2  s. c  o  m

        //declaratie en blad uit excel selecteren enzo
        FileInputStream fileInputStream = new FileInputStream(path);
        XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
        XSSFSheet worksheet = workbook.getSheet("Blad1");

        //            XSSFRow row1 = worksheet.getRow(0);
        //            XSSFCell cellA1 = row1.getCell((short) 0);
        //            String a1Val = cellA1.getStringCellValue();
        //            XSSFCell cellB1 = row1.getCell((short) 1);
        //            String b1Val = cellB1.getStringCellValue();
        //
        //            XSSFRow row2 = worksheet.getRow(1);
        //            XSSFCell cellA2 = row2.getCell((short) 0);
        //            String a2Val = cellA2.getStringCellValue();
        //            XSSFCell cellB2 = row2.getCell((short) 1);
        //            String b2Val = cellB2.getStringCellValue();
        //
        //            XSSFRow row7 = worksheet.getRow(6);
        //            int a7Val = (int) row7.getCell((short) 0).getNumericCellValue();
        //            String b7Val = row7.getCell((short) 1).getStringCellValue();
        //            int c7Val = (int) row7.getCell((short) 2).getNumericCellValue();
        //
        //            XSSFRow row8 = worksheet.getRow(7);
        //            int a8Val = (int) row8.getCell((short) 0).getNumericCellValue();
        //            String b8Val = row8.getCell((short) 1).getStringCellValue();
        //            int c8Val = (int) row8.getCell((short) 2).getNumericCellValue();
        //
        //            XSSFRow row9 = worksheet.getRow(8);
        //            int a9Val = (int) row9.getCell((short) 0).getNumericCellValue();
        //            String b9Val = row9.getCell((short) 1).getStringCellValue();
        //            int c9Val = (int) row9.getCell((short) 2).getNumericCellValue();
        //            System.out.println("A1: " + a1Val);
        //            System.out.println("B1: " + b1Val);
        //            System.out.println("A2: " + a2Val);
        //            System.out.println("B2: " + b2Val);
        //            System.out.println("Studentnr - naam - score");
        //            System.out.println(a7Val + " " + b7Val + " " + c7Val);
        //            System.out.println(a8Val + " " + b8Val + " " + c8Val);
        //            System.out.println(a9Val + " " + b9Val + " " + c9Val);
        //iterator dat door alle rijen gaat van het excel-blad
        Iterator<Row> rowIterator = worksheet.iterator();

        Test test = new Test(); //test aanmaken
        String klasNaam = "";
        Long klasId = 0L;

        while (rowIterator.hasNext()) { //als er nog een rij bestaat die niet leeg is
            Row row = rowIterator.next(); //row-object aanmaken van huidige rij

            if (row.getRowNum() == 0) { //als de nummer van de rij = 0 (dus de 0de rij van het excel bestand = klas)
                Iterator<Cell> cellIterator = row.cellIterator(); //voor deze rij elke cel in deze rij afgaan met een iterator

                while (cellIterator.hasNext()) { //als er nog een cell bestaat die niet leeg is
                    Cell cell = cellIterator.next(); //cell-object aanmaken van huidige cell

                    if (!cell.getStringCellValue().equals("klas")) { //als er het woord "klas" in de cell staat, deze overslaan. Als de cel van de 0de rij (klas-rij) iets anders is dan "klas" dus (=A1 in excel)
                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_STRING: //als het type van de cel een string is
                            Klas klas = new Klas(); // klas-object aanmaken
                            klasNaam = cell.getStringCellValue();
                            klas.setNaam(cell.getStringCellValue()); //naam van klas instellen op de waarde van de cell

                            List<Klas> alleKlassen = javaProject7Service.getAllKlassen();

                            boolean bestaatAl = false;

                            for (Klas alleKlas : alleKlassen) {
                                if (alleKlas.getNaam().equals(klasNaam)) {
                                    bestaatAl = true;
                                }
                            }

                            if (bestaatAl) {
                                klasId = javaProject7Service.addKlas(klas);
                            }
                            break;
                        }
                    }
                }
            }

            //volgende if is hetzelfde principe als vorige enkel voor een andere rij
            if (row.getRowNum() == 1) { //nummer van de rij = 1 (dus eigenlijk in excel de 2de rij)
                Iterator<Cell> cellIterator = row.cellIterator();

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

                    if (!cell.getStringCellValue().equals("Vak")) { //als er het woord "Vak" in de cell staat, deze overslaan
                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            //hier moet nog code komen om het vak toe te voegen aan het Test-object (zie regel 196)
                            break;
                        }
                    }
                }
            }

            //weer hetzelfde principe als hierboven
            if (row.getRowNum() > 5) { // enkel voor de rijen 5 en hoger (dus enkel studenten)
                Iterator<Cell> cellIterator = row.cellIterator();

                Student student = new Student(); //nieuw student-object aanmaken per rij

                while (cellIterator.hasNext()) {

                    Cell cell = cellIterator.next();

                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC: //als de cell een int is
                        student.setStudentnr((int) cell.getNumericCellValue());
                        //Klas klas = javaProject7Service.getKlasByNaam(klasNaam); //klas ophalen uit db adhv klasnaam
                        student.setKlasId(klasId);
                        break;
                    case Cell.CELL_TYPE_STRING: //als de cell een string is
                        if (cell.getStringCellValue().equals("zit al in de DB")
                                || cell.getStringCellValue() != null || cell.getStringCellValue().equals("")) { //als de cell "zit al in de DB" bevat, niets doen (zie excel; laatste regel)
                            break;
                        } else {
                            String volledigeNaam = cell.getStringCellValue();
                            String[] delen = volledigeNaam.split(" ");
                            student.setVoornaam(delen[0]);
                            student.setAchternaam(delen[1]);
                            break;
                        }
                    }
                    javaProject7Service.addStudent(student); //student toevoegen aan studenten list
                }
            }
        }

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

From source file:br.com.algoritmo.compilacao.CompilaXlsx.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;/*  ww w .jav  a2s  .com*/
    Map<Integer, Object[]> data = new TreeMap<Integer, Object[]>();
    data.put(0, new Object[] { 0, "Luiz Carlos Miyadaira Ribeiro Junior", "Base", "0468265522433921",
            "SOFTWARE", null, null, null });
    data.put(1, new Object[] { 1, "Sergio Antnio Andrade de Freitas", "Destino 1", "0395549254894676",
            "SOFTWARE", null, null, null });
    data.put(2, new Object[] { 2, "Andre Luiz Aquere de Cerqueira e Souza", "Destino 2", "8424412648258970",
            "CIVIL", null, null, null });
    data.put(3, new Object[] { 3, "Edson Mintsu Hung Destino", "Destino 3", "6753551743147880", "ELETRNICA",
            null, null, null });
    data.put(4, new Object[] { 4, "Edgard Costa Oliveira", "Destino 4", "1196380808351110", "SOFTWARE", null,
            null, null });
    data.put(5, new Object[] { 5, "Edson Alves da Costa Jnior", "Destino 5", "2105379147123450", "SOFTWARE",
            null, null, null });
    data.put(6, new Object[] { 6, "Andr Barros de Sales", "Destino 6", "7610669796869660", "SOFTWARE", null,
            null, null });
    data.put(7, new Object[] { 7, "Giovanni Almeida dos Santos", "Destino 7", "0580891429319047", "SOFTWARE",
            null, null, null });
    data.put(8, new Object[] { 8, "Cristiane Soares Ramos", "Destino 8", "9950213660160160", "SOFTWARE", null,
            null, null });
    data.put(9, new Object[] { 9, "Fabricio Ataides Braz", "Destino 9", "1700216932505000", "SOFTWARE", null,
            null, null });
    data.put(10, new Object[] { 10, "Alexandre Srgio de Arajo Bezerra", "Destino 10", "0255998976169051",
            "MEDICINA", null, null, null });
    data.put(11, new Object[] { 11, "Eduardo Stockler Tognetti", "Destino 11", "2443108673822680", "ELTRICA",
            null, null, null });
    data.put(12, new Object[] { 12, "Jan Mendona Correa", "Destino 12", "7844006017790570",
            "CINCIA DA COMPUTAO", null, null, null });
    data.put(13, new Object[] { 13, "Rejane Maria da Costa Figueiredo", "Destino 13", "2187680174312042",
            "SOFTWARE", null, null, null });
    data.put(14, new Object[] { 14, "Augusto Csar de Mendona Brasil", "Destino 14", "0571960641751286",
            "ENERGIA", null, null, null });
    data.put(15, new Object[] { 15, "Fbio Macdo Mendes", "Destino 15", "8075435338067780", "F?SICA", null,
            null, null });

    if (args.length > 0 && args[0].equals("-xls"))
        wb = new HSSFWorkbook();
    else
        wb = new XSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);

    Sheet aba1 = wb.createSheet("Percentual de similaridade 1");
    PrintSetup printSetup = aba1.getPrintSetup();
    printSetup.setLandscape(true);
    aba1.setFitToPage(true);
    aba1.setHorizontallyCenter(true);

    Sheet aba2 = wb.createSheet("Percentual de similaridade 2");
    PrintSetup printSetup2 = aba2.getPrintSetup();
    printSetup2.setLandscape(true);
    aba1.setFitToPage(true);
    aba1.setHorizontallyCenter(true);

    //title row
    Row titleRow = aba1.createRow(0);
    titleRow.setHeightInPoints(15);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue(
            "Resultado da aplicao do algoritmo de clculo do percentual de similaridade entre os indivduos");
    titleCell.setCellStyle(styles.get("title"));
    aba1.addMergedRegion(CellRangeAddress.valueOf("$A$1:$H$1"));

    //header row
    Row headerRow = aba1.createRow(1);
    headerRow.setHeightInPoints(15);
    Cell headerCell;
    for (int i = 1; i <= titles.length; i++) {
        headerCell = headerRow.createCell(i);
        headerCell.setCellValue(titles[i - 1]);
        headerCell.setCellStyle(styles.get("header"));
    }

    Row headerBase = aba1.createRow(2);
    headerBase.setHeightInPoints(15);
    Cell headerCellBase;
    for (int i = 1; i <= base.length; i++) {
        headerCellBase = headerBase.createCell(i);
        headerCellBase.setCellValue(base[i - 1]);
        headerCellBase.setCellStyle(styles.get("header1"));
    }

    Row headerDestino = aba1.createRow(4);
    headerDestino.setHeightInPoints(15);
    Cell headerCellDestino;
    for (int i = 1; i <= destino.length; i++) {
        headerCellDestino = headerDestino.createCell(i);
        headerCellDestino.setCellValue(destino[i - 1]);
        headerCellDestino.setCellStyle(styles.get("header1"));
    }

    /*int rownum = 2;
    for (int i = 0; i < 10; i++) {
        Row row = sheet.createRow(rownum++);
        for (int j = 0; j < titles.length; j++) {
     Cell cell = row.createCell(j);
     if(j == 9){
         //the 10th cell contains sum over week days, e.g. SUM(C3:I3)
         String ref = "C" +rownum+ ":I" + rownum;
         cell.setCellFormula("SUM("+ref+")");
         cell.setCellStyle(styles.get("formula"));
     } else if (j == 11){
         cell.setCellFormula("J" +rownum+ "-K" + rownum);
         cell.setCellStyle(styles.get("formula"));
     } else {
         cell.setCellStyle(styles.get("cell"));
     }
        }
    }
            
    rownum = 3;
    for (int i = 0; i < 10; i++) {
        Row row = sheet.createRow(rownum++);
        for (int j = 0; j < titles1.length; j++) {
     Cell cell = row.createCell(j);
     if(j == 9){
         //the 10th cell contains sum over week days, e.g. SUM(C3:I3)
         String ref = "C" +rownum+ ":I" + rownum;
         cell.setCellFormula("SUM("+ref+")");
         cell.setCellStyle(styles.get("formula"));
     } else if (j == 11){
         cell.setCellFormula("J" +rownum+ "-K" + rownum);
         cell.setCellStyle(styles.get("formula"));
     } else {
         cell.setCellStyle(styles.get("cell"));
     }
        }
    }
    */
    //set sample data
    //Iterate over data and write to sheet
    Set<Integer> keyset = data.keySet();
    int rownum = 0;
    for (Integer key : keyset) {
        Row row = aba1.createRow(3 + rownum++);
        Object[] objArr = data.get(key);
        int cellnum = 0;
        for (Object obj : objArr) {
            Cell cell = row.createCell(cellnum++);
            if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Integer)
                cell.setCellValue((Integer) obj);
        }
        if (row.getRowNum() == 3) {
            rownum++;
        }
    }
    //finally set column widths, the width is measured in units of 1/256th of a character width
    aba1.setColumnWidth(0, 2 * 256); //2 characters wide
    aba1.setColumnWidth(1, 26 * 256); //26 characters wide
    aba1.setColumnWidth(2, 20 * 256); //20 characters wide
    aba1.setColumnWidth(3, 18 * 256); //18 characters wide
    aba1.setColumnWidth(4, 20 * 256); //20 characters wide
    for (int i = 5; i < 9; i++) {
        aba1.setColumnWidth(i, 15 * 256); //6 characters wide
    }

    // Write the output to a file
    String file = "Sada/Percentual de similaridade.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}

From source file:br.com.bb.intranet.supermt.pf.desembolso.service.ImportaDados.java

/**
 *
 * @param planilha objeto do tipo Planilha que contm todos os dados
 * necessrios para facilitar a operao na planilha
 *//*from   w ww .ja  va 2 s . c  o m*/
public void processaPlanilha(Planilha planilha) throws NegocioException {
    List<Desembolso> dadosParaSalvar = new ArrayList();
    int linha = 0;
    Double d;
    try {

        //cria um workbook = planilha toda com todas as abas
        XSSFWorkbook workbook = new XSSFWorkbook(planilha.getFile());

        //recuperamos apenas a aba mandada
        XSSFSheet sheet = workbook.getSheetAt(planilha.getNumeroDaPlanilha() - 1);

        //retorna todas as linhas da planilha selecioada 
        Iterator<Row> rowIterator = sheet.iterator();

        //varre todas as linhas da planilha selecionada
        while (rowIterator.hasNext() && linha < planilha.getUltimaLinha()) {
            linha++;

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

            //andar as linhas que sero ignoradas no incio
            if (row.getRowNum() < planilha.getUltimaLinha()) {
                continue;
            }

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

            //responsavel por definir qual coluna esta sendo trabalhada no intante
            int coluna = 1;

            Cell cell;

            Desembolso desembolso = new Desembolso();
            //varremos todas as celulas da linha atual
            while (cellIterator.hasNext()) {
                //criamos uma celula
                cell = cellIterator.next();

                //TODO O CDIGO DE PERSISTENCIA AQUI!!

                switch (coluna) {
                case ColunasDesembolso.GRUPO:
                    desembolso.setGrupo(cell.getStringCellValue());
                    break;
                case ColunasDesembolso.PREFIXO_SUPER_INTENDENCIA:
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    desembolso.setPrefixoSuperintendencia(cell.getStringCellValue());
                    break;
                case ColunasDesembolso.NOME_SUPER_INTENDENCIA:
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    desembolso.setNomeSuperintendencia(cell.getStringCellValue());
                    break;
                case ColunasDesembolso.PREFIXO_REGIONAL:
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    desembolso.setPrefixoRegional(cell.getStringCellValue());
                    break;
                case ColunasDesembolso.NOME_AGENCIA:
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    desembolso.setNomeAgencia(cell.getStringCellValue());
                    break;
                case ColunasDesembolso.ORCAMENTO_PROPOSTO_ACUMULADO:
                    desembolso
                            .setOrcamentoPropostoAcumulado(this.doubleToBigDecimal(cell.getNumericCellValue()));
                    break;
                case ColunasDesembolso.REALIZADO_ATUAL:
                    desembolso.setRealizadoAtual(this.doubleToBigDecimal(cell.getNumericCellValue()));
                    break;
                case ColunasDesembolso.PERCENTUAL_ATINGIMENTO_UM:
                    desembolso.setPercentualAtingimentoUm(this.doubleToBigDecimal(cell.getNumericCellValue()));
                    break;
                case ColunasDesembolso.REALIZADO_D_MENOS_UM:
                    desembolso.setRealizadoDmenosUm(this.doubleToBigDecimal(cell.getNumericCellValue()));
                    break;
                case ColunasDesembolso.NECESSIDADE_DIA_MENOS_UM:
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    desembolso.setNecessidadeDiaDmenosUm(this.doubleToBigDecimal(cell.getNumericCellValue()));
                    break;
                case ColunasDesembolso.META_CONTATOS_ACUMULADA:
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    desembolso.setMetaContatosAcumulada(this.doubleToBigDecimal(cell.getNumericCellValue()));
                    break;
                case ColunasDesembolso.REALIZADO_CONTATOS_MES:
                    cell.setCellType(cell.CELL_TYPE_NUMERIC);
                    desembolso.setRealizadoContatosMes(this.doubleToBigDecimal(cell.getNumericCellValue()));
                    break;
                case ColunasDesembolso.PERCENTUAL_ATINGIMENTO_CONTATOS:
                    cell.setCellType(cell.CELL_TYPE_NUMERIC);
                    desembolso.setPercentualAtingimentoContatos(
                            this.doubleToBigDecimal(cell.getNumericCellValue()));
                    break;
                case ColunasDesembolso.CONTATOS_D_MENOS_UM:
                    cell.setCellType(cell.CELL_TYPE_NUMERIC);
                    desembolso.setContatosDmenosUm(this.doubleToBigDecimal(cell.getNumericCellValue()));
                    break;
                case ColunasDesembolso.CONTATOS_D_MENOS_DOIS:
                    cell.setCellType(cell.CELL_TYPE_NUMERIC);
                    desembolso.setContatosDmenosDois(this.doubleToBigDecimal(cell.getNumericCellValue()));
                    break;
                case ColunasDesembolso.PREFIXO_REPETE:
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    desembolso.setPrefixoRepete(cell.getStringCellValue());
                    break;
                case ColunasDesembolso.AGENCIA_REPETE:
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    desembolso.setAgenciaRepete(cell.getStringCellValue());
                    break;
                case ColunasDesembolso.CODIDGO_CARTEIRA:
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    desembolso.setCodigoCarteira(cell.getStringCellValue());
                    break;
                case ColunasDesembolso.CARTEIRA:
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    desembolso.setCarteira(cell.getStringCellValue());
                    break;
                case ColunasDesembolso.ORCAMENTO_PROPORCIONAL_ACUMULADO_DOIS:
                    cell.setCellType(cell.CELL_TYPE_NUMERIC);
                    desembolso.setOrcamentoProporcionalAcumuladoDois(
                            this.doubleToBigDecimal(cell.getNumericCellValue()));
                    break;
                case ColunasDesembolso.REALIZADO_ATUAL_DOIS:
                    cell.setCellType(cell.CELL_TYPE_NUMERIC);
                    desembolso.setRealizadoAtualDois(this.doubleToBigDecimal(cell.getNumericCellValue()));
                    break;
                case ColunasDesembolso.PERCENTUAL_ATINGIMENTO_DOIS:
                    cell.setCellType(cell.CELL_TYPE_NUMERIC);
                    desembolso
                            .setPercentualAgintimentoDois(this.doubleToBigDecimal(cell.getNumericCellValue()));
                    break;
                case ColunasDesembolso.META_CONTATOS_ACUMULADA_DOIS:
                    cell.setCellType(cell.CELL_TYPE_NUMERIC);
                    desembolso
                            .setMetaContatosAcumuladaDois(this.doubleToBigDecimal(cell.getNumericCellValue()));
                    break;
                case ColunasDesembolso.REALIZADO_CONTATOS_MES_DOIS:
                    cell.setCellType(cell.CELL_TYPE_NUMERIC);
                    desembolso.setRealizadoContatosMesDois(this.doubleToBigDecimal(cell.getNumericCellValue()));
                    break;
                case ColunasDesembolso.PERCENTUAL_ATINGIMENTO_CONTATOS_DOIS:
                    cell.setCellType(cell.CELL_TYPE_NUMERIC);
                    desembolso.setPercentualAtingimentoContatosDois(
                            this.doubleToBigDecimal(cell.getNumericCellValue()));
                    break;
                }

                System.out.println("valor = " + cell.toString());

                coluna++;
            }
            dadosParaSalvar.add(desembolso);
        }
        this.salvar(dadosParaSalvar);

    } catch (FileNotFoundException ex) {
        throw new NegocioException("Arquivo com Erro Tente novamente!!");
    } catch (IOException ex) {
        throw new NegocioException("Arquivo com Erro Tente novamente!!");
    }
}

From source file:br.com.gartech.nfse.integrador.view.ImportarView.java

public List<FaturamentoImport> readFaturamentoExcelFile(String excelFilePath) throws IOException {
    List<FaturamentoImport> listaFaturamento = new ArrayList<>();
    FileInputStream inputStream = new FileInputStream(new File(excelFilePath));

    Workbook workbook = new XSSFWorkbook(inputStream);
    Sheet firstSheet = workbook.getSheetAt(0);
    Iterator<Row> iterator = firstSheet.iterator();

    while (iterator.hasNext()) {
        Row nextRow = iterator.next();
        Iterator<Cell> cellIterator = nextRow.cellIterator();
        FaturamentoImport aFaturamentoImport = new FaturamentoImport();

        while (cellIterator.hasNext()) {
            Cell nextCell = cellIterator.next();
            int columnIndex = nextCell.getColumnIndex();

            switch (columnIndex) {
            case 0:
                if (nextRow.getRowNum() == 0) {
                    if (!getCellValue(nextCell).equals("CAMPUS")) {

                    }/*w w w . ja  v  a  2 s.  c  o m*/
                } else {
                    aFaturamentoImport.setCampus((String) getCellValue(nextCell));
                }
                break;
            case 1:
                aFaturamentoImport.setCampus((String) getCellValue(nextCell));
                break;
            case 2:
                aFaturamentoImport.setNumero((String) getCellValue(nextCell));
                break;
            //                    case 3:
            //                        aFaturamentoImport.setTipo_fatura((String) getCellValue(nextCell));
            //                        break;                        
            //                    case 4:
            //                        aFaturamentoImport.setAnocompetencia((Integer) getCellValue(nextCell));
            //                        break;                        
            //                    case 5:
            //                        aFaturamentoImport.setMescompetencia((int) getCellValue(nextCell));
            //                        break;                        
            //                    case 6:
            //                        aFaturamentoImport.setCodaluno((int) getCellValue(nextCell));
            //                        break;                        
            //                    case 7:
            //                        aFaturamentoImport.setAlunome((String) getCellValue(nextCell));
            //                        break;                        
            //                    case 8:
            //                        aFaturamentoImport.setCodcurso((int) getCellValue(nextCell));
            //                        break;                        
            //                    case 9:
            //                        aFaturamentoImport.setCurso((String) getCellValue(nextCell));
            //                        break;                      
            //                    case 10:
            //                        aFaturamentoImport.setTipocurso((String) getCellValue(nextCell));
            //                        break;                        
            //                    case 11:
            //                        aFaturamentoImport.setAluendereco((String) getCellValue(nextCell));
            //                        break;                        
            //                    case 12:
            //                        aFaturamentoImport.setAlubairro((String) getCellValue(nextCell));
            //                        break;                        
            //                    case 13:
            //                        aFaturamentoImport.setAlucep((String) getCellValue(nextCell));
            //                        break;                       
            //                    case 14:
            //                        aFaturamentoImport.setCiddesc((String) getCellValue(nextCell));
            //                        break;                        
            //                    case 15:
            //                        aFaturamentoImport.setCiduf((String) getCellValue(nextCell));
            //                        break;                      
            //                    case 16:
            //                        aFaturamentoImport.setAlutelefone((String) getCellValue(nextCell));
            //                        break;                        
            //                    case 17:
            //                        aFaturamentoImport.setAlutelefone2((String) getCellValue(nextCell));
            //                        break;                        
            //                    case 18:
            //                        aFaturamentoImport.setAluemail((String) getCellValue(nextCell));
            //                        break;                        
            //                    case 19:
            //                        aFaturamentoImport.setAlucpf((String) getCellValue(nextCell));
            //                        break;                       
            case 20:
                aFaturamentoImport.setValor((float) getCellValue(nextCell));
                break;
            }
        }
        listaFaturamento.add(aFaturamentoImport);
    }
    //workbook.close;
    inputStream.close();

    return listaFaturamento;
}

From source file:br.sp.telesul.service.ExportServiceImpl.java

@Override
public List<Funcionario> readExcelDocument() {
    try {//  w w  w. j av a 2 s. c  o  m
        List<Funcionario> funcionariosExcel = new ArrayList<>();
        ClassLoader classLoader = Thread.currentThread().getContextClassLoader();
        //            FileInputStream fl = new FileInputStream(new File("C:\\Matriz1.xlsx"));
        Workbook wb = new XSSFWorkbook(classLoader.getResourceAsStream("Matriz1.xlsx"));
        Sheet firstSheet = wb.getSheetAt(0);
        Iterator<Row> iterator = firstSheet.iterator();

        while (iterator.hasNext()) {
            Row nextRow = iterator.next();
            int row = nextRow.getRowNum();
            //                System.out.println("Row start" + row);
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            Funcionario f = new Funcionario();
            Formacao fm = new Formacao();
            Idioma id = new Idioma();
            int column = 0;
            while (cellIterator.hasNext()) {
                Cell nextCell = cellIterator.next();
                int columnIndex = nextCell.getColumnIndex();
                column = columnIndex;
                //                    System.out.println("Valor" + getCellValue(nextCell));
                //                    System.out.println("Index: " + columnIndex);
                if (row > 0) {
                    switch (columnIndex) {
                    case 1:
                        f.setArea((String) getCellValue(nextCell));
                        break;
                    case 2:
                        Date dt = new Date();
                        if (!getCellValue(nextCell).toString().isEmpty()) {
                            try {
                                dt = DateUtil.getJavaDate((Double) getCellValue(nextCell));
                            } catch (ClassCastException cce) {
                                SimpleDateFormat formatter = new SimpleDateFormat("dd-MMM-yyyy");

                                dt = formatter.parse((String) getCellValue((nextCell)));
                            }
                            ;
                        }

                        f.setDtAdmissao(dt);
                        break;
                    case 3:
                        f.setCargo((String) getCellValue(nextCell));
                        break;
                    case 4:
                        f.setNome((String) getCellValue(nextCell));
                        break;
                    case 5:
                        f.setGestor((String) getCellValue(nextCell));
                        break;
                    case 9:
                        fm.setNivel((String) getCellValue(nextCell));
                        break;
                    case 10:
                        fm.setCurso((String) getCellValue(nextCell));
                        break;
                    case 11:
                        fm.setInstituicaoo((String) getCellValue(nextCell));
                        break;
                    case 12:
                        String typeEnum = (String) getCellValue(nextCell);
                        if (!typeEnum.isEmpty()) {
                            id.setNome(Language.valueOf(typeEnum.trim()));
                        }

                        break;
                    case 13:
                        String typeEnumNivel = (String) getCellValue(nextCell);
                        if (!typeEnumNivel.isEmpty()) {
                            id.setNivel(Nivel.valueOf(typeEnumNivel.trim()));
                        }

                        break;
                    }
                }

            }

            List<Formacao> listFm = new ArrayList<>();
            listFm.add(fm);
            f.setFormacoes(listFm);

            List<Idioma> listId = new ArrayList<>();
            listId.add(id);
            f.setIdiomas(listId);

            if (row > 0) {
                funcionariosExcel.add(f);
            }

        }
        wb.close();
        //            fl.close();
        //            for (Funcionario fc : funcionariosExcel) {
        //                System.out.println(fc.getNome());
        //            }
        return funcionariosExcel;
    } catch (Exception e) {
        e.printStackTrace();
        return null;
    }

}

From source file:business.SongExcelParser.java

private ArrayList<SongContainer> sheetToSongFiles(Sheet sheet) {
    SongFile songFile;/*from  w ww  .jav  a2s  .  c  o m*/
    Row row;
    ArrayList<SongContainer> songFiles = new ArrayList<>();
    Iterator<Row> rows = sheet.rowIterator();
    while (rows.hasNext()) {
        row = rows.next();
        if (row.getRowNum() != 0) {
            songFile = getSongFileFromRow(row);
            if (songFile != null && songFile.isValid()) {
                songFiles.add(songFile);
            }
        }
    }
    return songFiles;
}

From source file:business.SongExcelParser.java

private ArrayList<Song> sheetToCDSongs(Sheet sheet) {
    Song song;//from  w w w .j  av a 2 s  . co  m
    Row row;
    ArrayList<Song> songs = new ArrayList<>();
    Iterator<Row> rows = sheet.rowIterator();
    while (rows.hasNext()) {
        row = rows.next();
        if (row.getRowNum() != 0) {
            song = getSongFromRow(row);
            if (song != null && song.isValid()) {
                songs.add(song);
            }
        }
    }
    return songs;
}