上传Excel，并解析

pom依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.0</version>
    <scope>compile</scope>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.0</version>
    <scope>compile</scope>
</dependency>

 

 

1.工具类方法，检测上传目录，若不存在则创建

    private void judgeDirExists(String uoloadPath) {
        try {
            File targetPatchFile = new File(uploadPath);
            if (!targetPatchFile.exists()) {
                targetPatchFile.mkdirs();
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("存储目录创建失败");
        }
    }

 

2.解析Excel

    private static HashMap<String, ArrayList<String[]>> analysisFile(MultipartFile file) throws IOException {
        HashMap<String, ArrayList<String[]>> hashMap = new HashMap<>();
        String msg = "";
        //  获取workbook对象
        Workbook workbook = null;
        String filename = file.getOriginalFilename();
        InputStream inputStream = file.getInputStream();
        //  根据后缀名是否excel文件
        if (filename.endsWith("xls")) {
            //  2003版本
            workbook = new HSSFWorkbook(inputStream);
        } else if (filename.endsWith("xlsx")) {
            //  2007版本
            workbook = new XSSFWorkbook(inputStream);
        }
        //  创建arrayList，把每一行作为一个String数组，存到集合中
        ArrayList<String[]> arrayList = new ArrayList<>();
        if (workbook != null) {
            //  只解析第一个sheet
            Sheet sheet = workbook.getSheetAt(0);
            if (sheet == null) {
                hashMap.put("Excel文件为空!", arrayList);
                return hashMap;
            }
            //  默认第一行为标题栏，从第二行开始解析
            int firstRowNum = 1;
            //  获取sheet中数据的总行数
            int lastRowNum = sheet.getPhysicalNumberOfRows();
            //  获取列数。默认第一行为0
            short firstCellNum = 0;
            //  获取标题栏的总列数
            int lastCellNum = sheet.getRow(0).getPhysicalNumberOfCells();
            //  循环每一行
            for (int rowNum = firstRowNum; rowNum < lastRowNum; rowNum++) {
                //  获取当前行
                Row row = sheet.getRow(rowNum);
                String[] strings = new String[lastCellNum];
                //  循环当前行的每一列
                for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
                    Cell cell = row.getCell(cellNum);
                    if (cell == null || "".equals(cell) || cell.getCellType() == CellType.BLANK) {
                        msg = msg + "第" + (rowNum + 1) + "行,第" + (cellNum + 1) + "列为空;";
                    }
                    String cellValue = "";
                    cellValue = getCellValue(cell);
                    strings[cellNum] = cellValue;
                }
                arrayList.add(strings);
            }
        }
        inputStream.close();
        hashMap.put(msg, arrayList);
        return hashMap;
    }

 

3.单位内容转为String

    public static String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        //  判断数据的类型
        switch (cell.getCellType()) {
            //数字0
            case NUMERIC:
                cellValue = NumberToTextConverter.toText(cell.getNumericCellValue());
                break;
            //字符串1
            case STRING:
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            //Boolean
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            //公式
            case FORMULA:
                try {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                } catch (IllegalStateException e) {
                    cellValue = String.valueOf(cell.getRichStringCellValue());
                }
                break;
            //空值
            case BLANK:
                cellValue = "";
                break;
            //故障
            case ERROR:
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }