1.所需jar
commons-fileupload-x.x.x.jar
commons-io-x.x.x.jar
2.思路
a).jsp页面,用于上传Excel文件
b).后台读取Excel文件
c).保存到数据库
3.代码实现
a).jsp页面
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>jxl导入Excel文件</title>
<script type="text/javascript"
src="<c:url value="/js/jquery-1.7.2.min.js"/>"></script>
<script type="text/javascript" src="<c:url value="/js/jquery.form.js"/>"></script>
<script type="text/javascript">
$(document).ready(function() {
$('#btnBatch').click(function(){
//检验导入的文件是否为Excel文件
var file = document.getElementById("file").value;
if(file == null || file == ''){
alert('请选择要上传的Excel文件');
return;
}else{
var fileExtend = file.substring(file.lastIndexOf('.')).toLowerCase();
if(fileExtend == '.xls'){
$('#excelForm').ajaxSubmit({
dataType:'json',
success: function(data) {
if(data.flag == 'success') {
alert('导入成功');
}
}
});
}else{
alert('文件格式需为\'.xls\'格式');
return;
}
}
});
});
</script>
</head>
<body>
<form id="excelForm" encType="multipart/form-data" method="post"
action="<c:url value="/excel/excelBatch.do"/>">
<input type="file" id="file" name="file" />
<input type="button" id="btnBatch" value="导入">
</form>
</body>
</html>
b.)后台处理
Excel格式:
/**
* 读取处理Excel
* */
@RequestMapping(value = "/excelBatch.do")
public void excelBatch(@RequestParam("file") MultipartFile file,
HttpServletResponse response, HttpServletRequest request)
throws Exception {
ByteArrayInputStream is = null;
try {
//读取流文件
is = new ByteArrayInputStream(file.getBytes());
// 打开文件
Workbook book = Workbook.getWorkbook(is);
// 得到第一个工作表对象
Sheet sheet = book.getSheet(0);
// 得到第一个工作表中的总行数
int rowCount = sheet.getRows();
Map<String, String> map = new HashMap<String, String>();
if (rowCount > 1) {
// 循环取出Excel中的内容
for (int i = 1; i < rowCount; i++) {
Cell[] cells = sheet.getRow(i);
String col1 = cells[0].getContents();//数据1
String col2 = cells[1].getContents();//数据2
String col3 = cells[2].getContents();//数据3
// 保存到数据库...
}
map.put("flag", "success");
writerJson(response, map);
}
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
is.close();
}
}
public static void writerJson(HttpServletResponse response, String jsonStr) {
writer(response, jsonStr);
}
public static void writerJson(HttpServletResponse response, Object object) {
try {
response.setContentType("application/json");
writer(response, JSONUtil.toJSONString(object));
} catch (JSONException e) {
e.printStackTrace();
}
}
private static void writer(HttpServletResponse response, String str) {
try {
StringBuffer result = new StringBuffer();
// 设置页面不缓存
response.setHeader("Pragma", "No-cache");
response.setHeader("Cache-Control", "no-cache");
response.setCharacterEncoding("UTF-8");
response.setContentType("text/html");
PrintWriter out = null;
out = response.getWriter();
out.print(str);
out.flush();
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}