客户端对象模型之列表数据导出到Excel
1,废话不多话,直接上代码,留着以后做类似功能时Copy一下!有需要的朋友也可以参考一下。
<!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <title></title> <script src="jquery-1.6.2.min.js"></script> <script type="text/javascript"> function exportalltaskdata() { var flag = true; try { folderpath = BrowseFolder(); if (folderpath == null || folderpath == 'null' || folderpath == '') { flag = false; alert("请选择保存路径!"); } } catch (e) { flag = false; alert("请确认只能使用IE导出,并将该站点添加到本地ie信任站点中,IE浏览器要设置工具-Internet选择-安全-可信站点-自定义级别-ActiveX控件和插件-将‘对未标记为可安全执行脚本的ActiveX控件初并执行脚本’选项置为‘启用"); } if (flag) { oldvalue = $("#btnExport").val(); $("#btnExport").attr("disabled", "disabled"); $("#btnExport").val("正在导出..."); try { var xls = new ActiveXObject("Excel.Application"); GetAllTasksWithProject(); } catch (e) { alert("请确保本地安装了Excel"); $("#btnExport").removeAttr("disabled"); $("#btnExport").val(oldvalue); } } } function GetAllTasksWithProject() { var projectid = getQueryString("FilterValue1"); var clientContext = new SP.ClientContext.get_current(); var oList = clientContext.get_web().get_lists().getByTitle('项目工作项'); var queryCertType = new SP.CamlQuery(); queryCertType.set_viewXml( '<View><Query><OrderBy><FieldRef Name=\'StartDate\' Ascending=\'True\'/></OrderBy><Where><Eq><FieldRef Name=\'Project\' LookupId=\'TRUE\' /><Value Type=\'Lookup\'>' + projectid + '</Value></Eq></Where></Query></View>' ); this.wangoListItemCertType = oList.getItems(queryCertType); clientContext.load(wangoListItemCertType); clientContext.executeQueryAsync(Function.createDelegate(this, this.ExportSuccessed), Function.createDelegate(this, this.ExportFailed)); } var orderedtasks = new TaskColliection(); var relationship = new TaskColliection(); var roottask = new TaskColliection(); var alltasks = new TaskColliection(); function MakeTaskOrdered(parentid) { if (relationship.ContainsKey(parentid)) { var children = relationship.get(parentid); for (var i = 0; i < children.size() ; i++) { if (!orderedtasks.ContainsKey(children.keys[i])) { var level = 0; if (orderedtasks.ContainsKey(parentid)) { level = orderedtasks.get(parentid) + 1; } orderedtasks.set(children.keys[i], level); MakeTaskOrdered(children.keys[i]); } } } } function ExportSuccessed(sender, args) { orderedtasks = new TaskColliection(); relationship = new TaskColliection(); roottask = new TaskColliection(); alltasks = new TaskColliection(); var listItemEnumerator = wangoListItemCertType.getEnumerator(); while (listItemEnumerator.moveNext()) { var oListItem = listItemEnumerator.get_current(); var taskid = oListItem.get_item('ID'); var title = oListItem.get_item('Title'); var ParentTask = oListItem.get_item('ParentTask'); if (ParentTask != null && ParentTask != '' && ParentTask != 'null') { var parentid = ParentTask.get_lookupId(); if (!relationship.ContainsKey(parentid)) { var children = new Map(); children.set(taskid, title); relationship.set(parentid, children); } else { var children = relationship.get(parentid) children.set(taskid, title); relationship.set(parentid, children) } } else { if (!relationship.ContainsKey(taskid)) { var children = new Map(); relationship.set(taskid, children); } roottask.set(taskid, title); } alltasks.set(taskid, oListItem) } for (var num = 0; num < roottask.size() ; num++) { if (!orderedtasks.ContainsKey(roottask.keys[num])) { orderedtasks.set(roottask.keys[num], 0); MakeTaskOrdered(roottask.keys[num]); } } var oApplication; var xls = new ActiveXObject("Excel.Application"); xls.Visible = false; var xlBook = xls.Workbooks.Add; var oSheet = xlBook.Worksheets(1); oSheet.Cells(1, 1).value = "工作项名称"; oSheet.Cells(1, 2).value = "开始日期"; oSheet.Cells(1, 3).value = "截止日期"; oSheet.Cells(1, 4).value = "负责人"; oSheet.Cells(1, 6).value = "前置工作项"; oSheet.Cells(1, 5).value = "父级工作项"; oSheet.Cells(1, 10).value = "所属项目"; oSheet.Cells(1, 7).value = "关系类型"; oSheet.Cells(1, 9).value = "任务级别"; oSheet.Cells(1, 8).value = "是否完成"; var projecttitle = ""; for (var i = 0; i < orderedtasks.size() ; i++) { var tasklevelnum = orderedtasks.get(orderedtasks.keys[i]); if (alltasks.ContainsKey(orderedtasks.keys[i])) { var oListItem = alltasks.get(orderedtasks.keys[i]); var title = oListItem.get_item('Title'); var startdate = new Date(oListItem.get_item('StartDate')); var dueDate = new Date(oListItem.get_item('DueDate')); var AssignedTo = oListItem.get_item('AssignedTo'); var Predecessors = oListItem.get_item('Predecessors'); var Status = oListItem.get_item('Status'); var RelationshipType = oListItem.get_item('RelationshipType'); var TaskLevel = oListItem.get_item('TaskLevel'); var Project = oListItem.get_item('Project'); var ParentTask = oListItem.get_item('ParentTask'); var rownum = i + 2; switch (tasklevelnum) { case 1: title = " " + title; break; case 2: title = " " + title; break; case 3: title = " " + title; break; case 4: title = " " + title; break; } oSheet.Cells(rownum, 1).value = title; oSheet.Cells(rownum, 2).value = startdate.getFullYear() + "/" + (startdate.getMonth() + 1) + "/" + startdate.getDate(); oSheet.Cells(rownum, 3).value = dueDate.getFullYear() + "/" + (dueDate.getMonth() + 1) + "/" + dueDate.getDate(); oSheet.Cells(rownum, 4).value = AssignedTo.get_lookupValue(); if (Predecessors != null && Predecessors != 'null' && Predecessors != '') oSheet.Cells(rownum, 6).value = Predecessors.get_lookupValue(); if (ParentTask != null && ParentTask != 'null' && ParentTask != '') oSheet.Cells(rownum, 5).value = ParentTask.get_lookupValue(); oSheet.Cells(rownum, 10).value = Project.get_lookupValue(); projecttitle = Project.get_lookupValue(); oSheet.Cells(rownum, 7).value = RelationshipType; oSheet.Cells(rownum, 9).value = TaskLevel; oSheet.Cells(rownum, 8).value = Status; } } var datenow = new Date(); var filetitle = projecttitle + "_" + datenow.format("yyyyMMddHHmmss") + ".xls"; oSheet.SaveAs(folderpath + filetitle); xls.quit(); alert("导出成功"); $("#btnExport").removeAttr("disabled"); $("#btnExport").val(oldvalue); } function ExportFailed(sender, args) { alert('导出失败,请重试'); $("#btnExport").removeAttr("disabled"); $("#btnExport").val(oldvalue); } function MakeTaskOrdered(parentid) { if (relationship.ContainsKey(parentid)) { var children = relationship.get(parentid); for (var i = 0; i < children.size() ; i++) { if (!orderedtasks.ContainsKey(children.keys[i])) { var level = 0; if (orderedtasks.ContainsKey(parentid)) { level = orderedtasks.get(parentid) + 1; } orderedtasks.set(children.keys[i], level); MakeTaskOrdered(children.keys[i]); } } } } function TaskColliection() { this.keys = new Array(); this.data = new Array(); //添加键值对 this.set = function (key, value) { if (this.data[key] == null) {//如键不存在则身【键】数组添加键名 this.keys.push(key); } this.data[key] = value;//给键赋值 }; //获取键对应的值 this.get = function (key) { return this.data[key]; }; //去除键值,(去除键数据中的键名及对应的值) this.remove = function (key) { this.keys.remove(key); this.data[key] = null; }; //判断键值元素是否为空 this.isEmpty = function () { return this.keys.length == 0; }; //获取键值元素大小 this.size = function () { return this.keys.length; }; this.ContainsKey = function (key) { if (this.data[key] == null) {//如键不存在则身【键】数组添加键名 return false; } else { return true; } } } function BrowseFolder() { var Message = "选择下载路径."; //选择框提示信息 var Shell = new ActiveXObject("Shell.Application"); //var Folder = Shell.BrowseForFolder(0, Message, 0x0040, 0x11); //起始目录为:我的电脑 var Folder = Shell.BrowseForFolder(0, Message, 0); //起始目录为:桌面 var folderpath = ""; if (Folder != null) { folderpath = Folder.Self.Path; // 返回路径 if (folderpath.charAt(folderpath.length - 1) != "\\") { folderpath = folderpath + "\\"; } } return folderpath; } </script> </head> <body> <input name="btnExport" id="btnExport" type="button" value="导出当前项目的所有工作项到Excel" style="position: absolute; right: 10px; top: 0px;" onclick="exportalltaskdata();" /> </body> </html>