SpringMVC使用poi上传Excel读取Excel实例（包含防止重复提交）

来源：互联网发布：sql 统计表个数编辑：程序博客网时间：2024/06/07 05:24

1、页面

<span style="font-size:18px;"><%@ page language="java" pageEncoding="UTF-8"%><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"><html xmlns="http://www.w3.org/1999/xhtml">  <head><%@include file="/pages/include/base-head.jsp"%><%-- <script type="text/javascript" src="${skin}/js/flow/batchrecharge.js"></script> --%>  </head><body><form id="dataform" method="post" enctype="multipart/form-data" action="${contextPath}/flow/public/batchRecharge" onsubmit="return verification()"><table style="align:center">  <tbody>     <tr>        <td>                        批量充值Excel模板：                    <img src="${skin}/image/flow/exceltemplate.png"/>        </td>     </tr>     <tr><td> </td></tr>     <tr>        <td>         <input type="file" name="batrechexcel"/>         <input type="hidden" name = "token" value="${token}"/>        <input type="submit" value="确认批量充值" id="submit"/>        </td>     </tr>     <tr><td> </td></tr>     <tr id="shangjieguo" style="display:none">        <td>充值结果：<span id="totalresult" style="color:red"></span>        </td>     </tr>     <tr id="xiajieguo" style="display:none">        <td>错误描述：<br/>            <span id="errordes" style="color:red"></span>            <span id="incompleteDataString" style="color:red"></span>        </td>     </tr>  </tbody></table></form><script>function verification(){ var batrechexcel = $("input[name='batrechexcel']").val(); if(batrechexcel == ""){ $("#shangjieguo").show(); $("#totalresult").text("请选择要导入的Excel文件"); $("#xiajieguo").css("display","none"); return false; }else{ $("#shangjieguo").show(); $("#totalresult").text("充值进行中，请耐心等待..."); $("#xiajieguo").css("display","none"); var btnSubmit = document.getElementById("submit"); btnSubmit.disabled= "disabled"; return true; }}$(function(){var straf = '${resultJson}';var str = JSON.parse(straf);if(str.result == "allsuccess"){$("#shangjieguo").show();$("#totalresult").text("全部受理成功:"+"总共充值数："+str.totalNumber+" ,充值成功数："+str.successNumber+" ,充值失败数："+str.failNumber+" ,格式不对数："+str.incompleteDataNumber);    window.parent.refreshLimit();}else if(str.result == "allfail"){$("#shangjieguo").show();$("#xiajieguo").show();$("#totalresult").text("全部受理失败:"+"总共充值数："+str.totalNumber+" ,充值成功数："+str.successNumber+" ,充值失败数："+str.failNumber+" ,格式不对数："+str.incompleteDataNumber);$("#errordes").html(str.failreason);$("#incompleteDataString").html(str.incompleteDataString);}else if(str.result == "null"){$("#shangjieguo").show();$("#totalresult").text("数据为空或数据大于1万");}else if(str.result == "partfail"){$("#shangjieguo").show();$("#xiajieguo").show();$("#totalresult").text("部分受理失败:"+"总共充值数："+str.totalNumber+" ,充值成功数："+str.successNumber+" ,充值失败数："+str.failNumber+" ,格式不对数："+str.incompleteDataNumber);$("#errordes").html(str.failreason);$("#incompleteDataString").html(str.incompleteDataString);window.parent.refreshLimit();}else if(str.result == "committwo"){$("#shangjieguo").show();$("#totalresult").text("请不要重复提交");}else if(str.result == "nomoney"){$("#shangjieguo").show();$("#totalresult").text(str.failreason);}else if(str.result == "geshibudui"){$("#shangjieguo").show();$("#totalresult").text("文件格式不对");}});</script>  </body></html></span>

2、控制层

import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;@Controller@RequestMapping(value="/flow")public class BatchRechargeMainAction extends ModelAction<BatchRechargeMain> {@Autowired BatchRechargeMainService batReChMainService;@Autowired UserService userService;/*** 跳到批量充值页面* @return * @author  * @throws UnsupportedEncodingException */@RequestMapping(value="/public/batchRecharge",method=RequestMethod.GET)public String tobatchRecharge(HttpServletRequest request, HttpServletResponse response) throws UnsupportedEncodingException {String token = TokenProccessor.getInstance().makeToken();//创建令牌getSession().setAttribute("token", token);  //在服务器使用session保存token(令牌)return "flow/batchrecharge/batchrecharge";}

/** * 批量充值 * @param request * @param response * @return * @author * @throws Exception  */@RequestMapping(value="/public/batchRecharge",method=RequestMethod.POST)public String batchRecharge(HttpServletRequest request, HttpServletResponse response) throws Exception{boolean b = isRepeatSubmit(request);//判断用户是否是重复提交if(b==true){//重复提交了   Map<String, Object> totalResult = new HashMap<String, Object>();   totalResult.put("result", "committwo");  attr("resultJson",JsonUtil.toJson(totalResult));   return "flow/batchrecharge/batchrecharge";}getSession().removeAttribute("token");MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;      MultipartFile multipartFile = multipartRequest.getFile("batrechexcel");      String sourceName = multipartFile.getOriginalFilename(); // 原始文件名      String geshi = sourceName.substring(sourceName.indexOf(".")+1);    if(!geshi.equals("xlsx")&&!geshi.equals("xls")){     Map<String, Object> totalResult = new HashMap<String, Object>(); totalResult.put("result", "geshibudui"); attr("resultJson",JsonUtil.toJson(totalResult)); String token = TokenProccessor.getInstance().makeToken();//创建令牌 getSession().setAttribute("token", token);  //在服务器使用session保存token(令牌) attr("token",token); return "flow/batchrecharge/batchrecharge";    }    String base = request.getSession().getServletContext().getRealPath("/") + "batchrecharge" + File.separator + "uploadedExcel";      File file = new File(base);      if(!file.exists()){         file.mkdirs();      }       String path=base + File.separator + sourceName;      multipartFile.transferTo(new File(path));    Set<PhoneSize> phoneSizes = readBatchRechargeExcel(path);    String account = getLoginUser().getAccount();    String resultJson = this.batReChMainService.doBatchRecharge(phoneSizes,account);    attr("resultJson",resultJson);    String token = TokenProccessor.getInstance().makeToken();//创建令牌getSession().setAttribute("token", token);  //在服务器使用session保存token(令牌)attr("token",token);    return "flow/batchrecharge/batchrecharge";}/**     * 判断客户端提交上来的令牌和服务器端生成的令牌是否一致     * @param request     * @return      *         true 用户重复提交了表单      *         false 用户没有重复提交表单     */    private boolean isRepeatSubmit(HttpServletRequest request) {        String client_token = request.getParameter("token");        //1、如果用户提交的表单数据中没有token，则用户是重复提交了表单        if(client_token==null){            return true;        }        //取出存储在Session中的token        String server_token = (String) request.getSession().getAttribute("token");        //2、如果当前用户的Session中不存在Token(令牌)，则用户是重复提交了表单        if(server_token==null){            return true;        }        //3、存储在Session中的Token(令牌)与表单提交的Token(令牌)不同，则用户是重复提交了表单        if(!client_token.equals(server_token)){            return true;        }        return false;    }/** * 读导入Excel表 * @param fileName * @return 集合 * @author */@SuppressWarnings("resource")private Set<PhoneSize> readBatchRechargeExcel(String fileName){        Set<PhoneSize> phoneSizes = new TreeSet<PhoneSize>();        try {              FileInputStream in = new FileInputStream(fileName);              Workbook wb;              if (fileName.endsWith(".xls")) {                  wb = new HSSFWorkbook(in);// Excel2003              } else {                  wb = new XSSFWorkbook(in);// Excel 2007              }              Sheet sheet = wb.getSheetAt(0); // 创建对工作表的引用               int row_num = sheet.getLastRowNum(); // 遍历所有单元格，读取单元格             if(row_num == 0){            return null;            }            if(row_num > 10000){            return null;            }            for (int i = 1; i <= row_num; i++) {                  Row row = sheet.getRow(i);                  String phone = "";                String size = "";                Cell cellphone = row.getCell(0);                Cell cellsize = row.getCell(1);                if(cellphone != null){                cellphone.setCellType(Cell.CELL_TYPE_STRING);                phone = cellphone.getStringCellValue();                }                if(cellsize != null){                cellsize.setCellType(Cell.CELL_TYPE_STRING);                size = cellsize.getStringCellValue();                }                PhoneSize phoneSize = new PhoneSize();                phoneSize.setNumber(String.valueOf(i+1));                if(PatternUtil.isMobileNO(phone)){                phoneSize.setPhone(phone);                }else{                phoneSize.setPhone(null);                }                if(PatternUtil.isInteger(size)){                phoneSize.setSize(size);                }else{                phoneSize.setSize(null);                }                phoneSizes.add(phoneSize);            }              return phoneSizes;          } catch (Exception e) {            e.printStackTrace();              return null;        } finally {          }  }}

0 0