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