The BootStrap Fileinput plug-in and the Bootstrap table plug-in are combined to implement file upload, preview, and submit import Excel data operation steps, bootstrapfileinput

Source: Internet
Author: User
Tags import database

The BootStrap Fileinput plug-in and the Bootstrap table plug-in are combined to implement file upload, preview, and submit import Excel data operation steps, bootstrapfileinput

Bootstrap-fileinput Source: https://github.com/kartik-v/bootstrap-fileinput

Bootstrap-fileinput online API: http://plugins.krajee.com/file-input

Bootstrap-fileinput Demo Presentation: http://plugins.krajee.com/file-basic-usage-demo

This plug-in mainly describes how to process image uploads. I used the Uploadify plug-in for Excel import operations. For details, refer to my post "use of the attachment Upload Component uploadify". however, this requires the support of the Flash control, which is troublesome in Some browsers (such as Chrome). Therefore, we decided to use a more general upload plug-in, this time, we will first upgrade the framework system based on the Bootstrap front-end architecture to replace the original Uploadify plug-in, so that the page upload function can be implemented in various Browsers without any difference.

In general, we need to introduce the following two files before the plug-in can be used properly:

bootstrap-fileinput/css/fileinput.min.cssbootstrap-fileinput/js/fileinput.min.js

When using the File input plug-in, if it is based on Asp. net mvc, we can use BundleConfig. cs to add the corresponding reference and add it to the Bundles collection reference.

// Add support for the bootstrap-fileinput control css_metronic.Include ("~ /Content/MyPlugins/bootstrap-fileinput/css/fileinput.min.css "); js_metronic.Include ("~ /Content/MyPlugins/bootstrap-fileinput/js/fileinput. min. js "); js_metronic.Include ("~ /Content/MyPlugins/bootstrap-fileinput/js/locales/zh. js ");

On the page, we use the following HTML code to display the interface, mainly the bootstrap fileinput plug-in Declaration, mainly the basic interface code.

<input id="excelFile" type="file">

The Excel import page is displayed as follows.

After selecting the specified file, we can see the Excel file list, as shown in the following interface.

After the file is uploaded, the data is directly displayed in the pop-up list, where the Bootstrap-table plug-in is directly used for display.

In this way, we can display the Excel Record, implement the preview function, select the necessary record, and save it, and then submit it to the server for storage, the real import database processing of Excel Data is realized.

2. Excel export operations

The HTML code is as follows.

<! -- Import data operation layer --> <div id = "import" class = "modal fade bs-modal-lg" tabindex = "-1" role = "dialog" aria-labelledby =" myModalLabel "aria-hidden =" true "> <div class =" modal-dialog modal-lg "> <div class =" modal-content "> <div class =" modal- header bg-primary "> <button type =" button "class =" close "data-dismiss =" modal "aria-hidden =" true "> </button> 

For the various attributes of bootstrap fileinput, we use JS for initialization to facilitate unified management and modification.

// Initialize the Excel file function InitExcelFile () {// record GUID $ ("# AttachGUID "). val (newGuid (); $ ("# excelFile "). fileinput ({uploadUrl: "/FileUpload/Upload", // Upload address uploadAsync: true, // asynchronous Upload language: "zh", // setting language showCaption: true, // whether to display the title showUpload: true, // whether to display the upload button showRemove: true, // whether to display the Remove button showPreview: true, // whether to display the preview button browseClass: "btn-primary", // button style dropZoneEnabled: false, // whether to display the drag/drop area allowedF IleExtensions: ["xls", "xlsx"], // The received file suffix maxFileCount: 1, // maximum number of uploaded files limit previewFileIcon: '<I class = "glyphicon-file"> </I>', allowedPreviewTypes: null, previewFileIconSettings: {'docx ': '<I class = "glyphicon-file"> </I>', 'xlsx': '<I class = "glyphicon-file"> </I> ', 'ppt': '<I class = "glyphicon-file"> </I>', 'jpg ': '<I class = "glyphicon-picture"> </I>', 'pdf ':' <I class = "glyphicon-file"> </I> ', 'zip ': '<I class = "glyphicon-file"> </I>',}, added additional parameter folder when uploading: 'Data import file', guid: $ ("# AttachGUID "). val ()}) // events after the file is uploaded. on ('fileuploaded', function (event, data, previewId, index) {var form = data. form, files = data. files, extra = data. extra, response = data. response, reader = data. reader; var res = data. response ;/ /Return result if (res. success) {showTips ('upload succeeded '); var guid = $ ("# AttachGUID "). val (); // prompt whether the Excel format is normal. If the data is loaded normally, $. ajax ({url: '/TestUser/CheckExcelColumns? Guid = '+ guid, type: 'get', ype: 'json', success: function (data) {if (data. success) {InitImport (guid); // refresh the table data showToast ("the file has been uploaded and the data has been loaded! "); // Refresh the GUID and clear the file to facilitate the next RefreshExcel ();} else {showToast (" the uploaded Excel file fails to be checked. Enter data according to the Excel template format in the upper-right corner of the page. "," Error ") ;}}) ;}else {showTips ('upload failed ');}});}

The above logic is specific: Set the background page of the file to/FileUpload/Upload, And the configuration parameters of various plug-ins. In uploadExtraData, set additional parameters for submission, that is, the parameters received by the background controller.

.on('fileuploaded', function (event, data, previewId, index) {

If the result returned by the uploaded file is successful, call ajax again to check whether the Excel Field meets the requirements:

url: '/TestUser/CheckExcelColumns?guid=' + guid,

If the check background returns a successful record, you need to extract the Excel Record and preview it again, and clear the bootstrap fileinput File Upload plug-in to facilitate the next file upload. The following code is used.

If (data. Success) {InitImport (guid); // refresh the table data showToast ("the file has been uploaded and the data has been loaded! "); // Refresh the GUID and clear the file to facilitate the next RefreshExcel ();} else {showToast (" the uploaded Excel file fails to be checked. Enter data according to the Excel template format in the upper-right corner of the page. "," Error ");}

RefreshExcel re-updates the value of the additional parameters for the next upload. Otherwise, the value of the additional parameters remains unchanged, leading to the problem that the GUID we set remains unchanged.

// Re-update the GUID value and clear the file function RefreshExcel () {$ ("# AttachGUID "). val (newGuid (); $ ('# excelFile '). fileinput ('clear'); // clear all files // The attached parameters remain unchanged after initialization, you need to use refresh to update $ ('# excelfile '). fileinput ('refresh', {uploadExtraData: {folder: 'Data import file', guid: $ ("# AttachGUID "). val ()},});}

InitImport obtains the Preview Data and displays it on the Bootstrap-table plug-in. For detailed use of this plug-in, let's review the article "Metronic-based Bootstrap development framework Experience Summary (16) -- use the plug-in bootstrap-table to query, pagination, sort, and other processes of table records.

// Query and bind the result var $ import according to the conditions; function InitImport (guid) {var url = "/TestUser/GetExcelData? Guid = "+ guid; $ import = $ ('# gridImport '). bootstrapTable ({url: url, // request background URL (*) method: 'get', // Request method (*) striped: true, // whether to display the row interval color cache: false, // whether to use the cache. The default value is true. Therefore, you need to set this attribute (*) pagination: false, // whether to display the page (*) sidePagination: "server", // paging mode: client page, server Page (*) pageNumber: 1, // initialize loading the first page, by default, the first page is displayed, and pageSize: 100, // The number of records per page (*) pageList: [10, 25, 50,100], // The number of lines per page (*) search: false, // Whether to display the table search strictSearch: true, showColumns: true, // whether to display all columns (select the displayed column) showRefresh: true, // whether to display the refresh button minimumCountColumns: 2, // the minimum number of columns allowed clickToSelect: true, // whether to enable the unique ID of the selected row uniqueId: "ID", // the unique ID of each row, generally the primary key column queryParams: function (params) {}, columns: [{checkbox: true, visible: true // whether to display check boxes}, {field: 'name', title: 'name '}, {field: 'mobile', title: 'mobile'}, {field: 'email ', title: 'mailbox', formatter: e MailFormatter}, {field: 'homepage', title: 'homepage', formatter: linkFormatter}, {field: 'hobby', title: 'interests '}, {field: 'gender', title: 'gender', formatter: sexFormatter}, {field: 'age', title: 'age'}, {field: 'birthdate', title: 'birthdate ', formatter: dateFormatter}, {field: 'height', title: 'height'}, {field: 'note', title: 'note'}], onLoadSuccess: function () {}, onLoadError: function () {showT Ips ("data loading failed! ");},});}

Finally, after the submission is confirmed, the data will be submitted to the background through JS for processing, as shown in the following code.

// Save the imported data function SaveImport () {var list = []; // construct the set object var rows = $ import. bootstrapTable ('getselection'); for (var I = 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]. holobby, '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 ("select a record", "warning"); return;} var postData = {'LIST': list }; // you can add other parameters, such as {'LIST': list, 'rucanghao': $ ("# Rucanghao "). val ()}; postData = JSON. stringify (postData); $. ajax ({url: '/TestUser/SaveExcelData', type: 'post', ype: 'json', contentType: 'application/json; charset = UTF-8 ', traditional: true, success: function (data) {if (data. success) {// saved successfully. close the pop-up layer, 2. clear record display 3. refresh the master list showToast ("saved successfully"); $ ("# import "). modal ("hide"); iterator (bodytag).html (""); Refresh ();} else {showToast ("failed to save:" + data. errorMessage, "error") ;}}, data: postData });}

3. Background controller code analysis

Here, the JS Code involves several MVC backend Methods: Upload, CheckExcelColumns, GetExcelData, and SaveExcelData. Here we will introduce them separately.

The method of the background controller for file upload is as follows.

/// <Summary> /// upload the attachment to the server /// </summary> /// <param name = "fileData"> Attachment Information </param> // /<param name = "guid"> attachment group GUID </param> // <param name = "folder"> specified upload directory </param> // <returns> </returns> [AcceptVerbs (HttpVerbs. post)] public ActionResult Upload (string guid, string folder) {CommonResult result = new CommonResult (); HttpFileCollectionBase files = HttpContext. request. files; if (files! = Null) {foreach (string key in files. Keys) {try {# region MyRegion HttpPostedFileBase fileData = files [key]; if (fileData! = Null) {HttpContext. request. contentEncoding = Encoding. getEncoding ("UTF-8"); HttpContext. response. contentEncoding = Encoding. getEncoding ("UTF-8"); HttpContext. response. charset = "UTF-8"; // File Upload path string filePath = Server. mapPath ("~ /UploadFiles/"); DirectoryUtil. assertDirExist (filePath); string fileName = Path. getFileName (fileData. fileName); // the name of the original file string fileExtension = Path. getExtension (fileName); // file extension // string saveName = Guid. newGuid (). toString () + fileExtension; // save the file name FileUploadInfo info = new FileUploadInfo (); info. fileData = ReadFileBytes (fileData); if (info. fileData! = Null) {info. fileSize = info. fileData. length;} info. category = folder; info. fileName = fileName; info. fileExtend = fileExtension; info. attachmentGUID = guid; info. addTime = DateTime. now; info. editor = CurrentUser. name; // login result = BLLFactory <FileUpload>. instance. upload (info); if (! Result. success) {LogTextHelper. error ("failed to upload file:" + result. errorMessage) ;}# endregion} catch (Exception ex) {result. errorMessage = ex. message; LogTextHelper. error (ex) ;}} else {result. errorMessage = "fileData object is blank";} return ToJsonContent (result );}

After the file is uploaded and processed, a common result object of CommonResult is returned, which makes it easy for us to judge and process the result on the JS client.

Check whether the data imported into the Excel file meets the requirements of the column to determine whether the data column is consistent with the pre-configured column name.

// List of imported or exported fields string columnString = "name, mobile phone, email, homepage, hobbies, gender, age, birth date, height, remarks "; /// <summary> /// check whether the fields in the Excel file contain the required fields. /// </summary> /// <param name = "guid"> GUID </param> /// <returns> </returns> public ActionResult CheckExcelColumns (string guid) {CommonResult result = new CommonResult (); try {DataTable dt = ConvertExcelFileToTable (guid); if (dt! = Null) {// check whether the list contains the required field result. success = DataTableHelper. containAllColumns (dt, columnString) ;}} catch (Exception ex) {LogTextHelper. error (ex); result. errorMessage = ex. message;} return ToJsonContent (result );}

GetExcelData is used to format the Excel Data to the specific List <TestUserInfo> set, so that we can perform various attribute operations on the client. Its code is as follows.

/// <Summary> /// obtain the Excel file on the server, convert the Object List and return it to the client // </summary> /// <param name = "guid"> attachment GUID </param> /// <returns> </returns> public ActionResult GetExcelData (string guid) {if (string. isNullOrEmpty (guid) {return null;} List <TestUserInfo> list = new List <TestUserInfo> (); DataTable table = ConvertExcelFileToTable (guid); if (table! = Null) {# region data conversion int I = 1; foreach (DataRow dr in table. rows) {bool converted = false; DateTime dtDefault = Convert. toDateTime ("1900-01-01"); DateTime dt; TestUserInfo info = new TestUserInfo (); info. name = dr ["Name"]. toString (); info. mobile = dr ["Mobile Phone"]. toString (); info. email = dr ["Email"]. toString (); info. homepage = dr ["Homepage"]. toString (); info. holobby = dr ["hobbies"]. toString (); info. gender = dr ["Gender"]. toString (); info. age = dr ["Age"]. toString (). toInt32 (); converted = DateTime. tryParse (dr ["Date of Birth"]. toString (), out dt); if (converted & dt> dtDefault) {info. birthDate = dt;} info. height = dr ["Height"]. toString (). toDecimal (); info. note = dr ["Remarks"]. toString (); info. creator = CurrentUser. ID. toString (); info. createTime = DateTime. now; info. editor = CurrentUser. ID. toString (); info. editTime = DateTime. now; list. add (info) ;}# endregion} var result = new {total = list. count, rows = list}; return ToJsonContent (result );}

Another SaveExcelData function is the final processing function for data import. It mainly writes the set to a specific database. The specific code is as follows.

/// <Summary> /// Save the list of related data uploaded by the client // </summary> /// <param name = "list"> data list </param> /// <returns> </returns> public ActionResult SaveExcelData (List <TestUserInfo> list) {CommonResult result = new CommonResult (); if (list! = Null & list. Count> 0) {# region uses transactions to submit data DbTransaction trans = BLLFactory <TestUser>. Instance. CreateTransaction (); if (trans! = Null) {try {// int seq = 1; foreach (TestUserInfo detail in list) {// detail. seq = seq ++; // Add 1 detail. createTime = DateTime. now; detail. creator = CurrentUser. ID. toString (); detail. editor = CurrentUser. ID. toString (); detail. editTime = DateTime. now; BLLFactory <TestUser>. instance. insert (detail, trans);} trans. commit (); result. success = true;} catch (Exception ex) {LogTextHelper. error (ex); result. errorMessage = ex. message; trans. rollback () ;}# endregion} else {result. errorMessage = "import information cannot be blank";} return ToJsonContent (result );}

The code of the above functions is generally regular and does not need to be written one by one. Generally, you can generate them in batches using the code generation tool Database2Sharp. This effectively improves the development efficiency of Web interface code and background code and reduces the chance of errors.

All the code for the whole process of importing Excel Data is pasted. Basically, the entire logic is well understood.

Summary

The above section describes how to import Excel Data by combining the BootStrap Fileinput plug-in and the Bootstrap table plug-in introduced by the editor to upload, preview, and submit files, if you have any questions, please leave a message and the editor will reply to you in time. Thank you very much for your support for the help House website!

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.