java使用poi技术读取excel表

阳光穿透心脏的1/2处 2022-05-22 11:12 144阅读 0赞

package priv.lg.bs.utils;
    
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.io.InputStream;
    import java.util.ArrayList;
    import java.util.List;
    
    import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
    import org.apache.poi.openxml4j.opc.OPCPackage;
    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;
    
    
    
    public class ReadExcel {
    	
    	public List<List<String[]>> readXLS(InputStream input){
    		List<List<String[]>> list = new ArrayList<List<String[]>>();
    		try {
    			OPCPackage op = OPCPackage.open(input);
    
    			XSSFWorkbook wb =new XSSFWorkbook(op);
    			
    			//获取excel表中的sheet表
    			for(int sheetNum=0;sheetNum<wb.getNumberOfSheets();sheetNum++){
    				Sheet sheet = wb.getSheetAt(sheetNum);
    				
    				if(sheet==null)
    					continue;
    				//获取表中的行
    				/*for(Row row:sheet){
    					if(row==null)
    						continue;
    					for(Cell cell:row){
    						if(cell==null)
    							continue;
    						
    						//System.out.println(cell);
    						//System.out.println(cell.getStringCellValue());
    						//System.out.println(cell.getColumnIndex()+"--"+cell.getRowIndex());
    						//for(int rowIndex=0;rowIndex<cell.get)
    					}
    				}*/
    				//System.out.println("sheet.getLastRowNum()"+sheet.getLastRowNum());
    				
    				List<String[]> sheetList = new ArrayList<String[]>();
    				for(int rowNum=sheet.getFirstRowNum();rowNum<sheet.getLastRowNum();rowNum++){
    					Row row = sheet.getRow(rowNum);
    
    					//System.out.println("row.getLastCellNum()"+row.getLastCellNum());
    					if(row==null)
    						continue;
    					//System.out.print(rowNum+"行	"+row.getLastCellNum());
    					String[] ary = new String[row.getLastCellNum()];
    					for(int cellNum=row.getFirstCellNum();cellNum<row.getLastCellNum();cellNum++){
    						Cell cell = row.getCell(cellNum);
    						//System.out.print("--"+cell.getAddress()+"		");
    						ary[cellNum]=cell.getStringCellValue();
    					}
    					sheetList.add(ary);
    					//System.out.println();
    				}
    				list.add(sheetList);
    			}
    		} catch (Exception e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    		
    		return list;
    	}
    	
    	public static void main(String[] args) {
    		ReadExcel re = new ReadExcel();
    		
    		File file = new File("E:/QQFile/17-18-2计算机学院班级课表.xlsx");
    		InputStream input;
    		try {
    			input = new FileInputStream(file);
    
    			List<List<String[]>> list=re.readXLS(input);
    			
    			for(int i=0;i<list.size();i++){
    				for(int j=0;j<list.get(i).size();j++){
    					for(int k=0;k<list.get(i).get(j).length;k++){
    						System.out.print(list.get(i).get(j)[k]+"%s");
    					}
    					System.out.println();
    				}
    			}
    		} catch (FileNotFoundException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    	}
    	
    }

poi包

http://mirror.bit.edu.cn/apache/poi/release/bin/poi-bin-3.17-20170915.zip