Delivering Spreadsheet Capabilities Across Platforms

Executive Summary

Many businesses require the management of data, from financials to inventory, from personnel to parts ordering, and depend on customized applications to provide the user interface and the manipulation algorithms. For many businesses, simply using spreadsheets in office applications are the extent of what they can handle. As they grow, though, those same businesses must move to more advanced applications, and spreadsheet components provide a quick and proven way to make the step up.

We have created a fictitious company, Calaway-Williamson winery, to illustrate the requirements that these companies face and the solutions they have found in spreadsheet components. With a minimal amount of .NET code, you can import existing Excel spreadsheets, customize the component appearance to create a corporate user interface, and quickly develop applications that can be deployed on the Windows desktop for back-office work as well as on the Web for a mobile sales force. FarPoint recommends trying the Spread component, in both Windows Forms and ASP.NET applications, for rapid spreadsheet application development across platforms.

Downloads

The complete sample code for the below projects can be found in the evaluation's samples folders:

• Spread for Windows Forms Evaluation - for .NET development.
• Spread for Web Forms Evaluation - for ASP.NET development.

Introduction

When Calaway-Williamson first opened their doors for business, they were a small winery with high hopes. They used Microsoft® Excel® spreadsheets to keep track of the data about their wine, and were very particular about the information. As the number of customers and employees increased, the business required better reporting and more data management, with a growing amount of data. The solution they found was to import their existing Excel spreadsheets into FarPoint’s spreadsheet component, convert the simple spreadsheet into a full-fledged spreadsheet application, and deploy it on multiple networked machines royalty-free. Once the business end moved to this more sophisticated solution, the sales people wondered if the same capability could be delivered to their Web platform so they could access the data as needed in the field. Since FarPoint Spread has a Web Forms version, they were able to quickly upgrade to this capability. Calaway-Williamson could handle their growth by using a spreadsheet component to rapidly and easily develop the applications they needed to manage their business. While this particular company is fictitious, the requirements and solutions represented in this story come from actual customers that have found their solutions in FarPoint Spread. It is because FarPoint can provide these capabilities across platforms in a royalty-free spreadsheet component that FarPoint is the leader in enterprise spreadsheet solutions.

Converting Excel Sheets to a Spreadsheet Application

With so much corporate data in easy-to-use Excel spreadsheets, you do not need to lose any of that data when moving to a spreadsheet application. Simply use FarPoint Spread to open the Excel (.XLS) file, and all the data is available, including formulas, custom names, and print settings. Converting from Excel to Spread is as easy as one line of code:

fpSpread1.OpenExcel(fileName);

There are several ways to open Excel files in Spread, and additional parameters that you can specify to determine the exact handling of the file. You can include lists of warnings during the import, you can specify the sheet name or number, you can import from a filename or from a stream. Whichever method you choose, it is a simple procedure. Another advantage of importing Excel files, besides the ability to rescue legacy spreadsheets, is the ability to prototype in Excel and then import the finished prototype into Spread.

Rapidly Developing a Spreadsheet Application

Seeing the Basics

The quickest way to see how easy it is to develop an application using a spreadsheet component is to try the trial version of the Spread product. With the downloaded Spread for Windows Forms setup, in the VB examples folder, open the fpwine-orderentry demo and see what Calaway-Williamson created. Open and run the order entry program. When you click on Enter Order, a search form appears. To enter an order, click on Find Customer; an additional list appears that displays the customers. Here is a good example showing some of the spreadsheet capability. The figure below shows the interface of the order entry program:

The code is easily edited and built inside Microsoft Visual Studio .NET. Whether you are working in C# or Visual Basic .NET, developing an application using Spread can be as easy as selecting Spread from the toolbox, placing it on a form, and writing a few lines of code to customize it. In this demo, there is a parent form that contains the menu of buttons on the left and the grapes graphic. The order entry form contains the two spreadsheets and the two buttons shown inside the parent form. This illustrates how the spreadsheet component can be used for data entry and database connection.

Showing the Code

With this type of spreadsheet capability behind each part of the user interface, you can validate user entries, allow spreadsheet calculations, have the application react to user inputs by basing actions on events, provide calendar and clock controls in cells, and provide printable reports.

Select a customer, and the order entry screen appears. With the order form, you can fill in the invoice; select an item, and the Item Total column fills in; type in a Sales Tax, automatically calculated; change the shipping method, and the Freight item changes. These are features made possible by the underlying spreadsheet capability – the powerful calculation engine. Add another item, with a quantity greater than one, and add a discount of 2%, and the values are automatically readjusted.

Following is all the code you need to sum up the row totals –

'Sums up a row = (qty * unitprice) * discount
fpSpread1.Sheets(0).Columns(5).Formula = "(A1*E1) * (1-D1)"

The figure here shows the results:

Here is a sample code that handles the selection of an item from the Item ID combo box list by a user and automatically populates the product description field and the unit price, which is then totaled in the calculation for the item total price:

Private Sub ssProducts_ComboSelChange(ByVal sender As Object, _
            ByVal e As FarPoint.Win.Spread.EditorNotifyEventArgs) _
            Handles ssProducts.ComboSelChange
      'Selected an ItemID. Find and add the Description and Price
      Dim seltext As Object
      'Get the selected ItemID
      seltext = CType(ssProducts.EditingControl, _
                FarPoint.Win.FpCombo).SelectedItem
 
      If seltext <> Nothing Then
         Dim dr As DataRow
         'Find the row in the table
         dr = prodtable.Rows.Find(seltext)
  
         With ssProducts.Sheets(0)
            'Add the Description
            .SetValue(e.Row, 2, dr.Item("Product"))
            'Save the ItemID in the cell tag for future reference
            .SetTag(e.Row, 2, seltext)
            'Add the Price
            .SetValue(e.Row, 4, dr.Item("Price"))
         End With
 
     End If

End Sub

Finding Lots of Capability

As another example of the capabilities of Spread, click on AgedReceivables. This form shows a spreadsheet that illustrates many of the customizable features of Spread.

A single Spread component can handle up to 2 billion sheets, each with up to 2 billion rows and columns. Cross-sheet referencing allows calculations to make use of data and formulas on a variety of sheets. With over 300 built-in functions and with the ability to create your own custom functions, Spread provides all the spreadsheet calculation power you need. With a formula editor, you can easily create formulas for any number of calculations, just as you would in any spreadsheet. As the example shows, calculations of totals are performed automatically just as discounts were calculated in the order entry form above.

As this use of Spread illustrates, you can set multiple headers and span several headers. You can span cells across columns or across rows, and even automatically merge cells with identical content. You can have cell notes (the red dots indicating a cell note is available when you move the cursor over that dot). The numbers of rows and columns can be predetermined. The widths of columns and the heights of rows can be adjusted. All of these are built-in features, and available by setting properties on certain objects in the spreadsheet.

All of this from a single spreadsheet component.

Setting these can be as easy as a few lines of code that set properties on those objects:

With fpSpread1.Sheets(0)
    .RowCount = 6
    .ColumnCount = 3
  
    .ColumnHeader.Visible = True
    .RowHeader.Visible = False
  
    'Set Column properties
    .Columns(0, 2).Locked = True
    .Columns(0).Width = 81
    .Columns(0).BackColor = color.Yellow
    .Columns(0).HorizontalAlignment = _
       FarPoint.Win.Spread.CellHorizontalAlignment.Right
  
    'Change default row height
    .Rows.Default.Height = 30
  
    'Add labels in row x, column 0
    .SetValue(0, 0, "Subtotal:")
    .SetValue(1, 0, "Sales Tax:")
    .SetValue(2, 0, "Freight:")
    .SetValue(3, 0, "Total:")
    .SetValue(4, 0, "Amount Paid:")
    .SetValue(5, 0, "Balance Due:")
  
    'Create Cell Spans
    ' row, column, rowCount, columnCount
    .Models.Span.Add(0, 0, 1, 2)
    .Models.Span.Add(2, 0, 1, 2)
    .Models.Span.Add(3, 0, 1, 2)
    .Models.Span.Add(4, 0, 1, 2)
     .Models.Span.Add(5, 0, 1, 2)
  
    'Create Percent cell
    Dim cellPercent As New FarPoint.Win.Spread.CellType.PercentCellType()
    cellPercent.DecimalPlaces = 1
    .Cells(1, 1).CellType = cellPercent
  
    'Create Currency cells for column 1
    Dim cellCur As New FarPoint.Win.Spread.CellType.CurrencyCellType()
    cellCur.NegativeRed = True
    cellCur.ShowSeparator = True
    'Apply to column 1
    .Columns(2).CellType() = cellCur
  
End With

Reaching the Web

With the success of rapid and easy development of a Windows application, the management of Calaway-Williamson next asked if the same capability could be available to their sales team who were constantly on the road and interfacing with existing and prospective customers. They learned they could easily develop a Web application using the Spread for Web Forms product that could access the same corporate database with similar or customized interfaces. With the same scalability and performance features as the Windows version, they generated the pages needed for a Web application. This figure shows the same capability that was available in the Windows application, but available in a Web browser:

Both Spread products (Windows and Web Forms) are built on common models, so developers can quickly get the same look and feel for applications developed on both platforms, with a small amount of changing the code between these two platforms.

With the client-side functionality built in, you can make your Web application work much like a Windows application without postbacks. By using AJAX call backs and the right considerations, the performance of the Web application can feel just like a Windows application to the end user.

With similar code as the Windows application, the Spread for Web Forms application was easy to develop.

The project went on to even more success.

Making Inventory Control Immediate and Mobile

Computer-Based Menu

When a local restaurant, anai-nalu, began carrying Calaway-Williamson wines, their management saw a demo of the interfaces that Calaway-Williamson had developed, and asked if they could use similar technology for their staff who accessed information about the wines stocked in their restaurant. With the large number of wines available, and the large amount of information about each wine, the restaurant wanted to provide staff with access on a portable device, if possible. The response was again Yes, and the restaurant was given a Windows-based application of their wine list. This application runs on Tablet PCs that the waiters and waitresses use when taking orders. When a wine is selected, the waiter could use a cell note to display more information about that wine, as shown in the figure below:

Notice that there are many features: the buttons, the graphics, and gradients in certain places, that don’t even look like a spreadsheet. Everything in this application is done with a spreadsheet component. The figure below shows the view inside the development environment, without the appearance customizations:

With this application, they could immediately notify the maitre d' to send someone to the wine cellar and prepare the wine, even before the waiter had left the customer.

Here is the code that handles clicking on a wine selection and adding it to the order:

Private Sub fpWineList_ButtonClicked(ByVal sender As Object, _
        ByVal e As FarPoint.Win.Spread.EditorNotifyEventArgs) _
        Handles fpWineList.ButtonClicked
'Clicked on a checkbox. Add the ordered item

    'Add the item to the cart
    With fpCart.ActiveSheet
        'Get the last empty row to add the data to
        Dim row As Integer = .NonEmptyRowCount
        'Add ID
       .Cells(row, 0).Text = fpWineList.ActiveSheet.Cells(e.Row, 0).Text
        'Add Description
        .Cells(row, 2).Text = fpWineList.ActiveSheet.Cells(e.Row, 4).Text _
                              & " - " & fpWineList.ActiveSheet.Cells(e.Row, 6).Text
        'Add Price
        .Cells(row, 3).Text = fpWineList.ActiveSheet.Cells(e.Row, 7).Text
    End With

End Sub

Complete Inventory Control

Then they figured out that they could even use this application to help keep inventory stocked for their more popular wines. When an order was selected, Calaway-Williamson could be notified immediately how many bottles were subtracted from the inventory, allowing them to keep the wine cellar stocked.

Making Spreadsheet Design Easy

Objects in the Spread

One of the lessons learned by Calaway-Williamson was how robust Spread was as a foundation for developing very functional applications that required spreadsheet capabilities. The IT department was impressed at how easy it was to work with the code and develop unique and tailored applications. The roots of that success lay in the use of objects in Spread that can so easily be manipulated with a small amount of code. By having columns and rows and even cells as objects, developers who use Spread can easily manipulate and customize those objects. Whether it is a custom cell type or a custom style (with a set of appearance settings for a cell), you can easily customize and extend the built-in objects.

For example, to set the text in a cell, simply use this line of code:

FpSpread1.ActiveSheet.Cells(4, 9).Text = "hello, world"

Spread Designer

Perhaps the easiest way to develop an interface without writing a line of code is to simply use the built-in Spread Designer, a design-time application that runs in Visual Studio or stand-alone and allows you to customize the look and feel of Spread from a graphical user interface (GUI). The Designer includes a display of the spreadsheet and a properties list as well as options from pull-down menus and dialogs. From the Designer, you can preview the spreadsheet before applying the changes to the Spread component.

Underlying Models

The models also allow developers to easily customize them to extend the capabilities of a simple default spreadsheet component. By creating a custom axis model, the use of rows and columns can be tailored throughout the component. There are separate underlying models for the axis (orientation), data, span, selection, and style information.

Finding More Information

You can easily download a free trial evaluation from the FarPoint website and see for yourself how easy it is to develop an application with spreadsheet capabilities.

• Spread for Windows Forms Product Tour
• Spread for Web Forms Product Tour

About FarPoint

Whether you are developing for the Web or a stand-alone Windows applications, FarPoint has a version of Spread that is right for you and that will deploy royalty-free in the environment you need. FarPoint delivers enterprise spreadsheet solutions.

For more information, check out our website, or contact us directly at the North American office or European office:

North America Contact
FarPoint Technologies, Inc.
808 Aviation Parkway
Suite 1300 
Morrisville, NC 27560 USA

Phone: 919-460-4551

email: <A href="mailto:donw@fpoint.com">fpsales@fpoint.com</A>
Web: <A href="http://www.farpointspread.com/" target=_blank>www.farpointspread.com</A>

Europe Contact
FarPoint Europe Ltd.
Whiteleaf, Roundabout Lane
West Chiltington
Pulborough, West Sussex  RH20 2RL
England
 
Tele:  +44 (0) 1798 812 372
Fax:   +44 (0) 1798 813 049

email: <A href="mailto:neils@fpoint.com">salesEurope@fpoint.com</A>

Founded in 1991, FarPoint Technologies, Inc., is a leading developer and publisher of professional components for Microsoft Visual Studio .NET and Microsoft Server products. FarPoint, a privately held company with corporate headquarters located next to Research Triangle Park (RTP) in Morrisville, North Carolina, is one of the most respected vendors in the component industry. Our award-winning tools benefit leading corporations, software companies, and independent consultants around the world as a cost-effective solution for building distributed enterprise-wide applications for in-house use.

Information in the white paper is subject to change without notice, and does not represent a commitment on the part of FarPoint Technologies, Inc.

© 2006 FarPoint Technologies, Inc. All rights reserved.

Unless otherwise noted, all names of companies, products, street addresses, and persons contained herein are part of a completely fictitious scenario or scenarios, and are designed solely to document the use of a FarPoint Technologies, Inc., product.

FarPoint Spread, Spread for Web Forms, and Spread for Windows Forms are trademarks of FarPoint Technologies, Inc. Other brand and product names are trademarks or registered trademarks of their respective holders.