java 解析 Excel 文件简单代码

	public int uploadGoodsStockFileAnalysis(MultipartFile file) {
        InputStream inputStream = null;
        Workbook rwb = null;

        User user = AuthUtils.getUserInfoOfSession();
        Assert.notNull(user, "未登录");

        // 货物库存列表
        List<GoodsStock> goodsStockList = new ArrayList<>();

        // 仓库列表
        Warehouse example = new Warehouse();
        example.setCompanyId(user.getCompanyId());
        example.setDeleted(false);
        List<Warehouse> warehouseList = warehouseMapper.queryWarehouse(example);
        Map<String, Long> map = new HashMap<>();
        for (Warehouse warehouse : warehouseList) {
            map.put(warehouse.getWarehouseName(),warehouse.getWarehouseId());
        }

        long fileSize = file.getSize();     // 文件大小
        String originalName = file.getOriginalFilename();
        log.info(originalName);
        log.info("大小： {}", fileSize);
        log.info(file.getContentType());

        try {
            inputStream = file.getInputStream();
            if(originalName.endsWith(".xlsx")){
                rwb = new XSSFWorkbook(inputStream);
            }else  if (originalName.endsWith(".xls") || originalName.endsWith(".et")){
                rwb = new HSSFWorkbook(inputStream);
            }else {
                throw new WhseInnerException(ResultStatus.ERROR_2002.getStatus(), "请上传Excel格式文件");
            }

            Sheet sheet = rwb.getSheetAt(0);
            // 获取行
            Iterator<Row> rows = sheet.rowIterator();
            Row row;
            while (rows.hasNext()){
                row = rows.next();
                int rowNum = row.getRowNum();
                if(rowNum==0)continue;
                if(StringUtils.isEmpty(getCellValue(row.getCell(1))) && getCellValue(row.getCell(2)).equals("合计"))break;
                GoodsStock goodsStock = new GoodsStock();
                goodsStock.setCompanyId(user.getCompanyId());
                goodsStock.setSourceType("导入");
                goodsStock.setCreateTime(new Date());
                goodsStock.setWarehouseId(map.get(getCellValue(row.getCell(1))));   // 仓库ID
                goodsStock.setGoodsName(getCellValue(row.getCell(2)));    // 物品名称
                goodsStock.setBatchNo(getCellValue(row.getCell(3)));      // 批次号
                goodsStock.setStockAmount(new BigDecimal(getCellValue(row.getCell(4))));    // 库存量
                goodsStock.setGrossWeight(new BigDecimal(getCellValue(row.getCell(5))));    // 毛重
                goodsStock.setGoodsType(getCellValue(row.getCell(6)).equals("普货")?"p":"w");// 物品类型
                goodsStock.setSpecs(getCellValue(row.getCell(7)));        // 规格型号
                goodsStock.setDocNo(getCellValue(row.getCell(8)));        // 单据号
                goodsStock.setRemark1(getCellValue(row.getCell(9)));      // 备注1
                goodsStock.setRemark2(getCellValue(row.getCell(10)));     // 备注2
                goodsStock.setRemark3(getCellValue(row.getCell(11)));     // 备注3
                goodsStockList.add(goodsStock);
            }
            return goodsStockMapper.insertBatch(goodsStockList);
        }catch (IOException e) {
            e.printStackTrace();
            throw new WhseInnerException();
        }

    }



	/**
     * 【内部工具】
     *
     * 获取cell中的值并返回String类型
     *
     */
    private static String getCellValue(Cell cell) {
        String cellValue = "";
        if (null != cell) {
            // 以下是判断数据的类型
            switch (cell.getCellTypeEnum()) {  //cell.getCellTypeEnum()  cell.getCellType()
                case NUMERIC: // 数字  // HSSFCell.CELL_TYPE_NUMERIC
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {// 判断是否为日期类型
                        Date date = cell.getDateCellValue();
                        DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm");
                        //DateFormat formater = new SimpleDateFormat("yyyy/MM/dd");
                        cellValue = formater.format(date);
                    } else {
                        // 有些数字过大，直接输出使用的是科学计数法： 2.67458622E8 要进行处理
                        DecimalFormat df = new DecimalFormat("####.####");
                        cellValue = df.format(cell.getNumericCellValue());
                        // cellValue = cell.getNumericCellValue() + "";
                    }
                    break;
                case STRING: // 字符串
                    cellValue = cell.getStringCellValue();
                    break;
                case BOOLEAN: // Boolean
                    cellValue = cell.getBooleanCellValue() + "";
                    break;
                case FORMULA: // 公式
                    try {
                        // 如果公式结果为字符串
                        cellValue = String.valueOf(cell.getStringCellValue());
                    } catch (IllegalStateException e) {
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {// 判断是否为日期类型
                            Date date = cell.getDateCellValue();
//                      DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm");
                            DateFormat formater = new SimpleDateFormat("yyyy/MM/dd");
                            cellValue = formater.format(date);
                        } else {
                            FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper()
                                    .createFormulaEvaluator();
                            //evaluator.evaluateFormulaCell(cell);
                            evaluator.evaluate(cell);
                            // 有些数字过大，直接输出使用的是科学计数法： 2.67458622E8 要进行处理
                            DecimalFormat df = new DecimalFormat("####.####");
                            cellValue = df.format(cell.getNumericCellValue());
//                          cellValue = cell.getNumericCellValue() + "";
                        }
                    }
//              //直接获取公式
//              cellValue = cell.getCellFormula() + "";
                    break;
                case BLANK: // 空值
                    cellValue = "";
                    break;
                case ERROR: // 故障
                    cellValue = "非法字符";
                    break;
                default:
                    cellValue = "未知类型";
                    break;
            }
        }
        return cellValue;
    }