首頁 > 開發者 > JavaScript
如何在web中實作類別似excel的表格控制項，excel表格控制項
Execl功能非常強大，內建的很多函數或公式可以大大提高對資料的加工處理能力。那麼在web中有沒有類似的控制項呢？經過一番搜尋，發現handsontable具備了基本的excel功能支援公式，同時能對資料進行即時編輯。另外支援拖動複製、Ctrl+C 、Ctrl+V 等等。在瀏覽器支援方面，它支援以下的瀏覽器： IE7+, FF, Chrome, Safari, Opera。
首先引入相關庫檔案，公式支援不包含在handsontable.full.js中，需要單獨引入：
1 <script src="http://handsontable.github.io/handsontable-ruleJS/lib/jquery/jquery-1.10.2.js"></script> 2 <script src="http://handsontable.github.io/handsontable-ruleJS/lib/handsontable/handsontable.full.js"></script> 3 <link rel="stylesheet" media="screen" href="http://handsontable.github.io/handsontable-ruleJS/lib/handsontable/handsontable.full.css"> 4 <script src="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/lib/lodash/lodash.js"></script> 5 <script src="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/lib/underscore.string/underscore.string.js"></script> 6 <script src="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/lib/moment/moment.js"></script> 7 <script src="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/lib/numeral/numeral.js"></script> 8 <script src="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/lib/numericjs/numeric.js"></script> 9 <script src="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/lib/js-md5/md5.js"></script>10 <script src="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/lib/jstat/jstat.js"></script>11 <script src="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/lib/formulajs/formula.js"></script>12 <script src="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/js/parser.js"></script>13 <script src="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/js/ruleJS.js"></script>14 <script src="http://handsontable.github.io/handsontable-ruleJS/lib/handsontable/handsontable.formula.js"></script>
在HTML中放置一個Div容器來存放handsontable控制項：
1 <body>2 <div id="handsontable-code"></div>3 </body>
在javascript代碼中，首先擷取div容器，然後建立表格控制項：
1 <script type="text/javascript"> 2 $(document).ready(function () { 3 4 var data1 = [ 5 ['=$B$2', "Maserati", "Mazda", "return 1+2;", 'return DataAccess.getScalar("select top 1 name from Cloud_Users where cellPhone=15895211486");', "=A$1"], 6 [2009, 0, 2941, 4303, 354, 5814], 7 [2010, 5, 2905, 2867, '=SUM(A4,2,3)', '=$B1'], 8 [2011, 4, 2517, 4822, 552, 6127], 9 [2012, '=SUM(A2:A5)', '=SUM(B5,E3)', '=A2/B2', 12, 4151]10 ];11 12 13 function negativeValueRenderer(instance, td, row, col, prop, value, cellProperties) {14 Handsontable.renderers.TextRenderer.apply(this, arguments);15 16 var escaped = Handsontable.helper.stringify(value),17 newvalue;18 19 if (escaped.indexOf('return') === 0) {20 //計算資料行為唯讀21 //cellProperties.readOnly = true;22 td.style.background = '#EEE';23 newvalue = document.createElement('span');24 $.ajax({25 //提交資料的類型 POST GET26 type: "POST",27 //提交的網址28 url: "/services/CSEngine.ashx",29 //提交的資料30 data: { code: value, code2: escaped },31 //返回資料的格式32 datatype: "html",//"xml", "html", "script", "json", "jsonp", "text".33 //在請求之前調用的函數34 //beforeSend: function () { $("#msg").html("logining"); },35 //成功返回之後調用的函數 36 success: function (data) {37 // $("#msg").html(decodeURI(data));38 newvalue.innerHTML = decodeURI(data);39 },40 //調用執行後調用的函數41 complete: function (XMLHttpRequest, textStatus) {42 //alert(XMLHttpRequest.responseText);43 // alert(textStatus);44 //HideLoading();45 },46 //調用出錯執行的函數47 error: function () {48 //請求出錯處理49 // alert('error')50 }51 });52 53 54 Handsontable.Dom.addEvent(newvalue, 'mousedown', function (e) {55 e.preventDefault(); // prevent selection quirk56 });57 58 Handsontable.Dom.empty(td);59 td.appendChild(newvalue);60 }61 // if row contains negative number62 if (parseInt(value, 10) < 0) {63 // add class "negative"64 td.className = 'negative';65 }66 67 68 }69 70 71 72 //類似excel進行拖放，公式會變73 var container1 = $('#handsontable-code');74 Handsontable.renderers.registerRenderer('negativeValueRenderer', negativeValueRenderer);75 container1.handsontable({76 data: data1,77 minSpareRows: 1,78 colHeaders: true,79 rowHeaders: true,80 contextMenu: true,81 manualColumnResize: true,82 formulas: true,83 cells: function (row, col, prop) {84 var cellProperties = {};85 var escaped = Handsontable.helper.stringify(this.instance.getData()[row][col]);86 if (escaped.indexOf('return')===0) {87 cellProperties.renderer = "negativeValueRenderer"; 88 }89 90 91 return cellProperties;92 }93 });94 95 });96 97 </script>
其中 =SUM(B5,E3)的公式是RuleJs提供的，return 1+2是自己實現的C#代碼指令碼，需要單擊解析：
1 public class CSEngine : IHttpHandler { 2 private static int count = 0; 3 public void ProcessRequest (HttpContext context) { 4 context.Response.ContentType = "text/plain"; 5 6 try 7 { 8 count++; 9 string ret = "";10 string code = context.Request["code"].ToString();11 if (string.IsNullOrEmpty(code))12 {13 ret = "參數錯誤";14 }15 else16 {17 ScriptOptions options = ScriptOptions.Default18 .AddReferences(19 Assembly.GetAssembly(typeof(DBServices.DataAccess))20 )21 //.AddImports("System.Data")22 //.AddImports("System.Data.SqlClient")23 .AddImports("DBServices");24 var state = CSharpScript.RunAsync(code, options).Result.ReturnValue;25 ret = state.ToString();26 27 state = null;28 options = null;29 }30 Console.WriteLine(count);31 context.Response.Write(ret);32 }33 catch(Exception ex)34 {35 //error36 Console.WriteLine(count);37 }38 }39 40 public bool IsReusable {41 get {42 return false;43 }44 }45 46 }
運行代碼，如下：
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
