在javaweb中实现excel的导⼊导出到数据库（mysql）
在做web开发时，我们经常会⽤到数据表格的导⼊导出功能，这样可以帮我们节省⼈⼯操作的时间，极⼤提⾼办事效率。
笔者所做的导⼊导出是针对Java springMVC框架、⼯作簿poi以及前端jquery-easyui插件设计的。
采⽤三层架构设计，前台发送请求到对应的servelet中，Servelet根据不同的请求实现具体的⽅法，到持久层查询数据库信息。（对于理解和学习java对excel操作很适合）
点击左上⾓黄⾊向右按钮导出，实现导出excel数据导出。
前台页⾯代码：
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%
String path = ContextPath();
String basePath = Scheme()+"://"+ServerName()+":"+ServerPort()+p
ath+"/";
%>
<!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导⼊导出⽰例"
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 del.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 = Parameter("excel");
//查询⽤户信息
List<UserBean> list = new UserJsonService().finall();
//封装好的⽤户信息放到json中返回前台
String json = null;
json = JSONString(list);
response.setCharacterEncoding("UTF-8");
}
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 del.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 del.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 = Connection();
String sql = "SELECT id,name,sex,age,tell,address FROM testexcel"; pstmt = conn.prepareStatement(sql);
rs = uteQuery();
while (rs.next()) {
UserBean ub = new UserBean();
ub.Int("id"));
ub.String("name"));
ub.String("sex"));
ub.Int("age"));
ub.Int("tell"));
ub.String("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.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 del.UserBean;
import com.xiaoxiaozhen.service.ExcelImportService;
//导⼊excel
public class ExcelImportServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { //请求发送都是utf-8
request.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();
}
//保存完重新打开⾸页，⾸页默认会查询
mysql下载到了c盘RequestDispatcher("/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(2003、2007)
String suffix = path
.substring(path.lastIndexOf("."), path.length());
file = new File(path);
try {
input = new FileInputStream(file);
} catch (FileNotFoundException e) {
System.out.println("未到指定的⽂件！");
}
// Excel2003
if (".xls".equals(suffix)) {
POIFSFileSystem fileSystem = null;
// ⼯作簿
HSSFWorkbook workBook = null;
try {
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论