1：引入ajaxfileupload.js
<span style="font-size:12px;"><span style="font-size:12px;">// JavaScript Document2：jsp代码
jQuery.extend({
createUploadIframe: function(id, uri)
{
//create frame
var frameId = 'jUploadFrame' + id;
if(window.ActiveXObject) {
var io = document.createElement('<iframe id="' + frameId + '" name="' + frameId + '" />');
if(typeof uri== 'boolean'){
io.src = 'javascript:false';
}
else if(typeof uri== 'string'){
io.src = uri;
}
}
else {
var io = document.createElement('iframe');
io.id = frameId;
io.name = frameId;
}
io.style.position = 'absolute';
io.style.top = '-1000px';
io.style.left = '-1000px';
document.body.appendChild(io);
return io;
},
createUploadForm: function(id, fileElementId)
{
//create form
var formId = 'jUploadForm' + id;
var fileId = 'jUploadFile' + id;
var form = jQuery('<form action="" method="POST" name="' + formId + '" id="' + formId + '" enctype="multipart/form-data"></form>');
var oldElement = jQuery('#' + fileElementId);
var newElement = jQuery(oldElement).clone();
jQuery(oldElement).attr('id', fileId);
jQuery(oldElement).before(newElement);
jQuery(oldElement).appendTo(form);
//set attributes
jQuery(form).css('position', 'absolute');
jQuery(form).css('top', '-1200px');
jQuery(form).css('left', '-1200px');
jQuery(form).appendTo('body');
return form;
},
ajaxFileUpload: function(s) {
// TODO introduce global settings, allowing the client to modify them for all requests, not only timeout
s = jQuery.extend({}, jQuery.ajaxSettings, s);
var id = s.fileElementId;
var form = jQuery.createUploadForm(id, s.fileElementId);
var io = jQuery.createUploadIframe(id, s.secureuri);
var frameId = 'jUploadFrame' + id;
var formId = 'jUploadForm' + id;
if( s.global && ! jQuery.active++ )
{
// Watch for a new set of requests
jQuery.event.trigger( "ajaxStart" );
}
var requestDone = false;
// Create the request object
var xml = {};
if( s.global )
{
jQuery.event.trigger("ajaxSend", [xml, s]);
}
var uploadCallback = function(isTimeout)
{
// Wait for a response to come back
var io = document.getElementById(frameId);
try
{
if(io.contentWindow)
{
xml.responseText = io.contentWindow.document.body?io.contentWindow.document.body.innerHTML:null;
xml.responseXML = io.contentWindow.document.XMLDocument?io.contentWindow.document.XMLDocument:io.contentWindow.document;
}else if(io.contentDocument)
{
xml.responseText = io.contentDocument.document.body?io.contentDocument.document.body.innerHTML:null;
xml.responseXML = io.contentDocument.document.XMLDocument?io.contentDocument.document.XMLDocument:io.contentDocument.document;
}
}catch(e)
{
jQuery.handleError(s, xml, null, e);
}
if( xml || isTimeout == "timeout")
{
requestDone = true;
var status;
try {
status = isTimeout != "timeout" ? "success" : "error";
// Make sure that the request was successful or notmodified
if( status != "error" )
{
// process the data (runs the xml through httpData regardless of callback)
var data = jQuery.uploadHttpData( xml, s.dataType );
if( s.success )
{
// ifa local callback was specified, fire it and pass it the data
s.success( data, status );
};
if( s.global )
{
// Fire the global callback
jQuery.event.trigger( "ajaxSuccess", [xml, s] );
};
} else
{
jQuery.handleError(s, xml, status);
}
} catch(e)
{
status = "error";
jQuery.handleError(s, xml, status, e);
};
if( s.global )
{
// The request was completed
jQuery.event.trigger( "ajaxComplete", [xml, s] );
};
// Handle the global AJAX counter
if(s.global && ! --jQuery.active)
{
jQuery.event.trigger("ajaxStop");
};
if(s.complete)
{
s.complete(xml, status);
} ;
jQuery(io).unbind();
setTimeout(function()
{ try
{
jQuery(io).remove();
jQuery(form).remove();
} catch(e)
{
jQuery.handleError(s, xml, null, e);
}
}, 100);
xml = null;
};
}
// Timeout checker
if( s.timeout > 0 )
{
setTimeout(function(){
if( !requestDone )
{
// Check to see ifthe request is still happening
uploadCallback( "timeout" );
}
}, s.timeout);
}
try
{
var form = jQuery('#' + formId);
jQuery(form).attr('action', s.url);
jQuery(form).attr('method', 'POST');
jQuery(form).attr('target', frameId);
if(form.encoding)
{
form.encoding = 'multipart/form-data';
}
else
{
form.enctype = 'multipart/form-data';
}
jQuery(form).submit();
} catch(e)
{
jQuery.handleError(s, xml, null, e);
}
if(window.attachEvent){
document.getElementById(frameId).attachEvent('onload', uploadCallback);
}
else{
document.getElementById(frameId).addEventListener('load', uploadCallback, false);
}
return {abort: function () {}};
},
uploadHttpData: function( r, type ) {
var data = !type;
data = type == "xml" || data ? r.responseXML : r.responseText;
// ifthe type is "script", eval it in global context
if( type == "script" )
{
jQuery.globalEval( data );
}
// Get the JavaScript object, ifJSON is used.
if( type == "json" )
{
eval( "data = " + data );
}
// evaluate scripts within html
if( type == "html" )
{
jQuery("<div>").html(data).evalScripts();
}
return data;
},
handleError: function (s, xhr, status, e) {
if (s.error) {
s.error.call(s.context || s, xhr, status, e);
}
if (s.global) {
(s.context ? jQuery(s.context) : jQuery.event).trigger("ajaxError", [xhr, s, e]);
}
},
httpData: function (xhr, type, s) {
var ct = xhr.getResponseHeader("content-type"),
xml = type == "xml" || !type && ct && ct.indexOf("xml") >= 0,
data = xml ? xhr.responseXML : xhr.responseText;
if (xml && data.documentElement.tagName == "parsererror")
throw "parsererror";
if (s && s.dataFilter)
data = s.dataFilter(data, type);
if (typeof data === "string") {
if (type == "script")
jQuery.globalEval(data);
if (type == "json")
data = window["eval"]("(" + data + ")");
}
return data;
}
});
</span></span>
<span style="font-size:12px;"><%@ page language="java" contentType="text/html; charset=UTF-8"3、controller
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta charset="utf-8">
<title></title>
<meta name="keywords" content="">
<meta name="description" content="">
<meta name="author" content="">
<META HTTP-EQUIV="Pragma" CONTENT="no-cache">
<META HTTP-EQUIV="Expires" CONTENT="-1">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="shortcut icon" href="">
</head>
<script src="<%=request.getContextPath()%>/man/js/ajaxfileupload.js"></script>
<%@ include file="/WEB-INF/views/treeogrist.jsp"%>
<script type="text/javascript">
var url = getContextPath()+'/czevent/sendFile.html';
function sendFile(){
var excelFile =$("#file_upload").val();
if(excelFile =='') {
layer.alert("请选择需上传的文件!");
return false;
}
if(excelFile .indexOf('.xls')==-1){
layer.alert("文件格式不正确，请选择正确的Excel文件(后缀名.xls)！");
return false;
}
$.ajaxFileUpload({
url:url,//处理图片脚本
secureuri :false,
fileElementId :'file_upload',//file控件id
type:"POST",
dataType:"json",
async : true,
data:{},
success : function (data,status){
if(data.error=='0'){
layer.alert('产值事件批量导入成功', {icon: 1});
laypageInitCZEventList();
limt();
}else{
layer.alert(data.message,{icon:2});
}
},
});
}
$(function () {
$('#btn').click(function(){
$('#file_upload').click();
});
});
</script>
<body >
<div class="page005">
<div class="row">
<div class="col-xs-3">
<div class="panel">
<div class="panel-body">
<div class="tree structure-left structure-dept">
<div id="jsTree_cztype" class="demo jstree jstree-2 jstree-default jstree-default-responsive" role="tree"></div>
</div>
</div>
</div>
</div>
<div class="col-xs-9">
<div class="panel panel-ui">
<div class="panel-heading">
<form class="form-inline">
<div class="form-group">
<a href="javascript:;" onclick="jkManage.export();" class="export action">事件导出</a>
<a href="javascript:;" id='btn' class="import action" >事件导入</a>
<input id="file_upload" name="file_upload" type="file" onchange="sendFile()" style='display:none;'>
<a href="<%=request.getContextPath()%>/download/excel.html?fileName=下载模板.xls" target=" _blank" class="download action">下载事件模板</a>
</div>
</form>
</div>
<div class="panel-body">
<table class="table table-print-list">
<thead>
<tr>
<th>所属分类</th>
<th>产值事件</th>
<th>产值类型</th>
<th>产值最小值</th>
<th>产值最大值</th>
<th>经办人</th>
<th>审核人</th>
<th>管理</th>
</tr>
</thead>
<tbody id="body_czevent">
</tbody>
</table>
<nav class="text-right">
<ul class="pagination" id="pagination_czevent">
</ul>
</nav>
</div>
</div>
</div>
</div>
</div>
</body>
</html>
</span>
<span style="font-size:12px;">package com.yiqi.controller.czmanager;4、Mapper.java
@Controller
@RequestMapping(value="/czevent")
public class CZEventController extends Component<CzEvent>{
private static String filename="";
private Logger log = Logger.getLogger(CZEventController.class);
/**
* 上传文件到服务器
* @param mvc
* @param request
* @param response
* @throws Exception
*/
@RequestMapping(value="sendFile")
public void sendFile (ModelAndView mvc,HttpServletRequest request,HttpServletResponse response) throws Exception {
request.setCharacterEncoding("utf-8"); // 设置编码
response.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=UTF-8");
//文件保存路径
String fileSavePath = request.getServletContext().getRealPath("/");//D:\apache-tomcat-7.0.57\webapps\yiqimanager-web
fileSavePath = fileSavePath.substring(0,fileSavePath.indexOf("webapps")+7) + File.separator + "excel" + File.separator;
ReturnJson json = new ReturnJson();
UserInfo userInfo = this.getUser(request);
String enterpriseid=userInfo.getEnterpriseid();
// 获得磁盘文件条目工厂
DiskFileItemFactory factory = new DiskFileItemFactory();
File file = new File(fileSavePath);
if(!file.exists()){
file.mkdirs();
}
// 如果没以下两行设置的话，上传大的 文件 会占用 很多内存，
// 设置暂时存放的 存储室 , 这个存储室，可以和 最终存储文件 的目录不同
factory.setRepository(file);
// 设置 缓存的大小，当上传文件的容量超过该缓存时，直接放到 暂时存储室
factory.setSizeThreshold(1024 * 1024);
// 高水平的API文件上传处理
ServletFileUpload upload = new ServletFileUpload(factory);
try {
// 请自行组织代码
List<FileItem> list = upload.parseRequest(request);
// 获取上传的文件
FileItem item = getUploadFileItem(list);
// 获取文件名
filename = getUploadFileName(item);
System.out.println("存放目录:" + fileSavePath);
System.out.println("文件名:" + filename);
// 真正写到磁盘上
item.write(new File(fileSavePath, filename)); // 第三方提供的
} catch (FileUploadException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}finally{
String path=fileSavePath+filename;
json.setError(Constants.JSON_SUCCESS);
resolveExcel(path,enterpriseid,json);
ResponseWriteUtils.returnAjax(response, json);
}
}
private FileItem getUploadFileItem(List<FileItem> list) {
for (FileItem fileItem : list) {
if(!fileItem.isFormField()) {
return fileItem;
}
}
return null;
}
private String getUploadFileName(FileItem item) {
// 获取路径名
String value = item.getName();
// 索引到最后一个反斜杠
int start = value.lastIndexOf("\\");
// 截取 上传文件的 字符串名字，加1是 去掉反斜杠，
String filename = value.substring(start + 1);
return filename;
}
/**
* 解析excel文件入庫
* @param args
*/
private void resolveExcel(String filePath,String enterpriseid,ReturnJson json) throws Exception {
Excel excel =new Excel();
Iterator<Row> it =excel.readExcel(filePath);
//初始化批量新增/更新数据
List<CzEvent> czEventList=new ArrayList<CzEvent>();
String regex = ",|，|\\s+";
while (it.hasNext()) {
CzEvent czEvent = new CzEvent();
CzType czType = new CzType();
czEvent.setCreatetime(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
Row row = it.next();
//如果第一列为空不读
if(StringUtils.isBlank(excel.getCellFormatValue(row.getCell(0)))){
continue;
}
int cellCount = row.getPhysicalNumberOfCells();
for (int i = 0; i < cellCount; i++) {
Cell cell = row.getCell(i);
//获取每一行没一列的值，从第二行开始就是我们需要的数据，需要在这里set进对应的实体
String cellValue = excel.getCellFormatValue(cell);
if(row.getRowNum()>=2 && !StringUtils.isBlank(cellValue)){
if (i==0) {//获取第一列的数据
//产值事件大类
List<CzType> listType = czTypeService.queryCZnameList(cellValue,enterpriseid);
if (listType.size()<1) {
json.setError(Constants.JSON_ERROR);
json.setMessage("【"+cellValue+"】"+"产值类型不存在");
return;
}else{
String jktypeiddl = listType.get(0).getCztypeid().substring(0, 1);
czEvent.setCzparentid(jktypeiddl);
czType.setCztypeid(listType.get(0).getCztypeid());
}
}
if(i==1){//获取第二列的数据
czEvent.setCzparentname(cellValue);
}
if(i==2){//获取第三列的数据
//事件名称
czEvent.setEventname(cellValue);
}
if(i==3){//获取第四列的数据
//产值类型
if("实产值".equals(cellValue)){
czEvent.setCztypeid("1");
}else if("虚产值".equals(cellValue)){
czEvent.setCztypeid("2");
}else if ("创富产值".equals(cellValue)) {
czEvent.setCztypeid("3");
}else {
json.setError(Constants.JSON_ERROR);
json.setMessage("【"+cellValue+"】"+"产值类型不正确");
return;
}
}
if(i==4){//获取第五列的数据
//产值最小值
czEvent.setCzmin(cellValue);
}
if(i==5){//获取第六列的数据
//产值最大值
czEvent.setCzmax(cellValue);
}
if(i==6){//获取第七列的数据
//经办人
}
if(i==7){//获取第八列的数据
//审核人
}
if(i==8){ //获取第九列的数据
//备注
czEvent.setRemark(cellValue);
}
}
}
if(row.getRowNum()>=2){
if(!BeanUtils.isEmObject(czEvent)){
String num = commonDao.getSeqNextvalByName("cz_czeventid");
String czid = JournalSeq.getTableBusinessId("C", 7, num);
czEvent.setCzid(czid);
czEvent.setOrgid(enterpriseid);
czEvent.setStatus('1');
//自动填写经办人和审核人
czType = czTypeService.queryCZTypeById(czEvent.getTypeid());
czEvent.setOperatid(czType.getOperatid());
czEvent.setCheckedid(czType.getCheckedid());
czEvent.setInitEvent('0');
czEventList.add(czEvent);
}
}
}
czEventService.insertBatchEvent(czEventList);//批量插入数据
}
}
</span>
<span style="font-size:12px;">package czmanager;
@Repository
public interface CzEventMapper {
//批量导入事件 信息
public void insertBatchEvent(@Param("list")List<CzEvent> list);
}</span>
5、Mapper.xml
<span style="font-size:12px;"><insert id="insertBatchEvent" parameterType="com.yiqi.dao.entity.JkEvent" >
insert into CZ_CZEVENT(czId, czParentId, czTypeId, eventName,
operatId, checkedId, remark,typeId,orgId,czmin,czmax,createTime,status,initEvent)
values
<foreach collection="list" item="item" index="index"
separator=",">
(#{item.czid},
#{item.czparentid},
#{item.cztypeid},
#{item.eventname},
#{item.operatid},
#{item.checkedid},
#{item.remark},
#{item.typeid},
#{item.Orgid},
#{item.czmin},
#{item.czmax},
#{item.createtime},
#{item.status},
#{item.initEvent}
)
</foreach>
</insert></span>
思路分析：
先下载上传文件模板→写入数据→点击导入→发送请求到controller→读取excel表格每一列的数据→调用service层→批量插入数据到数据库