Ich begegne mich oft auf die Notwendigkeit, Excel bei der Arbeit zu importieren und zu exportieren. Ich habe ein kurzes Antwortbeispiel, das ich mit Ihnen teilen kann.
Sag nicht viel Unsinn,
1. Erforderliches JAR -Paket:
2. Front-End-Code:
ieport.jsp:
<%@page import = "java.util.date"%> <%@page Language = "java" contentType = "text/html; charset = utf-" pageCoding = "utf-"%> <! docType html public " "http://www.w.org/tr/xhtml/dtd/xhtml-transitional.dtd"> <html xmlns = "http://www.w.org//xhtml"> <Head> <meta http- tequiv = content-ttp- "content-ype" content = "content =" text/http- /> <title> Seite importieren/exportieren </title> <skript type = "text/javaScript"> Funktion exportFile () {window.location.href = "<%= request.getContextPath ()%>/export.go"; } </script> </head> <body> <form action = "import.go" methode = "post" engType = "MultiPart/Form-Data"> Datei: <Eingabe type = "Datei" name = "uploadFile"/> </br> </BR> <Eingabe type = "subg." value = "/> </> body". </html>success.jsp:
<%@ page Language = "java" contentType = "text/html; charset = utf-" pageCoding = "utf-"%> <%@ taglib prefix = "c" uri = "http://java.sun.com/jsp/jstl/core"%> <! Transitional // en "" http://www.w.org/tr/xhtml/dtd/xhtml-transitional.dtd "> <html xmlns =" http://www.w.org/xhtml "content- charset = utf- "/> <title> Erfolgsseite </title> <script type =" text/javaScript "> // var secuserlist = '$ {SecUserList}'; // alarm (SecUserList); </script> </head> <body> <c: if test = "$ {type == 'import'}"> <div> Import erfolgreich! </div> <c:forEach items="${secUserList}" var="secUser"> <div>Id: ${secUser.userId}|Name: ${secUser.userName}|Password: ${secUser.userPassword}</div> </c:forEach> </c:if> <c:if test="${type == 'export'}"> <Div> erfolgreich exportieren! </div> </c: if> </body> </html>3. Hintergrundcode:
Regler:
Paket com.controller; Import Java.io.file; importieren java.util.list; import Javax.annotation.Resource; importieren javax.servlet.http.httpServletRequest; importieren javax.servlet.http.httpServletResponse; import org.springframework.stereotype.controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.requestParam; import org.springframework.web.multipart.multipartFile; import org.springframework.web.servlet.ModelandView; com.domain.secuser importieren; import com.service.ieportservice; @Controller Public Class IeportController {@Resource private ieportService IeportService; @RequestMapping ("//import") public ModelAndView ImportFile (@RequestParam (value = "uploadFile") MultiPartFile MFile, httpServletRequest -Anforderung, httpServletRespect -Antwort) {String RootPh = Request.getSession (). Liste <Secuser> SecuserList = IeportService.importFile (MFILE, WOOTPADE); ModelAndView mv = new ModelAndView (); mv.addObject ("Typ", "Import"); Mv.AddObject ("SecuserList", SecuserList); mv.setViewName ("/Erfolg"); MV zurückgeben; } @RequestMapping ("/export") public modelAndView exportFile (httpServletResponse -Antwort) {iePortService.exportFile (Antwort); ModelAndView mv = new ModelAndView (); Mv.AddObject ("Typ", "Export"); mv.setViewName ("/Erfolg"); MV zurückgeben; }} Service:
Paket com.service; Import Java.io.file; import Java.io.FileInputStream; importieren java.io.inputstream; importieren java.io.outputstream; importieren java.net.urlencoder; Java.Text.SimpledateFormat importieren; Import Java.util.ArrayList; import Java.util.date; importieren java.util.list; import Javax.annotation.Resource; importieren 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.s.usermodel.cellstyle; import org.apache.poi.s.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.stereotype.service; import org.springframework.web.multipart.multipartFile; import com.dao.ieportdao; com.domain.secuser importieren; @Service Public Class IeportService {@resource private ieportdao ieportdao; öffentliche Liste <secuser> ImportFile (MultiPartFile Mfile, String RootPath) {List <Secuser> SecUserList = New ArrayList <secuser> (); String Dateiname = MFILE.GetOriginalFileName (); String -Suffix = Dateiname.substring (Dateiname.lastIndexof (".") +, Dateiname.length ()); String ym = new SimpledateFormat ("yyyy-mm"). Format (neues Datum ()); String filepath = "UploadFile/" + ym + Dateiname; try {file file = new Datei (rootPath + filepath); if (file.exists ()) {file.delete (); Datei.mkdirs (); } else {file.mkdirs (); } mFile.transferto (Datei); if ("xls" .Equals (Suffix) || "xls" .Equals (Suffix)) {SecUserList = importxls (Datei); ieportdao.importFile (SecUserList); } else if ("xlsx" .Equals (Suffix) || "xlsx" .Equals (Suffix)) {SecUserList = importxlsx (Datei); ieportdao.importFile (SecUserList); }} catch (Ausnahme e) {e.printstacktrace (); } return SecUserList; } private list <secuser> importxls (Dateidatei) {list <secuser> Secuserlist = new ArrayList <secuser> (); InputStream ist = null; HSSFWORKBOOK HWWORKBOOK = NULL; try {is = new FileInputStream (Datei); hworkbook = new HSSFWorkbook (IS); Hssfsheet hsheet = hworkbook.getsheetat (); if (null! Hssfrow hrow = hsheet.getrow (i); Su.setUserName (hrow.getCell (). toString ()); Su.setUserPassword (hrow.getCell (). toString ()); SecuserList.Add (SU); }}} catch (exception e) {e.printstacktrace (); } endlich {if (null! = is) {try {is.close (); } catch (Ausnahme e) {e.printstacktrace (); }} if (null! = hworkbook) {try {hworkbook.close (); } catch (Ausnahme e) {e.printstacktrace (); }}} return SecUserList; } private Liste <secuser> importxlsx (Dateidatei) {list <secuser> SecUserList = new ArrayList <secuser> (); InputStream ist = null; XSSFWorkbook xworkbook = null; try {is = new FileInputStream (Datei); xworkbook = new XSSFWorkbook (IS); Xssfsheet xsheet = xworkbook.getsheetat (); if (null! XSSFROW XROW = XSheet.getRow (i); Su.setUserName (xrow.getCell (). toString ()); Su.setUserPassword (xrow.getCell (). toString ()); SecuserList.Add (SU); }} catch (Ausnahme e) {e.printstacktrace (); } endlich {if (null! = is) {try {is.close (); } catch (Ausnahme e) {e.printstacktrace (); }} if (null! = xworkbook) {try {xworkbook.close (); } catch (Ausnahme e) {e.printstacktrace (); }}} return SecUserList; } public void Exportfile (httpServletResponse -Antwort) {SimpleDateFormat df = new SimpledateFormat ("yyyymmdd"); OutputStream os = null; XSSFWorkbook xworkbook = null; try {String Dateiname = "user" + df.format (new Date ()) + ".xlsx"; os = response.getOutputStream (); response.reset (); response.setheader ("Inhaltsdisposition", "Anhang; Dateiname =" + urlencoder.encode (Dateiname, "utf-")); response.setContentType ("Anwendung/Oktettstreem"); xworkbook = new XSSFWorkbook (); Xssfsheet xsheet = xworkbook.createSheet ("userList"); // Blattseite Header SetSheeter (xworkbook, xsheet); // Set -Set -Inhalts -SetsheetContent (xworkbook, xsheet) einstellen; xworkbook.write (OS); } catch (Ausnahme e) {e.printstacktrace (); } endlich {if (null! = os) {try {os.close (); } catch (Ausnahme e) {e.printstacktrace (); }} if (null! = xworkbook) {try {xworkbook.close (); } catch (Ausnahme e) {e.printstacktrace (); }}}}} / ** * Set Sheet Side Header * @param xworkbook * @param xsheet * / private void setsheader (xssfworkbook xworkbook, xssfsheet xsheet) {xsheet.setcolumnwidth (, *); XSheet.SetColumnWidth (, *); XSheet.SetColumnWidth (, *); CellStyle cs = xworkbook.CreateCellStyle (); // horizontale und vertikale Zentrierung von Cs.Setalignment (CellStyle.Align_Center) festlegen; cs.SetverticalInignment (CellStyle.VERTICAL_CENTER); // Schriftart font Headerfont = xworkbook.CreateFont (); Headerfont.SetFontHeightIntinPoints ((kurz)); Headerfont.SetBoldWeight (xssffont.boldwight_bold); HeaderFont.SetFontName ("安体"); cs.setFont (Headerfont); cs.setWrapText (true); // Ist es möglich, die Zeile automatisch xssfrow xrow = xsheet.createrow () zu wickeln; Xssfcell xcell = xrow.createcell (); xcell.setCellStyle (CS); xcell.setCellValue ("Benutzer -ID"); Xssfcell xcell = xrow.createcell (); xcell.setCellStyle (CS); xcell.setCellValue ("Benutzername"); Xssfcell xcell = xrow.createcell (); xcell.setCellStyle (CS); xcell.setCellValue ("Passwort"); } / ** * Set Sheet Side Inhalt * @param xworkbook * @param xsheet * / private void setsetsheetContent (xssfWorkbook xworkbook, xssfsheet xsheet) {list <secuser> secuserlist = iefordao.getSecuserList (); CellStyle cs = xworkbook.CreateCellStyle (); cs.setWrapText (true); if (null! Secuser Secuser = SecUserList.get (i); für (int j =; j <; j ++) {xssfcell xcell = xrow.createcell (j); xcell.setCellStyle (CS); Switch (j) {case: xcell.setcellValue (Secuser.getUerId ()); brechen; Fall: xcell.setcellValue (Secuser.getUserName ()); brechen; Fall: xcell.setcellValue (Secuser.getUserPassword ()); brechen; Standard: Break; }}}}}}Dao:
Paket com.dao; importieren java.sql.resultset; importieren java.sql.sqlexception; Import Java.util.ArrayList; importieren java.util.list; import Javax.annotation.Resource; import org.springframework.stereotype.repository; com.domain.secuser importieren; import org.springframework.jdbc.core.jdbctemplate; import org.springframework.jdbc.core.rowmapper; @Repository public class ieportdao {@resource private jdbctemplate jdbctemplate; private rowMapper <secuser> surowMapper = null; private ieportdao () {surowMapper = new RowMapper <secuser> () {@Override public Secuser Maprow (Ergebnisset Rs, int Index) löst SQLEXception {Secuser Secuser = new SecUser () aus; Secuser.SetUserid (rs.getString ("user_id")); secuser.setusername (rs.getString ("user_name")); Secuser.SetUserPassword (rs.getString ("user_password")); Return Secuser; }}; } public void ImportFile (Liste <Secuser> SecUserList) {try {string sql = "In Sec_User -Werte einfügen (uUid (),?,?)"; List <Object []> paramSlist = new ArrayList <Object []> (); für (int i =; i <SecUserList.size (); i ++) {Secuser Secuser = SecUserList.get (i); Object [] params = new Object [] {Secuser.getUnername (), Secuser.getUserPassword ()}; paramSlist.add (params); } jdbctemplate.batchUpDate (SQL, ParamsList); } catch (Ausnahme e) {e.printstacktrace (); }} publiclist <secuser> getSecuserList () {list <secuser> sulist = new ArrayList <secuser> (); StringBuffer sb = new StringBuffer (); SB.Append ("SELECT SU.USER_ID, SU.USER_NAME, SU.USER_PASSWORD VON SEC_USER SU"); try {sulist = jdbctemplate.Query (sb.toString (), surowMapper); } catch (Ausnahme e) {e.printstacktrace (); } Return Sulist; }}Domain:
Paket com.domain; öffentliche Klasse Secuser {String userId; // Benutzer -ID -String -Benutzername; // Benutzername String -Benutzerpassword; // Passwort public String getUserid () {userID zurückgeben; } public void setUerId (String userID) {this.userId = userId; } public String getUserPassword () {Benutzerpassword zurückgeben; } public void setUserPassword (String -Benutzerpassword) {this.userpassword = userpassword; } public String getUnername () {return userername; } public void setUnername (String -Benutzername) {this.username = userername; }}4. Konfigurationsdatei:
<? xml Version = ".". coding = "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- =" id ". <display-name>SpringSpringmvcPoi</display-name> <welcome-file-list> <welcome-file>ieport.jsp</welcome-file> </welcome-file-list> <!-- Specify the name and location of the Spring configuration file --> <context-param> <param-name>contextConfigLocation</param-name> <param-value> classpath:application-context.xml CLASSPATH: DataSource-context.xml </param-value> </context-param> <!-Konfigurieren Sie Listener-> <Hörer> <Hörer-Klasse> org.springFramework.Web.Context.ContextLoaderListener </Listener-Class> </Hörer> <! <servlet-name> dispatcherservlet </servlet-name> <servlet-class> org.springframework.web.servlet.DispatcherServlet </Servlet-Class> <!-Konfigurieren Sie die Konfigurationsdateisposition von SpringMVC-> <init-param <param-value> classPath: spring-mvc.xml </param-value> </init-param> </servlet> <Servlet-Mapping> <Servlet-name> DispatherServlet </Servlet-name> <Url-Patter>*. <filter-name>CharacterEncodingFilter</filter-name> <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class> <init-param> <param-name>encoding</param-name> <param-value>utf-</param-value> </init-param> </filter> <filter-mapping> <Filter-name> charakteritätsgerichtet </filter-name> <url-pattern>/*</url-pattern> </filter-mapping> </web-App>
<? xml Version = ".". coding = "utf-"?> <beans xmlns = "http://www.springframework.org/schema/beans" xmlns: xsi = "http://www.w.org//xmlschemasinstance" xmlns: p = "http://www.springframework.org/schema/p" xmlns: context = "http://www.springframework.org/schema/aop" xmlns: tx = "http://www.spingframeWramework xmlns: mvc = "http://www.springframework.org/schema/mvc" xmlns: util = "http://www.spingframework.org/schema/util" xsi: schemalocation = "http./wwwwwwwwww./wwwwww./wwwww./wwww./wwww./www./wwww.Schema http://www.springframework.org/schema/beans/spring-beans-..xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/sping/sping/sping-conext-..xsd 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 "> <!-Automatische Scan-Pakete konfigurieren-> <context: component-scan base-package =" com.controller "> </context: component-scan> <!-Konfigurieren Sie SpringMvc's View-Auflösungs-Auflösung-<Beans. value = "/web-inf/views/"> </property> <Eigenschaft name = "suffix" value = ". jsp"> </property> </bean> <!-Unterstützung des Hochladens von Dateien-> <bean id = "multipartresolver"/> </beans>
<? xml Version = ".". coding = "utf-"?> <beans xmlns = "http://www.springframework.org/schema/beans" xmlns: xsi = "http://www.w.org//xmlschemasinstance" xmlns: context = "http://www.springframework.org/schema/context" xsi: schemalocation = "http://www.spingframework.org/schema/beans http:/www.springframe.org/schema/beans- http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-..xsd"> <context:component-scan base-package="com"></context:component-scan> </beans>
<? xml Version = ".". coding = "utf-"?> <beans xmlns = "http://www.springframework.org/schema/beans" xmlns: xsi = "http://www.w.org//xmlschemasinstance" xmlns: context = "http://www.springframework.org/schema/context" xsi: Schemalocation = "http://www.spingframework.org/schema/beans http://www.springramework.org/schema/beans/ http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd "> <!-Lesen Sie JDBC-Konfigurationsdateien-> <context: Property-Placeholder-Datei: JDBC.Propties: Property-Placeholder-Standort =" ClassPhode: Jdbc.Propties: JDBC.Propties: JDBC.Propties: Property-Placeholder-Standort = "ClassPlite". Quelle-> <bean id = "dataSource" Destroy-method = "close"> <Eigenschaft name = "user" value = "$ {jdbc.user}"> </property> <Eigenschaft name = "password" value = "$ {jdbc.password}" name = "jdbcurl" value = "$ {jdbc.jdbcurl}"> </property> <! name="initialPoolSize" value=""></property> <property name="maxPoolSize" value=""></property> <property name="minPoolSize" value=""></property> <property name="maxConnectionAge" value=""></property> <property name="maxIdleTime" value=""></property> <property name="maxIdleTimeExcessConnections" value=""></property> <property name="testConnectionOnCheckout" value="false"></property> <property name="testConnectionOnCheckin" value="false"></property> <!-- Check idle connections in the connection pool every second-> <property name="idleConnectionTestPeriod" value=""></property> <property name="acquireRetryAttempts" value = ""> </Eigenschaft> <Eigenschaft name = "accounteretryDelay" value = ""> </property> <Eigenschaft name = "bevorzugungstestQuery" value = "aus Dual"> </property> </bean> <!-Konfigurieren Sie JDBC-Vorlagen jdbctemplate-> <bean id = "jdbcTemplate"> <Constructor-<Constructor-<Constructor-ARGOR-ARGOR-ARGOR-ARGOR-ARGOR-ARGOR-ARGUR-ARGUR-ARGUR-ARGELLE. " ref = "dataSource"> </constructor-arg> </bean> </beans>jdbc.driverclass = com.mysql.jdbc.driver jdbc.jdbcurl = jdbc: mysql: // localhost:/mydb jdbc.user = myuser jdbc.password = myuser
5. Verzeichnisstruktur:
6. Ergebnisse Demonstration
Import:
Export:
PS:
1. Dieser Anfänger, da sie noch nicht gewusst hat, wie man Anhänge hinzufügt, veröffentlicht sie den gesamten Code und fügt die Verzeichnisstruktur hinzu. Sie wird lernen, wie man in Zukunft Anhänge hinzufügt und sie dann modifiziert.
Das obige ist der gesamte Inhalt dieses Artikels. Ich hoffe, es wird für das Lernen aller hilfreich sein und ich hoffe, jeder wird Wulin.com mehr unterstützen.