POI Excel 04

@ Author YHC

Use Java1.5 foreach to iterate through all rows and cells

Sometimes you only want to iterate through all rows in the working thin, or all cells in the Row. If you are using Java 5 or a later version, this is very easy to retrieve and traverse.

Because it will allow a new foreach loop to support work, It is very lucky, it is very simple, Sheet and Row both implement java. lang. Iterable Interface Support foreach, for Row (Row)

Allow access to the internal class of CellIterator to support iteration of all cells, and provide rowIterator () for working thin (Sheet) to iterate all rows.

[Java]
// Obtain the working thin object under the first 0 mark based on the Workbook
Sheet sheet = wb. getSheetAt (0 );
// Traverse all rows in the workbook
For (Row row: sheet ){
// Traverse all cells in a row
For (Cell cell: row ){
// Code for implementing the Function
}
}

// Obtain the working thin object under the first 0 mark based on the Workbook
Sheet sheet = wb. getSheetAt (0 );
// Traverse all rows in the workbook
For (Row row: sheet ){
// Traverse all cells in a row
For (Cell cell: row ){
// Code for implementing the Function
}
}
Get cell content

To get the content in a cell, you first need to know the type of the Cell (request a string-type cell. If its content is of the numeric type, this exception NumberFormatException is thrown, for example ), you will want to convert the cell type and call the appropriate getter method to get the cell.

In the following code, we will traverse all cells in a working thin table, convert the output through CellReference, and then output the Cell content;

// Import org. apache. poi. ss. usermodel. * // remember to import this package

 

[Java]
// Create a file
File file = new File ("D:/workbook.xls ");
// Initialize the input stream
FileInputStream is = new FileInputStream (file );
// Create an Excel file and specify the Excel reading location
Workbook wb = new HSSFWorkbook (is );
// Obtain the 0th lower-end workbooks Based on the Workbook
Sheet sheet1 = wb. getSheetAt (0 );
// Traverse all rows in the working thin table. Note that this foreach is only supported by Java 5 or later.
For (Row row: sheet1 ){
// You can use Java 5 + to traverse all cells in the row.
For (Cell cell: row ){
// Cell reference: determines the position of a cell based on rows and columns
CellReference cellRef = new CellReference (row. getRowNum (), cell. getColumnIndex ());
// Print the cell position
System. out. print (cellRef. formatAsString ());
// Output Separator
System. out. print ("-");
// Obtain the cell type
Switch (cell. getCellType ()){
Case Cell. CELL_TYPE_STRING: // String type Cell
// Output text
System. out. println (cell. getRichStringCellValue (). getString ());
Break;
Case Cell. CELL_TYPE_NUMERIC: // numeric type
// Check whether the cell contains a Date type
// Only check the internal Date Format of Excel,
If (DateUtil. isCellDateFormatted (cell )){
// Output date
System. out. println (cell. getDateCellValue ());
} Else {
// Output number
System. out. println (cell. getNumericCellValue ());
}
Break;
Case Cell. CELL_TYPE_BOOLEAN: // Boolean Type
System. out. println (cell. getBooleanCellValue ());
Break;
Case Cell. CELL_TYPE_FORMULA: // Formula
// Output Formula
System. out. println (cell. getCellFormula ());
Break;
Default:
System. out. println ();
}
}
}

// Create a file www.2cto.com
File file = new File ("D:/workbook.xls ");
// Initialize the input stream
FileInputStream is = new FileInputStream (file );
// Create an Excel file and specify the Excel reading location
Workbook wb = new HSSFWorkbook (is );
// Obtain the 0th lower-end workbooks Based on the Workbook
Sheet sheet1 = wb. getSheetAt (0 );
// Traverse all rows in the working thin table. Note that this foreach is only supported by Java 5 or later.
For (Row row: sheet1 ){
// You can use Java 5 + to traverse all cells in the row.
For (Cell cell: row ){
// Cell reference: determines the position of a cell based on rows and columns
CellReference cellRef = new CellReference (row. getRowNum (), cell. getColumnIndex ());
// Print the cell position
System. out. print (cellRef. formatAsString ());
// Output Separator
System. out. print ("-");
// Obtain the cell type
Switch (cell. getCellType ()){
Case Cell. CELL_TYPE_STRING: // String type Cell
// Output text
System. out. println (cell. getRichStringCellValue (). getString ());
Break;
Case Cell. CELL_TYPE_NUMERIC: // numeric type
// Check whether the cell contains a Date type
// Only check the internal Date Format of Excel,
If (DateUtil. isCellDateFormatted (cell )){
// Output date
System. out. println (cell. getDateCellValue ());
} Else {
// Output number
System. out. println (cell. getNumericCellValue ());
}
Break;
Case Cell. CELL_TYPE_BOOLEAN: // Boolean Type
System. out. println (cell. getBooleanCellValue ());
Break;
Case Cell. CELL_TYPE_FORMULA: // Formula
// Output Formula
System. out. println (cell. getCellFormula ());
Break;
Default:
System. out. println ();
}
}
}

The Excel content is as follows: click me in POI Excel 02 when this file is created !!!, Click this hyperlink!

 

 

Read the file and compare it with the result of running the Code:

Author: yhc13429826359