java实现excel表格导入数据库表，导入错误的数据展示

最新推荐文章于 2024-06-06 14:42:18 发布

破釜沉舟_.

最新推荐文章于 2024-06-06 14:42:18 发布

阅读量2.3k

点赞数

分类专栏： csv 数据库

本文链接：https://blog.csdn.net/qq_40987844/article/details/106566299

版权

数据库同时被 2 个专栏收录

6 篇文章 0 订阅

订阅专栏

csv

2 篇文章 0 订阅

订阅专栏

我做的是投票人信息的导入

投票人导入的jsp：

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ include file="/WEB-INF/jsp/common/meta.jsp"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>批量导入</title>
</head>
<body>
   <div style="padding-top: 20px;">
      <form   method="post" enctype="multipart/form-data"  id="voterImportExcel" class="hgform">
      <table width="98%" border="0" cellpadding="0" align="center" cellspacing="1" class="hgtable">
      <tr>
         <td align="center">选择导入文件<font>*</font>:
         </td>
         <td align="left" ><input name="excelFile" type="file"  class="form-input"  id="voterExcelFile" /></td>
         <td><input name="import" type="button" value="导&nbsp;&nbsp;入" οnclick="voterAjaxFileUpload()"/>&nbsp;
            <a href="${ctx}/static/template/voter.xls" target="_blank">模板下载</a>
         </td>
      </tr>
      <tr>
         <td colspan="3">
            <font>(文件必须为xls文件)</font>
         </td>
      </tr>
      <tr align="center" style="display: none" id="loadingExcel">
         <td colspan="3">正在导入，请等待 ...<br>
<%--            <img src="${ctx}/images/loadingExcel.gif">--%>
         </td>
      </tr>
   </table>
   </form>
   </div>
   <script type="text/javascript">
      $("#voterImportExcel").validate({
         rules : {
            excelFile : {
               required : true
            }
         },
         messages : {
            excelFile : {
               required : "请选择上传文件！ "
            }
         }
      });
      
      function voterAjaxFileUpload(){
         if(!$('#voterImportExcel').validate().form()) return false;
         $.messager.progress({
                title:"稍等",
                msg:"正在上传..."
            });
         $.ajaxFileUpload({ 
            //处理文件上传操作的服务器端地址
            url:"${ctx}/vote/voter/importExcel?seqId=${seqId}",
             fileElementId:'voterExcelFile',           //文件选择框的id属性
             dataType:'json',                       //服务器返回的格式,可以是json或xml等 
             success:function(data, status){
                $.messager.progress("close");
                if (data.result == "success") {
                   var msg = "上传数据成功!"
                    if(data.successNum){
                       msg += "导入成功"+data.successNum + "条。";
                    }
                   if(data.errorNum>0){
                      msg += "导入失败"+data.errorNum + "条。";
                      $.messager.ok(msg,function(){
                         var url = "/voterImportErrorDetail/showVoterImportErrorDetail?seqId=${seqId}";
                         var voterImportErrorWin = new HgWin({id:"voterImportErrorWin",title:"导入失败列表",width:850,height:500,iconCls:'m-icon-import',url:url});
                         $("#voterImportWin").window("close");
                        $('#voter_grid').datagrid("reload");
                     });
                   }else{
                      $.messager.ok(msg,function(){
                         $("#voterImportWin").window("close");
                        $('#voter_grid').datagrid("reload");
                     });
                   }
                   
                }else {
                   $.messager.alert("error",data.result);
                }       
                },
                error:function(data, status, e){ //服务器响应失败时的处理函数 
                   $.messager.progress("close");
                }     
         }); 
      }
   </script>
</body>

</html>

失败页面的jsp：

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>导入失败数据页面</title>
<%@ include file="/WEB-INF/jsp/common/meta.jsp"%>
</head>
<body>
   <div class="easyui-layout" data-options="fit:true" id="voterImportErrorDetail_layout"> 
      <form id="voterImportErrorDetail_form" class="hgform">
           <div title="导入失败列表" style="padding:1px;width: 100%;height: 339px;">
           
           <table  id="voterImportErrorDetail_grid" singleSelect=true  fitColumns=true  rownumbers="true" class="easyui-datagrid"
             data-options="url:'${ctx}/voterImportErrorDetail/list?seqId=${seqId }'"
                  pagination="true"
                  pageSize="${defaultPageSize}"
                  pageList="${defaultPageList}">
               <thead>
               <tr width="100%">
                  <th data-options="field:'detailId',width:80,hidden:true">Id</th>
                        <th data-options="field:'userName',width:120">用户名</th>
                        <th data-options="field:'phone',width:120">手机号</th>
                         <th data-options="field:'departName',width:120">部门名称</th>
                         <th data-options="field:'errorMsg',width:100">失败原因</th>
               </tr>
              </thead>
         </div> 
      </form>
   </div>
   <script type="text/javascript" src="${ctx}/static/js/vote/voterImportErrorDetail_show.js"></script>
</body>
</html>

controller：

@RequestMapping(value = "/showImportExcel")
public String showImportExcel(HttpServletRequest request,
                              HttpServletResponse response, Model model) {
   String seqId = UUID.randomUUID().toString();;
   model.addAttribute("seqId", seqId);
    OperateTemplete templete = new HttpTemplete(request) {
        protected void doSomething() throws BaseException {
            str = "vote/voter_import";
        }
    };
    return templete.operateModel();
}

@RequestMapping("/importExcel")
public void importExcel(@RequestParam(value="excelFile",required=false)MultipartFile multipartFile,
      final String seqId,HttpServletRequest request, HttpServletResponse response) throws Exception {
    Map resultMap = new HashMap();
    String result = "";
    try {
        String suffix = multipartFile.getOriginalFilename().substring(multipartFile.getOriginalFilename().lastIndexOf("."));
        if (".xls".equals(suffix)) {
            if (!multipartFile.isEmpty()) {
                resultMap = voterService.doReadXls(multipartFile.getInputStream(),seqId);
            }
            result = "success";
        } else {
            result = "导入失败,请导入xls文件";
        }

    }catch (BizException ex) {
        result = ex.getMessage();
    } catch (Exception ex) {
        result = "导入失败，请检查导入Excel的模板是否符合要求、数据的唯一性是否正确。";
        ex.printStackTrace();
    }

    resultMap.put("result",result);
}

public Map doReadXls(InputStream is,String seqId) throws Exception{
    Map<String, Object> map = new HashMap<>();
    int sum = 0;
    int error =0;
    HSSFWorkbook workBook = new HSSFWorkbook(is);
    HSSFSheet sheet = workBook.getSheetAt(0);
    Set<String> voterSet = new HashSet<String>();
    VoterImportErrorDetail detail = new VoterImportErrorDetail();
    detail.setSeqId(seqId);
    if(sheet.getLastRowNum()>101) {
        throw new BizException("最大上传100条！");
    }
    for (int rowNum = 1;rowNum <= sheet.getLastRowNum();rowNum++) {
        HSSFRow row = sheet.getRow(rowNum);
        Voter voter = new Voter();
        if (row != null) {
            //校验是否有重复的投票人信息
            try {
               if(getCellValue(row.getCell(0))!=null && getCellValue(row.getCell(1))!=null && getCellValue(row.getCell(2))!=null &&
                     !getCellValue(row.getCell(0)).isEmpty() && !getCellValue(row.getCell(1)).isEmpty() && !getCellValue(row.getCell(2)).isEmpty()) {
                  if(getCellValue(row.getCell(0)).length()>20){
                     throw new BizException("投票人姓名不能大于20字符！");
                  }
                  if(!Common.phoneVerification(getCellValue(row.getCell(1)))){
                     throw new BizException("投票人手机格式不正确！");
                  }
                  if(getCellValue(row.getCell(20)).length()>20){
                     throw new BizException("所属部门不能大于20字符！");
                  }
                  if(!voterSet.add(getCellValue(row.getCell(0)) + getCellValue(row.getCell(1)))) {
                       throw new BizException("表格内投票人重复！");
                    }
                    voter.setVoterName(getCellValue(row.getCell(0)));
                    voter.setPhone(getCellValue(row.getCell(1)));
                    voter.setDepartment(getCellValue(row.getCell(2)));
                    addVoter(voter);
                    sum += 1;
                }else {
                    throw new BizException("投票人姓名、手机号、部门不能为空！");
                }
            }catch(BizException e) {
               error += 1;
               detail.setUserName(getCellValue(row.getCell(0)));
                detail.setPhone(getCellValue(row.getCell(1)));
                detail.setDepartName(getCellValue(row.getCell(2)));
                detail.setErrorMsg(e.getMessage());
                voterImportErrorDetailService.addVoterImportErrorDetail(detail);
            }
        }
    }
    map.put("successNum", sum);
    map.put("errorNum", error);
    return map;
}

失败导入的controller：

@RequestMapping("/showVoterImportErrorDetail")
public String showVoterImportErrorDetail (final String seqId,HttpServletRequest request, HttpServletResponse response, Model model) {
   model.addAttribute("seqId",seqId);
   OperateTemplete templete = new HttpTemplete(request) {
           protected void doSomething() throws BaseException {
               str = "vote/voterImportErrorDetail_show";
           }
       };
       return templete.operateModel();
}

具体就是分页展示的了

用一个序列id（seqid）来存一次导入的所有数据，区别开，然后展示

数据库的设计：

CREATE TABLE `voter` (
`VOTER_ID` int(10) NOT NULL AUTO_INCREMENT COMMENT '主键',
`VOTER_NAME` varchar(100) DEFAULT NULL COMMENT '投票人姓名',
`PHONE` varchar(100) DEFAULT NULL COMMENT '手机号',
`DEPARTMENT` varchar(100) DEFAULT NULL COMMENT '所属部门',
`WECHAT_BINDING_STATUS` int(1) DEFAULT '0' COMMENT '微信绑定状态(0:未绑定，1:已绑定)',
`IS_VOTE` int(1) DEFAULT '0' COMMENT '是否已投票（0：否，1：是）',
`IS_DELETE_` int(1) DEFAULT '0' COMMENT '删除标识(1:已删除;0:正常)',
`IS_FINAL_` int(1) DEFAULT '0' COMMENT '是否不可修改(1:不可修改;0:可修改)',
`CRT_TIME_` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '数据创建时间',
`UPD_TIME_` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '数据最后修改时间',
`CRT_USERID_` int(10) DEFAULT NULL COMMENT '数据创建用户编号',
`UPD_USERID_` int(10) DEFAULT NULL COMMENT '数据修改用户编号',
PRIMARY KEY (`VOTER_ID`) USING BTREE,
KEY `INDEX_VOTER_ID` (`VOTER_ID`),
KEY `INDEX_VOTER_NAME` (`VOTER_NAME`),
KEY `INDEX_PHONE` (`PHONE`),
KEY `INDEX_DEPARTMENT` (`DEPARTMENT`),
KEY `INDEX_WECHAT_BINDING_STATUS` (`WECHAT_BINDING_STATUS`)
) ENGINE=InnoDB AUTO_INCREMENT=1677 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

CREATE TABLE `voter_import_error_detail` (
`DETAIL_ID_` int(111) NOT NULL COMMENT '主键ID',
`USER_NAME_` varchar(255) DEFAULT NULL COMMENT '用户名',
`PHONE_` varchar(255) DEFAULT NULL COMMENT '手机号',
`DEPART_NAME_` varchar(255) DEFAULT NULL COMMENT '部门名称',
`SEQ_ID_` varchar(50) DEFAULT NULL COMMENT '序列号',
`ERROR_MSG_` varchar(255) DEFAULT NULL COMMENT '错误信息',
`IS_DELETE_` int(1) DEFAULT '0' COMMENT '删除标识(1:已删除;0:正常)',
`IS_FINAL_` int(1) DEFAULT '1' COMMENT '是否不可修改(1:不可修改;0:可修改)',
`CRT_TIME_` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '数据创建时间',
`UPD_TIME_` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '数据最后修改时间',
`CRT_USERID_` int(10) DEFAULT NULL COMMENT '数据创建用户编号',
`UPD_USERID_` int(10) DEFAULT NULL COMMENT '数据修改用户编号',
PRIMARY KEY (`DETAIL_ID_`),
KEY `INDEX_SEQ` (`SEQ_ID_`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;