Saya sering menemukan perlunya mengimpor dan mengekspor Excel di tempat kerja. Saya memiliki contoh jawaban singkat untuk dibagikan kepada Anda.
Jangan bilang banyak omong kosong,
1. Paket JAR yang Diperlukan:
2. Kode front-end:
ieport.jsp:
<%@page Impor = "java.util.date"%> <%@page language = "java" contentType = "Text/html; charset = utf-" pageEncoding = "utf-"%> <! Doctml html public "-// wc // dtd xhtml. Transisi // Endml" endml "eng/wc // dtd xhtml. "http://www.w.org/TR/xhtml/DTD/xhtml-transitional.dtd"> <html xmlns="http://www.w.org//xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-" /> <itement> halaman impor/ekspor </iteme> <script type = "text/javascript"> function exportFile () {window.location.href = "<%= request.getContextPath ()%>/export.go"; } </script> </head> <body> <Form Action = "Impor.go" Method = "POST" ENCTYPE = "Multipart/Form-Data"> File: <input type = "file" name = "unggahfile"/> <br> </br> <input type = "kirim" value = "impor"/<input type "value" nilai = " </body> </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. "http://www.w.org/TR/xhtml/DTD/xhtml-transitional.dtd"> <html xmlns="http://www.w.org//xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-" /> <iteme> halaman sukses </iteme> <script type = "text/javascript"> // var secuserList = '$ {secuserlist}'; // peringatan (SecUserList); </script> </head> <body> <c: if test = "$ {type == 'impor'}"> <verv> Impor berhasil! </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> Ekspor dengan sukses! </div> </c: if> </body> </html>3. Kode Latar Belakang:
Pengontrol:
paket com.controller; impor java.io.file; impor java.util.list; impor javax.annotation.Resource; impor javax.servlet.http.httpservletRequest; impor javax.servlet.http.httpservletResponse; impor org.springframework.stereotype.controller; impor org.springframework.web.bind.annotation.requestmapping; impor org.springframework.web.bind.annotation.requestparam; impor org.springframework.web.multipart.multipartfile; impor org.springframework.web.servlet.modelandview; impor com.domain.secuser; impor com.service.ieportservice; @Controller kelas publik ieportController {@resource private ieportService ieportService; @RequestMapping ("/Impor") Public ModelAndView ImportFile (@RequestParam (value = "unggahfile") MultipartFile Mfile, httpservletRequest, httpservletResponse response) {string rootpath = request.getSesi (). GetServletcontext (). Daftar <Secuser> secuserList = ieportService.importfile (mfile, rootpath); ModelAndView mv = new ModelAndView (); mv.addObject ("type", "impor"); mv.addObject ("secuserlist", secuserlist); mv.setViewName ("/Success"); mengembalikan MV; } @RequestMapping ("/Export") Public ModelAndView ExportFile (httpservletResponse response) {ieportService.ExportFile (respons); ModelAndView mv = new ModelAndView (); mv.addObject ("type", "export"); mv.setViewName ("/Success"); mengembalikan MV; }} melayani:
paket com.service; impor java.io.file; impor java.io.fileInputStream; impor java.io.inputstream; impor java.io.outputStream; impor java.net.urlencoder; impor java.text.simpledateFormat; impor java.util.arraylist; impor java.util.date; impor java.util.list; impor javax.annotation.Resource; impor javax.servlet.http.httpservletResponse; impor org.apache.poi.hssf.usermodel.hssfrow; impor org.apache.poi.hssf.usermodel.hssfsheet; impor org.apache.poi.hssf.usermodel.hssfworkbook; impor org.apache.poi.ss.usermodel.cellstyle; impor org.apache.poi.ss.usermodel.font; impor org.apache.poi.xssf.usermodel.xssfcell; impor org.apache.poi.xssf.usermodel.xssffont; impor org.apache.poi.xssf.usermodel.xssfrow; impor org.apache.poi.xssf.usermodel.xssfsheet; impor org.apache.poi.xssf.usermodel.xssfworkbook; impor org.springframework.stereotype.service; impor org.springframework.web.multipart.multipartfile; impor com.dao.ieportdao; impor com.domain.secuser; @Service public class ieportService {@resource private ieportdao ieportdao; Daftar Publik <SecUser> ImportFile (MultipartFile MFile, String RootPath) {Daftar <Secuser> secuserList = ArrayList baru <secUser> (); String filename = mfile.getoriginalfileName (); String suffix = filename.substring (filename.LastIndexOf (".") +, Filename.length ()); String ym = new SimpleDateFormat ("yyyy-mm"). Format (new date ()); String filepath = "unggahfile/" + ym + fileName; coba {file file = file baru (rootpath + filepath); if (file.exists ()) {file.delete (); file.mkdirs (); } else {file.mkdirs (); } mfile.transferto (file); if ("xls" .Equals (sufiks) || "xls" .equals (sufiks)) {secuserlist = importXls (file); ieportdao.importfile (secuserlist); } lain jika ("xlsx" .Equals (sufiks) || "xlsx" .Equals (sufiks)) {secuserList = importXlsx (file); ieportdao.importfile (secuserlist); }} catch (Exception e) {e.printstacktrace (); } return SecUserList; } Daftar Pribadi <SecUser> ImportXLS (File File) {Daftar <Secuser> secuserList = ArrayList baru <secUser> (); InputStream adalah = null; Hssfworkbook hworkBook = null; coba {is = new fileInputStream (file); hworkBook = HSSFWorkbook baru (IS); Hssfsheet hseet = hworkBook.getsheetat (); if (null! = hseet) {for (int i =; i <hsheet.getphysicalNumberofrows (); i ++) {secuser su = new secuser (); Hssfrow hrow = hseet.getrow (i); su.setusername (hrow.getcell (). tostring ()); su.setUserPassword (hrow.getCell (). ToString ()); secuserlist.add (su); }}} catch (Exception e) {e.printstacktrace (); } akhirnya {if (null! = is) {try {is.close (); } catch (Exception e) {E.PrintStackTrace (); }} if (null! = hworkBook) {coba {hworkBook.close (); } catch (Exception e) {E.PrintStackTrace (); }}} return SecUserList; } Daftar Privat <SecUser> importXLSX (file file) {List <SecUser> secuserList = ArrayList baru <SecUser> (); InputStream adalah = null; Xssfworkbook xworkbook = null; coba {is = new fileInputStream (file); XWorkBook = XSSFWorkbook baru (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 (); } akhirnya {if (null! = is) {try {is.close (); } catch (Exception e) {E.PrintStackTrace (); }} if (null! = xworkbook) {coba {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; coba {string filename = "user" + df.format (new date ()) + ".xlsx"; os = response.getoutputStream (); response.reset (); response.setHeader ("konten-disposisi", "lampiran; fileName =" + urlencoder.encode (nama file, "utf-")); response.setContentType ("Application/Octet-Streem"); XWorkbook = XSSFWorkbook baru (); Xssfsheet xsheet = xworkbook.createSheet ("UserList"); // atur lembar halaman header setsheetheader (xworkbook, xsheet); // Set Lembar Halaman Konten SetSheetContent (XWorkbook, Xsheet); xworkbook.write (OS); } catch (Exception e) {E.PrintStackTrace (); } akhirnya {if (null! = os) {coba {os.close (); } catch (Exception e) {E.PrintStackTrace (); }} if (null! = xworkbook) {coba {xworkbook.close (); } catch (Exception e) {E.PrintStackTrace (); }}}}} / ** * Set header halaman lembar * @param xworkbook * @param xsheet * / private void setSheetHeader (xssfworkbook xworkbook, xssfsheet xsheet) {xsheet.setColumnWidth (, *); xsheet.setColumnWidth (, *); xsheet.setColumnWidth (, *); CellStyle CS = XWorkBook.CreateCellStyle (); // atur cs.setalignment cs.setalignment horizontal dan vertikal (cellstyle.align_center); cs.setVerticalAlignment (cellstyle.vertical_center); // atur font font headerFont = xworkbook.createFont (); headerfont.setFontheightInpoints ((pendek)); headerfont.setBoldWeight (xssffont.boldweight_bold); headerfont.setFontName ("安体"); cs.setfont (headerfont); cs.setWraptext (true); // Apakah mungkin untuk membungkus garis secara otomatis xssfrow xrow = xsheet.createrow (); Xssfcell xcell = xrow.createCell (); xcell.setCellstyle (CS); xcell.setCellValue ("ID pengguna"); Xssfcell xcell = xrow.createCell (); xcell.setCellstyle (CS); xcell.setCellValue ("nama pengguna"); Xssfcell xcell = xrow.createCell (); xcell.setCellstyle (CS); xcell.setCellValue ("kata sandi"); } / ** * Set Lembar Halaman Konten * @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); untuk (int j =; j <; j ++) {xssfcell xcell = xrow.createCell (j); xcell.setCellstyle (CS); switch (j) {case: xcell.setCellValue (secuser.getUserId ()); merusak; kasus: xcell.setCellValue (secuser.getusername ()); merusak; kasus: xcell.setCellValue (secuser.getUserPassword ()); merusak; default: break; }}}}}}Dao:
paket com.dao; impor java.sql.Resultset; impor java.sql.sqlexception; impor java.util.arraylist; impor java.util.list; impor javax.annotation.Resource; impor org.springframework.stereotype.repository; impor com.domain.secuser; impor org.springframework.jdbc.core.jdbctemplate; impor org.springframework.jdbc.core.rowmapper; @Repository Public Class ieportdao {@Resource private JDBCtemplate jdbctemplate; Private RowMapper <secUser> SurowMapper = null; private ieportdao () {SurowMapper = RowMapper baru <secUser> () {@Override public secuser mapRow (hasil RS, indeks int) melempar sqlexception {secuser secuser = new secuser (); secuser.setUserId (rs.getString ("user_id")); secuser.setusername (rs.getString ("user_name")); secuser.setUserPassword (rs.getString ("user_password")); mengembalikan secuser; }}; } public void importFile (Daftar <SecUser> secuserList) {coba {string sql = "masukkan ke dalam nilai sec_user (uuid (), ?,?)"; Daftar <object []> paramslist = ArrayList baru <object []> (); untuk (int i =; i <secuserlist.size (); i ++) {secuser secuser = secuserlist.get (i); Objek [] params = objek baru [] {secuser.getUserName (), secuser.getUserPassword ()}; paramslist.add (params); } jdbctemplate.batchupdate (sql, paramslist); } catch (Exception e) {E.PrintStackTrace (); }} Daftar Publik <SecUser> getsecuserList () {Daftar <Secuser> sulist = ArrayList baru <Secuser> (); StringBuffer SB = StringBuffer baru (); SB.Append ("Pilih su.user_id, su.user_name, su.user_password dari sec_user su"); coba {sulist = jdbctemplate.query (sb.tostring (), surowmapper); } catch (Exception e) {E.PrintStackTrace (); } return sulist; }}domain:
paket com.domain; Public Class SecUser {String UserId; // User ID String Username; // nama pengguna string userpassword; // kata sandi string publik getUserId () {return userId; } public void setUserId (String userId) {this.userId = userId; } string publik 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. File Konfigurasi:
<? 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/we-papp__." <play-name> springspringmvcpoi </play-name> <creendak-file-file> <creendak-file> ieport.jsp </welcome-file> </celcome-file-file> <!-tentukan nama dan lokasi file konfigurasi spring-> <mendel-param> <param-name> ContextConfiglocation </Param-name-name> PAINTMLEX.PAXLEXT.PAME: PAINMLEXTEX.PAME: PAINMLEXTEX.XPATH: PARAM- PAVAMLEX. classpath:dataSource-context.xml </param-value> </context-param> <!-- Configure Listener --> <listener> <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class> </listener> <!-- Configure SpringMVC's DispatcherServlet --> <servlet> <servlet-name> DispatcherServlet </servlet-name> <servlet-class> org.springframework.web.servlet.dispatcherServlet </servlet-class> <!-Konfigurasikan lokasi konfigurasi springmvc-> <init-param> <param-name> ContextFiglocation </param-n-name> <param-value> classpath: spring-mvc.xml </param-value> </it-param> </servlet> <servlet-Mapping> <servlet-name> DispatcherServlet </servlet-name> <Rerl-pola>*. GO </RURL-POLATHER> </Servlet-Mapping> <!-unggah encoding*. GO </RURL-POLATHER> </Servlet-Mapping> <! <filter-name> characterencodingFilter </tiler-name> <nift-class> org.springframework.web.filter.characterencodingfilter </filter-class> <InT-param> <param-name> encoding </param-name> <param-value> UTF-UTF-</param-value> </Param-name> <param-value> UTF-</param-value> </init-par/-nvalue> <param-value> </uTfpappap </Param-Value> </init---name> <param-value> <param> </--value> <param--value> </Param- value> </Param-Parnape> <filter-name> CharacterencodingFilter </filter-name> <RURL-PATERS>/*</RURL-PATERS> </TERFERTER-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:/schema. xmlns: mvc = "http://www.springframework.org/schema/mvc" xmlns: util = "http://www.springframewework.org/schema/util" xsi: schemalocation = "http.org http://www.springframework.org/schema/beans/spring-weans-..xsd http://www.springframework.org/schema/context http:/www.spramework.org/schema/context 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 "> <!-Mengkonfigurasi paket pemindaian otomatis-> <konteks: komponen-scan-package =" com.controller "> </konteks: component-scan> <!-configure springmc's view = boan/componen <can> <! value = "/web-inf/views/"> </property> <properti name = "suffix" value = ". jsp"> </propert> </bean> <!-mendukung pengunggahan file-> <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://wwww.spramework.org http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-..xsd "> <Context: component-scan-package =" com "> Context: component-scan>
<? 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:/wwww.springframework http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd "> <!-baca jdbc file konfigurasi-Konteks: Konteks Properti-Place.xsd"> <! <bean id = "DataSource" Destroy-Method = "Tutup"> <name properti = "user" value = "$ {jdbc.user}"> </property> <properti nama = "kata sandi" value = "$ {jdbc.password}"> </drover name = "value" value "$ {jdbc.password}"> </driver- Properties = "value" $ {JDBC $ {JDB. name = "jdbcurl" value = "$ {jdbc.jdbcurl}"> </prop Property> <!-Ketika koneksi dalam kumpulan koneksi digunakan, jumlah koneksi baru yang dibuat pada satu waktu dengan cp-> <properti nama = "AcquireIncrement" Nilai = ""> </properti> <!-Jumlah Connections selama Inisialisasi selama Minools "No." "> </Property> <!-Jumlah Connections Selama Initialization selama Minools BE BE BE BE BE BE" "> </properti> <!-Jumlah koneksi yang dibuat selama Minools (AcquireCrement" wajib = ""> </properti> <! 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 = ""> </property> <properti name = "AcquireretryDelay" value = ""> </property> <properti name = "preferredtestQuery" value = "SELECT DARI DUAL"> </properti> </bean> <!-Konfigurasikan Templat JDBC JDBCTEMPLATE-> <Bean ID = "JDBCTEMPLATE" Ref = "DataSource"> </constructor-Arg> </bean> </bean>jdbc.driverclass = com.mysql.jdbc.driver jdbc.jdbcurl = jdbc: mysql: // localhost:/mydb jdbc.user = myuser jdbc.password = myuser
5. Struktur Direktori:
6. Hasil demonstrasi
Impor:
Ekspor:
PS:
1. Pemula ini, karena dia belum tahu cara menambahkan lampiran, dia memposting semua kode dan menambahkan struktur direktori. Dia akan belajar cara menambahkan lampiran di masa depan dan kemudian memodifikasinya.
Di atas adalah semua konten artikel ini. Saya berharap ini akan membantu untuk pembelajaran semua orang dan saya harap semua orang akan lebih mendukung wulin.com.