动态资料导出导入平台(一)

又来啦,又要说偷懒的主张了。在实际的项目中,资料的导入导出是经常能遇的到的,到底有没有办法可以做成动态的呢? 开发人员开发出来后,剩下的可以将由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;  500px;"><img src="../images/connecting.gif" style=" 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", "
  <script type='text/javascript'>$('loadPic').innerHTML='文件已下载,请保存!';
            $('loadPic').style.display = 'block'; $('hideSearch').style.display='none';
            </script>");
                this.ClientScript.RegisterClientScriptBlock(this.GetType(), "showSaveMsg", "
 <script type='text/javascript'>$('loadPic').innerHTML='文件已下载,请保存!';
   $('loadPic').style.display = 'block'; $('hideSearch').style.display='none';
  </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
        }
    }

}
 
View Code
原文地址:https://www.cnblogs.com/Geton/p/4171431.html