java的poi技术读取Excel[2003-2007，2010]详解编程语言

/** 
 *  
 */ 
package com.b510.common; 
 
/** 
 * @author Hongten 
 * @created 2014-5-21 
 */ 
public class Common { 
 
    public static final String OFFICE_EXCEL_2003_POSTFIX = "xls"; 
    public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx"; 
 
    public static final String EMPTY = ""; 
    public static final String POINT = "."; 
    public static final String LIB_PATH = "lib"; 
    public static final String STUDENT_INFO_XLS_PATH = LIB_PATH + "/student_info" + POINT + OFFICE_EXCEL_2003_POSTFIX; 
    public static final String STUDENT_INFO_XLSX_PATH = LIB_PATH + "/student_info" + POINT + OFFICE_EXCEL_2010_POSTFIX; 
    public static final String NOT_EXCEL_FILE = " : Not the Excel file!"; 
    public static final String PROCESSING = "Processing..."; 
 
}

/** 
 *  
 */ 
package com.b510.excel; 
 
import java.io.FileInputStream; 
import java.io.IOException; 
import java.io.InputStream; 
import java.util.ArrayList; 
import java.util.List; 
 
import org.apache.poi.hssf.usermodel.HSSFCell; 
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.xssf.usermodel.XSSFCell; 
import org.apache.poi.xssf.usermodel.XSSFRow; 
import org.apache.poi.xssf.usermodel.XSSFSheet; 
import org.apache.poi.xssf.usermodel.XSSFWorkbook; 
 
import com.b510.common.Common; 
import com.b510.excel.util.Util; 
import com.b510.excel.vo.Student; 
 
/** 
 * @author Hongten 
 * @created 2014-5-20 
 */ 
public class ReadExcel { 
     
    /** 
     * read the Excel file 
     * @param path the path of the Excel file 
     * @return 
     * @throws IOException 
     */ 
    public List<Student> readExcel(String path) throws IOException { 
        if (path == null || Common.EMPTY.equals(path)) { 
            return null; 
        } else { 
            String postfix = Util.getPostfix(path); 
            if (!Common.EMPTY.equals(postfix)) { 
                if (Common.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) { 
                    return readXls(path); 
                } else if (Common.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) { 
                    return readXlsx(path); 
                } 
            } else { 
                System.out.println(path + Common.NOT_EXCEL_FILE); 
            } 
        } 
        return null; 
    } 
 
    /** 
     * Read the Excel 2010 
     * @param path the path of the excel file 
     * @return 
     * @throws IOException 
     */ 
    public List<Student> readXlsx(String path) throws IOException { 
        System.out.println(Common.PROCESSING + path); 
        InputStream is = new FileInputStream(path); 
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is); 
        Student student = null; 
        List<Student> list = new ArrayList<Student>(); 
        // Read the Sheet 
        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) { 
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet); 
            if (xssfSheet == null) { 
                continue; 
            } 
            // Read the Row 
            for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) { 
                XSSFRow xssfRow = xssfSheet.getRow(rowNum); 
                if (xssfRow != null) { 
                    student = new Student(); 
                    XSSFCell no = xssfRow.getCell(0); 
                    XSSFCell name = xssfRow.getCell(1); 
                    XSSFCell age = xssfRow.getCell(2); 
                    XSSFCell score = xssfRow.getCell(3); 
                    student.setNo(getValue(no)); 
                    student.setName(getValue(name)); 
                    student.setAge(getValue(age)); 
                    student.setScore(Float.valueOf(getValue(score))); 
                    list.add(student); 
                } 
            } 
        } 
        return list; 
    } 
 
    /** 
     * Read the Excel 2003-2007 
     * @param path the path of the Excel 
     * @return 
     * @throws IOException 
     */ 
    public List<Student> readXls(String path) throws IOException { 
        System.out.println(Common.PROCESSING + path); 
        InputStream is = new FileInputStream(path); 
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is); 
        Student student = null; 
        List<Student> list = new ArrayList<Student>(); 
        // Read the Sheet 
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { 
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); 
            if (hssfSheet == null) { 
                continue; 
            } 
            // Read the Row 
            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { 
                HSSFRow hssfRow = hssfSheet.getRow(rowNum); 
                if (hssfRow != null) { 
                    student = new Student(); 
                    HSSFCell no = hssfRow.getCell(0); 
                    HSSFCell name = hssfRow.getCell(1); 
                    HSSFCell age = hssfRow.getCell(2); 
                    HSSFCell score = hssfRow.getCell(3); 
                    student.setNo(getValue(no)); 
                    student.setName(getValue(name)); 
                    student.setAge(getValue(age)); 
                    student.setScore(Float.valueOf(getValue(score))); 
                    list.add(student); 
                } 
            } 
        } 
        return list; 
    } 
 
    @SuppressWarnings("static-access") 
    private String getValue(XSSFCell xssfRow) { 
        if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) { 
            return String.valueOf(xssfRow.getBooleanCellValue()); 
        } else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) { 
            return String.valueOf(xssfRow.getNumericCellValue()); 
        } else { 
            return String.valueOf(xssfRow.getStringCellValue()); 
        } 
    } 
 
    @SuppressWarnings("static-access") 
    private String getValue(HSSFCell hssfCell) { 
        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) { 
            return String.valueOf(hssfCell.getBooleanCellValue()); 
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) { 
            return String.valueOf(hssfCell.getNumericCellValue()); 
        } else { 
            return String.valueOf(hssfCell.getStringCellValue()); 
        } 
    } 
}

/** 
 *  
 */ 
package com.b510.excel.client; 
 
import java.io.IOException; 
import java.util.List; 
 
import com.b510.common.Common; 
import com.b510.excel.ReadExcel; 
import com.b510.excel.vo.Student; 
 
/** 
 * @author Hongten 
 * @created 2014-5-21 
 */ 
public class Client { 
 
    public static void main(String[] args) throws IOException { 
        String excel2003_2007 = Common.STUDENT_INFO_XLS_PATH; 
        String excel2010 = Common.STUDENT_INFO_XLSX_PATH; 
        // read the 2003-2007 excel 
        List<Student> list = new ReadExcel().readExcel(excel2003_2007); 
        if (list != null) { 
            for (Student student : list) { 
                System.out.println("No. : " + student.getNo() + ", name : " + student.getName() + ", age : " + student.getAge() + ", score : " + student.getScore()); 
            } 
        } 
        System.out.println("======================================"); 
        // read the 2010 excel 
        List<Student> list1 = new ReadExcel().readExcel(excel2010); 
        if (list1 != null) { 
            for (Student student : list1) { 
                System.out.println("No. : " + student.getNo() + ", name : " + student.getName() + ", age : " + student.getAge() + ", score : " + student.getScore()); 
            } 
        } 
    } 
}

/** 
 *  
 */ 
package com.b510.excel.util; 
 
import com.b510.common.Common; 
 
/** 
 * @author Hongten 
 * @created 2014-5-21 
 */ 
public class Util { 
 
    /** 
     * get postfix of the path 
     * @param path 
     * @return 
     */ 
    public static String getPostfix(String path) { 
        if (path == null || Common.EMPTY.equals(path.trim())) { 
            return Common.EMPTY; 
        } 
        if (path.contains(Common.POINT)) { 
            return path.substring(path.lastIndexOf(Common.POINT) + 1, path.length()); 
        } 
        return Common.EMPTY; 
    } 
}

/** 
 *  
 */ 
package com.b510.excel.vo; 
 
/** 
 * Student 
 *  
 * @author Hongten 
 * @created 2014-5-18 
 */ 
public class Student { 
    /** 
     * id    
     */ 
    private Integer id; 
    /** 
     * 学号 
     */ 
    private String no; 
    /** 
     * 姓名 
     */ 
    private String name; 
    /** 
     * 学院 
     */ 
    private String age; 
    /** 
     * 成绩 
     */ 
    private float score; 
 
    public Integer getId() { 
        return id; 
    } 
 
    public void setId(Integer id) { 
        this.id = id; 
    } 
 
    public String getNo() { 
        return no; 
    } 
 
    public void setNo(String no) { 
        this.no = no; 
    } 
 
    public String getName() { 
        return name; 
    } 
 
    public void setName(String name) { 
        this.name = name; 
    } 
 
    public String getAge() { 
        return age; 
    } 
 
    public void setAge(String age) { 
        this.age = age; 
    } 
 
    public float getScore() { 
        return score; 
    } 
 
    public void setScore(float score) { 
        this.score = score; 
    } 
 
}