Working in virtual mode (VB.Net / netframework)

Note

This demo is available in your FlexCel installation at <FlexCel Install Folder>\samples\vb\VS2022\netframework\10.API\22.Virtual Mode and also at https://github.com/tmssoftware/TMS-FlexCel.NET-demos/tree/master/vb/VS2022/netframework/Modules/10.API/22.Virtual Mode

Overview

This example shows how to read or write Excel files without fully loading them in memory. See the FlexCel Performance Guide for more information in how Virtual Mode works.

Concepts

How to read xls, xlsx or txt files without loading them into memory.

Files

AssemblyInfo.vb

Imports System.Reflection
Imports System.Runtime.CompilerServices

'
' General Information about an assembly is controlled through the following 
' set of attributes. Change these attribute values to modify the information
' associated with an assembly.
'
<Assembly: AssemblyTitle("")>
<Assembly: AssemblyDescription("")>
<Assembly: AssemblyConfiguration("")>
<Assembly: AssemblyCompany("")>
<Assembly: AssemblyProduct("")>
<Assembly: AssemblyCopyright("(c) 2002 - 2014 TMS Software")>
<Assembly: AssemblyTrademark("")>
<Assembly: AssemblyCulture("")>

'
' Version information for an assembly consists of the following four values:
'
'      Major Version
'      Minor Version 
'      Build Number
'      Revision
'
' You can specify all the values or you can default the Revision and Build Numbers 
' by using the '*' as shown below:

<Assembly: AssemblyVersion("6.2.1.0")>

'
' In order to sign your assembly you must specify a key to use. Refer to the 
' Microsoft .NET Framework documentation for more information on assembly signing.
'
' Use the attributes below to control which key is used for signing. 
'
' Notes: 
'   (*) If no key is specified, the assembly is not signed.
'   (*) KeyName refers to a key that has been installed in the Crypto Service
'       Provider (CSP) on your machine. KeyFile refers to a file which contains
'       a key.
'   (*) If the KeyFile and the KeyName values are both specified, the 
'       following processing occurs:
'       (1) If the KeyName can be found in the CSP, that key is used.
'       (2) If the KeyName does not exist and the KeyFile does exist, the key 
'           in the KeyFile is installed into the CSP and used.
'   (*) In order to create a KeyFile, you can use the sn.exe (Strong Name) utility.
'       When specifying the KeyFile, the location of the KeyFile should be
'       relative to the project output directory which is
'       %Project Directory%\obj\<configuration>. For example, if your KeyFile is
'       located in the project directory, you would specify the AssemblyKeyFile 
'       attribute as [assembly: AssemblyKeyFile("..\\..\\mykey.snk")]
'   (*) Delay Signing is an advanced option - see the Microsoft .NET Framework
'       documentation for more information on this.
'
<Assembly: AssemblyDelaySign(False)>
<Assembly: AssemblyKeyFile("")>
<Assembly: AssemblyKeyName("")>

CellReader.vb

Imports System.Text
Imports FlexCel.Core

Namespace VirtualMode
	'A simple cell reader that will get the values from FlexCel and put them into a grid.
	Friend Class CellReader
		Private Only50Rows As Boolean
		Private CellData As SparseCellArray
		Private FormatValues As Boolean
		Private SheetToRead As Integer
		Public StartSheetSelect As Date
		Public EndSheetSelect As Date

		Public Sub New(ByVal aOnly50Rows As Boolean, ByVal aCellData As SparseCellArray, ByVal aFormatValues As Boolean)
			Only50Rows = aOnly50Rows
			CellData = aCellData
			FormatValues = aFormatValues
		End Sub

		Public Sub OnStartReading(ByVal sender As Object, ByVal e As VirtualCellStartReadingEventArgs)
			StartSheetSelect = Date.Now
			Using SheetSelector As New SheetSelectorForm(e.SheetNames)

				If Not SheetSelector.Execute() Then
					EndSheetSelect = Date.Now
					e.NextSheet = Nothing 'stop reading
					Return
				End If

				EndSheetSelect = Date.Now
				e.NextSheet = SheetSelector.SelectedSheet
				SheetToRead = SheetSelector.SelectedSheetIndex + 1
			End Using
		End Sub

		Public Sub OnCellRead(ByVal sender As Object, ByVal e As VirtualCellReadEventArgs)
			If Only50Rows AndAlso e.Cell.Row > 50 Then
				e.NextSheet = Nothing 'Stop reading all sheets.
				Return
			End If

			If e.Cell.Sheet <> SheetToRead Then
				e.NextSheet = Nothing 'Stop reading all sheets.
				Return
			End If

			If FormatValues Then
				Dim Clr As TUIColor = Color.Empty
				CellData.AddValue(e.Cell.Row, e.Cell.Col, TFlxNumberFormat.FormatValue(e.Cell.Value, CType(sender, ExcelFile).GetFormat(e.Cell.XF).Format, Clr, (CType(sender, ExcelFile))))
			Else
				CellData.AddValue(e.Cell.Row, e.Cell.Col, Convert.ToString(e.Cell.Value))
			End If
		End Sub
	End Class
End Namespace

Form1.Designer.vb

Imports System.Collections
Imports System.ComponentModel
Imports FlexCel.Core
Imports FlexCel.XlsAdapter
Namespace VirtualMode
	Partial Public Class mainForm
		Inherits System.Windows.Forms.Form

		Private openFileDialog1 As System.Windows.Forms.OpenFileDialog
		Private panel2 As System.Windows.Forms.Panel
		Private statusBar As System.Windows.Forms.StatusBar
		Private components As System.ComponentModel.IContainer = Nothing

		''' <summary>
		''' Clean up any resources being used.
		''' </summary>
		Protected Overrides Sub Dispose(ByVal disposing As Boolean)
			If disposing Then
				If components IsNot Nothing Then
					components.Dispose()
				End If
			End If
			MyBase.Dispose(disposing)
		End Sub

		#Region "Windows Form Designer generated code"
		''' <summary>
		''' Required method for Designer support - do not modify
		''' the contents of this method with the code editor.
		''' </summary>
		Private Sub InitializeComponent()
			Dim dataGridViewCellStyle1 As New System.Windows.Forms.DataGridViewCellStyle()
			Dim resources As New System.ComponentModel.ComponentResourceManager(GetType(mainForm))
			Me.openFileDialog1 = New System.Windows.Forms.OpenFileDialog()
			Me.panel2 = New System.Windows.Forms.Panel()
			Me.cbFormatValues = New System.Windows.Forms.CheckBox()
			Me.cbIgnoreFormulaText = New System.Windows.Forms.CheckBox()
			Me.cbFirst50Rows = New System.Windows.Forms.CheckBox()
			Me.statusBar = New System.Windows.Forms.StatusBar()
			Me.DisplayGrid = New System.Windows.Forms.DataGridView()
			Me.GridCaptionPanel = New System.Windows.Forms.Panel()
			Me.GridCaption = New System.Windows.Forms.Label()
			Me.mainToolbar = New System.Windows.Forms.ToolStrip()
			Me.toolStripSeparator1 = New System.Windows.Forms.ToolStripSeparator()
			Me.btnOpenFile = New System.Windows.Forms.ToolStripButton()
			Me.btnExit = New System.Windows.Forms.ToolStripButton()
			Me.btnInfo = New System.Windows.Forms.ToolStripButton()
			Me.panel2.SuspendLayout()
			CType(Me.DisplayGrid, System.ComponentModel.ISupportInitialize).BeginInit()
			Me.GridCaptionPanel.SuspendLayout()
			Me.mainToolbar.SuspendLayout()
			Me.SuspendLayout()
			' 
			' openFileDialog1
			' 
			Me.openFileDialog1.DefaultExt = "xls"
			Me.openFileDialog1.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm|Excel 97/2003|*.xls|Excel 2007|*.xlsx;*.xlsm|All " & "files|*.*"
			Me.openFileDialog1.Title = "Open an Excel File"
			' 
			' panel2
			' 
			Me.panel2.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
			Me.panel2.Controls.Add(Me.cbFormatValues)
			Me.panel2.Controls.Add(Me.cbIgnoreFormulaText)
			Me.panel2.Controls.Add(Me.cbFirst50Rows)
			Me.panel2.Dock = System.Windows.Forms.DockStyle.Top
			Me.panel2.Location = New System.Drawing.Point(0, 38)
			Me.panel2.Name = "panel2"
			Me.panel2.Size = New System.Drawing.Size(880, 25)
			Me.panel2.TabIndex = 4
			' 
			' cbFormatValues
			' 
			Me.cbFormatValues.AutoSize = True
			Me.cbFormatValues.Location = New System.Drawing.Point(269, 5)
			Me.cbFormatValues.Name = "cbFormatValues"
			Me.cbFormatValues.Size = New System.Drawing.Size(131, 17)
			Me.cbFormatValues.TabIndex = 2
			Me.cbFormatValues.Text = "Format values (slower)"
			Me.cbFormatValues.UseVisualStyleBackColor = True
			' 
			' cbIgnoreFormulaText
			' 
			Me.cbIgnoreFormulaText.AutoSize = True
			Me.cbIgnoreFormulaText.Checked = True
			Me.cbIgnoreFormulaText.CheckState = System.Windows.Forms.CheckState.Checked
			Me.cbIgnoreFormulaText.Location = New System.Drawing.Point(150, 5)
			Me.cbIgnoreFormulaText.Name = "cbIgnoreFormulaText"
			Me.cbIgnoreFormulaText.Size = New System.Drawing.Size(113, 17)
			Me.cbIgnoreFormulaText.TabIndex = 1
			Me.cbIgnoreFormulaText.Text = "Ignore formula text"
			Me.cbIgnoreFormulaText.UseVisualStyleBackColor = True
			' 
			' cbFirst50Rows
			' 
			Me.cbFirst50Rows.AutoSize = True
			Me.cbFirst50Rows.Checked = True
			Me.cbFirst50Rows.CheckState = System.Windows.Forms.CheckState.Checked
			Me.cbFirst50Rows.Location = New System.Drawing.Point(11, 5)
			Me.cbFirst50Rows.Name = "cbFirst50Rows"
			Me.cbFirst50Rows.Size = New System.Drawing.Size(133, 17)
			Me.cbFirst50Rows.TabIndex = 0
			Me.cbFirst50Rows.Text = "Read only first 50 rows"
			Me.cbFirst50Rows.UseVisualStyleBackColor = True
			' 
			' statusBar
			' 
			Me.statusBar.Location = New System.Drawing.Point(0, 439)
			Me.statusBar.Name = "statusBar"
			Me.statusBar.Size = New System.Drawing.Size(880, 22)
			Me.statusBar.TabIndex = 7
			' 
			' DisplayGrid
			' 
			Me.DisplayGrid.AllowUserToAddRows = False
			Me.DisplayGrid.AllowUserToDeleteRows = False
			dataGridViewCellStyle1.Alignment = System.Windows.Forms.DataGridViewContentAlignment.MiddleCenter
			dataGridViewCellStyle1.BackColor = System.Drawing.SystemColors.Control
			dataGridViewCellStyle1.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, (CByte(0)))
			dataGridViewCellStyle1.ForeColor = System.Drawing.SystemColors.WindowText
			dataGridViewCellStyle1.SelectionBackColor = System.Drawing.SystemColors.Highlight
			dataGridViewCellStyle1.SelectionForeColor = System.Drawing.SystemColors.HighlightText
			dataGridViewCellStyle1.WrapMode = System.Windows.Forms.DataGridViewTriState.True
			Me.DisplayGrid.ColumnHeadersDefaultCellStyle = dataGridViewCellStyle1
			Me.DisplayGrid.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize
			Me.DisplayGrid.Dock = System.Windows.Forms.DockStyle.Fill
			Me.DisplayGrid.Location = New System.Drawing.Point(0, 86)
			Me.DisplayGrid.Name = "DisplayGrid"
			Me.DisplayGrid.ReadOnly = True
			Me.DisplayGrid.Size = New System.Drawing.Size(880, 353)
			Me.DisplayGrid.TabIndex = 8
			Me.DisplayGrid.VirtualMode = True
'			Me.DisplayGrid.CellValueNeeded += New System.Windows.Forms.DataGridViewCellValueEventHandler(Me.DisplayGrid_CellValueNeeded)
'			Me.DisplayGrid.RowPostPaint += New System.Windows.Forms.DataGridViewRowPostPaintEventHandler(Me.DisplayGrid_RowPostPaint)
			' 
			' GridCaptionPanel
			' 
			Me.GridCaptionPanel.BackColor = System.Drawing.SystemColors.ActiveCaption
			Me.GridCaptionPanel.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
			Me.GridCaptionPanel.Controls.Add(Me.GridCaption)
			Me.GridCaptionPanel.Dock = System.Windows.Forms.DockStyle.Top
			Me.GridCaptionPanel.ForeColor = System.Drawing.SystemColors.ActiveCaptionText
			Me.GridCaptionPanel.Location = New System.Drawing.Point(0, 63)
			Me.GridCaptionPanel.Name = "GridCaptionPanel"
			Me.GridCaptionPanel.Size = New System.Drawing.Size(880, 23)
			Me.GridCaptionPanel.TabIndex = 9
			' 
			' GridCaption
			' 
			Me.GridCaption.AutoSize = True
			Me.GridCaption.Location = New System.Drawing.Point(13, 6)
			Me.GridCaption.Name = "GridCaption"
			Me.GridCaption.Size = New System.Drawing.Size(0, 13)
			Me.GridCaption.TabIndex = 0
			' 
			' mainToolbar
			' 
			Me.mainToolbar.Items.AddRange(New System.Windows.Forms.ToolStripItem() { Me.btnOpenFile, Me.toolStripSeparator1, Me.btnExit, Me.btnInfo})
			Me.mainToolbar.Location = New System.Drawing.Point(0, 0)
			Me.mainToolbar.Name = "mainToolbar"
			Me.mainToolbar.Size = New System.Drawing.Size(880, 38)
			Me.mainToolbar.TabIndex = 10
			Me.mainToolbar.Text = "toolStrip1"
			' 
			' toolStripSeparator1
			' 
			Me.toolStripSeparator1.Name = "toolStripSeparator1"
			Me.toolStripSeparator1.Size = New System.Drawing.Size(6, 38)
			' 
			' btnOpenFile
			' 
			Me.btnOpenFile.Image = (CType(resources.GetObject("btnOpenFile.Image"), System.Drawing.Image))
			Me.btnOpenFile.ImageTransparentColor = System.Drawing.Color.Magenta
			Me.btnOpenFile.Name = "btnOpenFile"
			Me.btnOpenFile.Size = New System.Drawing.Size(59, 35)
			Me.btnOpenFile.Text = "Open file"
			Me.btnOpenFile.TextImageRelation = System.Windows.Forms.TextImageRelation.ImageAboveText
'			Me.btnOpenFile.Click += New System.EventHandler(Me.btnOpenFile_Click)
			' 
			' btnExit
			' 
			Me.btnExit.Alignment = System.Windows.Forms.ToolStripItemAlignment.Right
			Me.btnExit.Image = (CType(resources.GetObject("btnExit.Image"), System.Drawing.Image))
			Me.btnExit.ImageTransparentColor = System.Drawing.Color.Magenta
			Me.btnExit.Name = "btnExit"
			Me.btnExit.Size = New System.Drawing.Size(59, 35)
			Me.btnExit.Text = "     E&xit     "
			Me.btnExit.TextImageRelation = System.Windows.Forms.TextImageRelation.ImageAboveText
'			Me.btnExit.Click += New System.EventHandler(Me.btnExit_Click)
			' 
			' btnInfo
			' 
			Me.btnInfo.Alignment = System.Windows.Forms.ToolStripItemAlignment.Right
			Me.btnInfo.Image = (CType(resources.GetObject("btnInfo.Image"), System.Drawing.Image))
			Me.btnInfo.ImageTransparentColor = System.Drawing.Color.Magenta
			Me.btnInfo.Name = "btnInfo"
			Me.btnInfo.Size = New System.Drawing.Size(74, 35)
			Me.btnInfo.Text = "Information"
			Me.btnInfo.TextImageRelation = System.Windows.Forms.TextImageRelation.ImageAboveText
'			Me.btnInfo.Click += New System.EventHandler(Me.btnInfo_Click)
			' 
			' mainForm
			' 
			Me.AutoScaleDimensions = New System.Drawing.SizeF(6F, 13F)
			Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
			Me.ClientSize = New System.Drawing.Size(880, 461)
			Me.Controls.Add(Me.DisplayGrid)
			Me.Controls.Add(Me.GridCaptionPanel)
			Me.Controls.Add(Me.panel2)
			Me.Controls.Add(Me.statusBar)
			Me.Controls.Add(Me.mainToolbar)
			Me.Name = "mainForm"
			Me.Text = "Virtual Mode Example - Cells are not stored in memory"
			Me.panel2.ResumeLayout(False)
			Me.panel2.PerformLayout()
			CType(Me.DisplayGrid, System.ComponentModel.ISupportInitialize).EndInit()
			Me.GridCaptionPanel.ResumeLayout(False)
			Me.GridCaptionPanel.PerformLayout()
			Me.mainToolbar.ResumeLayout(False)
			Me.mainToolbar.PerformLayout()
			Me.ResumeLayout(False)
			Me.PerformLayout()

		End Sub
		#End Region

		Private WithEvents DisplayGrid As DataGridView
		Private GridCaptionPanel As Panel
		Private GridCaption As Label
		Private mainToolbar As ToolStrip
		Private WithEvents btnOpenFile As ToolStripButton
		Private toolStripSeparator1 As ToolStripSeparator
		Private WithEvents btnInfo As ToolStripButton
		Private WithEvents btnExit As ToolStripButton
		Private cbFirst50Rows As CheckBox
		Private cbIgnoreFormulaText As CheckBox
		Private cbFormatValues As CheckBox
	End Class
End Namespace

Form1.vb

Imports System.Collections
Imports System.ComponentModel

Imports FlexCel.Core
Imports FlexCel.XlsAdapter

Namespace VirtualMode
	''' <summary>
	''' A demo on how to read a file from FlexCel and display the results.
	''' </summary>
	Partial Public Class mainForm
		Inherits System.Windows.Forms.Form

		Private CellData As SparseCellArray 'we will store the data here. This is an example, in real world you would use "Virtual mode" to load the cells into your own structures.

		Public Sub New()
			InitializeComponent()
			ResizeToolbar(mainToolbar)
		End Sub

		Private Sub ResizeToolbar(ByVal toolbar As ToolStrip)

			Using gr As Graphics = CreateGraphics()
				Dim xFactor As Double = gr.DpiX / 96.0
				Dim yFactor As Double = gr.DpiY / 96.0
				toolbar.ImageScalingSize = New Size(CInt(Fix(24 * xFactor)), CInt(Fix(24 * yFactor)))
				toolbar.Width = 0 'force a recalc of the buttons.
			End Using
		End Sub

		Private Sub btnExit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExit.Click
			Close()
		End Sub

		Private Sub btnInfo_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnInfo.Click
			MessageBox.Show("This demo shows how to read the contents of an xls file without loading the file in memory." & vbLf & "We will first load the sheet names in the file, then open just a single sheet, and read all or just the 50 first rows of it.")
		End Sub


		Private Sub btnOpenFile_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnOpenFile.Click
			If openFileDialog1.ShowDialog() <> System.Windows.Forms.DialogResult.OK Then
				Return
			End If
			ImportFile(openFileDialog1.FileName)
		End Sub

		Private Sub ImportFile(ByVal FileName As String)
			Try
				Dim xls As New XlsFile()
				xls.VirtualMode = True 'Remember to turn virtual mode on, or the event won't be called.

				'By default, FlexCel returns the formula text for the formulas, besides its calculated value.
				'If you are not interested in formula texts, you can gain a little performance by ignoring it.
				'This also works in non virtual mode.
				xls.IgnoreFormulaText = cbIgnoreFormulaText.Checked

				CellData = New SparseCellArray()

				'Attach the CellReader handler.
				Dim cr As New CellReader(cbFirst50Rows.Checked, CellData, cbFormatValues.Checked)
				AddHandler xls.VirtualCellStartReading, AddressOf cr.OnStartReading
				AddHandler xls.VirtualCellRead, AddressOf cr.OnCellRead

				Dim StartOpen As Date = Date.Now

				'Open the file. As we have a CellReader attached, the cells won't be loaded into memory, they will be passed to the CellReader
				xls.Open(FileName)
				Dim StartSheetSelect As Date = cr.StartSheetSelect
				Dim EndSheetSelect As Date = cr.EndSheetSelect

				Dim EndOpen As Date = Date.Now
				statusBar.Text = "Time to open file: " & (StartSheetSelect.Subtract(StartOpen)).ToString() & "     Time to load file and fill grid: " & (EndOpen.Subtract(EndSheetSelect)).ToString()

				'Set up grid.
				GridCaption.Text = FileName
				If CellData IsNot Nothing Then
					DisplayGrid.ColumnCount = CellData.ColCount
					DisplayGrid.RowCount = CellData.RowCount
				Else
					DisplayGrid.ColumnCount = 0
					DisplayGrid.RowCount = 0
				End If

				For i As Integer = 0 To DisplayGrid.ColumnCount - 1
					DisplayGrid.Columns(i).Name = TCellAddress.EncodeColumn(i + 1)
				Next i
			Catch
				GridCaption.Text = "Error Loading File"
				CellData = Nothing
				Throw
			End Try
		End Sub

		Private Sub DisplayGrid_RowPostPaint(ByVal sender As Object, ByVal e As DataGridViewRowPostPaintEventArgs) Handles DisplayGrid.RowPostPaint
			'Show the row number in the grid at the left
			Dim r As String = (e.RowIndex + 1).ToString()
			Dim textSize As SizeF = e.Graphics.MeasureString(r, DisplayGrid.Font)
			If DisplayGrid.RowHeadersWidth < CInt(textSize.Width + 20) Then
				DisplayGrid.RowHeadersWidth = CInt(textSize.Width + 20)
			End If
			e.Graphics.DrawString(r, DisplayGrid.Font, SystemBrushes.ControlText, e.RowBounds.Left + DisplayGrid.RowHeadersWidth - textSize.Width - 5, e.RowBounds.Location.Y + ((e.RowBounds.Height - textSize.Height) / 2F))
		End Sub

		Private Sub DisplayGrid_CellValueNeeded(ByVal sender As Object, ByVal e As DataGridViewCellValueEventArgs) Handles DisplayGrid.CellValueNeeded
			If CellData Is Nothing Then
				e.Value = Nothing
				Return
			End If

			If e.RowIndex >= CellData.RowCount Then
				e.Value = Nothing
				Return
			End If

			e.Value = CellData.GetValue(e.RowIndex + 1, e.ColumnIndex + 1)
		End Sub
	End Class
End Namespace

Program.vb

Namespace VirtualMode
	Friend NotInheritable Class Program

		Private Sub New()
		End Sub

		''' <summary>
		''' The main entry point for the application.
		''' </summary>
		<STAThread> _
		Shared Sub Main()
			Application.EnableVisualStyles()
			Application.SetCompatibleTextRenderingDefault(False)
			Application.Run(New mainForm())
		End Sub
	End Class
End Namespace

SheetSelectorForm.Designer.vb

Namespace VirtualMode
	Partial Public Class SheetSelectorForm
		''' <summary>
		''' Required designer variable.
		''' </summary>
		Private components As System.ComponentModel.IContainer = Nothing

		''' <summary>
		''' Clean up any resources being used.
		''' </summary>
		''' <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
		Protected Overrides Sub Dispose(ByVal disposing As Boolean)
			If disposing AndAlso (components IsNot Nothing) Then
				components.Dispose()
			End If
			MyBase.Dispose(disposing)
		End Sub

		#Region "Windows Form Designer generated code"

		''' <summary>
		''' Required method for Designer support - do not modify
		''' the contents of this method with the code editor.
		''' </summary>
		Private Sub InitializeComponent()
			Me.panel1 = New System.Windows.Forms.Panel()
			Me.btnOk = New System.Windows.Forms.Button()
			Me.btnCancel = New System.Windows.Forms.Button()
			Me.SheetList = New System.Windows.Forms.ListBox()
			Me.panel1.SuspendLayout()
			Me.SuspendLayout()
			' 
			' panel1
			' 
			Me.panel1.Controls.Add(Me.btnCancel)
			Me.panel1.Controls.Add(Me.btnOk)
			Me.panel1.Dock = System.Windows.Forms.DockStyle.Bottom
			Me.panel1.Location = New System.Drawing.Point(0, 217)
			Me.panel1.Name = "panel1"
			Me.panel1.Size = New System.Drawing.Size(284, 45)
			Me.panel1.TabIndex = 0
			' 
			' btnOk
			' 
			Me.btnOk.DialogResult = System.Windows.Forms.DialogResult.OK
			Me.btnOk.Location = New System.Drawing.Point(116, 10)
			Me.btnOk.Name = "btnOk"
			Me.btnOk.Size = New System.Drawing.Size(75, 23)
			Me.btnOk.TabIndex = 0
			Me.btnOk.Text = "Ok"
			Me.btnOk.UseVisualStyleBackColor = True
			' 
			' btnCancel
			' 
			Me.btnCancel.Anchor = (CType((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles))
			Me.btnCancel.DialogResult = System.Windows.Forms.DialogResult.Cancel
			Me.btnCancel.Location = New System.Drawing.Point(197, 10)
			Me.btnCancel.Name = "btnCancel"
			Me.btnCancel.Size = New System.Drawing.Size(75, 23)
			Me.btnCancel.TabIndex = 1
			Me.btnCancel.Text = "Cancel"
			Me.btnCancel.UseVisualStyleBackColor = True
			' 
			' SheetList
			' 
			Me.SheetList.Dock = System.Windows.Forms.DockStyle.Fill
			Me.SheetList.FormattingEnabled = True
			Me.SheetList.Location = New System.Drawing.Point(0, 0)
			Me.SheetList.Name = "SheetList"
			Me.SheetList.Size = New System.Drawing.Size(284, 217)
			Me.SheetList.TabIndex = 1
'			Me.SheetList.DoubleClick += New System.EventHandler(Me.SheetList_DoubleClick)
			' 
			' SheetSelectorForm
			' 
			Me.AcceptButton = Me.btnOk
			Me.AutoScaleDimensions = New System.Drawing.SizeF(6F, 13F)
			Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
			Me.CancelButton = Me.btnCancel
			Me.ClientSize = New System.Drawing.Size(284, 262)
			Me.Controls.Add(Me.SheetList)
			Me.Controls.Add(Me.panel1)
			Me.Name = "SheetSelectorForm"
			Me.Text = "Select sheet to load..."
			Me.panel1.ResumeLayout(False)
			Me.ResumeLayout(False)

		End Sub

		#End Region

		Private panel1 As System.Windows.Forms.Panel
		Private btnCancel As System.Windows.Forms.Button
		Private btnOk As System.Windows.Forms.Button
		Private WithEvents SheetList As System.Windows.Forms.ListBox
	End Class
End Namespace

SheetSelectorForm.vb

Imports System.ComponentModel
Imports System.Text

Namespace VirtualMode
	Partial Public Class SheetSelectorForm
		Inherits Form

		Public Sub New()
			InitializeComponent()
		End Sub

		Public Sub New(ByVal SheetNames() As String)
			Me.New()
			For Each s As String In SheetNames
				SheetList.Items.Add(s)
			Next s

			SheetList.SelectedIndex = 0
		End Sub

		Friend Function Execute() As Boolean
			Return ShowDialog() = System.Windows.Forms.DialogResult.OK
		End Function

		Public ReadOnly Property SelectedSheet() As String
			Get
				Return Convert.ToString(SheetList.SelectedItem)
			End Get
		End Property

		Public ReadOnly Property SelectedSheetIndex() As Integer
			Get
				Return SheetList.SelectedIndex
			End Get
		End Property

		Private Sub SheetList_DoubleClick(ByVal sender As Object, ByVal e As EventArgs) Handles SheetList.DoubleClick
			DialogResult = System.Windows.Forms.DialogResult.OK
		End Sub
	End Class
End Namespace

SparseCellArray.vb

Imports System.Text

Namespace VirtualMode
	'''	<summary>
	'''	  This is a simple class that holds cell values. Items are supposed to
	'''	  be entered in sorted order, and it isn't really production-ready, just
	'''	  to be used in a demo.
	'''	</summary>
	Friend Class SparseCellArray
		Private Data As List(Of SparseRow)
		Private FColCount As Integer

		Public Sub New()
			FColCount = 0
		End Sub
		Public Sub AddValue(ByVal Row As Integer, ByVal Col As Integer, ByVal Value As String)
			If Col > FColCount Then
				FColCount = Col
			End If
			If Data Is Nothing Then
				Data = New List(Of SparseRow)()
			End If
			Dim SpRow As New SparseRow(Row)
			Dim Idx As Integer = Data.BinarySearch(SpRow)
			If Idx < 0 Then
				SpRow.CreateData()
				Data.Insert((Not Idx), SpRow)
			Else
				SpRow = Data(Idx)
			End If

			Dim SpCell As New SparseCell(Col, Value)
			Idx = SpRow.Data.BinarySearch(SpCell)
			If Idx < 0 Then
				SpRow.Data.Insert((Not Idx), SpCell)
			Else
				SpRow.Data(Idx) = SpCell
			End If

		End Sub

		Public Function GetValue(ByVal Row As Integer, ByVal Col As Integer) As String
			If Data Is Nothing Then
				Return Nothing
			End If

			Dim SpRow As New SparseRow(Row)
			Dim Idx As Integer = Data.BinarySearch(SpRow)
			If Idx < 0 Then
				Return Nothing
			End If
			SpRow = Data(Idx)
			Dim SpCell As New SparseCell(Col, Nothing)
			Idx = SpRow.Data.BinarySearch(SpCell)
			If Idx < 0 Then
				Return Nothing
			End If
			Return SpRow.Data(Idx).Value
		End Function

		Public ReadOnly Property ColCount() As Integer
			Get
				Return FColCount
			End Get
		End Property
		Public ReadOnly Property RowCount() As Integer
			Get
				If Data Is Nothing OrElse Data.Count = 0 Then
					Return 0
				End If
				Return Data(Data.Count - 1).Row
			End Get
		End Property
	End Class

	Friend Structure SparseRow
		Implements IComparable(Of SparseRow)

		Public Row As Integer
		Public Data As List(Of SparseCell)

		Public Sub New(ByVal aRow As Integer)
			Row = aRow
			Data = Nothing
		End Sub

		Public Sub CreateData()
			Data = New List(Of SparseCell)()
		End Sub

		#Region "IComparable<SparseRow> Members"

		Public Function CompareTo(ByVal other As SparseRow) As Integer Implements IComparable(Of SparseRow).CompareTo
			Return Row.CompareTo(other.Row)
		End Function

		#End Region
	End Structure

	Friend Class SparseCell
		Implements IComparable(Of SparseCell)

		Public Col As Integer
		Public Value As String

		Public Sub New(ByVal aCol As Integer, ByVal aValue As String)
			Col = aCol
			Value = aValue
		End Sub

		#Region "IComparable<SparseCell> Members"

		Public Function CompareTo(ByVal other As SparseCell) As Integer Implements IComparable(Of SparseCell).CompareTo
			Return Col.CompareTo(other.Col)
		End Function

		#End Region
	End Class
End Namespace

Table of Contents