工作记录：使用POI从excel导入数据(2003版本、2007以及以上版本)

使用poi读取2007版本及以上的数据实例:

public List<KqRecord> readXlsx(String path) throws IOException {
        InputStream is = new FileInputStream(path);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);//2007以上版本
        KqRecord kq = null;
        List<KqRecord> list = new ArrayList<KqRecord>();
        // 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) {
                    kq = new KqRecord();
                    //一个一个的读取
                    XSSFCell Id = xssfRow.getCell(0);
                    XSSFCell Kqid = xssfRow.getCell(1);
                    XSSFCell DeptName = xssfRow.getCell(2);
                    XSSFCell DeviceNmae = xssfRow.getCell(3);
                    XSSFCell Name = xssfRow.getCell(4);
                    XSSFCell CheckTime = xssfRow.getCell(5);
                    XSSFCell CheckType = xssfRow.getCell(6);
                    XSSFCell Validateflag = xssfRow.getCell(7);
                    XSSFCell SnId = xssfRow.getCell(8);
                    //对象设置值
                    kq.setId(getValue(Id));
                    kq.setKqid(getValue(Kqid));
                    kq.setDeptName(getValue(DeptName));
                    kq.setDeviceNmae(getValue(DeviceNmae));
                    kq.setName(getValue(Name));
                    kq.setCheckTime(getValue(CheckTime));
                    kq.setCheckType(getValue(CheckType));
                    kq.setValidateflag(getValue(Validateflag));
                    kq.setSnId(getValue(SnId));
                    list.add(kq);
                }
            }
        }
        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());
        }
    }

测试实例：

@Test
    public void testReadExcel(){
        try {
            List<KqRecord> readExcel = readExcel("D:/考勤记录表.xlsx");
            System.out.println(readExcel.size());
            System.out.println(readExcel);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    
    public List<KqRecord> readExcel(String path) throws IOException {
        if (StringUtils.isBlank(path)) {
            return null;
        } else {
            return readXlsx(path);
        }
    }

使用poi读取2003版excel数据的实例

public List<KqRecord> readXls(String path) throws IOException {
        InputStream is = new FileInputStream(path);
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        KqRecord kq = null;
        List<KqRecord> list = new ArrayList<KqRecord>();
        // 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) {
                    kq = new KqRecord();
                    HSSFCell no = hssfRow.getCell(0);
                    HSSFCell name = hssfRow.getCell(1);
                    HSSFCell age = hssfRow.getCell(2);
                    HSSFCell score = hssfRow.getCell(3);
                    kq.setId(getValue(no));
                    kq.setKqid(getValue(name));
                    kq.setDeptName(getValue(age));
                    kq.setDeviceNmae(getValue(score));
                    list.add(kq);
                }
            }
        }
        return list;
    }
 
    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());
        }
}

测试数据与之前一样。。

参考文章; http://www.cnblogs.com/hongten/p/java_poi_excel_xls_xlsx.html

转载于:https://my.oschina.net/u/576818/blog/405123