POI解析excel表格数据例子

2019-11-14 10:09:43

字体：大中小

来源：转载

供稿：网友

例子包含解析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包如下：这里写图片描述