XmlSS.NET Spreadsheet Component

• Download source and demo solution - 101 Kb

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 run down 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:

1. MVC (Model-View-Controller) Architecture (http://www.jdl.co.uk/briefings/MVC.pdf, http://st-www.cs.uiuc.edu/users/smarch/st-docs/mvc.html)
2. GOF Design Patterns (Design Patterns, Elements of Reusable Object-Oriented Software by Erich Gamma, Richard Helm, Ralph Johnson, and John Vlissides, ISBN: 0201633612), especially those typically involved with MVC.
3. XML, XML Schema
4. Windows Forms Control Development
5. Excel Spreadsheet Model & Concepts (workbook, worksheets, rows, columns, cells, formulas, formatting styles, GUI, etc…).

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 exposes at a very fine level of detail almost all the features available in an Excel workbook document, from raw worksheet table data, including formulas, all the way to the specifics regarding the format and location of the active cell at time of persistence. The only Excel features that I know of that are not included are VBA and ActiveX add-ins. For an in depth look at XMLSS I highly recommend that you take a look at the official reference page (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_xmlss.asp). Here you will find thorough but not exhaustive coverage of the schema in a convenient reference style manner. Also, http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_xlsmlinss.asp has some useful information as well.

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:

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 in order to initialize the model; instead the model should be completely independent of the manner by which it is constructed (e.g. DataTable, DataReader, Text File, Proprietary XML or Binary File, etc…).

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, type XmlSS.Model.Worksheet has properties ExpandedRowCount and ExpandedColumnCount, 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 setup 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, types XmlSS.Model.RowCollection, XmlSS.Model.ColumnCollection, and XmlSS.Model.CellCollection create objects on the fly via methods GetRow, GetColumn, and GetCell, 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. In order to obtain an instance that has already been created each collection exposes a default Item property which will return null if the object does not exist, otherwise, a previously created instance. Calling Item can safely be done while enumerating the collection since 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, type XmlSS.Model.Style 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, for 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 it 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. Since 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 are 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 on screen 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 explain thoroughly control drawing techniques; therefore, I myself am not going to do any elaboration on this matter. Suffice to say that you paint as quickly as possible only what needs to be painted and all painting must be carried out within OnPaint. Since 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, currently it 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, etc…) 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 = cell.DataType
    Dim widthDiff As Integer = textSize.Width -
            Column.GetWidth(colIndex, worksheet)
    Dim rightDiff, leftDiff As Integer
    If hAlign = alignment.HorizontalAlignment.Center OrElse (
hAlign = alignment.HorizontalAlignment.Automatic AndAlso (dataType =
        cell.CellDataType.Boolean OrElse dataType =
            cell.CellDataType.Error)) Then
 rightDiff = widthDiff \ 2
 leftDiff = rightDiff
    ElseIf hAlign = alignment.HorizontalAlignment.Right OrElse
    (hAlign = alignment.HorizontalAlignment.Automatic AndAlso
    (dataType = cell.CellDataType.Number OrElse dataType =
     cell.CellDataType.DateTime)) Then
 leftDiff = widthDiff + (WorksheetView.CELL_XY_PAD * 4) + (
 alignment.Indent * WorksheetView.CELL_INDENT_SIZE)
    Else
 rightDiff = widthDiff + WorksheetView.CELL_XY_PAD + (
 alignment.Indent * WorksheetView.CELL_INDENT_SIZE)
    End If
    Dim cells As CellCollection = worksheet.Cells
    Dim mergedCells As MergedCellCollection = worksheet.MergedCells
    Dim rightColIndex As Integer = colIndex + 1
    Dim colCount As Integer = worksheet.ExpandedColumnCount
    Dim right As Integer
    While rightDiff > 0 AndAlso rightColIndex < colCount
 Dim rightCell As cell = cells(rowIndex, rightColIndex)
 If Not rightCell Is Nothing Then
     If Not (rightCell.IsEmpty AndAlso rightCell.MergedTo Is Nothing
      AndAlso rightCell.MergeAcross = 0 AndAlso rightCell.MergeDown = 0
      AndAlso rightCell.TextInflow) Then
  Exit While
     End If
     rightDiff -= rightCell.Width
 ElseIf Not mergedCells.GetMergedTo(rowIndex, rightColIndex)
  Is Nothing Then
     Exit While
 Else
     rightDiff -= Column.GetWidth(rightColIndex, worksheet)
 End If
 right += 1
 rightColIndex += 1
    End While
    Dim leftColIndex As Integer = colIndex - 1
    Dim left As Integer
    While leftDiff > 0 AndAlso leftColIndex >= 0
 Dim leftCell As cell = cells(rowIndex, leftColIndex)
 If Not leftCell Is Nothing Then
     If Not (leftCell.IsEmpty AndAlso leftCell.MergedTo Is
       Nothing AndAlso leftCell.MergeDown = 0 AndAlso
        leftCell.TextInflow) Then
  Exit While
     End If
     leftDiff -= leftCell.Width
 ElseIf Not mergedCells.GetMergedTo(rowIndex, leftColIndex) Is Nothing Then
     Exit While
 Else
     leftDiff -= Column.GetWidth(leftColIndex, worksheet)
 End If
 left += 1
 leftColIndex -= 1
    End While
    Return New CellSpan(cell, left, right, style)
End Function

You can always 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 should then 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.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, however, just as easily load the workbook inside a button’s click event handler, if you prefer that route. TheIWorkbookFactory 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:

 Public Function CreateWorkboook() As Workbook 
  Implements IWorkbookFactory.CreateWorkboook
     Dim rootNav As XPathNavigator = New XPathDocument(_file,
       XmlSpace.Preserve).CreateNavigator()
     Dim nsMgr As New XmlNamespaceManager(rootNav.NameTable)
     nsMgr.AddNamespace(SPREADSHEET_NS_PREFIX, SPREADSHEET_NS)
     nsMgr.AddNamespace(EXCEL_NS_PREFIX, EXCEL_NS)
     Dim xpath As XPathExpression = rootNav.Compile("ss:Workbook")
     xpath.SetContext(nsMgr)
     Dim wbNode As XPathNodeIterator = rootNav.Select(xpath)
     wbNode.MoveNext()
     Dim wbNav As XPathNavigator = wbNode.Current
     Dim workbook As New workbook
     xpath = wbNav.Compile("ss:Styles/ss:Style")
     xpath.SetContext(nsMgr)
     Dim styleNodes As XPathNodeIterator = wbNav.Select(xpath)
     Dim styles As New Hashtable
     Dim protectedStyles As New Hashtable
     If styleNodes.Count > 0 Then
  While styleNodes.MoveNext()
      addStyle(styleNodes.Current, nsMgr, workbook, styles, protectedStyles)
  End While
     Else
  styles.Add(DEFAULT_STYLE_ID, workbook.Style)
     End If
     xpath = wbNav.Compile("ss:Worksheet")
     xpath.SetContext(nsMgr)
     Dim worksheetNodes As XPathNodeIterator = wbNav.Select(xpath)
     Dim worksheets As WorksheetCollection = workbook.Worksheets
     Dim worksheet As worksheet = worksheets(0)
     While worksheetNodes.MoveNext()
  If worksheet Is Nothing Then
      worksheet = New worksheet(workbook)
      updateWorksheet(worksheet, worksheetNodes.Current, nsMgr, 
workbook, styles)
      worksheets.Add(worksheet)
  Else
      updateWorksheet(worksheet, worksheetNodes.Current, nsMgr, 
workbook, styles)
  End If
  worksheet = Nothing
     End While
     xpath = wbNav.Compile("x:ExcelWorkbook")
     xpath.SetContext(nsMgr)
     wbNode = wbNav.Select(xpath)
     If wbNode.MoveNext() Then
  wbNav = wbNode.Current
  xpath = wbNav.Compile("x:HideWorkbookTabs")
  xpath.SetContext(nsMgr)
  workbook.TabStripVisible = Not wbNav.Select(xpath).MoveNext()
  xpath = wbNav.Compile("x:HideHorizontalScrollBar")
  xpath.SetContext(nsMgr)
  workbook.HScrollVisble = Not wbNav.Select(xpath).MoveNext()
  xpath = wbNav.Compile("x:HideVerticalScrollBar")
  xpath.SetContext(nsMgr)
  workbook.VScrollVisible = Not wbNav.Select(xpath).MoveNext()
  xpath = wbNav.Compile("x:ActiveSheet")
  xpath.SetContext(nsMgr)
  Dim activeSheetNode As XPathNodeIterator = wbNav.Select(xpath)
  If activeSheetNode.MoveNext() Then
      workbook.ActiveWorksheet = worksheets(Integer.Parse(
         activeSheetNode.Current.Value))
  End If
     End If
     For Each worksheet In workbook.Worksheets
  If worksheet.ReadOnly Then
      For Each cell As cell In worksheet.Cells
   cell.ReadOnly = Not cell.Style Is Nothing AndAlso 
       protectedStyles.ContainsKey(cell.Style)
      Next
  End If
     Next
     Return workbook
 End Function

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 arouse out of my attempts to improve the performance of the component, since the original implementation that relied on type System.Collections.Hashtable suffered the performance/memory blow associated with constant boxing. Since 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) in order to create my own. Types IntKeyHashtable and Hashtable 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 must replace 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.

How To Use 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:

1. 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.
2. 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.
3. The Excel style UI provided by the WorksheetView control.

Also, the demo makes use of the PropertyGrid control in order 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

1. 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.
2. Currently WorksheetView only supports about 85% of style characteristics. Formatting features like double borders are not currently supported. I don’t have any intention on doing anything else with WorksheetView except fix bugs.
3. 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.
4. Type Style should probably implement IDisposable, but currently it does not.
5. 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.
6. Currently the component has no error handling. Big no, no!
7. A while back ago Code Project asked the question: What is your worst programming sin? Well, you’ll notice right away just by looking at the code that my worst programming sin is my total inability to write comments. I’m certainly lucky that I haven’t been fired for this. My sincere apologies
8. If you have any questions or comments, good or bad, about the component, please post them or feel free to email 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, which 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!