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:
- 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)
- 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.
- XML, XML Schema
- Windows Forms Control Development
- 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:
- A typical workbook model. That is, a series of types that represent a workbook and its composition. Typically, a workbook has, among many other things, a collection of worksheets. Furthermore, a worksheet has, among many other things, a collection of rows, columns, and cells. The workbook model exposed by this component is no different.
- An Excel style view of and control over the worksheet model, one that is completely independent of the model itself and, furthermore, of any additional views the model may have observing it.
- The ability to initialize the workbook model though not necessarily through XMLSS but rather by means of any appropriate strategy. In other words, an XMLSS document instance shouldn’t be required to initialize the model; instead, the model should be completely independent of the manner by which it is constructed (for example, DataTable, DataReader, Text File, Proprietary XML or Binary File, and so on).
Design Architecture
XmlSS.NET has MVC (Model-View-Controller) as its underlying architecture. There are numerous resources that go into great detail explaining the ins and outs of this architecture and derivatives thereof. Therefore, there’s no need for me to do so here, and even if there was, I don’t consider myself qualified to detail what has been for a long time and continues to be an excellent architecture for building reusable software. If you are not familiar with MVC, Google it and inform yourself. I guarantee you will not be wasting your time.
Implementation
The XmlSS.NET spreadsheet component is organized into four namespaces, all of which lie within the root namespace, XmlSS. These four namespaces are: XmlSS.Model, XmlSS.View, XmlSS.Factory, and XmlSS.Utilities.
XmlSS.Model
In the XmlSS.Model namespace are all the types that correspond to the model aspect of the component. As I have already mentioned, the component’s workbook model is heavily based on XMLSS; specifically, the object model and the latter’s DOM are very similar. As it stands, the model is far from complete, simply because its current state does not handle formulas or defined names, two aspects that are, or at least should be, represented by any half decent spreadsheet model implementation, including this one. The only three points I am going to very briefly discuss regarding this component’s workbook model are 1) how the row, column, and cell collections behave, 2) the efficient use of styling, and 3) how the model informs its observers, if any, that it’s been changed somehow. Pardon the brevity, but I assume you are familiar with the basics of any spreadsheet/workbook model, perhaps the one exposed by Excel. If you have no idea, which I doubt, what a spreadsheet is or what one commonly looks like, you probably have some kind of spreadsheet software on your machine that will show you.
First, the XmlSS.Model.Worksheet type has ExpandedRowCount and ExpandedColumnCount properties, both of which will accept any positive integer value. Furthermore, together these properties define the bounds of the worksheet’s table data. However, just because a worksheet instance can be set up to have a high number of rows, columns, and, thereby, cells, it certainly doesn’t mean you need an instance in memory for each one of them. On the contrary, only a non-default intrinsic state warrants that additional instance. To handle this, the XmlSS.Model.RowCollection, XmlSS.Model.ColumnCollection, and XmlSS.Model.CellCollection types create objects on the fly via the GetRow, GetColumn, and GetCell methods, respectively. If the instance exists, it is returned immediately; otherwise, it is first created and then stored in the collection before it is returned. Given this behavior, be careful not to call these methods while the respective collection is being enumerated unless you’re sure the instance exists; otherwise, the underlying collection may be modified, resulting in an exception being thrown. Moreover, make sure you call these methods only if you actually need an instance to be created; otherwise, you may end up with unnecessary memory consumption, whether large or small. To obtain an instance that has already been created, each collection exposes a default Item property that will return null if the object does not exist; otherwise, a previously created instance. Calling Item can safely be done while enumerating the collection because it does not modify it. Here’s what GetCell and Item look like for CellCollection:
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 FunctionDefault 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.ModelPublic 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 BorderPublic 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 InteriorPublic Sub New(ByVal workbook As Workbook)
Me.New(workbook, Nothing, Color.Empty, Nothing, Nothing, _
Nothing, Nothing, Nothing, Nothing, Nothing, _
Nothing, Nothing)
End SubPublic 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 SubPublic ReadOnly Property Workbook() As Workbook
Get
Return _workbook
End Get
End PropertyPublic 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 PropertyPublic 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 PropertyPublic 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 PropertyPublic 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 PropertyPublic 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 PropertyPublic 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 PropertyPublic 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 PropertyPublic 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 PropertyPublic 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 PropertyPublic 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 PropertyPublic 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 PropertyEnd 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.
