java 远程文件url 转为输入流

水深无声 2023-07-08 05:48 18阅读 0赞

URL url = new URL(fileUrl);
    HttpURLConnection conn = (HttpURLConnection)url.openConnection();
    //设置超时间为3秒
    conn.setConnectTimeout(3*1000);
    //防止屏蔽程序抓取而返回403错误
    conn.setRequestProperty("User-Agent", "Mozilla/4.0 (compatible; MSIE 5.0; Windows NT; DigExt)");
    //得到输入流
    InputStream inputStream = conn.getInputStream();

public static AjaxModel parseExcelForInfo(InputStream inputStream, String fileName, int taskId) {
        try {
            //创建workbook对象
            Workbook workbook = null;
            if (fileName.contains(".xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            } else if (fileName.contains(".xls")) {
                workbook = new HSSFWorkbook(inputStream);
            } else {
                return AjaxModel.failed(-1, "文件类型不正确");
            }
            //获取第一个sheet表
            Sheet sheetAt = workbook.getSheetAt(0);
            if (sheetAt != null) {
                // TODO 校验excel头
                Row headRow = sheetAt.getRow(0);
                for (int i = 0; i < BusinessSettlementConstants.TEMPLATE_COULMN.length; i++) {
                    if (!FileUtil.getCellFormatValue(headRow.getCell(i)).trim().equals(BusinessSettlementConstants.TEMPLATE_COULMN[i])) {
                        LOGGER.info("parseExcelForInfo excel头部信息顺序不正确，getCellFormatValue(headRow.getCell(i)):{}," +
                                        "BusinessSettlementConstants.TEMPLATE_COULMN[i]:{},taskId:{}", FileUtil.getCellFormatValue(headRow.getCell(i)),
                                BusinessSettlementConstants.TEMPLATE_COULMN[i], taskId);
                        return AjaxModel.failed("excel标题头顺序不正确:" + FileUtil.getCellFormatValue(headRow.getCell(i)));
                    }
                }
                int startRowNum = sheetAt.getFirstRowNum() + 1;
                int lastRowNum = sheetAt.getLastRowNum();
    
                LOGGER.info("解析excel开始taskId:{}，从【{}】行开始,到第【{}】行结束", taskId, startRowNum, lastRowNum);
                List<Map<String, String>> dataList = new ArrayList<Map<String, String>>();
                for (int rowNum = startRowNum; rowNum <= lastRowNum; rowNum++) {
                    // 每一行数据
                    Row row = sheetAt.getRow(rowNum);
                    Map<String, String> map = new HashMap<>();
                    LOGGER.info("parseExcelForInfo row:{}", row);
                    if (row != null && row.getCell(0) != null && StringUtils.isNotEmpty(row.getCell(0).getStringCellValue()) && row.getCell(2) != null && row.getCell(4) != null) {
                        LOGGER.info("parseExcelForInfo row:{},cell:{}", row, row.getCell(0));
                        // 姓名
                        map.put("userName", FileUtil.getCellFormatValue(row.getCell(0)));
                        
                        dataList.add(map);
                    }
                }
                LOGGER.info("--------------解析完成 dataList:{}", dataList);
                if (dataList.size() <= 0) {
                    return AjaxModel.failed(-1, "解析表格数据为空");
                }
                AjaxModel success = AjaxModel.success();
                success.getData().put("dataList", dataList);
                return success;
            } else {
                LOGGER.info("sheet内容为空");
                return AjaxModel.failed(-1, "表格内容为空");
            }
        } catch (Exception e) {
            LOGGER.error("parseExcelForInfo 解析异常", e);
        }
        return AjaxModel.failed(-1, "解析表格异常");
    }

public static String getCellFormatValue(Cell cell) {
        cell.setCellType(CellType.STRING);
        return cell.getStringCellValue();
    }