Java POI Excel读取

红太狼 2022-06-02 23:08 240阅读 0赞

**POI版本3.15**

**pom文件**

<dependency>
    	<groupId>org.apache.poi</groupId>
    	<artifactId>poi</artifactId>
    	<version>3.15</version>
    </dependency>
    <dependency>
    	<groupId>org.apache.poi</groupId>
    	<artifactId>poi-ooxml</artifactId>
    	<version>3.15</version>
    </dependency>

**Excel文件**：

![Center][]

Java代码实现：

/**
     * 
     */
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.text.SimpleDateFormat;
    
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    /**
     *
     * Description: Excel操作
     * 
     * CreateTime: 2017年12月11日  下午3:08:09
     *
     * Change History:
     *
     *        Date             CR Number              Name              Description of change
     *
     */
    public class ExcelUtil {
    
    	private static final String EXCEL_XLS = "xls";  
        private static final String EXCEL_XLSX = "xlsx";  
      
        /** 
         * 判断Excel的版本,获取Workbook 
         * @param in 
         * @param filename 
         * @return 
         * @throws IOException 
         */  
        public static Workbook getWorkbok(InputStream in,File file) throws IOException{  
            Workbook wb = null;  
            if(file.getName().endsWith(EXCEL_XLS)){  //Excel 2003  
                wb = new HSSFWorkbook(in);  
            }else if(file.getName().endsWith(EXCEL_XLSX)){  // Excel 2007/2010  
                wb = new XSSFWorkbook(in);  
            }  
            return wb;  
        }  
      
        /** 
         * 判断文件是否是excel 
         * @throws Exception  
         */  
        public static void checkExcelVaild(File file) throws Exception{  
            if(!file.exists()){  
                throw new Exception("文件不存在");  
            }  
            if(!(file.isFile() && (file.getName().endsWith(EXCEL_XLS) || file.getName().endsWith(EXCEL_XLSX)))){  
                throw new Exception("文件不是Excel");  
            }  
        }  
        
        /** 
         * 读取Excel测试，兼容 Excel 2003/2007/2010 
         * @throws Exception  
         */  
        public static void main(String[] args) throws Exception {  
            SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");  
            try {  
                // 同时支持Excel 2003、2007  
                File excelFile = new File("d:/product.xlsx"); // 创建文件对象  
                FileInputStream in = new FileInputStream(excelFile); // 文件流  
                checkExcelVaild(excelFile);  
                Workbook workbook = getWorkbok(in,excelFile);  
                //Workbook workbook = WorkbookFactory.create(is); // 这种方式 Excel2003/2007/2010都是可以处理的  
      
                int sheetCount = workbook.getNumberOfSheets(); // Sheet的数量  
                /** 
                 * 设置当前excel中sheet的下标：0开始 
                 */  
    //            Sheet sheet = workbook.getSheetAt(0);   // 遍历第一个Sheet  
                Sheet sheet = workbook.getSheetAt(2);   // 遍历第三个Sheet  
                
                //获取总行数
    //          System.out.println(sheet.getLastRowNum());
                
                // 为跳过第一行目录设置count  
                int count = 0;
                for (Row row : sheet) {
                	try {
                		// 跳过第一和第二行的目录  
                        if(count < 2 ) {
                            count++;  
                            continue;  
                        }
                        
                        //如果当前行没有数据，跳出循环  
                        if(row.getCell(0).toString().equals("")){  
                        	return;
                        }
                        
                        //获取总列数(空格的不计算)
                        int columnTotalNum = row.getPhysicalNumberOfCells();
                        System.out.println("总列数：" + columnTotalNum);
                        
                        System.out.println("最大列数：" + row.getLastCellNum());
                        
                        //for循环的，不扫描空格的列
    //                    for (Cell cell : row) { 
    //                    	System.out.println(cell);
    //                    }
                        int end = row.getLastCellNum();
                        for (int i = 0; i < end; i++) {
                        	Cell cell = row.getCell(i);
                        	if(cell == null) {
                        		System.out.print("null" + "\t");
                        		continue;
                        	}
                        	
                        	Object obj = getValue(cell);
                        	System.out.print(obj + "\t");
    					}
    				} catch (Exception e) {
    					e.printStackTrace();
    				}
                }  
            } catch (Exception e) {  
                e.printStackTrace();  
            }
        }
        
        private static Object getValue(Cell cell) {
        	Object obj = null;
        	switch (cell.getCellTypeEnum()) {
    	        case BOOLEAN:
    	            obj = cell.getBooleanCellValue(); 
    	            break;
    	        case ERROR:
    	            obj = cell.getErrorCellValue(); 
    	            break;
    	        case NUMERIC:
    	            obj = cell.getNumericCellValue(); 
    	            break;
    	        case STRING:
    	            obj = cell.getStringCellValue(); 
    	            break;
    	        default:
    	            break;
        	}
        	return obj;
        }
    }

若是导入文件太大出现**内存溢出**，参考[https://blog.csdn.net/wwd0501/article/details/91626652][https_blog.csdn.net_wwd0501_article_details_91626652]实现

[Center]: /images/20220603/a47c7e29e31d42eea03dc87a13fadbd5.png
[https_blog.csdn.net_wwd0501_article_details_91626652]: https://blog.csdn.net/wwd0501/article/details/91626652