나는 종종 직장에서 Excel을 수입하고 수출해야 할 필요성을 겪습니다. 나는 당신과 공유 할 간단한 답변 예제가 있습니다.
넌센스를 많이 말하지 마세요.
1. 필수 JAR 패키지 :
2. 프론트 엔드 코드 :
ieport.jsp :
<%@page import = "java.util.date"%> <%@page language = "java"contenttype = "text/html; charset = utf-"pageencoding = "utf-"%> <! doctype html public "-// wc // dtd xhtml/en" "http://www.w.org/tr/xhtml/dtd/xhtml-transitional.dtd"> <html xmlns = "http://www.w.org//xhtml"> <head> <http-tequiv = "content-type"pext/html; <title> 가져 오기/내보내기 페이지 </title> <script type = "text/javaScript"> function exportFile () {wind }. </body> </html>성공 .jsp :
<%@ page language = "java"contmenttype = "text/html; charset = utf-"pageencoding = "utf-"%> <%@ taglib prefix = "c"uri = "http://java.sun.com/jsp/jstl/core"%> <! ductype html public "-// wc // dtd xhtml. 전환 // en ""http://www.w.org/tr/xhtml/dtd/xhtml-transitional.dtd "> <html xmlns ="http://www.w.org//xhtml "> charset = utf- "/> <title> 성공 페이지 </title> <script type ="text/javaScript "> // var secuserList = '$ {secuserList}'; // Alert (SecuserList); </script> </head> <body> <c : if test = "$ {type == 'import'}"> <div> 성공적으로 가져옵니다! </div> <c : foreach items = "$ {secuserList}"var = "secuser"> <div> id : $ {secuser.userid} | name : $ {secuser.username} | password : $ {secuser.userpassword} </div> </c : foreach> </c : if test = "$ {type = {secuser.userpassword}} </div> </c : <c : if test = <c : if test =" <div> 성공적으로 수출! </div> </c : if> </body> </html>3. 배경 코드 :
제어 장치:
패키지 com.controller; import java.io.file; Java.util.list 가져 오기; import javax.annotation.resource; import javax.servlet.http.httpservletrequest; import javax.servlet.http.httpservletresponse; org.springframework.stereotyp.controller import; org.springframework.web.bind.annotation.requestmapping; org.springframework.web.bind.annotation.requestparam; org.springframework.web.multipart.multipartfile; org.springframework.web.servlet.modelandview import; com.domain.secuser import; com.service.ieportservice import; @controller public class IEPORTCONTROLLER {@Resource private ieportService ieportService; @requestmapping ( "/import") public modelandview importfile (@requestparam (value = "uploadfile") multipartfile mfile, httpservletrequest request, httpservletresponse 응답) {string rootpath = requestsession (). getervletContext (). 목록 <Secuser> SecuserList = IEPORTSERVICE.IMPORTFILE (MFILE, ROOTPATH); ModelAndView MV = 새로운 ModelAndView (); mv.addoBject ( "type", "import"); mv.addoBject ( "secuserList", secuserList); mv.setViewName ( "/success"); 리턴 MV; } @RequestMapping ( "/Export") public model 및 exportFile (httpservletResponse 응답) {ieportService.exportFile (응답); ModelAndView MV = 새로운 ModelAndView (); mv.addoBject ( "유형", "내보내기"); mv.setViewName ( "/success"); 리턴 MV; }} 서비스:
패키지 com.service; import java.io.file; import java.io.fileInputStream; import java.io.inputstream; import java.io.outputStream; import java.net.urlencoder; import java.text.simpledateformat; java.util.arraylist 가져 오기; import java.util.date; 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; org.springframework.stereotyp.service; org.springframework.web.multipart.multipartfile; import com.dao.ieportdao; com.domain.secuser import; @Service public class IEPORTSERVICE {@Resource private ieportdao ieportdao; 공개 목록 <Secuser> importFile (MultipartFile MFile, String RootPath) {List <Secuser> SecuserList = New ArrayList <Secuser> (); 문자열 filename = mfile.getoriginalFilename (); 문자열 접미사 = filename.substring (filename.lastindexof ( ".") +, filename.length ()); 문자열 ym = new simpledateformat ( "yyyy-mm"). 형식 (new date ()); 문자열 filepath = "uploadfile/" + ym + filename; try {file file = 새 파일 (rootpath + filepath); if (file.exists ()) {file.delete (); file.mkdirs (); } else {file.mkdirs (); } mfile.transferto (파일); if ( "xls".equals (접미사) || "xls".equals (wiffix)) {secuserList = importXls (file); ieportdao.importfile (SecuserList); } else if ( "xlsx".equals (접미사) || "xlsx".equals (wiffix)) {secuserList = importxlsx (file); ieportdao.importfile (SecuserList); }} catch (예외 e) {e.printstacktrace (); } return secuserList; } 개인 목록 <Secuser> importXls (파일 파일) {list <Secuser> secuserList = new ArrayList <Secuser> (); 입력 스트림은 = null입니다. hssfworkbook hworkbook = null; try {is = new FileInputStream (파일); hworkbook = 새로운 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 (예외 e) {e.printstacktrace (); } 마침내 {if (null! = is) {try {is.close (); } catch (예외 e) {e.printstacktrace (); }} if (null! = hworkbook) {try {hworkbook.close (); } catch (예외 e) {e.printstacktrace (); }}} return secuserList; } 개인 목록 <Secuser> importxlsx (파일 파일) {list <Secuser> secuserList = new ArrayList <Secuser> (); 입력 스트림은 = null입니다. xssfworkbook xworkbook = null; try {is = new FileInputStream (파일); xworkbook = 새로운 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 (예외 e) {e.printstacktrace (); } 마침내 {if (null! = is) {try {is.close (); } catch (예외 e) {e.printstacktrace (); }} if (null! = xworkbook) {try {xworkbook.close (); } catch (예외 e) {e.printstacktrace (); }}} return secuserList; } public void exportFile (httpservletResponse 응답) {simpledateformat df = new SimpledateFormat ( "yyyymmdd"); OutputStream os = null; xssfworkbook xworkbook = null; {string filename = "user" + df.format (new date ()) + ".xlsx"; os = response.getoutputStream (); response.reset (); response.setHeader ( "Content-Disposition", "첨부 파일; filename =" + urlencoder.encode (filename, "utf-")); Response.setContentType ( "Application/Octet-streem"); xworkbook = 새로운 xssfworkbook (); xssfsheet xsheet = xworkbook.createsheet ( "userList"); // 시트 페이지 헤더 세트 세트 세트 세트 (Xworkbook, Xsheet); // 시트 페이지 컨텐츠 세트 세트 시트 코턴 (XWorkBook, Xsheet); xworkbook.write (OS); } catch (예외 e) {e.printstacktrace (); } 마침내 {if (null! = os) {try {os.close (); } catch (예외 e) {e.printstacktrace (); }} if (null! = xworkbook) {try {xworkbook.close (); } catch (예외 e) {e.printstacktrace (); }}}}} / ** * 세트 시트 페이지 헤더 설정 * @param xworkbook * @param xsheet * / private void setsheEtheAder (xssfworkbook xworkbook, xssfsheet xsheet) {xsheet.setcolumnwidth (, *); xsheet.setcolumnwidth (, *); xsheet.setcolumnwidth (, *); CellStyle CS = XWorkBook.CreateCellStyle (); // 수평 및 수직 중심 설정 CS.SetAlignment (CellStyle.Align_Center); cs.setverticalalignment (cellstyle.vertical_center); // 글꼴을 설정하여 HeaderFont = xworkbook.createFont (); headerfont.setfontheightinpoints ((짧은)); HeaderFont.setBoldweight (xssffont.boldweight_bold); headerfont.setfontname ( "安体"); cs. 세트 폰트 (HeaderFont); cs.setWrapText (true); // 라인을 자동으로 랩핑 할 수 있습니까 xssfrow xrow = xsheet.createrow (); xssfcell xcell = xrow.createcell (); XCELL.SETCELLSTYLE (CS); xcell.setCellValue ( "사용자 ID"); xssfcell xcell = xrow.createcell (); XCELL.SETCELLSTYLE (CS); xcell.setCellValue ( "사용자 이름"); xssfcell xcell = xrow.createcell (); XCELL.SETCELLSTYLE (CS); xcell.setCellValue ( "비밀번호"); } / ** * 시트 페이지 컨텐츠 설정 * @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 <secuserList.size (); i ++) {xssfrow xrow = xsheet.createrow (i+); Secuser secuser = secuserList.get (i); for (int j =; j <; j ++) {xssfcell xcell = xrow.createcell (j); XCELL.SETCELLSTYLE (CS); switch (j) {case : xcell.setCellValue (secuser.getUserId ()); 부서지다; 사례 : xcell.setCellValue (secuser.getUserName ()); 부서지다; 사례 : xcell.setCellValue (secuser.getUserPassword ()); 부서지다; 기본값 : 브레이크; }}}}}}Dao :
패키지 com.dao; java.sql.resultset import; java.sql.sqlexception 가져 오기; java.util.arraylist 가져 오기; Java.util.list 가져 오기; import javax.annotation.resource; org.springframework.stereotype.repository import; com.domain.secuser import; import org.springframework.jdbc.core.jdbctemplate; import org.springframework.jdbc.core.rowmapper; @repository public class IEPORTDAO {@Resource private jdbctemplate jdbctemplate; 프라이버시 rowmapper <Secuser> SUROWMAPPER = NULL; private ieportdao () {surowmapper = new rowmapper <secuser> () {@override public secuser maprow (resultet rs, int index) sqlexection {secuser secuser = new secuser (); secuser.setuserid (rs.getstring ( "user_id")); secuser.setusername (rs.getstring ( "user_name")); secuser.setuserpassword (rs.getstring ( "user_password")); 반환 보안체; }}; } public void importFile (list <Secuser> secuserList) {try {string sql = "sec_user 값에 삽입 (uuid (),?,?)"; List <Object []> paramsList = new ArrayList <Object []> (); for (int i =; i <secuserList.size (); i ++) {secuser secuser = secuserList.get (i); Object [] params = new Object [] {secuser.getUserName (), secuser.getUserPassword ()}; paramslist.add (params); } jdbctemplate.batchupdate (sql, paramslist); } catch (예외 e) {e.printstacktrace (); }} 공개 목록 <Secuser> getSecuserList () {list <Secuser> Sulist = new ArrayList <Secuser> (); StringBuffer sb = new StringBuffer (); sb.Append ( "sec.user_id, su.user_name, su.user_password sec_user su"); try {sulist = jdbctemplate.query (sb.tostring (), surowmapper); } catch (예외 e) {e.printstacktrace (); } return sulist; }}도메인:
패키지 com.Domain; 공개 클래스 Secuser {String userId; // 사용자 ID 문자열 사용자 이름; // 사용자 이름 문자열 userPassword; // 비밀번호 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. 구성 파일 :
<? xml 버전 = "." 인코딩 = "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" " <splay-name> springspringmvcpoi </display-name> <welcome-file-list> <welcome-file> ideport.jsp </welcome-file-list> <!-스프링 구성 파일의 이름과 위치를 지정-<context-param> 컨텍스트 configlocation </param-name> <param-value> ClassPath : DataSource-context.xml </param- value> </context-param> <!-청취자 구성-> <liseer> <layer-class.spramework.web.context.contextLoaderListener </Listenerclass> </Learger> <!-Springmvc 's DispateVer' <Servlet-name> DispatcherServlet </servlet-name> <servlet-class> org.springframework.web.servlet.dispatcherServlet.dispatcherServlet </servlet-class> <!-SpringMVC의 구성 파일 위치 구성-> <init-param> <param-name> contextConfiglocation> <Param-value> classpath : Spring-mvc.xml </param-value> </init-param> </servlet> <servlet-mapping> <servlet-name> dispatcherservlet </servlet-name> <Url-pattern>*. go </url-pattern> </servlet-mapping> <! <filter-name> atriblter-name> </filter-name> <filter-class> org.springframework.web.filter.characterencterencodingfilter </filter-class> <init-param> <param-name> encoding </param-name> <param- value> utf- </init-param> </init-param> </init-param> </init-param> <filter-name> 문자 인코딩 필터 </filter-name> <url-pattern>/*</url-pattern> </filter-mapping> </web-app>
<? xml 버전 = "." 인코딩 = "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.sprringfrfamwork.org/schema/tx" xmlns : mvc = "http://www.springframework.org/schema/mvc"xmlns : util = "http://www.springframework.org/schema/util"xsi : schemalocation = "http://www.spramframwork.org/schema http://www.springframework.org/schema/beans/spring-beans-..xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-conxt- .. http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-..xsd http://www.spramework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-..xsd "> <!-자동 스캐닝 패키지-> <context : component-scan base-package ="com.controller "> </context : component-scan> <"<eadmvc 's resolver-> value = "/web-inf/views/"> </propert> <속성 이름 = "접미어"value = ". jsp"> </property> </bean> <!-파일의 업로드 지원-> <bean id = "multipartresolver"/> </beans>
<? xml 버전 = "." 인코딩 = "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/schema/scremans/sprdeans- .. http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-..xsd "> <context : component-scan base-package ="com "> </context : component-scan>
<? xml 버전 = "." 인코딩 = "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/schema/sprideans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd "> <!-jdbc 구성 파일-> 컨텍스트 : 속성-placeholder location ="!-jdbc.properties "! 소스-> <bean id = "dataSource"destry-method = "close"> <속성 이름 = "user"value = "$ {jdbc.user}"> </property name = "password"value = "$ {jdbc.password}"> </property name = "driver class"value "{jdbc.driver}"> </property>}. 이름 = "jdbcurl"value = "$ {jdbc.jdbcurl}"> </property> <!-연결 풀의 연결이 소비되면 CP-> <속성 이름 = "arcireIncrement"value = ""> </propert> <!-<성물의 숫자는 minpoolsize 사이에 생성되어야합니다. 이름 = "InitialPoolSize"value = ""> </property> <속성 이름 = "maxpoolsize"value = ""> </property> <속성 이름 = "minpoolsize"value = ""> </property> <property name = "maxconnectionage"value = ""> </property name = "maxIdletime" "> <property name ="maxIdleitimeexcessCentions " value = ""> </property> <property name = "testConnectionOnCheckout"value = "false"> </property> <속성 이름 = "testConnectionOnCheckin"value = "false"> </property> <!-모든 두 번째 이름에서 유휴 연결을 확인하십시오. value = ""> </property> <property name = "acquireretryDelay"value = "" "> </property> <property name ="preferredTestQuery "value ="듀얼에서 선택 선택 "> </property> </bean> <!-JDBC 템플릿 JDBCTEMPLATE-> <bean id ="jdbctemplate "> <JDBCTemPlate"> 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. 디렉토리 구조 :
6. 결과 데모
수입:
내보내다:
추신:
1.이 초보자는 아직 첨부 파일을 추가하는 방법을 알지 못했기 때문에 모든 코드를 게시하고 디렉토리 구조를 추가합니다. 그녀는 미래에 첨부 파일을 추가하는 방법을 배우고 수정할 것입니다.
위는이 기사의 모든 내용입니다. 모든 사람의 학습에 도움이되기를 바랍니다. 모든 사람이 wulin.com을 더 지원하기를 바랍니다.