Use POI for Excel import and resolve insert Database

Objective

Recently also have to write Excel import and export, the result is Baidu, although can understand, but still want to record these things

Body 1. Importing JAR Packages

<dependency>        <groupId>org.apache.poi</groupId>        <artifactid>poi-ooxml</ Artifactid>        <version>3.17</version></dependency>

2. Start the Import

@RequestMapping (value = "Importusers", method =requestmethod.post) PublicMap<string,object>importusers (Multipartfile userfile) {Map<String,Object> Resultmap =NewHashmap<>(); if(UserFile = =NULL||Userfile.isempty ()) {Resultmap.put ("Success",false); Resultmap.put ("MSG", "Data file does not exist"); returnResultmap; }
Open a new thread to handle the import, and use callback to accept the result Futuretask<Map<String,Object>> task =NewFuturetask<>(                ()-userservice.importusers (userfile)); NewThread (Task). Start (); Try{Resultmap=Task.get (); } Catch(Exception e) {e.printstacktrace (); }        returnResultmap; }

3. Business Layer

@Override PublicMap<string, object>importusers (Multipartfile userfile) {Map<String,Object> Resultmap =NewHashmap<>(); Try {            //gets the input stream of the fileInputStream InputStream =Userfile.getinputstream (); //Create book pages based on different types of Excel. String FileName =Userfile.getoriginalfilename (); Workbook Book=NULL; if(Filename.endswith (XLSX)) { Book=NewXssfworkbook (InputStream); }Else if(Filename.endswith (XLS)) { Book=NewHssfworkbook (InputStream); }Else{resultmap.put ("Success",false); Resultmap.put ("MSG", "file format is wrong!" "); returnResultmap; }            if(Book! =NULL){                //First WorkbookSheet Sheet = book.getsheetat (0); //convert results to collectionslist<user> users =CONVERT (sheet);  for(User u:users) {usermapper.insert (U);            } System.out.println (users); } resultmap.put ("Success",true); Resultmap.put ("MSG", "Upload success!" "); returnResultmap; } Catch(IOException e) {e.printstacktrace (); }        returnResultmap; }
/**
* Encapsulate each row of data into one object
*/

Private list<user> convert (Sheet Sheet) {
list<user> userlist = new arraylist<> ();
for (int i = 2; I <= sheet.getlastrownum (); i++) {
The first row, the second row is skipped, is the record name and the field name, starting from the third row
Row row = Sheet.getrow (i);
User user = new user ();
iterator<cell> Iterator = Row.celliterator ();
while (Iterator.hasnext ()) {
Cell cell = Iterator.next ();
if (cell.getcolumnindex () = = 1) {
The second column, the type is set to string, and then assigns a value to the name
Cell.setcelltype (celltype.string);
User.setphone (Cell.getstringcellvalue ());
}
if (cell.getcolumnindex () = = 3) {
Third column, time format
if (dateutil.iscelldateformatted (cell)) {
Date date = Cell.getdatecellvalue ();
User.setcreatedate (date);
}
}
if (cell.getcolumnindex () = = 2) {
The second column, the type is set to string, and then assigns a value to the name
Cell.setcelltype (celltype.string);
User.setpassword (Cell.getstringcellvalue ());
}
}
Userlist.add (user);
}
return userlist;
}

So far, the import of Excel has been implemented!

It is important to note that the data must be taken out of the template and assigned to different attributes, respectively.

Use POI for Excel import and resolve insert Database