Excel のようなテーブル コントロールを 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> }

运行代码，如下：