原创

PoI解析

/**
     * 将Excel文件解析为Map结构
     * @param inputStream
     * @return
     */
    public static Map<String, List<Map<String, Object>>> excelConversionMap(InputStream inputStream,String version) {
        Map<String, List<Map<String, Object>>> excelMap = new HashMap<>(16);
        try {
            Workbook workbook = null;
            if ("07".equals(version)){
                workbook = new XSSFWorkbook(inputStream);
            }else {
                workbook = new HSSFWorkbook();
            }
            int sheetSize = workbook.getNumberOfSheets();

            //处理 sheet
            for (int i = 0; i < sheetSize; i++) {
                Sheet sheet = workbook.getSheetAt(i);
                List<Map<String, Object>> sheetMap = new ArrayList<>();
                int rowSize = sheet.getPhysicalNumberOfRows();
                int startRow = 0;

                //处理标题行
                List<String> keys = new ArrayList<>();
                for (int j = 0; j < rowSize; j++) {
                    Row row = sheet.getRow(j);
                    int cellSize = row.getPhysicalNumberOfCells();
                    boolean flag = false;
                    for (int x = 0; x < cellSize; x++) {

                        //处理本行内是否有合并单元格
                        Result mergedRegion = isMergedRegion(sheet, row.getRowNum(), x);
                        if (mergedRegion.merged) {
                            flag = true;
                        }
                    }
                    //如果有不规范单元格(合并后的单元格),跳过,不将此行作为标题行
                    if (flag) {
                        continue;
                    }

                    //将标题行 设置到keys中
                    for (int k = 0; k < cellSize; k++) {
                        Cell cell = row.getCell(k);
                        if (cell != null) {
                            keys.add(cell.getStringCellValue());
                        }
                    }
                    startRow = j + 1;
                    break;
                }

                //处理每一行数据,封装为Map 放入List
                for (int j = startRow; j < rowSize; j++) {
                    Row row = sheet.getRow(j);
                    int cellSize = row.getPhysicalNumberOfCells();
                    Map<String, Object> data = new HashMap<>(32);
                    for (int k = 0; k < cellSize; k++) {
                        Cell cell = row.getCell(k);
                        if (cell == null) {
                            continue;
                        }
                        CellType type = cell.getCellType();
                        switch (type) {
                            case STRING:
                                data.put(keys.get(k), cell.getStringCellValue());
                                break;
                            case NUMERIC:
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    data.put(keys.get(k), cell.getDateCellValue());
                                } else {
                                    data.put(keys.get(k), cell.getNumericCellValue());
                                }
                                break;
                            case BOOLEAN:
                                data.put(keys.get(k), cell.getBooleanCellValue());
                                break;
                            case FORMULA:
                                //String cellFormula = cell.getCellFormula(); 获取公式
                                XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
                                CellValue evaluate = evaluator.evaluate(cell);
                                CellType cellType = evaluate.getCellType();
                                switch (cellType) {
                                    case STRING:
                                        data.put(keys.get(k), evaluate.getStringValue());
                                        break;
                                    case NUMERIC:
                                        data.put(keys.get(k), evaluate.getNumberValue());
                                        break;
                                    case BOOLEAN:
                                        data.put(keys.get(k), evaluate.getBooleanValue());
                                        break;
                                    case BLANK:
                                        break;
                                    case ERROR:
                                        data.put(keys.get(k), evaluate.getErrorValue());
                                    default:
                                        break;
                                }
                                break;
                            case BLANK:
                                break;
                            case ERROR:
                                data.put(keys.get(k), cell.getErrorCellValue());
                            default:
                                break;
                        }
                    }
                    //保留位置信息,方便提示
                    data.put(SHEETNAME, sheet.getSheetName());
                    data.put(ROWNUMBER, row.getRowNum() + 1);
                    sheetMap.add(data);
                }
                excelMap.put(sheet.getSheetName(), sheetMap);
            }
            return excelMap;
        } catch (IOException e) {
            throw new RuntimeException("解析文件异常!");
        }
    }
Java

留言板