WEBINAR: On-demand webcast
How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >
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
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).
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.
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.
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.