Excel解析支持2003和2007

解析代码如下：

public class ExcelUtil {
    private static final SimpleDateFormat yyyyMMdd = new SimpleDateFormat("yyyy-MM-dd");

    public static List<Map<String, Object>> parseExcelInputStream(byte[] bytes, String[] properties) {
        List<Map<String, Object>> excelData = new ArrayList<Map<String, Object>>();
        //字节转为流
        InputStream is = new ByteArrayInputStream(bytes);
        try {

            //兼容Excel 2003/2007/2010 都是可以处理的
            Workbook workbook = WorkbookFactory.create(is);
            int sheetCount = workbook.getNumberOfSheets();//Sheet的数量

            //遍历每个Sheet
            for (int s = 0; s < sheetCount; s++) {
                Sheet sheet = workbook.getSheetAt(s);
                int rowCount = sheet.getPhysicalNumberOfRows(); //获取总行数
                //遍历每一行
                for (int r = 1; r < rowCount; r++) {
                    Map<String, Object> map = new HashMap<String, Object>();
                    Row row = sheet.getRow(r);
                    int cellCount = row.getPhysicalNumberOfCells(); //获取总列数
                    try {
                        //遍历每一列
                        for (int c = 0; c < cellCount; c++) {
                            Cell cell = row.getCell(c);
                            int cellType = cell.getCellType();
                            String cellValue = null;
                            switch (cellType) {
                                case Cell.CELL_TYPE_STRING: //文本
                                    cellValue = cell.getStringCellValue();
                                    break;
                                case Cell.CELL_TYPE_NUMERIC: //数字、日期
                                    if (DateUtil.isCellDateFormatted(cell)) {
                                        cellValue = yyyyMMdd.format(cell.getDateCellValue()); //日期型
                                    } else {
                                        cellValue = String.valueOf(cell.getNumericCellValue()); //数字
                                    }
                                    break;
                                case Cell.CELL_TYPE_BOOLEAN: //布尔型
                                    cellValue = String.valueOf(cell.getBooleanCellValue());
                                    break;
                                case Cell.CELL_TYPE_BLANK: //空白
                                    cellValue = cell.getStringCellValue();
                                    break;
                                case Cell.CELL_TYPE_ERROR: //错误
                                    cellValue = "error";
                                    break;
                                case Cell.CELL_TYPE_FORMULA: //公式
                                    cellValue = "error";
                                    break;
                                default:
                                    cellValue = "error";
                            }
                            if (cellValue == "error") {
                                System.out.println("###" + r + "行解析有问题，cellValue=" + cellValue);
                                continue;
                            }
                            map.put(properties[c], cellValue);
                        }
                    } catch (Exception e) {
                        System.out.println("解析excel" + r + "行数据出错" + e);
                    }
                    int nullNum = 0;
                    for (String key : map.keySet()) {
                        if (map.get(key) == null) {
                            nullNum += 1;
                        }
                    }
                    if (nullNum < map.size()) {
                        excelData.add(map);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return excelData;
    }
}

解读：

入参：bytes：要解析的二进制excel输入流
          properties：excel的sheet，每列的key值

返回：以properties数组中元素为key，sheet中每行单元格的值为value的List对象

如：

返回数据则为：

 

 

转载于:https://my.oschina.net/u/3453392/blog/909021