Je rencontre souvent la nécessité d'importer et d'exporter Excel au travail. J'ai un bref exemple de réponse à partager avec vous.
Ne dis pas beaucoup de non-sens,
1. Package de pot requis:
2. Code frontal:
ieport.jsp:
<% @ page import = "java.util.date"%> <% @ page linguisse = "java" contentType = "text / html; charset = utf-" pageencoding = "utf -"%> <! doctype html public "- // wc // dtd xhtml. transitional / en" "http://www.w.org/tr/xhtml/dtd/xhtml-transitional.dtd"> <html xmlns = "http://www.w.org//xhtml"> <éadfri /> <Title> Import / Export Page </ Title> <Script Type = "Text / JavaScript"> Fonction ExportFile () {Window.Location.href = "<% = request.getContextPath ()%> / export.go"; } </ script> </ head> <body> <form action = "import.go" metheth = "poster" EncType = "multipart / form-data"> Fichier: <input type = "file" name = "uploadfile" /> <br> </br> <input type = "soumide" value = "import" /> <entrée type = "bouton" value> "export" onclick = "exportfile ()") </html>Success.jsp:
<% @ Page Language = "Java" ContentType = "Text / Html; charSet = UTF-" Pageencoding = "UTF -"%> <% @ taglib Prefix = "C" URI = "http://java.sun.com/jsp/jstl/Core"%> <! DocType html public "- // wc // dtd xhtm. Transitional // en "" http://www.w.org/tr/xhtml/dtd/xhtml-transition.dtd "> <html xmlns =" http://www.w.org//xhtml "> <read> <méta http-equie charset = utf- "/> <tight> page de réussite </ title> <script type =" text / javascript "> // var secuserList = '$ {secuserList}'; // alert (SecUserList); </ script> </ head> <body> <c: if test = "$ {type == 'import'}"> <div> Importer avec succès! </div> <c: foreach items = "$ {SecUserList}" var = "SecUser"> <div> id: $ {SecUser.Userrid} | name: $ {secuser.username} | mot de passe: $ {secuser.userpassword} </v> </20 <div> Exporter avec succès! </div> </c: if> </ody> </html>3. Code d'arrière-plan:
contrôleur:
package com.Controller; Importer java.io.file; Importer java.util.list; import javax.annotation.resource; Importer javax.servlet.http.httpservletRequest; import javax.servlet.http.httpservletResponse; import org.springframework.sterreotype.Controller; import org.springframework.web.bind.annotation.requestmapping; import org.springframework.web.bind.annotation.requestParam; import org.springframework.web.multupar.MultiPartFile; import org.springframework.web.servlet.modelandView; import com.domain.secuser; Importer com.service.ieportService; @Controller public class ieportController {@Resource private ieportService ieportService; @RequestMapping ("/ Import") public ModelandView ImportFile (@RequestParam (value = "uploadFile") MultiartFile MFile, httpservletRequest request, httpservletResponse réponse) {String rootPath = request.getSession (). GetServletContext (). GetRealPath (file.separator); List <Secuser> SecUserList = ieportService.ImportFile (mFile, rootPath); ModelAndView mv = new ModelAndView (); mv.addObject ("type", "import"); mv.addObject ("SecUserList", SecUserList); mv.setViewName ("/ Success"); retour MV; } @RequestMapping ("/ Export") public ModelandView ExportFile (HttpServletResponse Response) {ieportService.ExportFile (réponse); ModelAndView mv = new ModelAndView (); mv.addObject ("type", "export"); mv.setViewName ("/ Success"); retour MV; }} service:
package com.service; Importer java.io.file; import java.io.fileInputStream; Importer java.io.inputStream; import java.io.outputStream; import java.net.urlencoder; import java.text.simpledateFormat; import java.util.arraylist; Importer java.util.date; Importer java.util.list; import javax.annotation.resource; import javax.servlet.http.httpservletResponse; 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.usermodel.cellStyle; import org.apache.poi.ss.usermodel.font; import org.apache.poi.xssf.usermodel.xssfcell; import org.apache.poi.xssf.usermodel.xssffont; import org.apache.poi.xssf.usermodel.xssfrow; import org.apache.poi.xssf.usermodel.xssfsheet; import org.apache.poi.xssf.usermodel.xssfworkbook; import org.springframework.sterreotype.service; import org.springframework.web.multupar.MultiPartFile; import com.dao.ieportdao; import com.domain.secuser; @Service public class ieportService {@Resource private ieportdao ieportdao; public List <SecUser> import String filename = mFile.getoriginalFileName (); String suffix = filename.substring (filename.lastIndexof (".") +, Filename.length ()); String ym = new SimpledateFormat ("Yyyy-mm"). Format (new Date ()); String filepath = "uploadfile /" + ym + filename; try {file file = new File (rootpath + filepath); if (file.exists ()) {file.delete (); file.mkDirs (); } else {file.mkDirs (); } mfile.transferto (fichier); if ("xls" .equals (suffixe) || "xls" .equals (suffixe)) {secuserList = importXls (fichier); ieportdao.importfile (SecUserList); } else if ("xlsx" .equals (suffixe) || "xlsx" .equals (suffixe)) {secuserList = importXlsx (file); ieportdao.importfile (SecUserList); }} catch (exception e) {e.printStackTrace (); } return secuserList; } Liste privée <SecUser> importXls (fichier de fichier) {list <Secuser> secuserList = new ArrayList <SecUser> (); InputStream est = null; Hssfworkbook hworkbook = null; try {is = new FileInputStream (fichier); hworkbook = new hssfworkbook (IS); Hssfsheet hsheet = hworkbook.getSheetAt (); if (null! = hSheet) {for (int i =; i <hsheet.getPhysicalNumberofrows (); i ++) {SecUser su = new SecUser (); Hssfrow hrow = hsheet.getRow (i); su.setUsername (hrow.getCell (). ToString ()); Su.SetUserPassword (hrow.getCell (). ToString ()); SecUserList.Add (Su); }}} catch (exception e) {e.printStackTrace (); } enfin {if (null! = is) {try {is.close (); } catch (exception e) {e.printStackTrace (); }} if (null! = hworkbook) {try {hworkbook.close (); } catch (exception e) {e.printStackTrace (); }}} return secuserList; } Liste privée <SecUser> ImportXlsx (fichier fichier) {list <Secuser> SecUserList = new ArrayList <SecUser> (); InputStream est = null; Xssfworkbook xworkbook = null; try {is = new FileInputStream (fichier); xworkBook = new XSSFWorkBook (IS); Xssfsheet xsheet = xworkbook.getSheetAt (); if (null! = xsheet) {for (int i =; i <xsheet.getPhysicalNumberOfrows (); i ++) {SecUser su = new SecUser (); Xssfrow xrow = xsheet.getRow (i); su.setUsername (xRow.getCell (). ToString ()); Su.SetUserPassword (xRow.getCell (). ToString ()); SecUserList.Add (Su); }} catch (exception e) {e.printStackTrace (); } enfin {if (null! = is) {try {is.close (); } catch (exception e) {e.printStackTrace (); }} if (null! = xworkbook) {try {xworkbook.close (); } catch (exception e) {e.printStackTrace (); }}} return secuserList; } public void ExportFile (HttpServletResponse Response) {SimpledateFormat df = new SimpledateFormat ("yyyymmdd"); OutputStream os = null; Xssfworkbook xworkbook = null; try {String filename = "user" + df.format (new Date ()) + ".xlsx"; OS = Response.getOutputStream (); réponse.RESET (); Response.SetHeader ("Content-Disposition", "Pixe; FileName =" + urlencoder.encode (FileName, "UTF-")); Response.SetContentType ("Application / Octet-Streem"); xworkBook = new xssfworkbook (); Xssfsheet xsheet = xworkbook.createSheet ("userList"); // Définir l'en-tête de la page SetSheetHeader (xworkBook, xsheet); // Définir le contenu de la page SetSheetContent (xworkBook, xsheet); xworkBook.Write (OS); } catch (exception e) {e.printStackTrace (); } enfin {if (null! = os) {try {os.close (); } catch (exception e) {e.printStackTrace (); }} if (null! = xworkbook) {try {xworkbook.close (); } catch (exception e) {e.printStackTrace (); }}}}} / ** * set En-tête de page de feuille * @param xworkbook * @param xsheet * / private void SetSheetheader (xssfworkbook xworkBook, xssfsheet xsheet) {xsheet.setColumnwidth (, *); xsheet.setColumnWidth (, *); xsheet.setColumnWidth (, *); CellStyle cs = xworkBook.CreateCellStyle (); // Définit les Cs.Setalignment horizontaux et verticaux (CellStyle.Align_Center); CS.SetVerticalAlignment (CellStyle.vertical_Center); // Set Font Font HeaderFont = xworkBook.createFont (); headerFont.setFontheightInpoints ((short)); headerfont.setboldweight (xssffont.boldweight_bold); headerFont.setFontName ("安体"); cs.setfont (headerFont); cs.setwraptext (true); // est-il possible d'envelopper automatiquement la ligne xssfrow xrow = xsheet.createrow (); XssfCell xcell = xRow.CreateCell (); xcell.setCellStyle (CS); xcell.setCellValue ("ID utilisateur"); XssfCell xcell = xRow.CreateCell (); xcell.setCellStyle (CS); xcell.setCellValue ("Nom d'utilisateur"); XssfCell xcell = xRow.CreateCell (); xcell.setCellStyle (CS); xcell.setCellValue ("mot de passe"); } / ** * SET Page de feuille Contenu * @param xworkbook * @param xsheet * / private void SetSheetContent (xssfworkbook xworkBook, xssfsheet xsheet) {list <Secuser> secuserList = ieportdao.getSecuserList (); CellStyle cs = xworkBook.CreateCellStyle (); cs.setWrapText (true); if (null! = secuserList && secuserList.size ()>) {for (int i =; i <secreserList.size (); i ++) {xssfrow xrow = xsheet.createrow (i +); SecUser SecUser = SecUserList.get (i); pour (int j =; j <; j ++) {xssfcell xcell = xrow.createCell (j); xcell.setCellStyle (CS); commutateur (j) {case: xcell.setCellValue (secreser.getUserId ()); casser; Cas: xcell.setCellValue (SecUser.GetUserName ()); casser; Cas: xcell.setCellValue (SecUser.GetUserPassword ()); casser; par défaut: pause; }}}}}}Dao:
package com.dao; import java.sql.resultSet; import java.sql.sqlexception; import java.util.arraylist; Importer java.util.list; import javax.annotation.resource; import org.springframework.sterreotype.repository; import com.domain.secuser; import org.springframework.jdbc.core.jdbcTemplate; import org.springframework.jdbc.core.rowmapper; @Repository public class ieportdao {@Resource private jdbcTemplate jdbCTemplate; RowMapper privé <SecUser> Surowmapper = null; ieportdao privé () {Surowmapper = new RowMapper <SecUser> () {@Override public Secuser MapRow (résultat Rs, int index) lève SQLEXception {SecUser Secuser = new Secuser (); SecUser.SetUserId (Rs.getString ("user_id")); SecUser.SetUserName (Rs.getString ("user_name")); SecUser.SetUserPassword (Rs.getString ("user_password")); Retour SecUser; }}; } public void import List <object []> paramslist = new ArrayList <objet []> (); for (int i =; i <SecuserList.size (); i ++) {SecUser SecUser = SecUserList.get (i); Objet [] params = nouvel objet [] {secreser.getUsername (), secreser.getUserPassword ()}; paramslist.add (params); } jdbctemplate.batchupdate (sql, paramslist); } catch (exception e) {e.printStackTrace (); }} public List <SecUser> getSecUserList () {list <Secuser> Sulist = new ArrayList <SecUser> (); StringBuffer sb = new StringBuffer (); SB.APPEND ("SELECT su.user_id, su.user_name, su.user_password de sec_user su"); essayez {sulist = jdbcTemplate.Query (sb.toString (), Surowmapper); } catch (exception e) {e.printStackTrace (); } Retour Sulist; }}domaine:
package com.domain; classe publique SecUser {String userId; // Nom d'utilisateur de chaîne d'ID utilisateur; // Username String userpassword; // Mot de passe public String getUserId () {return userId; } public void SetUserId (String userId) {this.userid = userId; } public String getUserPassword () {return userpassword; } public void SetUserPassword (String userpassword) {this.userPassword = userPassword; } public String getUserName () {return username; } public void setUsername (String username) {this.userName = username; }}4. Fichier de configuration:
<? xml version = "." Encoding = "utf -"?> <web-app xmlns: xsi = "http://www.w.org//xmlschema-instance" xmlns = "http://java.sun.com/xml/ns/javaee" xsi: schemalocation = "http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app__.xsd" id = "webapp_id" version = ".". <Display-Name> SpringsPringmvcpoi </ Display-Name> <Welcome-File-List> <Lelaen-File> ieport.jsp </venke-file> </ welcome-file-list> <! - Spécifiez le nom et l'emplacement du fichier de configuration de printemps -> <AXTERT-PARAM> <AMAM-NAME> contextConFigLocation </ param-name> <param-Value> ClassPath: DataSource-Context.xml </ Param-Value> </ Context-Param> <! - Configurez Lisonner -> <ousiner> <auditeur-CLASS> ORG.SPRINGFRAMEWROWN.WEB.CONTEXT.ContextLoaderListener </ auditeur-CLASSE> </VERCIER> <! <Serplet-Name> Dispatcherservlet </vrlett-name> <servlet-class> org.springframework.web.servlet.dispatcherservlet </ servlet-class> <! - Configurer l'emplacement du fichier de configuration de Springmvc -> <IniT-PARAM> <AMAM-NAME> ContextConfiglocation </ param-name> <Am param-Value> CLASSPATH: Spring-Mvc.xml </Ar param-Value> </Init-Param> </ Servlet> <Serplet-Mapping> <Servlet-Name> Dispatcherservlet </vrlet-name> <Url-Pattern> *. GO </ url-platern> </vilter> <filter-name> CaracterEncodingFilter </ Filter-Name> <Filter-Class> org.springframework.web.filter.characterencodingfilter </ filter-Class> <Init-Param> <Param-Name> Encoding </ Param-name> <param-valeur> utf - </ param-value> <Filter-Name> CaracterEncodingFilter </ Filter-Name> <URL-Pattern> / * </url-Pattern> </ Filter-Mapping> </ Web-App>
<? xml version = "." Encoding = "UTF -"?> <Beans xmlns = "http://www.springframework.org/schema/beans" xmlns: xsi = "http://www.w.org//xmlschema-instance" xmlns: p = "http://www.springframework.org/schema/p" xmlns: context = "http://www.springframework.org/schema/aop" xmlns: tx = "http://www.springframeworkwork.org/schema/tx" xmlns: mvc = "http://www.springframework.org/schema/mvc" xmlns: util = "http://www.springframework.org/schema/util" xsi: schemalation = "http://www.springframeworkwork.orga/bans//www. http://www.springframework.org/schema/beans/spring-beans-..xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-contex http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-..xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-..xsd "> <! - Configurez les packages de numérisation automatiques -> <context: composant-scan basage-package =" com. value = "/ web-inf / vues /"> </ propriété> <propriété name = "suffixe" value = ". jsp"> </ propriété> </ank> <! - Prise en charge du téléchargement de fichiers -> <bean id = "multipartResolver" /> </ beans>
<? xml version = "." Encoding = "UTF -"?> <Beans xmlns = "http://www.springframework.org/schema/beans" xmlns: xsi = "http://www.w.org//xmlschema-instance" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-..xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-..XSD "> <
<? xml version = "." Encoding = "UTF -"?> <Beans xmlns = "http://www.springframework.org/schema/beans" xmlns: xsi = "http://www.w.org//xmlschema-instance" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd "> <! - Lire le fichier de configuration JDBC -> <contexte: propriété-localisation de l'emplacement =" ClassPath: JDBCC. id = "dataSource" destrement-méthod = "close"> <propriété name = "user" value = "$ {jdbc.user}"> </ propriété> <propriété name = "passway" value = "$ {jdbc.password}"> </ propriété> <propriété name = driverclass "value =" jdbc.DiverClass} "> </ property> <woftaméli Value = "$ {jdbc.jdbcurl}"> </ propriété> <! - Lorsque les connexions dans le pool de connexions sont utilisées, le nombre de nouvelles connexions créées en même temps par CP -> <Property Name = "AcquireIncrement" Value = ""> </ Property Value = ""> </ propriété> <propriété name = "maxpoolSize" value = ""> </ propriété> <propriété name = "minpoolSize" value = ""> </ propriété> <propriété name = "maxconnectionage" value = ""> </ propriété = "MAXIDLEMEEXCESSCONCTION ="> </ propriété Name = "MAXIDLEMEEXCESSCONCTIONS" <propriété name = "TestConnectionOnCheckout" value = "false"> </ propriété> <propriété name = "TestConnectionOnCheckin" value = "false"> </ propriété> <! - Vérifiez les connexions IDLE dans le pool de connexions chaque seconde-> <propriété name = "acquériratTT. name = "acquerretryDelay" value = ""> </ propriété> <propriété name = "privRedTestQuery" value = "select from Dual"> </ propriété> </ bean> <! - Configurez JDBC Template JDBCTEmplate -> <bean Id = "JDBCTETTEMPLATE"> <Constructor-arg Ref = "DataSource"> </ Construct-Arg>jdbc.DriverClass = com.mysql.jdbc.driver jdbc.jdbcurl = jdbc: mysql: // localhost: / mydb jdbc.user = myuser jdbc.password = myuser
5. Structure du répertoire:
6. Démonstration des résultats
Importer:
Exporter:
PS:
1. Ce novice, comme elle ne savait pas encore comment ajouter des pièces jointes, elle publie tout le code et ajoute la structure du répertoire. Elle apprendra à ajouter des pièces jointes à l'avenir, puis à les modifier.
Ce qui précède est tout le contenu de cet article. J'espère que cela sera utile à l'apprentissage de tous et j'espère que tout le monde soutiendra davantage Wulin.com.