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; 
} 
}