Contents

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.

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.

XmlSS.NET Spreadsheet Component

Purpose

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

1. 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.
2. 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.
3. 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:

Public Function GetCell(ByVal rowIndex As Integer, 
ByVal colIndex As Integer) As Cell
            Dim cell As cell = Item(rowIndex, colIndex)
            If cell Is Nothing Then
                cell = New cell(rowIndex, colIndex, _worksheet)
                _items.Add(cell.GetIndex(rowIndex, colIndex), cell)
            End If
            Return cell
End Function
Default Public ReadOnly Property Item(
ByVal rowIndex As Integer, 
ByVal colIndex As Integer) As Cell
            Get
                Row.AssertValidIndex(rowIndex, _worksheet)
                Column.AssertValidIndex(colIndex, _worksheet)
                Return DirectCast(_items(Cell.GetIndex(rowIndex, colIndex)), Cell)
            End Get
End Property

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.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:

Protected Sub Subscribe()
  AddHandler _worksheet.ExpandedRowCountChanged, 
  AddressOf Worksheet_OnExpandedRowCountChanged
  AddHandler _worksheet.ExpandedColumnCountChanged, 
  AddressOf Worksheet_OnExpandedColumnCountChanged
  AddHandler _worksheet.TopRowVisibleChanged, 
  AddressOf Worksheet_OnTopRowVisibleChanged
  AddHandler _worksheet.LeftColumnVisibleChanged, 
  AddressOf Worksheet_OnLeftColumnVisibleChanged
  AddHandler _worksheet.DefaultRowHeightChanged, 
  AddressOf Worksheet_OnDefaultRowHeightChanged
  AddHandler _worksheet.DefaultColumnWidthChanged, 
  AddressOf Worksheet_OnDefaultColumnWidthChanged
  AddHandler _worksheet.DisplayGridlinesChanged, 
  AddressOf Worksheet_OnDisplayGridlinesChanged
  AddHandler _worksheet.DisplayRowHeadersChanged, 
  AddressOf Worksheet_OnDisplayRowHeadersChanged
  AddHandler _worksheet.DisplayColumnHeadersChanged, 
  AddressOf Worksheet_OnDisplayColumnHeadersChanged
  AddHandler _worksheet.ActiveCellChanged, 
  AddressOf Worksheet_OnActiveCellChanged
  AddHandler _worksheet.RangeSelectionChanged, 
  AddressOf Worksheet_OnRangeSelectionChanged
  AddHandler _worksheet.ColumnWidthChanged, 
  AddressOf Worksheet_OnColumnWidthChanged
  AddHandler _worksheet.RowHeightChanged, 
  AddressOf Worksheet_OnRowHeightChanged
  AddHandler _worksheet.StyleChanged, 
  AddressOf Worksheet_OnStyleChanged
  AddHandler _worksheet.ColumnHiddenChanged, 
  AddressOf Worksheet_OnColumnHiddenChanged
  AddHandler _worksheet.ColumnStyleChanged, 
  AddressOf Worksheet_OnColumnStyleChanged
  AddHandler _worksheet.RowStyleChanged, 
  AddressOf Worksheet_OnRowStyleChanged
  AddHandler _worksheet.RowHiddenChanged, 
  AddressOf Worksheet_OnRowHiddenChanged
  AddHandler _worksheet.ReadOnlyChanged, 
  AddressOf Worksheet_OnReadOnlyChanged
  AddHandler _worksheet.CellStyleChanged, 
  AddressOf Worksheet_OnCellStyleChanged
  AddHandler _worksheet.CellValueChanged, 
  AddressOf Worksheet_OnCellValueChanged
  AddHandler _worksheet.CellMergeChanged, 
  AddressOf Worksheet_OnCellMergeChanged
  AddHandler _worksheet.CellReadOnlyChanged, 
  AddressOf Worksheet_OnCellReadOnlyChanged
  AddHandler _worksheet.CellTextInflowChanged, 
  AddressOf Worksheet_OnCellTextInflowChanged
End Sub

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:

Protected Overrides Sub OnPaint(ByVal e As System.Windows.Forms.PaintEventArgs)
            If _isUpdating Then Return
            DrawBackground(e)
            DrawColumnHeaders(e)
            DrawRowHeaders(e)
            DrawWorksheetSelectArea(e)
            DrawCells(e)
            DrawRangeSelection(e)
            DrawActiveCellBorder(e)
            MyBase.OnPaint(e)
End Sub

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:

Public Shared Function Calculate (ByVal cell As Cell, 
          ByVal hfont As IntPtr) As CellSpan
    If cell.IsEmpty Then Return Nothing
    Dim rowIndex As Integer = cell.RowIndex
    Dim colIndex As Integer = cell.ColumnIndex
    Dim worksheet As worksheet = cell.Worksheet
    Dim style As style = cell.GetStyle(rowIndex, 
            colIndex, 
            worksheet)
    Dim alignment As alignment = style.Alignment
    If alignment.WrapText OrElse 
  cell.MergeAcross > 0 OrElse 
  cell.MergeDown > 0 Then
        Return New CellSpan(cell, 0, 0, style)
    End If
    Dim hAlign As alignment.HorizontalAlignment = alignment.Horizontal
    Select Case hAlign
        Case alignment.HorizontalAlignment.CenterAcrossSelection, 
    alignment.HorizontalAlignment.Distributed, _
                alignment.HorizontalAlignment.Fill, 
                alignment.HorizontalAlignment.Justify
            Return New CellSpan(cell, 0, 0, style)
    End Select
    Dim textSize As Size = MeasureString.GetStringSize(cell.Text, hfont)
    Dim dataType As cell.CellDataType =