1.导入导出功能操作步骤
导入："下载导入模板" -->保存文件到本地-->在模板中输入要导入的数据行-->"导入"-->选择录入数据的模板文件-->"确定"
导出："导出"-->保存Excel文件到本地。
2.功能实现原理
2.1.外部组件依赖说明
（1）数据库访问相关：mysql-connector-jaca-5.0.8-bin.jar
c3p0-0.9.1.2.jar
commons-dbutils-1.4.jar
commons-io-2.6.jar
commons-logging-1.1.1.jar
（2）前端jsp页面jstl相关：jstl.jar
standard.jar
（3）导入功能相关：jquery-EasyUI（前端组件）
commons-fileupload.jar
poi-3.17.jar
poi-ooxml-3.17.jar
poi-ooxml-schemas-3.17.jar
xmlbeans-2.6.0.jar
commons-collections4-4.1.jar
commons-beanutils-1.8.0.jar
slf4j-api-1.7.5.jar
（4）导出功能相关：jquery-table2excel（前端组件）
如果是maven工程，pom相关依赖：
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>commons-collections4</groupId>
<artifactId>commons-collections4</artifactId>
<version>4.1</version>
</dependency>
<dependency>
<groupId>commons-beanutils</groupId>
<artifactId>commons-beanutils</artifactId>
<version>1.8.3</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.5</version>
</dependency>
2.2.功能实现原理说明：
（1）导入：利用jquery-EasyUI组件展示上传文件对话框，选择目标excel文件；利用commons-fileupload组件解析和处理前端传递过来
的excel文件流数据；利用poi组件解析excel文件内容，转换成数据库表对象结构，利用c3p0、dbutils组件保存到数据库表中。
（2）导出：利用table2excel组件导出表格内容到目标excel文件。
3.功能实现步骤
（1）拷贝需要引入的依赖组件包和util包下的工具类代码到工程中。
（2）在需要导入导出的目标库表对象类中，将excel文件相关的几个字段属性，增加"@ExcelCell"注解。参考（Product.java）。
注意：类中属性的数据类型只能为包装类型（如String，Double等，不包含Integer），不能为基本类型（int,double等）。
index属性值必须与excel文件中的数据记录顺序一致。
（3）根据对象类结构，创建和上传导入模板文件到服务器文件夹，参考（WEB-INF/template/product.xlsx），
修改DownloadImportTemplateServlet里对应的文件名。
（4）增加导入excel功能代码，参考（ProductService.java中importProductDataFromExcel方法）。
（5）增加导出功能代码，参考（pagination.jsp中export2Excel方法）。
下面是前端页面pageination.jsp代码
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %> <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>Insert title here</title> <link rel="stylesheet" href="./resource/css/pagination.css"> <link rel="stylesheet" type="text/css" href="./jquery-EasyUI/themes/default/easyui.css"> <link rel="stylesheet" type="text/css" href="./jquery-EasyUI/themes/icon.css"> <script type="text/javascript" src="./resource/js/jquery-2.1.1.js"></script> <script type="text/javascript" src="./resource/js/jquery-table2excel/src/jquery.table2excel.js"></script> <script type="text/javascript" src="./jquery-EasyUI/js/jquery.easyui.min.js"></script> <script type="text/javascript" src="./resource/js/jquery.form.js"></script> <script type="text/javascript"> function importExcel() { var options = { type: 'post', url: 'importExcelData', dataType: 'json', success: function (json) { alert(json.message); location.reload(); }, error: function() { alert("error"); } }; $('#uploadfile-form').ajaxSubmit(options); $('#import-dialog').dialog('close'); } function export2Excel() { $("#table").table2excel({ // exclude CSS class exclude: ".noExl", sheetName: "product", filename: "product.xls" }); } </script> </head> <body> <div> <a href="DownloadImportTemplate" class="easyui-linkbutton">下载导入模板</a> <a href="javascript:void(0)" class="easyui-linkbutton" onclick="$('#import-dialog').dialog('open')">导入</a> <a href="javascript:void(0)" class="easyui-linkbutton" onclick="export2Excel()">导出</a> </div> <table id="table" border="1" cellspacing="" cellpadding="" width="100%"> <tr> <th>序号</th> <th>名称</th> <th>价格</th> <th>分类</th> <th>库存数量</th> </tr> <c:forEach var="product" items="${pageBean.ps}" varStatus="vs"> <tr> <td>${vs.count}</td> <td>${product.name}</td> <td>${product.price}</td> <td>${product.category}</td> <td>${product.pnum}</td> </tr> </c:forEach> </table> <div align="center"> <ul class="pagination"> <c:if test="${pageBean.currentPage==1}"> <li class="disabled"><a href="#">«上一页</a></li> </c:if> <c:if test="${pageBean.currentPage!=1}"> <li><a href="FindProductByPage?currentPage=${pageBean.currentPage-1}¤tCount=20">«上一页</a></li> </c:if> <c:forEach begin="1" end="${pageBean.totalPage}" var="pageNum"> <c:if test="${pageNum==pageBean.currentPage}"> <li class="active"><a href="#">${pageNum}</a></li> </c:if> <c:if test="${pageNum!=pageBean.currentPage}"> <li><a href="FindProductByPage?currentPage=${pageNum}¤tCount=20">${pageNum}</a></li> </c:if> </c:forEach> <c:if test="${pageBean.currentPage==pageBean.totalPage || pageBean.totalPage==0}"> <li class="disabled"><a href="#">»下一页</a></li> </c:if> <c:if test="${pageBean.currentPage!=pageBean.totalPage && pageBean.totalPage!=0}"> <li><a href="FindProductByPage?currentPage=${pageBean.currentPage+1}¤tCount=20">»下一页</a></li> </c:if> </ul> </div> <%--导入对话框--%> <div id="import-dialog" class="easyui-dialog" title="Basic Dialog" data-options="iconCls:'icon-save',closed:true,buttons:'#import-dialog-button'" style="400px;height:200px;padding:10px"> <form id="uploadfile-form" method="post" action="" enctype="multipart/form-data"> 选择文件:<input type="file" name="UPLOAD_FILE"> </form> </div> <%--导入对话框底部按钮--%> <div id="import-dialog-button"> <a href="javascript:void(0)" class="easyui-linkbutton" onclick="importExcel()">Save</a> <a href="javascript:void(0)" class="easyui-linkbutton" onclick="$('#import-dialog').dialog('close')">Close</a> </div> </body> </html>
实体类Product.java代码
package graduation.sample.entity; import graduation.sample.util.excel.ExcelCell; public class Product { private String id; @ExcelCell(index = 2) private String name; @ExcelCell(index = 3) private Double price; @ExcelCell(index = 4) private String category; public Double getPnum() { return pnum; } public void setPnum(Double pnum) { this.pnum = pnum; } @ExcelCell(index = 5) private Double pnum; public String getId() { return id; } public void setId(String id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public double getPrice() { return price; } public void setPrice(double price) { this.price = price; } public String getCategory() { return category; } public void setCategory(String category) { this.category = category; } }
模板下载DownloadImportTemplateServlet代码
package graduation.sample.servlet; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.net.URLEncoder; @WebServlet("/DownloadImportTemplate") public class DownloadImportTemplateServlet extends HttpServlet { private static final String FILE_PATH = "/WEB-INF/template/"; private static final String FILE_NAME = "product.xlsx"; private static final long serialVersionUID = 1L; public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { //设置ContentType字段值 response.setContentType("text/html;charset=utf-8"); //设置相应消息编码 response.setCharacterEncoding("utf-8"); //设置请求消息编码 request.setCharacterEncoding("utf-8"); //获取所要下载的文件名称 //对文件名称编码 String filename = new String(FILE_NAME.trim().getBytes("iso8859-1"), "UTF-8"); // 通知浏览器以下载的方式打开 response.addHeader("Content-Type", "application/octet-stream"); response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "utf-8")); // 通过文件流读取文件 InputStream in = getServletContext().getResourceAsStream(FILE_PATH + FILE_NAME); // 获取response对象的输出流 OutputStream out = response.getOutputStream(); byte[] buffer = new byte[1024]; int len; //循环取出流中的数据 while ((len = in.read(buffer)) != -1) { out.write(buffer, 0, len); } } public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doGet(request, response); } }
导入到Excel，ImportExcelDataServlet代码
package graduation.sample.servlet; import graduation.sample.service.ProductService; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.*; /** * 导入Excel文件内容到数据库表实现步骤： * 1.接收上传的文件输入流 * 2.将文件内容拷贝到 WEB-INF/upload文件夹下 * 3.解析WEB-INF/upload下对应的文件内容，保存至数据库表 */ @WebServlet("/importExcelData") public class ImportExcelDataServlet extends HttpServlet { @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { this.doGet(req, resp); } @Override protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { resp.setContentType("text/json;charset=UTF-8"); resp.setCharacterEncoding("UTF-8"); PrintWriter out = resp.getWriter(); String str = "{"code":"0","message":"success"}"; try { ProductService productService = new ProductService(); boolean result = productService.importProductDataFromExcel(req); if (!result) { str = "{"code":"-1","message":"failed"}"; } } catch (Exception e) { e.printStackTrace(); str = "{"code":"-2","message":"error"}"; } //返回响应结果到前端页面 out.println(str); out.flush(); out.close(); } }
service层代码
package graduation.sample.service; import graduation.sample.dao.ProductDao; import graduation.sample.dao.ProductDaoImpl; import graduation.sample.util.FileUploadFormParam; import graduation.sample.entity.PageBean; import graduation.sample.entity.Product; import graduation.sample.util.FileUploadRequestUtil; import graduation.sample.util.excel.ExcelLogs; import graduation.sample.util.excel.ExcelUtil; import org.apache.commons.fileupload.FileItem; import org.apache.commons.fileupload.servlet.ServletFileUpload; import javax.servlet.http.HttpServletRequest; import java.io.*; import java.sql.SQLException; import java.util.Collection; import java.util.List; import java.util.UUID; public class ProductService { public PageBean findProductByPage(int currentPage, int currentCount) throws SQLException { PageBean pageBean = new PageBean(); pageBean.setCurrentPage(currentPage); pageBean.setCurrentCount(currentCount); ProductDaoImpl imp = new ProductDaoImpl(); List<Product> products = imp.findByProductPage(currentCount, currentPage); pageBean.setPs(products); int totalCount = imp.findProductCount(); pageBean.setTotalCount(totalCount); int totalPage = (int) Math.ceil(totalCount * 1.0 / currentCount); pageBean.setTotalPage(totalPage); return pageBean; } public boolean importProductDataFromExcel(HttpServletRequest request) throws IOException, SQLException { //判断表单属性enctype，值是否为"multipart/form-data" if (!ServletFileUpload.isMultipartContent(request)) { return false; } //调用工具类解析请求参数 FileUploadFormParam formParam = FileUploadRequestUtil.parseParam(request); //获取请求参数中的file类型的item值 FileItem fileItem = formParam.getFileMap().get("UPLOAD_FILE"); if (fileItem == null) { return false; } //解析file类型的item内容值，生成文件保存在服务器upload文件夹中 String filePath = this.uploadFile(request, fileItem); //将保存到服务器的文件内容解析，导入到数据库表tv_product中 File file = new File(filePath); InputStream inputStream = new FileInputStream(file); ExcelLogs logs = new ExcelLogs(); //将上传到服务器的文件内容加载解析 Collection<Product> importExcel = ExcelUtil.importExcel(Product.class, inputStream, "yyyy/MM/dd HH:mm:ss", logs, 0); //文件解析内容保存到数据库表 ProductDao productDao = new ProductDaoImpl(); for (Product p : importExcel) { try { productDao.addProduct(p); } catch (SQLException e) { //TODO: e.printStackTrace(); throw e; } } return true; } /** * 上传文件到服务器指定路径 * * @param fileItem * @return 文件保存路径 * @throws IOException */ private String uploadFile(HttpServletRequest request, FileItem fileItem) throws IOException { String filePath = null; //上传的完整文件路径名 String uploadFilePath = fileItem.getName(); if (uploadFilePath == null || uploadFilePath.equals("")) { return filePath; } //获取文件名 String uploadFileName = uploadFilePath.substring(uploadFilePath.lastIndexOf("\") + 1); //保存到服务器上的文件名 String fileName = new StringBuffer(UUID.randomUUID().toString().replace("-", "")) .append("_").append(uploadFileName).toString(); //保存到服务器上的文件路径 filePath = request.getServletContext().getRealPath("/WEB-INF/upload/" + fileName); //读取和写入文件内容 InputStream in = null; FileOutputStream out = null; try { File file = new File(filePath); file.getParentFile().mkdirs(); file.createNewFile(); //输入流 in = fileItem.getInputStream(); //输出流 out = new FileOutputStream(file); // 流的对拷 byte[] buffer = new byte[1024];//每次读取1个字节 int len; //开始读取上传文件的字节，并将其输出到服务端的上传文件输出流中 while ((len = in.read(buffer)) > 0) { out.write(buffer, 0, len); } } catch (Exception e) { e.printStackTrace(); throw e; } finally { // 关闭文件输入输出流 if (in != null) { in.close(); } if (out != null) { out.close(); } } return filePath; } }
导出功能代码：
$("#table").table2excel({
// exclude CSS class
exclude: ".noExl",
sheetName: "product",
filename: "product.xls"
});
4.table2excel插件介绍
jquery-table2excel是一款可以将HTML表格的内容导出到微软Excel电子表格中的jQuery插件。该插件可以根据你的需要导出表格中的内容，不需要的行可以不导出。 它文件体积小，使用非常方便。
先写好前端按钮，还需要一个table
<input type="button" value="导出" class="Button" onclick="Export();" /> <table id='exceltable'><td>内容内容内容</td></table>
初始化js
function Export（）{
$("#exceltable").table2excel({ //exceltable为存放数据的table // 不被导出的表格行的CSS class类 exclude: ".noExl", // 导出的Excel文档的名称 name: "表格-" + new Date().getTime(), // Excel文件的名称 filename: "表格-" + new Date().getTime() + ".xls", bootstrap: false }); }
table2excel插件的可用配置参数有：
exclude：不被导出的表格行的CSS class类。
name：导出的Excel文档的名称。
filename：Excel文件的名称。
exclude_img：是否导出图片。
exclude_links：是否导出超链接
exclude_inputs：是否导出输入框中的内容。