Java解析Excel文件

1 添加依赖

.xls格式的excel文件需要HSSF支持，需要相应的poi.jar，.xlsx格式的excel文件需要XSSF支持，需要poi-ooxml.jar，

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

2 解析

/**
     * 根据不同的格式获取不同的对象
     *
     * @param in       输入流
     * @param fileName 文件名
     * @return Excel的对象
     * @throws Exception
     */
    public Workbook getWorkbook(InputStream in, String fileName) throws Exception {
        Workbook workbook = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));//获得后缀
        if (".xls".equals(fileType)) {
            workbook = new HSSFWorkbook(in);
        } else if (".xlsx".equals(fileType)) {
            workbook = new XSSFWorkbook(in);
        } else {
            throw new Exception("请上传excel文件！");
        }
        return workbook;
    }
    
	public void readExcel(InputStream in, String fileName){
		 try {
	            Workbook wb = getWorkbook(in, fileName);//获得正确的流
	            //获取第一个sheet页
                Sheet sheet=  wb.getSheetAt(0);
	            int firstRowIndex = sheet.getFirstRowNum()+1;   //第一行是列名，所以不读
	                int lastRowIndex = sheet.getLastRowNum();
	                System.out.println("firstRowIndex: "+firstRowIndex);
	                System.out.println("lastRowIndex: "+lastRowIndex);
	
	                for(int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) {   //遍历行
	                    System.out.println("rIndex: " + rIndex);
	                    Row row = sheet.getRow(rIndex);
	                    if (row != null) {
	                        int firstCellIndex = row.getFirstCellNum();
		                    int lastCellIndex = row.getLastCellNum();
		                    System.out.println("firstCellIndex = " + firstCellIndex + ".lastCellIndex = " + lastCellIndex);
		                    for (int cIndex = firstCellIndex; cIndex < lastCellIndex; cIndex++) {   //遍历列
		                        Cell cell = row.getCell(cIndex);
		                        if (cell != null) {
		                            System.out.print(getCellFormatValue(cell) + " ");
		                        }
		                    }
		                }
		                System.out.println();
	                }
	 	} catch (Exception e) {
	          e.printStackTrace();
	    }
 }
 
 public Object getCellFormatValue(Cell cell) {
        Object cellValue = null;
        if (cell == null) {
            return "";
        }

        //cell 类型
        CellType cellType = cell.getCellTypeEnum();

        // 判断cell类型
        switch (cellType) {
            //数字类型
            case NUMERIC: {
                cellValue = NumberToTextConverter.toText(cell.getNumericCellValue());
                break;
            }
            //字符串类型
            case STRING: {
                cellValue = cell.getRichStringCellValue().getString();
                break;
            }
            //空
            case BLANK: {
                cellValue = "";
                break;
            }
            default:
                System.out.println("无法解析的类型");
                cellValue = "";
        }
        return cellValue;
    }

3 Controller调用

@PostMapping("/uploadExcel")
    public void uploadExcel(@RequestBody MultipartFile file)  {
        try {
            InputStream inputStream = file.getInputStream();
            excelImportService.readExcel(inputStream, file.getOriginalFilename());
            inputStream.close();
        } catch (Exception ex) {
        }
    }

参考博客：https://blog.csdn.net/gxx_csdn/article/details/79085713