java实现excel解析方法

解析excel文件

//fileName为文件的名称，path为文件的路径，nameRow为excel中列的名称的起始行数，valueRow为值的起始行数，excel行数是从0开始的
public static ArrayList<Map<String, Object>> readExcelrowKuaiBao(String fileName, String path, int nameRow, int valueRow) throws IOException {
        ArrayList<Map<String, Object>> Row = new ArrayList<Map<String, Object>>();
        try {
            Workbook workBook = null;
            try {
                System.out.println(path + "/" + fileName);
                workBook = new XSSFWorkbook(path + "/" + fileName);
            } catch (Exception ex) {
                workBook = new HSSFWorkbook(new FileInputStream(path + "/" + fileName));
            }
            for (int numSheet = 0; numSheet < workBook.getNumberOfSheets(); numSheet++) {
                Sheet sheet = workBook.getSheetAt(numSheet);
                if (sheet == null) {
                    continue;
                }
                Row row0 = sheet.getRow(nameRow);
                if (null == row0) {
                    continue;
                }
                int colum = row0.getPhysicalNumberOfCells();
                List<String> keys = new ArrayList<String>();
                for (int i = 0; i < colum; i++) {
                    Cell cell = row0.getCell(i);
                    keys.add("column" + i);
                }
                // 循环行Row  
                for (int rowNum = valueRow; rowNum <= sheet.getPhysicalNumberOfRows(); rowNum++) {
                    //是否到达文件末尾
                    boolean isEnd = true;
                    Row row = sheet.getRow(rowNum);
                    if (row == null) {
                        continue;
                    }
                    // 循环列Cell  
                    Map<String, Object> arrCell = new HashMap<String, Object>();
                    for (int cellNum = 0; cellNum < colum; cellNum++) {
                        Cell cell = row.getCell(cellNum);
                        if (cell == null) {
                            arrCell.put(keys.get(cellNum), " ");
                        } else {
                            //System.out.println(getValue(cell));
                            arrCell.put(keys.get(cellNum), getValue(cell));
                        }
                    }
                    for (Object obj : keys) {//遍历key
                        isEnd = arrCell.get(obj) != null && !"".equals(arrCell.get(obj).toString()) ? false : true;
                        if (!isEnd)
                            break;
                    }
                    if (isEnd) {
                        break;
                    }
                    Row.add(arrCell);
                }
            }
        } catch (IOException e) {
            System.out.println("e:" + e);
            throw e;
        }
        return Row;
    }
    private static String getValue(Cell cell) {
        String result = new String();
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC:// 数字类型  
                System.out.println(cell.getCellStyle().getDataFormat());
                if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式  
                    SimpleDateFormat sdf = null;
                    if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
                        sdf = new SimpleDateFormat("HH:mm");
                    } else {// 日期  
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                    }
                    Date date = cell.getDateCellValue();
                    result = sdf.format(date);
                } else if (cell.getCellStyle().getDataFormat() == 181) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
                    double value = cell.getNumericCellValue();
                    Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
                    result = sdf.format(date);
                } else if (cell.getCellStyle().getDataFormat() == 58) {
                    // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)  
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    double value = cell.getNumericCellValue();
                    Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
                    result = sdf.format(date);
                } else {
                    double value = cell.getNumericCellValue();
                    CellStyle style = cell.getCellStyle();
                    //                 DecimalFormat format = new DecimalFormat();  
                    //                  String temp = style.getDataFormatString();  
                    //                   // 单元格设置成常规  
                    //                  if (temp.equals("General")) {  
                    //                      format.applyPattern("#");  
                    //                  }  
                    //                  result = format.format(value);  
                    result = value + "";
                }
                break;
            case HSSFCell.CELL_TYPE_STRING:// String类型  
                result = cell.getRichStringCellValue().toString();
                break;
            case HSSFCell.CELL_TYPE_FORMULA:
                result = cell.getNumericCellValue() + "";
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                result = "";
            default:
                result = "";
                break;
        }
        return result;
    }
```excel数据入库

//readExcelrowKuaiBao是调用上面的方法
ArrayList<Map<String, Object>> row = this.readExcelrowKuaiBao(fileName, filePath, 3, 4);
for (int i = 0; i < row.size(); i++) {
//获取第0列的值，也就是excel中第一列的值
row.get(i).get("column0").toString()；
//获取第1列的值，列数依次累加
row.get(i).get("column1").toString()；

}