JAVA导入

1、前台

    <div style="text-align:center; padding:10px 0;">
        <a href="javascript:void(0)" οnclick="importCustAccount();" Class="margin_left_11">
        <img src="${request.contextPath }/esresources/default/images/home/yonghudaoru.jpg"/>点击导入号码
        </a>
        <span id="exportExcelTemplet" class="margin_right_20" style="cursor: pointer;" οnclick="exportExcelTemplet()">
            <a href="javascript:void(0)">
                <img src="${pageContext.request.contextPath}/esresources/default/images/goods/down.gif" class="margin_left_4"/> 下载excel模板
            </a>
        </span>
    </div>

    <!-- 导入弹出框 start -->
    <div id="impDiv" name="impDiv" style="display: none">
        <form action="${request.contextPath}/smsSendExcel/importCustAccList.action"
            enctype="multipart/form-data" method="post" name="impForm">
            <input type="hidden" id="fileName" name="fileName"/>
            <table>
                <tr>
                    <td>
                        <span class="margin_left_20 floatLeft">
                            请选择您需要导入的文件,单击确定.
                        </span>
                    </td>
                </tr>
                <tr>
                    <td>
                        <span class="margin_left_20 floatLeft">
                            <input type="file" class="style_input_300 gray9" name="importFile" id="file" />
                        </span>
                    </td>
                </tr>
            </table>
        </form>
    </div>

    <!-- 导入弹出框 end -->

————————————————————————————————————————————————————————————————————————————

-------下载模板

      //下载excel模板
    function exportExcelTemplet()
    {
      $.ajax({
          type: "POST",
          url: "${request.contextPath}/smsSend/validateCustAccTemplet.action",
          dataType: "html",
          data: "time=" + new Date().getTime(),
          success:function(data)
          {
              if (data == 'SUCCESS')
              {
                  window.location.href="${request.contextPath}/smsSend/downloadCustTemplet.action";
              }
              else if (data == "BLANKPATH")
              {
                  $.DialogAlert('tips', '档次模板文件的存放路径为空，请联系管理员');
                  //parent.showDialog('账号导入模板文件的存放路径为空，请联系管理员');
                  return false;
              }
              else if (data == "FILENOTEXIST")
              {
                  $.DialogAlert('tips', '档次模板文件不存在，请联系管理员');
                  //parent.showDialog('账号导入模板文件不存在，请联系管理员');
                  return false;
              }
              else
              {
                  $.DialogAlert('tips', '下载档次模板失败');
                  //parent.showDialog('下载账号导入模板失败');
                  return false;
              }
          }
      });     
    }

    /**
     * 下载导入账号模板文件时，先校验文件是否存在
     *
     */
    public void validateCustAccTemplet()
    {
        PrintWriter writer = null;
        
        try
        {
            getRequest().setCharacterEncoding("GBK");
            
            getResponse().setContentType("text/html;charset=GBK");
            writer = getResponse().getWriter();
            
            // 获取模板文件存放路径
            String path = "";
            
            ParamBean paramBean = CacheUtil.getParameterBean(Constants.excelTemplet.CUSTACCOUNT_EXCELTEMPLET_PATH, Constants.excelTemplet.PROVINCE_REGION);
            if (paramBean != null && StringUtils.isNotBlank(paramBean.getParavalue()))
            {
                path = paramBean.getParavalue();
            }
            // 路径为空
            if (StringUtils.isBlank(path))
            {
                writer.print(Constants.excelTemplet.CUSTACCOUNT_EXCELTEMPLET_PATH_BLANK);
            }
            else
            {
                // 如果路径中最后一位字符不是/，则添加上/
                if (path.lastIndexOf("/") != path.length() - 1)
                {
                    path = path + "/";
                }
                
                // 模板的文件名称
                String fileName = Constants.excelTemplet.CUSTACCOUNT_EXCELTEMPLET_PRIVILEGE + ".xls";
                // 文件不存在
                File file = new File(path, fileName);
                System.out.println("--------------------------" + path);
                if (!file.exists())
                {
                    writer.print(Constants.excelTemplet.CUSTACCOUNT_EXCELTEMPLET_FILENOTEXIST);
                }
                else
                {
                    writer.print("SUCCESS");
                }
            }
            
            writer.flush();
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
        finally
        {
            if (writer != null)
            {
                writer.close();
            }
        }
    }
    
    /**
     * 下载号码导入模板文件
     */
    public void downloadCustTemplet()
    {
        HttpServletResponse response = getResponse();
        
        OutputStream os = null;
        
        FileInputStream fis = null;
        
        try
        {
            // 模板的存放路径
            String path = CacheUtil.getParameterBean(Constants.excelTemplet.CUSTACCOUNT_EXCELTEMPLET_PATH, Constants.excelTemplet.PROVINCE_REGION)
                    .getParavalue();
            
            // 如果路径中最后一位字符不是/，则添加上/
            if (path.lastIndexOf("/") != path.length() - 1)
            {
                path = path + "/";
            }
            
            // 模板的文件名称
            String fileName = Constants.excelTemplet.CUSTACCOUNT_EXCELTEMPLET_PRIVILEGE + ".xls";
            
            fis = new java.io.FileInputStream(path + fileName);
            
            // 下载excel文件
            response.setContentType("text/html;charset=GBK");
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
            
            byte[] b = new byte[1024];
            
            int i = 0;
            
            os = response.getOutputStream();
            while ((i = fis.read(b)) > 0)
            {
                os.write(b, 0, i);
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
            
            if (os != null)
            {
                try
                {
                    os.write("下载号码导入模板失败".getBytes());
                }
                catch (IOException ex)
                {
                    ex.printStackTrace();
                }
            }
        }
        finally
        {
            if (fis != null)
            {
                try
                {
                    fis.close();
                }
                catch (IOException e)
                {
                    e.printStackTrace();
                }
            }
            
            if (os != null)
            {
                try
                {
                    os.flush();
                }
                catch (IOException e)
                {
                    e.printStackTrace();
                }
                
                try
                {
                    os.close();
                }
                catch (IOException e)
                {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 上传下载excel模板
     */
    public static class excelTemplet
    {
        /**
         * “全省”对应的region
         */
        public static final int PROVINCE_REGION = 999;
        
        /**
         * 存放excel模版的路径在参数表中的paramid
         */
        public static final String CUSTACCOUNT_EXCELTEMPLET_PATH = "CUSTACCOUNT_EXCELTEMPLET_PATH";
        
        /**
         * 导入号码的excel模板的文件名称
         */
        public static final String CUSTACCOUNT_EXCELTEMPLET_PRIVILEGE = "custAccount";
        
        /**
         * 存放excel模版的路径为空
         */
        public static final String CUSTACCOUNT_EXCELTEMPLET_PATH_BLANK = "BLANKPATH";
        
        /**
         * excel模版文件不存在
         */
        public static final String CUSTACCOUNT_EXCELTEMPLET_FILENOTEXIST = "FILENOTEXIST";
        
        /**
         * 格式错误
         */
        public static final String FORMAT_ERROR = "FORMATERROR";
    }

——————————————————————————————————————————————————————————————————————————————

    function importCustAccount()
    {
        var DialogHtml = $("#impDiv").html();
        $.dialog({title: "文件导入", content: DialogHtml, ok: "确 定", cancel: "关闭", id: "DialogConfirm", className: "loginWindow", width: 420, okCallback: okConfirm, cancelCallback: cancelConfirm, modal: true});
        function okConfirm() {
            if(validateUploadForm()){
                var importName = $("#DialogConfirm").find("#file").val();
                $("#DialogConfirm").find("#fileName").val(importName);
                $("#DialogConfirm").find("[name='impForm']").submit();
            }
            return false;
        }
        function cancelConfirm()
        {
            return true;
        }
        
    }

/**
     * 从excel文件中导入用户角色列表
     *
     * @return
     */
    public String importCustAccList()
    {
        flag = "-2";  //上传失败
        if (importFile != null)
        {
            // 读取用户上传的文件
            Workbook wb = null;
            try
            {
                wb = Workbook.getWorkbook(importFile);                
                Sheet[] sheets = wb.getSheets();
                
                if (sheets != null && sheets.length > 0)
                {
                    StringBuffer buffer = new StringBuffer("");
                    
                    // 对每个工作表进行循环
                    for (int i = 0; i < sheets.length; i++)
                    {
                        String msg = transSheetToList(sheets[i]);
                        if (msg.indexOf(Constants.excelTemplet.FORMAT_ERROR) > -1)
                        {
                            String j = msg.split("%")[1];
                            resultMsg = "文件导入失败：第" + j + "行数据格式不正确 ";
                            break;
                        }
                        buffer.append(msg);
                    }
                    
                    // resultMsg为空：文件读取正常
                    if ("".equals(resultMsg))
                    {
                        String roleUserListStr = buffer.toString();
                        
                        if (StringUtils.isBlank(roleUserListStr))
                        {
                            resultMsg = "文件导入失败：上传文件为空文件 ";
                        }
                        else
                        {
                            String rets = insertDataToDB(roleUserListStr);
                            if ("error".equals(rets))
                            {
                                resultMsg = "文件导入数据库异常 ";
                            }
                            else
                            {
                                flag = "1";
                                resultMsg = "文件导入成功 ";
                            }
                        }
                    }
                }
                else
                {
                    resultMsg = "文件导入失败：上传文件为空文件 ";
                }
            }
            catch (Exception e)
            {
                resultMsg = "文件导入失败 ";
                
                e.printStackTrace();
            }
            finally
            {
                if (wb != null)
                {
                    wb.close();
                }
            }
        }
        else
        {
            resultMsg = "文件导入失败：上传文件为空文件 ";
        }
        return "global";
    }
    
    /**
     * 读取sheet内容，转化为字符串。记录与记录之间使用@@分隔，字段与字段之间使用$$分隔。
     * 格式：用户账号@@用户账号
     *
     * @param sheet
     * @return sheet中的内容
     */
    public String transSheetToList(Sheet sheet)
    {
        StringBuffer returnStr = new StringBuffer();
        
        // 得到当前工作表的行数。第一行为标题行
        int rowNum = sheet.getRows();
        if (rowNum > 1)
        {  
            // 列数：由第一行决定
            int cellCount = sheet.getRow(0).length;
            
            // j从1开始，去掉Excel中的第一行(标题行)
            for (int j = 1; j < rowNum; j++)
            {
                // 得到当前工作表的所有行
                Cell[] cells = sheet.getRow(j);
                if (cells == null)
                {
                    continue;
                }
                
                // 每行的列数。因为不是所有字段都是必填的，所以每行的列数可能会不同(不一定等于cellCount)
                int colCount = cells.length;
                
                // 依次对每列数据进行校验，发现格式错误时，停止读取文件
                for (int m = 0; m < cellCount; m++)
                {
                    String cellContent = "";
                    
                    // 避免数组越界
                    if (m < colCount)
                    {
                        cellContent = cells[m].getContents();
                        if (cellContent == null)
                        {
                            cellContent = "";
                        }
                        else
                        {
                            cellContent = cellContent.trim();
                        }
                    }
                    
                    String newCellContent = parseCellContent(m, cellContent);
                    if (Constants.excelTemplet.FORMAT_ERROR.equals(newCellContent))
                    {
                        return newCellContent + "%" + (j+1);
                    }
                    
                    returnStr.append(newCellContent);
                    
                    /*// 每条记录的最后一个字段，不需要追加$$
                    if (m != cellCount - 1)
                    {
                        returnStr.append("$$");
                    }*/
                }
                
                // 最后一条记录，不需要追加@@
                //if (j != rowNum - 1)
                //{
                    returnStr.append("@@");
                //}
            }
        }
        
        return returnStr.toString();
    }
    
    /**
     * 解析表格内容，同时校验数据是否合法
     *
     * @param m 列序号
     * @param cellContent 表格内容
     * @return 解析后的内容
     */
    private String parseCellContent(int m, String cellContent)
    {
        String returnStr = "";
        
        // 0：用户账号（手机号或邮箱账号），必填
        if (m == 0)
        {
            if ("".equals(cellContent) || (!isMobile(cellContent)))
            {
                returnStr = Constants.excelTemplet.FORMAT_ERROR;
            }
            else
            {
                returnStr = cellContent;
            }
        }
        //是否发送短信字段
        else if (m == 1)
        {
            if ("0".equals(cellContent)||"1".equals(cellContent))
            {
                returnStr = cellContent;;
            }
            else
            {
                returnStr = "1";
            }
        }
        return returnStr;
    }
    /**
     * 校验是否为手机号码
     *
     * @param str 校验的字符串
     * @return
     */
    public static boolean isMobile(String str)
    {
        String regex = "^1\\d{10}$";
        String regex1 = "^15[0-9]\\d{8}$";
        return str.matches(regex) || str.matches(regex1);
    }
    
    /**
     * 校验是否为数字
     *
     * @param str 校验的字符串
     * @return
     */
    public static boolean isNumber(String str)
    {
        String regex = "/^[0-9]+$/";
        return str.matches(regex);
    }

    /**
     * 校验第三列为0或者1
     *
     * @param str 校验的字符串
     * @return
     */
    public static boolean number(String str)
    {
        String regex = "/^[01]+$/";
        return str.matches(regex);
    }
    
    private String insertDataToDB(String roleUserStr)
    {
        String rets = "success";
        String[] accountList =  roleUserStr.split("@@");
        try
        {
            smsSendService.importCustAcc(accountList);
        }
        catch(Exception e)
        {
            rets = "error";
        }
        return rets;
    }