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:testpoi.POI_POC.java

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

        FileInputStream file = new FileInputStream(
                new File("/home/chandni/Documents/HMS Docs/01.01.2014.xlsx"));

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

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

        //Get iterator to all the rows in current 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();

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

From source file:testpoi.ReadExcelRowsRandomly.java

License:Open Source License

public static void main(String args[]) {
    try {/*from w  ww  .  j  av  a2  s .  c o m*/

        FileInputStream file = new FileInputStream(new File(
                "C:\\Documents and Settings\\Admin\\My Documents\\NetBeansProjects\\TestPOI\\Docs\\1.xlsx"));

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

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

        double random = Math.random();
        int rowNum = (int) (random * sheet.getPhysicalNumberOfRows());

        Row row = sheet.getRow(rowNum);

        Iterator<Cell> cellIterator = row.cellIterator();

        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                double cellValue = cell.getNumericCellValue();
                System.out.print(cellValue + "\t");
            } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                String cellValue = cell.getStringCellValue();
                System.out.print(cellValue + "\t");
            }
        }
        System.out.println();

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

From source file:testpoi.SplitOldDepartmentwise.java

License:Open Source License

private static void createDepartmentwiseSheets() {
    HashMap<String, Integer> depttMap = new HashMap<>();
    depttMap.put("Medicine", 1);
    depttMap.put("Surgery", 2);
    depttMap.put("Obs & Gynae", 3);
    depttMap.put("Paediatrics", 4);
    depttMap.put("Orthopaedics", 5);
    depttMap.put("Ophthalmology", 6);
    depttMap.put("ENT", 7);
    depttMap.put("Dental", 8);
    depttMap.put("Casualty", 9);

    int depttSheetCreateFlag = 0;

    Iterator<Row> rowIterator = sheetAllOld.rowIterator();
    //Store the first row to be printed as it is.
    ArrayList<String> heading = new ArrayList<>();
    Row row = rowIterator.next();
    Iterator<Cell> cellIterator = row.cellIterator();
    while (cellIterator.hasNext())
        heading.add(cellIterator.next().getStringCellValue());

    int rowNums[] = { 1, 1, 1, 1, 1, 1, 1, 1, 1 };
    while (rowIterator.hasNext()) {
        row = rowIterator.next();//from w  w w  .jav  a2 s .c o m
        XSSFSheet sheetToWrite = null;

        Cell cell = row.getCell(0);
        if ((depttSheetCreateFlag & 1 << (depttMap.get(cell.getStringCellValue()))) == 0) {
            //that means this deptt came in this sheet for the first time in this row.
            XSSFSheet sheet = sheets[depttMap.get(cell.getStringCellValue()) - 1] = workbookOld
                    .createSheet(cell.getStringCellValue());
            //create heading row in this sheet
            Row headingRow = sheet.createRow(0);
            for (int i = 0; i < heading.size(); i++) {
                String cellString = heading.get(i);
                Cell headingCell = headingRow.createCell(i);
                headingCell.setCellValue(cellString);//sets cell type to string too
            }
            //mark this deptt. as seen
            depttSheetCreateFlag |= (1 << (depttMap.get(cell.getStringCellValue())));
        }

        int sheetNum = depttMap.get(cell.getStringCellValue()) - 1;
        sheetToWrite = sheets[sheetNum];
        assert (sheetToWrite != null);

        //write row to sheetToWrite
        Row rowNew = sheetToWrite.createRow(rowNums[sheetNum]++);

        cellIterator = row.cellIterator();
        int cellNum = 0;
        while (cellIterator.hasNext()) {
            cell = cellIterator.next();

            //write cell
            Cell cellNew = rowNew.createCell(cellNum++);
            String cellValue;
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)
                cellValue = (int) (cell.getNumericCellValue()) + "";
            else
                cellValue = cell.getStringCellValue();
            cellNew.setCellValue(cellValue);
        }
    }
}

From source file:testpoi_.OPDDataTransfer.java

License:Open Source License

public static void main(String args[]) {
    try {/* www.  ja  v  a 2s  .co m*/

        FileInputStream file = new FileInputStream(
                new File("C:\\Documents and Settings\\Admin\\My Documents\\NetBeansProjects\\TestPOI\\Docs\\"
                        + dateFolder + "\\" + dateFolder + ".xlsx"));

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

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

        Connection conn = connectToDatabaseHMS();
        assert (conn != null);
        Connection conn2 = connectToDatabaseHMSSecurity();
        assert (conn2 != null);

        try {
            conn.setAutoCommit(false);
            conn2.setAutoCommit(false);
        } catch (SQLException sqle) {
            System.err.println("Could not set autocommit to false");
            sqle.printStackTrace();
        }

        //Get iterator to all the rows in current sheet
        Iterator<Row> rowIterator = sheet.iterator();

        //Skip the 1st row
        rowIterator.next();

        /*************************** TO UPDATE ON ERROR ******************************/
        //set time
        time = Time.valueOf("09:00:00");
        //set entry number default to 1.
        entryNumber = 1;

        /*****************************************************************************/

        //            //set entry crNo
        //            entryCrNo = 1;
        Timestamp timestamp = new Timestamp(date.getTime() + time.getTime() + 19800000/*for IST*/);
        System.out.println(timestamp.toString());

        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();

            //For each row, get values of each column
            Iterator<Cell> cellIterator = row.cellIterator();

            Cell cell = cellIterator.next();
            String dept;
            if (cell.getCellType() == 1)
                dept = cell.getStringCellValue();
            else {
                int no = (int) cell.getNumericCellValue();
                dept = no + "";
                System.out.println(dept);
            }
            cell = cellIterator.next();
            String type = cell.getStringCellValue();
            cell = cellIterator.next();

            int crNo;
            if (cell.getCellType() == 0)
                crNo = (int) (cell.getNumericCellValue());
            else {
                System.out.println("crNo cell value: " + cell.getStringCellValue());
                crNo = (int) Integer.parseInt(cell.getStringCellValue().trim());
            }

            cell = cellIterator.next();
            String name = cell.getStringCellValue();
            cell = cellIterator.next();
            String guardian = cell.getStringCellValue();
            cell = cellIterator.next();
            String rel = cell.getStringCellValue();
            cell = cellIterator.next();
            System.out.println("\n cell.getCellType() :" + cell.getCellType());
            int ageYrs = 0;
            if (cell.getCellType() == 0)
                ageYrs = (int) (cell.getNumericCellValue());
            else {
                System.out.println("age cell value: " + cell.getStringCellValue());
                ageYrs = (int) Integer.parseInt(cell.getStringCellValue().trim());
            }

            cell = cellIterator.next();
            String gender = cell.getStringCellValue();
            cell = cellIterator.next();
            String add = cell.getStringCellValue();
            cell = cellIterator.next();
            String city = cell.getStringCellValue();
            cell = cellIterator.next();
            String state = cell.getStringCellValue();

            int deptID = getDeptID(dept);
            assert (deptID != 0);
            int stateID = getStateID(state);
            assert (stateID != 0);
            int drID = getDrID(deptID);
            assert (drID != 0); // if drID = 0 that means a dept. has been entered which doesn't have a doctor
            String loginUserName = getLoginUserName();
            String userID = getLoginUserID(loginUserName);

            long OPDNo = Long.parseLong(OPDDATE + entryNumber);

            boolean queryExecuted = true;
            if (type.equals("New")) // As only New patients must be registered and have a CR generated
            {
                try {
                    String insertSql = "INSERT INTO Reg "
                            + "(Regno, Name, Fname, Relation, AgeY, Sex, Address1, City, State, Department, Date)"
                            + "VALUES(" + crNo + ",'" + name + "','" + guardian + "','" + rel + "'," + ageYrs
                            + ",'" + gender + "','" + add + "','" + city + "'," + stateID + "," + deptID + ",'"
                            + date + "')";
                    System.out.println(insertSql);
                    Statement st = conn.createStatement();
                    int val = st.executeUpdate(insertSql);
                    System.out.println("One row in Reg gets affected...");

                } catch (SQLException ex) {
                    queryExecuted = false;
                    System.out.println("Cannot insert row into Reg...!!");
                    ex.printStackTrace();
                }
            }
            try {
                String insertSql = "INSERT INTO OPD "
                        + "(OPDNo, CrNo, PatientType, DepartmentId, DrId, Date, Time, LoginUserName, IsActive)"
                        + "VALUES(" + OPDNo + "," + crNo + ",'" + type + "'," + deptID + "," + drID + ",'"
                        + date + "','" + time + "','" + loginUserName + "','" + true + "')";
                System.out.println(insertSql);
                Statement st = conn.createStatement();
                int val = st.executeUpdate(insertSql);
                System.out.println("One row in OPD gets affected...");
            } catch (SQLException ex) {
                queryExecuted = false;
                System.out.println("Cannot insert row into OPD...!!");
                ex.printStackTrace();
            }

            try {
                String updateSql = "UPDATE aspnet_Users " + "SET LastActivityDate='" + timestamp + "'"
                        + "WHERE UserId='" + userID + "'";
                System.out.println(updateSql);
                Statement st = conn2.createStatement();
                int val = st.executeUpdate(updateSql);
                System.out.println("One row in aspnet_Users gets affected...");
            } catch (SQLException ex) {
                queryExecuted = false;
                System.out.println("Cannot update timestamp in aspnet_Users...!!");
                ex.printStackTrace();
            }

            if (!queryExecuted)
                //if insertion to any table fails, rollback.
                try {
                    conn.rollback();
                    conn2.rollback();

                    break; // and run program again at any error
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            else
                try {
                    conn.commit();
                    conn2.commit();

                    entryNumber++;
                    //                        entryCrNo++;
                    time = new Time(time.getTime() + 50000);//add 50 seconds
                    timestamp = new Timestamp(timestamp.getTime() + 50000);
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }

        }
        file.close();

        if (conn != null) {
            try {
                // close() releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released.
                conn.close();
                System.out.println("HMS Database connection terminated...!!!");
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        if (conn2 != null) {
            try {
                // close() releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released.
                conn2.close();
                System.out.println("HMS_Security Database connection terminated...!!!");
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }

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

From source file:Tools.CompareDrotVSRoster.java

public void colorXLSXFile(String leaveXlsxRoster) throws FileNotFoundException, IOException {
    File xlsxFile = new File(leaveXlsxRoster);
    try {//from www  . ja v  a2  s  .  com
        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;
            // 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) { // 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);
                    foregroundColorSetUpV1(row, myWorkBook); //check each row and update foreground color
                }
                fis.close();
                FileOutputStream output;
                String targetFile = null;
                String[] names = leaveXlsxRoster.split("\\.");
                if (!names[0].equals(leaveXlsxRoster)) { //doesn't contain surfix
                    targetFile = names[0] + "COLORED.xlsx";
                } else {
                    targetFile = leaveXlsxRoster + "COLORED.xlsx";
                }
                output = new FileOutputStream(targetFile);
                myWorkBook.write(output);
                output.close();
                //myWorkBook.write(output);

            } else if (numCell == GlobalVar.LEAVE_TITLES_V2.length) { // full ssn roster
                System.out.println("CompareDrotVsRoster.java: Unsupported.");
                JOptionPane.showMessageDialog(null, "XLSX file doesn't contain correct info! "
                        + "CompareDrotVsRoster.java: Title V2 is not supported");
            } 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.");
        // finds the work book in stance for XLSX file
    } catch (FileNotFoundException ex) {
        JOptionPane.showMessageDialog(null, "Xlsx file not found!");
    }

}

From source file:tools.parsing.ExcelReader.java

public void readExcel() {
    try {/*from ww w  . j ava 2s  .  co m*/
        FileInputStream file = new FileInputStream(new File(this.filePath));
        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();
            Comment comment = new Comment();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    if (cell.getColumnIndex() == 0)
                        comment.setTopic(cell.getNumericCellValue() + "");
                    else if (cell.getColumnIndex() == 1)
                        comment.setCode(cell.getNumericCellValue() + "");
                    else if (cell.getColumnIndex() == 2)
                        comment.setAuthor(cell.getNumericCellValue() + "");
                    else if (cell.getColumnIndex() == 3)
                        comment.setDate(cell.getNumericCellValue() + "");
                    else
                        comment.setComment(cell.getNumericCellValue() + "");
                    break;
                case Cell.CELL_TYPE_STRING:
                    if (cell.getColumnIndex() == 0)
                        comment.setTopic(cell.getStringCellValue());
                    else if (cell.getColumnIndex() == 1)
                        comment.setCode(cell.getStringCellValue());
                    else if (cell.getColumnIndex() == 2)
                        comment.setAuthor(cell.getStringCellValue());
                    else if (cell.getColumnIndex() == 3)
                        comment.setDate(cell.getStringCellValue());
                    else
                        comment.setComment(cell.getStringCellValue());
                    break;
                }
            }
            this.comments.add(comment);
        }
        file.close();
    } catch (IOException e) {
        Logger.getLogger(ExcelReader.class.getName()).log(Level.SEVERE, null, e);
    }
}

From source file:Tools.PostProcessing.java

private Map<String, Integer> generateXLSXforS1(String XLSXfileName) throws FileNotFoundException, IOException {
    Map<String, Integer> statusMap = null;
    File myFile = new File(XLSXfileName);

    FileInputStream fis = new FileInputStream(myFile);
    XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);

    //Return first sheet from the XLSX workbook
    XSSFSheet mySheet = myWorkBook.getSheetAt(0);
    List<String> errorList = new ArrayList<>();
    Map<String, Map<String, List<String>>> leaves = new HashMap<String, Map<String, List<String>>>();
    //Get iterator to all the rows in current sheet
    Iterator<Row> rowIterator = mySheet.iterator();
    int lineCount = 1;
    // 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<String>(); //keep track info of each column

        while (it.hasNext()) {
            keyList.add(it.next().getStringCellValue()); // add the title in xlsx to keyList                  
            numCell++;/*w w  w  . j av a 2 s .  co  m*/
        }
        if (numCell == GlobalVar.LEAVE_TITLES_V2.length) {
            // System.out.println("XLSX2BatchHandler.java: V2, UCFR is not loaded.");
            //                int globalCount = 1;
            DataFormatter df = new DataFormatter();
            statusMap = new TreeMap<>();
            while (rowIterator.hasNext()) {
                lineCount++;
                Row row = rowIterator.next();
                Cell ctrlNumCell = row.getCell(GlobalVar.CTRL_NUM_CELL_INDEX_V2);
                Cell ssnCell = row.getCell(GlobalVar.FULL_SSN_CELL_INDEX_V2);
                String fullSSN = GlobalVar.fullSSNgenerator(df.formatCellValue(ssnCell));
                colorDeletedLeaves(fullSSN, ctrlNumCell, myWorkBook, statusMap);
            }
            // output to a new xlsx file
            fis.close();
            FileOutputStream output;
            String targetFile = null;
            if (XLSXfileName.contains(".xlsx")) {
                targetFile = XLSXfileName.replace(".xlsx", "_forS1.xlsx");
            } else {
                targetFile = XLSXfileName + "_forS1.xlsx";
            }
            output = new FileOutputStream(targetFile);
            myWorkBook.write(output);
            output.close();

        } else {
            JOptionPane.showMessageDialog(null, "XLSX file format is incorrect! Must be full SSN format");
        }
    }
    return statusMap;
}

From source file:Tools.PreProcessing.java

private List<String> readXlsxFile(String xlsxFileName) throws IOException {
    File myFile = new File(xlsxFileName);
    List<String> list = new ArrayList<>(); //store ctrlNum + GlobalVar.PRE_PROC_KEY_SYMBOL + fullSSN that printed on the pdf file
    try {/*from w  w  w. java 2 s . c o  m*/
        FileInputStream fis = new FileInputStream(myFile);
        XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);

        //Return first sheet from the XLSX workbook
        XSSFSheet mySheet = myWorkBook.getSheetAt(0);
        List<String> errorList = new ArrayList<>();
        Map<String, Map<String, List<String>>> leaves = new HashMap<String, Map<String, List<String>>>();
        //Get iterator to all the rows in current sheet
        Iterator<Row> rowIterator = mySheet.iterator();
        int lineCount = 1;
        // 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<String>(); //keep track info of each column

            while (it.hasNext()) {
                keyList.add(it.next().getStringCellValue()); // add the title in xlsx to keyList                  
                numCell++;
            }

            if (numCell == GlobalVar.LEAVE_TITLES_V2.length) {
                System.out.println("XLSX2BatchHandler.java: V2, UCFR is not loaded.");
                int globalCount = 1;
                while (rowIterator.hasNext()) {
                    //int cellCount = 0;             
                    lineCount++;

                    Row row = rowIterator.next();
                    Cell cell = row.getCell(GlobalVar.CTRL_NUM_CELL_INDEX_V2);
                    String ctrlNumString = cell.getStringCellValue();
                    if (!ctrlNumString.equalsIgnoreCase("")) { //skip empty lines
                        Iterator<Cell> cellIterator = row.cellIterator();
                        Map<String, String> rowContainer = new HashMap<>(); //store info of each row                                       
                        rowContainerBuilder(rowContainer, keyList, cellIterator); // update rowContainer

                        //    public static final String[] SIGNED_LEAVE_TITLES = {"Ctrl Number", 
                        //            "SSN", "Last Name", "Sign-in Date", "Sign-out Date", "Leave Area",
                        //        "Leave Type", "Num of Days", "First Five","Projected Sign-in Date", "Projected Sign-out Date"};
                        //      make sure the key mataches the name in the header                        
                        String ctrlNum = GlobalVar
                                .readCtrlNum(rowContainer.get(keyList.get(GlobalVar.CTRL_NUM_CELL_INDEX_V2)));
                        //String ctrlNum = GlobalVar.readCtrlNum(rowContainer.get(keyList.get(GlobalVar.CTRL_NUM_CELL_INDEX_V2))); 
                        if (ctrlNum == null) {
                            JOptionPane.showMessageDialog(null,
                                    "Line " + lineCount + ": Invalid ctrl number received.");
                        }
                        // String lastName = rowContainer.get(keyList.get(GlobalVar.LAST_NAME_CELL_INDEX_V2));
                        String fullSSN = rowContainer.get(keyList.get(GlobalVar.FULL_SSN_CELL_INDEX_V2)); // full ssn                                
                        list.add(ctrlNum + GlobalVar.PRE_PROC_KEY_SYMBOL + fullSSN);

                    }
                }
                fis.close();

            } else {

                JOptionPane.showMessageDialog(null, "XLSX file doesn't contain correct format!");
            }
        } else {
            JOptionPane.showMessageDialog(null, "XLSX file is empty!");
            System.out.println("The xlsx file is empty!");
        }
        // finds the work book in stance for XLSX file

    } catch (FileNotFoundException ex) {
        JOptionPane.showMessageDialog(null, "XLSX2BatchHandler.java: Xlsx file not found!");
        Logger.getLogger(XLSX2BatchHandler.class.getName()).log(Level.SEVERE, null, ex);
    }
    return list;
}

From source file:ultis.ReadFile.java

private static ArrayList<String> readXlsxFile(String path) throws FileNotFoundException, IOException {
    ArrayList<String> lines = new ArrayList<>();

    // Create file input stream from input file
    FileInputStream fis = new FileInputStream(path);
    // Create Workbook instance holding reference to input file
    XSSFWorkbook workbook = new XSSFWorkbook(fis);

    // Loop for all sheets
    for (int i = 0; i < workbook.getNumberOfSheets(); ++i) {
        XSSFSheet sheet = workbook.getSheetAt(i);
        // Iterate through each row in input file
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();
            String line = "";
            line += cellIterator.next().getStringCellValue().trim();
            line += "|";
            line += cellIterator.next().getStringCellValue().trim();
            lines.add(line);//  w w w.  j  a  va2  s  .  c om
        }
    }
    fis.close();

    return lines;
}

From source file:update2viva.ConvertXLSX.java

static void readXls(File inputFile) {
    try {/*from  w  w w. j av  a 2 s .c o  m*/
        // Get the workbook instance for XLS file
        HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(inputFile));
        // Get first sheet from the workbook
        HSSFSheet sheet = workbook.getSheetAt(0);
        Cell cell;
        Row row;

        // 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:
                    System.out.println(cell.getBooleanCellValue());
                    break;

                case Cell.CELL_TYPE_NUMERIC:
                    System.out.println(cell.getNumericCellValue());
                    break;

                case Cell.CELL_TYPE_STRING:
                    System.out.println(cell.getStringCellValue());
                    break;

                case Cell.CELL_TYPE_BLANK:
                    System.out.println(" ");
                    break;

                default:
                    System.out.println(cell);
                }
            }
        }

    }

    catch (FileNotFoundException e) {
        System.err.println("Exception" + e.getMessage());
    } catch (IOException e) {
        System.err.println("Exception" + e.getMessage());
    }
}