Spread the word

This article was originally published in VSJ, which is now part of Developer Fusion.
What is the first thing you demand from any component? In many cases the answer is complete transparency of use. You don’t want to spend days reading the manual before you get started, you simply want to purchase and play. FarPoint has been providing us with a purchase and play spreadsheet component for many years, and even though it has grown increasingly sophisticated, it is still possible to use most of its facilities without reading the manual. The latest version is available for both Windows and Web forms, and there are editions for Visual Studio 2005 and earlier.

Given that a spreadsheet is such a visual component perhaps this isn’t surprising, but there is more to it than simply embedding an Excel look-alike on a form or web page. What you require in a programmable component is very different from what’s needed in an end-user spreadsheet. The main distinction is the division into design time, coding and runtime activities. FarPoint Spread is easy to use in all three modes. When you start up a project you can simply place an instance of the control on a form or web page. What next? The answer is provided by the addition of a Spread Designer option in the right-click popup context menu. Selecting this takes you to a design tool that allows you to interactively customise most of the properties of interest. In the latest version the designer can operate stand-alone, and the results can be used as a template for other projects, or as an object loaded by a script (for example).

From the viewpoint of initial use, what you consider as important features depends on what you are trying to do, and given the range of features, there’s a huge range of possibilities. If you think that a spreadsheet control is just something to hold and work with tables of numbers then you need to think again. The reason that FarPoint Spread is a favourite for many programmers is that you can use it to create many different user interfaces and computational components. For example, it supports 14 pre-defined cell types, including Button, Picture, checkbox, combobox and owner-drawn, and a range of formatted simple data types. You can of course add your own cell types with a little bit of programming. Cells can also be joined to make tables with spanning cells.

The spreadsheet can be multi-page, and its tabbed access to pages makes it an easy way of implementing user interfaces with a tab structure. There are drawing tools that the user or your application can use to highlight different parts of the spreadsheet. You can create your own shapes, and each shape can be rotated and resized, and you can constrain how the end user can edit them.

Given that it is possible to use formatting to hide all of the familiar spreadsheet style of presentation and to customise how the user can interact with the grid, there is no reason the user need ever know that at the bottom of your sophisticated interface there is indeed a spreadsheet! For example, if you want a filmstrip display, simply customise the grid to show one row and multiple columns and use Picture cells. If you want a photo album use multiple pages with tabbed access and so on.

The Web Form spreadsheet is used on FarPoint's web site
The Web Form spreadsheet is used on FarPoint’s web site

FarPoint refers to Spread as being “cell-centric”, which doesn’t make immediate sense until you discover that you can customise almost everything at the single cell level, thus building up a spreadsheet that is a long way from being a regular grid.

Of course you can use FarPoint Spread as an embedded spreadsheet and make it look like one that the user knows, e.g. Excel. It supports 300 functions, now with the ability to reference data or functions across pages. You can add custom functions and control recalculation to a single cell. You can also sort using up 256 keys, and the user can sort on any column. Loading and saving of data can be in Excel format or, if you prefer to be vendor-independent, XML, or a range of text formats, and you can copy and paste data from and to Excel. From the point of view of presentation and formatting it also has everything you would expect and more. You can set fills and borders, automatically merge cells with identical content, and provide multi-row column headings. It also supports gradient fills, background images and alpha blending. You can define skins and styles for instant and almost complete customisation, which can be selected and applied by the end user.

You don’t have to bind the grid to a data source, and this makes using it easier for general applications. However, if there is a database involved in your application, data binding using ADO 2.0 makes things much simpler. For many database operations a spreadsheet approach to data retrieval, processing and storage is much simpler than exposing the user to SQL or anything similar.

You can achieve a lot without writing a single line of code, but usually the time does eventually arrive when you have to discover how to interact with a spreadsheet control interactively. In the case of FarPoint Spread, the object hierarchy isn’t difficult to discover by Intellisense-guided exploration. For example, to access the object that represents cell A1 on the first sheet you simply write:

FpSpread1.Sheets(0).Cells(0,0)
From here you have access to all of the cell’s properties. In addition to cell objects there are also Column and Row objects, which can be used in similarly obvious ways.

So far everything described has been about the Windows Form component, but there is a very similar Web Form component that brings almost all the same features. Given the generally restricted set user interface typical of a web page, this is even more useful and impressive. If you want to see how well it works take a look at FarPoint’s own web site, where many pages are implemented using the Spread control. Because of the range of cell types supported you can use the control to implement a huge number of page types, including online catalogues, shopping baskets, bulletin boards, and so on. Using a tabbed multi-page view allows you to pack the equivalent of multiple HTML pages into one structure. Of course the ability to load Excel spreadsheets provides an interesting way of publishing existing spreadsheets to the web.

The designer in action and a selection of cell types
The designer in action and a selection of cell types

If you are worried about performance over the Internet, then the “Ajax”-like ability to refresh the entire component without having to refresh the entire page is something you should look into. No round trip is required when the user modifies the view by expanding or contracting a hierarchical display, column sorting, inserting rows, searching, filtering, paging and editing formulae. The user can also perform multiple changes locally. Programming the Web component is just as easy, and it supports client side scripting, another way of reducing round trip editing. It has also been tested with a range of browsers and the .NET 1.1 and 2.0 Frameworks.

Clearly there are lots of useful features of FarPoint Spread that haven’t been mentioned or have suffered undeserved neglect – this would be so in any short review given the richness of the product. Once you start using Spread you will discover uses for it everywhere, and come to appreciate that it provides a quick, reliable, and hence cost-effective, approach to building user interfaces and to supplying much of the logic involved in an application. You could call it the Swiss Knife of components, but to be honest it has more uses than this suggests! Try one of the downloadable evaluation copies, and you will almost certainly find that you want to keep it.

You might also like...

Comments

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“My definition of an expert in any field is a person who knows enough about what's really going on to be scared.” - P. J. Plauger