Excel操作 >> 导入

/**
	 * 導入文件（excel）
	 * @author dingyuanwei
	 * @param path   路徑+檔名
	 * @param line   從第幾欄開始（写出的Excel）　从0开始算起
	 * @param rowLen 到第几列结束  　从1开始算起
	 * @param atRow 从第几行开始写（写入的Excel）　从0开始算起
	 * @return list 返回一个包含行的LIST对象记录
	 * @return inputStream 将excel进行流化
	 * @throws Exception
	 * 解析Excel文件
	 */
	public static List<Object> importData(InputStream inputStream,int atRow, int line, int rowLen) throws Exception{  
		List<Object> tmpList = new ArrayList<Object>();  
		POIFSFileSystem pfs = new POIFSFileSystem(inputStream);
		HSSFWorkbook wb = new HSSFWorkbook(pfs);
		HSSFSheet sheet = wb.getSheetAt(0);//第0索引文件解析
		HSSFRow row = null;
		try{   
			int totalRow = sheet.getLastRowNum(); //excel資料行數
			if(totalRow>0){ //判斷excel是否為空
				for(int i = atRow ; i <=totalRow; i++){
			    	List<Object> rowList = new ArrayList<Object>();  
			    	row = sheet.getRow(i);
			    	if(row!=null){ //判断行是否为空
			    		for(int j = line; j < rowLen; j++){  
							HSSFCell cell = row.getCell((short) j);
							if(cell == null){ //判断列是否为空
								rowList.add(""); 
							}else{
								int ctype = cell.getCellType();
								switch(ctype){
									// 處理string部份
									case HSSFCell.CELL_TYPE_BLANK:
										rowList.add("");
									break;
									
									case HSSFCell.CELL_TYPE_STRING:
										rowList.add(cell.getRichStringCellValue().getString().trim());
									break;
									
									//處理number部份
									case HSSFCell.CELL_TYPE_NUMERIC:
										
										if (HSSFDateUtil.isCellDateFormatted(cell)) { // 是否為日期型
											System.out.print( cell.getDateCellValue());
											//須轉date格式
											rowList.add(cell.getDateCellValue()); 
									    }else{
									    	
											double tmpdouble = cell.getNumericCellValue();
											if(tmpdouble - (int)tmpdouble < Double.MIN_VALUE) { 
												
												//int 
												rowList.add(Integer.toString((int)tmpdouble)); 
											}else{ 
												
												//double 
												rowList.add(Double.toString(cell.getNumericCellValue())); 
											}
									    }
									break;
									
									//處理boolean部份
									case HSSFCell.CELL_TYPE_BOOLEAN:  
										rowList.add(cell.getBooleanCellValue()); 
									break;
								} 
							}  
				    	} 
				    	tmpList.add(rowList);//加入每一行，行中又包含单元格字段
			    	}
			    }
			}
		}catch(Exception e){ 
			e.printStackTrace();
			throw e;
		} 
	    return tmpList;//所有的行
	}
	
	public static void main(String[] args) throws FileNotFoundException, Exception {
		List  a = importData(new FileInputStream("E:\\项目需求\\華文網開發需求\\example.xls"),1,0,5);
		List parseData;
		List resultResult = new ArrayList();
		List successData = new ArrayList(); 
		int s = 0;
		int f = 0;
		try {
			parseData = ExcelUtil
					.importData(
							new FileInputStream("E:\\项目需求\\華文網開發需求\\example.xls"),
							1, 0, 5);
			for (int i = 0; i < parseData.size(); i++) {
				List tempResult = (List) parseData.get(i);
				boolean tfFalse = false;//標志成功或失敗
				Pattern p = Pattern.compile("[1-9]\\d*");//是否是正整數表達式
				TeachreSource ts = new TeachreSource();
				
				
				for (int j = 0; j < tempResult.size(); j++) {
					String temp = "";
					switch (j) {
						case 0:
							temp = tempResult.get(0).toString(); // *教學資源類別ID
							if("".equals(temp)){
								tfFalse = false;
							}else if(!p.matcher(temp.trim()).matches()){
								tfFalse = false;//是否是正整數
							}else{
								tfFalse = true;
							}
							break;
						case 1:
							//temp = tempResult.get(1).toString(); //教學資源類別
							tfFalse = true;
							break;
						case 2:
							temp = tempResult.get(2).toString();//*商品名稱
							if("".equals(temp)){
								tfFalse = false;
							}else{
								tfFalse = true;
							}
							break;
						case 3:
							temp = tempResult.get(3).toString();//冊次
							if("".equals(temp)){
								tfFalse = true;
							}else if(!p.matcher(temp.trim()).matches() || Integer.parseInt(temp) > 99){
								tfFalse = false;
							}else{
								tfFalse = true;
							}
							break;
						case 4:
							temp = tempResult.get(4).toString();//*路徑
							File file1=new File(temp.trim());
							if(file1.isFile()){
								tfFalse = true;
							}else{
								tfFalse = false;
							}
							
							break;

					}
					
					if(!tfFalse){
						break;//中斷循環
					}
				}
				
				
				if(tfFalse){
					successData.add(ts);//加入的數據
					s ++;
					
				}else{
					f ++;
				}
			}
			resultResult.add(s);
			resultResult.add(f);
			
		} catch (Exception e) {
			e.printStackTrace();
			
		}
		System.out.println(a);
	}
	

 

 

 

 

package com.tkb.post.action.admin;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import com.sun.org.apache.bcel.internal.generic.Select;

public class importExcel {

	
	
	
	public static List<Object> importData(InputStream inputStream,int atRow, int line, int rowLen) throws Exception{     
        List<Object> tmpList = new ArrayList<Object>();     
        POIFSFileSystem pfs = new POIFSFileSystem(inputStream);   
        HSSFWorkbook wb = new HSSFWorkbook(pfs);   
        HSSFSheet sheet = wb.getSheetAt(2);//第2索引文件解析   EB_VIEW_ORG
        HSSFRow row = null;   
        try{      
            int totalRow = sheet.getLastRowNum(); //excel資料行數   
            if(totalRow>0){ //判斷excel是否為空   
                for(int i = atRow ; i <=totalRow; i++){   
                    List<Object> rowList = new ArrayList<Object>();     
                    row = sheet.getRow(i);   
                    if(row!=null){ //判断行是否为空   
                        for(int j = line; j < rowLen; j++){     
                            HSSFCell cell = row.getCell((short) j);   
                            if(cell == null){ //判断列是否为空   
                                rowList.add("");    
                            }else{   
                                int ctype = cell.getCellType();   
                                switch(ctype){   
                                    // 處理string部份   
                                    case HSSFCell.CELL_TYPE_BLANK:   
                                        rowList.add("");   
                                    break;   
                                       
                                    case HSSFCell.CELL_TYPE_STRING:   
                                        rowList.add(cell.getRichStringCellValue().getString().trim());   
                                    break;   
                                       
                                    //處理number部份   
                                    case HSSFCell.CELL_TYPE_NUMERIC:   
                                           
                                        if (HSSFDateUtil.isCellDateFormatted(cell)) { // 是否為日期型   
                                            System.out.print( cell.getDateCellValue());   
                                            //須轉date格式   
                                            rowList.add(cell.getDateCellValue());    
                                        }else{   
                                               
                                            double tmpdouble = cell.getNumericCellValue();   
                                            if(tmpdouble - (int)tmpdouble < Double.MIN_VALUE) {    
                                                   
                                                //int    
                                                rowList.add(Integer.toString((int)tmpdouble));    
                                            }else{    
                                                   
                                                //double    
                                                rowList.add(Double.toString(cell.getNumericCellValue()));    
                                            }   
                                        }   
                                    break;   
                                       
                                    //處理boolean部份   
                                    case HSSFCell.CELL_TYPE_BOOLEAN:     
                                        rowList.add(cell.getBooleanCellValue());    
                                    break;   
                                }    
                            }     
                        }    
                        tmpList.add(rowList);//加入每一行，行中又包含单元格字段   
                    }   
                }   
            }   
        }catch(Exception e){    
            e.printStackTrace();   
            throw e;   
        }    
        return tmpList;//所有的行   
    }   

	
	/**
	 * @param args
	 * @throws Exception 
	 * @throws FileNotFoundException 
	 */
	public static void main(String[] args) throws FileNotFoundException, Exception {
		String sql = "";
		List  a = importData(new FileInputStream("D:\\1.xls"),1,0,5);  
		for(int i=0;i<a.size();i++){
			List b = (List)a.get(i);
			sql += "insert into eb_view_org(nodeID,parentNodeId,supervisorUserPK,twTitle,twTitlePath,nodePath,isExist) values(";
			for (int j = 0; j < b.size(); j++) {
				sql += "'"+ b.get(j)+"'";
			}
			sql +=");";
		}
		System.out.println(sql);
	}

}