java导入Excel表格数据

佚名 7年前 (2019-05-15) 随笔 835人围观抢沙发百度已收录

首先导入Excel数据需要几样东西

第一需要两个依赖包，这里直接是在pom注入依赖

SRE实战互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。

<!--excel-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.10.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>

第二就是需要一个工具类，就和分页的工具类一个性质，这里就不贴出来了太长。百度搜索 “ExcelUtil”

代码中用到的js文件 jquery-3.1.1.min.js、layer.js

下面开始贴js代码

          //h5的话，写一个文件域就好了  
　　　　　　//上传Excel文件
　　　　　　　　uploadFile() {
                var file = $("#upload").val();
                file = file.substring(file.lastIndexOf('.'), file.length);
                if (file == '') {
                    layer.open({
                        content: '上传文件不能为空！'
                        , skin: 'msg'
                        , time: 2 //2秒后自动关闭
                    });
                } else if (file != '.xlsx' && file != '.xls') {
                    layer.open({
                        content: '请选择正确的excel类型文件！'
                        , skin: 'msg'
                        , time: 2 //2秒后自动关闭
                    });
                } else {
                    this.ajaxFileUpload();
                }
            },
            ajaxFileUpload() {
                var formData = new FormData();
                formData.append("file", document.getElementById("upload").files[0]);
                $.ajax({
                    url: "${ctx}/bookcase/InputExcel",
                    type: "POST",
                    async: true,
                    data: formData,
                    processData: false,
                    contentType: false,
                    beforeSend: function () {
                        layer.open({
                            type: 2
                            , content: '文件上传中，请稍候'
                        });
                    },
                    success: function (data) {
                        layer.closeAll();
                        data = JSON.parse(data);
                        if (data.state == 200) {
                            layer.open({
                                content: data.message
                                , skin: 'msg'
                                , time: 1 //2秒后自动关闭
                            });
                            window.location.reload();
                        } else {
                            layer.open({
                                content: data.message
                                , skin: 'msg'
                                , time: 2 //2秒后自动关闭
                            });
                        }
                    }
                });
            }
        },

到这里页面的的操作就差不多了，下面是java的操作

@RequestMapping("InputExcel")
    @ResponseBody
    public ResultEntity InputExcel(@RequestParam("file") MultipartFile file, HttpServletRequest request) {
        ResultEntity result = new ResultEntity();
        if (!file.isEmpty()) {
            try {
                //获取原始的文件名
                String originalFilename = file.getOriginalFilename();
                String fileType = originalFilename.substring(originalFilename.lastIndexOf(".") + 1, originalFilename.length());
                //默认从第一行开始读取
                Integer startRows = 1;
                //获取输入流
                InputStream is = file.getInputStream();
                List<DoorAntRel> bindingList = new ArrayList<>();
                List<Bookcase> bookcaseList = new ArrayList<>();
                List<String[]> strings = ExcelUtil.readData(fileType, startRows, true, is);//这里使用输入流把数据拿到
                //遍历Excel表每一行的数据
                for (String[] str : strings) {//这里比较特殊，肯定有更简便的办法，暂时我就只有这个水平、、、尴尬
                    Bookcase bookcase = new Bookcase();
                    DoorAntRel doorAntRel = new DoorAntRel();
                    bookcase.setName(str[1]);
                    bookcase.setType(str[2]);
                    bookcase.setCom(Integer.parseInt(str[3]));
                    doorAntRel.setDoorName(str[4]);
                    doorAntRel.setDoorNo(Integer.parseInt(str[5]));
                    doorAntRel.setAntennaNo(Integer.parseInt(str[6]));
                    doorAntRel.setReadWriterId(Integer.parseInt(str[7]));
                    doorAntRel.setBookcaseId(Integer.parseInt(str[8]));
                    doorAntRel.setBadFlag(Integer.parseInt(str[9]));
                    doorAntRel.setDoorSlot(Integer.parseInt(str[10]));
                    bindingList.add(doorAntRel);
                    bookcaseList.add(bookcase);
                }
                boolean bookState = bookcaseService.insertOrUpdateBatch(bookcaseList);
                boolean doorState = doorAntRelService.insertOrUpdateBatch(bindingList);
                if(bookState){
                    if(doorState){
                        result.setState(HttpCode.SUCCESS);
                        result.setMessage("上传文件成功！");
                        return result;
                    }
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        result.setState(HttpCode.FAILED);
        result.setMessage("上传文件失败！");
        return result;
    }