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:Logic.ReadDoctorsFromExcel.java

public void readFromExcel(String file, JTable table) throws IOException {

    XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));

    XSSFSheet sheet = wb.getSheetAt(0);
    Iterator<Row> it = sheet.iterator();
    while (it.hasNext()) {
        Row row = it.next();/* www  .ja  v a 2  s .  c  o m*/
        Iterator<Cell> cells = row.iterator();
        while (cells.hasNext()) {
            Cell cell = cells.next();
            int cellIndex = cell.getColumnIndex();

            switch (cellIndex) {
            case 0:
                name = cell.getStringCellValue();
                break;
            case 1:
                if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                    snils = String.valueOf((int) cell.getNumericCellValue());
                    break;
                }
                if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                    snils = cell.getStringCellValue();
                    break;
                }
                break;

            case 2:
                if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                    v002 = String.valueOf((int) cell.getNumericCellValue());
                    break;
                }
                if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                    v002 = cell.getStringCellValue();
                    break;
                }
                break;
            case 3:
                if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                    v015 = String.valueOf((int) cell.getNumericCellValue());
                    break;
                }
                if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                    v015 = cell.getStringCellValue();
                    break;
                }
                break;

            default:
                System.out.print("|");
                break;
            }
        }
        DefaultTableModel model = (DefaultTableModel) table.getModel();
        String[] data = { name, snils, v002, v015 };
        model.addRow(data);

        removeAllFields();
    }

}

From source file:main.KeywordList.java

private List<Keyword> readFromExcel(String fileName) {
    List<Keyword> keywordList = new ArrayList();

    try {/*from w  w w  .j a va 2  s . c o  m*/
        ForcastUi.consoleLog("Opening filename: " + fileName);
        FileInputStream fIP = openExcelFileOrCreate(fileName);
        //Get the workbook instance for XLSX file 
        XSSFWorkbook workbook = new XSSFWorkbook(fIP);
        XSSFSheet spreadsheet = workbook.getSheetAt(0);
        Iterator<Row> rowIterator = spreadsheet.iterator();
        XSSFRow row;

        boolean firstLine = true;
        while (rowIterator.hasNext()) {
            String group = ""; //0
            String packageName = ""; //1
            String subpackageName = ""; //2
            String positionType = ""; //3
            String keyword = ""; //4
            String date = "01/01/2000"; //5
            String priority = "1"; //6 

            row = (XSSFRow) rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();

            // ?
            Cell cell = cellIterator.next();
            group = cell.getStringCellValue();

            if (firstLine) {
                firstLine = false;
                continue;
            }

            if (cellIterator.hasNext()) {
                cell = cellIterator.next();
                packageName = getCellValue(cell);
            }

            if (cellIterator.hasNext()) {
                cell = cellIterator.next();
                subpackageName = getCellValue(cell);
            }

            if (cellIterator.hasNext()) {
                cell = cellIterator.next();
                positionType = getCellValue(cell);
            }

            if (cellIterator.hasNext()) {
                cell = cellIterator.next();
                keyword = getCellValue(cell);
            }

            if (cellIterator.hasNext()) {
                cell = cellIterator.next();
                date = getCellDate(cell);
            }

            if (cellIterator.hasNext()) {
                cell = cellIterator.next();
                priority = getCellValue(cell);
            }
            //System.out.println("Keyword "+ keyword+"Date: "+date);
            keywordList.add(
                    new Keyword(group, packageName, subpackageName, positionType, keyword, date, priority));//.add(new TopStockDescription(shortName.trim(),longName.trim(),explanation.trim(),false));
        }
        fIP.close();

    } catch (FileNotFoundException e) {
        ForcastUi.consoleLog(e.getMessage());
        e.printStackTrace();
    } catch (IOException e) {
        ForcastUi.consoleLog(e.getMessage());
        e.printStackTrace();
    } catch (Exception ex) {
        ForcastUi.consoleLog(ex.getMessage());
        Logger.getLogger(TopStockDescriptionList.class.getName()).log(Level.SEVERE, null, ex);
    }

    return keywordList;
}

From source file:mindbodymerge.Parser.java

/**
 * Parses the Sales data excel sheet that was passed in, and creates Array List populated with the member's names (memberList), the items the members
 * purchase (myzouItemList) and the price of the items that they paid (itemPriceList)
 * All array lists are indexed to the same person (i.e. memberList.get(0) bought myzouItemList.get(0) and paid itemPriceList.get(0))
 * Parsing of the excel files are hard coded to the columns that held the correct data fields, and will need to be changed if the format of the excel 
 * file changes/* w w w  . j  a v a  2 s  .  co  m*/
 *
 * @param memberList: list of members who purchased items
 * @param myzouItemList: list of what the members purchased
 * @param itemPriceList: list of how much the member paid
 * 
 * @return void
 *
*/
private void parseSales(ArrayList<String> memberList, ArrayList<String> myzouItemList,
        ArrayList<String> itemPriceList) {

    try {
        //Variable to be used in determining which columns of the excel file should be read from used in the while loop
        int iterationCount = 0;

        //Used for parsing of the item price into an item string
        Double itemNumber;
        String itemString;

        //Holds mocode
        String mocode;

        //Uses Apache POI .XLSX file parser to get the data, as treating it like a normal .CSV will give weird data
        //sales is the sales data.xlsx file
        FileInputStream fis = new FileInputStream(sales);

        Database db = new Database();

        //Open WorkBook (.XLSX file)
        XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);
        //Open first sheet of workbook, shouldn't have to change unless the formatting of the sales data.xlsx file changes to a different sheet
        XSSFSheet mySheet = myWorkBook.getSheetAt(0);

        //Iterator for moving through each row of the sheet
        Iterator<Row> rowIterator = mySheet.iterator();

        //This first call of the iterator is to move the iterator past the labels at the top of the columns in the excel sheet
        rowIterator.next();

        //Move through each row of the excel file
        while (rowIterator.hasNext()) {
            //Move to next row
            Row row = rowIterator.next();
            //Iterator for the cells in the row
            Iterator<Cell> cellIterator = row.cellIterator();

            //Reset the iterationCount to 0 so the while loop below knows what column we are in
            iterationCount = 0;

            while (cellIterator.hasNext()) {
                //Move to the next cell
                Cell cell = cellIterator.next();

                //The second column (column B) holds the list of member names, so we read from that column to the memberList
                if (iterationCount == 1) {
                    //Get rid of all the spaces so matching is easier
                    memberList.add(cell.getStringCellValue().replaceAll("\\s", ""));
                }
                //The fourth column (column D) holds the list of purchased items, so we read from the column to the memberList
                else if (iterationCount == 3) {
                    mocode = db.getMocode(cell.getStringCellValue());
                    myzouItemList.add(mocode);
                }
                //The 17th column (column Q) holds the list of amount paid for the items with tax
                //Make sure that you choose the column that holds the actual amount paid (e.g. the row with negative numbers showing credit charges and tax)
                //number is taken in as a double, and formatted as a string to be added
                else if (iterationCount == 16) {
                    itemNumber = cell.getNumericCellValue();
                    itemString = String.format("%.2f", itemNumber);
                    itemPriceList.add(itemString);
                }

                //Move counter to next cell
                iterationCount++;
            }
        }
        //Test block for ensuring the lists are correct, the sizes should all be equal
        //            System.out.println(memberList.size());
        //            System.out.println(myzouItemList.size());
        //            System.out.println(itemPriceList.size());
        //            
        //            System.out.println(memberList);
        //            System.out.println(myzouItemList);
        //            System.out.println(itemPriceList);
    } catch (IOException e) {

    }
}

From source file:mindbodymerge.Parser.java

/**
 * Parses the membership.XLSX file to make a dictionary of student names and student numbers
 * Format is membershipDictionary.get(i) is the student number and membershipDictionary.get(i+1) is the student name for that student number
 * This is only because in the excel file the student number comes first, if this changes you will have to change the logic in the parseMemberList method
 * Almost the exact same logic as parseSales method, so look at that if this is confusing
 *
 * @param membershipDictionary: dictionary of student numbers/names where the student number membershipDictionary.get(i) correlates to the student name membershipDictionary.get(i+1)
*///from w  w w. ja  va  2  s  .  co  m
private void parseMembership(ArrayList<String> membershipDictionary) {
    //IOException
    try {
        int iterationCount;
        Double studentNumber;
        String studentString;

        FileInputStream fis = new FileInputStream(membership);

        XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);
        XSSFSheet mySheet = myWorkBook.getSheetAt(0);

        Iterator<Row> rowIterator = mySheet.iterator();
        rowIterator.next();

        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();

            iterationCount = 0;

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

                //This is the only part in which this is different from parseSales method
                //In the current format of the excel sheet, there are some rows in the middle of the sheet which contains column headers, not the data we want
                //In order to get rid of this data, we check if the data in column A is a number,
                //If it isn't, it's column header data and we skip over it by setting the iterationCount to 2 (which is past any data we need)
                if (iterationCount == 0) {
                    //Check if the cellType is numeric by comparing it to 0 because Cell.CELL_TYPE_NUMERIC == 0)
                    if (cell.getCellType() == 0) {
                        studentNumber = cell.getNumericCellValue();
                        studentString = String.format("%.0f", studentNumber);
                        membershipDictionary.add(studentString);
                    } else
                        iterationCount = 2;
                }
                //Get the student name that correlates to the student number we just parsed
                else if (iterationCount == 1) {
                    //Have to replace some super weird unicode garbage from the data
                    membershipDictionary
                            .add(cell.getStringCellValue().replaceAll("\u00a0", "").replaceAll(" ", ""));
                }

                iterationCount++;
            }
        }
        //Testing block, should have a student number followed by a student name for each person in the excel sheet
        //            System.out.println(membershipDictionary.size());
        //            System.out.println(membershipDictionary);
    } catch (IOException e) {

    }
}

From source file:Model.AccountImport.java

public String excelReader(String path_file, List excelColumn) {
    String extension = FilenameUtils.getExtension(path_file);

    if (extension.equalsIgnoreCase(".xlsx")) {
        try {/*  w w w.j av  a2  s.c om*/
            FileInputStream file = new FileInputStream(new File(path_file));
            XSSFWorkbook workbook = new XSSFWorkbook(file);
            XSSFSheet sheet = workbook.getSheetAt(0);
            Iterator<Row> rowIterator = sheet.iterator();
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                Iterator<Cell> cellIterator = row.cellIterator();

                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.print(cell.getNumericCellValue() + " | ");
                        break;
                    case Cell.CELL_TYPE_STRING:
                        System.out.print(cell.getStringCellValue() + " | ");
                        break;
                    }
                }
                System.out.println("");
            }
            file.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    } else if (extension.equalsIgnoreCase(".xls")) {
        try {
            FileInputStream file = new FileInputStream(new File(path_file));
            HSSFWorkbook workbook = new HSSFWorkbook(file);

            HSSFSheet sheet = workbook.getSheetAt(0);

            Iterator<Row> rowIterator = sheet.iterator();
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                Iterator<Cell> cellIterator = row.cellIterator();

                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.print(cell.getNumericCellValue() + " | ");
                        break;
                    case Cell.CELL_TYPE_STRING:
                        System.out.print(cell.getStringCellValue() + " | ");
                        break;
                    }
                }
                System.out.println("");
            }
            file.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    return null;
}

From source file:model.converter.ConvertToInstances.java

public void convertXlsxToCSV(File inputFile, File outputFile) {
    // For storing data into CSV files
    StringBuffer data = new StringBuffer();

    try {//  w ww .j  a  v a2 s  .  c o  m
        FileOutputStream fos = new FileOutputStream(outputFile);
        // Get the workbook object for XLSX file
        XSSFWorkbook wBook = new XSSFWorkbook(new FileInputStream(inputFile));
        // Get first sheet from the workbook
        XSSFSheet sheet = wBook.getSheetAt(0);
        Row row;
        Cell cell;
        // Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.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() + ",");

                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    data.append(cell.getNumericCellValue() + ",");

                    break;
                case Cell.CELL_TYPE_STRING:
                    data.append(cell.getStringCellValue() + ",");
                    break;

                case Cell.CELL_TYPE_BLANK:
                    data.append("" + ",");
                    break;
                default:
                    data.append(cell + ",");

                }

            }
            data.append("\r\n");
        }

        String csvData = data.toString();
        csvData = csvData.replaceAll(",\r\n", "\r\n");
        fos.write(csvData.getBytes());
        fos.close();

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

From source file:mpqq.MPQQ.java

private static void printsheet(XSSFSheet sheet) {
    //Iterate through each rows from first sheet
    Iterator<Row> rowIterator = sheet.iterator();
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();/*from   ww w . j a va 2s  . c om*/

        //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("");
    }
}

From source file:negocio.parser.ExcelReader.java

@Override
public IExcelContent leerArchivo(String ruta) throws Exception {
    java.util.Date date = new java.util.Date();
    Date entrada = new Date(date.getTime());
    IExcelContent ec = ExcelContent.getInstantiateExcelContent();
    try {// www  .  j  a  v  a  2s  .c om
        LogDAO dao = new LogDAO();
        LogDTO dto = new LogDTO("Leer archivo", "Comienzo de lectura de archivo", entrada.toString(),
                entrada.toString());
        dao.registrarLog(dto);
        File archivo = new File(ruta);
        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(archivo)); //crear un libro excel
        XSSFSheet sheet = workbook.getSheetAt(0); //acceder a la primera hoja
        Iterator<Row> rowIterator = sheet.iterator();
        Row row;
        boolean sw = true;
        ArrayList<List<String>> datos = new ArrayList<>();
        while (rowIterator.hasNext()) {

            row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();
            Cell celda;
            List<String> fila = new ArrayList<>();
            while (cellIterator.hasNext()) {
                celda = cellIterator.next();
                String dato = "";
                switch (celda.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    if (HSSFDateUtil.isCellDateFormatted(celda)) {
                        dato = celda.getDateCellValue().toString();
                    } else {
                        dato = celda.getNumericCellValue() + "";
                    }
                    break;
                case Cell.CELL_TYPE_STRING:
                    dato = celda.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    dato = celda.getBooleanCellValue() + "";
                    break;
                }
                fila.add(dato);
            }
            if (sw) {
                sw = false;
                ec.setTitulos(fila);
            } else {
                datos.add(fila);
            }
        }
        ec.setDatos(datos);
        workbook.close();
        return ec;
    } catch (IOException ex) {
        Logger.getLogger(ExcelReader.class.getName()).log(Level.SEVERE, null, ex);
    }
    return null;

}

From source file:net.creativeidesign.timekeeper_v1.util.TaskImport.java

public boolean doImport() {
    try {//from ww  w . ja  v a  2s.com
        JFrame parentFrame = new JFrame();

        FileFilter filter = new FileNameExtensionFilter("MS Excel .xlsx", "xlsx");
        JFileChooser fileChooser = new JFileChooser();
        fileChooser.setDialogTitle("Please select an excel file to import from");
        fileChooser.setFileFilter(filter);

        int userSelection = fileChooser.showSaveDialog(parentFrame);
        File fileToSave = fileChooser.getSelectedFile();
        if (fileToSave == null)
            return false;
        String filePathName = fileToSave.getAbsolutePath();

        FileInputStream file = new FileInputStream(new File(filePathName));

        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        //Get first/desired sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(0);

        importedItems = new ArrayList<>();
        //Iterate through each rows one by one
        Iterator<Row> rowIterator = sheet.iterator();
        rowIterator.next();//skip first row [it should be the header]
        while (rowIterator.hasNext()) {
            ToDoItemModel tmpItem = new ToDoItemModel();
            int iCellCnt = 0;

            Row row = rowIterator.next();
            //For each row, iterate through all the columns
            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) {
                String cellStr = null;
                double cellNum = 0;
                Cell cell = cellIterator.next();
                //Check the cell type and format accordingly
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    cellNum = cell.getNumericCellValue();
                    System.out.print(cell.getNumericCellValue() + "\t");
                    break;
                case Cell.CELL_TYPE_STRING:
                    cellStr = cell.getStringCellValue();
                    System.out.print(cell.getStringCellValue() + "\t");
                    break;
                }//end switch (cell.getCellType())

                switch (iCellCnt) {
                case 0:
                    tmpItem.setiId((int) cellNum);
                    break;
                case 1:
                    tmpItem.setStrTitle(cellStr);
                    break;
                case 2:
                    tmpItem.setStrDescription(cellStr);
                    break;
                case 3:
                    tmpItem.setDtDateUntil(cellStr);
                    break;
                case 4:
                    tmpItem.setiCategory((int) cellNum);
                    break;
                }//end switch(iCellCnt)

                ++iCellCnt;
            }
            System.out.println("");
            importedItems.add(tmpItem);
        }
        file.close();
    } catch (Exception e) {
        e.printStackTrace();
        JOptionPane.showMessageDialog(null, "Wohoo, something went wrong, please try again later", "File Saved",
                JOptionPane.INFORMATION_MESSAGE);
    }

    return compareItemsInDB();
}

From source file:nl.mawoo.wcmscript.modules.excel.ExcelImportV1.java

License:Apache License

/**
 * Gets the iterator holding all the rows of a given sheet
 * @param sheet Sheet which the rows iterator is return from
 * @return Iterator with all the rows from the given sheet
 *///from   w  w  w  .ja  v a 2 s.c om
public Iterator<Row> getRowIterator(XSSFSheet sheet) {
    return sheet.iterator();
}