SpringMVC下载上传Excel文件

水深无声 2022-05-24 13:47 175阅读 0赞

前端框架：Bootstrap，后端框架：SpringMVC、Spring

*  下载、上传的页面如下：  
    ![这里写图片描述][70]
 *  模版内容如下：  
    ![这里写图片描述][70 1]
 *  下载、上传的前端代码如下：

<div class="modal" id="batchUpdateDigestModal" role="dialog" aria-hidden="true">
        <div class="modal-dialog">
            <div class="modal-content">
                <div class="modal-header">
                    <button type="button" class="close" data-dismiss="modal" aria-hidden="true"></button>
                    <h4 class="modal-title">文件批量补充摘要信息</h4>
                </div>
                <div class="modal-body text-center">
                    <form action="##" class="form-horizontal form-row-sepe" id="batchUpdateDigestForm">
                        <div class="form-group" style="margin-left: 10px;">
                            <a href="javascript:exportTemplateData();" id="exportTemplate" class="btn btn-sm green">
                                <i class="fa"></i>模板下载 </a> <span>（请下载模版，按照模版格式上传）</span>
                        </div>
                        <hr>
                        <div class="form-group margin-top-10">
                            <div class="col-md-6">
                                <input type="file" name="multipartFile" id="file_id" style="margin-left: 120px;">
                            </div>
                            <div class="col-md-2">
                                <button type="button" onclick="batchUpdateDigest();" class="btn btn-sm blue"
                                            style="height: 30px;">批量补充摘要
                                </button>
                            </div>
                        </div>
                    </form>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn default" data-dismiss="modal">关闭</button>
                </div>
            </div>
        </div>
    </div>

js代码：

//下载模版
    function exportTemplateData() {
            var form = document.getElementById("batchUpdateDigestForm");
            form.action = "${basePath}/bankFlowJournal/downloadTemplate.do";
            form.submit();
            form.action = "##";
        }
        
    //上传摘要文件，批量补充摘要
    function batchUpdateDigest() {
            var file_id = $("#file_id").val();
            if (file_id == null || file_id == "") {
                bootbox.alert("上传文件不能为空！");
                return;
            }
            $.startMaskLayer(); //缓冲图层打开
            $("#batchUpdateDigestForm").ajaxSubmit({
                type: "post",
                dataType: "json",
                clearForm: true,
                resetForm: true,
                url: "${basePath}/bankFlowJournal/batchUpdateDigest.do",
                success: function (data) {
                    $.endMaskLayer();//缓冲图层关闭
                    $("#batchUpdateDigestModal").modal("hide");
                    bootbox.alert(data.resultMsg); //显示返回信息
                    $("#go_search").click(); //刷新页面
                }
            });
        }

*  下载文件的JAVA代码：

@RequestMapping("/downloadTemplate")
        public void downloadTemplate(HttpServletRequest request, HttpServletResponse response) {
            try {
                String fileName = "批量补充摘要模板.xlsx";
                OutputStream out = response.getOutputStream();// 取得输出流
                response.reset();// 清空输出流
    
                //组装模版数据
                List<List<String>> data = new ArrayList<List<String>>();
                List<String> list = new ArrayList<>();
                list.add("xxxxxxxxxxxx");
                list.add("2018-01-01或2018/1/1");
                list.add("101001|xxx");
                data.add(list);
    
                //生成Excel
                String headers[] = {"流水号", "交易日期", "摘要"};
                ExportExcel<Object> eeu = new ExportExcel<Object>();
                HSSFWorkbook workbook = new HSSFWorkbook();
                eeu.exportExcel(workbook, 0, "摘要模板", headers, data, out);
    
                //输出到response
                response.setContentType("application/vnd.ms-excel;charset=UTF-8");
                response.setHeader("Content-Disposition","attachment;filename="+ new String((fileName).getBytes(), "iso-8859-1"));
                OutputStream output  = response.getOutputStream();
                workbook.write(output);
                output.flush();
                output.close();
    
            } catch (Exception e) {
                logger.error("【下载模板出错", e);
                throw new SettleException(RetCode.FAILURE, "下载模板出错");
            }
        }

*  上传文件的JAVA代码：

@RequestMapping("/batchUpdateDigest")
        @ResponseBody
        public Object batchUpdateDigest(HttpServletRequest request, MultipartFile multipartFile) {
            User user = (User) request.getSession().getAttribute(Cons.S_USER);
            logger.info("【账户流水日记账-批量更新摘要信息】操作人={}", user.getLoginName());
    
            SettleResponse response = new SettleResponse(RetCode.SUCCESS);
    
            try {
                String filename = multipartFile.getOriginalFilename();
                logger.info("begin to parse file...");
                if (null == filename || "".equals(filename)) {
                    throw new SettleException(RetCode.FAILURE, "上传文件不能为空！");
                }
                if (!filename.toUpperCase().endsWith(".XLS")) {
                    throw new SettleException(RetCode.FAILURE, "不支持此文件类型，请重新上传！");
                }
                Workbook wb = Workbook.getWorkbook(multipartFile.getInputStream());
                Sheet sheet = wb.getSheet(0);    //获得第一个表单
                int lastRow = sheet.getRows();
                BankDigestUpdateReq digestUpdateReq;
                DateCell dCell=null;
                for (int i = 1; i < lastRow; i++) {
    
                    digestUpdateReq = new BankDigestUpdateReq();
                    Cell[] cells = sheet.getRow(i);
    
                    //防止有编辑过的空行被扫描
                    if(null == cells || cells.length < 1 || CellType.EMPTY.equals(cells[0].getType())){
                        continue;
                    }
                    //非空检验
                    if (null == cells[0]) {
                        throw new SettleException(RetCode.FAILURE, "流水号不能为空，请检查文件！");
                    }
                    if (null == cells[1] || StringUtils.isBlank(cells[1].getContents())) {
                        throw new SettleException(RetCode.FAILURE, "交易日期不能为空，请检查文件！");
                    }
                    if (null == cells[2]) {
                        throw new SettleException(RetCode.FAILURE, "摘要不能为空，请检查文件！");
                    }
                    //如果是文本格式的日期，即：yyyy-MM-dd
                    if(CellType.LABEL.equals(cells[1].getType())){
                        digestUpdateReq.setTradeDate(DateUtil.SDF1().parse(cells[1].getContents()));
                    }
                    //如果是日期格式的日期，即：yyyy/MM/dd
                    else if(CellType.DATE.equals(cells[1].getType())){
                        dCell = (DateCell) cells[1];
                        digestUpdateReq.setTradeDate(dCell.getDate());
                    }
                    digestUpdateReq.setTradeNo(cells[0].getContents());
                    digestUpdateReq.setDigest(cells[2].getContents());
                    digestUpdateReq.setOperator(user.getLoginName());
    
                    BaseRes res = journalUpdateFacade.updateDigestInfo(digestUpdateReq);
                    if (!RetCode.SUCCESS.getCode().equals(res.getRetCode())) {
                        throw new SettleException(RetCode.FAILURE, res.getRetInfo());
                    }
                }
            } catch (SettleException se) {
                logger.info("解析摘要补充文件业务异常，异常原因：{}", se.getMessage());
                response.setResult(RetCode.FAILURE, se.getMessage());
            } catch (Exception ex) {
                logger.error("读取excel出错", ex);
                response.setResult(RetCode.FAILURE, "读取excel出错！");
            }
            return response;
        }

*  涉及到的pom配置：

<dependency>
    	<groupId>org.springframework.boot</groupId>
    	<artifactId>spring-boot-starter-web</artifactId>
    	<version>1.5.9.RELEASE</version>			
    </dependency>
    <dependency>
    	<groupId>org.springframework</groupId>
    	<artifactId>spring-context</artifactId>
    	<version>4.1.7.RELEASE</version>
    </dependency>
    <dependency>
    	<groupId>javax.servlet</groupId>
    	<artifactId>servlet-api</artifactId>
    	<version>2.5</version>
    </dependency>
    <dependency>
    	<groupId>commons-fileupload</groupId>
    	<artifactId>commons-fileupload</artifactId>
    	<version>1.3.1</version>
    </dependency>
    <dependency>
    	<groupId>org.apache.poi</groupId>
    	<artifactId>poi-ooxml</artifactId>
    	<version>3.9</version>
    </dependency>
    <dependency>
    	<groupId>commons-io</groupId>
    	<artifactId>commons-io</artifactId>
    	<version>2.0.1</version>
    </dependency>
    <dependency>
    	<groupId>net.sourceforge.jexcelapi</groupId>
    	<artifactId>jxl</artifactId>
    	<version>2.6.12</version>
    </dependency>

[70]: /images/20220524/7798b627b3ed4743bfad35faefe662e3.png
[70 1]: /images/20220524/41d57227c2d84c97bee67f10cb473786.png