最近在开发excel数据导入模块,在网上找了一些案例和资料,网上的方法大多是上传excel文件至服务器,后台进行数据比对验证后插入数据库,最后返回导入失败的数据给用户导出,并重新修改整理后再次导入数据库中;本人觉得以上方法存在一些缺点:
1.个人觉得如果数据量过大,页面肯定会一直卡死在那里造成用户不知道程序是不是在工作,导致用户体验差
2.不能实时的显示导入的进度,最好是可以显示导入到多少条了
3.返回导入失败结果后,需要导出错误值,修改后再次重复导入操作,不能直接在系统中将错误值修改后重新导入
针对以上缺点自己基于extjs开发了一套excel数据导入功能模块, 下面先给大家看一下效果图:
经测试,5000条数据导入没有问题,主要难点在于如果记录数较多,采用循环读取数据提交的方法会导致浏览器假死,但是使用递归提交数据可以解决这个问题,以上思路仅供参考,希望高手路过可以提出更好的解决方案.
具体前台代码:
Ext.namespace("EIMS.Main.IMPSys");
EIMS.Main.IMPSys.ImpWindow=function(Par){
this.WorkSheetStore= new Ext.data.JsonStore({//获取excel中的工作表的Store
url : 'AcExcel_getWorkSheet.ac',
baseParams:{FilePath:Par.PhyName+'.'+Par.FileExt},
fields: ['SheetName']
});
var FieldRecord = Ext.data.Record.create([//定义忽略下拉选项Record
{name: 'FieldName', type: 'string'},
{name: 'FieldTitle', type: 'string'},
{name: 'IsHide', type: 'string'}
]);
this.ignoreField=new FieldRecord({//添加忽略下拉选项
FieldName:'Ignore',FieldTitle:'Ignore',IsHide:'1'
});
this.ObjectfieldsDef = new Ext.data.JsonStore({//目标字段store定义
autoDestroy: true,
url: 'AcSubSys_QueryFields.ac',
fields: [{name:'FieldName',mapping:'FieldName'},{name:'FieldTitle',mapping:'FieldTitle'},{name:'IsHide',defaultValue:'1'}],
listeners:{
scope: this,
'load': function(store,records,options){
store.insert(0,this.ignoreField);
}
}
});
this.FieldGrid=new Ext.grid.EditorGridPanel({
sm:new Ext.grid.CheckboxSelectionModel(),
id:'ImportSystem.FieldGrid',
clicksToEdit:2,
scope:this,
store:new Ext.data.JsonStore({
fields:[{name:'SourceCoIndex',mapping:'SourceCoIndex'},
{name:'SourceField',mapping:'SourceField'},
{name:'ObjectFiled',mapping:'ObjectFiled'}]
}),
columns:[new Ext.grid.RowNumberer(),
{id:'SourceCoIndex',hidden:true,dataIndex:'SourceCoIndex'},
{id: 'SourceFiled',hideable:false, header: '源字段', width: 150, sortable: true, dataIndex: 'SourceField'},
{id: 'ObjectFiled',
editor:{
xtype: 'combo',
scope:this,
id:'ImportSystem.ObjField',
valueField : 'FieldName',
displayField :'FieldTitle',
selectOnFocus : true,
forceSelection: true,
mode: 'local',
store :this.ObjectfieldsDef,
resizable:true,
minChars:1,
triggerAction : 'all',
listeners:{
scope:this,
change:function(combo,newValue,oldValue ){
this.ClearFilter(combo.store);
var oldindex = combo.store.find('FieldName',oldValue);
var newindex= combo.store.find('FieldName',newValue);
if(newindex!=-1){
var newRecord=combo.store.getAt(newindex);
if(newRecord.get("FieldName")!="Ignorevalue"){
combo.store.getAt(newindex).set("IsHide",'0');
if(oldindex!=-1){
combo.store.getAt(oldindex).set("IsHide",'1');
}
this.ComboFilter(combo.store);
}
}
}
},
typeAhead: true,
loadingText : 'Loading...'
},
scope:this,
renderer: function(value, metaData, record, rowIndex, colIndex, store){
var Ret='';
this.ClearFilter(this.ObjectfieldsDef);
var index = this.ObjectfieldsDef.find('FieldName',value);
if(index!=-1){
Ret=this.ObjectfieldsDef.getAt(index).data.FieldTitle;
this.ComboFilter(this.ObjectfieldsDef);
record.set("ObjectFiled",value);
}
return Ret;
},
hideable:false,header: '目标字段', width: 150, sortable: true, dataIndex: 'ObjectField'}],
bbar:[{
text:"<s:text name='Button.Submit'/>",
scope:this,
handler: function(){
var v=this.FieldGrid.store.getRange();
var jsonQueryArray=[];
Ext.each(v,function(item){
jsonQueryArray.push("{SourceField:\""+item.get("SourceField")+"\",SourceCoIndex:\""+item.get("SourceCoIndex")+"\",ObjectFiled:\""+item.get("ObjectFiled")+"\"}");
});
jsonQueryArray="["+jsonQueryArray+"]";
Ext.Ajax.request({
url: 'AcExcel_LoadExcelData.ac',
success: function(response, opts) {
var obj = Ext.util.JSON.decode(response.responseText);
Ext.getCmp("IMPSys.Excel.Previewgrid").store.loadData(Ext.util.JSON.decode(response.responseText));
},
failure: function(){},
params: { FieldsStr: jsonQueryArray ,SheetName:Par.SheetName,FilePath:Par.PhyName+'.'+Par.FileExt}
});
}
},{
text: "<s:text name='Button.Reset'/>",
scope:this,
handler: function(){
this.form.form.reset();
}
},{
text: "<s:text name='Button.Cancel'/>",
scope:this,
handler: function(){
this.close();
}
}]
});
this.form=new Ext.FormPanel({
id: 'window_form_import',
name: 'window_form_import',
labelAlign: 'right',
bodyStyle:'padding:5px 5px 0',
border:false,
items:[new Ext.form.ComboBox({
id:'ImportSystem.WorkSheet',
name : 'ImportSystem.WorkSheet',
displayField : 'SheetName',
hiddenName:'value',
selectOnFocus : true,
forceSelection: true,
store :this.WorkSheetStore,
resizable:true,
minChars:3,
triggerAction : 'all',
typeAhead: true,
fieldLabel : "Select Sheet",
anchor:'100%',
listeners : {
scope:this,
'select' : function(thisCombo) {
Par.SheetName=thisCombo.getValue();
Ext.Ajax.request({
url: 'AcExcel_getField.ac',
scope:this,
success: function(response,options){
this.FieldGrid.store.loadData(Ext.util.JSON.decode(response.responseText));
this.ObjectfieldsDef.reload();
},
failure: function(){
Ext.MessageBox.show({
title:"<s:text name='Public.Infor'/>",
msg:action.result.message,
buttons: Ext.MessageBox.OK,
icon:Ext.MessageBox.WARNING
});
},
params: { SheetName: thisCombo.getValue(),FilePath:Par.PhyName+'.'+Par.FileExt }
})
}
}
})]
});
EIMS.Main.IMPSys.ImpWindow.superclass.constructor.call(this,{
title:'导入数据',
id:'IMPSys.Import.window',
modal: true,
shadow:false,
resizable : true,
maximizable: true,
width : 500,
height : 300,
layout : 'border',
items: [
{
region : 'north',
height : 30,
border:false,
items:this.form
},{
region : 'center',
layout : 'fit',
border:false,
bodyStyle:'padding:5px 5px',
items : [this.FieldGrid]
}],
bodyStyle:'padding:5px 5px 0'
});
};
Ext.extend(EIMS.Main.IMPSys.ImpWindow,Ext.Window,{
ClearFilter:function(ComStore){//清除过滤条件
ComStore.snapshot = ComStore.realSnapshot;
delete ComStore.realSnapshot;
ComStore.clearFilter();
},
ComboFilter:function(ComStore){//执行过滤
ComStore.filter('IsHide', '1');
ComStore.realSnapshot = ComStore.snapshot;
ComStore.snapshot = ComStore.data;
}
}
);
//所有控件的父panel容器
EIMS.Main.IMPSys.Panel=function(Content,Config){
EIMS.Main.IMPSys.Panel.superclass.constructor.call(this,{
tbar:[
{
text:"Upload Excel",
iconCls:'search',
scope:this,
handler:function(){
this.Search();
}
},{
text:"Import to system",
scope:this,
handler:function(){
Ext.MessageBox.show({
title:'请等待',
msg:'读取数据中',
width:300,
progress:true,
closable:false //隐藏对话框右上角的关闭按钮,从而禁止用户关闭进度条
});
var Vstore=this.items.item(0).store;
Vstore.filterBy(function(record){
var Result=record.get('Result');
if(Result){
Result=Result.trim();
}
if(Result!="Update success" && Result!="Insert success"){
return true;}else{return false;}
},this);
if(Vstore.getCount()==0){
Ext.MessageBox.hide();
alert("没有可操作记录!");
}else{
this.PostData(0,Vstore);
}
}
}],
closable:true,
layout: 'border',
bodyStyle:'padding:5px',
id:Config.id,
title:Config.title,
items:Content.items,
border:false});
};
Ext.extend(EIMS.Main.IMPSys.Panel,Ext.Panel,{
PostData:function(i,v){//开始提交导入数据
var Rec=v.getRange();
var totalCount=v.getCount();
Ext.Ajax.request({
url: 'AcExcel_importdata.ac',
scope:this,
method:'post',
params:{
TableName:'TblSubSys',
funExt: Ext.util.JSON.encode(Rec[i].data)
},
success: function(response, opts) {
Rec[i].data['Result'] = response.responseText;
Rec[i].commit();
if(++i<totalCount){
Ext.MessageBox.updateProgress(eval(i+1)/totalCount,'正在读取第' + eval(i+1) + '个,一共'+totalCount+'个');
this.PostData(i,v);
}else{
Ext.MessageBox.hide();
}
},
failure: function(){
Ext.MessageBox.hide();
}
});
},
Search:function(){
var win = new Ext.Window({
title : '多文件上传',
width : 500,
height : 300,
resizable : true,
modal: true,
shadow:false,
layout : 'fit',
items : [{
xtype : 'uploadpanel',
uploadUrl : "AcFile_uploadFiles.ac?ClientId=<%=session.getId() %>",
filePostName : 'myUpload', // 这里很重要,默认值为'fileData',这里匹配action中的setMyUpload属性
flashUrl : 'swfupload.swf',
fileSize : '200 MB',
height : 200,
border : false,
fileTypes : '*.xls;*.xlsx', // 在这里限制文件类型:'*.jpg,*.png,*.gif'
fileTypesDescription : '所有文件',
postParams : {
}
}],
listeners:{
close:function(){
var filegrid = Ext.getCmp("IMPSys.File.Previewgrid");
filegrid.store.reload();
}
}
});
win.show();
}
});
//preview grid
EIMS.Main.IMPSys.Grid = function(){
this.sm = new Ext.grid.CheckboxSelectionModel();
this.store=new Ext.data.JsonStore({
fields:[<EIMSTags:ExtStroeMap AFilter="AutoGen='false'" TName="TblSubSys"/>,{name:'Result',mapping:'Result'}]
});
this.columns =[
new Ext.grid.RowNumberer({width:40}),
this.sm,<EIMSTags:ExtColumns TName="TblSubSys" AFilter="AutoGen='false'"/>
,{id:'Result',hideable:false,sortable:true,hidden:false,dataIndex:'Result','header':'Result'}
];
EIMS.Main.IMPSys.Grid.superclass.constructor.call(this,{
margins:'0 0 0 0',
sm: this.sm,
autoScroll:true,
region: 'west',
split: true,
width: 730,
StripeRows:true,
split: true,
id:'IMPSys.Excel.Previewgrid',
loadMask: {msg:'loading,please wait……'},
frame : false,
border:false,
view: new Ext.ux.grid.BufferView({
scrollDelay: false
}),
clicksToEdit : 2,// 设置点击几次才可编辑
trackMouseOver:true})
};
Ext.extend(EIMS.Main.IMPSys.Grid,Ext.grid.EditorGridPanel);
EIMS.Main.IMPSys.FileGrid = function(){
this.store=new Ext.data.Store({
autoLoad : true,
remoteSort:true,
proxy : new Ext.data.HttpProxy({
url : 'AcFile_FileList.ac'
}),
baseParams:{start:0,limit : 100,sort:'UploadTime',dir:'Desc'},
reader : new Ext.data.JsonReader({ //使用JSON传输入数据
root : 'Rows',
totalProperty : 'total'
},
[{
name : "id",
mapping : "id"
},{
name : "FileExt",
mapping : "FileExt"
},{
name:"PhyName",
mapping:"PhyName"
},{
name:"FileName",
mapping:"FileName"
},{
name:"UploadTime",
mapping:"UploadTime",
type:"date",
dateFormat:"Y-m-d H:i:s"
},{
name:"Operator",
mapping:"Operator"
}
]
)});
var fm = Ext.form;
this.sm = new Ext.grid.CheckboxSelectionModel();
// 列模型定义了表格所有列的信息,
// dataIndex 将特定的列映射到数据源(Data Store)中的数据列(在后面创建)
this.columns =[
new Ext.grid.RowNumberer(),
this.sm,
{
id:'id',
header:'id',
dataIndex:'id',
hidden:true
},{
id:'FileExt',
header:'FileExt',
dataIndex:'FileExt',
hidden:true
},{
id:'PhyName',
header:'PhyName',
dataIndex:'PhyName',
hideable:false,
hidden:true
},{
id:'FileName',
header:'文件名',
dataIndex:'FileName',
hideable:true,
hidden:false,
sortable:true
},{
id:'UploadTime',
header:'上传时间',
dataIndex:'UploadTime',
hideable:true,
hidden:false,
sortable:true,
renderer:Ext.util.Format.dateRenderer("Y-m-d H:i:m")
},{
id:'Operator',
header:'Upload user',
dataIndex:'Operator',
hideable:true,
hidden:false,
sortable:true
}];
EIMS.Main.IMPSys.FileGrid.superclass.constructor.call(this,{
margins:'0 0 0 0',
sm: this.sm,
autoScroll:true,
region:'center',
StripeRows:true,
id:'IMPSys.File.Previewgrid',
loadMask: {msg:'loading,please wait……'},
frame : false,
border:false,
listeners:{
rowcontextmenu:function( grid, rowIndex, e ){
e.preventDefault();//关闭右键默认菜单
if (rowIndex < 0) { return; }
var treeMenu = new Ext.menu.Menu({
items: [
{
text: '导入',
handler : function(){
var record = grid.getStore().getAt(rowIndex);
var win = Ext.getCmp('IMPSys.Import.window');
if(win){
win.show();
}else{
new EIMS.Main.IMPSys.ImpWindow({PhyName:record.data.PhyName,FileExt:record.data.FileExt}).show();
}
}
}
]});
treeMenu.showAt(e.getXY());
}},
trackMouseOver:true,
bbar: new Ext.PagingToolbar({
pageSize: 100,
store: this.store,
displayInfo: true,
displayMsg: 'Display {0} - {1}Records /Total:{2}',
emptyMsg: "No Data to display"
})
})
};
Ext.extend(EIMS.Main.IMPSys.FileGrid,Ext.grid.EditorGridPanel,{});
EIMS.Main.IMPSys.CallBackFn=function(Config){
var previewGridobj=new EIMS.Main.IMPSys.Grid();
var FileGridobj=new EIMS.Main.IMPSys.FileGrid();
var obj=new EIMS.Main.IMPSys.Panel({
items:[previewGridobj,FileGridobj]
},Config);
return obj;
};