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

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

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

HTML is suitable for beginners because it is simple and easy to learn and can quickly see results. 1) The learning curve of HTML is smooth and easy to get started. 2) Just master the basic tags to start creating web pages. 3) High flexibility and can be used in combination with CSS and JavaScript. 4) Rich learning resources and modern tools support the learning process.

HTML defines the web structure, CSS is responsible for style and layout, and JavaScript gives dynamic interaction. The three perform their duties in web development and jointly build a colorful website.

WebdevelopmentreliesonHTML,CSS,andJavaScript:1)HTMLstructurescontent,2)CSSstylesit,and3)JavaScriptaddsinteractivity,formingthebasisofmodernwebexperiences.

GiteePages static website deployment failed: 404 error troubleshooting and resolution when using Gitee...

AnexampleofastartingtaginHTMLis,whichbeginsaparagraph.StartingtagsareessentialinHTMLastheyinitiateelements,definetheirtypes,andarecrucialforstructuringwebpagesandconstructingtheDOM.

To achieve the effect of scattering and enlarging the surrounding images after clicking on the image, many web designs need to achieve an interactive effect: click on a certain image to make the surrounding...

HTML, CSS and JavaScript are the three pillars of web development. 1. HTML defines the web page structure and uses tags such as, etc. 2. CSS controls the web page style, using selectors and attributes such as color, font-size, etc. 3. JavaScript realizes dynamic effects and interaction, through event monitoring and DOM operations.

How to distinguish between closing tabs and closing entire browser using JavaScript on your browser? During the daily use of the browser, users may...
