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

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

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

Introduction

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

Prototype

Iterator<Cell> cellIterator();

Source Link

Usage

From source file:com.jkoolcloud.tnt4j.streams.inputs.AbstractExcelStream.java

License:Apache License

/**
 * Calculates {@link Row} contained data bytes count.
 *
 * @param row//ww  w.jav  a2s.co m
 *            the row
 *
 * @return the row data bytes count
 */
static int getRowBytesCount(Row row) {
    int bCount = 0;

    if (row != null) {
        Iterator<Cell> cells = row.cellIterator();
        while (cells.hasNext()) {
            Cell c = cells.next();
            if (c != null) {
                String cv = c.toString();
                bCount += cv.getBytes().length;
            }
        }
    }

    return bCount;
}

From source file:com.jkoolcloud.tnt4j.streams.inputs.ExcelSXSSFRowStream.java

License:Apache License

private static long getRowByteSize(Row activityItem) {
    Iterator<Cell> cells = activityItem.cellIterator();
    if (cells == null) {
        return 0;
    }//from  w ww  . j  a  v  a 2  s .c  o  m

    long rowSize = 0;
    while (cells.hasNext()) {
        Cell cell = cells.next();
        rowSize += cell.toString().getBytes().length;
    }

    return rowSize;
}

From source file:com.jkoolcloud.tnt4j.streams.inputs.ExcelSXSSFRowStreamTest.java

License:Apache License

public static void printRow(Row row) {
    Iterator<Cell> cellIterator = row.cellIterator();
    System.out.print("|");
    while (cellIterator.hasNext()) {
        Cell cell = cellIterator.next();
        System.out.print(cell);/*from   w w  w .  jav  a2s.co  m*/
        System.out.print("(");
        System.out.print(cell.getCellTypeEnum());
        if (cell.getCellTypeEnum().equals(CellType.NUMERIC) && DateUtil.isCellDateFormatted(cell)) {
            System.out.print("Date");
        }
        System.out.print(")");
        System.out.print("\t |");
    }
    System.out.println("|");
}

From source file:com.jmc.jfxxlsdiff.task.GetWorkSheetContent.java

private List<List> findRows() {
    List<List> rows = new ArrayList<>();
    Iterator<Row> ri = sheet.rowIterator();

    while (ri.hasNext()) {
        Row r = ri.next();
        Iterator<Cell> ci = r.cellIterator();

        if (ci.hasNext()) {
            List colVals = new ArrayList();
            //colVals.add( r.getRowNum() + 1 );

            for (int i = 0; ci.hasNext(); i++) {
                Cell cell = ci.next();// ww  w .j  a v  a  2s .  c  o  m

                while (cell.getColumnIndex() > i) {
                    i++;
                    colVals.add(null);
                }

                colVals.add(POIXlsUtil.getCellValue(cell));
            }
            rows.add(colVals);
        } else {
            rows.add(null);
        }
        //rows.add( ri.next() );
    }

    return rows;
}

From source file:com.jogo.dao.RepositorioDao.java

@Override
public List importar() {

    //arry de usuario para armazenar os meus usuarios que pega do excel na folha 1
    List<Usuario> usuarios = new ArrayList<>();

    //CAPTURA OS DADOS DO USUARIO NO EXCEL
    try {//from  w  w  w . ja v  a 2 s. c o  m
        //capturando o excel para meu wb
        wb = WorkbookFactory.create(new FileInputStream(patch));

        //CAPTURAR A PRIMEIRA FOLHA DO EXCEL 
        Sheet folha = wb.getSheetAt(0);

        //criO um iterator para interagir com as linhas
        Iterator filaIterator = folha.rowIterator();

        //ENQUANTO HOUVER LINHAS O ITERATOR ME TRAZ.
        while (filaIterator.hasNext()) {

            //CAPTURO A LINHA DO EXCEL
            Row linha = (Row) filaIterator.next();

            //CRIO UM INTERATOR PARA INTERAGIR COM AS COLUNAS
            Iterator colunaIterator = linha.cellIterator();

            //CRIOU A CLASSE DE USUARIO E ADD DENTRO DO MEU ARRAY
            Usuario user = new Usuario();
            usuarios.add(user);

            //ENAUQNTO HOUVER COLUNAS O INTERATOR ME TRAZ.
            while (colunaIterator.hasNext()) {

                //COM A LINHA E A COLUNA JA POSSO CRIAR UMA CELULA.
                Cell celula = (Cell) colunaIterator.next();

                //APOS CAPTURAR O VALOR NA CELULA, SETO PARA MINHA CLASSE USUARIO QUE CRIEI LOGO ACIMA.
                if (celula != null) {
                    //CAPTURO O TIPO DA CELULA, NESSE CASO E STRING E NUMERICO(INT)
                    switch (celula.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        //CONVERTENDO O VALOR PARA INTEIRO.
                        user.setPontuacao((int) Math.round(celula.getNumericCellValue()));
                        break;
                    case Cell.CELL_TYPE_STRING:
                        user.setNome(celula.getStringCellValue());
                        break;

                    }

                }

            }

        }

    } catch (IOException | InvalidFormatException | EncryptedDocumentException e) {
    }
    return usuarios;
}

From source file:com.jogo.dao.RepositorioDao.java

@Override
public List importarPerguntas(int index) {

    //CRIO UM ARRAY DE PERGUNTAS PARA ARMAZENAR AS PERGUNTAS BUSCAS DO EXCEL.
    List<Perguntas> perguntas = new ArrayList<>();

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

        //CAPTURANDO O EXCEL PARA MEU WB.    
        wb = WorkbookFactory.create(new FileInputStream(patch));

        //CAPTURO A FOLHA DO EXCEL PASSANDO O INDEX
        Sheet folha = wb.getSheetAt(index);

        //CRIO UM ITERATOR PARA INTERAGIR COM AS LINHAS.
        Iterator filaIterator = folha.rowIterator();

        //ENQUANTO HOUVER INTERAO PEGA UMA LINHA.
        while (filaIterator.hasNext()) {

            //CAPTURO A LINHA DO EXCEL
            Row linha = (Row) filaIterator.next();
            //CRIO UM ITERATOR PARA PEGAR AS COLUNAS
            Iterator colunaIterator = linha.cellIterator();

            //AQUI DIGO QUE MINHAS COLUNAS NO PODE PASSAR DE 6, COMO TA A MINHA ESTRUTURA PARA O EXCEL
            //1 - PERGUNTA, 2- ALTERNATIVA, 3- ALTERNATIVA, 4- ATLTERNATIVA, 5- ALTERNATIVA, 6- RESPOSTA
            //CHEGOU MAIOR QUE 6 SAIU DO LOOP DE COLUNAS.
            if (linha.getLastCellNum() > 6) {
                break;
            }

            //CRIOU A CLASSE DE PERGUNTAS E ADD DENTRO DO MEU ARRAY
            Perguntas per = new Perguntas();
            perguntas.add(per);

            //INTERAGIR COM AS COLUNAS, PEGAR AS COLUNAS DO EXCEL
            while (colunaIterator.hasNext()) {

                //TENDO A LINHA E COLUNA JA POSSO TER UMA CELULA.
                Cell celula = (Cell) colunaIterator.next();

                //APOS CAPTURAR O VALOR NA CELULA, SETO PARA MINHA CLASSE PERGUNTAS QUE CRIEI LOGO ACIMA.
                if (celula != null) {
                    //CAPTURAR O TIPO DA CELULA, NO CASO TODAS AS PERGUNTAS E ALTERNATIVAS SO STRINGS.
                    //OBS: OLHE QUE NESSE CASO S POSSO TRAZER STRING'S CASO CONTRARIO NO IR? FUNCIONAR.:/
                    switch (celula.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        switch (celula.getColumnIndex()) {
                        case 0:
                            per.setPergunta(celula.getStringCellValue());
                            break;
                        case 1:
                            per.setAlt1(celula.getStringCellValue());
                            break;
                        case 2:
                            per.setAlt2(celula.getStringCellValue());
                            break;
                        case 3:
                            per.setAlt3(celula.getStringCellValue());
                            break;
                        case 4:
                            per.setAlt4(celula.getStringCellValue());
                            break;
                        case 5:
                            per.setResposta(celula.getStringCellValue());
                            break;
                        default:
                            break;
                        }
                    }

                }

            }
        }
    } catch (IOException | InvalidFormatException | EncryptedDocumentException e) {
    }
    return perguntas;
}

From source file:com.larasolution.serverlts.FileUploadHandler.java

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    //     tablename=request.getParameter(tablename)
    //process only if its multipart content
    FileOutputStream fos = new FileOutputStream("C:\\uploads\\data.csv");
    String list = "";
    List<List> allData = new ArrayList<List>();

    List<String> parameters = new ArrayList<String>();
    if (ServletFileUpload.isMultipartContent(request)) {

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

            StringBuilder data = new StringBuilder();
            List<FileItem> multiparts = new ServletFileUpload(new DiskFileItemFactory()).parseRequest(request);
            System.out.println(multiparts);
            for (FileItem item : multiparts) {
                if (item.isFormField()) {
                    parameters.add(item.getFieldName());
                    System.out.println(parameters);
                }
                if (!item.isFormField()) {
                    String name = new File(item.getName()).getName();

                    item.write(new File(UPLOAD_DIRECTORY + File.separator + name));
                    //System.out.println(File.separator);
                    // Get the workbook object for XLSX file
                    XSSFWorkbook wBook = new XSSFWorkbook(
                            new FileInputStream(UPLOAD_DIRECTORY + File.separator + name));

                    XSSFSheet zz = wBook.getSheetAt(0);
                    FormulaEvaluator formulaEval = wBook.getCreationHelper().createFormulaEvaluator();

                    Row row;
                    Cell cell;

                    // Iterate through each rows from first sheet
                    Iterator<Row> rowIterator = zz.iterator();
                    while (rowIterator.hasNext()) {
                        row = rowIterator.next();

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

                        while (cellIterator.hasNext()) {

                            cell = cellIterator.next();

                            switch (cell.getCellType()) {
                            case Cell.CELL_TYPE_BOOLEAN:
                                data.append(cell.getBooleanCellValue()).append(",");
                                break;
                            case Cell.CELL_TYPE_NUMERIC:
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    data.append(
                                            com.larasolution.modle.getDate.getDate5(cell.getDateCellValue()))
                                            .append(",");
                                } else {
                                    data.append(cell.getNumericCellValue()).append(",");
                                }

                                break;
                            case Cell.CELL_TYPE_STRING:
                                data.append(cell.getStringCellValue()).append(",");
                                break;
                            case Cell.CELL_TYPE_BLANK:
                                data.append("" + ",");
                                break;
                            case Cell.CELL_TYPE_FORMULA:
                                Double value = Double.parseDouble(formulaEval.evaluate(cell).formatAsString());

                                data.append(String.format("%.2f", value)).append(",");
                                break;
                            default:
                                data.append(cell).append("");

                            }

                        }
                        data.append("\r\n");
                        //String k = data.substring(0, data.length() - 3);
                        //ls.add(k);

                        // data.setLength(0);
                    }

                    fos.write(data.toString().getBytes());
                    fos.close();

                    //
                }
            }

            savetosql();
            request.setAttribute("message", "successfully uploaded ");
        } catch (Exception ex) {
            request.setAttribute("message", "File Upload Failed due to " + ex);
        }

    } else {
        request.setAttribute("message", "Sorry this Servlet only handles file upload request");
    }

    request.setAttribute("arrayfile", allData);
    request.setAttribute("names", parameters);
    RequestDispatcher disp = getServletContext().getRequestDispatcher("/FileUploadResult.jsp");
    disp.forward(request, response);

    // System.out.println(allData.size());
    // response.sendRedirect("send.jsp?arrayfile=" + list + "");
    //request.getRequestDispatcher("/send.jsp?arrayfile='"+ls+"'").forward(request, response);
}

From source file:com.lushapp.common.excel.ExcelUtil.java

License:Apache License

/**
 * /*w  w w. j  av  a  2 s  .c om*/
 *  excel
 * 
 * @param inputstream : ?
 * @param pojoClass :  (?)
 * @return
 */
public static Collection importExcelByIs(InputStream inputstream, Class pojoClass) {
    Collection dist = new ArrayList<Object>();
    try {
        // 
        Field filed[] = pojoClass.getDeclaredFields();
        // Annotation??,map
        Map<String, Method> fieldSetMap = new HashMap<String, Method>();
        Map<String, Method> fieldSetConvertMap = new HashMap<String, Method>();
        // ?
        for (int i = 0; i < filed.length; i++) {
            Field f = filed[i];
            // ?Annotation
            Excel excel = f.getAnnotation(Excel.class);
            // Annotationd?
            if (excel != null) {
                // AnnotationSetter
                String fieldname = f.getName();
                String setMethodName = "set" + fieldname.substring(0, 1).toUpperCase() + fieldname.substring(1);
                // method
                Method setMethod = pojoClass.getMethod(setMethodName, new Class[] { f.getType() });
                // methodAnnotaion??key?
                // ???  ??
                fieldSetMap.put(excel.exportName(), setMethod);
                if (excel.importConvert() == true) {
                    // get/setXxxxConvert??? ?Entity?
                    StringBuffer setConvertMethodName = new StringBuffer("convertSet");
                    setConvertMethodName.append(fieldname.substring(0, 1).toUpperCase());
                    setConvertMethodName.append(fieldname.substring(1));
                    Method getConvertMethod = pojoClass.getMethod(setConvertMethodName.toString(),
                            new Class[] { String.class });
                    fieldSetConvertMap.put(excel.exportName(), getConvertMethod);
                }
            }
        }
        // FileFileInputStream;
        // // 
        HSSFWorkbook book = new HSSFWorkbook(inputstream);
        // // 
        HSSFSheet sheet = book.getSheetAt(0);
        // // ?
        Iterator<Row> row = sheet.rowIterator();
        // 
        Row title = row.next();
        // 
        Iterator<Cell> cellTitle = title.cellIterator();
        // map
        Map titlemap = new HashMap();
        // 
        int i = 0;
        // 
        while (cellTitle.hasNext()) {
            Cell cell = cellTitle.next();
            String value = cell.getStringCellValue();
            titlemap.put(i, value);
            i = i + 1;
        }
        // ??DateFormat
        // SimpleDateFormat sf;
        while (row.hasNext()) {
            // 
            Row rown = row.next();
            // 
            Iterator<Cell> cellbody = rown.cellIterator();
            // 
            Object tObject = pojoClass.newInstance();
            int k = 0;
            // ??
            while (cellbody.hasNext()) {
                Cell cell = cellbody.next();
                // 
                String titleString = (String) titlemap.get(k);
                // ?Annotation?set
                if (fieldSetMap.containsKey(titleString)) {
                    Method setMethod = (Method) fieldSetMap.get(titleString);
                    // setter?
                    Type[] ts = setMethod.getGenericParameterTypes();
                    // ???
                    String xclass = ts[0].toString();
                    // ?
                    if (Cell.CELL_TYPE_STRING == cell.getCellType()
                            && fieldSetConvertMap.containsKey(titleString)) {
                        // ???String?
                        fieldSetConvertMap.get(titleString).invoke(tObject, cell.getStringCellValue());
                    } else {
                        if (xclass.equals("class java.lang.String")) {
                            // Cell??String?
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            setMethod.invoke(tObject, cell.getStringCellValue());
                        } else if (xclass.equals("class java.util.Date")) {
                            // update-start--Author:Quainty Date:20130523 for??(?Excel?)
                            Date cellDate = null;
                            if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                                // ?
                                cellDate = cell.getDateCellValue();
                            } else { //   Cell.CELL_TYPE_STRING: ? yyyy-mm-dd hh:mm:ss ??(wait to do:?)
                                cellDate = stringToDate(cell.getStringCellValue());
                            }
                            setMethod.invoke(tObject, cellDate);
                            //// --------------------------------------------------------------------------------------------
                            //String cellValue = cell.getStringCellValue();
                            //Date theDate = stringToDate(cellValue);
                            //setMethod.invoke(tObject, theDate);
                            //// --------------------------------------------------------------------------------------------
                        } else if (xclass.equals("class java.lang.Boolean")) {
                            boolean valBool;
                            if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType()) {
                                valBool = cell.getBooleanCellValue();
                            } else {//   Cell.CELL_TYPE_STRING
                                valBool = cell.getStringCellValue().equalsIgnoreCase("true")
                                        || (!cell.getStringCellValue().equals("0"));
                            }
                            setMethod.invoke(tObject, valBool);
                        } else if (xclass.equals("class java.lang.Integer")) {
                            Integer valInt;
                            if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                                valInt = (new Double(cell.getNumericCellValue())).intValue();
                            } else {//   Cell.CELL_TYPE_STRING
                                valInt = new Integer(cell.getStringCellValue());
                            }
                            setMethod.invoke(tObject, valInt);
                        } else if (xclass.equals("class java.lang.Long")) {
                            Long valLong;
                            if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                                valLong = (new Double(cell.getNumericCellValue())).longValue();
                            } else {//   Cell.CELL_TYPE_STRING
                                valLong = new Long(cell.getStringCellValue());
                            }
                            setMethod.invoke(tObject, valLong);
                        } else if (xclass.equals("class java.math.BigDecimal")) {
                            BigDecimal valDecimal;
                            if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                                valDecimal = new BigDecimal(cell.getNumericCellValue());
                            } else {//   Cell.CELL_TYPE_STRING
                                valDecimal = new BigDecimal(cell.getStringCellValue());
                            }
                            setMethod.invoke(tObject, valDecimal);
                        }
                    }
                }
                // 
                k = k + 1;
            }
            dist.add(tObject);
        }
    } catch (Exception e) {
        e.printStackTrace();
        return null;
    }
    return dist;
}

From source file:com.mum.processexceldata.RateService.java

public void processCallRates(HSSFSheet sheet, Date effectiveDate) {
    String serviceName = null;/*from   w  w  w  .  jav a  2s .  c o  m*/
    String sourceCountryName = null;
    String sheetName = sheet.getSheetName();
    Service serv = null;
    st = new StringTokenizer(sheetName, "_");
    if (st.countTokens() == 2) {
        serviceName = st.nextToken();
        sourceCountryName = st.nextToken();
    }

    if (serviceName != null && !serviceName.isEmpty()) {
        //get service from DB matching service Name and Source Country
        serv = new Service();
    }

    Iterator<Row> rowIterator = sheet.iterator();
    //Skip first row with headings
    if (rowIterator.hasNext()) {
        rowIterator.next();
    }

    //insertNewRates
    //Service  service
    //destination  Country
    //offPeakRate long
    //peakRate  long        
    //effectiveDate Date
    while (rowIterator.hasNext()) {
        Rate rate = new Rate();
        Row row = rowIterator.next();
        //For each row, iterate through each columns
        Iterator<Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext()) {
            //Cell cell = cellIterator.next();                   
            //if(cell.getCellType()== Cell.CELL_TYPE_NUMERIC){
            //System.out.print(cell.getNumericCellValue() + "\t\t");
            rate.setDestination(countryCodeMap.get(cellIterator.next().getNumericCellValue()));
            rate.setPeakRate(cellIterator.next().getNumericCellValue());
            rate.setOffPeakRate(cellIterator.next().getNumericCellValue());
            //}

        }
        rate.setService(serv);
        //insert into Rate and get Id
        // serviceIdList.add(rateId)
    }
    //update Service with RateIds 
    //serv.getRates().addAll(RateIds); saveService
}

From source file:com.mum.processexceldata.ReadExcel.java

public static void main(String args[]) {
    try {/* w  w  w  .  java  2 s  .co m*/
        FileInputStream file = new FileInputStream(
                new File("C:\\Users\\demodem\\Downloads\\Calling_Codes.xls"));
        System.out.println("File:" + file);
        // HSSFWorkbook book = new HSSFWorkbook(file);

        //Get the workbook instance for XLS file 
        HSSFWorkbook workbook = new HSSFWorkbook(file);

        //Get first sheet from the workbook
        HSSFSheet sheet = workbook.getSheetAt(0);
        //System.out.println("sheet    :"+sheet);
        //Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();

            //For each row, iterate through each columns
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {

                Cell cell = cellIterator.next();
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_BOOLEAN:
                    System.out.print(cell.getBooleanCellValue() + "\t\t");
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    System.out.print(cell.getNumericCellValue() + "\t\t");
                    break;
                case Cell.CELL_TYPE_STRING:
                    System.out.print(cell.getStringCellValue() + "\t\t");
                    break;
                }
            }
            System.out.println("");
        }
        file.close();
        FileOutputStream out = new FileOutputStream(new File("C:\\Users\\demodem\\Downloads\\test.xls"));
        workbook.write(out);
        out.close();

    } catch (Exception e) {
        System.out.println("Error in reading the file.");
    }
}