How to handle the large import of Excel data in the Web interface

In the early bootstrap framework introduction, my essay, "Combining bootstrap Fileinput plug-ins and bootstrap-table form plug-ins, to achieve file upload, preview, submit import Excel data operation process" described in the use of bootstrap The Fieinput plugin uploads the Excel file to the server, then uses the Bootstrap-table form plugin to display the data, and finally imports it into the system, which can be previewed to the data to be imported and optionally imported. In the actual use of the process, found using AJAX to import large quantities (hundreds of records of data) in the case of the page will strike, estimated and committed data size limit, in order to solve this problem, and combined with the import data is generally all imported, we modify the process of data import, This enables the import of Excel data with a large amount of data.

1, use the preview data, and check the import processing mode

The interface displayed by Excel import is shown below.

After uploading the file, the data is displayed directly in the pop-up list, which is displayed directly using the Bootstrap-table form plugin.

In this way, we can show the Excel record, realize the function of preview, tick the necessary records, then save it to the server to save, and realize the real Import database processing of Excel data.

The actual code is a little more, detailed can refer to the following essay, "Combined with bootstrap Fileinput plug-ins and bootstrap-table form plug-ins, to achieve file upload, preview, submit the import of Excel data operation process", Here is a brief introduction of the processing logic of the import, because it is assembled in the client list data, and then submitted through AJAX, its code as shown below. (This is the problem that needs to be solved later).

        //Save the imported data        functionSaveimport () {varlist = [];//Constructing Collection Objects            varrows = $import. Bootstraptable (' getselections ');  for(vari = 0; i < rows.length; i++) {List.push ({' Name ': rows[i]. Name, ' Mobile ': rows[i]. Mobile, ' Email ': rows[i]. Email, ' homepage ': Rows[i]. Homepage,' Hobby ': rows[i]. Hobby, ' Gender ': rows[i]. Gender, ' age ': rows[i]. Age, ' BirthDate ': Rows[i]. BirthDate,' Height ': rows[i]. Height, ' Note ': Rows[i].            Note}); }            if(List.length = = 0) {Showtoast ("Please select a record", "Warning"); return; }            varPostData = {' list ': list};//additional parameters can be added, such as {' list ': List, ' Rucanghao ': $ ("#Rucanghao"). Val ()};PostData =json.stringify (postdata); $.ajax ({URL:'/testuser/saveexceldata ', type:' Post ', DataType:' JSON ', ContentType:' Application/json;charset=utf-8 ', Traditional:true, Success:function(data) {if(data. Success) {//save successful 1. Close the popup layer, 2. Clear the Record display 3. Refresh the master listShowtoast ("Saved successfully"); $("#import"). Modal ("Hide"); $ (bodytag). HTML ("");                    Refresh (); }                    Else{showtoast ("Save failed:" + data. ErrorMessage, "Error");        }}, Data:postdata}); }

In the actual use process, found that the data hundreds of, the page on strike, not normal insertion, search under the problem is limited to solve the problem, but I also set the size of the upload file in the Web. config, and finally did not find the configuration solution.

<executiontimeout= "  maxrequestlength"= "951200"     useFullyQualifiedRedirectUrl= "true"  minfreethreads= "8"     minLocalRequestFreeThreads= "4"  apprequestqueuelimit= "   Enableversionheader= "true"/>

Finally, this configuration item cannot be solved, so we can only find other ways to avoid the mass submission of data.

2, using the controller background read Excel file Import Database

The above data import way, the general data is relatively small, the experience is good, but its process is to upload Excel files, and then read the records in Excel, converted to the corresponding list<t> type, in the serial number JSON list in the front-end interface display.

Since our files are on the server, and can also be converted to the corresponding list<t> by the Excel file, then we reduce the user tick, confirm the direct read import, so processing should not be limited by the size of the page data problems.

In this way, we use the device information import as a case, introduce this process, the following front-end code is uploaded to the server after the file, the user is responsible for the import logic after confirmation.

             //Save the imported data        functionSaveimport () {varPostData = {' GUID ': Importguid}; PostData=json.stringify (postdata); $.ajax ({URL:'/device/saveexcelbyguid ', type:' Post ', DataType:' JSON ', ContentType:' Application/json;charset=utf-8 ', Traditional:true, Success:function(data) {if(data.                        Success) {Refresh (); //save successful 1. Close the popup layer, 2. Clear the Record display 3. Refresh the master listShowtoast ("Saved successfully"); $("#import"). Modal ("Hide"); $ (bodytag). HTML (""); }                    Else{showtoast ("Save failed:" + data. ErrorMessage, "Error"); }}, Data:postdata});

Finally we see that the processing is in the Saveexcelbyguid controller method, this method according to the server GUID, get the corresponding Excel file information, and then read and import operations.

The detailed code for this method is shown below.

        /// <summary>        ///save Excel on the server side/// </summary>        /// <param name= "GUID" ></param>        /// <returns></returns>         PublicActionResult Saveexcelbyguid (stringGUID) {Commonresult result=NewCommonresult (); if(!string. IsNullOrEmpty (GUID)) {varList = GetDevice (GUID);//obtain the corresponding Excel file according to the GUID, and convert the content to the corresponding list<t>                if(List! =NULL)                {                    foreach(DeviceInfo detailinchlist) {                        varIsexist = Bllfactory<device>. Instance.isexistkey ("DeviceId", detail.                        DEVICEID); if(!isexist) {Bllfactory<Device>.                        Instance.insert (detail); }                    }                    //Successful OperationResult. Success =true; }                Else{result. ErrorMessage="import information cannot be empty"; }            }            Else{result. ErrorMessage="import information cannot be empty"; }            returntojsoncontent (Result); }

Where we see the GetDevice (GUID) is the process of getting the contents of an Excel file and converting it to the corresponding entity class list.

The GetDevice is the process of converting to a collection of corresponding entity classes, as shown in the code below.

        /// <summary>        ///get the device import file and convert it to the corresponding entity class collection/// </summary>        /// <param name= "GUID" >Attachment GUID</param>        /// <returns></returns>        PrivateList<deviceinfo> GetDevice (stringGUID) {List<DeviceInfo> list =NewList<deviceinfo>(); DataTable Table=convertexcelfiletotable (GUID); if(Table! =NULL)            {                #regionData conversionforeach(DataRow Drinchtable. Rows) {deviceinfo info=Newdeviceinfo (); Info. DeviceId= dr["Device ID"].                    ToString (); Info. Versioninfo= dr["Version Information"].                    ToString (); Info. Minitorinfo= dr["Reserve Monitoring Information"].                    ToString (); Info. DEVICEMSISDN= dr["phone number of public telephone"].                    ToString (); List.                ADD (info); }                #endregion            }            returnlist; }

And convertexcelfiletotable is using Aspose. The cell's Excel manipulation control, which implements data conversion.

        /// <summary>        ///get the first Excel file from the list of attachments and convert the Excel data back to the corresponding DataTable/// </summary>        /// <param name= "GUID" >GUID of attachment</param>        /// <returns></returns>        protectedDataTable convertexcelfiletotable (stringGUID) {DataTable dt=NULL; if(!string. IsNullOrEmpty (GUID)) {//get the path to the upload attachment                stringServerrealpath = bllfactory<fileupload>.                Instance.getfirstfilepath (GUID); if(!string. IsNullOrEmpty (Serverrealpath)) {//convert Excel files to dattable inside                    stringError =""; DT=NewDataTable (); Asposeexceltools.excelfiletodatatable (Serverrealpath, outDt outerror); }            }            returnDT; }

This implementation effect, regardless of the user check the record, after confirming directly to the entire Excel file to determine the import operation, generally also conforms to our actual import process, so processing up, there will be no such situation as described above, at least we can successfully upload Excel files, There is no pressure to read the Excel file in the background and the experience is very good, very fast.

Finally, after a large amount of data is imported, it can be refreshed quickly and can be displayed in the pagination control.