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:

  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, and so forth).

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

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

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

  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 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 such as 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. 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.
  8. 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!



Downloads

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • When it comes to desktops – physical or virtual – it's all about the applications. Cloud-hosted virtual desktops are growing fast because you get local data center-class security and 24x7 access with the complete personalization and flexibility of your own desktop. Organizations make five common mistakes when it comes to planning and implementing their application management strategy. This eBook tells you what they are and how to avoid them, and offers real-life case studies on customers who didn't let …

  • The latest release of SugarCRM's flagship product gives users new tools to build extraordinary customer relationships. Read an in-depth analysis of SugarCRM's enhanced ability to help companies execute their customer-facing initiatives from Ovum, a leading technology research firm.

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds