Fork me on GitHub

【Bootstrap系列】详解Bootstrap-table

本篇文章将与大家分享bootstrap-table插件,借助于它实现基本的增删改查,导入导出,分页,父子表等。

至于其他技术,如冻结表头,列排列,行拖动,列拖动等,会在后续文章中与大家分享。

 一   效果图

(一)页面初始化

下图是页面首次加载结束后的效果,主要完成以下功能:

1.日期部分,开始时间:当前月第一天对应的8位日期,结束时间:当前月最后一天对应的8位日期,时间格式为:yyyy-mm-dd 

2.bootstrap-table加载的数据为日期部分所对应的时间,且按照时间递减展示

(二)查询

1.支持日期查询和订单编号查询

2.当日期和订单编号都存在时,忽略日期条件(因为订单编号是唯一的) 

 

如下为查询结果:

(三)添加

1.利用dialog模态框加载AddForm页面;

2.实现可拖拽

 

 (四)编辑

1.利用dialog模态框加载EditForm页面

2.根据订单编号选择编辑

 (五)删除

1.选中删除

(六)导入

1.下载导入模板

2.按照模板格式导入数据

(七)导出

1.选中导出

2.导出支持多种格式

 (八)父子表

1.订单表作为父表,产品表作为子表

2.父表和字表通过产品编号来关联

 

 二   Bootstrap-table讲解

关于bootstrap-table参数,需要掌握如下几大类:表格参数,列参数,事件,方法和多语言,

详情可以参考bootstrap-table官网:http://bootstrap-table.wenzhixin.net.cn/zh-cn/documentation/

三  本Demo技术讲解

(一)Demo架构图

本Demo采用UI+BLL+DAL+Model三层架构。

(二)核心代码

1.Bootstrap-table JS结构定义

 1 //初始化
 2 var InitTable = function (url) {
 3     //先销毁表格
 4     $('#tb_SaleOrder').bootstrapTable("destroy");
 5     //加载表格
 6     $('#tb_SaleOrder').bootstrapTable({
 7         rowStyle: function (row, index) {//row 表示行数据,object,index为行索引,从0开始
 8             var style = "";
 9             if (row.SignInTime == '' || row.SignOutTime=='') {
10                 style = { css: { 'color': 'red' } };
11             }
12             return  style;
13         },
14         //searchAlign: 'left',
15         //search: true,   //显示隐藏搜索框
16         showHeader: true,     //是否显示列头
17         //classes: 'table-no-bordered',
18         showLoading: true,
19         undefinedText: '',
20         showFullscreen: true,
21         toolbarAlign: 'left',
22         paginationHAlign: 'right',
23         silent: true,
24         url: url,
25         method: 'get',                      //请求方式(*)
26         toolbar: '#toolbar',                //工具按钮用哪个容器
27         striped: true,                      //是否显示行间隔色
28         cache: false,                       //是否使用缓存,默认为true,所以一般情况下需要设置一下这个属性(*)
29         pagination: true,                   //是否显示分页(*)
30         sortable: false,                     //是否启用排序
31         sortOrder: "asc",                   //排序方式
32         //queryParams: InitTable.queryParams,  //传递参数(*)
33         sidePagination: "server",           //分页方式:client客户端分页,server服务端分页(*)
34         pageNumber: 1,                       //初始化加载第一页,默认第一页
35         pageSize: 10,                       //每页的记录行数(*)
36         pageList: [2, 5, 10, 15],        //可供选择的每页的行数(*)
37         search: false,                      //是否显示表格搜索,此搜索是客户端搜索,不会进服务端,所以,个人感觉意义不大
38         strictSearch: true,
39         showColumns: true,                  //是否显示所有的列
40         showRefresh: true,                  //是否显示刷新按钮
41         minimumCountColumns: 2,             //最少允许的列数
42         clickToSelect: true,                //是否启用点击选中行
43         //height: 680,                        //行高,如果没有设置height属性,表格自动根据记录条数觉得表格高度
44         uniqueId: "ID",                     //每一行的唯一标识,一般为主键列
45         showToggle: true,                    //是否显示详细视图和列表视图的切换按钮
46         cardView: false,                    //是否显示详细视图
47         detailView: false,                   //是否显示父子表
48         showExport: true,
49         //exportDataType: 'all',
50         exportDataType: "selected",        //导出checkbox选中的行数
51         paginationLoop: false,             //是否无限循环
52         columns: [{
53             checkbox: true
54         }, {
55                 field: 'OrderNO',
56                 title: '订单编号'
57         }, {
58                 field: 'ProductNo',
59                 title: '产品编号'
60         }, {
61                 field: 'CustName',
62                 title: '客户姓名'
63         }, {
64                 field: 'CustAddress',
65                 title: '客户地址',
66         }, {
67                 field: 'CustPhone',
68                 title: '客户电话',
69         }, {
70                 field: 'CustCompany',
71                 title: '客户公司',
72         }, {
73                 field: 'CreateDateTime',
74                 title: '订单创建时间',
75         }, {
76                 field: 'UpdateDateTime',
77                 title: '订单更新时间',
78         }]
79     });
80     return InitTable;
81 };
View Code

2.订单表增删改查

 1 $(function () {
 2     //初始时间控件
 3     var frstDayDate = GetLocalMonFrstDayDate();
 4     var lastDayDate = GetLocalMonLastDayDate();
 5     $("#startDate").val(frstDayDate);
 6     $("#endDate").val(lastDayDate);
 7 
 8     //初始化bootstrap-table参数
 9     var filterParam = "";
10     var startDate = $("#startDate").val();
11     var endDate = $("#endDate").val();
12     url = "/SaleManage/GetOrderList?startDate=" + startDate + "&endDate=" + endDate + "&orderNO=" + filterParam + "";
13     InitTable(url);
14 
15     //查询数据
16     $("#btn_query").click(function () {
17         var filterParam = $("#queryKey").val();
18         var startDate = $("#startDate").val();
19         var endDate = $("#endDate").val();
20         var url = "/SaleManage/GetOrderList?startDate="+ startDate + "&endDate=" +endDate + "&orderNO=" + filterParam + "";
21         InitTable(url);
22     })
23 
24     //添加
25     $("#btn_add").click(function () {
26         var url = "/SaleManage/AddForm";
27         openDialog(url, "AddForm", "添加订单", 645, 470, function (iframe) {
28             top.frames[iframe].AcceptClick()
29         });
30     })
31 
32     //编辑
33     $("#btn_edit").click(function () {
34         //获取当前选择行id
35         var selectedRows = $("#tb_SaleOrder").bootstrapTable('getSelections');
36         if (selectedRows.length <= 0) {
37             alert('请选择要编辑的数据');
38         } else if (selectedRows.length > 1) {
39             alert('一次只能选择一行数据进行编辑');
40         } else {
41             var KeyValue = selectedRows[0].OrderNO;
42             var url = "/SaleManage/EditForm?KeyValue=" + KeyValue;
43             openDialog(url, "EditForm", "编辑邮件", 645, 470, function (iframe) {
44                 top.frames[iframe].AcceptClick()
45             });
46         }
47     })
48     //删除数据
49     $("#btn_delete").click(function () {
50         //获取当前选择行id
51         var selectedRows = $("#tb_SaleOrder").bootstrapTable('getSelections');
52         if (selectedRows.length <= 0) {
53             alert('请选择要删除的数据');
54             return;
55         }
56         if (selectedRows.length > 1) {
57             alert('一次只能选择一行删除');
58             return;
59         }
60         var orderNo = selectedRows[0].OrderNO;
61         //aja异步请求
62         $.ajax({
63             url: '/SaleManage/DelOrder',
64             type: 'get',
65             contentType: 'application/json;charset=utf-8',
66             data: { orderNo: orderNo },
67             success: function (data) {
68                 //刷新bootstrap-table
69                 $("#tb_SaleOrder").bootstrapTable('refresh');
70             },
71             error: function (data) {
72                 alert('数据删除失败' + data);
73             }
74         })
75     })
76 
77     //回车键
78     document.onkeydown = function (e) {
79         if (!e) e = window.event; //火狐中是 window.event
80         if ((e.keyCode || e.which) == 13) {
81             var query = document.getElementById("btn_query");
82             query.focus();
83             query.click();
84         }
85     }
86 });
View Code

3.日期初始化

 1 //当月第一天所对应的日期 yyyy-mm-dd
 2 function GetLocalMonFrstDayDate() {
 3     var now = new Date();
 4     var year = now.getFullYear();//
 5     var mon = now.getMonth() + 1;//
 6     if (mon < 10) {
 7         mon = '-0' + mon;
 8     }
 9     var frstDay = "-01"; //
10     return year + mon + frstDay;
11 }
12 //当月最后一天所对应的日期 yyyy-mm-dd
13 function GetLocalMonLastDayDate() {
14     var now = new Date();
15     var year = now.getFullYear();//
16     var mon = now.getMonth() + 1;//
17     if (mon < 10) {
18         mon = '-0' + mon;
19     }
20     var LastDay = "-" + GetDayCountInMon(year + mon);
21     return year + mon + LastDay;
22 }
23 //计算当月对应的最大天数
24 function GetDayCountInMon(YearMon) {
25     var arr = YearMon.split("-");
26     var localYear = parseInt(arr[0]);
27     var localMon = parseInt(arr[1]);
28     var localDate = new Date(localYear, localMon, 0);
29     return localDate.getDate();
30 }
View Code

4.Index.cshtml

 1 @{
 2     Layout = "~/Views/Shared/_LayoutBTSTable.cshtml";
 3 }
 4 
 5 <!--查询条件-->
 6 <div class="panel-body" style="padding-bottom:0px;width:104%;margin-left:-15px">
 7     <div class="panel panel-default">
 8         <div class="panel-heading">
 9             订单管理
10         </div>
11         <div style="margin-top:-30px;text-align:right">
12             <a href="~/Files/ImportTemple.xlsx" style="margin-right:20px">下载导入模板 </a>
13         </div>
14         <div class="panel-body">
15             <div style="margin-top:10px;">
16                 日期:
17                 <input type="text" id="startDate" style="height:35px;width:100px;margin-left:5px;margin-top:-32px;border-radius: 6px;border: 1px #cccccc solid; outline: none" onfocus="WdatePicker({dateFmt:'yyyy-MM-dd'})">
18 19                 <input type="text" id="endDate" style="height:35px;width:100px;margin-left:8px;margin-top:-34px;border-radius: 6px;border: 1px #cccccc solid; outline: none" onfocus="WdatePicker({dateFmt:'yyyy-MM-dd'})">
20                 &nbsp; &nbsp;订单编号:<input type="text" id="queryKey" placeholder="请输入订单编号进行查询" style="height:35px;width:170px;margin-left:10px;margin-top:-34px;border-radius: 6px;border: 1px #cccccc solid; outline: none">
21                 <button type="button" style="width:70px;height:35px;margin-left:20px;margin-top:-3px" id="btn_query" class="btn btn-success">查询</button>
22                 <button type="button" style="width:70px;height:35px;margin-left:20px;margin-top:-3px" id="btn_add" class="btn btn-info">添加</button>
23                 <button type="button" style="width:70px;height:35px;margin-left:20px;margin-top:-3px" id="btn_edit" class="btn btn-warning">编辑</button>
24                 <button type="button" style="width:70px;height:35px;margin-left:20px;margin-top:-3px" id="btn_delete" class="btn btn-danger">删除</button>
25             </div>
26         </div>
27     </div>
28 </div>
29 <!--初始化bootstrap-table-->
30 <div style="margin-bottom:-40px;color:red">注释:订单数据</div>
31 <table id="tb_SaleOrder" class="table"></table>
32 
33 <style>
34     #tb_SaleOrder tbody > tr:hover {
35         background-color: #449d44;
36     }
37 
38     #tb_SaleOrder > thead th {
39         padding: 0;
40         margin: 0;
41         background-color: #d9edf7;
42     }
43 </style>
44 <script>
45     //刷新bootstrap-table
46     function refleshBootStrapTable() {
47         $("#tb_SaleOrder").bootstrapTable('refresh');
48     }
49 </script>
50 
51 <script src="~/CustomUI/TableJS/SaleOrder.js"></script>
View Code

5.AddForm.cshtml

 1 @{
 2     ViewBag.Title = "AddForm";
 3     Layout = "~/Views/Shared/_LayoutBTSTable.cshtml";
 4 }
 5 
 6 <script>
 7     //添加数据
 8     function AcceptClick() {
 9         var OrderNO = $("#OrderNO").val();
10         var ProductNo = $("#ProductNo").val();
11         var CustName = $("#CustName").val();
12         var CustAddress = $("#CustAddress").val();
13         var CustPhone = $("#CustPhone").val();
14         var CustCompany = $("#CustCompany").val();
15         var CreateDateTime = $("#CreateDateTime").val();
16         var UpdateDateTime = $("#UpdateDateTime").val();
17         $.ajax({
18             url: '/SaleManage/AddDataToDB',
19             type: 'get',
20             contentType: 'application/json;charset=utf-8',
21             data: {
22                 'OrderNO': OrderNO, 'ProductNo': ProductNo, 'CustName': CustName,
23                 'CustAddress': CustAddress, 'CustPhone': CustPhone, 'CustCompany': CustCompany,
24                 'CreateDateTime': CreateDateTime, 'UpdateDateTime': UpdateDateTime
25             },
26             success: function (data) {
27                 reflesh();
28                 //关闭对话框
29                 closeDialog();
30             },
31             error: function (data) {
32                 alert('添加数据失败' + data);
33             }
34         })
35     }
36     //刷新
37     function reflesh() {
38         window.parent.refleshBootStrapTable();
39     }
40 </script>
41 
42 
43 <div class="table" style="width:100%;margin-top:10px">
44     <table id="tb_SaleOrder_Add" class="table text-nowrap" style="text-align:right;">
45         <tr>
46             <td style="height:35px;line-height:35px">订单编号&nbsp;&nbsp;&nbsp;</td>
47             <td><input type="text" id="OrderNO" style="width:500px;" /></td>
48         </tr>
49         <tr>
50             <td style="height:35px;line-height:35px">产品名称&nbsp;&nbsp;&nbsp;</td>
51             <td><input type="text" id="ProductNo" style="width:500px;" /></td>
52         </tr>
53         <tr>
54             <td style="height:35px;line-height:35px">客户姓名&nbsp;&nbsp;&nbsp;</td>
55             <td><input type="text" id="CustName" style="width:500px;" /></td>
56         </tr>
57         <tr>
58             <td style="height:35px;line-height:35px">客户地址&nbsp;&nbsp;&nbsp;</td>
59             <td><input type="text" id="CustAddress" style="width:500px;" /></td>
60         </tr>
61         <tr>
62             <td style="height:35px;line-height:35px">客户电话&nbsp;&nbsp;&nbsp;</td>
63             <td><input type="text" id="CustPhone" style="width:500px;" /></td>
64         </tr>
65         <tr>
66             <td style="height:35px;line-height:35px">客户公司&nbsp;&nbsp;&nbsp;</td>
67             <td><input type="text" id="CustCompany" style="width:500px;" /></td>
68         </tr>
69         <tr>
70             <td style="height:35px;line-height:35px">订单创建时间&nbsp;&nbsp;&nbsp;</td>
71             <td><input type="text" id="CreateDateTime" style="width:500px;" onfocus="WdatePicker({dateFmt:'yyyy-MM-dd HH:mm:ss'})" class="Wdate"/></td>
72         </tr>
73         <tr>
74             <td style="height:35px;line-height:35px">订单更新时间&nbsp;&nbsp;&nbsp;</td>
75             <td><input type="text" id="UpdateDateTime" style="width:500px;" onfocus="WdatePicker({dateFmt:'yyyy-MM-dd HH:mm:ss'})" class="Wdate"/></td>
76         </tr>
77     </table>
78 </div>
79 
80 <style>
81     #tb_SaleOrder_Add td input[type=text] {
82         height: 35px;
83         border-radius: 6px;
84         border: 1px #cccccc solid;
85         outline: none
86     }
87 </style>
View Code

6.EditForm.cshtml

@{
    ViewBag.Title = "EditForm";
    Layout = "~/Views/Shared/_LayoutBTSTable.cshtml";
}

<script>
    $(function () {
        //初始化页面控件
        $.ajax({
            url: "/SaleManage/InitModifySheet",
            type: 'get',
            contentType: 'application/json;charset=utf-8',
            data: {
                orderNO: GetQuery('KeyValue')
            },
            success: function (data) {
                //将回调数据转化为json对象
                var jsonData = eval(data);
                //遍历,为表单赋值
                $("#OrderNO").val(jsonData[0].OrderNO);
                $("#ProductNo").val(jsonData[0].ProductNo);  
                $("#CustName").val(jsonData[0].CustName);
                $("#CustAddress").val(jsonData[0].CustAddress);
                $("#CustPhone").val(jsonData[0].CustPhone);
                $("#CustCompany").val(jsonData[0].CustCompany);
                $("#CreateDateTime").val(jsonData[0].CreateDateTime);
                $("#UpdateDateTime").val(jsonData[0].UpdateDateTime); 
            },
            error: function (data) {
                alert('编辑数据失败' + data);
            }
        })
    })

    //添加数据
    function AcceptClick() {
        var OrderNO = $("#OrderNO").val();
        var ProductNo = $("#ProductNo").val();
        var CustName = $("#CustName").val();
        var CustAddress = $("#CustAddress").val();
        var CustPhone = $("#CustPhone").val();
        var CustCompany = $("#CustCompany").val();
        var CreateDateTime = $("#CreateDateTime").val();
        var UpdateDateTime = $("#UpdateDateTime").val();
        $.ajax({
            url: '/SaleManage/ModifyDataToDB',
            type: 'get',
            contentType: 'application/json;charset=utf-8',
            data: {
                'OrderNO': OrderNO, 'ProductNo': ProductNo, 'CustName': CustName,
                'CustAddress': CustAddress, 'CustPhone': CustPhone, 'CustCompany': CustCompany,
                'CreateDateTime': CreateDateTime, 'UpdateDateTime': UpdateDateTime
            },
            success: function (data) {
                reflesh();
                //关闭对话框
                closeDialog();
            },
            error: function (data) {
                alert('添加数据失败' + data);
            }
        })
    }
    //刷新
    function reflesh() {
        window.parent.refleshBootStrapTable();
    }
</script>



<div class="table" style="width:100%;margin-top:10px">
    <table id="tb_SaleOrder_Add" class="table text-nowrap" style="text-align:right;">
        <tr>
            <td style="height:35px;line-height:35px">订单编号&nbsp;&nbsp;&nbsp;</td>
            <td><input type="text" id="OrderNO" style="width:500px;" disabled/></td>
        </tr>
        <tr>
            <td style="height:35px;line-height:35px">产品名称&nbsp;&nbsp;&nbsp;</td>
            <td><input type="text" id="ProductNo" style="width:500px;" /></td>
        </tr>
        <tr>
            <td style="height:35px;line-height:35px">客户姓名&nbsp;&nbsp;&nbsp;</td>
            <td><input type="text" id="CustName" style="width:500px;" /></td>
        </tr>
        <tr>
            <td style="height:35px;line-height:35px">客户地址&nbsp;&nbsp;&nbsp;</td>
            <td><input type="text" id="CustAddress" style="width:500px;" /></td>
        </tr>
        <tr>
            <td style="height:35px;line-height:35px">客户电话&nbsp;&nbsp;&nbsp;</td>
            <td><input type="text" id="CustPhone" style="width:500px;" /></td>
        </tr>
        <tr>
            <td style="height:35px;line-height:35px">客户公司&nbsp;&nbsp;&nbsp;</td>
            <td><input type="text" id="CustCompany" style="width:500px;" /></td>
        </tr>
        <tr>
            <td style="height:35px;line-height:35px">订单创建时间&nbsp;&nbsp;&nbsp;</td>
            <td><input type="text" id="CreateDateTime" style="width:500px;" onfocus="WdatePicker({dateFmt:'yyyy-MM-dd HH:mm:ss'})" class="Wdate" /></td>
        </tr>
        <tr>
            <td style="height:35px;line-height:35px">订单更新时间&nbsp;&nbsp;&nbsp;</td>
            <td><input type="text" id="UpdateDateTime" style="width:500px;" onfocus="WdatePicker({dateFmt:'yyyy-MM-dd HH:mm:ss'})" class="Wdate" /></td>
        </tr>
    </table>
</div>

<style>
    #tb_SaleOrder_Add td input[type=text] {
        height: 35px;
        border-radius: 6px;
        border: 1px #cccccc solid;
        outline: none
    }
</style>
View Code

7.Import.cshtml

  1 @{
  2     ViewBag.Title = "EditForm";
  3     Layout = "~/Views/Shared/_LayoutBTSTable.cshtml";
  4 }
  5 
  6 <script>
  7     $(function () {
  8         //初始化页面控件
  9         $.ajax({
 10             url: "/SaleManage/InitModifySheet",
 11             type: 'get',
 12             contentType: 'application/json;charset=utf-8',
 13             data: {
 14                 orderNO: GetQuery('KeyValue')
 15             },
 16             success: function (data) {
 17                 //将回调数据转化为json对象
 18                 var jsonData = eval(data);
 19                 //遍历,为表单赋值
 20                 $("#OrderNO").val(jsonData[0].OrderNO);
 21                 $("#ProductNo").val(jsonData[0].ProductNo);  
 22                 $("#CustName").val(jsonData[0].CustName);
 23                 $("#CustAddress").val(jsonData[0].CustAddress);
 24                 $("#CustPhone").val(jsonData[0].CustPhone);
 25                 $("#CustCompany").val(jsonData[0].CustCompany);
 26                 $("#CreateDateTime").val(jsonData[0].CreateDateTime);
 27                 $("#UpdateDateTime").val(jsonData[0].UpdateDateTime); 
 28             },
 29             error: function (data) {
 30                 alert('编辑数据失败' + data);
 31             }
 32         })
 33     })
 34 
 35     //添加数据
 36     function AcceptClick() {
 37         var OrderNO = $("#OrderNO").val();
 38         var ProductNo = $("#ProductNo").val();
 39         var CustName = $("#CustName").val();
 40         var CustAddress = $("#CustAddress").val();
 41         var CustPhone = $("#CustPhone").val();
 42         var CustCompany = $("#CustCompany").val();
 43         var CreateDateTime = $("#CreateDateTime").val();
 44         var UpdateDateTime = $("#UpdateDateTime").val();
 45         $.ajax({
 46             url: '/SaleManage/ModifyDataToDB',
 47             type: 'get',
 48             contentType: 'application/json;charset=utf-8',
 49             data: {
 50                 'OrderNO': OrderNO, 'ProductNo': ProductNo, 'CustName': CustName,
 51                 'CustAddress': CustAddress, 'CustPhone': CustPhone, 'CustCompany': CustCompany,
 52                 'CreateDateTime': CreateDateTime, 'UpdateDateTime': UpdateDateTime
 53             },
 54             success: function (data) {
 55                 reflesh();
 56                 //关闭对话框
 57                 closeDialog();
 58             },
 59             error: function (data) {
 60                 alert('添加数据失败' + data);
 61             }
 62         })
 63     }
 64     //刷新
 65     function reflesh() {
 66         window.parent.refleshBootStrapTable();
 67     }
 68 </script>
 69 
 70 
 71 
 72 <div class="table" style="width:100%;margin-top:10px">
 73     <table id="tb_SaleOrder_Add" class="table text-nowrap" style="text-align:right;">
 74         <tr>
 75             <td style="height:35px;line-height:35px">订单编号&nbsp;&nbsp;&nbsp;</td>
 76             <td><input type="text" id="OrderNO" style="width:500px;" disabled/></td>
 77         </tr>
 78         <tr>
 79             <td style="height:35px;line-height:35px">产品名称&nbsp;&nbsp;&nbsp;</td>
 80             <td><input type="text" id="ProductNo" style="width:500px;" /></td>
 81         </tr>
 82         <tr>
 83             <td style="height:35px;line-height:35px">客户姓名&nbsp;&nbsp;&nbsp;</td>
 84             <td><input type="text" id="CustName" style="width:500px;" /></td>
 85         </tr>
 86         <tr>
 87             <td style="height:35px;line-height:35px">客户地址&nbsp;&nbsp;&nbsp;</td>
 88             <td><input type="text" id="CustAddress" style="width:500px;" /></td>
 89         </tr>
 90         <tr>
 91             <td style="height:35px;line-height:35px">客户电话&nbsp;&nbsp;&nbsp;</td>
 92             <td><input type="text" id="CustPhone" style="width:500px;" /></td>
 93         </tr>
 94         <tr>
 95             <td style="height:35px;line-height:35px">客户公司&nbsp;&nbsp;&nbsp;</td>
 96             <td><input type="text" id="CustCompany" style="width:500px;" /></td>
 97         </tr>
 98         <tr>
 99             <td style="height:35px;line-height:35px">订单创建时间&nbsp;&nbsp;&nbsp;</td>
100             <td><input type="text" id="CreateDateTime" style="width:500px;" onfocus="WdatePicker({dateFmt:'yyyy-MM-dd HH:mm:ss'})" class="Wdate" /></td>
101         </tr>
102         <tr>
103             <td style="height:35px;line-height:35px">订单更新时间&nbsp;&nbsp;&nbsp;</td>
104             <td><input type="text" id="UpdateDateTime" style="width:500px;" onfocus="WdatePicker({dateFmt:'yyyy-MM-dd HH:mm:ss'})" class="Wdate" /></td>
105         </tr>
106     </table>
107 </div>
108 
109 <style>
110     #tb_SaleOrder_Add td input[type=text] {
111         height: 35px;
112         border-radius: 6px;
113         border: 1px #cccccc solid;
114         outline: none
115     }
116 </style>
View Code

8.ParentAndChild.cshtml

 1 @{
 2     Layout = "~/Views/Shared/_LayoutBTSTable.cshtml";
 3 }
 4 
 5 <!--查询条件-->
 6 <div class="panel-body" style="padding-bottom:0px;width:104%;margin-left:-15px">
 7     <div class="panel panel-default">
 8         <div class="panel-heading">
 9             订单管理
10         </div>
11         <div style="margin-top:-30px;text-align:right">
12             <a href="~/Files/ImportTemple.xlsx" style="margin-right:20px">下载导入模板 </a>
13         </div>
14         <div class="panel-body">
15             <div style="margin-top:10px;">
16                 日期:
17                 <input type="text" id="startDate" style="height:35px;width:100px;margin-left:5px;margin-top:-32px;border-radius: 6px;border: 1px #cccccc solid; outline: none" onfocus="WdatePicker({dateFmt:'yyyy-MM-dd'})">
18 19                 <input type="text" id="endDate" style="height:35px;width:100px;margin-left:8px;margin-top:-34px;border-radius: 6px;border: 1px #cccccc solid; outline: none" onfocus="WdatePicker({dateFmt:'yyyy-MM-dd'})">
20                 &nbsp; &nbsp;订单编号:<input type="text" id="queryKey" placeholder="请输入订单编号进行查询" style="height:35px;width:170px;margin-left:10px;margin-top:-34px;border-radius: 6px;border: 1px #cccccc solid; outline: none">
21                 <button type="button" style="width:70px;height:35px;margin-left:20px;margin-top:-3px" id="btn_query" class="btn btn-success">查询</button>
22                 <button type="button" style="width:70px;height:35px;margin-left:20px;margin-top:-3px" id="btn_add" class="btn btn-info">添加</button>
23                 <button type="button" style="width:70px;height:35px;margin-left:20px;margin-top:-3px" id="btn_edit" class="btn btn-warning">编辑</button>
24                 <button type="button" style="width:70px;height:35px;margin-left:20px;margin-top:-3px" id="btn_delete" class="btn btn-danger">删除</button>
25             </div>
26         </div>
27     </div>
28 </div>
29 <!--初始化bootstrap-table-->
30 <div style="margin-bottom:-40px;color:red">注释:父子表</div>
31 <table id="tb_SaleOrder" class="table"></table>
32 
33 <style>
34     #tb_SaleOrder > thead th {
35         padding: 0;
36         margin: 0;
37         background-color: #d9edf7;
38     }
39 </style>
40 <script>
41     //刷新bootstrap-table
42     function refleshBootStrapTable() {
43         $("#tb_SaleOrder").bootstrapTable('refresh');
44     }
45 </script>
46 
47 <script src="~/CustomUI/TableJS/ParentChild.js"></script>
View Code

9.布局页 _LayoutBTSTable.cshtml

 1 <!DOCTYPE html>
 2 
 3 <html>
 4 <head>
 5     <meta name="viewport" content="width=device-width" />
 6     <link href="~/CustomUI/bootstrap/css/bootstrap.css" rel="stylesheet" />
 7     <link href="~/CustomUI/bootstrapTable/bootstrap-table.css" rel="stylesheet" />
 8     <link href="~/CustomUI/skin/WdatePicker.css" rel="stylesheet" />
 9     <script src="~/CustomUI/jquery/jquery-3.3.1.js"></script>
10     <script src="~/CustomUI/lhgdialog/lhgdialog.min.js"></script>
11     <script src="~/CustomUI/bootstrap/js/bootstrap.js"></script>
12     <script src="~/CustomUI/bootstrapTable/bootstrap-table.js"></script>
13     <script src="~/CustomUI/bootstrapTable/tableExport.js"></script>
14     <script src="~/CustomUI/bootstrapTable/bootstrap-table-export.js"></script>
15     <script src="~/CustomUI/bootstrapTable/bootstrap-table-zh-CN.js"></script>
16     <script src="~/CustomUI/datepicker/WdatePicker.js"></script>
17 </head>
18 <body>
19     <div>
20         @RenderBody()
21     </div>
22 </body>
23 </html>
24 
25 <script src="~/CustomUI/TableJS/DialogTemple.js"></script>
View Code

10.ImportExcelToDB.cs

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Configuration;
  4 using System.Data;
  5 using System.Data.OleDb;
  6 using System.Data.SqlClient;
  7 using System.Linq;
  8 using System.Text;
  9 using System.Web;
 10 
 11 namespace BTStrapTB.Common
 12 {
 13     public class ImportExcelToDB
 14     {
 15         //全局数据库连接字符串
 16         private readonly string strConnection = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
 17 
 18         //从Excel读取数据
 19         public static DataSet ReadExcelDataToTable(string filepath)
 20         {
 21             try
 22             {
 23                 int index1 = filepath.LastIndexOf("\\");
 24                 int index2 = filepath.LastIndexOf(".");
 25                 string fileName ="["+filepath.Substring(index1+1,index2-index1-1)+"$]";
 26                 string strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", filepath);
 27                 using (OleDbConnection oleConn = new OleDbConnection(strConn))
 28                 {
 29                     oleConn.Open();
 30                     string sql = "select * from "+fileName+ "";
 31                     OleDbDataAdapter oleDaExcel = new OleDbDataAdapter(sql, oleConn);
 32                     DataSet oleDsExcel = new DataSet();
 33                     oleDaExcel.Fill(oleDsExcel, "table1");
 34                     return oleDsExcel;
 35                 }
 36             }
 37             catch (Exception ex)
 38             {
 39                 throw ex;
 40             }
 41         }
 42         public void InsertExcelDataToDB(string fileName)
 43         {
 44             //导入表格格式化SQL
 45             string sqlText = @"INSERT INTO [dbo].[SaleOrder]
 46                                ([OrderNO]
 47                                ,[ProductNo]
 48                                ,[CustName]
 49                                ,[CustAddress]
 50                                ,[CustPhone]
 51                                ,[CustCompany]
 52                                ,[CreateDateTime]
 53                                ,[UpDateDateTime])
 54                                VALUES
 55                                 ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')";
 56 
 57             if (!System.IO.File.Exists(fileName))
 58             {
 59                 throw new Exception("指定路径的Excel文件不存在!");
 60             }
 61             DataSet ds = ReadExcelDataToTable(fileName);
 62             DataTable dt = ds.Tables[0];
 63             //将excel数据插入到DB之前,先判断DB中是否存在数据
 64             DelDBRepeatData(dt);
 65             List<string> list = (from DataRow row in dt.Rows
 66                                  select String.Format(sqlText, row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7])).ToList();
 67             OperateDB(list);
 68         }
 69 
 70         /*
 71          将excel数据插入到DB之前,
 72          先判断DB中是否存在同一天同一员工记录
 73        */
 74         public int DelDBRepeatData(DataTable dt)
 75         {
 76             //sql脚本
 77             string delSqlText = @"DELETE FROM [dbo].[SaleOrder]
 78                                   WHERE OrderNO IN ('{0}')
 79                                   ";
 80 
 81             //取excel中的员工号和打卡日期
 82             StringBuilder strBld = new StringBuilder();
 83 
 84             for (int i = 0; i < dt.Rows.Count; i++)
 85             {
 86                 strBld.Append(dt.Rows[i][0]);
 87                 
 88             }
 89 
 90             List<string> list = (from DataRow row in dt.Rows
 91                                  select String.Format(delSqlText, row[0])).ToList();
 92 
 93             OperateDB(list);
 94             return 0;
 95         }
 96 
 97         //DB操作
 98         public int OperateDB(List<string> list)
 99         {
100             int result = 0;
101             using (SqlConnection conn = new SqlConnection(strConnection))
102             {
103                 if (conn.State==ConnectionState.Closed)
104                 {
105                     conn.Open();
106                 }
107                 foreach (string item in list)
108                 {
109                     SqlCommand cmd = new SqlCommand(item, conn);
110                     result=cmd.ExecuteNonQuery();
111                 }
112             }
113             return result;
114         }
115     }
116 }
View Code

12.ConvertHelpers.cs

 1 using Newtonsoft.Json;
 2 using System;
 3 using System.Collections.Generic;
 4 using System.Data;
 5 using System.Linq;
 6 using System.Reflection;
 7 using System.Web;
 8 
 9 namespace BTStrapTB.Common
10 {
11     /// <summary>
12     /// 转换Json格式帮助类
13     /// </summary>
14     public static class JsonHelper
15     {
16         public static object ToJson(this string Json)
17         {
18             return JsonConvert.DeserializeObject(Json);
19         }
20         public static string ToJson(this object obj)
21         {
22             return JsonConvert.SerializeObject(obj);
23         }
24         public static List<T> JonsToList<T>(this string Json)
25         {
26             return JsonConvert.DeserializeObject<List<T>>(Json);
27         }
28         public static T JsonToEntity<T>(this string Json)
29         {
30             return JsonConvert.DeserializeObject<T>(Json);
31         }
32     }
33 }
View Code

13.SaleManageController

 1 using BTStrapTB.BLL;
 2 using BTStrapTB.Common;
 3 using BTStrapTB.Models;
 4 using System;
 5 using System.Collections.Generic;
 6 using System.IO;
 7 using System.Linq;
 8 using System.Web;
 9 using System.Web.Mvc;
10 
11 namespace BTStrapTB.Controllers
12 {
13     //销售管理
14     public class SaleManageController : BaseManageController
15     {
16         ImportExcelToDB ImportToExcl = new ImportExcelToDB();
17         SaleOrderBLL SOBLL = new SaleOrderBLL();
18         SaleProductBLL SPBLL = new SaleProductBLL();
19         public override ActionResult Index()
20         {
21             return View();
22         }
23         //导入页面
24         public ActionResult Import()
25         {
26             return View();
27         }
28 
29         //将Excel订单数据导入
30         [HttpPost]
31         public ActionResult ImportExclToDB(HttpPostedFileBase file)
32         {
33             var severPath = this.Server.MapPath("/Files"); //获取当前虚拟文件路径
34             var savePath = Path.Combine(severPath, file.FileName); //拼接保存文件路径
35             file.SaveAs(savePath);
36             try
37             {
38                 ImportToExcl.InsertExcelDataToDB(savePath);
39                 return Content("<script>alert('上传成功!!')</script>");
40             }
41             catch (Exception ex)
42             {
43                 throw new Exception(ex.Message);
44             }
45 
46             //Response.Redirect("/PunchCardRecord/Index");
47         }
48 
49         //父子页面
50         public ActionResult ParentAndChild()
51         {
52             return View();
53         }
54         
55         //获取子表数据
56         public ActionResult GetChildDataList(int limit, int offset,string productNo)
57         {
58             List<SaleProduct> list = SPBLL.GetProductOrderList(productNo);
59             int total = list.Count;
60             var rows = list.Skip(offset).Take(limit).ToList();
61             return Json(new { total, rows }, JsonRequestBehavior.AllowGet);
62         }
63 
64         //获取订单列表
65         public ActionResult GetOrderList(int limit, int offset,string startDate,string endDate,string orderNO)
66         {
67             List<SaleOrder> list = SOBLL.GetSaleOrderList(startDate,endDate, orderNO);
68             int total = list.Count;
69             var rows = list.Skip(offset).Take(limit).ToList();
70             return Json(new { total, rows }, JsonRequestBehavior.AllowGet);
71         }
72         //删除数据
73         public void DelOrder(string orderNo)
74         {
75             SOBLL.DelDataToDB(orderNo);
76         }
77         //添加数据
78         public void AddDataToDB(SaleOrder saleOrder)
79         {
80             SOBLL.AddDataToDB(saleOrder);
81         }
82         //初始化修改表单
83         public ActionResult InitModifySheet(string orderNO)
84         {
85             List<SaleOrder> list = SOBLL.GetSaleOrderList("", "", orderNO);
86             return Content(list.ToJson());
87         }
88         //修改数据
89         public void ModifyDataToDB(SaleOrder saleOrder)
90         {
91             SOBLL.ModifyDataToDB(saleOrder);
92         }
93     }
94 }
View Code

14.父子表JS

  1 //初始化
  2 var InitTable = function (url) {
  3     //先销毁表格
  4     $('#tb_SaleOrder').bootstrapTable("destroy");
  5     //加载表格
  6     $('#tb_SaleOrder').bootstrapTable({
  7         rowStyle: function (row, index) {//row 表示行数据,object,index为行索引,从0开始
  8             var style = "";
  9             if (row.SignInTime == '' || row.SignOutTime == '') {
 10                 style = { css: { 'color': 'red' } };
 11             }
 12             return style;
 13         },
 14         //searchAlign: 'left',
 15         //search: true,   //显示隐藏搜索框
 16         showHeader: true,     //是否显示列头
 17         //classes: 'table-no-bordered',
 18         showLoading: true,
 19         undefinedText: '',
 20         showFullscreen: true,
 21         toolbarAlign: 'left',
 22         paginationHAlign: 'right',
 23         silent: true,
 24         url: url,
 25         method: 'get',                      //请求方式(*)
 26         toolbar: '#toolbar',                //工具按钮用哪个容器
 27         striped: true,                      //是否显示行间隔色
 28         cache: false,                       //是否使用缓存,默认为true,所以一般情况下需要设置一下这个属性(*)
 29         pagination: true,                   //是否显示分页(*)
 30         sortable: false,                     //是否启用排序
 31         sortOrder: "asc",                   //排序方式
 32         //queryParams: InitTable.queryParams,  //传递参数(*)
 33         sidePagination: "server",           //分页方式:client客户端分页,server服务端分页(*)
 34         pageNumber: 1,                       //初始化加载第一页,默认第一页
 35         pageSize: 10,                       //每页的记录行数(*)
 36         pageList: [2, 5, 10, 15],        //可供选择的每页的行数(*)
 37         search: false,                      //是否显示表格搜索,此搜索是客户端搜索,不会进服务端,所以,个人感觉意义不大
 38         strictSearch: true,
 39         showColumns: true,                  //是否显示所有的列
 40         showRefresh: true,                  //是否显示刷新按钮
 41         minimumCountColumns: 2,             //最少允许的列数
 42         clickToSelect: true,                //是否启用点击选中行
 43         //height: 680,                        //行高,如果没有设置height属性,表格自动根据记录条数觉得表格高度
 44         uniqueId: "ID",                     //每一行的唯一标识,一般为主键列
 45         showToggle: true,                    //是否显示详细视图和列表视图的切换按钮
 46         cardView: false,                    //是否显示详细视图
 47         detailView: true,                   //是否显示父子表
 48         showExport: true,
 49         //exportDataType: 'all',
 50         exportDataType: "selected",        //导出checkbox选中的行数
 51         paginationLoop: false,             //是否无限循环
 52         columns: [{
 53             checkbox: true
 54         }, {
 55             field: 'OrderNO',
 56             title: '订单编号'
 57         }, {
 58             field: 'ProductNo',
 59             title: '产品编号'
 60         }, {
 61             field: 'CustName',
 62             title: '客户姓名'
 63         }, {
 64             field: 'CustAddress',
 65             title: '客户地址',
 66         }, {
 67             field: 'CustPhone',
 68             title: '客户电话',
 69         }, {
 70             field: 'CustCompany',
 71             title: '客户公司',
 72         }, {
 73             field: 'CreateDateTime',
 74             title: '订单创建时间',
 75         }, {
 76             field: 'UpdateDateTime',
 77             title: '订单更新时间',
 78         }],
 79 
 80         //无限循环取子表,直到子表里面没有记录
 81           onExpandRow: function (index, row, $Subdetail) {
 82             InitSubTable(index, row, $Subdetail);
 83         }
 84     });
 85     return InitTable;
 86 
 87     
 88 };
 89 
 90 //初始化子表格(无线循环)
 91 InitSubTable = function (index, row, $detail) {
 92     var parentid = row.ProductNo;
 93     var cur_table = $detail.html('<table></table>').find('table');
 94     $(cur_table).bootstrapTable({
 95         url: "/SaleManage/GetChildDataList",
 96         method: 'get',
 97         queryParams: { 'limit':10000,'offset':0,'productNo':parentid},
 98         clickToSelect: true,
 99         detailView: false,//父子表
100         sidePagination: "server",
101         uniqueId: "ProductNo",
102         pageSize: 10,
103         pageList: [10, 25],
104         columns: [{
105             field: 'ProductNo',
106             title: '产品编号'
107         },
108         {
109             field: 'ProductName',
110             title: '产品名称'
111         }, {
112             field: 'ProductType',
113             title: '产品类型'
114         }, {
115             field: 'ProductCount',
116             title: '产品数量'
117         },
118         {
119             field: 'ProductPrice',
120             title: '产品单价'
121         }],
122         //无限循环取子表,直到子表里面没有记录
123         onExpandRow: function (index, row, $Subdetail) {
124             InitSubTable(index, row, $Subdetail);
125         }
126     });
127 };
View Code

(三)其他技术点

1.改变bootstrap-table表头颜色

1 #tb_SaleOrder > thead th {
2         padding: 0;
3         margin: 0;
4         background-color: #d9edf7;
5     }

2.改变bootstrap-table 光标悬停颜色

1 #tb_SaleOrder tbody > tr:hover {
2         background-color: #449d44;
3     }

3.刷新bootstrap-table

1 //刷新bootstrap-table
2     function refleshBootStrapTable() {
3         $("#tb_SaleOrder").bootstrapTable('refresh');
4     }

 4.弹窗

 1 /*
 2 弹出对话框(带:确认按钮、取消按钮)
 3 */
 4 function openDialog(url, _id, _title, _width, _height, callBack) {
 5     Loading(true);
 6     top.$.dialog({
 7         id: _id,
 8         width: _width,
 9         height: _height,
10         max: false,
11         lock: true,
12         title: _title,
13         resize: false,
14         extendDrag: true,
15         content: 'url:' + RootPath() + url,
16         ok: function () {
17             callBack(_id);
18             return false;
19         },
20         cancel: true
21     });
22 }

5.Bootstrap-table核心技术点,再次强调

 1 var InitTable = function (url) {
 2     //先销毁表格
 3     $('#tb_SaleOrder').bootstrapTable("destroy");
 4     //加载表格
 5     $('#tb_SaleOrder').bootstrapTable({
 6         rowStyle: function (row, index) {//row 表示行数据,object,index为行索引,从0开始
 7             var style = "";
 8             if (row.SignInTime == '' || row.SignOutTime=='') {
 9                 style = { css: { 'color': 'red' } };
10             }
11             return  style;
12         },
13         //searchAlign: 'left',
14         //search: true,   //显示隐藏搜索框
15         showHeader: true,     //是否显示列头
16         //classes: 'table-no-bordered',
17         showLoading: true,
18         undefinedText: '',
19         showFullscreen: true,
20         toolbarAlign: 'left',
21         paginationHAlign: 'right',
22         silent: true,
23         url: url,
24         method: 'get',                      //请求方式(*)
25         toolbar: '#toolbar',                //工具按钮用哪个容器
26         striped: true,                      //是否显示行间隔色
27         cache: false,                       //是否使用缓存,默认为true,所以一般情况下需要设置一下这个属性(*)
28         pagination: true,                   //是否显示分页(*)
29         sortable: false,                     //是否启用排序
30         sortOrder: "asc",                   //排序方式
31         //queryParams: InitTable.queryParams,  //传递参数(*)
32         sidePagination: "server",           //分页方式:client客户端分页,server服务端分页(*)
33         pageNumber: 1,                       //初始化加载第一页,默认第一页
34         pageSize: 10,                       //每页的记录行数(*)
35         pageList: [2, 5, 10, 15],        //可供选择的每页的行数(*)
36         search: false,                      //是否显示表格搜索,此搜索是客户端搜索,不会进服务端,所以,个人感觉意义不大
37         strictSearch: true,
38         showColumns: true,                  //是否显示所有的列
39         showRefresh: true,                  //是否显示刷新按钮
40         minimumCountColumns: 2,             //最少允许的列数
41         clickToSelect: true,                //是否启用点击选中行
42         //height: 680,                        //行高,如果没有设置height属性,表格自动根据记录条数觉得表格高度
43         uniqueId: "ID",                     //每一行的唯一标识,一般为主键列
44         showToggle: true,                    //是否显示详细视图和列表视图的切换按钮
45         cardView: false,                    //是否显示详细视图
46         detailView: false,                   //是否显示父子表
47         showExport: true,
48         //exportDataType: 'all',
49         exportDataType: "selected",        //导出checkbox选中的行数
50         paginationLoop: false,             //是否无限循环
51         columns: [{
52             checkbox: true
53         }, {
54                 field: 'OrderNO',
55                 title: '订单编号'
56         }, {
57                 field: 'ProductNo',
58                 title: '产品编号'
59         }, {
60                 field: 'CustName',
61                 title: '客户姓名'
62         }, {
63                 field: 'CustAddress',
64                 title: '客户地址',
65         }, {
66                 field: 'CustPhone',
67                 title: '客户电话',
68         }, {
69                 field: 'CustCompany',
70                 title: '客户公司',
71         }, {
72                 field: 'CreateDateTime',
73                 title: '订单创建时间',
74         }, {
75                 field: 'UpdateDateTime',
76                 title: '订单更新时间',
77         }]
78     });
79     return InitTable;
80 };

四  写在最后

  本片文章借助于bootstrap-table插件,实现了基本的增删改查,导入导出,分页,父子表等。至于其他技术,如冻结表头,列排列,行拖动,列拖动等,会在后续文章中与大家分享。

posted @ 2018-09-10 21:45 Alan_beijing 阅读(...) 评论(...) 编辑 收藏