java上传excel文件及解析
CreateTime--2018年3月5日16:25:14
Author:Marydon
一、准备工作
1.1 文件上传插件：swfupload；
1.2 文件上传所需jar包：commons-fileupload-1.3.1.jar和commons-io-2.2.jar；
1.3 解析excel所需jar包：dom4j-1.6.1.jar,poi-3.8-20120326.jar,poi-ooxml-3.8-20120326.jar,poi-ooxml-schemas-3.8-20120326.jar和xmlbeans-2.3.0.jar
1.4目录结构
二、代码展示
2.1 客户端代码设计
JSP部分
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>excel导入演示</title>
<%@ include file="commons/jsp/include.jsp" %>
<script type="text/javascript" src="<c:url value="/commons/js/swfupload/swfupload.js" />"></script>
<script type="text/javascript" src="index.js"></script>
</head>
<body>
<table>
<tbody>
<tr>
<td align="right">导入排班信息</td>
<td>
<input id="FILENAME" maxlength="256" type="text" class="" readonly/>
<input id="saveFileName" type="hidden"/>
</td>
<td>
<span id="ButtonPlaceholder"></span>
<input onclick="readExcel();" type="button" value="导入"/>
</td>
</tr>
</tbody>
</table>
</body>
</html>
js文件
var uploadItem;
// 页面加载
$(function() {
// 必须是页面加载完毕后，再实例化该对象
uploadItem = new UploadItem();
}); /**
* 导入Excel
* @returns
*/
function readExcel() {
var FILENAME = $("#saveFileName").val();// 上传文件
if(FILENAME == ""){
Dialog.Alert('消息提示',"请点击浏览按钮选择EXCEL文件！",null,null,100);
return;
} var param = "FILENAME=" + FILENAME;//文件名字
$.ajax({
type : 'POST',
url : baseUrl + "/readExcel.do",
data : param,
success : function(result) {
var result = eval("(" + result + ")");
$get('FILENAME').value = "";
$get('saveFileName').value = "";
// 返回执行结果
var returnMsg = result.msg;
if ("数据导入成功！" != result.msg) {
returnMsg = result.expMsg;
}
alert(returnMsg);
}
});
}
/*
* SWFUpload 浏览按钮：上传文件到文件夹
*/
function UploadItem() {
var object = this; this.settings_object = {
flash_url : baseUrl + "/commons/js/swfupload/swfupload.swf",
upload_url : baseUrl + "/uploadExcel.do",
file_post_name : "uploadFile",
post_params:{"test":"测试参数传递"},
file_size_limit : "20 MB",
file_types : "*.xls;*.xlsx",
file_types_description : "excel File",
file_upload_limit : "0", file_queued_handler : fileQueued,// 指定文件上传事件
upload_error_handler : uploadError,// 指定上传异常处理事件
file_queue_error_handler : fileQueueError,//文件上传校验事件异常处理
upload_success_handler : uploadSuccess,// 指定上传成功事件 button_image_url : baseUrl + "/commons/images/browser.gif",
button_placeholder_id : "ButtonPlaceholder",// 根据ID绑定浏览按钮及事件
button_width : 69,
button_height : 21, debug : false
}; this.swfu = new SWFUpload(object.settings_object); /**
* 开始上传
*/
this.startUpload = function () {
object.swfu.startUpload();
}; } function fileQueued(file) {
uploadItem.startUpload();
}; /**
* 上传成功
* @param file
* @param serverData
* @returns
*/
function uploadSuccess(file, result) {
var result = eval("(" + result + ")");
$get("FILENAME").value = result.oldFileName;
$get("saveFileName").value = result.saveFileName;
} function fileQueueError(file, errorCode, message) {
switch (errorCode) {
case -100:
message = "您上传的文件过大！";// QUEUE_LIMIT_EXCEEDED
break;
case -110:
message = "您上传的文件过大！";// FILE_EXCEEDS_SIZE_LIMIT
break;
case -120:
message = "您上传的文件类型不正确！";// ZERO_BYTE_FILE
break;
case -130:
message = "您上传的文件类型格式错误！";// INVALID_FILETYPE
break;
default:
break;
} alert(result.msg);
$get('FILENAME').focus();
}; /**
* 上传失败
* @param file
* @param errorCode
* @returns
*/
function uploadError(file, errorCode) {
var result = eval("(" + errorCode + ")");
alert(result.msg);
};
2.2 服务器端代码设计
文件上传代码
package controller; import java.io.File;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.Calendar;
import java.util.Iterator;
import java.util.List;
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 org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileItemFactory;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload; /**
* Servlet implementation class UploadExcel
*/
@WebServlet("/uploadExcel.do")
public class UploadExcelController extends HttpServlet {
private static final long serialVersionUID = 1L; protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
this.doPost(request, response);
} protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// 指定保存路径
String fileSavePath = "/upload";
String rootPath = this.getServletContext().getRealPath("");
fileSavePath = rootPath + fileSavePath;
// 获取前台传参
String param = request.getParameter("test");
System.out.println("获取前台参数：" + param); // 上传操作
FileItemFactory factory = new DiskFileItemFactory();
ServletFileUpload upload = new ServletFileUpload(factory);
upload.setHeaderEncoding("UTF-8");
String saveFileName = "";
String oldFileName = "";
try {
List items = upload.parseRequest(request);
if (null != items) {
Iterator itr = items.iterator();
while (itr.hasNext()) {
FileItem item = (FileItem) itr.next();
if (!item.isFormField()) {// 文件格式
// 以当前精确到秒的日期为上传的文件的文件名
saveFileName = this.getServerSysDateAndTimeAsCode();
oldFileName = item.getName();
String fileType = oldFileName.substring(oldFileName.lastIndexOf("."));
saveFileName += fileType;
// 空文件对象路径+文件名
File savedFile = new File(fileSavePath, saveFileName);
// 写入
item.write(savedFile);
}
}
} StringBuffer sb = new StringBuffer();
// key和value两边都必须带""
sb.append("{").append("\"oldFileName\"").append(":").append("\"").append(oldFileName).append("\"")
.append(",").append("\"saveFileName\"").append(":").append("\"").append(saveFileName).append("\"").append("}");
// json字符串：文件名称及文件路径
String returnMsg = sb.toString();
System.out.println(returnMsg);
// 返回信息
response.setContentType("text/html; charset=UTF-8");
PrintWriter out = response.getWriter(); // 返回页面
out.print(returnMsg); } catch (Exception e) {
e.printStackTrace();
} } /**
* 获得当前日期【long型】作为文件名称
* @return
*/
public String getServerSysDateAndTimeAsCode() {
String result = null;
long currentTimeInMilis = Calendar.getInstance().getTimeInMillis();
result = String.valueOf(currentTimeInMilis);
return result;
}
}
解析excel package controller; import java.io.File;
import java.io.IOException;
import java.io.PrintWriter;
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 bo.BoExcelImpl;
import bo.IBoExcel; /**
* Servlet implementation class AnalyzeExcelController
*/
@WebServlet("/readExcel.do")
public class AnalyzeExcelController extends HttpServlet {
private static final long serialVersionUID = 1L; protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
this.doPost(request, response);
} protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String FILENAME = request.getParameter("FILENAME");
String msg = "";
String expMsg = "";
IBoExcel boExcel = new BoExcelImpl();
try {
String path = this.getServletContext().getRealPath("/upload");
// 上传excel的绝对路径
path += File.separator + FILENAME;
// 解析excel数据
boolean isSuccess = boExcel.readExcel(path); if (isSuccess) {
msg = "数据导入成功！";
} else {
msg = "数据导入失败！";
} } catch (Exception e) {
expMsg = e.getMessage();
} finally {
StringBuffer sb = new StringBuffer();
sb.append("{").append("\"msg\"").append(":").append("\"").append(msg).append("\"").append(",")
.append("\"expMsg\"").append(":").append("\"").append(expMsg).append("\"").append("}");
// json字符串：文件名称及文件路径
String returnMsg = sb.toString();
System.out.println(returnMsg); // 返回信息
response.setContentType("text/html; charset=UTF-8");
PrintWriter out = response.getWriter(); // 返回页面
out.print(returnMsg);
} } }
业务层
package bo; import java.io.File;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.apache.log4j.Logger;
import tools.ReadExcelUtils; /**
*
* @author Marydon
* @createTime 2018年3月2日下午8:01:07
* @updateTime
* @Email:Marydon20170307@163.com
* @version:1.0.0
*/
public class BoExcelImpl implements IBoExcel {
private Logger log = Logger.getLogger(this.getClass()); @Override
public boolean readExcel(String filePath) throws Exception {
try {
boolean isSuccess = false; ReadExcelUtils excelReader = new ReadExcelUtils(filePath); List<String> columnsList = new ArrayList<String>();
columnsList.add("ORG_ID");
columnsList.add("DEPENT_NAME");
columnsList.add("DOCTOR_NAME");
columnsList.add("DOCTOR_PHONE");
columnsList.add("SCHEDULE_DATE");
columnsList.add("WEEK_TXT");
columnsList.add("WB_TYPE");
columnsList.add("CLOSE_TZ");
columnsList.add("REPLACE_TZ"); // 1.对读取Excel表格内容
List<Map> scheduleList = excelReader.readExcelContent(columnsList);
System.out.println(scheduleList); isSuccess = true; // 删除该上传的文件
File excelFile = new File(filePath);
if (excelFile.exists()) {
excelFile.delete();
}
return isSuccess;
} catch (Exception e) {
log.error(e.getMessage());
throw new RuntimeException(e.getMessage(), e);
}
}
}
效果展示：
excel文件
上传成功
刷新upload目录
导入成功
注意：
1.文章中的Dialog和$get()是自定义封装的方法，无需理会；
2.其中，excel中代表数值的字段，需要改成文本格式，否则解析出来后面会带".0"；
3.关于上面为什么要抛出运行时异常？
在往数据库中批量插入数据时，如果中间插入失败，需要进行回滚。