Execl is very powerful, and many built-in functions or formulas can greatly improve the processing ability of data. So are there similar controls in the web? After some search, I found that handsontable has basic Excel function support formulas and can edit data in real time. In addition, it supports drag copy, Ctrl+C, Ctrl+V, etc. In terms of browser support, it supports the following browsers: IE7+, FF, Chrome, Safari, Opera.
First, the relevant library files are introduced. The formula support is not included in the handsontable.full.js and needs to be introduced separately:
<script src="http://handsontable.github.io/handsontable-ruleJS/lib/jquery/jquery-1.10.2.js"></script><script src="http://handsontable.github.io/handsontable-ruleJS/lib/handsontable/handsontable.full.js"></script><link rel="stylesheet" media="screen" href="http://handsontable.github.io/handsontable-ruleJS/lib/handsontable/handsontable.full.css"><script src="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/lib/lodash/lodash.js"></script><script src="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/lib/underscore.string/underscore.string.js"></script><script src="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/lib/moment/moment.js"></script><script src="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/lib/moment/moment.js"></script><script src="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/lib/numeral/numeral.js"></script><script src="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/lib/numericjs/numeric.js"></script><script src="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/lib/js-md5/md5.js"></script><script src="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/lib/js-md5/md5.js"></script><script src="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/lib/jstat/jstat.js"></script><script src="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/lib/parser.js"></script><script src="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/js/parser.js"></script><script src="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/js/parser.js"></script><script src="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/js/ruleJS.js"></script><script src="http://handsontable.github.io/handsontable-ruleJS/lib/handsontable/handsontable.formula.js"></script>
Put a Div container in HTML to store the handsontable control:
<body> <div id="handsontable-code"></div> </body>
In javascript code, first get the div container and then create the table control:
<script type="text/javascript"> $(document).ready(function () { var data1 = [ ['=$B$2', "Maserati", "Mazda", "return 1+2;", 'return DataAccess.getScalar("select top 1 name from Cloud_Users where cellPhone=15895211486");', "=A$1"], [2009, 0, 2941, 4303, 354, 5814], [2010, 5, 2905, 2867, '=SUM(A4, 2, 3)', '=$B1'], [2011, 4, 2517, 4822, 552, 6127], [2012, '=SUM(A2:A5)', '=SUM(B5,E3)', '=A2/B2', 12, 4151] ]; function negativeValueRenderer(instance, td, row, col, prop, value, cellProperties) { Handsontable.renderers.TextRenderer.apply(this, arguments); var escaped = Handsontable.helper.stringify(value), newvalue; if (escaped.indexOf('return') === 0) { //Calculate column as read-only //cellProperties.readOnly = true; td.style.background = '#EEE'; newvalue = document.createElement('span'); $.ajax({ //Type of submitting data POST GET type: "POST", //Submitted URL url: "/services/CSEngine.ashx", //Submitted data data: { code: value, code2: escaped }, //Return data format datatype: "html", //"xml", "html", "script", "json", "jsonp", "text". //The function called before the request//beforeSend: function () { $("#msg").html("logining"); }, //The function called after successful return success: function (data) { // $("#msg").html(decodeURI(data)); newvalue.innerHTML = decodeURI(data); }, //The function called after execution complete: function (XMLHttpRequest, textStatus) { //alert(XMLHttpRequest.responseText); // alert(textStatus); //HideLoading(); }, //Call the function that executes error: function () { //Request error handling// alert('error') } }); Handsontable.Dom.addEvent(newvalue, 'mousedown', function (e) { e.preventDefault(); // prevent selection quirk }); Handsontable.Dom.empty(td); td.appendChild(newvalue); } // if row contains negative number if (parseInt(value, 10) < 0) { // add class "negative" td.className = 'negative'; } } //Drag and drop similar to excel, the formula will change var container1 = $('#handsontable-code'); Handsontable.renderers.registerRenderer('negativeValueRenderer', negativeValueRenderer); container1.handsontable({ data: data1, minSpareRows: 1, colHeaders: true, rowHeaders: true, contextMenu: true, manualColumnResize: true, formulas: true, cells: function (row, col, prop) { var cellProperties = {}; var escaped = Handsontable.helper.stringify(this.instance.getData()[row][col]); if (escaped.indexOf('return')===0) { cellProperties.renderer = "negativeValueRenderer"; } return cellProperties; } }); }); </script>The formula of =SUM(B5,E3) is provided by RuleJs. Return 1+2 is a C# code script implemented by yourself. You need to click to parse:
public class CSEngine : IHttpHandler { private static int count = 0; public void ProcessRequest (HttpContext context) { context.Response.ContentType = "text/plain"; try { count++; string ret = ""; string code = context.Request["code"].ToString(); if (string.IsNullOrEmpty(code)) { ret = "parameter error"; } else { ScriptOptions options = ScriptOptions.Default .AddReferences( Assembly.GetAsembly(typeof(DBServices.DataAccess)) ) //.AddImports("System.Data") //.AddImports("System.Data.SqlClient") .AddImports("DBServices"); var state = CSharpScript.RunAsync(code, options).Result.ReturnValue; ret = state.ToString(); state = null; options = null; } Console.WriteLine(count); context.Response.Write(ret); } catch(Exception ex) { //error Console.WriteLine(count); } } public bool IsReusable { get { return false; } }}Run the code as follows:
The above is all the content of this article. I hope it will be helpful to everyone's learning and I hope everyone will support Wulin.com more.