在做web开发时，我们经常会用到数据表格的导入导出功能，这样可以帮我们节省人工操作的时间，极大提高办事效率。
笔者所做的导入导出是针对Java springMVC框架、工作簿poi以及前端jquery-easyui插件设计的。
采用三层架构设计，前台发送请求到对应的servelet中，Servelet根据不同的请求实现具体的方法，到持久层查询数据库信息。（对于理解和学习java对excel操作很适合）
项目所需要的jar包和插件下载地址：
/download/a1150499208/10921184
项目所需要的sql下载地址：
/download/a1150499208/10921220
整体结构如下：
因为只是为了实现，导入导出的功能，和对数据库的查询和插入，页面直接使用的是插件包里面的，没做过多的修饰，效果如下：
点击左上角红色向左按钮导入，支持导入excel，通过解析excel里面的数据，封装起来，到持久层插入到对应的表中。
点击左上角黄色向右按钮导出，实现导出excel数据导出。
前台页面代码：
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%><%String path = request.getContextPath();String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html><head><base href="<%=basePath%>"><title>POIExcel</title><meta http-equiv="pragma" content="no-cache"><meta http-equiv="cache-control" content="no-cache"><meta http-equiv="expires" content="0"><meta http-equiv="keywords" content="keyword1,keyword2,keyword3"><meta http-equiv="description" content="This is my page"><script type="text/javascript" src="./jquery-easyui-1.4.1/jquery.min.js"></script><script type="text/javascript" src="./jquery-easyui-1.4.1/jquery.easyui.min.js"></script><script type="text/javascript" src="./jquery-easyui-1.4.1/locale/easyui-lang-zh_CN.js"></script><link rel="stylesheet" type="text/css" href="./jquery-easyui-1.4.1/themes/default/easyui.css"><link rel="stylesheet" type="text/css" href="./jquery-easyui-1.4.1/themes/icon.css"><script type="text/javascript">$(function(){Init();//打开页面就去查询了});//我去查询所有用户信息了function Init(){$('#excel').datagrid({type : 'post',url : './UserJsonServlet',loadMsg : '数据加载中,请稍等!!!!!!!!',nowrap : false,striped : true,fit : true,pagination:true,columns : [[{field:'checkbox',checkbox:true},{field : 'id',title : '编号',width : 100,align:'center'}, {field : 'name',title : '姓名',width : 120,align:'center'}, {field : 'sex',title : '性别',width : 100,align:'center'}, {field : 'age',title : '年龄',width : 120,align:'center'}, {field : 'tell',title : '电话',width : 150,align:'center'}, {field : 'address',title : '地址',width : 280,align:'center'}]],toolbar: [{width:'50',iconCls: 'icon-undo',handler: function(){ExcelImport();}},'-',{width:'50',iconCls: 'icon-redo',handler: function(){ExcelExport();}}]});}/*Excel导入 */function ExcelImport() {alert("导入excel!")var url="./ExcelImportServlet";window.open(url);}/*Excel导出 */function ExcelExport(){alert("导出excel!")var url="./ExcelExportServlet";window.open(url);}</script></head><div align="center"><div class="easyui-panel" title="Excel导入导出示例"style="width: 930px; height: 350px; background: #fafafa;"data-options="border:false"><table id="excel"></table></div></div></body></html>
首次访问index页面，默认加载去查询数据库中表是否有数据：
package com.xiaoxiaozhen.servlet;import java.io.IOException;import java.util.ArrayList;import java.util.List;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import com.alibaba.fastjson.JSON;import com.xiaoxiaozhen.model.UserBean;import com.xiaoxiaozhen.service.UserJsonService;//用户的业务层public class UserJsonServlet extends HttpServlet {protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {//请求发送都是utf-8 request.setCharacterEncoding("UTF-8");response.setCharacterEncoding("UTF-8");String parameter = request.getParameter("excel");//查询用户信息List<UserBean> list = new UserJsonService().finall();//封装好的用户信息放到json中返回前台String json = null;json = JSON.toJSONString(list);response.setCharacterEncoding("UTF-8");response.getWriter().write(json);}protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {doGet(request, response);}}
业务层向下跳：
package com.xiaoxiaozhen.service;import java.util.List;import com.xiaoxiaozhen.dao.UserJsonDao;import com.xiaoxiaozhen.model.UserBean;//用户业务层public class UserJsonService {UserJsonDao dao = new UserJsonDao();//查询所有用户public List<UserBean> finall() {return dao.finall();}}
持久层来处理数据库相关的：
package com.xiaoxiaozhen.dao;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import com.xiaoxiaozhen.model.UserBean;import com.xiaoxiaozhen.util.DBTool;//持久层public class UserJsonDao {//查询所有用户信息public List<UserBean> finall() {DBTool dbt = new DBTool();PreparedStatement pstmt = null;Connection conn = null;ResultSet rs = null;List<UserBean> list = new ArrayList<UserBean>();try {conn = dbt.getConnection();String sql = "SELECT id,name,sex,age,tell,address FROM testexcel";pstmt = conn.prepareStatement(sql);rs = pstmt.executeQuery();while (rs.next()) {UserBean ub = new UserBean();ub.setId(rs.getInt("id"));ub.setName(rs.getString("name"));ub.setSex(rs.getString("sex"));ub.setAge(rs.getInt("age"));ub.setTell(rs.getInt("tell"));ub.setAddress(rs.getString("address"));list.add(ub);}} catch (SQLException e) {e.printStackTrace();}return list;}}
首次进来因为数据库中没有资料，查询为空。
点击导入，读取分析excel中的数据信息，导入到数据中：
package com.xiaoxiaozhen.servlet;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.io.InputStream;import java.util.ArrayList;import java.util.List;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.poifs.filesystem.POIFSFileSystem;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import com.xiaoxiaozhen.model.UserBean;import com.xiaoxiaozhen.service.ExcelImportService;//导入excelpublic class ExcelImportServlet extends HttpServlet {protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {//请求发送都是utf-8request.setCharacterEncoding("UTF-8");response.setCharacterEncoding("UTF-8");//文件路径 test.xlsx 文件在代码打包里面，给他这个excel放到C盘根目录下List<UserBean> list = parseExcel("C:\\test.xlsx");try {//保存new ExcelImportService().save(list);} catch (Exception e) {e.printStackTrace();}//保存完重新打开首页，首页默认会查询request.getRequestDispatcher("/index.jsp").forward(request, response);}// 解析Excel,读取内容,path Excel路径public static List<UserBean> parseExcel(String path) {List<UserBean> list = new ArrayList<UserBean>();File file = null;InputStream input = null;if (path != null && path.length() > 7) {// 判断文件是否是Excel(、)String suffix = path.substring(path.lastIndexOf("."), path.length());file = new File(path);try {input = new FileInputStream(file);} catch (FileNotFoundException e) {System.out.println("未找到指定的文件！");}// Excelif (".xls".equals(suffix)) {POIFSFileSystem fileSystem = null;// 工作簿HSSFWorkbook workBook = null;try {fileSystem = new POIFSFileSystem(input);workBook = new HSSFWorkbook(fileSystem);} catch (IOException e) {e.printStackTrace();}// 获取第一个工作簿HSSFSheet sheet = workBook.getSheetAt(0);list = getContent((Sheet) sheet);// Excel} else if (".xlsx".equals(suffix)) {XSSFWorkbook workBook = null;try {workBook = new XSSFWorkbook(input);} catch (IOException e) {e.printStackTrace();}// 获取第一个工作簿XSSFSheet sheet = workBook.getSheetAt(0);list = getContent(sheet);}} else {System.out.println("非法的文件路径!");}return list;}// 获取Excel内容public static List<UserBean> getContent(Sheet sheet) {List<UserBean> list = new ArrayList<UserBean>();// Excel数据总行数int rowCount = sheet.getPhysicalNumberOfRows();// 遍历数据行，略过标题行，从第二行开始for (int i = 1; i < rowCount; i++) {UserBean ub = new UserBean();Row row = sheet.getRow(i);int cellCount = row.getPhysicalNumberOfCells();// 遍历行单元格for (int j = 0; j < cellCount; j++) {Cell cell = row.getCell(j);switch (j) {case 0:// 根据cell中的类型来输出数据if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {ub.setId((int)cell.getNumericCellValue());}break;case 1:if (cell.getCellType() == Cell.CELL_TYPE_STRING) {ub.setName(cell.getStringCellValue());}break;case 2:if (cell.getCellType() == Cell.CELL_TYPE_STRING) {ub.setSex(cell.getStringCellValue());}break;case 3:if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {ub.setAge((int)cell.getNumericCellValue());}break;case 4:if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {ub.setTell((int)cell.getNumericCellValue());}break;case 5:if (cell.getCellType() == Cell.CELL_TYPE_STRING) {ub.setAddress(cell.getStringCellValue());}break;}}list.add(ub);}return list;}protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {doGet(request, response);}}
向下跳：
package com.xiaoxiaozhen.service;import java.util.List;import com.xiaoxiaozhen.dao.ExcelImportDao;import com.xiaoxiaozhen.model.UserBean;public class ExcelImportService {//保存导入的信息public void save(List<UserBean> list) {new ExcelImportDao().save(list);}}
持久层来和数据库交互：
package com.xiaoxiaozhen.dao;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;import java.util.List;import com.xiaoxiaozhen.model.UserBean;import com.xiaoxiaozhen.util.DBTool;public class ExcelImportDao {//保存导入的信息public void save(List<UserBean> list) {for (int i = 0; i < list.size(); i++) {Connection conn = null;PreparedStatement ps = null;DBTool dbt = new DBTool();try {conn = dbt.getConnection();String sql = "insert into testexcel(id, name,sex, age, tell, address) values('"+ list.get(i).getId()+ "', '"+ list.get(i).getName()+ "', '"+ list.get(i).getSex()+ "', '"+ list.get(i).getAge()+ "', '"+ list.get(i).getTell()+ "', '" + list.get(i).getAddress() + "')";ps = conn.prepareStatement(sql);ps.execute();} catch (Exception e) {e.printStackTrace();} finally {if (ps != null) {try {ps.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}if (conn != null) {try {conn.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}}}}
导入成功。
如果觉得《在javaweb中实现excel的导入导出到数据库（mysql）》对你有帮助，请点赞、收藏，并留下你的观点哦！
