In this article, I'll graphically use the Spreadjs in the Wijmo (JavaScript Library) to step through the spreadsheet product spreadsheet on a Web page (for example, to build Office 365 Excel products, Google's online spreadsheet).
Blog Introduction:
Wijmo control, the Html\javascript library provided by Grape City, is currently the latest version of the V2 (released 2014.7.22), supporting jquery UI and jquery, jquery Mobile, Angular.js, Bootstrap.js, Knockout.js and so on. At the same time, WIJMO provides a number of widgets (UI controls) that are very handy for building a Web application or Web site.
Among them, Wijmo's Spreadjs provides the following features:
- Display of spreadsheets by HTML5 for easy data entry
- Easy to set color, border, font
- You can use similar Excel formulas
- Compatible with JSON, CSV data input, output
- Data analysis available: conditional format filtering, etc.
- Cell graphic Display
- Date, IME format
- Custom input cell type
- Touch gesture Support
- Application of graphics and images
This article will cover 1~4 points.
Who is suitable for reading this article?
- If you want to learn about building technologies for Web sites and Web applications
- If you want to build a complex spreadsheet type of website in a short period of time
- If you want to implement a Web site in a purely front-end, HTML5 way
The necessary environment
Develop with the following environment
- Wijmo V2 spread JS 3.20142.11
- JQuery UI 1.11.0
- JQuery 1.11.1
- JQuery Mobile 1.4.0
- Windows 8.1 64-bit version
- IE browser 11, Chrome 36.0.1985.125 m
Spreadjs version and Wijmo license
Spreadjs's mandate is included in the WIJMO enterprise
|
Wijmo Professional |
Wijmo Corporate |
Wijmo Corporate Optional Packages |
Basic Widgets |
? |
? |
- |
Spreadjs |
- |
? |
- |
Spreadjs Designer and Excel I/O |
- |
- |
? |
Technical support |
? |
? |
? |
Wijmo's personal authorization is based on the MIT and GPL version 2; For enterprise users, adopting the Wijmo Enterprise Edition is a very cost effective strategy.
The Spreadjs Designer and Excel i\o included in the WIJMO Enterprise Edition are useful for accelerating design spreadsheet, such as a convenient view of JSON-formatted data.
Preparing Spreadjs CDN before use
In order to use the SPREADJS, please list the description in the head tag in HTML. (1) in the description of the language,(2) - (5) is the UI to read the CSS and jquery spreadjs,jquery.
<!--Spreadjs Language Settings (1)- <meta name= "Spreadjs culture" content= "ZH-CN"/> <!--jQuery, Jqurey UI (2)-- <script src= "http://code.jquery.com/jquery-1.11.1.min.js" type= "Text/javascript" ></script > <script src= "http://code.jquery.com/ui/1.11.0/jquery-ui.min.js" type= "Text/javascript" ></ Script> <!--wijmo CSS (3)-- <link href= "Http://cdn.wijmo.com/themes/aristo/jquery-wijmo.css" Rel= "stylesheet" type= "Text/css"/> <!--spreadjs (4)-- <script src= "http://cdn.wijmo.com/ Spreadjs/jquery.wijmo.wijspread.all.3.20142.11.min.js "type=" Text/javascript "></script> <!-- Spreadjs CSS (5)-- <link href= "Http://cdn.wijmo.com/spreadjs/jquery.wijmo.wijspread.3.20142.11.css" Rel= "stylesheet" type= "Text/css"/>
First, let's show the spreadsheet
First, let's try to simply display a spreadsheet, and you can view the empty spreadsheet in HTML by installing the following method on the Wijspread element, as shown in the following code:
<script id= "Scriptinit" type= "Text/javascript" > $ (document). Ready (function () { // Spreadjs Initialization $ ("#wijspread1"). Wijspread (); }) ; </script>
Note: If you do not specify the size of the div element, the spreadsheet will display full screen in the browser, thus limiting the display range of the spreadsheet to the specified size.
Spreadjs the definition of a DIV element
<div id= "Wijspread1" style= "width:480px; height:320px; border:1px Solid Gray "/>
Spreadjs to display an empty spreadsheet on a webpage:
This simple spreadsheet, which now has a magical basic function, can enter numbers or letters, and you can enter a formula in a cell.
You can customize the initial display by setting parameters in the JavaScript object to the Wijspread method. My table 2 lists the main parameters.
Main parameters and default values for Wijspread:
Parameter name |
Parameter description |
Initial value |
Sheetcount |
Sheet label pages |
1 |
Activesheetindex |
Initially Show Table tab page |
0 |
Tabeditable |
Whether the sheet name can be edited |
True |
Allowuserresize |
Whether to change the size of the column |
True |
Allowuserzoom |
is variable focus |
True |
Newtabvisible |
Whether to display the new tab button |
True |
If we can set 2 parameters, to start with 3 tabs, while hiding the new button, the code is as follows:
<script id= "Scriptinit" type= "Text/javascript" > $ (document). Ready (function () { // Spreadjs Initialization $ ("#wijspread1"). Wijspread ({ // initialize to 3 tabs newtabvisible:false // Hide New tab button })) ; </script>
Effect:
Manipulating Spreadjs spreadsheets in JavaScript
As with Visual Basic, you can manipulate Excel's VBA by implementing Excel spreadsheets, where you also use JavaScript for manipulating Spreadjs spreadsheets.
In order to manipulate the spreadsheet, you first need to get the Wijspread object, so that you can get the table of the Spreadjs object, you can get the table object, cells cell object and so on. Then, you can do the actual operation of each object individually.
The following code is an example of the size of the cell, setting the font, and assigning a value to the text cell, the Autofitcolumn/autofitrow method.
//get Spread Object varSpread = $ ("#wijspread1"). Wijspread ("Spread"); //get the currently active tab page varActiveSheet =Spread.getactivesheet (); //get row 2nd 2 column cell varCell = Activesheet.getcell (1, 1); //assign a value to this cellCell.value ("Wijmo Spreadjs"); //cell Set Font sizeCell.font ("15pt Song Body"); //The current tab is automatically row, column fit sizeActivesheet.autofitcolumn (1); Activesheet.autofitrow (1);
Effect:
By leveraging the Getcells method, instead of the Getcell method, you can manipulate multiple cells in a range at the same time:
// get 2nd row 2 Column ~ 4th row 5 column and set background color var cell = Activesheet.getcells (1, 1, 3, 4) Cell.backcolor ("#00ff00");
At the same time, by generating the boundary of the Lineborder object is set to the cell setting unit, BorderBottom borderright, Borderleft, each method bordertop.
Get the Lineborder property varnew $.wijmo.wijspread.lineborder ("#000000", $. Wijmo.wijspread.LineStyle.thin); // Set Border cell.bordertop (lineborder); Cell.borderbottom (Lineborder); Cell.borderleft (Lineborder); Cell.borderright (Lineborder);
You can also set the type of the border by modifying the LineStyle second parameter-using Lineborder. The following table lists the primary border settings
Border name |
Note |
Thin |
Thin Solid Line |
Medium |
Thick Solid line |
Thick |
Thick Solid line |
Dashed |
Dashed |
Dotted |
Point line |
Dashdot |
Dot Dotted Line |
In Excel, there are requirements for merging cells; with Spreadjs, you can also use the Addspan method to merge multiple cells on a worksheet, as in Excel:
// column activesheet.addspan (0, 0, 2, 4, $.wijmo.wijspread.sheetarea.colheader); // row to close Activesheet.addspan (0, 0, 2, 4, $.wijmo.wijspread.sheetarea.rowheader); // cell combination Activesheet.addspan (0, 0, 2, 4, $.wijmo.wijspread.sheetarea.viewport);
You can use Excel-like formulas and functions
You can do calculations in Excel by setting up cells in formulas, such as sums, averages, and Spreadjs can also help you do similar work: Formula Functions
In the example below, the average (average function) and total (Sum function) are shown. You can display the results by setting the formula in the cell.
Activesheet.getcell (0, 0). Value (' Exam results '); //results of each sectionActivesheet.getcell (2, 0). Value (' Language '); Activesheet.getcell (2, 1). Value (80); Activesheet.getcell (3, 0). Value (' math ')); Activesheet.getcell (3, 1). Value (70); Activesheet.getcell (4, 0). Value (' English '); Activesheet.getcell (4, 1). Value (90); Activesheet.getcell (5, 0). Value (' Politics '); Activesheet.getcell (5, 1). Value (45); //Total and average scoreActivesheet.getcell (6, 0). Value ("Total score");//TotalActivesheet.getcell (6, 1). Formula ("SUM (B3:B6)"); Activesheet.getcell (7, 0). Value (' average score ');//Average scoreActivesheet.getcell (7, 1). Formula ("AVERAGE (B3:B6)");
Input and output data using CSV or JSON
You can output the data you entered in the Spreadjs, or enter data from the external to the reverse. Format with the corresponding CSV and JSON.
I will input and output the JSON Tojson/fromjson method in the Spreadjs object, and also use the Json.stringify\parse method.
//Data Output functionExportjson () {//get Spread Object varSpread = $ ("#wijspread1"). Wijspread ("Spread"); varJsonstr =json.stringify (Spread.tojson ()); $("#jsonCode"). HTML (JSONSTR); } ////Data entry functionImportjson () {//get Spread Object varSpread = $ ("#wijspread1"). Wijspread ("Spread"); varJsonobj = Json.parse ($ ("#jsonCode"). HTML ()); Spread.fromjson (Jsonobj); }
Effect:
On the other hand, in the case of CSV, I will use the Getcsv Table object, the method Setcsv. Excel as that is, using CSV will hold the unit in case of input and output. You can also specify the starting position and range, and the cell delimiter.
//Export a CSV string functionexportcsv () {//get Spread Object varSpread = $ ("#wijspread1"). Wijspread ("Spread"); varCsvstring =Spread.getactivesheet (). Getcsv (0,//Start line0,//Start Column10,//Number of rows5,//Number of columns"\ n",//Line split character","//Column Split character ); $("#jsonCode"). HTML (csvstring); } //Import a CSV string functionImportCSV () {//get Spread Object varSpread = $ ("#wijspread1"). Wijspread ("Spread"); varcsvstring = $ ("#jsonCode"). html (); Spread.getactivesheet (). Setcsv (0,//Start line0,//Start ColumnCsvstring,//CSV string"\ n",//Line split character",",//Column Split character$.wijmo.wijspread.textfileopenflags.none//Import Options ); }
It should be noted, however, that the values specified in table 4 can be used to setcsv the import tag of the method. Default is None.
Textfileopenflags Options |
The contents of the option |
Importformula |
Import formulas |
Ncludecolumnheader |
Include column headings |
Includerowheader |
Have header row |
None |
None (Default) |
Unformatted |
Formatted data |
Summarize
In this article, a spreadsheet is implemented using the SPREADJS JavaScript components provided by the WIJMO Library through code examples and illustrations. SPREADJS provides data entry and calculation, first-level data display, such as import and export to Csv/json format, which can be used for browsing on web browsers. This approach is useful for designing Excel-like spreadsheets on the Web front-end.
In Wijmo, the optional package Wijmo enterprise can be used as an option to take advantage of more convenient spreadjs. By using the spread designer contained in this option, you can create a standalone application for data spreadjs. The IO service in Excel allows you to export Excel files with data that you create or import in an Excel file in Spreadjs.
---------------------------------------------------------------------------
Wijmo Chinese Website: http://wijmo.gcpowertools.com.cn/
Wijmo working with Documents: http://wijmo.com/docs/wijmo/#WijmoUserGuide. html
Wijmo Online demo:http://wijmo.com/demos/