SSM对接Excel文件批量导入数据

最新推荐文章于 2022-05-30 13:30:34 发布

码呀

最新推荐文章于 2022-05-30 13:30:34 发布

阅读量829

点赞数 2

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/qq_39130032/article/details/89321563

本文介绍如何利用AjaxFileUpload.js实现文件上传功能，包括HTML表单设置、JavaScript调用及后端处理流程，展示了如何验证文件类型及大小，并通过示例代码详细解释了整个过程。

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

这里使用ajaxfileupload.js 比较方便点

<script src="/static/Jquery/ajaxfileupload.js"></script>

ajaxfileupload.js 文件源码


jQuery.extend({
	

    createUploadIframe: function(id, uri)
	{
			//create frame
            var frameId = 'jUploadFrame' + id;
            var iframeHtml = '<iframe id="' + frameId + '" name="' + frameId + '" style="position:absolute; top:-9999px; left:-9999px"';
			if(window.ActiveXObject)
			{
                if(typeof uri== 'boolean'){
					iframeHtml += ' src="' + 'javascript:false' + '"';

                }
                else if(typeof uri== 'string'){
					iframeHtml += ' src="' + uri + '"';

                }	
			}
			iframeHtml += ' />';
			jQuery(iframeHtml).appendTo(document.body);

            return jQuery('#' + frameId).get(0);			
    },
    createUploadForm: function(id, fileElementId, data)
	{
		//create form	
		var formId = 'jUploadForm' + id;
		var fileId = 'jUploadFile' + id;
		var form = jQuery('<form  action="" method="POST" name="' + formId + '" id="' + formId + '" enctype="multipart/form-data"></form>');	
		if(data)
		{
			for(var i in data)
			{
				jQuery('<input type="hidden" name="' + i + '" value="' + data[i] + '" />').appendTo(form);
			}			
		}		
		var oldElement = jQuery('#' + fileElementId);
		var newElement = jQuery(oldElement).clone();
		jQuery(oldElement).attr('id', fileId);
		jQuery(oldElement).before(newElement);
		jQuery(oldElement).appendTo(form);


		
		//set attributes
		jQuery(form).css('position', 'absolute');
		jQuery(form).css('top', '-1200px');
		jQuery(form).css('left', '-1200px');
		jQuery(form).appendTo('body');		
		return form;
    },

    ajaxFileUpload: function(s) {
        // TODO introduce global settings, allowing the client to modify them for all requests, not only timeout		
        s = jQuery.extend({}, jQuery.ajaxSettings, s);
        var id = new Date().getTime()        
		var form = jQuery.createUploadForm(id, s.fileElementId, (typeof(s.data)=='undefined'?false:s.data));
		var io = jQuery.createUploadIframe(id, s.secureuri);
		var frameId = 'jUploadFrame' + id;
		var formId = 'jUploadForm' + id;		
        // Watch for a new set of requests
        if ( s.global && ! jQuery.active++ )
		{
			jQuery.event.trigger( "ajaxStart" );
		}            
        var requestDone = false;
        // Create the request object
        var xml = {}   
        if ( s.global )
            jQuery.event.trigger("ajaxSend", [xml, s]);
        // Wait for a response to come back
        var uploadCallback = function(isTimeout)
		{			
			var io = document.getElementById(frameId);
            try 
			{				
				if(io.contentWindow)
				{
					 xml.responseText = io.contentWindow.document.body?io.contentWindow.document.body.innerHTML:null;
                	 xml.responseXML = io.contentWindow.document.XMLDocument?io.contentWindow.document.XMLDocument:io.contentWindow.document;
					 
				}else if(io.contentDocument)
				{
					 xml.responseText = io.contentDocument.document.body?io.contentDocument.document.body.innerHTML:null;
                	xml.responseXML = io.contentDocument.document.XMLDocument?io.contentDocument.document.XMLDocument:io.contentDocument.document;
				}						
            }catch(e)
			{
				jQuery.handleError(s, xml, null, e);
			}
            if ( xml || isTimeout == "timeout") 
			{				
                requestDone = true;
                var status;
                try {
                    status = isTimeout != "timeout" ? "success" : "error";
                    // Make sure that the request was successful or notmodified
                    if ( status != "error" )
					{
                        // process the data (runs the xml through httpData regardless of callback)
                        var data = jQuery.uploadHttpData( xml, s.dataType );    
                        // If a local callback was specified, fire it and pass it the data
                        if ( s.success )
                            s.success( data, status );
    
                        // Fire the global callback
                        if( s.global )
                            jQuery.event.trigger( "ajaxSuccess", [xml, s] );
                    } else
                        jQuery.handleError(s, xml, status);
                } catch(e) 
				{
                    status = "error";
                    jQuery.handleError(s, xml, status, e);
                }

                // The request was completed
                if( s.global )
                    jQuery.event.trigger( "ajaxComplete", [xml, s] );

                // Handle the global AJAX counter
                if ( s.global && ! --jQuery.active )
                    jQuery.event.trigger( "ajaxStop" );

                // Process result
                if ( s.complete )
                    s.complete(xml, status);

                jQuery(io).unbind()

                setTimeout(function()
									{	try 
										{
											jQuery(io).remove();
											jQuery(form).remove();	
											
										} catch(e) 
										{
											jQuery.handleError(s, xml, null, e);
										}									

									}, 100)

                xml = null

            }
        }
        // Timeout checker
        if ( s.timeout > 0 ) 
		{
            setTimeout(function(){
                // Check to see if the request is still happening
                if( !requestDone ) uploadCallback( "timeout" );
            }, s.timeout);
        }
        try 
		{

			var form = jQuery('#' + formId);
			jQuery(form).attr('action', s.url);
			jQuery(form).attr('method', 'POST');
			jQuery(form).attr('target', frameId);
            if(form.encoding)
			{
				jQuery(form).attr('encoding', 'multipart/form-data');      			
            }
            else
			{	
				jQuery(form).attr('enctype', 'multipart/form-data');			
            }			
            jQuery(form).submit();

        } catch(e) 
		{			
            jQuery.handleError(s, xml, null, e);
        }
		
		jQuery('#' + frameId).load(uploadCallback	);
        return {abort: function () {}};	

    },

    uploadHttpData: function( r, type ) {
        var data = !type;
        data = type == "xml" || data ? r.responseXML : r.responseText;
        // If the type is "script", eval it in global context
        if ( type == "script" )
            jQuery.globalEval( data );
        // Get the JavaScript object, if JSON is used.
        if ( type == "json" )
            eval( "data = " + data );
        // evaluate scripts within html
        if ( type == "html" )
            jQuery("<div>").html(data).evalScripts();

        return data;
    }
})

HTML：

            <div>
                <input id="upfile" type="file" name="upfile" accept=".xls,.xlsx"/>
                <button id="btn_Muti_add" class="btn btn-default" type="button" aria-hidden="true">
                    <span aria-hidden="true"></span>批量提交
                </button>
            </div>

<script type="text/javascript">
    window.onload = function (ev) {
        $("#upfile").css("display","initial");  //文件上传之后，清除文件名显示
    }
    $("#btn_Muti_add").click(function () {
        if (checkData()) {
            $.ajaxFileUpload({
                url: "/user/uploadExcelWrite",
                type: "POST",
                fileElementId: "upfile",
                dataType: "text",
                success: function (dataJson) {
                    alert(dataJson);
                    $("#upfile").val("");
                },
                error: function (dataJson) {
                    alert("导入异常！");
                    $("#upfile").val("");
                }
            });
        }
    });

    function checkData() {
        var fileDir = $("#upfile").val();
        var suffix = fileDir.substr(fileDir.lastIndexOf("."));
        if ("" == fileDir) {
            alert("选择需要导入的Excel文件！");
            return false;
        }
        if (".xls" != suffix && ".xlsx" != suffix) {
            alert("选择Excel格式的文件导入！");
            return false;
        }
        return true;
    }
</script>

依赖文件:

      <dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi-ooxml</artifactId>
          <version>3.9</version>
      </dependency>
      <dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi</artifactId>
          <version>3.9</version>
      </dependency>
      <dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi-ooxml-schemas</artifactId>
          <version>3.9</version>
      </dependency>

SpringMVC:

    @RequestMapping(value = "uploadExcelWrite" , method = RequestMethod.POST)
    @ResponseBody
    public String uploadExcelWrite(@RequestParam("upfile") MultipartFile file,     
        HttpServletRequest request, HttpServletResponse response) {
        return userService.uploadExcelWrite(request, response);
    }

Java处理文件：

    @Override
    public String uploadExcelWrite(HttpServletRequest request, HttpServletResponse response) {
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;

        MultipartFile file = multipartRequest.getFile("upfile");
        if (file.isEmpty()) {
            try {
                throw new Exception("文件不存在！");
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        InputStream in = null;
        try {
            in = file.getInputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }

        List<List<Object>> listob = null;
        ExcelWrite excelWrite = new ExcelWrite();
        try {
            listob = excelWrite.getBankListByExcel(in, file.getOriginalFilename());
        } catch (Exception e) {
            e.printStackTrace();
        }

        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        //获取每条数据并处理
        for (int i = 0; i < listob.size(); i++) {
            List<Object> lo = listob.get(i);
            CurriculumWarn vo = new CurriculumWarn();
            vo.setUserId(Integer.valueOf(lo.get(0).toString()));
            vo.setCurriculumId(Integer.valueOf(lo.get(1).toString()));
            vo.setType(Integer.valueOf(lo.get(2).toString()));
            vo.setAchievement(Float.valueOf(lo.get(3).toString()));
            try {
                vo.setCreateTime(format.parse(lo.get(4).toString()));
            } catch (ParseException e) {
                e.printStackTrace();
            }
            //插入数据库
            int a = curriculumWarnMapper.insertSelective(vo);
        }
        return "导入成功！";
    }