1. jexcel_数据表_excel导入到页面.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="jexcel_数据表_excel导入到页面.aspx.cs" Inherits="jexcel_数据表_excel导入到页面" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>excel导入到页面</title> <!-- jquery --> <script src="Scripts/jquery/jquery-3.1.1.min.js"></script> <%--Jexcel包--%> <link rel="stylesheet" href="Scripts/jexcel/jexcel.css" /> <link rel="stylesheet" href="Scripts/jexcel/jsuites.css" /> <script src="Scripts/jexcel/jexcel.js"></script> <script src="Scripts/jexcel/jsuites.js"></script> <%--excel导入--%> <script src="Scripts/bootstrap/xlsx.core.min.js"></script> <%--excel导入按钮(将其他控件合并)--%> <style> .lqwvje-btn { display: inline-block; height: 38px; width: 78px; line-height: 38px; padding: 0 4px; background-color: #009688; color: #fff; white-space: nowrap; text-align: center; font-size: 14px; border: none; border-radius: 2px; cursor: pointer; } </style> </head> <body> <form id="form1" runat="server"> <div> <label id="realBtn" class="lqwvje-btn"> <%--lqwvje-btn：独显excel导入按钮(将其他控件合并)--%> <input type="file" id="testFile" hidden="hidden" /> 上传文件 </label> <button id="my_refresh2" class="lqwvje-btn" onclick="location.reload()"> <span class="glyphicon glyphicon-refresh" aria-hidden="true" ></span>刷新页面 </button> </div> <div id="spreadsheet2"></div> </form> </body> <%--excel导入到页面--%> <script type="text/javascript"> //原创来自 www.luofenming.com //首先监听input框的变动，选中一个新的文件会触发change事件 document.querySelector("#testFile").addEventListener("change", function () { //获取到选中的文件 var file = document.querySelector("#testFile").files[0]; //判断文件大小 //if (file.size > 1024 * 1024) { // alert('当前文件大小:' + Math.floor(file.size / 1024) + 'KB,上传文件不能大于1024KB'); // return false; //} //判断文件类型 var type = file.name.split('.'); if (type[type.length - 1] !== 'xlsx' && type[type.length - 1] !== 'xls' && type[type.length - 1] !== 'XLS' && type[type.length - 1] !== 'XLSX') { alert('只能选择excel文件导入'); return false; } //新建一个对象_读取文件 const reader = new FileReader(); //读取二进制字符串 reader.readAsBinaryString(file); //文件读取完时触发一个方法 reader.onload = function (e) { const data1 = e.target.result; //把读出来的二进制字符串赋值给data1变量 const zzexcel = window.XLS.read(data1, { //从XLS文件中读取 type: 'binary' //数据类型_二进制数据 }); //申明变量result const result = []; //从EXCEL中取值，放入变量result for (let i = 0; i < zzexcel.SheetNames.length; i++) { const newData = window.XLS.utils.sheet_to_json(zzexcel.Sheets[zzexcel.SheetNames[i]]); //xlsx.core.min.js插件 将excel数据取出 result.push(...newData) } var data2 = JSON.stringify(result) //用变量接收JSON数据，JSON.stringify(result)将对象变为字符串 var excel_data = JSON.parse(data2); //JSON.parse()将字符串变为对象 。。。重要、重要 //console.log(excel_data); //F12的控制台中_确认 jspreadsheet(document.getElementById('spreadsheet2'), { //往jexcel中装入数据 data: excel_data, }); ////逐条提取数据 for (var i = 0; i < result.length; i++) { //console.log(JSON.stringify(result[i])); //EXCEL中每一行数据 F12的控制台中_确认 var strJsonData = JSON.stringify(result[i]); //alert(strJsonData); //弹窗_确认 //} //将excel_data传给后台,写入数据库 $.ajax({ url: "/jexcel_数据表_excel导入到页面.ashx", datatype: "json", data: { "RequestType": "inserted_excel_data", "excel_data": strJsonData }, success: function (data) { //alert("excel导入成功") } }); } //刷新表格 ？？？？没成功？？？ document.getElementById('spreadsheet2').jexcel.refresh(); } }); </script> <script type="text/javascript"> var mySpreadsheet = jspreadsheet(document.getElementById('spreadsheet2'), { url: '/jexcel_数据表_excel导入到页面.ashx', // 获取数据 search: true, // 搜索 搜索自定义的新事件 onsearchstart、onserchrow * async: true, allowExport: true, // 是否允许导出 colWidths: [50, 160, 100, 100, 120, 80, 100, 100, 100, 100, 100, 100, 100, 70, 70, 80, 100], //列宽 tableOverflow: true, // 是否允许表溢出溢出的时候右边有那个滚动条 tableWidth: "1420px", // 表宽度 tableHeight: "430px", // 表高度 pagination: 15, // 每页显示数据 * paginationOptions: [10, 15, 20, 50], //用户自主选择每页需展示的数据条数 * tabs: true, //标签 toolbar: true, //工具栏 需加载<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" /> 目前使用不可 //toolbar: '#toolbar', //工具栏 editable: true, // 启用行内编辑 striped: true, //表格斑马纹 sortable: true, //排序 sortname: "ID", //设置默认的排序列 sortOrder: "asc", //排序方式 asc升序 desc降序 //列属性 OK columns: [ { field: 'ID', title: 'DBID', sortable: true, align: 'center', }, { field: 'STARTE_DATE', title: 'STARTE_DATE', sortable: true, align: 'center', }, { field: 'USE_NAME', title: 'USE_NAME', sortable: true, align: 'center', }, { field: 'MACHINE_NAME', title: 'MACHINE_NAME', sortable: true, align: 'center', }, { field: 'MATNR', title: 'MATNR', sortable: true, align: 'center', }, { field: 'SBOX_QTY', title: 'SBOX_QTY', sortable: true, align: 'center', }, { field: 'BOX_QTY', title: 'BOX_QTY', sortable: true, align: 'center', }, { field: 'PO_QTY', title: 'PO_QTY', sortable: true, align: 'center', }, { field: 'PO_CODE', title: 'PO_CODE', sortable: true, align: 'center', }, { field: 'TOTAL_QTY', title: 'TOTAL_QTY', sortable: true, align: 'center', }, { field: 'SURPLUS_QTY', title: 'SURPLUS_QTY', sortable: true, align: 'center', }, { field: 'ST_TIME', title: 'ST_TIME', sortable: true, align: 'center', }, { field: 'END_ST', title: 'END_ST', sortable: true, align: 'center', }, { field: 'Remark', title: 'Remark', sortable: true, align: 'center', }, ], }); </script> </html>
2. jexcel_数据表_excel导入到页面.ashx (后端逻辑)
<%@ WebHandler Language="C#" Class="jexcel_数据表_excel导入到页面" %> using System; using System.Web; using System.Data; using System.IO; using System.Data.SqlClient; //数据库 using Newtonsoft.Json; //操作json库 using System.Text; public class jexcel_数据表_excel导入到页面 : IHttpHandler { CommonClass class1 = new CommonClass(); //创建一个简单的Person类 public class Person { public string No; public int ID; public string STARTE_DATE; public string USE_NAME; public string MACHINE_NAME; public string MATNR; public string SBOX_QTY; public string BOX_QTY; public string PO_QTY; public string PO_CODE; public string TOTAL_QTY; public string SURPLUS_QTY; public string ST_TIME; public string END_ST; public string Remark; } string excel_data; public void ProcessRequest(HttpContext context) { context.Response.ContentType = "text/plain"; context.Response.Write(mysourcestring()); //显示数据表 //前端获取excel表数据_写入数据库 if (context.Request["RequestType"] == "inserted_excel_data") { //接受页面传来的值 string excel_data = context.Request["excel_data"].ToString(); //字符处理，去掉斜杠“/” StringBuilder s0 = new StringBuilder(excel_data); //去掉JSON格式内的反斜杠 var json = JsonConvert.DeserializeObject<Person>(s0.ToString()); //反序列 //System.Diagnostics.Debug.WriteLine("s0 : " + s0); //确认 (调试-窗口-输出) string s2 = "select count(ID) from Exp_PILEUP WHERE ID=" + json.ID ; //统计数据条数 string ss = class1.ExecScalar(class1.GetConnection2(), s2); System.Diagnostics.Debug.WriteLine("s2 : " + s2); //确认 (调试-窗口-输出) //操作数据库 SqlConnection conn = new SqlConnection("server=150.17.232.180;database=SSC;uid=sscosd;pwd=BtQasYxonQuZqukIYkGM"); conn.Open(); SqlCommand cmd1 = new SqlCommand(s2, conn); //用来执行查询语句 cmd1.ExecuteNonQuery(); //执行SQL语句，返回受影响的行数 cmd1.Dispose(); //释放资源 conn.Close(); //如果ID存在，那么ss大于1，更新数据；如果ID不存在，那么ss=0，插入新数据 if (ss == "0") { //string s1 = "insert into Exp_PILEUP (USE_NAME) values ('" + json.USE_NAME + "')"; string s1 = "insert into Exp_PILEUP (STARTE_DATE,USE_NAME,MACHINE_NAME,MATNR,SBOX_QTY,BOX_QTY,PO_QTY,PO_CODE,TOTAL_QTY,SURPLUS_QTY,ST_TIME,END_ST,Remark) values ('" //+ json.ID + "','" + json.STARTE_DATE + "','" + json.USE_NAME + "','" + json.MACHINE_NAME + "','" + json.MATNR + "','" + json.SBOX_QTY + "','" + json.BOX_QTY + "','" + json.PO_QTY + "','" + json.PO_CODE + "','" + json.TOTAL_QTY + "','" + json.SURPLUS_QTY + "','" + json.ST_TIME + "','" + json.END_ST + "','" + json.Remark + "')"; //往数据库写入 //SqlConnection conn = new SqlConnection("server=150.17.232.180;database=SSC;uid=sscosd;pwd=BtQasYxonQuZqukIYkGM"); conn.Open(); SqlCommand cmd2 = new SqlCommand(s1, conn); //用来执行查询语句 cmd2.ExecuteNonQuery(); //执行SQL语句，返回受影响的行数 cmd2.Dispose(); //释放资源 conn.Close(); } else { string s3 ="update Exp_PILEUP set " + "STARTE_DATE ='" + json.STARTE_DATE + "',"+ "USE_NAME ='" + json.USE_NAME + "',"+ "MACHINE_NAME ='" + json.MACHINE_NAME + "',"+ "MATNR ='" + json.MATNR + "',"+ "SBOX_QTY ='" + json.SBOX_QTY + "',"+ "BOX_QTY ='" + json.BOX_QTY + "',"+ "PO_QTY ='" + json.PO_QTY + "',"+ "PO_CODE ='" + json.PO_CODE + "',"+ "TOTAL_QTY ='" + json.TOTAL_QTY + "',"+ "SURPLUS_QTY ='" + json.SURPLUS_QTY + "',"+ "ST_TIME ='" + json.ST_TIME + "',"+ "END_ST ='" + json.END_ST + "',"+ "Remark ='" + json.Remark + "'"+ " where id=" + json.ID; //System.Diagnostics.Debug.WriteLine("ss>0 数据更新 " ); //确认 (调试-窗口-输出) //往数据库写入 //SqlConnection conn = new SqlConnection("server=150.17.232.180;database=SSC;uid=sscosd;pwd=BtQasYxonQuZqukIYkGM"); conn.Open(); SqlCommand cmd3 = new SqlCommand(s3, conn); //用来执行查询语句 cmd3.ExecuteNonQuery(); //执行SQL语句，返回受影响的行数 cmd3.Dispose(); //释放资源 conn.Close(); }; }; } //打开网页显示数据表 private string mysourcestring() { //返回的样式例 [{"id":5989,"type":"ZVDH","Shipto":"S716000456","Soldto":"S7160"},{"id":6003,"type":"ZPPH","Shipto":"S2000050","Soldto":"S2000"}] string s0 = "select * from Exp_PILEUP ORDER BY ID"; SqlConnection conn = new SqlConnection("server=150.17.232.180;database=SSC;uid=sscosd;pwd=BtQasYxonQuZqukIYkGM"); conn.Open(); SqlCommand cmd = new SqlCommand(s0, conn); //用来执行查询语句 SqlDataAdapter sda = new SqlDataAdapter(); //数据库适配器，用来充当数据库与数据集之间的桥梁 sda.SelectCommand = cmd; //选择命令向数据库发送（发送查询语句） DataSet ds = new DataSet(); //创建一个数据集对象,相当于小型数据库，它当中存放若干个数据块 sda.Fill(ds, "cs"); //Fill: 填充 把数据填充小型数据库的“CS”表中。 System.Data.DataTable table1 = ds.Tables[0]; string jsonstr = JsonConvert.SerializeObject(table1); //datatable格式转换Json格式 前提要导入 using Newtonsoft.Json conn.Close(); return jsonstr; } public bool IsReusable { get { return false; } } }