对Excel进行解析\生成\查询\计算等处理是Java下较常见的任务 ， 但Excel的文件格式很复杂 ， 自行编码读写太困难 ， 有了POI\EasyExcel\JExcel等类库就方便多了 ， 其中POI最为出色 。
POI具有全面而细致的xls读写能力POI可读写多种Excel文件格式 ， 既支持古老的二进制格式（xls） ， 也支持现代的OOXML格式（xlsx） ， 既支持全内存一次性读写 ， 也支持小内存流式读写 。POI为大量Excel元素设计了相应的JAVA类 ， 包括workbook、printer、sheet、row、cell ， 其中 ， 与cell相关的类包括单元格样式、字体、颜色、日期、对齐、边框等 。仅单元格样式类 ， 方法就超过了四十个 ， 可进行最全面最细致的读写操作 。
POI的读写功能很底层POI的读写功能全面而细致 ， 但细致也意味着过于底层 ， 开发者必须从头写起 ， 自己处理每一处细节 ， 即使简单的操作也要编写大量代码 。比如 ， 读入首行为列名的行式xls：
FileInputStream fileInputStream = new FileInputStream("d:\\Orders.xls");// get the excel bookWorkbook workbook = new HSSFWorkbook(fileInputStream);if (workbook != null) {// get the first sheetSheet sheet = workbook.getSheetAt(0);if (sheet != null) {//get the col name/first lineRow rowTitle = sheet.getRow(0); // first lineif (rowTitle != null) {int cellTitles = rowTitle.getPhysicalNumberOfCells(); // get column numberfor (int i = 0; i < cellTitles; i++) {Cell cell = rowTitle.getCell(i); //the cell!if (cell != null) {System.out.print(cell.getStringCellValue() + " | ");}}}//get the value/other linesint rows = sheet.getPhysicalNumberOfRows(); // get line numberfor (int i = 1; i < rows; i++) {Row row = sheet.getRow(i); // get row iif (row != null) {int cells = row.getPhysicalNumberOfCells(); // get column numberfor (int j = 0; j < cells; j++) {// line number and row numberSystem.out.print("[" + i + "-" + j + "]");Cell cell = row.getCell(j); // the cell!if (cell != null) {int cellType = cell.getCellType();Object valuehttps://www.huyubaike.com/biancheng/= "";switch (cellType) {case HSSFCell.CELL_TYPE_STRING: // stringvalue = https://www.huyubaike.com/biancheng/cell.getStringCellValue();break;case HSSFCell.CELL_TYPE_BLANK: // 空break;case HSSFCell.CELL_TYPE_BOOLEAN: // booleanvalue = cell.getBooleanCellValue();break;case HSSFCell.CELL_TYPE_NUMERIC: // numberif (HSSFDateUtil.isCellDateFormatted(cell)) { // date numberDate date = cell.getDateCellValue();value = new DateTime(date).toString("yyyy-MM-dd HH:mm:ss");}else { // normal number// change to string to avoid being too longcell.setCellType(HSSFCell.CELL_TYPE_STRING);value = https://www.huyubaike.com/biancheng/cell;}break;case HSSFCell.CELL_TYPE_ERROR:throw new RuntimeException("data type mistaken");}System.out.println(value);}}}System.out.println("end of the "+i+" line");}System.out.println("end of the value lines=======================================");}}行式xls是最常见的格式 ， 但POI并没有为此提供方便的处理方法 ， 只能按照workbook->sheet->line->cell的顺序进行循环解析 ， 造成了如此繁琐的代码 。
这还只是将数据简单读出来 ， 如果下一步想再处理数据 ， 还要事先转为结构化数据对象 ， 比如ArrayList<实体类>或HashMap ， 代码就更繁琐了 。
POI查询计算困难解析Excel并不是目标 ， 我们通常还要对这些文件进查询计算 ， 但POI作为Excel的解析类 ， 没有也不合适再提供相关的方法 ， 只能用JAVA手工硬写 。比如基础的分组汇总运算 ， JAVA代码大概这样：
Comparator<salesRecord> comparator = new Comparator<salesRecord>() { public int compare(salesRecord s1, salesRecord s2) {if (!s1.salesman.equals(s2.salesman)) {return s1.salesman.compareTo(s2.salesman);} else {return s1.ID.compareTo(s2.ID);} }};Collections.sort(sales, comparator);ArrayList<resultRecord> result=new ArrayList<resultRecord>();salesRecord standard=sales.get(0);float sumValue=https://www.huyubaike.com/biancheng/standard.value;for(int i = 1;i