本文共 5518 字，大约阅读时间需要 18 分钟。

Java读取Excel文件（支持xls，xlsx，多sheet）

1. pom.xml依赖

     
    
     org.apache.poi
        
    
     poi
        
    
     4.0.1
    
   
       
    
     org.apache.poi
        
    
     poi-ooxml
        
    
     4.0.1
    
   
       
    
     org.apache.poi
        
    
     poi-ooxml-schemas
        
    
     4.0.1
    
   

2. 工具类封装

public class ExcelReadUtil {
       private static Logger logger = LoggerFactory.getLogger(ExcelReadUtil.class);	public static HashMap
   
    >> readExcel(File file, int ignoreRow) {
   	        if (file.getName().toLowerCase().endsWith(".xlsx")) {
   	            return readExcelForXlsx(file, ignoreRow);	        } else if (file.getName().toLowerCase().endsWith(".xls")) {
   	            return readExcelForXls(file, ignoreRow);	        }	        return null;	    }    /**     * 读取Excel xlsx后缀名文件数据     *     * @param file     */    private static HashMap
    
     >> readExcelForXlsx(File file, int ignoreRow) {
           HashMap
     
      >> map = new HashMap<>();        if (!file.exists()) {
               logger.error("{}文件不存在", file.getName());            return null;        }        int rowSize = 0;        try (BufferedInputStream in = new BufferedInputStream(new FileInputStream(file))) {
               XSSFWorkbook workbook = null;            try {
                   workbook = new XSSFWorkbook(in);            } catch (IOException e) {
                   e.printStackTrace();            }            XSSFCell cell = null;            for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
                   XSSFSheet sheet = workbook.getSheetAt(sheetIndex);                ArrayList
      
       
        > lists = new ArrayList<>();                for (int rowIndex = ignoreRow; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                       XSSFRow row = sheet.getRow(rowIndex);                    if (null == row) {
                           continue;                    }                    int tempRowSize = row.getLastCellNum() + 1;                    if (tempRowSize > rowSize) {
                           rowSize = tempRowSize;                    }                    ArrayList
        
          list = new ArrayList<>(); int col = 0; for (int colIndex = 0; colIndex <= row.getLastCellNum(); colIndex++) { cell = row.getCell(colIndex); String value = ""; if (cell != null) { CellType cellType = cell.getCellType(); switch (cellType) { case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { value = String.valueOf(cell.getDateCellValue()); } else { value = String.valueOf(new DecimalFormat("0").format(cell.getNumericCellValue())); } break; case STRING: value = String.valueOf(cell.getStringCellValue()); break; case FORMULA: value = String.valueOf(cell.getCellFormula()); break; case BLANK: value = ""; break; case BOOLEAN: value = String.valueOf(cell.getBooleanCellValue()); break; case ERROR: value = String.valueOf(cell.getErrorCellValue()); break; default: value = ""; } if (StringUtils.isNotBlank(value)) { list.add(value); } else { col++; } } } if (col == row.getRowNum()) { continue; } if (list.size() > 0) { lists.add(list); } } map.put("sheet" + sheetIndex, lists); } } catch (Exception e) { e.printStackTrace(); } return map; } /** * 读取excel xls后缀名文件 * * @param file * @param ignoreRow * @return */ private static HashMap
         
          >> readExcelForXls(File file, int ignoreRow) { HashMap
          
           >> map = new HashMap<>(); if (!file.exists()) { logger.error("{}文件不存在", file.getName()); return null; } int rowSize = 0; try { BufferedInputStream bufferedInputStream = new BufferedInputStream(new FileInputStream(file)); HSSFWorkbook workbook = new HSSFWorkbook(bufferedInputStream); HSSFCell cell = null; for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) { HSSFSheet sheet = workbook.getSheetAt(sheetIndex); ArrayList
           
            
             > lists = new ArrayList<>(); for (int rowIndex = ignoreRow; rowIndex < sheet.getLastRowNum(); rowIndex++) { HSSFRow row = sheet.getRow(rowIndex); if (null == row) { continue; } int tempRowSize = row.getLastCellNum() + 1; if (tempRowSize > rowSize) { rowSize = tempRowSize; } ArrayList
             
               list = new ArrayList<>(); int col = 0; for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) { cell = row.getCell(colIndex); String value = ""; if (cell != null) { CellType cellType = cell.getCellType(); switch (cellType) { case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { value = String.valueOf(cell.getDateCellValue()); } else { value = String.valueOf(new DecimalFormat("0").format(cell.getNumericCellValue())); } break; case STRING: value = String.valueOf(cell.getStringCellValue()); break; case FORMULA: value = String.valueOf(cell.getCellFormula()); break; case BLANK: value = ""; break; case BOOLEAN: value = String.valueOf(cell.getBooleanCellValue()); break; case ERROR: value = String.valueOf(cell.getErrorCellValue()); break; default: value = ""; } if (StringUtils.isNotBlank(value)) { list.add(value); } else { col++; } } } if (col == row.getRowNum()) { continue; } if (list.size() > 0) { lists.add(list); } } map.put("sheet" + sheetIndex, lists); } } catch (Exception e) { e.printStackTrace(); } return map; }}
             
            
           
          
         
        
       
      
     
    
   

3. 使用示例说明

@Testpublic void testExcelRead(){
   HashMap
   
    >> excelReadMap = ExcelReadUtil.readExcel(new File(excelFilePath), 1);	if(excelReadMap != null){
   		excelReadMap.entrySet().stream().forEach(entry -> {
   		            entry.getValue().stream().forEach(col -> {
   		 				col.stream().forEach(System.out::println);		 	});		 });	} }
   

转载地址：http://rzixi.baihongyu.com/