js html 实现excel文件上传 服务端使用java语言 Apache poi工具解析 excel中图片解析获取

一. html web页面

    <script type="text/javascript" src="../../static/plugin/zTree_v3/js/jquery-1.4.4.min.js"></script>

 <div class="layui-input-inline" >
        <span class="input" ><input type="file" id="upfile" name="upfile" style="text-align: right; margin-left: 20px; height:30px;" class="upload" /></span>
        <a class="layui-btn" id="btn_upload"><i class="layui-icon"></i>批量上传</a>
    </div>

二. js 文件上传处理

//批量上传
    $("#btn_upload").click(function () {
        var formData = new FormData();
        var name = $("#upfile").val();
        if (name === undefined) {
            layer.msg("请选择文件");
            return ;
        }
        // 校验文件
        var fileType = (name.substr(name.lastIndexOf(".") + 1, name.length)).toLowerCase();
        if (fileType !== 'xls' && fileType !== 'xlsx') {
            layer.msg('文件格式不正确，excel文件！');
            return ;
        }
        formData.append("file", $("#upfile")[0].files[0]);
        formData.append("name", name);
        $.ajax({
            url: 'http://192.168.1.10:6402' + '/app/export/deductionIntegral
            type: 'POST',
            async: false,
            data: formData,
            // 告诉jQuery不要去处理发送的数据
            processData: false,
            // 告诉jQuery不要去设置Content-Type请求头
            contentType: false,
            dataType: "json",
            beforeSend: function () {
                console.log("正在进行，请稍候");
            },
            success: function (data) {
                if (data.code == 0) {
                    layer.msg("导入成功");
                } else if (data.code == 1) {
                    layer.alert(data.message, {icon: 5})
                }
                base.reload(tableIns)
            }
        });
    })

三. 服务端 Apache.poi 解析 spring cloud框架

1.controller 层

 @PostMapping("/deductionIntegral")
    public String deductionIntegral(@RequestParam MultipartFile file){
        if (!file.isEmpty()){
            try {
                exportExcelDataService.deductionIntegral(file);
            } catch (Exception e){
                logger.error("批量导入失败！" , e);
                return ResponseUtil.fail(e.getMessage());
            }
        }else{
            return ResponseUtil.fail("Empty file!!");
        }
        return ResponseUtil.success("success");
    }

2. service 层

 public void deductionIntegral(MultipartFile file) {
 		// 数据对象容器
        List<IntegralExport> dataList = new ArrayList<>();
        // 调用工具类获取excel中的数据 亦可解析图片， 接收工具类返回数据
         String message = excelUtilWithPic.handleDataFromExcel(file, (mapList, sheet) ->{
            if(sheet.getLastRowNum() < 1){
                return;
            }
            Row row;
            Cell cell;
            String memberId;
            int integral;
            String discr;
            String tableName = IntegralUtil.getTableName("integraldetails");
            // 从第二行开始获取 不获取表头
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                row = sheet.getRow(i);
                // 描述
                cell = row.getCell(0);
                cell.setCellType(CellType.STRING);
                discr = cell.getStringCellValue().trim();
                // 积分
                cell = row.getCell(1);
                cell.setCellType(CellType.NUMERIC);
                integral = (int) cell.getNumericCellValue();
                // 用户id
                cell = row.getCell(3);
                cell.setCellType(CellType.STRING);
                memberId = cell.getStringCellValue().trim();

                if (StringUtils.isNotBlank(memberId) && integral < 0 && StringUtils.isNotBlank(discr)){
                    dataList.add(new IntegralExport(discr, integral, integraltype, Long.valueOf(memberId), tableName));
                } else {
                    logger.info("批量导入，数据空行，行号 ：" + (i + 1));
                }
            }
        });
        if (dataList.size() == 0){
            return;
        }
        // 拿到数据后 做自己的处理
        insertData(dataList);
    }

四. excel解析工具类

 public String handleDataFromExcel(MultipartFile file, BiConsumer<Map<String, List<String>>, Sheet> biConsumer) {
        Workbook workbook = null;
        try {
        	// 文件校验
            String fileName = file.getOriginalFilename();
            if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
                return "上传文件格式不正确";
            }
            boolean isExcel2003 = true;
            if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
                isExcel2003 = false;
            }
            InputStream is = file.getInputStream();
			// 根据文件格式 获取对应的解析方式
            if (isExcel2003) {
                workbook = new HSSFWorkbook(is);
            } else {
                workbook = new XSSFWorkbook(is);
            }
            Map<String, List<String>> maplist =null;
            Sheet sheet = workbook.getSheetAt(0);
            // 解析图片,放入map
            if (isExcel2003) {
                maplist = getPicturesXlsMulit((HSSFSheet) sheet);
            } else if (fileName.endsWith(".xlsx")) {
                maplist = getPicturesXlsxMulit((XSSFSheet) sheet);
            }
            // 通过 BiConsumer<T, U> 函数式接口封装两个参数， 
            // 调用方通过lambda表达式，对封装的参数进行处理 后， accept方法后的逻辑继续执行
            biConsumer.accept(maplist, sheet);
            workbook.close();
        } catch (IOException e) {
            logger.error("解析带图片excel失败", e);
            return "解析带图片excel失败";
        }
        return null;
    }
    /**
     * 获取图片和位置 (xlsx)
     * 获取一个单元格中的多个图片
     */
 public Map<String, List<String>> getPicturesXlsxMulit(XSSFSheet sheet) throws IOException {
        Map<String, List<String>> map = new HashMap<String, List<String>>();
        List<POIXMLDocumentPart> list = sheet.getRelations();
        for (POIXMLDocumentPart part : list) {
            if (part instanceof XSSFDrawing) {
                XSSFDrawing drawing = (XSSFDrawing) part;
                List<XSSFShape> shapes = drawing.getShapes();
                for (XSSFShape shape : shapes) {
                    XSSFPicture picture = (XSSFPicture) shape;
                    XSSFClientAnchor anchor = picture.getPreferredSize();
                    CTMarker marker = anchor.getFrom();
                    String key = marker.getRow() + "-" + marker.getCol();
                    String img=byteToMultipartFile(picture.getPictureData().getData());
                    List<String> list1;
                    List<String> list2=map.get(key);
                    if(list2==null){
                        list1=new ArrayList<>();
                        list1.add(img);
                        map.put(key, list1);
                    }else{
                        list2.add(img);
                        map.put(key, list2);
                    }
                }
            }
        }
        return map;
    }

  /**
     * 获取图片和位置 (xls)
     * 一个单元格中有多个图片
     */
    public Map<String, List<String>> getPicturesXlsMulit(HSSFSheet sheet) throws IOException {
        Map<String, List<String>> map = new HashMap<String, List<String>>();
        List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
        for (HSSFShape shape : list) {
            if (shape instanceof HSSFPicture) {
                HSSFPicture picture = (HSSFPicture) shape;
                HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor();
                // 行号-列号
                String key = cAnchor.getRow1() + "-" + cAnchor.getCol1();
                String img=byteToMultipartFile(picture.getPictureData().getData());
                List<String> list1;
                List<String> list2=map.get(key);
                if(list2==null){
                    list1=new ArrayList<>();
                    list1.add(img);
                    map.put(key, list1);
                }else{
                    list2.add(img);
                    map.put(key, list2);
                }
            }
        }
        return map;
    }