POI导入Excel附件上传

最新推荐文章于 2024-12-22 00:49:37 发布

原创最新推荐文章于 2024-12-22 00:49:37 发布 · 950 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#POI #Excel导入 #附件上传

java 专栏收录该内容

20 篇文章

订阅专栏

博客介绍了Excel附件上传相关内容，包含前台页面和后台处理。后台会验证上传文件，成功后保存文件并返回路径，接着通过该路径打开Excel文件进行解析，最终将数据存入数据库。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

-------------------------------------------前台页面---------------------------------------------------

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ include file="/common/taglibs.jsp"%>
<%
    String path = request.getContextPath();
    String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<script>
    $(function () {
            var uploader = new plupload.Uploader({
            runtimes : 'html5,flash,silverlight,html4',
            //触发文件选择对话框的按钮，为那个元素id
            browse_button : 'pickfiles',
            //服务器端的上传页面地址
            url : '<%=basePath%>sysuser/import.do?i=${i}',
            //swf文件，当需要使用swf方式进行上传时需要配置该参数
            flash_swf_url : '../js/Moxie.swf',
            //silverlight文件，当需要使用silverlight方式进行上传时需要配置该参数
            silverlight_xap_url : '../js/Moxie.xap',
            //是否可以在文件浏览对话框中选择多个文件
            multi_selection:true,
            filters : {
                mime_types : [ //只允许上传图片
                    { title : "Image files", extensions : "jpg,gif,png,doc,docx,pdf,xlsx,xls" }
                ],
                max_file_size : '50mb', //最大只能上传50mb的文件
                prevent_duplicates : true //不允许选取重复文件
            },
            init: {
                PostInit: function() {
                    document.getElementById('uploadfiles').onclick = function() {
                        uploader.start();
                        return false;
                    };
                },
                FilesAdded:function(up,files){
                    plupload.each(files, function(file) {
                        document.getElementById('upload-file-list').value = file.name ;
                        document.getElementById('submit').disabled = true;
                    });
                },
                FileUploaded:function(up,file,response){
//                    var user=JSON.parse(response.response).user
                    var fileUrl=JSON.parse(response.response).fileUrl
                    var flog=JSON.parse(response.response).flog
                    console.log(flog)
                    document.getElementById('submit').disabled=flog
                    document.getElementById('fileUrl').value=fileUrl
                    document.getElementById('type').value=JSON.parse(response.response).type
                    var list=JSON.parse(response.response).list
                    var tbBody=document.getElementById("tbBody")
                    var tr=document.createElement("tr")
//                    tr.setAttribute("id", list[1].userCardId)
                    var text=null
                    console.log(list)
                    console.log(list.length)
                    for(var j=0;j<list.length+1;j++){
                        var tr=document.createElement("tr")
                        for(var i=0;i<5;i++) {
                            var td = document.createElement("td")
                            td.setAttribute("align","center")
                            if(i==0){if(j==0){
                                text = document.createTextNode("姓名");
                            }else {
                                text = document.createTextNode(list[j - 1].userName);
                            }
                            }else if(i==1) {
                                if(j==0){
                                    text = document.createTextNode("登录名");
                                }else {
                                    text = document.createTextNode(list[j-1].userId);
                                }
                            }else if(i==2){
                                if(j==0){
                                    text = document.createTextNode("性别");
                                }else {
                                    text = document.createTextNode(list[j-1].userSex);
                                }
                            }else if(i==3){
                                if(j==0){
                                    text = document.createTextNode("身份证");
                                }else {
                                    text = document.createTextNode(list[j-1].userCardId);
                                }
                            }else if(i==4){
                                if(j==0){
                                    text = document.createTextNode("用户类型");
                                }else {
                                    text = document.createTextNode(list[j-1].userType);
                                }
                            }
                            td.appendChild(text)
                            tr.appendChild(td)
                        }
                        tbBody.appendChild(tr)
                    }

//                  $("#uploadfiles").bjuiajax('refreshlayout', { target:$("#tabelRe")});
//                  $("#uploadfiles").dialog('refresh')
                },
                Error: function(up, err) {
                    $.CurrentDialog.alertmsg("error", err.message);
                }
            }
        });

        uploader.init();

    })
</script>
<base href="<%=basePath %>">
<div class="bjui-pageContent">
    <form action="<%=basePath%>sysuser/import-save.do" method="post" data-toggle="validate" data-reloadNavtab="false">
        <input type="hidden" name="fileUrl" id="fileUrl">
        <input type="hidden" name="orgId"  value="${orgId}">
        <input type="hidden" name="type" id="type">
        <table class="table table-condensed table-hover">
            <tbody>
            <tr>
                <td id="columnArticleTitlePicForm">
                    <table width="70%" border="0" cellspacing="0" cellpadding="0">
                        <tr>
                            <td width="15%"><label  class="control-label x90" style="width: 105px;">文件名称：</label></td>
                            <td>
                                <input id="upload-file-list" size="30" readonly="readonly"/>
                            </td>
                            <td>
                                <button id="pickfiles" class="btn-default " data-icon="hand-pointer-o">浏览</button>
                            </td>
                            <td>
                                <button id="uploadfiles" class="btn-blue" data-icon="upload" >上传</button>
                            </td>
                        </tr>
                    </table>
                    <table id="tabelRe" class="table table-bordered table-hover table-striped table-top" >
                        <tbody id="tbBody">
                        <tr>
                            <%--<td align="center" width="30%">文件名称</td>--%>
                            <%--<td align="center" width="35%">上传时间</td>--%>
                        </tr>
                        </tbody>

                    </table>
                </td>
            </tr>
            </tbody>
        </table>
    </form>

</div>
<div class="bjui-pageFooter">
    <ul>
        <li><button type="button" class="btn-close">关闭</button></li>
        <li><button type="submit" id="submit" class="btn-default" disabled="true">保存</button></li>
    </ul>
</div>

----------------------------------------------后台---------------------------------------------------

--------------验证上传文件返回成功保存文件并返回filepath------------

@RequestMapping("import")
   @ResponseBody
   public Map<String, Object> imports(@RequestParam MultipartFile file,Model model, HttpSession session,String i) {
      Map<String, Object> resMap = new HashMap<String, Object>();
      VUser user=(VUser)session.getAttribute("user_session");
      resMap.put("user", user);
      model.addAttribute("message", "File '" + file.getOriginalFilename());
      System.out.println(i);
//        String bol="yes";
      String fileOriginalName = file.getOriginalFilename();
      String statusCode = "200", message = "上传成功";
      List<GxSysUser> list=new ArrayList<>();
      String type=null;
      try {
         if (!StringUtils.isEmpty(fileOriginalName)) {
//          FileUtil fileHelper = new FileUtil();
//          String decodeFileName = fileHelper.getDecodeFileName(fileOriginalName);// 文件名编码
//          String mFilePath = session.getServletContext().getRealPath("") + decodeFileName; // 取得服务器路径
//          mFilePath = mFilePath.substring(0, 2) + "\\njdtjsis" + "\\hiddenDanger\\" + decodeFileName;
//          fileHelper.createFile(mFilePath, file.getBytes());
//              FileInputStream fileIn = new FileInputStream(mFilePath);
            //----------------------------------------------------------------
            Workbook wb0=null;
            if(file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".")).equals(".xlsx")){
               wb0=new XSSFWorkbook(file.getInputStream());
               type="ByteArrayInputStream";
            }else if(file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".")).equals(".xls")){
               wb0=new HSSFWorkbook(file.getInputStream());
               type="FileInputStream";
            }
            Sheet sheet = wb0.getSheetAt(0);
            for(Row row:sheet){
               if(row.getCell(1)==null){
                  break;
               }
               GxSysUser gxSysUser=new GxSysUser();
               Cell cell0 = row.getCell(1);
               Cell cell3 = row.getCell(3);
               if(cell0!=null&&cell3!=null){
                  cell0.setCellType(Cell.CELL_TYPE_STRING);
                  cell3.setCellType(Cell.CELL_TYPE_STRING);
               }
               GxSysUser u=sysUserManager.findUniqueBy("userId",row.getCell(1).getStringCellValue());
               GxSysUser u1=sysUserManager.findUniqueBy("userCardId",row.getCell(3).getStringCellValue());
               if(u!=null||u1!=null){
                  gxSysUser.setUserName(row.getCell(0).getStringCellValue());
                  gxSysUser.setUserId(row.getCell(1).getStringCellValue());
                  gxSysUser.setUserSex(row.getCell(2).getStringCellValue());
                  gxSysUser.setUserCardId(row.getCell(3).getStringCellValue());
                  gxSysUser.setUserType(row.getCell(4)!=null?row.getCell(4).getStringCellValue():"");
                  if(u!=null){
                     gxSysUser.setUserId("用户名已存在");
                  }
                  if(u1!=null){
                     gxSysUser.setUserCardId("身份证已存在");
                  }
                  list.add(gxSysUser);
               }
            }
            if(list.size()==0){
               FileUtil fileHelper = new FileUtil();
               String decodeFileName = fileHelper.getDecodeFileName(fileOriginalName);// 文件名编码
               String mFilePath = session.getServletContext().getRealPath("") + decodeFileName; // 取得服务器路径
               mFilePath = mFilePath.substring(0, 2) + "\\njdtjsis" + "\\userinfo\\" + decodeFileName;
               fileHelper.createFile(mFilePath, file.getBytes());
               resMap.put("fileUrl",mFilePath);
               resMap.put("flog",false);
            }else {
               resMap.put("flog",true);
            }
            file.getInputStream().close();
         }
      } catch (Exception e) {
         statusCode = "300";
         message = "上传失败";
         resMap.put("flog",true);
//            bol="no";
         e.printStackTrace();
      }
      resMap.put("list", list);
      resMap.put("type",type);
      resMap.put("statusCode", statusCode);
      resMap.put("message", message);
      return resMap;
   }

--------------通过返回filepath打开Excel并解析入库------------

@RequestMapping("import-save")
public @ResponseBody
Map<String, Object> UserSaveImport(String fileUrl,HttpSession session,String orgId,String type) {
   Map<String, Object> resMap = new HashMap<String, Object>();
   String statusCode = "200", message = "操作成功";
   VUser gxuser = (VUser) (session.getAttribute("user_session") == null ? null
         : session.getAttribute("user_session"));

   Workbook wb0=null;
   try {
      Map<String, Object> parameterMap = new HashMap<String, Object>();
      InputStream in= new FileInputStream(fileUrl);
      if(type.equals("ByteArrayInputStream")){
         wb0=new XSSFWorkbook(in);
      }else {
         wb0=new HSSFWorkbook(in);
      }

      Sheet sheet = wb0.getSheetAt(0);
      for(Row row:sheet) {
         if(row.getCell(1)==null){
            break;
         }
         GxSysUser gxSysUser=new GxSysUser();
         gxSysUser.setRowId(null);
         gxSysUser.setUserName(row.getCell(0).getStringCellValue());
         gxSysUser.setUserShowName(row.getCell(0).getStringCellValue());
         row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
         gxSysUser.setUserId(row.getCell(1).getStringCellValue());
         gxSysUser.setUserMobileNum(row.getCell(1).getStringCellValue());
         gxSysUser.setUserEnName("123");
         gxSysUser.setUserSex(row.getCell(2).getStringCellValue().equals("男")?"1":"0");
         gxSysUser.setUserCardId(row.getCell(3).getStringCellValue());
         gxSysUser.setUserType(row.getCell(4)!=null?row.getCell(4).getStringCellValue():"");
         gxSysUser.setCreateTime(new Timestamp(new Date().getTime()));

         GxSysUserInOrgCopy gxSysUserInOrgCopy = new GxSysUserInOrgCopy();
         gxSysUserInOrgCopy.setCreateTime(new Timestamp(new Date().getTime()));
         gxSysUserInOrgCopy.setCreateUserId(gxuser != null ? gxuser
               .getUserId() : "");
         gxSysUserInOrgCopy.setOrgId(orgId);
         gxSysUserInOrgCopy.setUserId(gxSysUser.getUserId());

         String hql = "SELECT max(t.dataOrder) FROM  GxSysUserInOrgCopy t where t.orgId=?";
         List maxNum = gxSysUserInOrgCopyManager.find(hql, orgId);
         int num = 1;
         if (maxNum.get(0)==null) {
            num=Integer.parseInt(orgDataOrderDefaultManager.findUniqueBy("orgId",orgId).getDataOrder());
         } else {
            Object obj = maxNum.get(0);
            num = !StringUtils.validateLong(obj) ? 1 : new BigDecimal(
                  obj.toString()).intValue() + 1;
         }
         gxSysUserInOrgCopy.setDataOrder(num);
         gxSysUserInOrgCopyManager.save(gxSysUserInOrgCopy);
         gxUserManager.save(gxSysUser);
      }
   } catch (Exception e) {
      statusCode = "300";
      message = "操作失败";
      e.printStackTrace();
   }
   resMap.put("statusCode", statusCode);
   resMap.put("message", message);
   resMap.put("closeCurrent", true);
   resMap.put("divid", "user-manager-user-list1");

   return resMap;

}