Excel One-click Upload to the database

<a class= "edit" id= "Batchimport" > Bulk Import </a>

JS Code pop-up window:

$ ("#batchImport"). Click (function () {

Pop-up window the following contents
var html= ' <form id= "Execlform" method= "post" enctype= "Multipart/form-data" > ";
html+= ' <input type= "file" name= "file" id= "Execlfile" accept= "application/vnd.ms-excel"/> ";
html+= ' </form> ';
Layer.confirm (HTML, {
BTN: [' Import ', ' Cancel '],//button
Title: ' Importing Testers '
}, function () {
var file = $ ("#execlFile"). Val ();
Console.info (file);
var strfilename=file.replace (/^.+?\\ ([^\\]+?) (\. [^\.\\]*?)?  $/gi, "$"); Regular expression gets the file name without the suffix
var filename=file.replace (/.+\./, "");
if (filename== ') {
Layer.msg (' Please select Upload file! ', {time:1000});
return false;
}

$.ajax ({
URL: ' <%=path%>/front/conner/uploadexecl.do? ',
Type: ' POST ',
Cache:false,
Data:new FormData ($ (' #execlForm ') [0]),
Processdata:false,
Contenttype:false
}). Done (function (res) {
Console.info (RES);
if (res== "true") {
Layer.msg (' upload succeeded ', {time:1000});
SetTimeout (function () {
Table.ajax.reload (). Draw ();
},1500);
}else{
Layer.msg (' upload failed ' +res+ ' data ', {time:1000});
SetTimeout (function () {
Table.ajax.reload (). Draw ();
},1500);
}

}). Fail (function (res) {
Console.info (RES);
});
});
});
Controllor Control Code

/**
* Bulk Import of testers ' information
* @param request
* @param response
* @param bankname
*/
@RequestMapping ("Uploadexecl")
public void Uploadbillfile (HttpServletRequest request, httpservletresponse response) {
TODO set up counselor number get in session
Map map = Sessioncontext.get ("Frontuserinfo");
String Counselorid = (string) map.get ("id");//Get the ID of the counselor

The list collection receives the Excel file read
list<multipartfile> list = connerservice.getmultipartfile (request);

if (List.size () ==0) {
Out.out (response, "false");//indicates that the file is not uploaded
}else{
String filename = list.get (0). Getoriginalfilename ();
Determine if the read file is in Excel format
Boolean flag = Filename.endswith (". xls");
if (flag) {
try {
The function that reads the Excel file is called when it is judged correctly
Out.out (response, Connerservice.uploadbillfile (list.get (0), Counselorid));
} catch (Exception e) {
E.printstacktrace ();
Out.out (response, "false");
}
}else{
Out.out (response, "false");//The file being uploaded is not an Excel file
}
}
}

Service Code

/**
* Get upload Batch import tester information
* @param request
* @return
*/
Public list<multipartfile> Getmultipartfile (HttpServletRequest request) {
list<multipartfile> list = new arraylist<multipartfile> ();
Commonsmultipartresolver multipartresolver = new Commonsmultipartresolver (
Request.getsession (). Getservletcontext ());
Determine if request has a file upload, that is, multipart requests
if (Multipartresolver.ismultipart (request)) {
Convert to multi-part Request
Multiparthttpservletrequest multirequest = (multiparthttpservletrequest) request;
Get all the file names in request
Iterator<string> iter = Multirequest.getfilenames ();
while (Iter.hasnext ()) {
Get upload file
Multipartfile file = Multirequest.getfile (Iter.next ());
List.add (file);
}

}
return list;
}
/**
* Operations to read Excel files
* @param billfile
* @return
* @throws Exception
*/
Public String uploadbillfile (multipartfile billfile,string counselorid) throws Exception {
Create a Collection
list<list> tollist = new arraylist<list> ();
InputStream is = Billfile.getinputstream ();
Reading the work area of an Excel file
Jxl. Workbook Workbook = Jxl. Workbook.getworkbook (IS);
Set the first workspace to get only Excel files
Sheet Sheet = workbook.getsheet (0);
Get row
int rows = Sheet.getrows ();
Get column
int columns = Sheet.getcolumns ();
Outer Loop Fetch row
for (int i = 1; i < rows; i++) {

List evelist = new arraylist<string> ();
Define a Boolean variable to make a mark
Boolean flag=true;
Inner Loop column
for (int j = 0; J <; J + +) {
The cell is a class in the JXL, where rows and columns are fetched
Cell cell = Sheet.getcell (j, I);
String content = cell.getcontents ();
Stringutils.isblank determines whether a string is empty or a length of 0 or by a white space character
if (Stringutils.isblank (content)) {
Flag=false;
For false jumps
Break
}
Add to collection for true
Evelist.add (content);
}
Evaluates to true to add to the collection
if (flag) {
Tollist.add (evelist);
}

}


Reads an Excel file and returns an exception message if there is an error
List errorlist = Userinfodao.savebatchconner (Tollist,counselorid);
String result = "true";


if (errorlist.size () = = 0) {
}else{
result = "" +errorlist.size ();
}
return result;
}
DAO Layer Code

/**
* Consultant Operation Batch Save tester, upload Excel operation
* Description: Function mainly read Excel file, save entity
* @param tollist
* @return
*/
Public List Savebatchconner (list<list> tolist,string counselorid) {
School number, name, gender, date of birth, nationality, blood type, QQ, mailbox, only child, single parent, region, occupation, education, remark, counselor verification.
List<userinfo> errorlist=new arraylist<userinfo> ();
Extract execl column data to a collection of properties, looping the contents of Excel
for (int i=0,len=tolist.size (); i<len;i++) {
Gets the Excel Tollist.get (gets a row). Get (the first column in a row). ToString ()
UserInfo UserInfo = new UserInfo ();
Userinfo.setid (Uuid.getuuid ());//get non-repeating number
Userinfo.setcounselorid (Counselorid);
Userinfo.setisconner ("1");//1 says it's a tester.
Userinfo.setaccount (Tolist.get (i). Get (0). toString ());//Get First Row first column
Userinfo.setusername (Tolist.get (i). Get (1). ToString ());//Loop First row second column
Userinfo.setsex (Tolist.get (i). Get (2). toString ());
Userinfo.setbirthday (Tolist.get (i). Get (3). ToString ());
Userinfo.setnation (Tolist.get (i). Get (4). ToString ());
Userinfo.setbloodgroup (Tolist.get (i). Get (5). toString ());
USERINFO.SETQQ (Tolist.get (i). Get (6). ToString ());
Userinfo.setemail (Tolist.get (i). Get (7). ToString ());
Userinfo.setistheonlychild (Tolist.get (i). Get (8). toString ());//Singleton
Userinfo.setissingleparent (Tolist.get (i). Get (9). ToString ());//single Parent
Userinfo.setarea (Tolist.get (i). Get () toString ());//region
Userinfo.setprofession (Tolist.get (i). Get (one). ToString ());//Occupation
Userinfo.setenducationlbackground (Tolist.get (i). Get (+). toString ());//Education
Userinfo.setremark (Tolist.get (i). Get (. toString ());//Remarks

Save to entity class
String flag=saveentity (UserInfo);
Returns a collection if there is an error message
if (! " True ". Equals (Flag)") {
Errorlist.add (UserInfo);
}
}
return errorlist;
}

Excel One-click Upload to the database