POI解析excel表格数据例子

来源：互联网发布：列宾美院知乎编辑：程序博客网时间：2024/06/05 07:47
例子包含解析2003及之前版本和2007及之后版本。

/** * @author Yuansheng.Lei * excel表格导入工具类 */public class PoiExcel {    public static List<Dto> getDataByPoi(String filePath){        Workbook wb = null;        InputStream stream = null;        Sheet sheet = null;        try {            stream = new FileInputStream(filePath);            if (filePath.endsWith(".xls")) {                wb = (Workbook)new HSSFWorkbook(stream);            }else if (filePath.endsWith(".xlsx")) {                wb = (Workbook)new XSSFWorkbook(stream);            }            sheet = wb.getSheetAt(0);        }catch (Exception e) {            e.printStackTrace();        }finally{            if (stream != null) {                try {                    stream.close();                } catch (IOException e) {                    e.printStackTrace();                }            }        }        List<Dto> list = null;        if( sheet != null ){             int count_row = sheet.getLastRowNum();//获取最后一行行数            int count_cell= sheet.getRow(1).getPhysicalNumberOfCells();//获取第一行单元格数            Row row = null;            Cell cell = null;            list = new ArrayList<Dto>();            for (int i = 1; i <= count_row; i++) {                Dto dto = Dtos.newDto();                row = sheet.getRow((short)i);                for (int j = 0; j < count_cell; j++) {                    cell = row.getCell((short)j);                    dto.put("a"+(j+1), getValue(cell));                }                list.add(dto);            }        }        return list;    }    @SuppressWarnings("deprecation")    public static  String getValue(Cell cell){        String cellvalue = "";        if(cell != null){            switch (cell.getCellType()) {                case Cell.CELL_TYPE_STRING:                    cellvalue = cell.getRichStringCellValue().getString(); //获取字符串                    break;                case Cell.CELL_TYPE_FORMULA:                case Cell.CELL_TYPE_NUMERIC:                    if (HSSFDateUtil.isCellDateFormatted(cell)) {                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");                        cellvalue = sdf.format(cell.getDateCellValue());        //获取时间                    }else if(cell.getCellStyle().getDataFormatString().indexOf("%") != -1){                        ellvalue = (new DecimalFormat("0").format(cell.getNumericCellValue()*100))+"%";   //获取带%的百分比                    }else {                        cellvalue = String.valueOf(new DecimalFormat("0").format(cell.getNumericCellValue()));  //获取数字                    }                    break;                case Cell.CELL_TYPE_BOOLEAN:                    cellvalue = String.valueOf(cell.getBooleanCellValue());                    break;                case Cell.CELL_TYPE_BLANK:                    cellvalue = "";                    break;                default :                    cellvalue = "";                    break;            }        }        return cellvalue;    }}
相关jar包如下：
这里写图片描述
0 0