jquery插件Ajaxfileupload上传Excel文件

在做批量导入文件的功能时使用ajaxfileupload插件上传Excel文件。

首先下载ajaxfileupload.js

按钮样式：

<form action="" name="" method="post" enctype="multipart/form-data" >
     <input type="file" name="upload" id="upload" style="height: 30px;width: 70px;" οnchange="new WLFWQ().upLoadFile();" class="text-file">
     <input type="button" class="btn btn-info btn-primary btn-sm CanUpd botton-box-zsgc" style="margin-top:-53px;height: 30px;width: 70px;" value="批量导入">
</form>

"text-file"css样式，用于透明隐藏选择文件域，点击"批量导入"按钮时实际点击的是上传文件的选择域

.text-file {
    filter:alpha(opacity:0);
    opacity: 0;
    width:260px
}
WLFWQ.prototype.upLoadFile = function(){
    $.ajaxFileUpload({
        url: "tbPyBasicAction!readExcelContent.action",  //用于文件上传的服务器端请求地址
        secureuri: false,                                //一般设置为false
        fileElementId: "upload",            //文件上传空间的id属性  <input type="file" id="file" name="file" />
        dataType: "json",
    });
}
使用poi上传Excel文件，只限于Excel2007及以上版本，导入poi的jar包

 //封装文件标题请求参数的属性
    private String title;
    //封装上传文件域的属性
    private File upload;
    //封装上传文件类型的属性
    private String uploadContentType;
    //封装上传文件名的属性
    private String uploadFileName;
    //    直接在struts.xml文件中配置的属性
    private String savePath;
    //接受struts.xml文件配置值的方法

    public void setSavePath(String value)
    {
        this.savePath = value;
    }
    //    返回上传文件的保存位置
    private String getSavePath() throws Exception
    {
        return ServletActionContext.getServletContext().getRealPath(savePath);
    }

    //文件标题的setter和getter方法
    public void setTitle(String title)
    {
        this.title = title;
    }
    public String getTitle()
    {
        return (this.title);
    }

    //上传文件对应文件内容的setter和getter方法
    public void setUpload(File upload)
    {
        this.upload = upload;
    }
    public File getUpload()
    {
        return (this.upload);
    }

    //上传文件的文件类型的setter和getter方法
    public void setUploadContentType(String uploadContentType)
    {
        this.uploadContentType = uploadContentType;
    }
    public String getUploadContentType()
    {
        return (this.uploadContentType);
    }

    //上传文件的文件名的setter和getter方法
    public void setUploadFileName(String filename)
    {
        // 防止图片重名，将图片改名
        String format = filename.substring(filename.lastIndexOf("."));
        String file_name = filename.substring(filename.lastIndexOf("\\") + 1,filename.lastIndexOf("."));
        Date m_date_now = new Date();
        SimpleDateFormat timeformat = new SimpleDateFormat("yyyyMMddHHmmss",java.util.Locale.CHINA);
        String m_date = timeformat.format(m_date_now);
        filename = file_name + m_date + format;
        this.uploadFileName = filename;
    }
    public String getUploadFileName()
    {
        return (this.uploadFileName);
    }

    public String uploadFile() throws Exception
    {
//        FileInputStream fis = new FileInputStream(getUpload());
//        System.out.println(fis);
//        以服务器的文件保存地址和原文件名建立上传文件输出流
//        String filePath = getSavePath() + "\\" + getUploadFileName();
        String filePath = getSavePath();   //获取文件夹的路径
        File file = new File(filePath);
        //  如果不存在文件夹 就创建一个文件夹
        if(!file .exists()  && !file .isDirectory()){
            file.mkdir();
        }
        //  获取文件夹的路径 和 重命名后的文件的名称
        String name = file.getPath() +"\\" + getUploadFileName();
        //  写入文件
        FileOutputStream fos = new FileOutputStream(name);
        FileInputStream fis = new FileInputStream(getUpload());
        byte[] buffer = new byte[1024];
        int len = 0;
        while ((len = fis.read(buffer)) > 0)
        {
            fos.write(buffer , 0 , len);
        }
        fos.close();
//        在这添加写进数据库的语句
        ///  返回文件的路径和名称
        return name;
    }
    /**
     * 读取Excel表格表头的内容
     * @return String 表头内容的数组
     */
    public String[] readExcelTitle() {
//         POIFSFileSystem fs = null;
        InputStream is = null;
        try {
            is  = new FileInputStream("d:\\physical.xlsx");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        XSSFWorkbook wb = null;           // XSSFWorkbook对象用于Excel2007及以上版本
        XSSFSheet sheet = null;
        XSSFRow row = null;
        try {
//            fs = new POIFSFileSystem(is);
            wb = new XSSFWorkbook(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
        sheet = wb.getSheetAt(0);
        row = sheet.getRow(0);
        // 标题总列数
        int colNum = row.getPhysicalNumberOfCells();
        System.out.println("colNum:" + colNum);
        String[] title = new String[colNum];
        for (int i = 0; i < colNum; i++) {
            //title[i] = getStringCellValue(row.getCell((short) i));
            title[i] = getCellFormatValue(row.getCell((short) i));
        }
        return title;
    }

    /**
     * 导入信息
     * 读取Excel数据内容
     *
     * @return Map 包含单元格数据内容的Map对象
     */
    public  String readExcelContent() {
//        POIFSFileSystem fs = null;
        HttpServletRequest request = getRequest();
        HttpServletResponse response = getResponse();
        String  filePath = "";
        try {
            filePath = uploadFile();

        } catch (Exception e) {
            e.printStackTrace();
        }
        InputStream is = null;
        try {
            is  = new FileInputStream(filePath);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        XSSFWorkbook wb = null;
        XSSFSheet sheet = null;
        XSSFRow row = null;
        List<TbPyBasic> tbPyBasicList = new ArrayList<TbPyBasic>();
        List<TbBasicOtherInfo> basicOtherInfoList = new ArrayList<TbBasicOtherInfo>();
        try {
            wb = new XSSFWorkbook(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
        sheet = wb.getSheetAt(0);
        // 得到总行数
        int rowNum = sheet.getLastRowNum();
        row = sheet.getRow(0);
        int colNum = row.getPhysicalNumberOfCells();

        //检验数据合法性标识
        boolean flagStr = true;
        
        StringBuffer stringBuffer = new StringBuffer();
        // 正文内容应该从第二行开始,第一行为表头的标题

        int sCount = 0;
        int fCount = 0;

        String  ipStr  = "";
        String nameStr = "";
        for (int i = 1; i <= rowNum; i++) {
            row = sheet.getRow(i);
            for(int j=0;j<colNum;j++){
                String physicalName = getCellFormatValue(row.getCell(j++)).trim();   
                String ip = getCellFormatValue(row.getCell(j++)).trim();   
                String osSys = getCellFormatValue(row.getCell(j++)).trim();  
                String seriableNumber = getCellFormatValue(row.getCell(j++)).trim();   
                String crUser = getCellFormatValue(row.getCell(j++)).trim();  
                TbPyBasic pyBasic = new TbPyBasic();
                TbBasicOtherInfo tbBasicOtherInfo = new TbBasicOtherInfo();
                try {
                    if(!"".equals(physicalName)){
                        if(nameStr.contains(physicalName) || tbPyBasicManager.checkNameUnique(physicalName)){
                            nameStr += physicalName+"|";
                            stringBuffer.append("第"+i+"行["+physicalName+"]名称重复|");
                            fCount ++;
                            flagStr = false;
                        }else{
                            nameStr += physicalName+"|";
                            pyBasic.setPhysicalName(physicalName);
                            tbBasicOtherInfo.setDeviceId(physicalName);
                            pyBasic.setDeviceId(physicalName);
                            pyBasic.setPhId(physicalName);
                            pyBasic.setCrUser(crUser);

                            // 验证
                            if(osSys.contains("Linux") || osSys.contains("linux") || osSys.contains("LINUX")){
                                pyBasic.setOsType("2");
                            }else if(osSys.contains("windows") || osSys.contains("WINDOWS") || osSys.contains("Windows")){
                                pyBasic.setOsType("3");
                            }else if(osSys.contains("aix") || osSys.contains("AIX") || osSys.contains("Aix")){
                                pyBasic.setOsType("4");
                            }else if(osSys.contains("UX") || osSys.contains("UNIX") || osSys.contains("Unix") || osSys.contains("unix")){
                                pyBasic.setOsType("6");
                            }else if(osSys.contains("solarix") || osSys.contains("Solaris") || osSys.contains("SOLARIS")){
                                pyBasic.setOsType("5");
                            }else{
                                pyBasic.setOsType("7");
                            }
                            pyBasic.setOsVersion(osSys);
                            tbBasicOtherInfo.setDeviceNum(seriableNumber);
                            if(ip.contains("/")){   // 一台服务器两个IP 如 192.168.0.12/13
                                ip = ip.split("/")[0];
                            }
                            if(ipStr.contains(ip) || tbPyBasicManager.checkIpUnique(ip)){
                                ipStr += ip+"|";
                                stringBuffer.append("第"+i+"行["+ip+"]IP重复" +"|");
                                flagStr = false;
                            }else{
                                String regexStr = "\\b((?!\\d\\d\\d)\\d+|1\\d\\d|2[0-4]\\d|25[0-5])\\.((?!\\d\\d\\d)\\d+|1\\d\\d|2[0-4]\\d|25[0-5])\\.((?!\\d\\d\\d)\\d+|1\\d\\d|2[0-4]\\d|25[0-5])\\.((?!\\d\\d\\d)\\d+|1\\d\\d|2[0-4]\\d|25[0-5])\\b";
                                Pattern p = Pattern.compile(regexStr);
                                Matcher m = p.matcher(ip);
                                if(m.matches()){
                                    ipStr += ip+"|";
                                    pyBasic.setIpAddress(ip);
                                    flagStr = true;
                                    sCount ++;
                                } else {
                                    stringBuffer.append("第"+i+"行["+ip+"] IP输入有误 " +"|");
                                    flagStr = false;
                                }
                            }
                        }
                    }




                } catch (Exception e) {
                    e.printStackTrace();
                }

                if(flagStr){
                    tbPyBasicList.add(pyBasic);
                    basicOtherInfoList.add(tbBasicOtherInfo);
                }
            }
        }
        JSONObject jsonObject = new JSONObject();
        try {
            boolean flag   = tbPyBasicManager.insertPhysical(tbPyBasicList, basicOtherInfoList);  
            jsonObject.put("success",flag);
            jsonObject.put("messages",stringBuffer.toString());
            jsonObject.put("sCount",sCount);
            jsonObject.put("fCount",fCount);
            print(response,jsonObject.toString());

        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
strutx.xml 配置

 <action name="tbPyBasicAction" class="com.emc.action.TbPyBasicAction">
            <param name="savePath">/upload</param>
            <result name = "/pages/TbPyBasic/show.jsp" >/pages/TbPyBasic/show.jsp</result>
 </action>
poi-3.11解压后的目录，如果导入Excel2007及以上版本，需导入第一、第四和第五个jar包。