解析Excel(轉)

標籤：

下面是本文的項目結構：

項目中所需要的jar檔案：

所用的Excel資料（2003-2007,2010都是一樣的資料）

運行效果：

 

/Excel2010/src/com/b510/common/Common.java

 

/** *  */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...";}

/Excel2010/src/com/b510/excel/ReadExcel.java

** *  */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());        }    }}

/Excel2010/src/com/b510/excel/client/Client.java

/** *  */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());            }        }    }}

/Excel2010/src/com/b510/excel/util/Util.java

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

/Excel2010/src/com/b510/excel/vo/Student.java

 

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

　　

　

解析Excel(轉)