SSH series: (11) User Management-excel Import, export

The POI component is used here, and the jar package that needs to be introduced is:

Curvesapi-1.03.jar

Poi-3.14-20160307.jar

Poi-ooxml-3.14-20160307.jar

Poi-ooxml-schemas-3.14-20160307.jar

Xmlbeans-2.6.0.jar

Reference: POI components: POI operations Excel http://lsieun.blog.51cto.com/9210464/1836601

1. The user list is exported into Excel

1.1, listui.jsp

UI section

<input type= "button" value= "Export" class= "S_button" onclick= "Doexportexcel ()"/>&nbsp;<input name= " Userexcel "type=" file "/><input type=" button "value=" Import "class=" S_button "onclick=" Doimportexcel () "/>& nbsp

JavaScript section

Export user list function Doexportexcel () {window.open ("${basepath}/tax/user_exportexcel.action");}    Import function Doimportexcel () {document.forms[0].action = "${basepath}/tax/user_importexcel.action"; Document.forms[0].submit ();}

1.2, Useraction.java

Export user list public void Exportexcel () {try {//1, lookup user list userlist = Userservice.findall ();//2, export httpservletrequest request = Servletactioncontext.getrequest (); String useragent = Request.getheader ("user-agent"); System.out.println (useragent); HttpServletResponse response = Servletactioncontext.getresponse (); Response.setcontenttype ("application/ Octet-stream "); Response.setheader (" Content-disposition "," attachment;filename= "+ New String (" user list. xls ". GetBytes () , "iso-8859-1")); Servletoutputstream outputstream = Response.getoutputstream (); Userservice.exportexcel (UserList,outputStream); if ( OutputStream! = null) {Outputstream.close ();}} catch (Exception e) {e.printstacktrace ();}}

knowledge Point (1) new String ("user list. xls". GetBytes (), "iso-8859-1")

In this regard, its purpose is to solve the problem of Chinese garbled. I looked up some information and tested it (including Chrome, Firefox, IE11), New String ("user list. xls". GetBytes (), "iso-8859-1") to display Chinese correctly. When you do file download, when the file name is in Chinese, often garbled phenomenon occurs. The general reason is that only ASCII is supported in the header, so the file name we transmit must be ASCII, and when the file name is Chinese, it must be converted to ASCII. What is the relationship between the ASCII code and the iso-8859-1? The ASCII code range is 0~127 and can be represented by 7 bits. Most computers have a byte of 8 bits, the value range is 0~255, and the ASCII code does not specify a character numbered 128~255, in order to represent more characters, the manufacturers have developed many kinds of ASCII code extension specification. Note that although these specifications are often referred to as extended ASCII (Extended ASCII), they are not ASCII standard. The most widely used extended ASCII code in the graphical interface is Iso-8859-1, also known as Latin-1, which contains the most commonly used non-English alphabets in European languages, but after all, only 128 characters, some of which are not included in some languages. There are many ways to convert: Method One: Re-encode the Chinese file name with iso-8859-1, such as Headers.add ("Content-disposition", "Attachment;filename=" +new String ("China"). GetBytes ( "UTF-8"), "iso-8859-1") + ". txt"); Mode two: can use the Chinese file name URL encoding, such as Headers.add ("Content-disposition", "Attachment;filename=" +urlencoder.encode ("China", "UTF-8") + ". txt"); Question: Chinese file name converted to ASCII after the browser, the browser encountered a heap of ASCII, how can correctly restore the original Chinese file name of it? There are three ways: New String (, iso-8859-1), Urlencoder, Filename*=utf-8 ', ------------------------------------------------ Web applications, want to force a resource to being downloaded rather than directly rendered in a Web browser issue a Con Tent-disposition header in the HTTP response of the form: Content-disposition:attachment; Filename=filename The filename parameter can be used to suggest a name for the file into which the resource was downloaded by the browser. RFC 2183 (content-disposition), however, states in sections 2.3 (the Filename Parameter) that the file name can only use US -ascii characters: Current [RFC 2045] Grammar restricts parameter values (and hence content-disposition filenames) to Us-ascii. We recognize the great desirability of allowing arbitrary character sets in filenames, but it's beyond the scope of this Document to define the necessary mechanisms. The parameters "filename" and "filename*" differ only on that "filename*" uses the encoding Def Ined in [RFC5987], allowing the use of characters not present in the iso-8859-1 character Set ([Iso-8859-1]).

1.3, Userserivce.java

void Exportexcel (list<user> userlist, OutputStream outputstream);

1.4, Userserviceimpl.java

public void Exportexcel (list<user> userlist, OutputStream outputstream) {excelutils.exportuserexcel (userList, OutputStream);}

1.5, Excelutils.java

package com.rk.tax.utils;import java.io.ioexception;import java.io.outputstream;import  java.util.list;import org.apache.poi.hssf.usermodel.hssfcell;import  org.apache.poi.hssf.usermodel.hssfcellstyle;import org.apache.poi.hssf.usermodel.hssffont;import  org.apache.poi.hssf.usermodel.hssfrow;import org.apache.poi.hssf.usermodel.hssfsheet;import  org.apache.poi.hssf.usermodel.hssfworkbook;import org.apache.poi.ss.util.cellrangeaddress;import  com.rk.tax.entity.user;public class excelutils {/** *  Export all lists of users to excel * @ param userlist  user list  *  @param  outputStream  output stream  */public static  Void exportuserexcel (List<user> userlist,outputstream outputstream) {TRY&NBSP;{//1, Create a workbook Hssfworkbook workbook = new hssfworkbook ();//1.1, create a merged cell object cellrangeaddress  Cellrangeaddress = new cellrangeaddress (0,&NBSP;0,&NBSP;0,&NBSP;4);//1.2, header style Hssfcellstyle titlestyle =  createcellstyle (workbook, (short) 16);//1.3, column heading style hssfcellstyle columnheaderstyle = createcellstyle (workbook, (short) 13);//2, creating Worksheets Hssfsheet  sheet = workbook.createsheet ("user list");//2.1, load merged Cell object Sheet.addmergedregion (cellrangeaddress);// Set the default column width Sheet.setdefaultcolumnwidth (25),//3, create line//3.1, create Header header row, and set header header hssfrow titlerow =  Sheet.createrow (0); Hssfcell titlecell = titlerow.createcell (0);//Load cell style Titlecell.setcellstyle (TitleStyle); Titlecell.setcellvalue ("user list");//3.2, create Column header row, and set column header Hssfrow columnheaderrow = sheet.createrow (1) ; string[] titles = {"username", "account", "department", "Gender", "e-mail"};for (int i=0;i<titles.length;i++) { Hssfcell columnheadercell = columnheaderrow.createcell (i);// Load cell style Columnheadercell.setcellstyle (ColumnHeaderStyle); Columnheadercell.setcellvalue (Titles[i]);} 4, Operation cell; Write the user list to Excelif (Userlist != null) {for (Int&nbsP;j=0;j<userlist.size (); j + +) {Hssfrow row = sheet.createrow (j+2); Hssfcell datacell0 = row.createcell (0);d Atacell0.setcellvalue (Userlist.get (j). GetName ()); Hssfcell datacell1 = row.createcell (1);d Atacell1.setcellvalue (Userlist.get (j). GetAccount ()); Hssfcell datacell2 = row.createcell (2);d Atacell2.setcellvalue (Userlist.get (j). GetDept ()); Hssfcell datacell3 = row.createcell (3);d Atacell3.setcellvalue (Userlist.get (j). IsGender ()? " Male ":" female "); Hssfcell datacell4 = row.createcell (4);d Atacell4.setcellvalue (Userlist.get (j). GetEmail ());}} 5, Output workbook.write (outputstream); Workbook.close ();}  catch  (ioexception e)  {e.printstacktrace ();}} /** *  Create cell Styles  *  @param  workbook  workbooks  *  @param  fontSize  font size  *  @return  */private static hssfcellstyle createcellstyle (hssfworkbook  Workbook, short fontsize)  {hssfcellStyle style = workbook.createcellstyle (); 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 (font); return style;}}

2. Import user information from Excel

2.1, Useraction.java

Import user list public String importexcel () {//1, gets the file, and determines whether the Excel file if (Userexcel! = null && userexcelfilename.matches ( "^.+\\. (? i) ((xls) | (xlsx)) $ ")) {//2, Import userservice.importexcel (userexcel,userexcelfilename);} return "list";}

2.2, Userserivce.java

void Importexcel (File userexcel, String userexcelfilename);

2.3, Userserivceimpl.java

Public void importexcel (File userexcel, string userexcelfilename)  {try { Fileinputstream fileinputstream = new fileinputstream (userexcel); boolean is03Excel  = userexcelfilename.matches ("^.+\\. i) (XLS) $ "); Workbook workbook = is03excel ? new hssfworkbook (FileInputStream)  :  New xssfworkbook (FileInputStream); Sheet sheet = workbook.getsheetat (0); if (Sheet.getphysicalnumberofrows () >2) {User user  = null;for (Int k=2;k<sheet.getphysicalnumberofrows (); k++) {row row =  Sheet.getrow (k); User = new user ();//user name 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"));//Mobile phone number string mobile =  ""; Cell cell4 = row.getcell (4); Try {mobile = cell4.getstringcellvalue ();}  catch  (exception e)  {double dmobile = cell4.getnumericcellvalue (); mobile  = bigdecimal.valueof (Dmobile). toString ();} User.setmobile (mobile);//Mailbox Cell cell5 = row.getcell (5); User.setemail (Cell5.getstringcellvalue () );//Birthday Cell cell6 = row.getcell (6); if (Cell6.getdatecellvalue ()  != null) { User.setbirthday (Cell6.getdatecellvalue ());} The default user password is 123456user.setpassword ("123456");//The default user state is   valid user.setstate (user.user_state_valid);//5, saving Save ( user);} Workbook.close (); Fileinputstream.close ();}}  catch  (exception e)  {e.printstacktrace ();}}

SSH series: (11) User Management-excel Import, export