"Diagram" Web Frontend implements Excel-like spreadsheets

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/