Handler1.ashx （用一般程序连接数据库）
1 <%@ WebHandler Language="C#" Class="Handler1" %>
2
3 using System;
4 using System.Web;
5 using System.Data;
6 using System.Data.SqlClient; //数据库
7 using Newtonsoft.Json; //操作json库
8
9 public class Handler1 : IHttpHandler {
10
11 //HttpContext context 是ASP.NET中的一个对象，代表正在处理的HTTP请求的上下文信息，包括请求头、请求主体、响应头、响应主体等。这个对象在ASP.NET Web页面或者Web服务中经常用到，用于获取客户端提交的数据、生成动态HTML并将其发送回客户端等。
12 public void ProcessRequest (HttpContext context) {
13 context.Response.ContentType = "text/plain";
14 context.Response.Write(mysourcestring2());
15 }
16
17 public bool IsReusable {
18 get {
19 return false;
20 }
21 }
22
23 private string mysourcestring2()
24 {
25 //返回的样式例 [{"id":5989,"type":"ZVDH","Shipto":"S716000456","Soldto":"S7160"},{"id":6003,"type":"ZPPH","Shipto":"S2000050","Soldto":"S2000"}]
26 string s0 = "select * from outstanding";
27 SqlConnection conn = new SqlConnection("server=数据库IP;database=数据库名;uid=用户名;pwd=用户密码");
28 conn.Open();
29 SqlCommand cmd = new SqlCommand(s0, conn); //用来执行查询语句
30 SqlDataAdapter sda = new SqlDataAdapter(); //数据库适配器，用来充当数据库与数据集之间的桥梁
31 sda.SelectCommand = cmd; //选择命令向数据库发送（发送查询语句）
32 DataSet ds = new DataSet(); //创建一个数据集对象,相当于小型数据库，它当中存放若干个数据块
33 sda.Fill(ds, "cs"); //Fill: 填充 把数据填充小型数据库的“CS”表中。
34 System.Data.DataTable table1 = ds.Tables[0];
35
36 string jsonstr = JsonConvert.SerializeObject(table1); //datatable格式转换Json格式 前提要导入 using Newtonsoft.Json
37
38 return jsonstr;
39 }
40
41 }
homePage.aspx （homePage.aspx.cs中无相关代码）
1 <%@ Page Language="C#" AutoEventWireup="true" CodeFile="homePage.aspx.cs" Inherits="homePage" %>
2
3 <!DOCTYPE html>
4
5 <html xmlns="http://www.w3.org/1999/xhtml">
6
7 <head runat="server">
8 <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
9 <title>SSC</title>
10
11 <%--CSS--%>
12 <link href="/Content/bootstrap.min.css" rel="stylesheet" />
13 <link href="/Content/bootstrap-table.min.css" rel="stylesheet" />
14 <link href="/Content/bootstrap-editable.css" rel="stylesheet" />
15 <link href="/Scripts/jexcel/jexcel.css" rel="stylesheet" />
16 <link href="/Scripts/jexcel/jsuites.css" rel="stylesheet" />
17 <link href="/Scripts/page/css/pagination.min.css" rel="stylesheet" />
18 <link href="/Scripts/page/css/pagination.scss" rel="stylesheet" />
19 </head>
20
21 <body>
22 <form id="form1" runat="server">
23
24 <%--表头--%>
25 <div id="header">
26 <h1>
27 <asp:Image ID="Image3" src="/img/KMlogo1.png" Style="opacity: 0.7" Width="205" Height="60" runat="server" />C宝不仅是技术，更是便利！
28
29 </h1>
30 </div>
31 <nav class="navbar navbar-inverse">
32 <div class="container-md"> <%--container-fluid--%>
33 <div class="navbar-header">
34 <button type="button" class="navbar-toggle" data-toggle="collapse" data-target="#myNavbar">
35 <span class="icon-bar"></span>
36 <span class="icon-bar"></span>
37 <span class="icon-bar"></span>
38 </button>
39 <a class="navbar-brand" href="#">SSC</a>
40 </div>
41 <div class="collapse navbar-collapse" id="myNavbar">
42 <ul class="nav navbar-nav navbar-left">
43 <li class="active"><a href="#">主页</a></li>
44 <li><a href="/Web/MailList.aspx">联系方式</a></li>
45 <li><a href="/help.aspx">帮助查询</a></li>
46 </ul>
47 <ul class="nav navbar-nav navbar-right">
48 <li>
49 <a href="#" id="loginname">
50 <asp:Image ID="Image1" runat="server" ImageUrl="~/img/u12.png"></asp:Image>
51 <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>  访问
52 </a>
53 </li>
54 <li>
55 <a href="/Web/statistics.aspx">统计</a>
56 </li>
57 <li>
58 <a href="#" onclick="AddFavorite(window.location,document.title)" rel="sidebar">加入收藏</a>
59 </li>
60 </ul>
61 </div>
62 </div>
63 </nav>
64
65 <%--工具栏--%>
66 <div class="container-fluid">
67 <div class="row">
68 <%--增删改查按钮--%>
69 <div class="col-md-6">
70 <div id="toolbar1" class="btn-group">
71 <button id="btn_add" type="button" class="btn btn-info" onclick="mySpreadsheet.insertRow()">
72 <span class="glyphicon glyphicon-plus" aria-hidden="true"></span>新增行
73 </button>
74 <button id="btn_edit" type="button" class="btn btn-warning" onclick="mySpreadsheet.undo()">
75 <span class="glyphicon glyphicon-pencil" aria-hidden="true"></span>撤消更改
76 </button>
77 <button id="btn_delete" type="button" class="btn btn-danger" onclick="mySpreadsheet.deleteRow()">
78 <span class="glyphicon glyphicon-remove" aria-hidden="true"></span>删除行
79 </button>
80 <button id="btn_save" type="button" class="btn btn-success" onclick="mySpreadsheet.save()">
81 <span class="glyphicon glyphicon-edit" aria-hidden="true"></span>保存页面修改的数据
82 </button>
83
84 <input class="hidden" />
85 </div>
86 </div>
87
88 <div class="col-md-6" >
89 <div class="text-right">
90
91 <button id="btn_1" type="button" class="btn btn-warning" onclick="customSearch()">
92 <span class="glyphicon glyphicon-search" aria-hidden="true"></span>刷新
93 </button>
94 <button id="my_download" type="button" class="btn btn-success">
95 <span class="glyphicon glyphicon-plus" aria-hidden="true"></span>导出
96 </button>
97 </div>
98 </div>
99
100
101 <%--表格--%>
102 <div id="spreadsheet" class="container"></div>
103
104
105 </div>
106
107
108 </div>
109
110
111 </form>
112 </body>
113 <%--JS
114 <%--jquery和bootstrap--%>
115 <script src="../Scripts/jquery.min.js"></script>
116 <%--<script src="../Scripts/jquery-1.10.2.min.js"></script>--%>
117 <script src="../Scripts/bootstrap.min.js"></script>
118 <%--bootstrp-table--%>
119 <script src="../Scripts/bootstrap-table.min.js"></script>
120 <%--bootstrap-table编辑包--%>
121 <script src="../Scripts/bootstrap-editable.js"></script>
122 <script src="../Scripts/bootstrap-table-editable.js"></script>
123 <%--bootstrap-table导出包--%>
124 <script src="../Scripts/tableExport.js"></script>
125 <script src="../Scripts/bootstrap-table-export.js"></script>
126 <script src="../Scripts/FileSaver.min.js"></script>
127 <script src="../Scripts/xlsx.core.min.js"></script>
128 <%--bootstrap-table语言包--%>
129 <script src="../Scripts/bootstrap-table-zh-CN.min.js"></script>
130 <%--Jexcel包--%>
131 <script src="../Scripts/jexcel/jexcel.js"></script>
132 <script src="../Scripts/jexcel/jsuites.js"></script>
133 <%--Jexcel包 分页--%>
134 <script src="../Scripts/page/js/pagination.js"></script>
135
136 <%--图片--%>
137 <script src="../Scripts/material-ui.development.js"></script>
138
139 <%--js脚本--%>
140 <script type="text/javascript">
141
142 var mySpreadsheet = jspreadsheet(document.getElementById('spreadsheet'), {
143
144 url: 'Handler1.ashx', // 获取数据
145 search: true, // 搜索
146 async: true,
147 allowExport: true, // 是否允许导出
148 colWidths: [30, 80, 100, 100, 100, 80, 100, 100, 80, 80, 100, 70, 70, 70, 70, 80, 100], //列宽
149 tableOverflow: true, // 是否允许表溢出溢出的时候右边有那个滚动条
150 tableWidth: "1520px", // 表宽度
151 tableHeight: "430px", // 表高度
152 pagination: 10, // 显示分页，每页10行数据
153 paginationOptions: [10, 15, 25, 50], //用户自主选择每页需展示的数据条数
154
155
156 //鼠标右键菜单_翻译
157 text: {
158 copy: '复制',
159 paste: '粘贴',
160 insertANewRowBefore: '在此前插入行',
161 insertANewRowAfter: '在此后插入行',
162 deleteSelectedRows: '删除选定行',
163 saveAs: '保存为',
164 about: '关于',
165 noRecordsFound: '未找到记录',
166 showingPage: '显示第 {0} 页，共 {1} 页',
167 show: '显示',
168 entries: '条目',
169 insertANewColumnBefore: '在此前插入列',
170 insertANewColumnAfter: '在此后插入列',
171 renameThisColumn: '重命名列',
172 deleteSelectedColumns: '删除选定列',
173 orderAscending: '升序',
174 orderDescending: '降序',
175 editComments: '编辑注释',
176 addComments: '添加注释',
177 comments: '注释',
178 clearComments: '清除注释',
179 areYouSureToDeleteTheSelectedRows: '您确定要删除选定的行吗？',
180 areYouSureToDeleteTheSelectedColumns: '您确定要删除所选列吗？',
181 thisActionWillDestroyAnyExistingMergedCellsAreYouSure: '此操作将销毁所有现有的合并单元格。你确定吗',
182 thisActionWillClearYourSearchResultsAreYouSure: '此操作将清除您的搜索结果。你确定吗',
183 Search: '搜索',
184 thereIsAConflictWithAnotherMergedCell: '与另一个合并单元格发生冲突',
185 invalidMergeProperties: '合并无效',
186 cellAlreadyMerged: '单元格已合并',
187 noCellsSelected: '未选择单元格',
188
189 },
190
191
192 //日期选取器
193 options: {
194 format: 'DD/MM/YYYY', //日期格式
195 readonly: 0, //允许键盘输入日期
196 today: 0, //今天是默认值
197 time: 0, //显示时间选择器
198 resetButton: true, //显示重置按钮
199 placeholder: '', //占位符
200 // 翻译
201 months: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'],
202 weekdays: ['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'],
203 weekdays_short: ['Su', 'M', 'T', 'W', 'T', 'F', 'Sa'],
204 value: null, //值
205 onclose: null, //onclose事件
206 onchange: null, //onchange事件
207 fullscreen: false,//全屏显示（这是屏幕尺寸＜800时自动设置的）
208 },
209
210
211 //列属性
212 columns: [
213 {
214 title: 'checkbox',
215 field: 'checkbox', //第一列显示复选框，若单选，需加入singleSelect
216 type: 'checkbox',
217 }, {
218 field: 'id', //数据键(数据库字段名)
219 title: 'DBid', //列名 （页面显示）
220 sortable: true, //是否允许排序
221 align: 'center', //居中
222 footerFormatter: 'Total', //表底显示
223 },
224 { field: 'Soldto', title: 'Soldto', sortable: true, align: 'center', },
225 { field: 'MOsPONo', title: 'MOsPONo', sortable: true, align: 'center', },
226 { field: 'Item', title: 'Item', sortable: true, align: 'center', },
227 { field: 'Docrecodedate', title: 'Docrecodedate', sortable: true, align: 'center', },
228 { field: 'Firstdate', title: 'Firstdate', sortable: true, align: 'center', type: 'calendar', options: { format: 'YYYY/MM/DD' }, },
229 { field: 'SONo', title: 'SONo', sortable: true, align: 'center', type: 'calendar', options: { format: 'YYYY/MM/DD' }, },
230 { field: 'SOItem', title: 'SOItem', sortable: true, align: 'center', },
231 { field: 'Material', title: 'Material', sortable: true, align: 'center', },
232 { field: 'SOQty', title: 'SOQty', sortable: true, align: 'center', },
233 { field: 'Vendor', title: 'Vendor', sortable: true, align: 'center', },
234 { field: 'PCPIC', title: 'PCPIC', sortable: true, align: 'center', },
235 { field: 'DPT', title: 'DPT', sortable: true, align: 'center', },
236 { field: 'Deliverydate', title: 'Deliverydate', sortable: true, align: 'center', },
237 { field: 'PONo', title: 'PONo', sortable: true, align: 'center', },
238 { field: 'POItem', title: 'POItem', sortable: true, align: 'center', },
239 { field: 'POqty', title: 'POqty', sortable: true, align: 'center', },
240 { field: 'PackGRBal', title: 'PackGRBal', sortable: true, align: 'center', },
241 { field: 'POIssDate', title: 'POIssDate', sortable: true, align: 'center', },
242 { field: 'Priorityorder', title: 'Priorityorder', sortable: true, align: 'center', },
243 { field: 'Lastreply', title: 'Lastreply', sortable: true, align: 'center', },
244 { field: 'WHreservation', title: 'WHreservation', sortable: true, align: 'center', },
245 { field: 'DRcode', title: 'DRcode', sortable: true, align: 'center', },
246 { field: 'Causeofdelay', title: 'Causeofdelay', sortable: true, align: 'center', },
247 { field: 'Targetdate', title: 'Targetdate', sortable: true, align: 'center', },
248 { field: 'TargetQTY', title: 'TargetQTY', sortable: true, align: 'center', },
249 { field: 'Column27', title: 'Column27', sortable: true, align: 'center', },
250 { field: 'Column10', title: 'Column10', sortable: true, align: 'center', },
251 { field: 'Remark', title: 'Remark', sortable: true, align: 'center', },
252 { field: 'Column19', title: 'Column19', sortable: true, align: 'center', },
253 { field: 'destination', title: 'destination', sortable: true, align: 'center', },
254 { field: 'supplier', title: 'supplier', sortable: true, align: 'center', },
255 { field: 'Orderstatus', title: 'Orderstatus', sortable: true, align: 'center', },
256 { field: 'Inlibrarystate', title: 'Inlibrarystate', sortable: true, align: 'center', },
257 { field: 'ConfrimDel', title: 'ConfrimDel', sortable: true, align: 'center', },
258 { field: 'Dateofdelivery', title: 'Dateofdelivery', sortable: true, align: 'center', },
259 { field: 'Supplierdeliverytime', title: 'Supplierdeliverytime', sortable: true, align: 'center', },
260 { field: 'Purchaseperiod', title: 'Purchaseperiod', sortable: true, align: 'center', },
261 { field: 'Ordercode', title: 'Ordercode', sortable: true, align: 'center', },
262 { field: 'SalesinvoiceNo', title: 'SalesinvoiceNo', sortable: true, align: 'center', },
263 { field: '明細No', title: '明細No', sortable: true, align: 'center', },
264 { field: 'ETDQTY', title: 'ETDQTY', sortable: true, align: 'center', },
265 { field: '納入期日', title: '納入期日', sortable: true, align: 'center', type: 'calendar', options: { format: 'YYYY/MM/DD' }, },
266 { field: 'date2', title: 'date2', sortable: true, align: 'center', type: 'calendar', options: { format: 'YYYY/MM/DD' }, },
267 { field: 'ShippingMethod', title: 'ShippingMethod', sortable: true, align: 'center', },
268 { field: 'Deliverytimestorage', title: 'Deliverytimestorage', sortable: true, align: 'center', },
269 { field: '購買発注No', title: '購買発注No', sortable: true, align: 'center', },
270 { field: 'NO', title: 'NO', sortable: true, align: 'center', },
271 { field: 'Destination2', title: 'Destination2', sortable: true, align: 'center', },
272 { field: 'Buyer', title: 'Buyer', sortable: true, align: 'center', },
273 { field: 'QTY', title: 'QTY', sortable: true, align: 'center', },
274 { field: 'Remarks', title: 'Remarks', sortable: true, align: 'center', },
275 { field: 'No3', title: 'No3', sortable: true, align: 'center', },
276 { field: 'item4', title: 'item4', sortable: true, align: 'center', },
277 { field: 'pgrp', title: 'pgrp', sortable: true, align: 'center', },
278 { field: 'ic', title: 'ic', sortable: true, align: 'center', },
279 { field: 'ac', title: 'ac', sortable: true, align: 'center', },
280 { field: 'grqty', title: 'grqty', sortable: true, align: 'center', },
281 { field: 'loc', title: 'loc', sortable: true, align: 'center', },
282 { field: 'confirmedqty', title: 'confirmedqty', sortable: true, align: 'center', },
283 { field: 'reason', title: 'reason', sortable: true, align: 'center', },
284 { field: 'KEY1', title: 'KEY1', sortable: true, align: 'center', },
285 { field: 'KEY2', title: 'KEY2', sortable: true, align: 'center', },
286
287 ],
288 });
289
290
291 //导出,格式为csv文件 OK
292 $('#my_download').on('click', function () {
293 document.getElementById('spreadsheet').jexcel.download();
294 });
295
296
297 </script>
298
299
300
301
302 <script type="text/javascript">
303 //接收ajax返回的值时, ajax⼀定要设置为同步⽅式
304 function my_source_data() {
305 var a; //这里的值,是无所谓的
306 $.ajaxSetup({
307 async: false
308 })
309 $.ajax({
310 url: "SQL_search.ashx",
311 datatype: "json",
312 //"mytime": getNowTime() 不重复的时间变量,确保能刷新数据
313 data: { "RequestType": "sourcedata", "mytime": getNowTime(), "current_page": current_page, "page_rows": page_rows },
314 success: function (data1) {
315 a = data1;
316 }
317 });
318 return a;
319 }
320 </script>
321
322
323 </html>
标签：sortable,center,title,C#,align,改查,JEXCEL,field,true From： https://www.cnblogs.com/automationanywhere/p/17296702.html