Home > Developer > JavaScript
How to Implement excel-like table control and excel table control on the web
Execl is very powerful. Many built-in functions or formulas can greatly improve data processing capabilities. Are there any similar controls on the web? After some searches, it is found that handsontable has basic excel functions to support formulas and can edit data in real time. In addition, it supports drag copy, Ctrl + C, Ctrl + V, and so on. In terms of browser support, it supports the following browsers: IE7 +, FF, Chrome, Safari, and Opera.
First, introduce the relevant library file. The formula is not included in handsontable. full. js and needs to be introduced separately:
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>
Place a Div container in HTML to store the handsontable control:
1 <body>2 <div id="handsontable-code"></div>3 </body>
In javascript code, first obtain the div container and then create a table control:
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,430 3, 354,581 4], 7 [2010, 5, 2905,286 7, '= SUM (A4, 2011)', '= $ B1'], 8 [2517,482, 4, 552,612 2, 7], 9 [2012, '= SUM (A2: A5)', '= SUM (B5, E3)', '= A2/b2', 12,415 1] 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 // The calculation column is read-only 21 // cellProperties. readOnly = true; 22 td. style. background = '# EEE'; 23 newvalue = document. createElement ('span '); 24 $. ajax ({25 // type of submitted data POST GET26 type: "POST", 27 // submitted url 28 url: "/services/CSEngine. ashx ", 29 // 30 data submitted: {code: value, code2: escaped}, 31 // The format of the returned data 32 datatype:" html ", // "xml", "html", "script", "json", "jsonp", "text ". 33 // function 34 called before the request // beforeSend: function () {$ ("# msg" ining .html ("logining ");}, 35 // function 36 success: function (data) {37 // $ ("# msg" ).html (decodeURI (data); 38 newvalue. innerHTML = decodeURI (data); 39}, 40 // function 41 complete: function (XMLHttpRequest, textStatus) {42 // alert (XMLHttpRequest. responseText); 43 // alert (textStatus); 44 // HideLoading (); 45}, 46 // call the function 47 error: function () {48 // request error handling 49 // alert ('error') 50} 51}); 52 53 54 Handsontable. dom. addEvent (newvalue, 'mouseunder', 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 // drag and drop like excel, the formula will change to 73 var container1 = $ (' # handsontable-Code '); 74 Handsontable. renderers. registerRenderer ('signature', comment); 75 container1.handsontable ({76 data: data1, 77 minSpareRows: 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>
The formula = SUM (B5, E3) is provided by RuleJs, and return 1 + 2 is self-implemented C # code script. You need to click parse:
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 = "parameter error"; 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}
Run the Code as follows:
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
