How to implement an excel-like table control in the web
Execl功能非常强大,内置的很多函数或公式可以大大提高对数据的加工处理能力。那么在web中有没有类似的控件呢?经过一番搜寻,发现handsontable具备了基本的excel功能支持公式,同时能对数据进行实时编辑。另外支持拖动复制、Ctrl+C 、Ctrl+V 等等。在浏览器支持方面,它支持以下的浏览器: IE7+, FF, Chrome, Safari, Opera。
首先引入相关库文件,公式支持不包含在handsontable.full.js中,需要单独引入:
<span style="color: #008080;"> 1</span> <span style="color: #0000ff;"><</span><span style="color: #800000;">script </span><span style="color: #ff0000;">src</span><span style="color: #0000ff;">="http://handsontable.github.io/handsontable-ruleJS/lib/jquery/jquery-1.10.2.js"</span><span style="color: #0000ff;">></</span><span style="color: #800000;">script</span><span style="color: #0000ff;">></span> <span style="color: #008080;"> 2</span> <span style="color: #0000ff;"><</span><span style="color: #800000;">script </span><span style="color: #ff0000;">src</span><span style="color: #0000ff;">="http://handsontable.github.io/handsontable-ruleJS/lib/handsontable/handsontable.full.js"</span><span style="color: #0000ff;">></</span><span style="color: #800000;">script</span><span style="color: #0000ff;">></span> <span style="color: #008080;"> 3</span> <span style="color: #0000ff;"><</span><span style="color: #800000;">link </span><span style="color: #ff0000;">rel</span><span style="color: #0000ff;">="stylesheet"</span><span style="color: #ff0000;"> media</span><span style="color: #0000ff;">="screen"</span><span style="color: #ff0000;"> href</span><span style="color: #0000ff;">="http://handsontable.github.io/handsontable-ruleJS/lib/handsontable/handsontable.full.css"</span><span style="color: #0000ff;">></span> <span style="color: #008080;"> 4</span> <span style="color: #0000ff;"><</span><span style="color: #800000;">script </span><span style="color: #ff0000;">src</span><span style="color: #0000ff;">="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/lib/lodash/lodash.js"</span><span style="color: #0000ff;">></</span><span style="color: #800000;">script</span><span style="color: #0000ff;">></span> <span style="color: #008080;"> 5</span> <span style="color: #0000ff;"><</span><span style="color: #800000;">script </span><span style="color: #ff0000;">src</span><span style="color: #0000ff;">="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/lib/underscore.string/underscore.string.js"</span><span style="color: #0000ff;">></</span><span style="color: #800000;">script</span><span style="color: #0000ff;">></span> <span style="color: #008080;"> 6</span> <span style="color: #0000ff;"><</span><span style="color: #800000;">script </span><span style="color: #ff0000;">src</span><span style="color: #0000ff;">="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/lib/moment/moment.js"</span><span style="color: #0000ff;">></</span><span style="color: #800000;">script</span><span style="color: #0000ff;">></span> <span style="color: #008080;"> 7</span> <span style="color: #0000ff;"><</span><span style="color: #800000;">script </span><span style="color: #ff0000;">src</span><span style="color: #0000ff;">="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/lib/numeral/numeral.js"</span><span style="color: #0000ff;">></</span><span style="color: #800000;">script</span><span style="color: #0000ff;">></span> <span style="color: #008080;"> 8</span> <span style="color: #0000ff;"><</span><span style="color: #800000;">script </span><span style="color: #ff0000;">src</span><span style="color: #0000ff;">="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/lib/numericjs/numeric.js"</span><span style="color: #0000ff;">></</span><span style="color: #800000;">script</span><span style="color: #0000ff;">></span> <span style="color: #008080;"> 9</span> <span style="color: #0000ff;"><</span><span style="color: #800000;">script </span><span style="color: #ff0000;">src</span><span style="color: #0000ff;">="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/lib/js-md5/md5.js"</span><span style="color: #0000ff;">></</span><span style="color: #800000;">script</span><span style="color: #0000ff;">></span> <span style="color: #008080;">10</span> <span style="color: #0000ff;"><</span><span style="color: #800000;">script </span><span style="color: #ff0000;">src</span><span style="color: #0000ff;">="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/lib/jstat/jstat.js"</span><span style="color: #0000ff;">></</span><span style="color: #800000;">script</span><span style="color: #0000ff;">></span> <span style="color: #008080;">11</span> <span style="color: #0000ff;"><</span><span style="color: #800000;">script </span><span style="color: #ff0000;">src</span><span style="color: #0000ff;">="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/lib/formulajs/formula.js"</span><span style="color: #0000ff;">></</span><span style="color: #800000;">script</span><span style="color: #0000ff;">></span> <span style="color: #008080;">12</span> <span style="color: #0000ff;"><</span><span style="color: #800000;">script </span><span style="color: #ff0000;">src</span><span style="color: #0000ff;">="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/js/parser.js"</span><span style="color: #0000ff;">></</span><span style="color: #800000;">script</span><span style="color: #0000ff;">></span> <span style="color: #008080;">13</span> <span style="color: #0000ff;"><</span><span style="color: #800000;">script </span><span style="color: #ff0000;">src</span><span style="color: #0000ff;">="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/js/ruleJS.js"</span><span style="color: #0000ff;">></</span><span style="color: #800000;">script</span><span style="color: #0000ff;">></span> <span style="color: #008080;">14</span> <span style="color: #0000ff;"><</span><span style="color: #800000;">script </span><span style="color: #ff0000;">src</span><span style="color: #0000ff;">="http://handsontable.github.io/handsontable-ruleJS/lib/handsontable/handsontable.formula.js"</span><span style="color: #0000ff;">></</span><span style="color: #800000;">script</span><span style="color: #0000ff;">></span>
在HTML中放置一个Div容器来存放handsontable控件:
<span style="color: #008080;">1</span> <span style="color: #0000ff;"><</span><span style="color: #800000;">body</span><span style="color: #0000ff;">></span> <span style="color: #008080;">2</span> <span style="color: #0000ff;"><</span><span style="color: #800000;">div </span><span style="color: #ff0000;">id</span><span style="color: #0000ff;">="handsontable-code"</span><span style="color: #0000ff;">></</span><span style="color: #800000;">div</span><span style="color: #0000ff;">></span> <span style="color: #008080;">3</span> <span style="color: #0000ff;"></</span><span style="color: #800000;">body</span><span style="color: #0000ff;">></span>
在javascript代码中,首先获取div容器,然后创建表格控件:
<span style="color: #008080;"> 1</span> <script type="text/javascript"> <span style="color: #008080;"> 2</span> $(document).ready(<span style="color: #0000ff;">function</span><span style="color: #000000;"> () { </span><span style="color: #008080;"> 3</span> <span style="color: #008080;"> 4</span> <span style="color: #0000ff;">var</span> data1 =<span style="color: #000000;"> [ </span><span style="color: #008080;"> 5</span> ['=$B$2', "Maserati", "Mazda", "return 1+2;", 'return DataAccess.getScalar("select top 1 name from Cloud_Users where cellPhone=15895211486");', "=A$1"<span style="color: #000000;">], </span><span style="color: #008080;"> 6</span> [2009, 0, 2941, 4303, 354, 5814<span style="color: #000000;">], </span><span style="color: #008080;"> 7</span> [2010, 5, 2905, 2867, '=SUM(A4,2,3)', '=$B1'<span style="color: #000000;">], </span><span style="color: #008080;"> 8</span> [2011, 4, 2517, 4822, 552, 6127<span style="color: #000000;">], </span><span style="color: #008080;"> 9</span> [2012, '=SUM(A2:A5)', '=SUM(B5,E3)', '=A2/B2', 12, 4151<span style="color: #000000;">] </span><span style="color: #008080;">10</span> <span style="color: #000000;"> ]; </span><span style="color: #008080;">11</span> <span style="color: #008080;">12</span> <span style="color: #008080;">13</span> <span style="color: #0000ff;">function</span><span style="color: #000000;"> negativeValueRenderer(instance, td, row, col, prop, value, cellProperties) { </span><span style="color: #008080;">14</span> Handsontable.renderers.TextRenderer.apply(<span style="color: #0000ff;">this</span><span style="color: #000000;">, arguments); </span><span style="color: #008080;">15</span> <span style="color: #008080;">16</span> <span style="color: #0000ff;">var</span> escaped =<span style="color: #000000;"> Handsontable.helper.stringify(value), </span><span style="color: #008080;">17</span> <span style="color: #000000;"> newvalue; </span><span style="color: #008080;">18</span> <span style="color: #008080;">19</span> <span style="color: #0000ff;">if</span> (escaped.indexOf('return') === 0<span style="color: #000000;">) { </span><span style="color: #008080;">20</span> <span style="color: #008000;">//</span><span style="color: #008000;">计算列为只读</span> <span style="color: #008080;">21</span> <span style="color: #008000;">//</span><span style="color: #008000;">cellProperties.readOnly = true;</span> <span style="color: #008080;">22</span> td.style.background = '#EEE'<span style="color: #000000;">; </span><span style="color: #008080;">23</span> newvalue = document.createElement('span'<span style="color: #000000;">); </span><span style="color: #008080;">24</span> <span style="color: #000000;"> $.ajax({ </span><span style="color: #008080;">25</span> <span style="color: #008000;">//</span><span style="color: #008000;">提交数据的类型 POST GET</span> <span style="color: #008080;">26</span> type: "POST"<span style="color: #000000;">, </span><span style="color: #008080;">27</span> <span style="color: #008000;">//</span><span style="color: #008000;">提交的网址</span> <span style="color: #008080;">28</span> url: "/services/CSEngine.ashx"<span style="color: #000000;">, </span><span style="color: #008080;">29</span> <span style="color: #008000;">//</span><span style="color: #008000;">提交的数据</span> <span style="color: #008080;">30</span> <span style="color: #000000;"> data: { code: value, code2: escaped }, </span><span style="color: #008080;">31</span> <span style="color: #008000;">//</span><span style="color: #008000;">返回数据的格式</span> <span style="color: #008080;">32</span> datatype: "html",<span style="color: #008000;">//</span><span style="color: #008000;">"xml", "html", "script", "json", "jsonp", "text".</span> <span style="color: #008080;">33</span> <span style="color: #008000;">//</span><span style="color: #008000;">在请求之前调用的函数</span> <span style="color: #008080;">34</span> <span style="color: #008000;">//</span><span style="color: #008000;">beforeSend: function () { $("#msg").html("logining"); },</span> <span style="color: #008080;">35</span> <span style="color: #008000;">//</span><span style="color: #008000;">成功返回之后调用的函数 </span> <span style="color: #008080;">36</span> success: <span style="color: #0000ff;">function</span><span style="color: #000000;"> (data) { </span><span style="color: #008080;">37</span> <span style="color: #008000;">//</span><span style="color: #008000;"> $("#msg").html(decodeURI(data));</span> <span style="color: #008080;">38</span> newvalue.innerHTML =<span style="color: #000000;"> decodeURI(data); </span><span style="color: #008080;">39</span> <span style="color: #000000;"> }, </span><span style="color: #008080;">40</span> <span style="color: #008000;">//</span><span style="color: #008000;">调用执行后调用的函数</span> <span style="color: #008080;">41</span> complete: <span style="color: #0000ff;">function</span><span style="color: #000000;"> (XMLHttpRequest, textStatus) { </span><span style="color: #008080;">42</span> <span style="color: #008000;">//</span><span style="color: #008000;">alert(XMLHttpRequest.responseText);</span> <span style="color: #008080;">43</span> <span style="color: #008000;">//</span><span style="color: #008000;"> alert(textStatus);</span> <span style="color: #008080;">44</span> <span style="color: #008000;">//</span><span style="color: #008000;">HideLoading();</span> <span style="color: #008080;">45</span> <span style="color: #000000;"> }, </span><span style="color: #008080;">46</span> <span style="color: #008000;">//</span><span style="color: #008000;">调用出错执行的函数</span> <span style="color: #008080;">47</span> error: <span style="color: #0000ff;">function</span><span style="color: #000000;"> () { </span><span style="color: #008080;">48</span> <span style="color: #008000;">//</span><span style="color: #008000;">请求出错处理</span> <span style="color: #008080;">49</span> <span style="color: #008000;">//</span><span style="color: #008000;"> alert('error')</span> <span style="color: #008080;">50</span> <span style="color: #000000;"> } </span><span style="color: #008080;">51</span> <span style="color: #000000;"> }); </span><span style="color: #008080;">52</span> <span style="color: #008080;">53</span> <span style="color: #008080;">54</span> Handsontable.Dom.addEvent(newvalue, 'mousedown', <span style="color: #0000ff;">function</span><span style="color: #000000;"> (e) { </span><span style="color: #008080;">55</span> e.preventDefault(); <span style="color: #008000;">//</span><span style="color: #008000;"> prevent selection quirk</span> <span style="color: #008080;">56</span> <span style="color: #000000;"> }); </span><span style="color: #008080;">57</span> <span style="color: #008080;">58</span> <span style="color: #000000;"> Handsontable.Dom.empty(td); </span><span style="color: #008080;">59</span> <span style="color: #000000;"> td.appendChild(newvalue); </span><span style="color: #008080;">60</span> <span style="color: #000000;"> } </span><span style="color: #008080;">61</span> <span style="color: #008000;">//</span><span style="color: #008000;"> if row contains negative number</span> <span style="color: #008080;">62</span> <span style="color: #0000ff;">if</span> (parseInt(value, 10) < 0<span style="color: #000000;">) { </span><span style="color: #008080;">63</span> <span style="color: #008000;">//</span><span style="color: #008000;"> add class "negative"</span> <span style="color: #008080;">64</span> td.className = 'negative'<span style="color: #000000;">; </span><span style="color: #008080;">65</span> <span style="color: #000000;"> } </span><span style="color: #008080;">66</span> <span style="color: #008080;">67</span> <span style="color: #008080;">68</span> <span style="color: #000000;"> } </span><span style="color: #008080;">69</span> <span style="color: #008080;">70</span> <span style="color: #008080;">71</span> <span style="color: #008080;">72</span> <span style="color: #008000;">//</span><span style="color: #008000;">类似excel进行拖放,公式会变</span> <span style="color: #008080;">73</span> <span style="color: #0000ff;">var</span> container1 = $('#handsontable-code'<span style="color: #000000;">); </span><span style="color: #008080;">74</span> Handsontable.renderers.registerRenderer('negativeValueRenderer'<span style="color: #000000;">, negativeValueRenderer); </span><span style="color: #008080;">75</span> <span style="color: #000000;"> container1.handsontable({ </span><span style="color: #008080;">76</span> <span style="color: #000000;"> data: data1, </span><span style="color: #008080;">77</span> minSpareRows: 1<span style="color: #000000;">, </span><span style="color: #008080;">78</span> colHeaders: <span style="color: #0000ff;">true</span><span style="color: #000000;">, </span><span style="color: #008080;">79</span> rowHeaders: <span style="color: #0000ff;">true</span><span style="color: #000000;">, </span><span style="color: #008080;">80</span> contextMenu: <span style="color: #0000ff;">true</span><span style="color: #000000;">, </span><span style="color: #008080;">81</span> manualColumnResize: <span style="color: #0000ff;">true</span><span style="color: #000000;">, </span><span style="color: #008080;">82</span> formulas: <span style="color: #0000ff;">true</span><span style="color: #000000;">, </span><span style="color: #008080;">83</span> cells: <span style="color: #0000ff;">function</span><span style="color: #000000;"> (row, col, prop) { </span><span style="color: #008080;">84</span> <span style="color: #0000ff;">var</span> cellProperties =<span style="color: #000000;"> {}; </span><span style="color: #008080;">85</span> <span style="color: #0000ff;">var</span> escaped = Handsontable.helper.stringify(<span style="color: #0000ff;">this</span><span style="color: #000000;">.instance.getData()[row][col]); </span><span style="color: #008080;">86</span> <span style="color: #0000ff;">if</span> (escaped.indexOf('return')===0<span style="color: #000000;">) { </span><span style="color: #008080;">87</span> cellProperties.renderer = "negativeValueRenderer"<span style="color: #000000;">; </span><span style="color: #008080;">88</span> <span style="color: #000000;"> } </span><span style="color: #008080;">89</span> <span style="color: #008080;">90</span> <span style="color: #008080;">91</span> <span style="color: #0000ff;">return</span><span style="color: #000000;"> cellProperties; </span><span style="color: #008080;">92</span> <span style="color: #000000;"> } </span><span style="color: #008080;">93</span> <span style="color: #000000;"> }); </span><span style="color: #008080;">94</span> <span style="color: #008080;">95</span> <span style="color: #000000;">}); </span><span style="color: #008080;">96</span> <span style="color: #008080;">97</span> </script>
其中 =SUM(B5,E3)的公式是RuleJs提供的,return 1+2是自己实现的C#代码脚本,需要单击解析:
<span style="color: #008080;"> 1</span> <span style="color: #0000ff;">public</span> <span style="color: #0000ff;">class</span><span style="color: #000000;"> CSEngine : IHttpHandler { </span><span style="color: #008080;"> 2</span> <span style="color: #0000ff;">private</span> <span style="color: #0000ff;">static</span> <span style="color: #0000ff;">int</span> count = <span style="color: #800080;">0</span><span style="color: #000000;">; </span><span style="color: #008080;"> 3</span> <span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> ProcessRequest (HttpContext context) { </span><span style="color: #008080;"> 4</span> context.Response.ContentType = <span style="color: #800000;">"</span><span style="color: #800000;">text/plain</span><span style="color: #800000;">"</span><span style="color: #000000;">; </span><span style="color: #008080;"> 5</span> <span style="color: #008080;"> 6</span> <span style="color: #0000ff;">try</span> <span style="color: #008080;"> 7</span> <span style="color: #000000;"> { </span><span style="color: #008080;"> 8</span> count++<span style="color: #000000;">; </span><span style="color: #008080;"> 9</span> <span style="color: #0000ff;">string</span> ret = <span style="color: #800000;">""</span><span style="color: #000000;">; </span><span style="color: #008080;">10</span> <span style="color: #0000ff;">string</span> code = context.Request[<span style="color: #800000;">"</span><span style="color: #800000;">code</span><span style="color: #800000;">"</span><span style="color: #000000;">].ToString(); </span><span style="color: #008080;">11</span> <span style="color: #0000ff;">if</span> (<span style="color: #0000ff;">string</span><span style="color: #000000;">.IsNullOrEmpty(code)) </span><span style="color: #008080;">12</span> <span style="color: #000000;"> { </span><span style="color: #008080;">13</span> ret = <span style="color: #800000;">"</span><span style="color: #800000;">参数错误</span><span style="color: #800000;">"</span><span style="color: #000000;">; </span><span style="color: #008080;">14</span> <span style="color: #000000;"> } </span><span style="color: #008080;">15</span> <span style="color: #0000ff;">else</span> <span style="color: #008080;">16</span> <span style="color: #000000;"> { </span><span style="color: #008080;">17</span> ScriptOptions options =<span style="color: #000000;"> ScriptOptions.Default </span><span style="color: #008080;">18</span> <span style="color: #000000;"> .AddReferences( </span><span style="color: #008080;">19</span> Assembly.GetAssembly(<span style="color: #0000ff;">typeof</span><span style="color: #000000;">(DBServices.DataAccess)) </span><span style="color: #008080;">20</span> <span style="color: #000000;"> ) </span><span style="color: #008080;">21</span> <span style="color: #008000;">//</span><span style="color: #008000;">.AddImports("System.Data") </span><span style="color: #008080;">22</span> <span style="color: #008000;">//</span><span style="color: #008000;">.AddImports("System.Data.SqlClient")</span> <span style="color: #008080;">23</span> .AddImports(<span style="color: #800000;">"</span><span style="color: #800000;">DBServices</span><span style="color: #800000;">"</span><span style="color: #000000;">); </span><span style="color: #008080;">24</span> <span style="color: #0000ff;">var</span> state =<span style="color: #000000;"> CSharpScript.RunAsync(code, options).Result.ReturnValue; </span><span style="color: #008080;">25</span> ret =<span style="color: #000000;"> state.ToString(); </span><span style="color: #008080;">26</span> <span style="color: #008080;">27</span> state = <span style="color: #0000ff;">null</span><span style="color: #000000;">; </span><span style="color: #008080;">28</span> options = <span style="color: #0000ff;">null</span><span style="color: #000000;">; </span><span style="color: #008080;">29</span> <span style="color: #000000;"> } </span><span style="color: #008080;">30</span> <span style="color: #000000;"> Console.WriteLine(count); </span><span style="color: #008080;">31</span> <span style="color: #000000;"> context.Response.Write(ret); </span><span style="color: #008080;">32</span> <span style="color: #000000;"> } </span><span style="color: #008080;">33</span> <span style="color: #0000ff;">catch</span><span style="color: #000000;">(Exception ex) </span><span style="color: #008080;">34</span> <span style="color: #000000;"> { </span><span style="color: #008080;">35</span> <span style="color: #008000;">//</span><span style="color: #008000;">error</span> <span style="color: #008080;">36</span> <span style="color: #000000;"> Console.WriteLine(count); </span><span style="color: #008080;">37</span> <span style="color: #000000;"> } </span><span style="color: #008080;">38</span> <span style="color: #000000;"> } </span><span style="color: #008080;">39</span> <span style="color: #008080;">40</span> <span style="color: #0000ff;">public</span> <span style="color: #0000ff;">bool</span><span style="color: #000000;"> IsReusable { </span><span style="color: #008080;">41</span> <span style="color: #0000ff;">get</span><span style="color: #000000;"> { </span><span style="color: #008080;">42</span> <span style="color: #0000ff;">return</span> <span style="color: #0000ff;">false</span><span style="color: #000000;">; </span><span style="color: #008080;">43</span> <span style="color: #000000;"> } </span><span style="color: #008080;">44</span> <span style="color: #000000;"> } </span><span style="color: #008080;">45</span> <span style="color: #008080;">46</span> }
运行代码,如下:

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

The article discusses the HTML <datalist> element, which enhances forms by providing autocomplete suggestions, improving user experience and reducing errors.Character count: 159

The article discusses the HTML <progress> element, its purpose, styling, and differences from the <meter> element. The main focus is on using <progress> for task completion and <meter> for stati

The article discusses using HTML5 form validation attributes like required, pattern, min, max, and length limits to validate user input directly in the browser.

The article discusses the <iframe> tag's purpose in embedding external content into webpages, its common uses, security risks, and alternatives like object tags and APIs.

The article discusses the HTML <meter> element, used for displaying scalar or fractional values within a range, and its common applications in web development. It differentiates <meter> from <progress> and ex

Article discusses best practices for ensuring HTML5 cross-browser compatibility, focusing on feature detection, progressive enhancement, and testing methods.

The article discusses the viewport meta tag, essential for responsive web design on mobile devices. It explains how proper use ensures optimal content scaling and user interaction, while misuse can lead to design and accessibility issues.

This article explains the HTML5 <time> element for semantic date/time representation. It emphasizes the importance of the datetime attribute for machine readability (ISO 8601 format) alongside human-readable text, boosting accessibilit
