Home > Article > Web Front-end > How to implement an excel-like table control in the web
How to implement an excel-like table control in the web
- WBOYOriginal
- 2016-09-02 08:42:582936browse
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> }
运行代码，如下：
Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：Ellipses appear in the extended part of the textNext article：Ellipses appear in the extended part of the text