把旧系统迁移到.Net Core 2.0 日记（8) - EASYUI datagrid+ Dapper+ 导出Excel

迁移也没太大变化,有一个, 之前的Request.QueryString 是返回NameValueCollection, 现在则是返回整个字符串. 你要改成Request.Query[“key”]

直接上代码吧.

  1 @using FoxCRMCore
  2 @{
  3     var controller = "CRM/Announcement";
  4     ViewBag.Title = "公告信息";
  5 }
  6 
  7 
  8     <script type="text/javascript" language="javascript">
  9 
 10         $(function () {
 11 
 12             $('#grid').datagrid({
 13                 title: '@ViewBag.Title',
 14                 iconCls: 'icon-blank',
 15                 nowrap: false,
 16                 striped: true,
 17                 url: '/@controller/ListByPage',
 18                 sortName: 'cDate',
 19                 sortOrder: 'desc',
 20                 remoteSort: true,
 21                 fitColumns: true,
 22                 fit: true,
 23                 idField: 'id',
 24                 frozenColumns: [[
 25                     { field: 'id', checkbox: true, width: 50, sortable: true },
 26                     { field: 'OPERATION', title: '编辑', width: 50, formatter:
 27                         function (value, row, index) {
 28 
 29                             var edit = '<a href="/@controller/View/' + row.id + '">编辑</a> ';
 30                             return edit;
 31                         }
 32                     }
 33                 ]],
 34 
 35                 columns: [[
 36                     { field: 'subject', title: '标题', width: 150, align: 'right', sortable: true },
 37                     { field: 'contentDesc', title: '内容', width: 500, align: 'left', sortable: true },
 38                     { field: 'cDate', title: '创建时间', width: 120, align: 'right', sortable: true },
 39                     { field: 'modifyDate', title: '修改时间', width: 120, align: 'right', sortable: true },    
 40                     { field: 'isActive', title: '是否有效', width: 50, align: 'right', sortable: true }
 41                 ]],
 42                 onDblClickRow: function (index, data) {
 43                     var row = $(this).datagrid('getRows')[index];
 44                     window.location = "/@controller/View/" + row.id;
 45                 },
 46                 pagination: true,
 47                 pageSize: 10,
 48                 rownumbers: true,
 49                 toolbar: "#dlg-toolbar"
 50             });
 51 
 52             $('#grid').datagrid('gotoPage', 2);
 53         });
 54 
 55         //SearchBox传value过来,不能用$('#txtKey').val()
 56         function Search(value, name) {
 57             $('#grid').datagrid('load', { "key": "Air", "value": value });
 58         }
 59         function Add() {
 60             window.location = "/@controller/View/";
 61         }
 62         function Edit() {
 63 
 64             var row = $('#grid').datagrid('getSelected');
 65             if (row) {
 66                 window.location = "/@controller/View/" + row.id;
 67             }
 68             else {
 69                 $.messager.alert('提示', '请选择要修改的数据');
 70                 return;
 71             }
 72         }
 73         function Delete() {
 74             var rows = $('#grid').datagrid('getSelections');
 75             if (!rows || rows.length == 0) {
 76                 $.messager.alert('提示', '请选择要删除的数据');
 77                 return;
 78             }
 79             var parm;
 80             $.each(rows, function (i, n) {
 81                 if (i == 0) {
 82                     parm = "idList=" + n.id;
 83                 }
 84                 else {
 85                     parm += "&idList=" + n.id;
 86                 }
 87             });
 88             $.messager.confirm('提示', '是否删除这些数据?', function (r) {
 89                 if (!r) {
 90                     return;
 91                 }
 92 
 93                 $.ajax({
 94                     type: "POST",
 95                     url: "/@controller/Delete/",
 96                     data: parm,
 97                     success: function (msg) {
 98                         if (msg.IsSuccess) {
 99                             $.messager.alert('提示', '删除成功！', "info", function () {
100                                 $('#grid').datagrid("reload");
101                             });
102                         }
103                     },
104                     error: function () {
105                         $.messager.alert('错误', '删除失败！', "error");
106                     }
107                 });
108             });
109         }
110         
111     </script>
112 
113 
114     <div region="center" style="padding: 5px;" border="false">
115         <table id="grid">
116         </table>
117     </div>
118     <div id="dlg-toolbar" style="padding: 2px 0;display:none">
119         <table cellpadding="0" cellspacing="0" style="width: 100%">
120             <tr>
121                 <td style="padding-left: 2px">
122                     <a id="btnSave" href="javascript:Add();" class="easyui-linkbutton" data-options="iconCls:'icon-add',plain:true">
123                         添加</a> @*<a id="btnUpdate" href="javascript:Edit();" class="easyui-linkbutton" data-options="iconCls:'icon-save',plain:true">
124                             修改</a> <a id="btnDelete" href="javascript:Delete();" class="easyui-linkbutton" data-options="iconCls:'icon-cut',plain:true">
125                                 删除</a>*@
126                     <input id="txtKey" class="easyui-searchbox" data-options="prompt:'请输入查询条件',searcher:Search" style="width: 250px" />
127                 </td>
128             </tr>
129         </table>
130     </div>

列表页Index.csHtml

Controller的ListByPage 方法,给EASYUI 的datagrid 调用, 如果要把过滤条件,排序等动态传到LINQ,可以使用一个微软提供的DynamicQueryable,

https://blog.csdn.net/lee576/article/details/43666969

这个类在.net core需要修改一下.参考https://stackoverflow.com/questions/41784393/how-to-emit-a-type-in-net-core

//AssemblyBuilder assembly = AppDomain.CurrentDomain.DefineDynamicAssembly(name, AssemblyBuilderAccess.Run);  //NET CORE 方法变了.

AssemblyBuilder assembly =AssemblyBuilder.DefineDynamicAssembly(new AssemblyName(Guid.NewGuid().ToString()), AssemblyBuilderAccess.Run);

　　　　  /// <summary>
        ///Annoucement列表
        /// JQuery EasyUI datagrid分页的参数page, rows, order, sort
        /// </summary>
        /// <param name="page">页码</param>
        /// <param name="rows">每页条数</param>
        /// <param name="order">顺序/倒序 asc/desc</param>
        /// <param name="sort">排序字段</param>
        /// <param name="key">查找字段</param>
        /// <param name="value">查找值</param>
        /// <returns></returns>
        [ResponseCache(Duration = 10,VaryByQueryKeys = new string[]{"key","page","rows"})]
        public ActionResult ListByPage(int page = 1, int rows = 10, string order = "",
            string sort = "ID", string key = "", string value = "")
        {
            //int total = 0;
            string orderBy = " order by "+ sort + " " + order;
            string filter = " where 1=1 ";
            if (!String.IsNullOrEmpty(value))
                filter += " and "+ key +" like '%" + value + "%'";  //like 操作
            string tableName = _context.Model
                                       .FindEntityType(typeof(Announcement).FullName)
                                       .Relational().TableName;
            string sql = "select * from "+ tableName  + filter ;

            var qry = _context.Announcements.FromSql(sql).OrderBy(sort+" "+ order);
            var list = qry.Skip((page-1)* rows).Take(rows);


            var result = new { total = qry.Count(), rows = list.ToList() };


            return Json(result);
        }

当然列表页面可能有多种查询条件组合, 更灵活的方式是用Dapper, Dapper使用_context.Databae.GetDbConnect, 然后可以conn.Execute(sql),conn.ExecuteScalar(sql), conn.Query(sql)

            var conn = _context.Database.GetDbConnection();

            var actions = conn.Query("exec [usp_search] @table_code = N'" + tableCode + "');
            var list = actions.Skip((page - 1) * rows).Take(rows);
            var result = new { total = actions.Count(), rows = list.ToList() };
            //Dapper 返回的json格式Pascal格式,不是CamelCase
            return Json(result);

如果后台返回的json数据,有些是关联的对象,例如这样, 我们要显示department里的depName.

在JEasyUI的datagrid的column,就得这样写

{ field: 'department', title: '部门', width: 100, align: 'right', sortable: true, formatter: departmentFormatter },



    function departmentFormatter(value) {
        return value.depName;
    }

Index,View,Save 方法

        public IActionResult Index()
        {
            //return View("../CRM/Announcement/Index");
            return View();
        }
        public IActionResult View(int? id)
        {
            Announcement entity = null;
            if (id != null)
            {
                entity = _context.Announcements.FirstOrDefault(tt => tt.ID.Equals(id));
            }
            if (entity == null)
            {
                entity = new Announcement();
                entity.IsActive = true;
                entity.CUser = 1;
                entity.CDate = DateTime.Now;
            }

            this.ViewBag.entity = entity;
           
            return View();
        }


        [HttpPost]
        public ActionResult Save(Announcement entity)
        {
            try
            {
                entity.ModifyDate = DateTime.Now;
                entity.ModifyUser = 1; //TODO: replace with login user id
                _context.Attach(entity);
                //Attach之后,PrimaryKey存在的记录状态为unchaged, 不存在的记录状态为Added
                if(_context.Entry(entity).State== EntityState.Unchanged)
                    _context.Entry(entity).State = EntityState.Modified;
                _context.SaveChanges();
                if (entity.ID > 0)
                    return Json(new { isSuccess = true, message = "保存成功", entityId = entity.ID  });
                else
                    return Json(new { isSuccess = false, message = "保存失败" });
            }
            catch (Exception e)
            {
                //Response.Write(e.Message + e.StackTrace);
                return Json(new { isSuccess = true, message = "保存失败:" + e.Message });
            }
        }

datagird,某一页的记录,点击是跳到其他页面,我本来想做一个,跳到其他页面返回后, datagrid会记住之前的页码. 但datagrid好像默认都是显示第一页的.指定PageNumber也没用

就放弃了. 关键是EasyUI的界面中规中矩,不好看, 也说不出哪里难看. 我打算换一个UI, 再花时间在datagrid不划算.

上网找了一下,国内有layUI,fineUI,amazeUI 做的不错, 我个人比较喜欢amazeUI. 下次再换了.

easyui datagrid 导出excel,参考这个地方: https://blog.csdn.net/cc1314_/article/details/78810175

但他这个方法有2个bug,一个是utf8乱码, 另一个是把Frozen Column 也导出了,因为我的datagrid的Fronzen Column是Edit操作按钮,导出没意义.

修改如下:

    $.extend($.fn.datagrid.methods, {  
        toExcel: function(jq, filename){  
            return jq.each(function(){  
                var uri = 'data:application/vnd.ms-excel;base64,'  
                    , template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><meta charset="utf-8"/><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>'  
                    , base64 = function (s) { return window.btoa(unescape(encodeURIComponent(s))) }  
                    , format = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) }  
  
                var alink = $('<a style="display:none"></a>').appendTo('body');  
                var view = $(this).datagrid('getPanel').find('div.datagrid-view');  
                //非冻结列的table body
               var table = view.find('div.datagrid-view2 table.datagrid-btable').clone();  

                //非冻结列的table header
                var head = view.find('div.datagrid-view2 table.datagrid-htable').clone();  
                var hbody = head.find('>tbody');  

                hbody.prependTo(table);  

                var ctx = { worksheet: filename || 'Worksheet', table: table.html()||'' };  
                alink[0].href = uri + base64(format(template, ctx));  
                alink[0].download = filename;  
                alink[0].click();  
                alink.remove();  
            })  
        }  
})

posted on 2018-04-12 18:50 Gu 阅读(1574) 评论(0) 编辑收藏举报

刷新页面返回顶部