动态资料导出导入平台（一）
又来啦，又要说偷懒的主张了。在实际的项目中，资料的导入导出是经常能遇的到的，到底有没有办法可以做成动态的呢？ 开发人员开发出来后，剩下的可以将由HELPDESK组去维护，后续如果还要增加类似的功能，HELPDESK组人员就自己可以去处理，而我们开发人员可以不用处理，节省一些不必要的时间，或者可以花更多的时间到某件事情上，而不必受过多的打扰呢。答案是有的，接下我们先讲一下导出的实现，导出在这里相对简单一点。导入将放到后面的随笔。这里的导入导出与网上大多数人的ExcelHelper或者COM形式的导出还是有比较大的区别。
动态，所谓的动态，就是很容易的就实现数据源的切换，还有某数据源其中的一部分数据。这里我们主要用到EAI接口，按指定格式导入书写XML，然后读取这个XML以获得动态的数所输出。实现起来也不会太难。
1.前台页面设计。
从别的画面到跳出这个导出面面，只需要指定相应的XML
function btn_ExUR() {
window.open('/ImportAndExport/SelectFields.aspx?loadXml=UserRightMapping', 'ExportSelFields', 'top=' + (screen.height - 525) / 2 + ', left=' + (screen.width - 820) / 2 + ', width=820, height=525, toolbar=no, menubar=no, scrollbar=no, resizable=no, location=no, status=no')
}
运行后按XML动态产生字段。
然后用户可以动态选择所需要的导出字段。
导出结果如下
下面大家看一下XML到底是如何构成的。
<?xml version="1.0" encoding="utf-8" ?> <EAI xmlns="urn:mapping-schema" from="SqlServerDataBase" to="ExcelFile"> <Source> <table name="CENTRAL_USERIGHT" dataSourceName="SqlServer" handType="EAI.DataBaseHandler.SqlServerExport"> <fields> <field name="SUSER" mapping="员工帐号" dbType="Char"/> <field name="MODNAME" mapping="模组" dbType="Char"/> <field name="CANREAD" mapping="只读" dbType="Bit"/> <field name="CANINS" mapping="新增" dbType="Bit"/> <field name="CANUPD" mapping="修改" dbType="Bit"/> <field name="CANDEL" mapping="删除" dbType="Bit"/> </fields> </table> </Source> <Target> <file range="A2" dataSourceName="Test.xls" name="Sheet1" titleLine="1" autoGenColumnTitle="false" handType="EAI.FileHandler.ExcelImport"> <fields> <field name="SUSER" mapping="员工帐号" primaryKey="true" canNull="false" dbType="Char" length="20"/> <field name="MODNAME" mapping="模组" canNull="false" dbType="NChar" length="255"/> <field name="CANREAD" mapping="只读" dbType="Bit" defaultValue="0" replace="是,1|否,0|是,TRUE|否,FALSE"/> <field name="CANINS" mapping="新增" dbType="Bit" defaultValue="0" replace="是,1|否,0|是,TRUE|否,FALSE"/> <field name="CANUPD" mapping="修改" dbType="Bit" defaultValue="0" replace="是,1|否,0|是,TRUE|否,FALSE"/> <field name="CANDEL" mapping="删除" dbType="Bit" defaultValue="0" replace="是,1|否,0|是,TRUE|否,FALSE"/> </fields> </file> </Target> </EAI>
大家一看，相信也不难理解。后续如果用户还需要导出其它数据，HELPDESK人员可以按XML做一个类似的文档，同时在前台写个按钮直接把XML参数传给这个SelectFields页面就可以了。开发人员不需再管任何事情。
下面把SelectFields页面的后台代码也帖出。如果有人想要EAI的动态库的话，可以留下邮箱。如果方便，顺便给个赞呗。
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.SessionState; using System.Web.Profile; using System.Text; using System.IO; namespace WebApp.ImportAndExport { public partial class SelectFields : WebBase, IRequiresSessionState { // Fields private const string btnHideSearch = "<div id='hideSearch' style=\"height:25px; position:absolute; z-index:-20px;\"><input type=\"button\" name=\"btnSearch\" id=\"btnSearch\" value=\"搜索\" class=\"SUB-BUTTON\" style=\"visibility:hidden;\" onclick=\"btnSearchClick()\"/></div>"; private const string btnShowSave = "<div id='showSave' style=\"height:25px;\"><input type=\"button\" name=\"btnSave\" id=\"btnSave\" value=\"保存\" class=\"SUB-BUTTON\" style=\"visibility:visible; background-color:#54bd24; color:#FFFFFF; font-weight:bold\" onclick=\"btnSaveClick()\"/></div>"; private const string btnShowSearch = "<div style=\"height:25px\"><input type=\"button\" name=\"btnSearch\" id=\"btnSearch\" value=\"搜索\" class=\"SUB-BUTTON\" style=\"visibility:visible;\" onclick=\"btnSearchClick()\"/></div>"; private string compareKey; private string compareValue; private Dictionary<string, string> dicChkSel = new Dictionary<string, string>(); private Dictionary<string, string> dicMap; private const string disabledCheckBox = "<li style=\"height:22px\"><input type=\"checkbox\" name=\"{0}\" id=\"{0}\" checked=\"checked\" disabled=\"true\"/><label for=\"{0}\" class=\"font-12\">{1}</label></li>"; private const string enabledCheckBox = "<li style=\"height:22px\"><input type=\"checkbox\" name=\"{0}\" id=\"{0}\" checked=\"checked\"/><label for=\"{0}\" class=\"font-12\">{1}</label></li>"; private string equalKey; private string equalValue; private string jField; private string jTable; private string loadXml; private string tField; private const string unselDisabledCheckBox = "<li style=\"height:22px\"><input type=\"checkbox\" name=\"{0}\" id=\"{0}\" disabled=\"true\"/><label for=\"{0}\" class=\"font-12\">{1}</label></li>"; private string whereCase; private XmlFieldsToTable xmlFieldsToTable; // Methods protected void BuildPage(BtnType btnType) { string str = " <div id=\"loadPic\" class=\"font-12\" style=\"display:none; color:#336699; float:left; width: 500px;\"><img src=\"../images/connecting.gif\" style=\"width: 140px; height: 13px\" />。下载中。</div>"; StringBuilder builder = new StringBuilder(); if (btnType == BtnType.Search) { builder.Append("<div style=\"height:25px\"><input type=\"button\" name=\"btnSearch\" id=\"btnSearch\" value=\"搜索\" class=\"SUB-BUTTON\" style=\"visibility:visible;\" onclick=\"btnSearchClick()\"/></div>"); builder.Append(str); } else { builder.Append("<div id='hideSearch' style=\"height:25px; position:absolute; z-index:-20px;\"><input type=\"button\" name=\"btnSearch\" id=\"btnSearch\" value=\"搜索\" class=\"SUB-BUTTON\" style=\"visibility:hidden;\" onclick=\"btnSearchClick()\"/></div>"); builder.Append("<div id='showSave' style=\"height:25px;\"><input type=\"button\" name=\"btnSave\" id=\"btnSave\" value=\"保存\" class=\"SUB-BUTTON\" style=\"visibility:visible; background-color:#54bd24; color:#FFFFFF; font-weight:bold\" onclick=\"btnSaveClick()\"/></div>"); builder.Append(str); } builder.Append("<div style=\"margin:0px 0px 0px 0px; background-color:#EFEFEF\"><ul>"); this.xmlFieldsToTable = new XmlFieldsToTable(base.Server.MapPath("~/XmlMapping/" + this.loadXml + ".xml"), SelXmlNodeMode.TargetNode, SelXmlNodeInfo.MappingOnly); this.dicMap = this.xmlFieldsToTable.DicMap; if (btnType == BtnType.Search) { foreach (string str2 in this.dicMap.Keys) { builder.Append(string.Format("<li style=\"height:22px\"><input type=\"checkbox\" name=\"{0}\" id=\"{0}\" checked=\"checked\"/><label for=\"{0}\" class=\"font-12\">{1}</label></li>", str2.ToString(), this.dicMap[str2.ToString()])); } } else { if (this.Session["selectCheckList"] != null) { this.dicChkSel = this.Session["selectCheckList"] as Dictionary<string, string>; } foreach (string str3 in this.dicMap.Keys) //保存按钮时，所有的选项均为不可以选 { if (this.dicChkSel.ContainsKey(str3.ToString())) { builder.Append(string.Format("<li style=\"height:22px\"><input type=\"checkbox\" name=\"{0}\" id=\"{0}\" checked=\"checked\" disabled=\"true\"/><label for=\"{0}\" class=\"font-12\">{1}</label></li>", str3.ToString(), this.dicMap[str3.ToString()])); } else { builder.Append(string.Format("<li style=\"height:22px\"><input type=\"checkbox\" name=\"{0}\" id=\"{0}\" disabled=\"true\"/><label for=\"{0}\" class=\"font-12\">{1}</label></li>", str3.ToString(), this.dicMap[str3.ToString()])); } } } builder.Append("</ul></div>"); //this.Page.RegisterClientScriptBlock("createControl", builder.ToString()); this.ClientScript.RegisterClientScriptBlock(this.GetType(), "createControl", builder.ToString()); } protected string ExecAction(string whereCase, string loadXml, Dictionary<string, string> excludeDic) { ExportSearch search = new ExportSearch(whereCase, loadXml, excludeDic, new RowFilterDictionary(this.tField, this.jTable, this.jField, this.compareKey, this.compareValue, this.equalKey, this.equalValue).RowFilterList); string importExcelDataTable = search.GetImportExcelDataTable(); this.Session["OutputFileName"] = search.Name; return importExcelDataTable; } protected void Page_Load(object sender, EventArgs e) { // 其它通过网页前台实现。 // base.Request.Form["ThisFormJustForSelectFields"] 这个是网页前台提交的值 this.Page.Title = "XXX Crop : " + this.Page.Title; this.SetContextTitle(); if ((base.Request.Form["ThisFormJustForSelectFields"] == null) && (this.Session["SelectFieldsToExportPath"] == null)) { //1. 第一次生成搜索网页 this.loadXml = (base.Request.QueryString["loadXml"] == null) ? "" : base.Request.QueryString["loadXml"]; this.BuildPage(BtnType.Search); } else if ((base.Request.Form["ThisFormJustForSelectFields"] != null) && (this.Session["SelectFieldsToExportPath"] != null)) { //4.下载所需要的文件 string path = base.Server.MapPath("~/export/" + HttpContext.Current.User.Identity.Name + this.Session["SelectFieldsToExportPath"].ToString() + ".xls"); if (File.Exists(path)) { FileInfo info = new FileInfo(path); base.Response.ClearHeaders(); base.Response.ClearContent(); base.Response.ContentType = "APPLICATION/OCTET-STREAM"; base.Response.AppendHeader("Content-Disposition", string.Format("Attachment; Filename=\"{0}\"", this.Session["SelectFieldsToExportPath"].ToString() + ".xls")); base.Response.AppendHeader("Content-Length", info.Length.ToString()); base.Response.Flush(); base.Response.WriteFile(info.FullName); this.Session["selectCheckList"] = null; this.Session["SelectFieldsToExportPath"] = null; base.Response.End(); } } else if ((base.Request.Form["ThisFormJustForSelectFields"] == null) && (this.Session["SelectFieldsToExportPath"] != null)) { //3.生成保存网页，供用户可以下载生成的表格 this.loadXml = (base.Request.QueryString["loadXml"] == null) ? "" : base.Request.QueryString["loadXml"]; this.BuildPage(BtnType.Save); //this.Page.RegisterClientScriptBlock("showSaveMsg", "\r\n <script type='text/javascript'>$('loadPic').innerHTML='文件已下载，请保存！';\r\n $('loadPic').style.display = 'block'; $('hideSearch').style.display='none';\r\n </script>"); this.ClientScript.RegisterClientScriptBlock(this.GetType(), "showSaveMsg", "\r\n <script type='text/javascript'>$('loadPic').innerHTML='文件已下载，请保存！';\r\n $('loadPic').style.display = 'block'; $('hideSearch').style.display='none';\r\n </script>"); } else { //2.按照选择的字段生成文件，同时再跳转回目前网页 转到第3步 this.loadXml = (base.Request.QueryString["loadXml"] == null) ? "" : base.Request.QueryString["loadXml"]; this.whereCase = (this.Session["SearchSqlString"] == null) ? "" : this.Session["SearchSqlString"].ToString(); this.tField = (base.Request.QueryString["tField"] == null) ? "" : base.Request.QueryString["tField"].ToString(); this.jTable = (base.Request.QueryString["jTable"] == null) ? "" : base.Request.QueryString["jTable"].ToString(); this.jField = (base.Request.QueryString["jField"] == null) ? "" : base.Request.QueryString["jField"].ToString(); this.compareKey = (base.Request.QueryString["compareKey"] == null) ? "" : base.Request.QueryString["compareKey"].ToString(); this.compareValue = "";// base.userpositionid; this.equalKey = (base.Request.QueryString["equalKey"] == null) ? "" : base.Request.QueryString["equalKey"].ToString(); this.equalValue = base.userinfo.Trim().ToLower(); this.xmlFieldsToTable = new XmlFieldsToTable(base.Server.MapPath("~/XmlMapping/" + this.loadXml + ".xml"), SelXmlNodeMode.TargetNode, SelXmlNodeInfo.MappingOnly); this.dicMap = this.xmlFieldsToTable.DicMap; foreach (string str2 in base.Request.Form.AllKeys) { if (this.dicMap.ContainsKey(str2)) { this.dicMap.Remove(str2); this.dicChkSel.Add(str2, ""); } } this.Session["selectCheckList"] = this.dicChkSel; StringBuilder builder = new StringBuilder(); builder.Append(this.ExecAction(this.whereCase, this.loadXml, this.dicMap)); base.DeleteTemp(); this.Session["SelectFieldsToExportPath"] = DateTime.Now.Ticks.ToString(); FileStream stream = new FileStream(base.Server.MapPath("~/export/" + HttpContext.Current.User.Identity.Name + this.Session["SelectFieldsToExportPath"].ToString() + ".xls"), FileMode.Create); StreamWriter writer = new StreamWriter(stream, Encoding.Unicode); writer.Write(builder.ToString()); writer.Flush(); writer.Close(); stream.Close(); HttpContext.Current.Response.Redirect(HttpContext.Current.Request.Url.ToString()); } } protected void SetContextTitle() { if (this.Session["ExportContextTitle"] == null) { this.Label3.Text = "员工权限表"; //员工基本资料 } else { this.Label3.Text = this.Session["ExportContextTitle"].ToString(); } } // Nested Types protected enum BtnType { Search, Save } } }
