依赖
< dependency>
< groupId> org.apache.poi</ groupId>
< artifactId> poi</ artifactId>
< version> 4.1.2</ version>
</ dependency>
< dependency>
< groupId> org.apache.poi</ groupId>
< artifactId> poi-ooxml</ artifactId>
< version> 4.1.2</ version>
</ dependency>
工具类
public class ApachePoiUtil {
private static final Logger log = LoggerFactory. getLogger ( ApachePoiUtil. class ) ;
public static List< String[ ] > readExcel ( String fileName, InputStream inputStream, String sheetName, Integer startRow, Integer endRow, Integer startCol, Integer endCol) throws IOException {
Workbook workbook = getWorkbook ( fileName, inputStream) ;
if ( workbook == null) {
inputStream. close ( ) ;
throw new IOException ( "The file name suffix is not .xls or .XLSX or .xls or .XLSX" ) ;
}
Sheet sheet = workbook. getSheet ( sheetName) ;
int lastRowNum = sheet. getLastRowNum ( ) ;
if ( startRow == null || startRow < 0 ) {
startRow = 0 ;
}
if ( startRow > lastRowNum) {
workbook. close ( ) ;
return null;
}
if ( endRow == null) {
endRow = lastRowNum;
}
if ( endRow < startRow) {
workbook. close ( ) ;
return null;
} else {
endRow = Math. min ( lastRowNum, endRow) ;
}
if ( startCol == null || startCol < 0 ) {
startCol = 0 ;
}
if ( endCol == null || endCol < startCol) {
workbook. close ( ) ;
throw new IOException ( "endCol is illegal" ) ;
}
int length = endCol - startCol + 1 ;
ArrayList< String[ ] > rows = new ArrayList < > ( length * 4 / 3 ) ;
for ( Integer i = startRow; i <= endRow; i++ ) {
Row row = sheet. getRow ( i) ;
String[ ] cols = new String [ length] ;
for ( int j = startCol; j <= endCol; j++ ) {
Cell cell = row. getCell ( j) ;
cols[ j] = getContentByCellType ( cell) ;
}
rows. add ( cols) ;
}
workbook. close ( ) ;
return rows;
}
private static String getContentByCellType ( Cell cell) {
String value = null;
switch ( cell. getCellType ( ) ) {
case NUMERIC:
if ( DateUtil. isCellDateFormatted ( cell) ) {
SimpleDateFormat sdf = new SimpleDateFormat ( "yyyy-MM-dd HH:mm:ss" ) ;
value = sdf. format ( DateUtil. getJavaDate ( cell. getNumericCellValue ( ) ) ) ;
break ;
} else {
value = Double. toString ( cell. getNumericCellValue ( ) ) ;
}
break ;
case STRING:
value = cell. getStringCellValue ( ) ;
break ;
case BOOLEAN:
value = Boolean. toString ( cell. getBooleanCellValue ( ) ) ;
break ;
case FORMULA:
value = cell. getCellFormula ( ) ;
break ;
case BLANK:
break ;
case ERROR:
break ;
default :
break ;
}
return value;
}
private static boolean isExcel2003 ( String filePath) {
return filePath. matches ( "^.+\\.(?i)(xls)$" ) ;
}
private static boolean isExcel2007 ( String filePath) {
return filePath. matches ( "^.+\\.(?i)(xlsx)$" ) ;
}
private static Workbook getWorkbook ( String fileName, InputStream inputStream) throws IOException {
if ( isExcel2007 ( fileName) ) {
return new XSSFWorkbook ( inputStream) ;
}
if ( isExcel2003 ( fileName) ) {
return new HSSFWorkbook ( inputStream) ;
}
return null;
}
}