XmlSS.NET Spreadsheet Component

Introduction

XmlSS.NET is a managed spreadsheet component based almost entirely on XMLSS, the XML Schema defined by Microsoft to govern the Excel workbook document instance. What follows in this article is a very brief introduction to XMLSS for those of you who are not already familiar with the subject, followed by a very quick rundown of the purpose, design, implementation, and use of the XmlSS.NET spreadsheet component.

Note: This article assumes you are familiar with the following subject matters:

XMLSS (XML Spreadsheet)

XMLSS is the XML Schema Microsoft established for its Excel workbook document. It became available beginning with Excel 2002 and the Office XP Spreadsheet Component. Any XML document that abides by this specification and is, therefore, an XMLSS instance document can be consumed, manipulated, and once again exported by either of these two products. Notice that when you open or save an Excel file (2002 or above), you have the option to specify XML Spreadsheet (*.xml) as the type of file to open or save, respectively. The Office XP Spreadsheet Component also has the necessary interface needed to load, manipulate, and export an XMLSS instance.

Now that I've pointed you in the right direction for more details regarding XMLSS, let's move on and discuss the XmlSS.NET spreadsheet component. It should be of no surprise, however, that throughout this very short discussion XMLSS will be continually referenced, simply because it is the basis of this component.

XmlSS.NET Spreadsheet Component

Purpose

XmlSS.NET is a lightweight spreadsheet component intended to provide the following main features:

A typical workbook model. That is, a series of types that represent a workbook and its composition. Typically, a workbook has, among many other things, a collection of worksheets. Furthermore, a worksheet has, among many other things, a collection of rows, columns, and cells. The workbook model exposed by this component is no different.

An Excel style view of and control over the worksheet model, one that is completely independent of the model itself and, furthermore, of any additional views the model may have observing it.

The ability to initialize the workbook model though not necessarily through XMLSS but rather by means of any appropriate strategy. In other words, an XMLSS document instance shouldn't be required to initialize the model; instead, the model should be completely independent of the manner by which it is constructed (for example, DataTable, DataReader, Text File, Proprietary XML or Binary File, and so on).

Design Architecture

XmlSS.NET has MVC (Model-View-Controller) as its underlying architecture. There are numerous resources that go into great detail explaining the ins and outs of this architecture and derivatives thereof. Therefore, there's no need for me to do so here, and even if there was, I don't consider myself qualified to detail what has been for a long time and continues to be an excellent architecture for building reusable software. If you are not familiar with MVC, Google it and inform yourself. I guarantee you will not be wasting your time.

Implementation

The XmlSS.NET spreadsheet component is organized into four namespaces, all of which lie within the root namespace, XmlSS. These four namespaces are: XmlSS.Model, XmlSS.View, XmlSS.Factory, and XmlSS.Utilities.

XmlSS.Model

In the XmlSS.Model namespace are all the types that correspond to the model aspect of the component. As I have already mentioned, the component's workbook model is heavily based on XMLSS; specifically, the object model and the latter's DOM are very similar. As it stands, the model is far from complete, simply because its current state does not handle formulas or defined names, two aspects that are, or at least should be, represented by any half decent spreadsheet model implementation, including this one. The only three points I am going to very briefly discuss regarding this component's workbook model are 1) how the row, column, and cell collections behave, 2) the efficient use of styling, and 3) how the model informs its observers, if any, that it's been changed somehow. Pardon the brevity, but I assume you are familiar with the basics of any spreadsheet/workbook model, perhaps the one exposed by Excel. If you have no idea, which I doubt, what a spreadsheet is or what one commonly looks like, you probably have some kind of spreadsheet software on your machine that will show you.

First, the XmlSS.Model.Worksheet type has ExpandedRowCount and ExpandedColumnCount properties, both of which will accept any positive integer value. Furthermore, together these properties define the bounds of the worksheet's table data. However, just because a worksheet instance can be set up to have a high number of rows, columns, and, thereby, cells, it certainly doesn't mean you need an instance in memory for each one of them. On the contrary, only a non-default intrinsic state warrants that additional instance. To handle this, the XmlSS.Model.RowCollection, XmlSS.Model.ColumnCollection, and XmlSS.Model.CellCollection types create objects on the fly via the GetRow, GetColumn, and GetCell methods, respectively. If the instance exists, it is returned immediately; otherwise, it is first created and then stored in the collection before it is returned. Given this behavior, be careful not to call these methods while the respective collection is being enumerated unless you're sure the instance exists; otherwise, the underlying collection may be modified, resulting in an exception being thrown. Moreover, make sure you call these methods only if you actually need an instance to be created; otherwise, you may end up with unnecessary memory consumption, whether large or small. To obtain an instance that has already been created, each collection exposes a default Item property that will return null if the object does not exist; otherwise, a previously created instance. Calling Item can safely be done while enumerating the collection because it does not modify it. Here's what GetCell and Item look like for CellCollection:

Second, the XmlSS.Model.Style type holds formatting information that can be attached to any workbook, worksheet, row, column, or cell. Each Style instance is workbook specific and can be shared by all components of the workbook. In other words, a Style instance can be pooled when that instance expresses the formatting needs of different or all parts of the workbook. Furthermore, Style instances aren't the lightest objects in the world; therefore, it is by all means wise to share them as much as possible. It would definitely be naive to create a bolded font, center aligned, thick bordered Style instance for every cell that needs one. On the contrary, the efficient approach would be to create only one Style instance with these formatting characteristics and subsequently assign it to every object that needs one like it. Once again, make sure you don't create a Style object unless you're certain that you don't already have a compatible one in memory, because Style objects belonging to the same workbook can be efficiently shared by all components of this workbook. In order to give you an idea of the resources involved when creating a Style instance, here's the definition of Style:

Namespace XmlSS.Model
Public Class Style
Private _workbook As Workbook
Private _font As Font
Private _foreColor As Color
Private _alignment As Alignment
Private _interior As Interior
Private _format As String
Private _leftBorder As Border
Private _topBorder As Border
Private _rightBorder As Border
Private _bottomBorder As Border
Private _diagonalLeftBorder As Border
Private _diagonalRightBorder As Border
Public Const DEFAULT_FONT_NAME As String = "Arial"
Public Const DEFAULT_FONT_SIZE As Single = 10.0F
Public Const DEFAULT_EXCEL_FORMAT As String = "General"
Public Shared ReadOnly DefaultFont _
As New Font(Style.DEFAULT_FONT_NAME, _
Style.DEFAULT_FONT_SIZE)
Public Shared ReadOnly DefaultAlignment As New Alignment
Public Shared ReadOnly DefaultForeColor As Color = Color.Black
Public Shared ReadOnly DefaultInterior As New Interior
Public Sub New(ByVal workbook As Workbook)
Me.New(workbook, Nothing, Color.Empty, Nothing, Nothing, _
Nothing, Nothing, Nothing, Nothing, Nothing, _
Nothing, Nothing)
End Sub
Public Sub New(ByVal workbook As Workbook, _
ByVal font As Font, ByVal foreColor As Color, _
ByVal alignment As Alignment, _
ByVal interior As Interior, _
ByVal format As String, _
ByVal leftBorder As Border, _
ByVal rightBorder As Border, _
ByVal topBorder As Border, _
ByVal bottomBorder As Border, _
ByVal diagonalLeftBorder As Border, _
ByVal diagonalRightBorder As Border)
If workbook Is Nothing Then
Throw New ArgumentNullException("Workbook cannot be _
null.")
End If
_workbook = workbook
_font = font
_foreColor = foreColor
_alignment = alignment
_interior = interior
_format = format
_leftBorder = leftBorder
_rightBorder = rightBorder
_topBorder = topBorder
_bottomBorder = bottomBorder
_diagonalLeftBorder = diagonalLeftBorder
_diagonalRightBorder = diagonalRightBorder
End Sub
Public ReadOnly Property Workbook() As Workbook
Get
Return _workbook
End Get
End Property
Public ReadOnly Property Alignment() As Alignment
Get
If _alignment Is Nothing Then
If Not (_workbook.Style Is Me _
OrElse _workbook.Style.Alignment Is _
Nothing) Then
Return _workbook.Style.Alignment
Else
Return DefaultAlignment
End If
Else
Return _alignment
End If
End Get
End Property
Public ReadOnly Property Font() As Font
Get
If _font Is Nothing Then
If Not (_workbook.Style Is Me _
OrElse _workbook.Style.Font Is Nothing) Then
Return _workbook.Style.Font
Else
Return DefaultFont
End If
Else
Return _font
End If
End Get
End Property
Public ReadOnly Property ForeColor() As Color
Get
If _foreColor.IsEmpty Then
If Not (_workbook.Style Is Me _
OrElse _workbook.Style.ForeColor.IsEmpty) Then
Return _workbook.Style.ForeColor
Else
Return DefaultForeColor
End If
Else
Return _foreColor
End If
End Get
End Property
Public ReadOnly Property Interior() As Interior
Get
If _interior Is Nothing Then
If Not (_workbook.Style Is Me _
OrElse _workbook.Style.Interior Is _
Nothing) Then
Return _workbook.Style.Interior
Else
Return DefaultInterior
End If
Else
Return _interior
End If
End Get
End Property
Public ReadOnly Property Format() As String
Get
If _format Is Nothing Then
If Not (_workbook.Style Is Me _
OrElse _workbook.Style.Format Is _
Nothing) Then
Return _workbook.Style.Format
Else
Return String.Empty
End If
Else
Return _format
End If
End Get
End Property
Public ReadOnly Property LeftBorder() As Border
Get
If _leftBorder Is Nothing AndAlso Not _workbook.Style _
Is Me Then
Return _workbook.Style.LeftBorder
Else
Return _leftBorder
End If
End Get
End Property
Public ReadOnly Property TopBorder() As Border
Get
If _topBorder Is Nothing AndAlso Not _workbook.Style _
Is Me Then
Return _workbook.Style.TopBorder
Else
Return _topBorder
End If
End Get
End Property
Public ReadOnly Property RightBorder() As Border
Get
If _rightBorder Is Nothing AndAlso Not _workbook.Style _
Is Me Then
Return _workbook.Style.RightBorder
Else
Return _rightBorder
End If
End Get
End Property
Public ReadOnly Property BottomBorder() As Border
Get
If _bottomBorder Is Nothing AndAlso _
Not _workbook.Style Is Me Then
Return _workbook.Style.BottomBorder
Else
Return _bottomBorder
End If
End Get
End Property
Public ReadOnly Property DiagonalLeftBorder() As Border
Get
If _diagonalLeftBorder Is Nothing AndAlso _
Not _workbook.Style Is Me Then
Return _workbook.Style.DiagonalLeftBorder
Else
Return _diagonalLeftBorder
End If
End Get
End Property
Public ReadOnly Property DiagonalRightBorder() As Border
Get
If _diagonalRightBorder Is Nothing AndAlso _
Not _workbook.Style Is Me Then
Return _workbook.Style.DiagonalRightBorder
Else
Return _diagonalRightBorder
End If
End Get
End Property
End Class
End Namespace

Third, the component's model informs its observers, if any, of changes made to it via events. I have to assume you know how easy it is to implement the Observer pattern by means of events and, therefore, I will not go into any further detail.

XmlSS.NET Spreadsheet Component

XmlSS.View

The XmlSS.View namespace holds the types that provide and help to provide an Excel-style view of and control over the worksheet model, although, as I stated earlier, this is just one of many possible views the worksheet model can have observing it. It is perfectly reasonable to view and control the model via a Windows or Web Form, or via any other UI strategy that makes sense. What's important here is that the model is independent of any and all views, including the XmlSS.View.WorksheetView, the one and only View/Controller type this component exposes.

WorksheetView is a UserControl that provides an Excel-style view of and control over an XmlSS.Model.Worksheet object. WorksheetView has a Worksheet property that, when set, corresponds to the view's subject of observation, display, and control. The WorksheetView does its best to provide an Excel-style UI that reflects the current state of the worksheet, and if the worksheet changes somehow, perhaps because certain cells have been modified in style or content, the WorksheetView will update its display, if necessary, to reflect this change, even if the change to the worksheet is not made through the control facilities of the WorksheetView itself but perhaps through some other controller. The WorksheetView doesn't really care who causes its worksheet subject to change, because either way the subject notifies any and all observers of this change. Because we're on the subject of implementation here, WorksheetView has a Subscribe method that is indirectly called as a result of its Worksheet property being set. Within Subscribe, a series of event handlers is set to be invoked in response to event notifications the subject worksheet will raise when changes are made to it. Here's what Subscribe looks like:

The actual onscreen rendering of the Excel-style view is accomplished by overriding the control's OnPaint method. WorksheetView is completely owner drawn, except for the rendering of its vertical and horizontal scrollbars, both of which are simply child controls. There are plenty of resources available that thoroughly explain control drawing techniques; therefore, I myself am not going to do any elaboration on this matter. Suffice it to say that you paint as quickly as possible only what needs to be painted and all painting must be carried out within OnPaint. Because we're talking implementation here, drawing performance and the efficient use of drawing resources is of the utmost importance. For those of you familiar with Excel or with the Office XP Spreadsheet Component, it isn't all that complicated to imitate the worksheet UI these products provide, but matching the drawing performance is kind of tough, especially when you take into account that 1) WorksheetView is a managed control as opposed to a hard core native C++ control and 2) I am just a humble VB programmer. Nonetheless, it currently does provide decent performance, at least when compared to other managed spreadsheet or grid type components out there on the market. However, you of course are the final judge. Here's what OnPaint looks like:

WorksheetView redraws either certain sections of itself or entirely depending on the kind of changes made to its worksheet subject. Of course, this observation and corresponding reaction to change can certainly have a negative impact on performance in cases where you need to make numerous changes to the worksheet, yet each change may (or may not) result in redrawing on behalf of the WorksheetView. WorksheetView addresses this issue in the same manner a ListBox does, and that is by providing BeginUpdate and EndUpdate methods. BeginUpdate should be called right before numerous changes are made to the worksheet and EndUpdate should be called right after they have been made. During the interval between, WorksheetView will not repaint itself. Here's what BeginUpdate and EndUpdate look like:

Public Sub BeginUpdate()
_isUpdating = True
End Sub
Public Sub EndUpdate()
If Not _isUpdating Then Return
_isUpdating = False
Invalidate(_worksheetBounds)
Update()
End Sub

However, just because you call BeginUpdate prior to making changes to the worksheet subject doesn't mean that WorksheetView will not perform any work whatsoever in response to these changes. BeginUpdate only ensures that no painting is done, yet certain changes made to the worksheet necessitate action on behalf WorksheetView, regardless, some of which are more expensive than others, with the most expensive one being keeping track of cell overflow.

Cell overflow is a UI feature by which the contents of a cell can span across multiple columns if adjacent cells have no content of their own and are not merged to other cells. Excel handles this feature very nicely and WorksheetView tries to do so as well. However, doing so requires additional work and resources. I must say that cell overflow was the feature that gave me the most headaches when implementing WorksheetView. To handle cell overflow, WorksheetView relies on the help of type XmlSS.View.CellSpan, which captures information regarding how much a non-empty cell spans to its left or right, or even both, given the cell's content and style (font, alignment, and so forth) among other things. WorksheetView will store a CellSpan instance for every cell that has content, regardless of whether the content spans beyond the cell itself; hence, the additional resources that are involved. Furthermore, instantiation of a CellSpan instance requires a calculation to be performed, hence, the extra work required. This calculation, CellSpan.Calculate, is as follows:

You can turn off cell overflow by setting the CellOverflow property of the WorksheetView to false. Doing so will prevent the above calculation from ever being performed in response to changes made to the worksheet model. For example, here's the event handler that is invoked in response to changes made to cell content; notice the extra work involved if cell overflow is enabled:

Protected Sub Worksheet_OnCellValueChanged(ByVal sender As Object, _
ByVal e As CellChangedEventArgs)
Dim rowIndex As Integer = e.RowIndex
Dim colIndex As Integer = e.ColumnIndex
If _cellOverflow Then
Dim cs As CellSpan = GetCellSpan(rowIndex, colIndex)
If Not cs Is Nothing Then
RemoveCellSpans(cs)
End If
Dim cell As cell = _worksheet.Cells.GetCell(rowIndex, colIndex)
If cell.TextInflow AndAlso cell.MergeAcross = 0 _
AndAlso cell.MergeDown = 0 _
AndAlso cell.MergedTo Is Nothing Then
Dim leftCol As Integer = colIndex - 1
If leftCol >= 0 AndAlso _
_columnCellSpans.ContainsKey(leftCol) Then
cs = GetCellSpan(rowIndex, leftCol)
If cs Is Nothing Then
cs = GetLeftCellSpan(rowIndex, leftCol)
If Not cs Is Nothing Then
RemoveCellSpans(cs)
AddCellSpans(cs, cs.Cell)
End If
Else
RemoveCellSpans(cs)
AddCellSpans(cs, cs.Cell)
End If
End If
Dim rightCol As Integer = colIndex + 1
If rightCol < _worksheet.ExpandedColumnCount _
AndAlso _columnCellSpans.ContainsKey(rightCol) Then
cs = GetCellSpan(rowIndex, rightCol)
If cs Is Nothing Then
cs = GetRightCellSpan(rowIndex, rightCol)
If Not cs Is Nothing Then
RemoveCellSpans(cs)
AddCellSpans(cs, cs.Cell)
End If
Else
RemoveCellSpans(cs)
AddCellSpans(cs, cs.Cell)
End If
End If
End If
AddCellSpans(Nothing, cell)
End If
If _isUpdating OrElse Not _visibleCells.ContainsKey(_
cell.GetIndex(rowIndex, colIndex)) Then
Return
End If
Invalidate(_worksheetBounds)
Update()
End Sub

If you need to make numerous changes to the worksheet AFTER it has become the WorksheetView's subject, you should probably call BeginUpdate and set CellOverflow to false in order to avoid redundant repainting and cell overflow recalculations, respectively. Once the changes have been made, you then should proceed to set CellOverflow to true and call EndUpdate. Now remember, this is only necessary if you need to make a large number of changes to the worksheet AFTER it has become the WorksheetView's subject of observation. The best approach, at least in terms of speed, is to first load the worksheet and then afterwards configure it to be the WorksheetView's subject, although this is not always convenient or possible. Nonetheless, I'm comfortable with the cell overflow performance given my comparison with other managed third-party, grid-type controls that also support cell overflow. However, once again you are the final judge.

XmlSS.NET Spreadsheet Component

XmlSS.Factory

The XmlSS.Factory namespace exposes two types that implement the XmlSS.Factory.IWorkbookFactory interface, which has a single public method called CreateWorkbook that returns an XmlSS.Model.Workbook instance. Types that implement this interface act as strategies by which to load the workbook model. As mentioned earlier, the model's composition can be constructed via any applicable means, and not just by the types provided by this component. Furthermore, it is not even necessary for a type to implement this interface in order to compose the model. The intention of the interface is simply to promote a well-defined, factory-style manner of constructing the workbook model that can be easily configured at compile time or runtime. You can just as easily load the workbook inside a button's click event handler, if you prefer that route. The IWorkbookFactory definition is as follows:

Namespace XmlSS.Factory
Public Interface IWorkbookFactory
Function CreateWorkboook() As Workbook
End Interface
End Namespace

The main workbook creation strategy exposed by this component is the XmlSS.Factory.XmlSSWorkbookFactory type, which requires a path to a valid XMLSS document instance in order to carry out its workbook creation strategy. Currently, the strategy does not populate the workbook model with information relating to defined names, formulas, or range selections. Furthermore, the strategy does a poor job at converting an Excel number format string to a .NET number format string. Here's how XmlSSWorkbookFactory implements the IWorkbookFactory interface:

XmlSS.Utilities

The XmlSS.Utilities namespace exposes types that, for the most part, provide static utility functions. Need I say more? Well, actually, there is one, and only one, type I must elaborate on, not because it's of any particular significance but rather due its underlying implementation source, which I have the responsibility to disclose.

Type XmlSS.Utilities.IntKeyHashtable, as its name implies, is a hashtable that accepts only non-negative integer keys for the values stored within it. The need for it arose out of my attempts to improve the performance of the component, because the original implementation that relied on the System.Collections.Hashtable type suffered the performance/memory blow associated with constant boxing. Because Generics is not yet available and I wasn't about to implement a custom hashtable of my own, I decided to follow someone's advice to download and study the hashtable implementation provided by the SSCLI (http://www.microsoft.com/downloads/details.aspx?FamilyId=3A1C93FA-7462-47D0-8E56-8DD34C6292F0&displaylang=en) to create my own. The IntKeyHashtable and Hashtable types are not identical; rather, the former exposes a small subset of the functionality provided by the latter, with the biggest difference being the type of key each one accepts. However, this small subset IS pretty much identical to the original source, with the only difference being the language used. Therefore, if for some reason you decide to use this component for commercial purposes you mustreplace type IntKeyHashtable or its implementation with your own in order to comply with http://msdn.microsoft.com/MSDN-FILES/027/002/097/ShSourceCLILicense.htm. The reason why I haven't taken the time to implement my own integer key hashtable is that by the time I finish the model's calculation engine .NET 2.0 will most likely be available.

Using XmlSS.NET

For details regarding how to use XmlSS.NET, please download the solution, which contains the component itself as well as a demo demonstrating its basic use. The demo shows:

How an XMLSS instance document can be used to load the workbook model. Under the file menu, there's an "Open XMLSS File" menu item that, when clicked, will prompt you for an XMLSS file. If you don't have one, you can open up the one that comes along with the demo.

An alternative way of loading the workbook model via a tab delimited text file that comes along with the demo. This function can be invoked by clicking on the file menu's "Load Tab Delimited File" menu item.

The Excel style UI provided by the WorksheetView control.

Also, the demo makes use of the PropertyGrid control to easily modify the properties of the WorksheetView and its worksheet subject. Notice that WorksheetView not only responds to changes made to its own properties but also to those made to its worksheet.

Final Notes

Currently, the component lacks support for formulas and defined names. These are next on my list and I'm sure the Interpreter pattern will play a significant role in the design and implementation of formulas.

Currently, WorksheetView only supports about 85% of style characteristics. Formatting features such as double borders are not currently supported. I don't have any intention on doing anything else with WorksheetView except fix bugs.

The component is not thread safe, although this will certainly have to change once the model supports formulas. The event handlers WorksheetView configures to respond to changes made to its subject worksheet do not currently check InvokeRequired to determine whether a call to Invoke is needed in order to execute the event handler on the same thread as the one the control was created on.

Type Style should probably implement IDisposable, but currently it does not.

There's no doubt in my mind that bugs exist, especially within WorksheetView, although I have fixed all the ones I've spotted. If you happen to find something, please let me know.

Currently, the component has no error handling. Big no, no!

You'll notice right away just by looking at the code that my worst programming sin is my inability to write comments. I'm certainly lucky that I haven't been fired for this. My sincere apologies.

If you have any questions or comments, good or bad, about the component, please post them or feel free to e-mail me (giancarloaguilera@hotmail.com). This article has been very brief, I know, and I apologize for all the assumptions I've made throughout.

Acknowledgements

I'd like to thank Gnumeric (http://www.gnome.org/projects/gnumeric/). Like I mentioned earlier, cell overflow was probably the toughest feature to implement within WorksheetView. Luckily, I found the solution, in the form of type CellSpan, within the Gnumeric source code. Gnumeric is "classic" MVC in action, unlike other components, including WorksheetView, that merge the roles of View and Controller into just one type, thereby resulting in extensibility being achievable only via class composition as opposed to object composition. Thanks!

Top White Papers and Webcasts

Migrating away from Windows Server 2003 is an investment in your organization's future, and there has never been a better time to begin the migration process. Take the next step to transform your datacenter by upgrading your server platform with leading edge Windows Operating Systems and SanDisk flash solutions.

Data integrity and ultra-high performance dictate the success and growth of many companies.
One of these companies is BridgePay Network Solutions, a recently launched and rapidly growing financial services organization that allows merchants around the world to process millions of daily credit card transactions. Due to the nature of their business, their IT team needed to strike the perfect balance between meeting regulatory-mandated data security measures with the lowest possible levels of latency and …