[Utils] POI Implementation Excel Import Export

1. Analyzing Excel

2.poi Tool Class

Import Org.apache.poi.hssf.usermodel.*;import Org.apache.poi.ss.usermodel.cell;import Org.apache.poi.ss.usermodel.row;import Org.apache.poi.ss.usermodel.sheet;import Org.apache.poi.ss.usermodel.workbook;import Org.apache.poi.ss.util.cellrangeaddress;import Org.apache.poi.xssf.usermodel.xssfworkbook;import Org.springframework.web.multipart.multipartfile;import Org.zenzzat.toa.nsfw.user.entity.user;import Javax.servlet.servletoutputstream;import Javax.servlet.http.httpservletresponse;import Java.io.inputstream;import Java.math.bigdecimal;import Java.util.arraylist;import java.util.list;/** * Excel Import Export Tool class * Created by Zenz.    */public class Excelutil {/** * export Excel format user list * @param userlist user list to export * @param response Output Response */ public static void Exportuserexcel (list<user> userlist, httpservletresponse response) {try {//Set            File type Response.setcontenttype ("Application/x-excel"); Set file name, Chinese with iso-8859-1 encoding Response.setheaDer ("Content-disposition", "attachment;filename=" + New String ("user list. xls". GetBytes (), "iso-8859-1"));            Servletoutputstream outputstream = Response.getoutputstream ();            1. Create Workbook Hssfworkbook workbook = new Hssfworkbook ();            1.1. Create merged Cell object cellrangeaddress cellrangeaddress = new cellrangeaddress (0, 0, 0, 4);//start line number, end line number, start column number, end column number            1.2, head title style Hssfcellstyle Headstyle = Createcellstyle (Workbook, (short) 16);            1.3. column heading style Hssfcellstyle colstyle = Createcellstyle (Workbook, (short) 13);            2. Create worksheet Hssfsheet sheet = workbook.createsheet ("user list");            2.1. Load merged Cell object Sheet.addmergedregion (cellrangeaddress);            Sets the default column width sheet.setdefaultcolumnwidth (25);            3. Create line//3.1, create Header header row, and set header header Hssfrow Headrow = sheet.createrow (0);            Hssfcell headcel = Headrow.createcell (0); Load cell style Headcel.setcelLstyle (Headstyle);            Headcel.setcellvalue ("user list");            3.2. Create column header row, and set column header Hssfrow colrew = Sheet.createrow (1);            String[] titles = {"username", "account", "department", "Gender", "E-mail"};                for (int i = 0; i < titles.length; i++) {Hssfcell colcel = Colrew.createcell (i);                Load cell style Colcel.setcellstyle (Colstyle);            Colcel.setcellvalue (Titles[i]); }//4, Action cell; Write user list to Excel if (userlist! = null) {for (int j = 0; J < userlist.size ();                    J + +) {Hssfrow row = Sheet.createrow (j + 2);                    Hssfcell cell0 = Row.createcell (0);                    Cell0.setcellvalue (Userlist.get (j). GetName ());                    Hssfcell cell1 = Row.createcell (1);                    Cell1.setcellvalue (Userlist.get (j). Getaccount ());                    Hssfcell cell2 = Row.createcell (2); Cell2.setcellvalue (Userlist.get (j). Getdept ());                    Hssfcell Cell3 = Row.createcell (3); Cell3.setcellvalue (Userlist.get (j). Getgender ()?                    "Male": "female");                    Hssfcell CELL4 = Row.createcell (4);                Cell4.setcellvalue (Userlist.get (j). Getemail ());            }}//5, Output workbook.write (OutputStream);            Workbook.close ();        Outputstream.close ();        } catch (Exception e) {e.printstacktrace (); }}/** * Create cell style * @param workbook workbook * @param fontSize font size * @return cell style * */private STA Tic Hssfcellstyle Createcellstyle (Hssfworkbook workbook, short fontSize) {Hssfcellstyle style = Workbook.createcel        Lstyle (); Style.setalignment (hssfcellstyle.align_center);//Horizontal Center style.setverticalalignment (hssfcellstyle.vertical_center)        ;//Vertical Center//Create font Hssffont font = Workbook.createfont (); Font.setboldweight (hssffont.boldweight_bold);//Bold Font font.seTfontheightinpoints (fontSize);        Load font style.setfont (fonts);    return style; /** * Import Excel format user list * @param userexcel the Excel file to import * @return read to the list of users */public static List<user > Importuserexcel (multipartfile userexcel) {if (userexcel! = null) {String userexcelfilename = user            Excel.getoriginalfilename (); Whether it is Excel if (Userexcelfilename.matches ("^.+\\. i) ((xls) | (                xlsx)) {list<user> userlist = new arraylist<> ();                    try {inputstream InputStream = Userexcel.getinputstream (); Boolean is03excel = Userexcelfilename.matches ("^.+\\.                    i) (XLS) $ "); 1. Read workbook Workbook Workbook = Is03excel?                    New Hssfworkbook (InputStream): New Xssfworkbook (InputStream);                    2. Read sheet Sheet Sheet = workbook.getsheetat (0); 3. Read line if (sheet.geTphysicalnumberofrows () > 2) {User user;                            for (int i = 2; I < sheet.getphysicalnumberofrows (); i++) {user = new user ();                            User.setid (Uuidutil.getuuid ());                            4. Read the cell row row = Sheet.getrow (i);                            Username Cell cell0 = Row.getcell (0);                            User.setname (Cell0.getstringcellvalue ());                            Account Cell cell1 = Row.getcell (1);                            User.setaccount (Cell1.getstringcellvalue ());                            Department Cell cell2 = Row.getcell (2);                            User.setdept (Cell2.getstringcellvalue ());                            Gender Cell Cell3 = Row.getcell (3);   User.setgender (Cell3.getstringcellvalue (). Equals ("male"));                         Phone number-The number needs special handling: If it is a string, read directly, if it is a number, it needs to be converted to string mobilenum = "";                            Cell CELL4 = Row.getcell (4);                            try {mobilenum = Cell4.getstringcellvalue ();                                } catch (Exception e) {Double mobiledlb = Cell4.getnumericcellvalue ();                            Mobilenum = bigdecimal.valueof (MOBILEDLB). toString ();                            } user.setmobile (Mobilenum);                            E-mail Cell cell5 = Row.getcell (5);                            User.setemail (Cell5.getstringcellvalue ());                            Birthday Cell cell6 = Row.getcell (6);                            if (cell6.getdatecellvalue () = null) {User.setbirthday (Cell6.getdatecellvalue ());        }                    Non-empty fields need to be set to the default value//default password 123456 user.setpassword ("12                            3456 ");                            The default state is valid User.setstate (user.user_state_valid);                        5. Save User Userlist.add (username);                    }}//Close resource workbook.close ();                Inputstream.close ();                } catch (Exception e) {e.printstacktrace ();            } return userlist;        } return null;    } return null; }}

3. File styles with export Import

[Utils] POI Implementation Excel Import Export