POI解析excel表格数据例子

最新推荐文章于 2022-06-20 11:32:58 发布

懒惰---蚂蚁

最新推荐文章于 2022-06-20 11:32:58 发布

阅读量326

点赞数

分类专栏： Java 文章标签： java poi excel

本文链接：https://blog.csdn.net/lei273834/article/details/54864235

版权

Java 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

例子包含解析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;
    }
}