SpreadJS 2014v1 has Landed

I am happy to announce that Spread 2014v1 has landed! SpreadJS 2014 v1 is the first release of 2014, with many new features. Full touch support has been added in this release. We have also added Picture support, Structured References, and more. Performance has also been improved, initializing, data loading, and UI operations are much faster.
Features in this release includes:

  • Touch Support
  • Touch Support provides Excel 2013 like UI touch behavior and appearance. This helps the end-user easily operate the spread control by using touch in a web application which runs in a touch-able device.

  • Performance Enhancement
  • We improved performance of UI operations like scrolling, selection, editing, initializing and data binding, no matter how large the data source is, SpreadJS spends same time for initializing and data loading, try the sample in action here.

  • Picture Support
  • Now each sheet can have its own picture layer that can contain any image.

  • Structure Reference
  • Make it much easier and more intuitive to work with table data when you are using formulas that reference a table, either portions of a table or the entire table.

  • PDF Export
  • Now the ExcelIO service support PDF export.

  • Named Style
  • Now the “style” has a name property, you can use the name to apply the style to cells (or rows or columns), that provides an easy way to change a style for bunch of cells by changing a named style setting.

  • Text Decoration
  • Now you can use underline, strikethrough, and overline to decorate text in cells.

  • More…

For more details in this release, please visit the what’s new page. And you can also visit SpreadJS product page for more information of the product.

Integrate Spreadsheet into CaplinTrader by Using SpreadJS

CaplinTrader by Caplin, is a pure HTML5 technology product that provides sophisticated ready-to-run trading front ends for a range of asset classes. CaplinTrader comes complete with tools that make it easy to style, brand, modify and extend these.
TeamFirefly, Caplin Lab’s Hackday winning team, created a very cool demo – ScratchPad. ScratchPad integrates spreadsheets into the CaplinTrader application by using Wijmo SpreadJS, from ComponentOne, a division of GrapeCity. “Using the Wijmo SpreadJS, we spiked out a blade that would integrate a spreadsheet into Caplin Trader” they said in their community. The demo not only provides an Excel syntax compatible formula, but also shows real time data by using custom function and drag-drop data from other parts of the application, TeamFirefly completed this really cool demo only in 24 hours, which is awesome! Here is a video they made for the demonstration.

Video

ScratchPad – Hackday IV Winner from Caplin Systems on Vimeo.

About Caplin Lab

Caplin Lab is a lab where developer teams from Caplin share some of the cool things that they create on the job.

Add Context Menu for SpreadJS

Do you want to add a context menu in SpreadJS? It is very simple. No mouse down event, no left or right mouse button checking, just popup the menu in contextmenu event of the SpreadJS place-holder element.

Here is a sample that illustrates how you can show a Wijmo Menu widget as a context menu in SpreadJS.
The first step is to create an HTML page, then add both the JavaScript library and CSS references in the “head” tag. Then add a SpreadJS place holder element in the “body” tag, please refer here for more information on this step. Next, define the Wijmo Menu in the “body” tag as well.

Then we need to initialize the SpreadJS and Wijmo Menu.

$("#ss").wijspread();
$("#contextMenu").wijmenu({
    trigger: "#ss",
    triggerEvent: "rtclick",
    orientation: "vertical",
    select: function (e, data) {
        // process menu item click here
    }
});

You will need to adjust the menu position in the contextmenu event, and close the menu in the mousedown event of the canvas element.

$("canvas").mousedown(function (e) {
    // hide context menu when the mouse down on SpreadJS
    $("#contextMenu").wijmenu("hideAllMenus", e)
});
$("#ss").bind('contextmenu', function (e) {
    // move the context menu to the position of the mouse point
    $("#contextMenu").wijmenu("option", "position", { of: e });
	return false;
});

OK, done! When you perform a right mouse click in SpreadJS, the context menu will popup.

Here is the sample so you may try it in action.

Adding Cell ToolTips in SpreadJS

Adding Tooltips to Spreadsheets is a common usage where you want to show additional information about cells. SpreadJS allows you to display the tooltip only for the HyperlinkCellType. However, you may want to show cell tooltips for any type of cell, say for instance to display the current cell location in a tooltip.

SpreadJS provides simple solution for this. Here in this blog lets discuss two different yet simple approaches that will help you to create and display tooltips for all the cells in SpreadJS.

Using a CustomCellType

The first approach is to use a CustomCelltype. For the demonstration purpose, lets name this customcelltype as TipCellType which will display tooltips for the cells.

$(function ()
{
  function TipCellType()
  {
  }
  TipCellType.prototype = new $.wijmo.wijspread.TextCellType();
  TipCellType.prototype.getHitInfo = function (x, y, row, col, cellStyle, cellRect, sheetArea) {
     return { x: x, y: y, row: row, col: col, cellStyle: cellStyle, cellRect: cellRect, sheetArea: sheetArea };
  }
  TipCellType.prototype.processMouseEnter = function (hitinfo){
    if (!this._toolTipElement) {
        var div = document.createElement("div");
        $(div).css("position", "absolute")
          .css("border", "1px #C0C0C0 solid")
          .css("box-shadow", "1px 2px 5px rgba(0,0,0,0.4)")
          .css("font", "9pt Arial")
          .css("background", "white")
          .css("padding", 5);
          this._toolTipElement = div;
        }
     $(this._toolTipElement).text("Cell [R:" + hitinfo.row + "] : [C:" + hitinfo.col + "]")
        .css("top", hitinfo.y + 15)
        .css("left", hitinfo.x + 15);
         $(this._toolTipElement).hide();
        document.body.insertBefore(this._toolTipElement, null);
        $(this._toolTipElement).show("fast");
 };
 TipCellType.prototype.processMouseLeave = function (hitinfo) {
    if (this._toolTipElement) {
        document.body.removeChild(this._toolTipElement);
        this._toolTipElement = null;
     }
 };

 $("#ss").wijspread();
 var spread = $("#ss").wijspread("spread");
 var sheet = spread.getActiveSheet();
 spread.useWijmoTheme = true;
 spread.repaint();
 sheet.getDefaultStyle().cellType = new TipCellType();

 var dataValidator = new $.wijmo.wijspread.DefaultDataValidator();
 dataValidator.type = $.wijmo.wijspread.CriteriaType.AnyValue;
 dataValidator.showInputMessage = true;
 dataValidator.inputTitle = "test";
 dataValidator.inputMessage = "Here is a tip."
 sheet.getCell(2, 2).value("click here").dataValidator(dataValidator);
});

Monitor the DOM Mouse events

The second approach is useful where you would want to show dynamic tooltips on mouse hover. This can be done simply by using DOM Mouse events to display tooltips. Here is the code:

$(function ()
{
  $("#ss").wijspread();
  var spread = $("#ss").wijspread("spread");
  var sheet = spread.getActiveSheet();
  $("#ss").mouseenter(function () {
     if (!_toolTipElement) {
         _toolTipElement = createTipElement();
         document.body.insertBefore(_toolTipElement, null);
         $(_toolTipElement).hide();
      }
  });
 $("#ss").mouseleave(function () {
      console.log("leave");
      document.body.removeChild(_toolTipElement);
       _toolTipElement = null;
});

$("#ss").mousemove(function (event) {
   var leftOffset = event.pageX;
   var topOffset = event.pageY;
   var hitInfo = sheet.hitTest(leftOffset - $("#ss").offset().left, topOffset - $("#ss").offset().top);
   if (hitInfo) {
      if (hitInfo.hitTestType === $.wijmo.wijspread.SheetArea.viewport
           && !hitInfo.dragInfo
           && hitInfo.row != undefined
           && hitInfo.col != undefined) {
                var newCell = sheet.getCell(hitInfo.row, hitInfo.col);
                if (!oldCell) {
                  oldCell = newCell;
                }
                if (oldCell.row != newCell.row || oldCell.col != newCell.col) {
                   $(_toolTipElement).hide();
                   oldCell = newCell;
                   var cellRect = sheet.getCellRect(oldCell.row, oldCell.col);
                   $(_toolTipElement).text("Cell [R:" + oldCell.row + "] : [C:" + oldCell.col + "]")
                         .css("top", hitInfo.y + 15)
                         .css("left", hitInfo.x + 15).show("fast");
                  }
           return;
       }
    }
    oldCell = null;
    $(_toolTipElement).hide();
 });
});

Please Note: This approach does not work for IE10

Conclusion

With both the approaches, you can generate a Spreadsheet as the image shows here

Download the source code of the sample for complete implementation.

Add Excel Comments in SpreadJS

SpreadJS has a very flexible API which provides for programmable customization. While you can change individual cells and their settings through methods, you can also make much deeper customization like owner-draw and change behavior. SpreadJS provides CellType for each cell which permits them to have different UI and behaviors. CellType can be used for different types of data like texture, Boolean, and hyperlink… Plus, you can also define your own CellType through the CustomCellType class.

Here is a sample that illustrates how you can customize a simple Excel-like comments feature. In Excel, if a cell has comments, there is a red triangle display at the right-top corner of the cell and when you mouse hover on the cell the comments will popup. We can change the appearance of a cell by inheriting from TextCellType class, and customize the behavior of the mouse to instead popup a comments panel which is simulated with a div element.

The first step is to create an HTML page, then add JavaScript library and CSS references in the head tag, and add a SpreadJS place holder element in the body tag, please refer to here for more information.
Then, define the CommentsCellType class which is inherited from TextCellType class.

function CommentsCellType(text) {
	this.text = text;
}
CommentsCellType.prototype = new $.wijmo.wijspread.TextCellType();

Here we need to override some of the base class functions, Let’s start with the paint function and draw a red triangle at the right-top corner.

CommentsCellType.prototype.paint = function(ctx, value, x, y, w, h, style, options) {
	$.wijmo.wijspread.TextCellType.prototype.paint(ctx,value,x,y,w,h,style,options);
	ctx.fillStyle = "red";
	ctx.beginPath();
	ctx.moveTo(x+w-7,y);
	ctx.lineTo(x+w,y);
	ctx.lineTo(x+w,y+7);
	ctx.fill();
};

You will need to override the getHitInfo function that allows the cell type can get mouse information like enter, leave, hover, click etc.

CommentsCellType.prototype.getHitInfo = function (x, y, row, col, cellStyle, cellRect, sheetArea) {
	return {x: x, y: y, row: row, col: col, cellStyle: cellStyle, cellRect: cellRect, sheetArea: sheetArea};
}

Next we’ll customize the mouse behavior, so that when the mouse enters the cell, a div element is created and prompts a popup.

    CommentsCellType.prototype.processMouseEnter = function (hitinfo) {
        if (!this._commentsElement) {
            var div = document.createElement("div");
            $(div).css("position", "absolute")
                .css("border", "1px black solid")
                .css("box-shadow", "1px 2px 5px rgba(0,0,0,0.4)")
                .css("font", "9pt Arial")
                .css("background", "#FFFFEE")
                .css("padding", 5);

            this._commentsElement = div;
        }
        $(this._commentsElement).html(this.text)
            .css("top", hitinfo.y + 15)
            .css("left", hitinfo.x + 15);
        $(this._commentsElement).hide();
        document.body.insertBefore(this._commentsElement, null);
        $(this._commentsElement).show("fast");
    };

And when the mouse leaves the cell, we need to close the comments div element.

CommentsCellType.prototype.processMouseLeave = function (hitinfo) {
    if (this._commentsElement) {
        document.body.removeChild(this._commentsElement);
        this._commentsElement = null;
    }
};

Now that the CommentsCellType is defined, we can initialize SpreadJS and set this cell type instance to a cell.

$("#ss").wijspread();
var spread = $("#ss").wijspread("spread");
var sheet = spread.getActiveSheet();
spread.useWijmoTheme = true;
spread.repaint();
sheet.getCell(1, 1).value("comments").cellType(new CommentsCellType("Eric:this cell has comments!"));

OK, done! The cell [1,1] now has comments, a red triangle displayed in the right-top corner of the cell and when the mouse hovers over the cell, comments popup.

Here is the sample so you may try it in action, and get the complete sample source code.

Importing & Exporting Excel Files with SpreadJS

Excel file import and export within SpreadJS became available in the 2013 v3 product release. You can use the Excel Import and Export service to import and export Excel files into the SpreadJS widget. While the service resides on your application server, SpreadJS may be used to upload an Excel file and import a file, all while a preserving its formatting. Interact with the data in SpreadJS from within your application, and then export your spreadsheets to an Excel file for portable distribution to your users.

SpreadJS supports the current versions of Excel as well as multiple Excel file formats, including Excel (2007 – current XLSX), Excel 97 (XLS), comma-delimited (CSV), and text (TXT).

To use this service, we provide a setup which will help you to install the service, you only need to click one button, the setup will deploy the service to the local IIS service automatically. Here I’d like to introduce how to deploy and configure the service and show you how to use the service in an application.

First of all, there is ExcelIO.msi file in the SpreadJS package; you will use this file to install the ExcelIO service onto the server. Note: please make sure ASP.NET 4 and IIS 7 are installed before your run the setup. For more details on platform requirements please read help doc here.

The next step is to click the install button, and the setup will create the web service under the default site in IIS automatically.

After installation, a license dialog box will open to guide you through the process of entering a product key license to activate the service. If you don’t have a product key yet, you can skip this dialog box, and continue with a 30 day trial for product evaluation purposes.

Once the steps have been completed, the service will be ready to use. The default address is http://localhost/excelio/, and you can find it in the IIS manage console and configure it manually if it is necessary.

And then you can start to use the ExcelIO service for Excel file import/export for SpreadJS. Generally, you can create an html page and add SpreadJS and an import/export button on the page. Next, you may use Ajax to upload the Excel file to the service, and present the results in the JSON data format within SpreadJS. After you have had some UI operation and data entry in SpreadJS, you may post the JSON data back to the server, and the ExcelIO service will generate an Excel file and download to the client. We also provide some options for consuming ExcelIO service to determine how or what data will be import/export Excel file.

Here is an online sample of ExcelIO service, so you can try it in action. For more information about SpreadJS, please visit our web site.

SpreadJS 2013v3 has Landed

I am happy to announce that Spread 2013v3 has landed! This is another monumental release for us. This release includes some major new features like Excel import/export, a WYSIWYG standalone Designer and Table support as well as mainstream javascript libs support like AngularJS, BreezeJS, KnockoutJS, and more new features…

Designer


A WYSIWYG standalone Designer which made by SpreadJS, allow you to create your spreadsheet layout and formatting using the rich, familiar design environment of Spread Designer. Easily create forms, and add calculated cells to your spreadsheet templates without writing any code. Load Excel documents, set conditional formatting, and add sparklines – all within the designer.
[/highlight]

Import/Export


provide ExcelIO service which can use to import Microsoft Excel documents and preserve complete formatting. Interact with the data in Spread from within your application, then export your spreadsheets to Microsoft Excel for portable distribution to your users. Spread supports the current versions of Excel as well as multiple Excel file formats, including Excel (2007 – current XLSX), Excel 97 (XLS), comma-delimited (CSV), and text (TXT). In addition, the ExcelIO service also can be used to export all content in SpreadJS html format file.

Support JS libs

we support popup JS libs like AngularJS, BreezeJS, KnockoutJS in this release, provide full directives which you can use to create SpreadJS and set settings through html tag and attribute, and easily binding, manipulate, and update bound data source.

For more details in this release, please visit the what’s new page. And you can also visit SpreadJS product page for more information of the product.

Wijmo 2013v2 has Landed

I am happy to announce that Wijmo Enterprise 2013 v2 has landed. This release includes a massive list of bug fixes and some great new features. The biggest news in this release is our official support for AngularJS. We have also added some powerful new features to SpreadJS.

AngularJS Support


We heard your feedback during our AngularJS Beta and have made some changes. We now support binding expressions for two-way, one-way and include filter support. Also, our widgets now have a “-” in them after wij. We also added a dash convention to our option names. A dash indicates the next letter should be capitalized. So allowPaging is now allow-paging.

Our support now exposes all widgets and all options of our widgets as AngularJS directives. To use them, simply declare an HTML element that matches the name of the widget (including the dash after wij) and then set any options you want as attributes. Note, you can specify complex options as child elements. And if the widget needs specific markup (like wijmenu) you can simply add it inside the widget element.

SpreadJS

This is the second major release of our SpreadJS component. In this release we have added some powerful new features:

  • New conditional formatting (Icon Sets, Data Bar)
  • Data validation UI
  • Wijmo.data binding support
  • Cell Types, (ComboBox, CheckBox, Button, Hyperlink)
  • Cell level binding
  • Support latest version of jQuery, jQueryUI
  • New documentation

Learn more about what’s new in SpreadJS

New Docs

One of the most requested features for wijmo is better documentation. So we have spent a long time completely rewriting it and have just published a brand new version of our docs online. But don’t take my word for it, go check out our new documentation.

Go Get It!

Download Wijmo 2013v2 and explore the new mobile widgets.

How To Validate Data Entry in SpreadJS

Here are some common scenarios that may sound familiar to you:

  • Only numbers 5 through 10 can be entered into this cell
  • This cell can only accept date that is later than the last cell
  • Entered text should be less than 5 characters length
  • Value of this cell should be value of one cell in the first column

Did you know that you can do all of these in SpreadJS?
SpreadJS provides data validation similar to Excel, to control the type of data or the value that users enter into a cell. There are many types of built in -data validators inside SpreadJS that you mayuse to validate data entry in the cell.
First of all you need to create a data validator by static methods of DefaultDataValidator class, and set the data validator by the dataValidator method of the Cell class to the cell which need data validation. DefaultDataValidator class provide 6 type of data validator, easy to create different criteria validator for your variety requirement. They are:

  • Date Validator, uses to validate date or time type value.
  • Number Validator, uses to validate number type value.
  • List Validator, uses to validate the entered value should be in a list.
  • Text Length Validator, uses to validate length of entered text.
  • Formula Validator, uses to validate result which calculated by a formula.
  • Formula List Validator, uses to validate the entered value should be in a list which is result of a formula.

Here is a sample, create a number validator to check entered value should be between 5 and 10.

var dv = $.wijmo.wijspread.DefaultDataValidator.createNumberValidator($.wijmo.wijspread.ComparisonOperator.Between,5,10,true);
dv.errorMessage = "value should be number and between 5 and 10";
sheet.getCell(1,1).dataValidator(dv);

The second, add code in ValidationError event to process result of validation. This event will be fired when a cell is ending the editing if the entered value is invalid. In event arguments, you can get sheet object which the cell belong to, row index and column index of the cell, validator of the cell, and validationResult this is a flag indicate how to process the invalid value, type of this flag is DataValidationResult, it has three options:

  • ForceApply (default): Indicates the invalid value will be accepted, and save in the cell
  • Discard: Indicates the invalid value will be discard, and original value will be restore in the cell
  • Retry: Indicates the editing will not be end until user enter a valid value
spread.bind($.wijmo.wijspread.Events.ValidationError, function (event, data) {
    var dv = data.validator;
    if (dv) {
        alert(dv.errorMessage);
        if(data.row==0 && data.col==1) {
            // finish editing, and restor the original value
	    data.validationResult=$.wijmo.wijspread.DataValidationResult.Discard;
        } else if(data.row==1 && data.col==1) {
            // keep editing until enter a valid value
	    data.validationResult=$.wijmo.wijspread.DataValidationResult.Retry;
        }
    }
});

You can try the sample page, to learn more about getting started with SpreadJS in a web page, please read Quick Start Guide to Using SpreadJS.

For more information about how to use SpreadJS, includes its data model and API, please read our online documentation or try online demos.

How To Align Cell Text in SpreadJS

SpreadJS provides nine alignment options by combination of two directions horizontal and vertical, allow you to show your data at proper position in the cell.
Here is sample code how to show your data in different position in the cell:

sheet.getCells(1,1,1,3).vAlign($.wijmo.wijspread.VerticalAlign.top);
sheet.getCells(2,1,2,3).vAlign($.wijmo.wijspread.VerticalAlign.center);
sheet.getCells(3,1,3,3).vAlign($.wijmo.wijspread.VerticalAlign.bottom);
sheet.getCells(1,1,3,1).hAlign($.wijmo.wijspread.HorizontalAlign.left);
sheet.getCells(1,2,3,2).hAlign($.wijmo.wijspread.HorizontalAlign.center);
sheet.getCells(1,3,3,3).hAlign($.wijmo.wijspread.HorizontalAlign.right);
sheet.getCells(1,1,3,3).value("text");
sheet.getRows(1,3).height(60);

You can try all of the above code in a web page which has initialized SpreadJS, to learn more about getting started with SpreadJS in a web page, please read Quick Start Guide to Using SpreadJS.

For more information about how to use SpreadJS, includes its data model and API, please read our online documentation or try online demos.

Understand Cell Style Composite of SpreadJS

SpreadJS not only provides style for the cell, but also for the row, the column, and the sheet, so that you can quickly customize the appearance of a cell or a range of cells (or rows or columns) or default style for all cells in the sheet.
The actual style of a cell is composited in priority by the style of the cell, the style of the row which the cell is in, the style of the column which the cell is in, and the default style of the sheet. So that you can make the cell show some special effect with few code.
This is a sample shows result of compositing back color, border, fore color of cell/row/column/sheet.

var ns = $.wijmo.wijspread;
var style = sheet.getDefaultStyle();
style.backColor = "lightgray";
style.foreColor = "purple";
style.borderLeft = new ns.LineBorder("red", ns.LineStyle.hair);
style.borderTop = new ns.LineBorder("red", ns.LineStyle.hair);
style.borderRight = new ns.LineBorder("red", ns.LineStyle.hair);
style.borderBottom = new ns.LineBorder("red", ns.LineStyle.hair);

var cell = sheet.getCells(3, 3, 7, 7);
cell.value(10);
cell.formatter("0.0%");
cell.backColor("lightgreen");
cell.borderLeft(new ns.LineBorder("gray", ns.LineStyle.double));
cell.borderTop(new ns.LineBorder("gray", ns.LineStyle.double));
cell.borderRight(new ns.LineBorder("gray", ns.LineStyle.double));
cell.borderBottom(new ns.LineBorder("gray", ns.LineStyle.double));

var row = sheet.getRows(2, 8);
row.backColor("lightblue");
row.borderLeft(new ns.LineBorder("green", ns.LineStyle.dashed));
row.borderRight(new ns.LineBorder("green", ns.LineStyle.dashed));

var col = sheet.getColumns(2, 8);
col.backColor("pink");
col.borderTop(new ns.LineBorder("blue", ns.LineStyle.dashed));
col.borderBottom(new ns.LineBorder("blue", ns.LineStyle.dashed));

You can try all of the above code in a web page which has initialized SpreadJS, to learn more about getting started with SpreadJS in a web page, please read Quick Start Guide to Using SpreadJS.

For more information about how to use SpreadJS, includes its data model and API, please read our online documentation or try online demos.

How to Set Cell Styles in SpreadJS

A Cell Style is a great way to make important data stand out on a sheet, SpreadJS provides styles to customize your text to give it the exact look you want. You can add a variety of styles and colors to your text, as well as borders or gridlines of cells.

Give your text a specific color and font style by using the font method of the Cell object. SpreadJS uses syntax of css font, same as the way to set font for other DOM elements.

sheet.addSpan(1, 1, 1, 5);
sheet.setValue(1, 1, "Sale Data Analysis");
sheet.getCell(1, 1).font("bold 16px arial");
sheet.getCell(1, 1).foreColor("white");
sheet.getCell(1, 1).backColor("Purple");.

Plus, give different color or line style borders to a range of cells by using the setBorder method of the Sheet object. It provides many options to easily determine which part of the cell range to set.

sheet.setBorder(new $.wijmo.wijspread.Range(1, 1, 6, 5), new $.wijmo.wijspread.LineBorder("Black", $.wijmo.wijspread.LineStyle.thin), { outline: true });
sheet.setBorder(new $.wijmo.wijspread.Range(1, 1, 6, 5), new $.wijmo.wijspread.LineBorder("Blue", $.wijmo.wijspread.LineStyle.dotted), { inside: true });
sheet.setBorder(new $.wijmo.wijspread.Range(5, 1, 1, 5), new $.wijmo.wijspread.LineBorder("Black", $.wijmo.wijspread.LineStyle.double), { bottom: true });

And you also can change the color of the gridline, even hide the gridline to make your data stand out on the sheet.

sheet.setGridlineOptions({showVerticalGridline: false, showHorizontalGridline: false});

You can try all of the above code in a web page which has initialized SpreadJS, to learn more about getting started with SpreadJS in a web page, please read Quick Start Guide to Using SpreadJS.

For more information about how to use SpreadJS, includes its data model and API, please read our online documentation or try online demos.

Wijmo 2013 v1 Webcast

Thanks to everyone that showed up for our webcast this week. It had record breaking attendance here at ComponentOne! If you missed it or want to share or watch it again, here is the recording.

Video

Join Sheela Nath and Chris Bannon as they unveil new technology from Team Wijmo. Wijmo 2013 v1 was a major release that added jQuery Mobile support, new widgets and a powerful spreadsheet component. We have been solidifying our framework and creating a platform for HTML5 development. Come take a tour through Wijmo 2013 v1.

Samples

These samples will be integrated into our download soon. But for now you can download them here.

You can also try out AppView and SpreadJS from our online demos. And don’t forget to download wijmo and start building great mobile applications.

Slides

How To Merge Cells in SpreadJS

Merging is when you combine a range of cells to create a new, larger cell. This is a great way to create a label that spans several columns or several rows. You can easily create a cell span in the view port, row header, or column header area. For example, here cells A1, B1, and C1 were merged to create the label “Monthly Sales” to describe the information below. It is easy to do it with code:

sheet.addSpan(0, 0, 1, 3);
sheet.getCell(0, 0).hAlign($.wijmo.wijspread.HorizontalAlign.center)
sheet.setValue(0, 0, "Monthly Sales");
sheet.setValue(1, 0, "Jan");
sheet.setValue(1, 1, "Feb");
sheet.setValue(1, 2, "Mar");

And you can use the addSpan method to merge cells on the row header or the column header, by simply adding a SheetArea parameter.
sheet.addSpan(0, 0, 1, 3, $.wijmo.wijspread.SheetArea.colHeader);

Certainly, you can remove span from a merged cell by using the removeSpan method, this code removes the span for all merged cells in the sheet view port.

var spans = sheet.getSpans();
for(var i = 0; i < spans.length; i++) {
	sheet.removeSpan(spans.row,spans.col);
}

You can try all of the above code in a web page which has initialized SpreadJS, to learn more about getting started with SpreadJS in a web page, please read Quick Start Guide to Using SpreadJS.

For more information about how to use SpreadJS, includes its data model and API, please read our online documentation or try online demos.

How To Set Cell Value in SpreadJS

SpreadJS provides many ways to assign value to cells, you can choose any one of options below to set value of cells.
1. setValue/setText method
Sheet object of SpreadJS has a method setValue, this method can be used to set value to any cell in viewport or headers by specific row and column index. The value can be any type of data like string, date time, boolean, number. In addition, the Sheet object has another method setText, you can use this method to set text to any cell in either viewport or header.

sheet.setValue(0,2,"ColumnHeader", $.wijmo.wijspread.SheetArea.colHeader);
sheet.setValue(2,0,"RowHeader", $.wijmo.wijspread.SheetArea.rowHeader);
sheet.setValue(2,2,"viewport", $.wijmo.wijspread.SheetArea.viewport);

2. Set value by Cell object
Sheet object has two methods getCell and getCells, you can use them to get a Cell object which indicates a specific cell, or a range of cells, and use value/text method of Cell object to set value to a cell or a range of cells.

sheet.getCell(0,0).text("cell");
sheet.getCells(1,1,3,3).value("range");


3. Binding to JSon array
You can create a JSON array, and bind it to Sheet object by using the setDataSource method.

var arr = [
{"Series0":2,"Series1":1},
{"Series0":4,"Series1":2},
{"Series0":3,"Series1":4}
];
sheet.setDataSource(arr);


4. Import CSV text
You can import a CSV text string into a range of cells by using the setCsv method of Sheet object.

var csv = "1,2,3;a,b,c";
sheet.setCsv(1,1,csv,";",",");

You can try all of the above code in a web page which has initialized SpreadJS, to learn more about getting started with SpreadJS in a web page, please read Quick Start Guide to Using SpreadJS.

For more information about how to use SpreadJS, includes its data model and API, please read our online documentation or try online demos.

Quick Start Guide to Using SpreadJS

SpreadJS is JavaScript spreadsheet widget which is based on HTML5 and jQuery technology. It is very easy to use in a web page, and in this post I’ll show you how to initialize a SpreadJS widget in a web page in four simple steps.
Step 1. Create a standard html file

<!DOCTYPE html>
<html>
    <head>

    </head>
    <body>

    </body>
</html>

Step 2. Define SpreadJS host in the body section, give the host an id which will use to create and get the widget instance in JavaScript code. Then you can set some settings of the host like size, border via css styling.

<div id="ss" style="height:500px;border:solid gray 1px;"/>

Step 3. Add script files and css files reference in the head section, here it uses CDN, as for file names of Wijmo and SpreadJS, please go to our download page to get CDN links of the latest version.

<!--jQuery References-->
<script src="http://code.jquery.com/jquery-1.8.2.min.js" type="text/javascript"></script>
<script src="http://code.jquery.com/ui/1.9.1/jquery-ui.min.js" type="text/javascript"></script>

<!--Theme-->
<link href="http://cdn.wijmo.com/themes/aristo/jquery-wijmo.css" rel="stylesheet" type="text/css" title="rocket-jqueryui"/>

<!--Wijmo Widgets CSS-->
<link href="http://cdn.wijmo.com/jquery.wijmo-pro.all.3.20131.3.min.css" rel="stylesheet" type="text/css" />

<!--Wijmo Widgets JavaScript-->
<script src="http://cdn.wijmo.com/jquery.wijmo-open.all.3.20131.3.min.js" type="text/javascript"></script>
<script src="http://cdn.wijmo.com/jquery.wijmo-pro.all.3.20131.3.min.js" type="text/javascript"></script>

<!-- SpreadJS CSS and script -->
<script src="http://cdn.wijmo.com/spreadjs/jquery.wijmo.wijspread.all.1.20131.1.min.js" type="text/javascript"></script>
<link href="http://cdn.wijmo.com/spreadjs/jquery.wijmo.wijspread.1.20131.1.css" rel="stylesheet" type="text/css" />

Step 4. Add script to create an instance of SpreadJS after these references in the head section, and initialize SpreadJS by JavaScript code, merge cell, set value, style, formula to cells…

<script type="text/javascript">
    $(document).ready(function () {
                $("#ss").wijspread({ sheetCount: 2 }); // create wijspread widget instance
                var spread = $("#ss").wijspread("spread"); // get instance of wijspread widget
                var sheet = spread.getActiveSheet(); // get active worksheet of the wijspread widget
                // initialize spreadJS
            });
</script>

That’s it, then you can open the file in a browser, SpreadJS will be there.

For more information about how to use SpreadJS, its data model and API, please read our online documentation or try online demos.

Wijmo 2013 v1 Releases Mobile Widgets and an HTML5 Spreadsheet Control

A Collection of UI Widgets for Both Mobile and Web Development now with BreezeJS Support

PITTSBURGH, PA – April 18, 2013 - ComponentOne, a division of GrapeCity and a leader in developer-productivity tools, has announced the 2013 v1 release of Wijmo Enterprise: a kit of user interface (UI) widgets for HTML5 development. This release debuts a new addition to the kit, a high performance spreadsheet component for enterprise web application development.

“We have expanded our collection of over 40 widgets to include first-class mobile support,” said Chris Bannon, product manager at ComponentOne.  “All of our widgets will automatically adapt to jQuery Mobile or jQuery UI environments given we have created the first and only Adaptive Widget Framework on the market with this release” said Bannon.

Initially released as a Beta version in February of 2013 and since moved into RTW (release to web),  Wijmo Enterprise   now models the company’s other products with a subscription-based plan. Bannon shared that, “Wijmo will feature three releases per year and any customer with a current subscription will be eligible for free upgrades.”

BreezeJS is quickly becoming the industry standard plug-in for providing data services to JavaScript applications and the 2013 v1 release of Wijmo integrates with these standards. This integration with BreezeJS keeps Wijmo lightweight and gives developers the flexibility to choose the frameworks they want to work in.

Building off the company’s 20 years’ experience with spreadsheet controls, ComponentOne has introduced SpreadJS, the new spreadsheet control which is now part of Wijmo Enterprise. This JavaScript-based widget uses HTML5 Canvas to render Excel-like spreadsheets. “SpreadJS brings additional data visualization and calculation features into your web applications,” said Bannon. “Now you can create calculators, dynamic interactive dashboards, rich colorful reports, and much more using SpreadJS.”

Availability                                                                                                                                                                              The Company offers download and purchase options online at https://wijmo.com/purchase/  or by email at sales@wijmo.com.

Resources

Chris Bannon Blog post: http://wijmo.com/wijmo-2013v1-has-landed/

About Wijmo                                                                                                                                                                               Wijmo provides a set of jQuery widgets for creating interactive websites and apps. Built on jQuery UI and jQuery Mobile guidelines and frameworks, each widget is fully themed and theme-able using Themeroller. Wijmo has everything you need including grids, charts, interactive widgets, and more. Visit http://wijmo.com. Follow @wijmo at http://twitter.com/wijmo and “Like” Wijmo on Facebook at http://www.facebook.com/Wijmo.

SpreadJS Beta has Landed

SpreadJS Beta: Just Released! We’ve improved quality and updated some parts according to user feedback, in addition we add API ref for data model in user guide document, that might help you understand functionality of SpreadJS more. For more information about the product, please read my blog for alpha release.

Download SpreadJS Beta and try the online sample and documentation.

If you have any feedback or questions, please post them in the SpreadJS forum. Your feedback is very important to us.

Using SpreadJS with Knockout

SpreadJS supports Knockout (KO), a JavaScript library that uses the Model View View Model (MVVM) pattern, allowing you to easily create and maintain a dynamically changing UI. You can use KO in addition to jQuery to enhance your pages with an underlying data model. For a full KO introduction, tutorials, documentation, explanation of MVVM and more, visit http://knockoutjs.com/.

You can easily use Knockout with SpreadJS by following a few simple steps:
1. Add references to the latest jQuery dependencies, Wijmo widgets, Knockout .js file, and KO extension library for Wijmo.
2. Create the ViewModel and View: add JavaScript to define the data and behavior of the UI, and add markup to create the View—the visual, interactive UI.
3. Bind the SpreadJS widget to the ViewModel and activate KO.

Here is a sample of list-details mode UI for records editing, it will show you how to add references to the required files, create the ViewModel and View, and bind the SpreadJS widget. The first step is to create an .html page and add links to the dependencies to your project within the <head> tags. To do this, just link to the content delivery network (CDN) files:

<!-- jQuery -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js" type="text/javascript"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.9.1/jquery-ui.min.js" type="text/javascript"></script>
<!-- Knockout -->
<script type="text/javascript" src="http://cdn.wijmo.com/external/knockout-2.1.0.js"></script>
<!-- SpreadJS CSS and script -->
<link href="http://cdn.wijmo.com/themes/cobalt/jquery-wijmo.css" rel="stylesheet" type="text/css" title="rocket-jqueryui" />
<link href="http://cdn.wijmo.com/spreadjs/gcfilter-ui.css" rel="stylesheet" title="metro-jqueryui" type="text/css" />
<link href="http://cdn.wijmo.com/themes/wijmo/jquery.wijmo.wijsuperpanel.css" rel="stylesheet" type="text/css" />
<script src="http://cdn.wijmo.com/spreadjs/jquery.wijmo.wijspread.full.min.js" type="text/javascript"></script>

Now a view model to define the sample data and behavior of the UI. Enter the following <script> within the <head> tags:

function Pet(name, sales, price) {
    this.name = ko.observable(name);
    this.sales = ko.observable(sales);
    this.price = ko.observable(price);
}

var initialData = [
    new Pet("Well-Travelled Kitten", 352, 75.95),
    new Pet("Speedy Coyote", 89, 190.00),
    new Pet("Furious Lizard", 152, 25.00),
    new Pet("Indifferent Monkey", 1, 99.95),
    new Pet("Brooding Dragon", 0, 6350),
    new Pet("Ingenious Tadpole", 3940, 0.35),
    new Pet("Optimistic Snail", 420, 1.50)];

var PagedGridModel = function (items) {
    this.activeIndex = ko.observable(0);
    this.items = ko.observableArray(items);
    this.activeItem = ko.observable(this.items()[this.activeIndex()]);
    this.addItem = function () {
        this.items.push(new Pet("New item", 0, 100));
    };
};

To create Textbox HTML elements and SpreadJS Widget, place the following markup within the body of the .html document.

    <div>
        <div>
            <h3>Knockout List-Detail Binding Sample</h3>
            <p>Name: <input type="text" data-bind="value: activeItem().name" /></p>
            <p>Sales: <input type="value" data-bind="value: activeItem().sales" /></p>
            <p>Price: <input type="value" data-bind="value: activeItem().price" /></p>
            <br />
            <div id="ss" data-bind="dataSource: items()" style="position:relative; width:640px; height:300px; border:1px solid grey"></div>
        </div>
    </div>

Now create SpreadJS instance and bind to the ViewModel and activate knockout.js by adding the following JavaScript within the <script> tags after the ViewModel script:

$("#ss").wijspread({ sheetCount: 1 });
var ss = $("#ss").wijspread("spread");
var vm = new PagedGridModel(initialData);
ko.applyBindings(vm);

In addition provide some data field wrapper, and binding initialize and value sync logic:

function name(item, value) {
    if (arguments.length == 2) {
        item["name"](value);
    } else {
        value = item["name"]();
        return value;
    }
}

function sales(item, value) {
    if (arguments.length == 2) {
        item["sales"](value);
    } else {
        value = item["sales"]();
        if (typeof (value) == "string" && value.length > 0) {
            value = parseInt(value);
        }
        return value;
    }
}

function price(item, value) {
    if (arguments.length == 2) {
        item["price"](value);
    } else {
        value = item["price"]();
        if (typeof (value) == "string" && value.length > 0) {
            value = parseFloat(value);
        }
        return value;
    }
}

ko.bindingHandlers.dataSource = {
    init: function (element, valueAccessor, allBindingsAccessor, viewModel) {
        var ss = $("#" + element.id).wijspread("spread");
        ss.getActiveSheet().autoGenerateColumns = false;
        ss.getActiveSheet().setDataSource(valueAccessor());
        var cis = [
            { name: "name", value: name },
            { name: "sales", value: sales },
            { name: "price", formatter: "$#,##0.00", value: price }];
        ss.getActiveSheet().bindColumns(cis);

        var cc = ss.getActiveSheet().getColumnCount();
        for (var i = 0; i < cc; i++)
            ss.getActiveSheet().setColumnWidth(i, 160);
        ss.invalidateLayout();
        ss.repaint();
    },
    update: function (element, valueAccessor, allBindingsAccessor, viewModel) {
        var ss = $("#" + element.id).wijspread("spread");
        ss.repaint();
    }
};

When you open the page, it looks similar to the following image. Enter a new value in the TextBox and leave focus out. Notice the values in SpreadJS change to match the specified value, vice versa. Download the sample html page here.