Heim > Artikel > Web-Frontend > 如何在web中实现类似excel的表格控件
如何在web中实现类似excel的表格控件
- WBOYOriginal
- 2016-09-02 08:42:582897Durchsuche
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 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 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 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 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 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 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 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 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 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 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 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 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 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 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></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
在HTML中放置一个Div容器来存放handsontable控件：
<span style="color: #008080;">1</span> <span style="color: #0000ff;"><span style="color: #800000;">body</span><span style="color: #0000ff;">></span> <span style="color: #008080;">2</span> <span style="color: #0000ff;"><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></span></span>
在javascript代码中，首先获取div容器，然后创建表格控件：
<span style="color: #008080;"> 1</span> <script type="text/javascript">
<span style="color: #008080;"> 2 $(document).ready(<span style="color: #0000ff;">function<span style="color: #000000;"> () {
<span style="color: #008080;"> 3
<span style="color: #008080;"> 4 <span style="color: #0000ff;">var data1 =<span style="color: #000000;"> [
<span style="color: #008080;"> 5 ['=$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 style="color: #008080;"> 6 [2009, 0, 2941, 4303, 354, 5814<span style="color: #000000;">],
<span style="color: #008080;"> 7 [2010, 5, 2905, 2867, '=SUM(A4,2,3)', '=$B1'<span style="color: #000000;">],
<span style="color: #008080;"> 8 [2011, 4, 2517, 4822, 552, 6127<span style="color: #000000;">],
<span style="color: #008080;"> 9 [2012, '=SUM(A2:A5)', '=SUM(B5,E3)', '=A2/B2', 12, 4151<span style="color: #000000;">]
<span style="color: #008080;">10 <span style="color: #000000;"> ];
<span style="color: #008080;">11
<span style="color: #008080;">12
<span style="color: #008080;">13 <span style="color: #0000ff;">function<span style="color: #000000;"> negativeValueRenderer(instance, td, row, col, prop, value, cellProperties) {
<span style="color: #008080;">14 Handsontable.renderers.TextRenderer.apply(<span style="color: #0000ff;">this<span style="color: #000000;">, arguments);
<span style="color: #008080;">15
<span style="color: #008080;">16 <span style="color: #0000ff;">var escaped =<span style="color: #000000;"> Handsontable.helper.stringify(value),
<span style="color: #008080;">17 <span style="color: #000000;"> newvalue;
<span style="color: #008080;">18
<span style="color: #008080;">19 <span style="color: #0000ff;">if (escaped.indexOf('return') === 0<span style="color: #000000;">) {
<span style="color: #008080;">20 <span style="color: #008000;">//<span style="color: #008000;">计算列为只读
<span style="color: #008080;">21 <span style="color: #008000;">//<span style="color: #008000;">cellProperties.readOnly = true;
<span style="color: #008080;">22 td.style.background = '#EEE'<span style="color: #000000;">;
<span style="color: #008080;">23 newvalue = document.createElement('span'<span style="color: #000000;">);
<span style="color: #008080;">24 <span style="color: #000000;"> $.ajax({
<span style="color: #008080;">25 <span style="color: #008000;">//<span style="color: #008000;">提交数据的类型 POST GET
<span style="color: #008080;">26 type: "POST"<span style="color: #000000;">,
<span style="color: #008080;">27 <span style="color: #008000;">//<span style="color: #008000;">提交的网址
<span style="color: #008080;">28 url: "/services/CSEngine.ashx"<span style="color: #000000;">,
<span style="color: #008080;">29 <span style="color: #008000;">//<span style="color: #008000;">提交的数据
<span style="color: #008080;">30 <span style="color: #000000;"> data: { code: value, code2: escaped },
<span style="color: #008080;">31 <span style="color: #008000;">//<span style="color: #008000;">返回数据的格式
<span style="color: #008080;">32 datatype: "html",<span style="color: #008000;">//<span style="color: #008000;">"xml", "html", "script", "json", "jsonp", "text".
<span style="color: #008080;">33 <span style="color: #008000;">//<span style="color: #008000;">在请求之前调用的函数
<span style="color: #008080;">34 <span style="color: #008000;">//<span style="color: #008000;">beforeSend: function () { $("#msg").html("logining"); },
<span style="color: #008080;">35 <span style="color: #008000;">//<span style="color: #008000;">成功返回之后调用的函数
<span style="color: #008080;">36 success: <span style="color: #0000ff;">function<span style="color: #000000;"> (data) {
<span style="color: #008080;">37 <span style="color: #008000;">//<span style="color: #008000;"> $("#msg").html(decodeURI(data));
<span style="color: #008080;">38 newvalue.innerHTML =<span style="color: #000000;"> decodeURI(data);
<span style="color: #008080;">39 <span style="color: #000000;"> },
<span style="color: #008080;">40 <span style="color: #008000;">//<span style="color: #008000;">调用执行后调用的函数
<span style="color: #008080;">41 complete: <span style="color: #0000ff;">function<span style="color: #000000;"> (XMLHttpRequest, textStatus) {
<span style="color: #008080;">42 <span style="color: #008000;">//<span style="color: #008000;">alert(XMLHttpRequest.responseText);
<span style="color: #008080;">43 <span style="color: #008000;">//<span style="color: #008000;"> alert(textStatus);
<span style="color: #008080;">44 <span style="color: #008000;">//<span style="color: #008000;">HideLoading();
<span style="color: #008080;">45 <span style="color: #000000;"> },
<span style="color: #008080;">46 <span style="color: #008000;">//<span style="color: #008000;">调用出错执行的函数
<span style="color: #008080;">47 error: <span style="color: #0000ff;">function<span style="color: #000000;"> () {
<span style="color: #008080;">48 <span style="color: #008000;">//<span style="color: #008000;">请求出错处理
<span style="color: #008080;">49 <span style="color: #008000;">//<span style="color: #008000;"> alert('error')
<span style="color: #008080;">50 <span style="color: #000000;"> }
<span style="color: #008080;">51 <span style="color: #000000;"> });
<span style="color: #008080;">52
<span style="color: #008080;">53
<span style="color: #008080;">54 Handsontable.Dom.addEvent(newvalue, 'mousedown', <span style="color: #0000ff;">function<span style="color: #000000;"> (e) {
<span style="color: #008080;">55 e.preventDefault(); <span style="color: #008000;">//<span style="color: #008000;"> prevent selection quirk
<span style="color: #008080;">56 <span style="color: #000000;"> });
<span style="color: #008080;">57
<span style="color: #008080;">58 <span style="color: #000000;"> Handsontable.Dom.empty(td);
<span style="color: #008080;">59 <span style="color: #000000;"> td.appendChild(newvalue);
<span style="color: #008080;">60 <span style="color: #000000;"> }
<span style="color: #008080;">61 <span style="color: #008000;">//<span style="color: #008000;"> if row contains negative number
<span style="color: #008080;">62 <span style="color: #0000ff;">if (parseInt(value, 10) < 0<span style="color: #000000;">) {
<span style="color: #008080;">63 <span style="color: #008000;">//<span style="color: #008000;"> add class "negative"
<span style="color: #008080;">64 td.className = 'negative'<span style="color: #000000;">;
<span style="color: #008080;">65 <span style="color: #000000;"> }
<span style="color: #008080;">66
<span style="color: #008080;">67
<span style="color: #008080;">68 <span style="color: #000000;"> }
<span style="color: #008080;">69
<span style="color: #008080;">70
<span style="color: #008080;">71
<span style="color: #008080;">72 <span style="color: #008000;">//<span style="color: #008000;">类似excel进行拖放，公式会变
<span style="color: #008080;">73 <span style="color: #0000ff;">var container1 = $('#handsontable-code'<span style="color: #000000;">);
<span style="color: #008080;">74 Handsontable.renderers.registerRenderer('negativeValueRenderer'<span style="color: #000000;">, negativeValueRenderer);
<span style="color: #008080;">75 <span style="color: #000000;"> container1.handsontable({
<span style="color: #008080;">76 <span style="color: #000000;"> data: data1,
<span style="color: #008080;">77 minSpareRows: 1<span style="color: #000000;">,
<span style="color: #008080;">78 colHeaders: <span style="color: #0000ff;">true<span style="color: #000000;">,
<span style="color: #008080;">79 rowHeaders: <span style="color: #0000ff;">true<span style="color: #000000;">,
<span style="color: #008080;">80 contextMenu: <span style="color: #0000ff;">true<span style="color: #000000;">,
<span style="color: #008080;">81 manualColumnResize: <span style="color: #0000ff;">true<span style="color: #000000;">,
<span style="color: #008080;">82 formulas: <span style="color: #0000ff;">true<span style="color: #000000;">,
<span style="color: #008080;">83 cells: <span style="color: #0000ff;">function<span style="color: #000000;"> (row, col, prop) {
<span style="color: #008080;">84 <span style="color: #0000ff;">var cellProperties =<span style="color: #000000;"> {};
<span style="color: #008080;">85 <span style="color: #0000ff;">var escaped = Handsontable.helper.stringify(<span style="color: #0000ff;">this<span style="color: #000000;">.instance.getData()[row][col]);
<span style="color: #008080;">86 <span style="color: #0000ff;">if (escaped.indexOf('return')===0<span style="color: #000000;">) {
<span style="color: #008080;">87 cellProperties.renderer = "negativeValueRenderer"<span style="color: #000000;">;
<span style="color: #008080;">88 <span style="color: #000000;"> }
<span style="color: #008080;">89
<span style="color: #008080;">90
<span style="color: #008080;">91 <span style="color: #0000ff;">return<span style="color: #000000;"> cellProperties;
<span style="color: #008080;">92 <span style="color: #000000;"> }
<span style="color: #008080;">93 <span style="color: #000000;"> });
<span style="color: #008080;">94
<span style="color: #008080;">95 <span style="color: #000000;">});
<span style="color: #008080;">96
<span style="color: #008080;">97 </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> }
运行代码，如下：
Stellungnahme：
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Vorheriger Artikel：文本超出部分出现省略号Nächster Artikel：列表的响应式排版