Excel document Upload

I haven't written anything for two months, so I pulled out to practice today. Write an Excel document data upload to save the database.

One: the preparatory work

1. Jar Package: Maven used at the time, I joined the jar package

<!--Jxl.jar Working with Excel tables-    <dependency><groupId>net.sourceforge.jexcelapi</groupId> <artifactId>jxl</artifactId><version>2.6.10</version></dependency>

2. Now it's time to start writing

@RequestMapping (value = "/importfile") public @ResponseBody Map ImportFile (httpservletrequest request, HttpServletResponse response) throws Exception

Back to map data

String Interfacecode = ""; resultmap = new hashmap<string, object> (); User user = null; String userName = null;  User name string realname = null;  real name string cellphone = null; Mobile number string deptname = null;  Department name string remark = null;    Note int count = 0; Current database official number of employees int total = 0; Total: Current number of employees plus number of imports workbook book = null; File localfile = null;

Defining variables

Authorized official number of employees int presetnumber = Integer.parseint (Sysconfigutil.getparamvalue ("accredit.official.employees"));

Obtain authorization information from the configuration file.

Commonsmultipartresolver multipartresolver  = new Commonsmultipartresolver (Request.getsession (). Getservletcontext ()); if (Multipartresolver.ismultipart (Request)) {multiparthttpservletrequest  multirequest = ( multiparthttpservletrequest) request;iterator<string>  iter = Multirequest.getfilenames (); while ( Iter.hasnext ()) {Multipartfile file = Multirequest.getfile ((String) Iter.next ()); if (file = = null) {continue;}

Springmvc the upload file method that comes with it.

String fileName = File.getoriginalfilename (); String path = Request.getservletcontext (). Getrealpath ("File/temp"); localfile = new File (path+ "/" +filename); File.transferto (LocalFile);
Book = Workbook.getworkbook (LocalFile);//Get File

This way, get the file.

Sheet Sheet = book.getsheet (0); Get the Sheet object int rows = Sheet.getrows ();//Line list<user> successlist = new arraylist<user> (rows); count = Userservice.countfindbyusertype (2);//Get the full number of employees total = Rows+count;

Create a worksheet

if (total<presetnumber) {//title string usernametitle = Sheet.getcell (0,0). getcontents (). Trim (); String realnametitle = Sheet.getcell (1,0). getcontents (). Trim (); String cellphonetitle = Sheet.getcell (2,0). getcontents (). Trim (); String deptnametitle = Sheet.getcell (3,0). getcontents (). Trim (); String DefaultPassword = Sysconfigutil.getparamvalue ("System.default.password"); for (int i = 0; i < rows; i++) { Username= Sheet.getcell (0,i). getcontents (). Trim (); User name realname = Sheet.getcell (1,i). getcontents (). Trim ();//real name cellphone= Sheet.getcell (2,i). getcontents (). Trim () ;//Mobile number Deptname = Sheet.getcell (3,i). getcontents (). Trim ();//department Name Remark = Sheet.getcell (4,i). getcontents (). Trim (); Memo//Checksum parameter if (Stringutils.isblank (userName)) {//username resultmap.put ("result", "FAIL") resultmap.put ("Result", "No." + i + " The line "+ Usernametitle +" cannot be empty! "); return resultmap;} if (Stringutils.isblank (realname)) {//Name Resultmap.put ("result", "FAIL") resultmap.put ("Result", "No." + i + "line" + Realnam Etitle + "cannot be empty! "); return resultmap;}if (Stringutils.isblank (cellphone)) {//Phone number resultmap.put ("Result", "FAIL") resultmap.put ("Result", "No." + i + "line" + CELLP Honetitle + "cannot be empty! "); return resultmap;} if (Stringutils.isblank (deptname)) {//Department Resultmap.put ("result", "FAIL") resultmap.put ("Result", "No." + i + "line" + Deptnam Etitle + "cannot be empty! "); return resultmap;} Encapsulates data into the collection user = new user (); user.setusername (userName); User.setrealname (realname); User.setpassword ( DefaultPassword); User.setdeptname (deptname); User.setremark (remark); User.setusertype (2); Successlist.add (user); if (Userservice.isusernameexist (User.getusername ())) {resultmap.put ("result", "FAIL"), Resultmap.put ("result", User.getusername () + messageutil.getmessage ("user.userName.exist"));//user name already exists return resultmap;}} Loop save Data int maxSize = Successlist.size (); for (int i = (maxSize-1); i > 0; I-) {user = Successlist.get (i); Userservi Ce.insert (user); Roleservice.authorize (User.getid (), User.getusertype ());} Resultmap.put ("Result", "OK"), Resultmap.put ("message", "");

　

Localfile.delete ();

Delete temporary files, this is best put in finally

Loop to save data, just start to do is the right information to save, the error message returned to the user, and later found that the amount of data is large, it will be changed to return to the user error, and then upload

Excel document Upload