今天小编给大家分享一下java怎么将Excel文件上载并把数据导入数据库的相关知识点，内容详细，逻辑清晰，相信大部分人都还太了解这方面的知识，所以分享这篇文章给大家参考一下，希望大家阅读完这篇文章后有所收获，下面我们一起来了解一下吧。
前端借助jQuery-EasyUI来实现
在jquery-easyui-1.9.4/themes/icon.css文件尾部添加
.icon-import-excel{
background:url('icons/ImportExcel.png') no-repeat center center;
}上载图标 ImportExcel.png 在这里
前端代码及相关问题
查看input表单控件（type=“file”）有没有获得文件名
input表单控件如下：
<input id="upload" type="file" name="userUploadFile" hidden />
可以发现：
使用id名称（upload）接一个点可以引用js对象属性。
浏览器的console窗口就是个js shell，可以执行命令，甚至可以上下键翻取历史命令。
前端示范代码
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>jQuery EasyUI Demo for Java</title>
<!-- 主题可以通过themes/下的easyui.css进行更换 -->
<link rel="stylesheet" type="text/css" href="jquery-easyui-1.9.4/themes/ui-cupertino/easyui.css">
<link rel="stylesheet" type="text/css" href="jquery-easyui-1.9.4/themes/icon.css">
<link rel="stylesheet" type="text/css" href="jquery-easyui-1.9.4/demo/demo.css">
<script type="text/javascript" src="jquery-easyui-1.9.4/jquery.min.js"></script>
<script type="text/javascript" src="jquery-easyui-1.9.4/jquery.easyui.min.js"></script>
<script type="text/javascript" src="jquery-easyui-1.9.4/locale/easyui-lang-zh_CN.js"></script>
<script type="text/javascript">var url;
function deleteUser() {
var row = $('#dg').datagrid('getSelected');
if (row) {
$.messager.confirm("系统提示", "您确定要删除这条记录吗?", function(r) {
if (r) {
$.post('userDelete', {
delId: row.id
}, function(result) {
if (result.success) {
$.messager.alert("系统提示", "已成功删除这条记录!");
$("#dg").datagrid("reload"); //刷新前端
} else {
$.messager.alert("系统提示", result.errorMsg);
}
}, 'json');
}
});
}
}
function newUser() {
$("#dlg").dialog('open').dialog('setTitle', '添加用户');
$('#fm').form('clear');
url = 'userSave'; //Ajax发送至后端服务器
}
function editUser() {
var row = $('#dg').datagrid('getSelected');
if (row) {
$("#dlg").dialog('open').dialog('setTitle', '编辑用户');
$('#fm').form('load', row); //加载当前行的数据
url = 'userSave?id=' + row.id; //Ajax发送至后端服务器
}
}
function saveUser() {
$('#fm').form('submit', {
url: url, //Ajax发送至后端服务器对应的url
onSubmit: function() {
return $(this).form('validate');
},
success: function(result) {
var result = eval（'(' + result + ')'); //转化为前端JSON
if (result.errorMsg) {
$.messager.alert("系统提示", result.errorMsg);
return;
} else {
$.messager.alert("系统提示", "保存成功");
$('#dlg').dialog('close');
$("#dg").datagrid("reload"); //刷新前端
}
}
});
}
/*
* ajax无法直接接收导出的excel。
* 因为ajax只处理返回的字符流，而后端返回前端的excel是二进制的字节流，ajax无法处理。
* 对于二进制的字节流只能交给浏览器来处理。
*/
function exportUser() {
var searchVal = $('#searchBox').val();
console.log("查找关键字：" + searchVal);
if (searchVal != "") {
//使用window.open()方法，浏览器会“跳窗”，闪一下。
//window.open("exportExcel?searchKey="+searchVal);
window.location.href = "exportExcel?searchKey=" + searchVal;
} else {
//window.open("exportExcel");
window.location.href = "exportExcel";
}
}
function searchUser() {
var searchVal = $('#searchBox').val();
console.log("查找关键字：" + searchVal);
if (searchVal != '') { //查找框有输入
$('#dg').datagrid({
url: 'userList',
queryParams: {
"searchVal": searchVal
}
});
} else { //没有输入则全量显示
$('#dg').datagrid({
url: 'userList',
queryParams: {}
});
}
}
function importUser() {
$("#uploadDlg").dialog('open').dialog('setTitle', '批量导入数据');
}
function downloadTemplate() {
//对应jsp&servlet项目（Dynamic Web Project）的WebContent/template/template.xls
window.open('template/template.xls');
}
function uploadFile() {
console.log("到uploadFile");
$("#uploadForm").form("submit", {
success: function(result) {
//将JSON字符串转成JSON对象
//var result = eval（'(' + result + ')');
result=JSON.parse(result); //注意：JSON.stringify()是将JSON对象转换为字符串，便于网络传输。
if (result.errorMsg) {
$.messager.alert("系统提示", result.errorMsg);
} else {
$.messager.alert("系统提示", "上传成功");
$("#uploadDlg").dialog("close");
$("#dg").datagrid("reload");
}
}
});
}</script>
</head>
<body>
<!-- url属性表示要从Web服务器上请求数据，且与后端交互的是JSON串。交互通过Ajax（XHR）来完成。 -->
<!-- 可以将jQuery EasyUI涉及到的组件都放在这个页面上统一调度。 -->
<table id="dg" title="用户管理" class="easyui-datagrid"style="width:1024px;height:450px" url="userList" toolbar="#toolbar"
pagination="true" rownumbers="true" fitColumns="true" singleSelect="true">
<thead>
<tr>
<th field="id" width="50" hidden="true">编号</th>
<th field="name" width="50">姓名</th>
<th field="phone" width="50">电话</th>
<th field="email" width="50">Email</th>
<th field="qq" width="50">QQ</th>
</tr>
</thead>
</table>
<div id="toolbar">
<div>
<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-add" plain="true" onclick="newUser()">添加用户</a>
<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-edit" plain="true" onclick="editUser()">编辑用户</a>
<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-remove" plain="true" onclick="deleteUser()">删除用户</a>
<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-import-excel" plain="true" onclick="importUser()">批量导入</a>
</div>
<div>
<!-- href=“javascript:void(0);”意为执行一个js的空方法，这样既执行了链接动作，页面又可以不跳转。 -->
<input id="searchBox" type="text"style="border:#87CEFA 1px solid;" />
<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-search" plain="true" onclick="searchUser()">搜索</a>
<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-export-excel" plain="true" onclick="exportUser()">导出用户</a>
</div>
</div>
<div id="dlg" class="easyui-dialog"style="width:400px;height:250px;padding:10px 20px" closed="true" buttons="#dlg-buttons">
<form id="fm" method="post">
<table cellspacing="10px;">
<tr>
<td>姓名：</td>
<td><input name="name" class="easyui-validatebox" required="true"style="width: 200px;"></td>
</tr>
<tr>
<td>联系电话：</td>
<td><input name="phone" class="easyui-validatebox" required="true"style="width: 200px;"></td>
</tr>
<tr>
<td>Email：</td>
<td><input name="email" class="easyui-validatebox" validType="email" required="true"style="width: 200px;"></td>
</tr>
<tr>
<td>QQ：</td>
<td><input name="qq" class="easyui-validatebox" required="true"style="width: 200px;"></td>
</tr>
</table>
</form>
</div>
<div id="dlg-buttons">
<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-ok" onclick="saveUser()">保存</a>
<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-cancel" onclick="javascript:$('#dlg').dialog('close')">关闭</a>
</div>
<div id="uploadDlg" class="easyui-dialog"style="width:400px;height:180px;padding:10px 20px" closed="true" buttons="#upload-btns">
<form id="uploadForm" action="fileUpload" method="post" enctype="multipart/form-data">
<table>
<tr>
<td>下载模版：</td>
<td><a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-download" onclick="downloadTemplate()">下载</a></td>
</tr>
<trstyle="height: 10px;">
</tr>
<tr>
<td>上传文件：</td>
<td><a href="javascript:$('#upload').click();" class="easyui-linkbutton" iconCls="icon-upload">选择文件</a>
<input id="upload" type="file" name="userUploadFile" hidden />
<span id="fileShow"></span>
</td>
</tr>
</table>
</form>
</div>
<div id="upload-btns">
<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-ok" onclick="uploadFile()">上传</a>
<a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-cancel" onclick="javascript:$('#uploadDlg').dialog('close')">关闭</a>
</div>
</body>
<script type="text/javascript">$("#upload").change(function() {
var arrs = $(this).val().split('\\');
var filename = arrs[arrs.length - 1];
$("#fileShow").html(filename);
});</script>
</html>注意，最后</body>和</html>之间的jQuery代码等效于下边的代码：
$(document).ready(function() {
$("#upload").change(function() {
var arrs = $(this).val().split('\\');
var filename = arrs[arrs.length - 1];
$("#fileShow").html(filename);
});
});后端代码及相关内容
工具类新增处理Excel单元格内容格式的代码
public class ExcelUtil {
......
public static String formatCell(HSSFCell hssfCell) {
if (hssfCell == null) {
return "";
}
switch (hssfCell.getCellType()) {
case BOOLEAN:
return String.valueOf(hssfCell.getBooleanCellValue());
case NUMERIC:
DecimalFormat decimalFormat = new DecimalFormat("###################.###########");
String str = decimalFormat.format(hssfCell.getNumericCellValue());
System.out.println(str);
return str;
default:
return String.valueOf(hssfCell.getStringCellValue());
}
}
}这里可以保证double类型的整数没有小数点和其后的零。
关于日期与字符串互转的工具类
public class DateUtil {
// java.util.Date转换为String
public static String formatDate(Date date, String format) {
String result = "";
SimpleDateFormat sdf = new SimpleDateFormat(format);
if (date != null) {
result = sdf.format(date);
}
return result;
}
// String转换为java.util.Date
public static Date formatString(String str, String format) throws Exception {
if (StringUtil.isEmpty(str)) {
return null;
}
SimpleDateFormat sdf = new SimpleDateFormat(format);
return sdf.parse(str);
}
// 取当前时间的字符串形式
public static String getCurrentDateStr() throws Exception {
Date date = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddhhmmss");
return sdf.format(date);
}
}配置web.xml
<servlet> <servlet-name>uploadExcelServlet</servlet-name> <servlet-class>com.bee.web.UserUploadServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>uploadExcelServlet</servlet-name> <url-pattern>/fileUpload</url-pattern> </servlet-mapping>
新增一个处理Excel文件上传的Servlet
package com.bee.web;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.util.Iterator;
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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.tomcat.util.http.fileupload.FileItem;
import org.apache.tomcat.util.http.fileupload.FileItemFactory;
import org.apache.tomcat.util.http.fileupload.FileUploadException;
import org.apache.tomcat.util.http.fileupload.disk.DiskFileItemFactory;
import org.apache.tomcat.util.http.fileupload.servlet.ServletFileUpload;
import org.apache.tomcat.util.http.fileupload.servlet.ServletRequestContext;
import com.bee.dao.UserDao;
import com.bee.model.User;
import com.bee.utils.DBUtil;
import com.bee.utils.DateUtil;
import com.bee.utils.ExcelUtil;
import com.bee.utils.ResponseUtil;
import net.sf.json.JSONObject;
public class UserUploadServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
private DBUtil dbUtil = new DBUtil();
private UserDao userDao = new UserDao();
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doPost(request, response);
}
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
FileItemFactory factory = new DiskFileItemFactory();
ServletFileUpload upload = new ServletFileUpload(factory);
Iterator<FileItem> it = null;
try {
List<FileItem> items = upload.parseRequest(new ServletRequestContext(request));
it = items.iterator();
} catch (FileUploadException e1) {
e1.printStackTrace();
}
String userUploadFile = null;
while (it.hasNext()) {
FileItem item = it.next();
userUploadFile = item.getName();
System.out.println("上载的文件：" + userUploadFile);
if (!item.isFormField() && userUploadFile != null) { // 是上传的文件（二进制数据）
try {
// 取当前日期作为文件名，取上传文件的扩展名为服务器端存储文件的扩展名。
String fileName = DateUtil.getCurrentDateStr();
String extName = userUploadFile.split("\\.")[1];// java中由于转义\变\\
// 在后端访问文件系统只能使用绝对路径
String filePath = "e:/tmp/" + fileName + "." + extName;
System.out.println("上传文件存储在这里 --> " + filePath);
userUploadFile = filePath;
item.write(new File(userUploadFile));
} catch (Exception e) {
e.printStackTrace();
}
}
}
JSONObject result = new JSONObject();
if (userUploadFile == null) {
result.put("errorMsg", "我去，上传失败！");
try {
ResponseUtil.write(response, result);
} catch (Exception e) {
e.printStackTrace();
}
return;
}
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(new File(userUploadFile)));
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet hssfSheet = wb.getSheetAt(0); // 获取第一个Sheet页
if (hssfSheet != null) {
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { // 第二行开始
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow == null) {
continue;
}
// 用户的实体类（Entity/Model）：POJO/DTO/JavaBean
User user = new User();
user.setName(ExcelUtil.formatCell(hssfRow.getCell(0)));
user.setPhone(ExcelUtil.formatCell(hssfRow.getCell(1)));
user.setEmail(ExcelUtil.formatCell(hssfRow.getCell(2)));
user.setQq(ExcelUtil.formatCell(hssfRow.getCell(3)));
Connection con = null;
try {
con = dbUtil.getConnection();
userDao.userAdd(con, user);
} catch (Exception e) {
e.printStackTrace();
} finally {
dbUtil.closeConnection(con);
}
}
}
result.put("success", "true");
try {
ResponseUtil.write(response, result);
} catch (Exception e) {
e.printStackTrace();
}
}
}以上就是“java怎么将Excel文件上载并把数据导入数据库”这篇文章的所有内容，感谢各位的阅读！相信大家阅读完这篇文章都有很大的收获，小编每天都会为大家更新不同的知识，如果还想学习更多的知识，请关注亿速云行业资讯频道。
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
