Mvc+easyui-based Web development Framework Experience Summary (10)--Implement data import and export on Web interface

Data import and export, in many systems are more common, this import and export operations, in the WinForm is relatively easy to implement, I have in a previous article "WinForm Development framework of the general data import and export operations" This paper introduces the design and development of general import and export module in WinForm, but how should we implement it on the Web? This paper mainly introduces the characteristics of using Mvc4+easyui, and the use of File upload control uploadify, the implementation of file upload immediately after processing and display, and then confirm the data written to the database process.

We know that the web on the processing of Excel and WinForm, if it is processed on the web, we need to upload the Excel document to the server, and then read the file to display, so the first step is to implement the file upload operation, about the file upload control, specifically can refer to my article " Mvc4+easyui-based Web development framework formation journey-The use of attachment upload component uploadify.

1. Interface effect display of imported data

In WinForm, our interface for processing Excel data import is shown below.

650) this.width=650; "src=" Http://pic002.cnblogs.com/images/2012/8867/2012071909293560.png "style=" border:0px; "/ >

The main interface on the web is shown below.

650) this.width=650; "src=" Http://images.cnitblog.com/i/8867/201407/281637136337384.png "style=" border:0px; "/>

The import interface is shown below.

650) this.width=650; "src=" Http://images.cnitblog.com/i/8867/201407/281635237127065.png "style=" border:0px; "/>

2. Processing logic and code for Web data import

In order to implement the data import and export operation on the web, we need to add two buttons, one is the import button and the other is the export button.

<a href= "javascript:void (0)" class= "Easyui-linkbutton" id= "Btnimport" iconcls= "Icon-excel" onclick= "ShowImport () "> Import </a> <a href=" javascript:void (0) "class=" Easyui-linkbutton "id=" Btnexport "iconcls=" Icon-excel " Onclick= "Showexport ()" > Export </a>

The input JS processing code is shown below.

Display Import interface function Showimport () {$.showwindow ({title: ' Customer contact-excel data import ', useiframe:true,width:1024,height:700, Content: ' Url:/contact/import ', buttons: [{text: ' Cancel ', Iconcls: ' Icon-cancel ', handler:function (Win) {win.close ();}}]});}

The above is mainly a pop-up window (above the Import Data window), to facilitate customers to select Excel files and save data or download import templates and other operations.

Then in Import.cshtml's view code, we need to initialize the DataGrid and the associated interface elements, and the code to initialize the DataGrid is shown below.

Implements a bind operation to the Datagird control function Initgrid () {var guid = $ ("#AttachGUID"). Val (); $ (' #grid '). DataGrid ({//Navigate to Table label, The ID of the table tag is gridurl: '/contact/getexceldata?guid= ' + GUID,//pointer to background action to get the current user's information in JSON format Data title: ' Customer contact-excel data import ' , Iconcls: ' Icon-view ', height:400,width:function () {return document.body.clientWidth * 0.9},//auto-width .......

The content of the red section above is that we initialize the DataGrid's list data according to a GUID parameter when the file is successfully uploaded to the server.

Here is the attachment upload Control uploadify initialization script code, where the red part of the note, we need to upload a file, and do not allow multiple selection, limit the type of upload file is xls.

After uploading the file, first call the Checkexcelcolumns controller function to check whether the field of the imported template is matched, if the match passes, load Excel and display the data into the DataGrid, otherwise prompt the user to enter data in template format.

$ (function  ()  {//add interface for the attachment Management $ (' #file_upload '). Uploadify ({' swf ':  '/content/jquerytools/uploadify/ Uploadify.swf ',   //flash file path ' buttontext ':  '    ',                                   //button text ' uploader ':  '/fileupload/upload ',                         //processing ashx page ' Queueid ':  ' filequeue ',          ID of the                //queue ' Queuesizelimit ': 1,                           //queue up to upload files, default to 999 ' Auto ':  false ,                                   //If the file is automatically uploaded after selecting it, the default is True ' multi ': false,                                   //is a multi-select, the default is True ' Removecompleted ': true,                        //whether to remove the sequence after completion, the default is True ' Filesizelimit ':  ' 10MB ',                         //Single File size, 0 is unrestricted, can accept string value of KB,MB,GB units such as ' Filetypedesc ':  ' excel files ',                  //file Description ' Filetypeexts ':  ' *.xls ',  Uploaded file suffix filter ' onqueuecomplete ': function  (event, data)  {    // Event var guid = $ ("#AttachGUID") after all queues have completed. val (); Viewupfiles (guid,  "div_files");//Prompts the user Excel format is normal, if the normal load data $.ajax ({url:  '/contact/checkexcelcolumns? Guid= '  + guid,type:  ' get ', DataType: ' JSON ',success: function  (data)  {if  ( Data. Success)  {                                 initgrid ();  //refresh the table data $.messager.alert ("Prompt",  "file has been uploaded, the data has been loaded!) ");} Else {$.messager.alert ("Prompt",  "uploaded Excel file check does not pass. Please follow the Excel template format in the top right corner of the page for data entry. ");}}});                      }, ' Onuploadstart ': function  (file)  {initupfile ();//upload the file before  , reset the GUID at each different $ ("#file_ Upload "). Uploadify (" Settings ", &nbsP; ' FormData ', {  ' folder ':  ' data import file ',  ' GUID ':  $ ("#AttachGUID"). Val ()  });  //Dynamic Transfer parameters }, ' Onuploaderror ': function  (event, queueid, fileobj, errorobj)  {//alert ( errorobj.type +  ":"  + errorobj.info);});

In order to effectively handle the import of data, we need to strictly guarantee that the imported data is matched to the field of the template, otherwise the processing is error-prone and meaningless. To achieve this, the framework provides methods to check the fields, mainly to ensure that Excel contains the Complete field.

<summary>///Check if the fields of the Excel file contain the required fields///</summary>///<param name= "GUID" > Attachments guid</param> <returns></returns>public actionresult checkexcelcolumns (string guid) {Commonresult result = new Commonresult (); try{datatable dt = convertexcelfiletotable (GUID); if (dt! = NULL) {//Check list contains required field result. Success = datatablehelper.containallcolumns (dt, columnstring);}} catch (Exception ex) {logtexthelper.error (ex); result. ErrorMessage = ex. Message;} return tojsoncontent (result);}

 <summary>///  get the Excel file on the server and convert it to a list of entities returned to the client/// </summary>/// <param  name= "GUID" > Guid</param>/// <returns></returns>public actionresult of Attachments  getexceldata (String guid) {if  (string. IsNullOrEmpty (GUID)) {return null;} List<contactinfo> list = new list<contactinfo> ();D atatable table =  convertexcelfiletotable (GUID);if  (table != null) {#region   data conversion int i =  1;foreach  (datarow dr in table. Rows) {string customername = dr["Customer name"]. ToString ();if  (string. IsNullOrEmpty (CustomerName)) {continue;//Customer name is empty, record skipped}customerinfo customerinfo = bllfactory< Customer>. Instance.findbyname (CustomerName);if  (customerinfo == null) {continue;//Customer name does not exist, record skipped}contactinfo  info = new contactinfo (); info. customer_id = customerinfo.id;//Customer idinfo.handno  = dr["Number"]. ToString (); info. name = dr["Name"]. ToString ();  ..............................//adds an escape info for a special field. Data1 = bllfactory<customer>. Instance.getcustomername (info. customer_id); list. ADD (info);} #endregion}var result = new { total = list. Count, rows = list };return jsondate (result);}

3. Export operation of data on the Web

Just introduced the data import operation, the data export operation is relatively simple, its JS function operation is as follows.

Export Excel data var exportcondition;function showexport () {var url = "/contact/export"; $.ajax ({type: "POST", Url:url,data: Exportcondition,success:function (FilePath) {var downurl = '/fileupload/downloadfile?file= ' + filePath; window.location = Downurl;}});}

Although the data export is relatively simple, but because we need to use the Post method to submit data conditions, so do not download the file window.open (URL) as a normal way to implement the file download. If the Post method submits the parameter, the returned data is not valid for download, even if it is a file stream.

From the above script we can see that the inside of the exportcondition is we need to submit to the server condition, the server based on this condition to retrieve data, and return an Excel file on it.

Since the use of Ajax this post way can not directly download the file stream, so we need to build the file on the server according to the condition, return a file path, again through the DownloadFile method to download the file.

So the condition of this transmission is also very important, in the query operation, we can pass the corresponding conditions to it.

Bind the Search button's Click event function bindsearchevent () {//Conditionally query data, first we get the value of the data $ ("#btnSearch"). Click (function () {///// There are several ways to take the value: $ ("#id"). ComboBox (' GetValue '), $ ("#id"). Datebox (' GetValue '), $ ("#id"). Val (), Combotree (' getValue ')// The field increases the WHC_ prefix character to avoid passing the Request keyword conflict such as a URL var querydata = {whc_name: $ ("#txtName"). Val (), Whc_officephone: $ ("# Txtofficephone "). Val (), Whc_mobile: $ (" #txtMobile "). Val (), Whc_address: $ (" #txtAddress "). Val (), Whc_email: $ (" # Txtemail "). Val (), Whc_note: $ (" #txtNote "). Val ()}//Pass the value to Datagridinitgrid (querydata);//pass to export operation Exportcondition = Querydata;return false;});

When we select the node of a tree, we can also pass a custom condition to it.

Loads the specified list according to the message group function Loadbygrouptree (node) {//assigns a value to a special field, the company and the Department queries the time to select one of the var queryparams = $ (' #grid '). DataGrid (' Options '). Queryparams;var condition = "{id: \" "+ node.id +" \ ", groupname:\" "+ Node.text +" \ ", userid:\" "+ @Session [" Us Erid "] +" \ "}"; queryparams.customedcondition = condition;//provided to the datagrid condition Exportcondition = {customedcondition: Condition};//is provided to the exported condition $ ("#grid"). DataGrid ("Reload"); $ (' #grid '). DataGrid (' Uncheckall ');

The main logic behind the export controller method in the background is as follows.

650) this.width=650; "src=" Http://images.cnitblog.com/i/8867/201407/281713051803941.png "style=" border:0px; "/>

Finally, it returns a generated file address.

Finally, you can download the file directly to a method.

/// <summary>///  download files based on the path, primarily for the download of generated files/// </ Summary>/// <param name= "FilePath" > File path </param>/// <returns></returns >public actionresult downloadfile (String file) {string realpath =  Server.MapPath (file); String savefilename = fileutil.getfilename (Realpath); Response.WriteFile (Realpath); response.charset =  "GB2312"; Response.contentencoding = encoding.getencoding ("GB2312"); response.contenttype =  "Application/ms-excel/msword"; Response.appendheader ("Content-disposition",  "Attachment;filename="  + httputility.urlencode ( Savefilename)); Response.Flush (); Response.End (); Return new filestreamresult (response.outputstream,  "Application/ms-excel/msword" );}

The exported Excel interface effect is as follows.

650) this.width=650; "src=" Http://images.cnitblog.com/i/8867/201407/281718085551959.png "style=" border:0px; "/>

Because of the length of the reason, this import and export operation is introduced here, I hope there are problems to discuss together.

This article is from the "Wu Huacong blog" blog, make sure to keep this source http://wuhuacong.blog.51cto.com/1779896/1828953

Mvc+easyui-based Web development Framework Experience Summary (10)--Implement data import and export on Web interface