Example usage for org.apache.poi.xssf.usermodel XSSFSheet iterator

List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet iterator

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFSheet
  6. iterator

Introduction

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

Prototype

@Override
public Iterator<Row> iterator()

Source Link

Document

Alias for #rowIterator() to allow foreach loops

Usage

From source file:Util.exemploLendoXLSX.java

/**
 * @param args the command line arguments
 *//*from   w  w  w .ja va 2 s.com*/
public static void main(String[] args) {

    FileInputStream fisPlanilha = null;

    try {
        File file = new File("D:\\planilhas\\planilhaDaAula.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 (aba 1)
        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:
                    System.out.println("TIPO STRING: " + cell.getStringCellValue());
                    break;

                case Cell.CELL_TYPE_NUMERIC:
                    System.out.println("TIPO NUMERICO: " + cell.getNumericCellValue());
                    break;

                case Cell.CELL_TYPE_FORMULA:
                    System.out.println("TIPO FORMULA: " + cell.getCellFormula());
                }

            }
        }

    } catch (FileNotFoundException ex) {
        Logger.getLogger(exemploLendoXLSX.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(exemploLendoXLSX.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        try {
            fisPlanilha.close();
        } catch (IOException ex) {
            Logger.getLogger(exemploLendoXLSX.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

}

From source file:Utilities.BatchInDJMSHelper.java

public void compareXlsxBatch(String xlsxFileName, Map<String, Map<String, String>> legitLvMap) {
    //Map<String, Map<String, String>> LegitLvMap = 
    File xlsxFile = new File(xlsxFileName);
    try {/*w  ww  .  ja va 2s.  c  om*/
        FileInputStream fis = new FileInputStream(xlsxFile);
        XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);
        //Return first sheet from the XLSX workbook
        XSSFSheet mySheet = myWorkBook.getSheetAt(0);
        legendBuilder(myWorkBook);
        //Get iterator to all the rows in current sheet
        Iterator<Row> rowIterator = mySheet.iterator();
        // Traversing over each row of XLSX file
        if (rowIterator.hasNext()) {
            Row headerRow = rowIterator.next(); //skip the header row
            Iterator<Cell> it = headerRow.cellIterator();
            int numCell = 0;
            // List<String> keyList = new ArrayList<>(); //keep track info of each column
            while (it.hasNext()) {
                //keyList.add(it.next().getStringCellValue());   
                it.next();
                numCell++;
            }

            if (numCell == GlobalVar.LEAVE_TITLES_V1.length || numCell == GlobalVar.LEAVE_TITLES_V2.length) { // correct xlsx file                 
                int rowNum = 1;
                while (rowIterator.hasNext()) {
                    Row row = rowIterator.next();
                    //row.getRowStyle();
                    rowNum++;
                }
                for (int i = 1; i < rowNum; i++) {
                    Row row = mySheet.getRow(i);
                    foregroundColorSetUp(row, myWorkBook, numCell, legitLvMap); //check each row and update foreground color
                }
                fis.close();
                FileOutputStream output;

                String targetFile = null;
                if (xlsxFileName.contains(".xlsx")) {
                    targetFile = xlsxFileName.replace(".xlsx", "COLORED.xlsx");
                } else {
                    targetFile = xlsxFileName + "COLORED.xlsx";
                }
                output = new FileOutputStream(targetFile);
                myWorkBook.write(output);
                output.close();

            } else {
                JOptionPane.showMessageDialog(null, "XLSX file doesn't contain correct info!");
            }
        } else {
            JOptionPane.showMessageDialog(null, "XLSX file is empty!");
            System.out.println("The xlsx file is empty!");
        }
        JOptionPane.showMessageDialog(null,
                "The leave roster is colored successfully. Please check *COLORED.xlsx.\n");

        //                        case DUPLICATE_LV_ERR: return IndexedColors.YELLOW.getIndex();
        //            case INPROCESSING_ERR: return IndexedColors.PINK.getIndex();
        //            case AFTER_PCS_ERR: return IndexedColors.LAVENDER.getIndex();
        //            case PCS_ERR: return IndexedColors.BLUE.getIndex();
        //            case DUPLICATE_CTRL_NUM_ERR: return IndexedColors.DARK_YELLOW.getIndex();
        // finds the work book in stance for XLSX file
    } catch (FileNotFoundException ex) {
        JOptionPane.showMessageDialog(null, "BatchInDJMS.java: Xlsx file not found exception!");
    } catch (IOException ex) {
        JOptionPane.showMessageDialog(null, "BatchInDJMS.java: Xlsx file IO Exception!");
        Logger.getLogger(BatchInDJMS.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:utilities.DebtMgmtBatchInDJMS.java

public void compareXlsxBatch(String xlsxFileName) {
    File xlsxFile = new File(xlsxFileName);
    try {/* w ww .  j  a v a 2 s  . co  m*/
        FileInputStream fis = new FileInputStream(xlsxFile);
        XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);

        //Return first sheet from the XLSX workbook
        XSSFSheet mySheet = myWorkBook.getSheetAt(0);
        legendBuilder(myWorkBook);
        //Get iterator to all the rows in current sheet
        Iterator<Row> rowIterator = mySheet.iterator();
        // Traversing over each row of XLSX file
        if (rowIterator.hasNext()) {
            Row headerRow = rowIterator.next(); //skip the header row
            //                Iterator<Cell> it = headerRow.cellIterator();
            //                int numCell = 0;
            //               // List<String> keyList = new ArrayList<>(); //keep track info of each column
            //                while(it.hasNext()){
            //                    //keyList.add(it.next().getStringCellValue());   
            //                    it.next();
            //                    numCell++;
            //                }

            //  if (numCell == GlobalVar.LEAVE_TITLES_V1.length || numCell == GlobalVar.LEAVE_TITLES_V2.length){  // correct xlsx file                 
            int rowNum = 1;
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                //row.getRowStyle();
                rowNum++;
            }
            for (int i = 0; i < rowNum; i++) {
                Row row = mySheet.getRow(i);
                foregroundColorSetUp(row, myWorkBook); //check each row and update foreground color
            }

            fis.close();
            FileOutputStream output;

            String targetFile = null;
            if (xlsxFileName.contains(".xlsx")) {
                targetFile = xlsxFileName.replace(".xlsx", "COLORED.xlsx");
            } else {
                targetFile = xlsxFileName + "COLORED.xlsx";
            }
            output = new FileOutputStream(targetFile);
            myWorkBook.write(output);
            output.close();
            //
            //                } else {
            //                     JOptionPane.showMessageDialog(null, "XLSX file doesn't contain correct info!");
            //                }
        } else {
            JOptionPane.showMessageDialog(null, "XLSX file is empty!");
            System.out.println("The xlsx file is empty!");
        }
        JOptionPane.showMessageDialog(null,
                "The leave roster is colored successfully. Please check *COLORED.xlsx.\n");

        //                        case DUPLICATE_LV_ERR: return IndexedColors.YELLOW.getIndex();
        //            case INPROCESSING_ERR: return IndexedColors.PINK.getIndex();
        //            case AFTER_PCS_ERR: return IndexedColors.LAVENDER.getIndex();
        //            case PCS_ERR: return IndexedColors.BLUE.getIndex();
        //            case DUPLICATE_CTRL_NUM_ERR: return IndexedColors.DARK_YELLOW.getIndex();
        // finds the work book in stance for XLSX file
    } catch (FileNotFoundException ex) {
        JOptionPane.showMessageDialog(null, "BatchInDJMS.java: Xlsx file not found exception!");
    } catch (IOException ex) {
        JOptionPane.showMessageDialog(null, "BatchInDJMS.java: Xlsx file IO Exception!");
        Logger.getLogger(DebtMgmtBatchInDJMS.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:utilities.DebtMgmtBatchInDJMS.java

private void buildSSNMap(String xlsxFileName) {
    File xlsxFile = new File(xlsxFileName);
    try {//from  w w w . j  av a  2s  . co m
        FileInputStream fis = new FileInputStream(xlsxFile);
        XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);
        //Return first sheet from the XLSX workbook
        XSSFSheet mySheet = myWorkBook.getSheetAt(0);

        //Get iterator to all the rows in current sheet
        Iterator<Row> rowIterator = mySheet.iterator();
        // Traversing over each row of XLSX file
        if (rowIterator.hasNext()) {
            Row headerRow = rowIterator.next(); //skip the header row
            Iterator<Cell> it = headerRow.cellIterator();
            int numCell = 0;

            DataFormatter df = new DataFormatter(); //for ssn

            // if (numCell == TITLE_LEN){  // correct xlsx file                 
            int rowNum = 1;
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();

                //row.getRowStyle();
                Cell cell = row.getCell(SSN_INDEX);
                String ssn = df.formatCellValue(cell); //return ***-**-****
                ssn = GlobalVar.fullSSNgenerator(ssn);
                SSN_MAP.put(ssn, INITIAL_MSG); //initial ssn map .  value is for the status
            }
            //                } else {
            //                    JOptionPane.showMessageDialog(null, "Invalid Xlsx file!");
            //                }
        }
    } catch (FileNotFoundException ex) {
        JOptionPane.showMessageDialog(null, "BatchInDJMS.java: Xlsx file not found exception!");
    } catch (IOException ex) {
        JOptionPane.showMessageDialog(null, "BatchInDJMS.java: Xlsx file IO Exception!");
        Logger.getLogger(DebtMgmtBatchInDJMS.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:utilities.DMPORosterToMapGenerator.java

public DMPORosterToMapGenerator(String xlsxFileName) throws FileNotFoundException, IOException {
    db = new TreeMap();
    File myFile = new File(xlsxFileName);

    FileInputStream fis = new FileInputStream(myFile);

    System.out.println(xlsxFileName);
    XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);

    XSSFSheet mySheet = myWorkBook.getSheetAt(0);
    Iterator<Row> rowIterator = mySheet.iterator();
    DataFormatter df = new DataFormatter();
    //        System.out.println(IndexedColors.YELLOW);
    //        System.out.println(IndexedColors.BLUE.getIndex());
    //        System.out.println(IndexedColors.RED.getIndex());
    //        System.out.println(IndexedColors.WHITE);
    //        System.out.println(IndexedColors.BLACK);
    //        System.out.println(IndexedColors.GREEN);

    rowIterator.next(); // skip the header row
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();/*from w  ww  . j  a va 2 s . com*/
        Cell cell = row.getCell(FILE_AT_DMPO_INDX); // File at DMPO      
        int type = cell.getCellType();
        //System.out.println(cell.getStringCellValue());

        if (type == HSSFCell.CELL_TYPE_STRING && cell.getStringCellValue().equalsIgnoreCase("Yes")) { // File at DMPO      
            CellStyle style = cell.getCellStyle();

            // getFillBackgroundColor() always 64.  getFillForegroundColor() is 64 for white
            // getFillForegroundColor() is 0 for yellow
            //                System.out.println(style.getFillForegroundColor());
            //                //if (style.getFillForegroundColorColor() != null)
            //                    System.out.println(style.getFillForegroundColorColor());
            //               
            //                System.out.println(style.getFillBackgroundColor());
            //                //if (style.getFillBackgroundColorColor() != null)
            //                    System.out.println(style.getFillBackgroundColorColor());
            //if(style.getFillForegroundColor() != IndexedColors.YELLOW.getIndex()) { 
            Color color = style.getFillForegroundColorColor();
            if (color == null || color.toString().equals(GlobalVar.WHITE)) { // no fill or fill with white

                Cell ssnCell = row.getCell(SSN_INDX);
                String ssnString = df.formatCellValue(ssnCell); //return ***-**-****
                ssnString = readSSN(ssnString).trim();
                if (!db.containsKey(ssnString)) {
                    List<String> list = new LinkedList<String>();
                    String ssn = displayFormatSSN(ssnString).trim();
                    String name = row.getCell(NAME_INDX).getStringCellValue().trim();
                    String dutyStation = row.getCell(ORIGIN_INDX).getStringCellValue().trim();
                    String typeCM = row.getCell(TYPE_CM_INDX).getStringCellValue().trim();
                    //String typeCM = row.getCell(TYPE_CM_INDX).getStringCellValue();
                    list.add(ssn);
                    list.add(name);
                    list.add(dutyStation);
                    list.add(typeCM);
                    db.put(ssnString, list);
                }
            }
        }
    }
}

From source file:Utility.CSV_File_Generator.java

public static void write_single_row(XSSFSheet original_sheet, XSSFSheet resultSheet, int index) {
    FileOutputStream out = null;/*  w w w  . j a v  a2 s  .  c  o m*/
    try {

        out = new FileOutputStream(new File(file_details("Excel_Traffic_Rows")));

        int col_index;
        //Create blank workbook
        XSSFWorkbook workbook = new XSSFWorkbook();
        //Create a blank sheet
        XSSFSheet intermediate = workbook.createSheet("Test Data");
        //Create row object
        Row row;
        XSSFRow intermediate_row;

        XSSFRow actual_row = original_sheet.getRow(index);

        //This data needs to be written (Object[])
        TreeMap<String, TreeMap<String, Cell>> row_map = new TreeMap<String, TreeMap<String, Cell>>();

        if (index == 0) {
            TreeMap<String, Cell> cols = new TreeMap<String, Cell>();

            XSSFRow temp = intermediate.createRow(index);
            Iterator<Cell> cellIterator = actual_row.cellIterator();
            int i = 1;
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                cols.put(Integer.toString(i++), cell);
            }
            row_map.put("0", cols);

            Set<String> keyid = row_map.get("0").keySet();
            int cellid = 0;
            for (String key : keyid) {
                Cell original = cols.get(key);
                Cell cell = temp.createCell(cellid++);
                switch (original.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    cell.setCellValue(original.getNumericCellValue());
                    break;
                case Cell.CELL_TYPE_STRING:
                    cell.setCellValue(original.getStringCellValue());
                    break;
                }
            }
            workbook.write(out);
            row_map.clear();
        } else {
            int count = 0;
            while (count < index) {
                Iterator<Row> rowIterator = resultSheet.iterator();
                while (rowIterator.hasNext()) {
                    //Reading a row from the existing result sheet
                    TreeMap<String, Cell> data_row = new TreeMap<String, Cell>();
                    row = rowIterator.next();
                    Iterator<Cell> cell = row.cellIterator();
                    col_index = 0;
                    while (cell.hasNext()) {
                        Cell c = cell.next();
                        data_row.put(Integer.toString(col_index++), c);
                    }

                    row_map.put(Integer.toString(count), data_row);
                    count++;
                }
                //writing the row read into the new workbook(intermediate)
                Set<String> keyid = row_map.keySet();
                for (String key : keyid) {
                    int column_counter = 0;
                    intermediate_row = intermediate.createRow(Integer.parseInt(key));
                    TreeMap<String, Cell> map = row_map.get(key);
                    Set<String> row_data = map.keySet();
                    for (String cell_data : row_data) {
                        Cell original = map.get(cell_data);
                        Cell new_cell = intermediate_row.createCell(column_counter++);
                        switch (original.getCellType()) {
                        case Cell.CELL_TYPE_NUMERIC:
                            new_cell.setCellValue(original.getNumericCellValue());
                            break;
                        case Cell.CELL_TYPE_STRING:
                            new_cell.setCellValue(original.getStringCellValue());
                            break;
                        }
                    }
                }
            }
            XSSFRow temp = intermediate.createRow(index);
            Iterator<Cell> cellIterator = actual_row.cellIterator();
            TreeMap<String, Cell> required_data = new TreeMap<String, Cell>();

            int i = 0;
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                required_data.put(Integer.toString(i++), cell);
            }
            row_map.put(Integer.toString(index), required_data);

            required_data = row_map.get(Integer.toString(index));

            Set<String> keyid = required_data.keySet();
            int cellid = 0;
            for (String key : keyid) {
                Cell original = required_data.get(key);
                Cell cell = temp.createCell(cellid++);
                switch (original.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    cell.setCellValue(original.getNumericCellValue());
                    break;
                case Cell.CELL_TYPE_STRING:
                    cell.setCellValue(original.getStringCellValue());
                    break;
                }
            }
            workbook.write(out);
            out.flush();
            row_map.clear();
        }
        out.close();
    } catch (FileNotFoundException ex) {
        Logger.getLogger(CSV_File_Generator.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(CSV_File_Generator.class.getName()).log(Level.SEVERE, null, ex);
    }
    System.out.println("Traffic Data is: " + index + " row.");
}

From source file:vd10_workbook.AbilityManagement.java

public void loadWorkSheet(XSSFWorkbook workbook) {
    this.list = new ArrayList<>();
    //Get the sheet at index 0 (fist sheet)
    XSSFSheet spreadsheet = workbook.getSheet("kha_nang");
    //declare row
    XSSFRow row;/*from w w  w.j  av a2s.  c  om*/
    //declare Iterator<Row> to browse row by row
    Iterator<Row> rowIterator = spreadsheet.iterator();
    row = (XSSFRow) rowIterator.next();
    row = (XSSFRow) rowIterator.next();
    while (rowIterator.hasNext()) {
        row = (XSSFRow) rowIterator.next();
        Iterator<Cell> cellIterator = row.cellIterator();
        Cell cell = cellIterator.next();

        cell = cellIterator.next();
        int employeeID = (int) cell.getNumericCellValue();
        cell = cellIterator.next();
        int languageID = (int) cell.getNumericCellValue();
        this.list.add(new Ability(employeeID, languageID));
    }
}

From source file:vd10_workbook.AssignedVoteManagement.java

public void loadWorkSheet(XSSFWorkbook workbook) throws ParseException {
    this.list = new ArrayList<>();
    //Get the sheet at index 0 (fist sheet)
    XSSFSheet spreadsheet = workbook.getSheet("phieu_phan_cong");
    //declare row
    XSSFRow row;/*from w  w w.  ja  v  a2 s .c  om*/
    //declare Iterator<Row> to browse row by row
    Iterator<Row> rowIterator = spreadsheet.iterator();
    row = (XSSFRow) rowIterator.next();
    row = (XSSFRow) rowIterator.next();
    int count = 1;
    while (rowIterator.hasNext()) {
        row = (XSSFRow) rowIterator.next();
        Iterator<Cell> cellIterator = row.cellIterator();
        Cell cell = cellIterator.next();
        cell = cellIterator.next();
        String date_s = cell.getStringCellValue();
        SimpleDateFormat dt = new SimpleDateFormat("yyyy-MM-dd");
        Date date = dt.parse(date_s);
        cell = cellIterator.next();
        int numberOfday = (int) cell.getNumericCellValue();
        cell = cellIterator.next();
        int employeeID = (int) cell.getNumericCellValue();
        cell = cellIterator.next();
        int workTypeID = (int) cell.getNumericCellValue();
        this.list.add(new AssignedVote(date, numberOfday, employeeID, workTypeID));
        if (count == NUMBEROFROWS) {
            break;
        } else {
            count++;
        }
    }
}

From source file:vd10_workbook.Company.java

private void loadWorkSheet() {
    //Get the sheet at index 0 (fist sheet)
    XSSFSheet spreadsheet = this.workbook.getSheet("cong_ty");
    //declare row
    XSSFRow row;//from   w w w .  j av a  2 s . c  om
    //declare Iterator<Row> to browse row by row
    Iterator<Row> rowIterator = spreadsheet.iterator();
    row = (XSSFRow) rowIterator.next();
    row = (XSSFRow) rowIterator.next();
    row = (XSSFRow) rowIterator.next();
    Iterator<Cell> cellIterator = row.cellIterator();
    Cell cell = cellIterator.next();

    cell = cellIterator.next();
    this.name = cell.getStringCellValue();

    cell = cellIterator.next();
    this.phoneNumber = cell.getStringCellValue();

    cell = cellIterator.next();
    this.address = cell.getStringCellValue();

    cell = cellIterator.next();
    this.minAge = (int) cell.getNumericCellValue();

    cell = cellIterator.next();
    this.maxAge = (int) cell.getNumericCellValue();
}

From source file:vd10_workbook.DepartmentManagement.java

public void loadWorkSheet(XSSFWorkbook workbook) throws ParseException {
    this.list = new ArrayList<>();
    //Get the sheet at index 0 (fist sheet)
    XSSFSheet spreadsheet = workbook.getSheet("don_vi");
    //declare row
    XSSFRow row;//www .  ja  v  a2  s  .  c om
    //declare Iterator<Row> to browse row by row
    Iterator<Row> rowIterator = spreadsheet.iterator();
    row = (XSSFRow) rowIterator.next();
    row = (XSSFRow) rowIterator.next();
    while (rowIterator.hasNext()) {
        row = (XSSFRow) rowIterator.next();
        Iterator<Cell> cellIterator = row.cellIterator();
        Cell cell = cellIterator.next();
        cell = cellIterator.next();
        this.list.add(new Department(cell.getStringCellValue()));
    }
}