读取excel文件中的信息实例1

来源：互联网发布：windows gcc搭建编辑：程序博客网时间：2024/06/05 20:24
package excel;import java.io.FileInputStream;import java.io.IOException;import java.io.InputStream;import java.text.DateFormat;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.List;import org.apache.commons.lang3.StringUtils;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.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.DateUtil;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;public class ExcelUtils {    /**     * suffix of excel 2003     */    public static final String OFFICE_EXCEL_V2003_SUFFIX = "xls";    /**     * suffix of excel 2007     */    public static final String OFFICE_EXCEL_V2007_SUFFIX = "xlsx";    /**     * suffix of excel 2010     */    public static final String OFFICE_EXCEL_V2010_SUFFIX = "xlsx";    public static final String EMPTY = "";    public static final String DOT = ".";    public static final String LIB_PATH = "lib";    public static final String STUDENT_INFO_XLS_PATH = LIB_PATH + "/student_info" + DOT + OFFICE_EXCEL_V2003_SUFFIX;    public static final String STUDENT_INFO_XLSX_PATH = LIB_PATH + "/student_info" + DOT + OFFICE_EXCEL_V2007_SUFFIX;    public static final String NOT_EXCEL_FILE = " is Not a Excel file!";    public static final String PROCESSING = "Processing...";    public static void main(String[] args) throws IOException {        try {            List<Student> list = readExcel("E:\\test\\excel\\student.xlsx");            System.out.println(list);        } catch (Exception e) {            e.printStackTrace();        }    }    /**     * Check which version of The Excel file is. Throw exception if Excel file path is illegal.     *      * @param path  the Excel file      * @return a list that contains Students from Excel.     * @throws IOException     */     public static List<Student> readExcel(String path) throws IOException, IllegalArgumentException {            if (StringUtils.isBlank(path)) {                throw new IllegalArgumentException(path + " excel file path is either null or empty");            } else {                String suffiex = getSuffiex(path);                if(StringUtils.isBlank(suffiex)){                    throw new IllegalArgumentException(path + " suffiex is either null or empty");                }                if (OFFICE_EXCEL_V2003_SUFFIX.equals(suffiex)) {                    return readXls(path);                } else if (OFFICE_EXCEL_V2007_SUFFIX.equals(suffiex)) {                    return readXlsx(path);                } else if (OFFICE_EXCEL_V2010_SUFFIX.equals(suffiex)) {                    return readXlsx(path);                } else {                    throw new IllegalArgumentException(path + NOT_EXCEL_FILE);                }            }        }        /**         * Read the Excel 2017 or 2010         * @param path the path of the excel file         * @return         * @throws IOException         */        public static List<Student> readXlsx(String path) throws IOException {            System.out.println(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(getStringVal(no));                        student.setName(getStringVal(name));                        student.setAge(getStringVal(age));                        student.setScore(Float.valueOf(getStringVal(score)));                        list.add(student);                    }                }            }            return list;        }        /**         * Read the Excel 2003         * @param path the path of the Excel         * @return         * @throws IOException         */        public static List<Student> readXls(String path) throws IOException {            System.out.println(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(getStringVal(no));                        student.setName(getStringVal(name));                        student.setAge(getStringVal(age));                        student.setScore(Float.valueOf(getStringVal(score)));                        list.add(student);                    }                }            }            return list;        }        @SuppressWarnings("static-access")        private static String getValue(XSSFCell xssfCell) {            if (xssfCell.getCellType() == xssfCell.CELL_TYPE_BOOLEAN) {                return String.valueOf(xssfCell.getBooleanCellValue());            } else if (xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC) {                return String.valueOf(xssfCell.getNumericCellValue());            } else {                return String.valueOf(xssfCell.getStringCellValue());            }        }        @SuppressWarnings("static-access")        private static 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());            }        }        public static String getSuffiex(String path) {            if(StringUtils.isBlank(path)){                return EMPTY;            }            int index = path.lastIndexOf(DOT);            if (index == -1) {                return EMPTY;            }            return path.substring(index + 1, path.length());        }        public static String getStringVal(HSSFCell cell){        switch(cell.getCellType()){        case Cell.CELL_TYPE_BOOLEAN:        return cell.getBooleanCellValue()?"TRUE":"FALSE";        case Cell.CELL_TYPE_FORMULA:        return  cell.getCellFormula();        case Cell.CELL_TYPE_NUMERIC:        if (DateUtil.isCellDateFormatted(cell)) {            DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");            return sdf.format(cell.getDateCellValue());            }        cell.setCellType(Cell.CELL_TYPE_STRING);;        case Cell.CELL_TYPE_STRING:        return cell.getStringCellValue();        default:        return EMPTY;        }        }        public static String getStringVal(XSSFCell cell){        switch(cell.getCellType()){        case Cell.CELL_TYPE_BOOLEAN:        return cell.getBooleanCellValue()?"TRUE":"FALSE";        case Cell.CELL_TYPE_FORMULA:        return  cell.getCellFormula();        case Cell.CELL_TYPE_NUMERIC:        if (DateUtil.isCellDateFormatted(cell)) {        DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");        return sdf.format(cell.getDateCellValue());        }        cell.setCellType(Cell.CELL_TYPE_STRING);        case Cell.CELL_TYPE_STRING:        return cell.getStringCellValue();        default:        return EMPTY;        }        }}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;    }}
阅读全文
0 0