Reading Excel files (VB.Net / netframework)

Note

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

Overview

A demo showing how to read the contents of an Excel file using FlexCel.

Concepts

• To read an Excel file you use the XlsFile class, from where you can read and write to any Excel 2.0 or newer file.

• To get the value for a single cell, use XlsFile.GetCellValue.

• To get the value for a cell when looping a full sheet, use XlsFile.GetCellValueIndexed. It is faster than using GetCellValue since you will only read the used cells.

• XlsFile.GetCellValue and XlsFile.GetCellValueIndexed can return one of the following objects:

  • null

  • Double

  • Boolean

  • String

  • TRichString

  • TFormula

• With GetCellValue and GetCellValueIndexed you will get the actual values. But if you want to actually display formatted data (for example if you have the number 2 with 2 decimals, and you want to display 2.00 instead of 2), you need to use other methods. There are 2 ways to do it:

  1. XlsFile.GetStringFromCell will return a rich string with the cell formatted.

  2. FormatValue will format an object with a specified format and then return the corresponding rich string. TFlxNumberFormat.FormatValue is used internally by GetStringFromCell.

• In Excel, Dates are doubles. The only difference between a date and a double is on the format on the cell. With FormatValue you can get the actual string that is displayed on Excel. Also, to convert this double to a DateTime, you can use FlxDateTime.FromOADate.

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("")>

Form1.Designer.vb

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

		Private openFileDialog1 As System.Windows.Forms.OpenFileDialog
		Private DisplayGrid As System.Windows.Forms.DataGrid
		Private panel1 As System.Windows.Forms.Panel
		Private label1 As System.Windows.Forms.Label
		Private WithEvents sheetCombo As System.Windows.Forms.ComboBox
		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 resources As New System.ComponentModel.ComponentResourceManager(GetType(mainForm))
			Me.openFileDialog1 = New System.Windows.Forms.OpenFileDialog()
			Me.DisplayGrid = New System.Windows.Forms.DataGrid()
			Me.panel1 = New System.Windows.Forms.Panel()
			Me.sheetCombo = New System.Windows.Forms.ComboBox()
			Me.label1 = New System.Windows.Forms.Label()
			Me.statusBar = New System.Windows.Forms.StatusBar()
			Me.mainToolbar = New System.Windows.Forms.ToolStrip()
			Me.btnOpenFile = New System.Windows.Forms.ToolStripButton()
			Me.toolStripSeparator1 = New System.Windows.Forms.ToolStripSeparator()
			Me.btnFormatValues = New System.Windows.Forms.ToolStripButton()
			Me.toolStripSeparator2 = New System.Windows.Forms.ToolStripSeparator()
			Me.btnValueInCellA1 = New System.Windows.Forms.ToolStripButton()
			Me.btnExit = New System.Windows.Forms.ToolStripButton()
			Me.btnInfo = New System.Windows.Forms.ToolStripButton()
			CType(Me.DisplayGrid, System.ComponentModel.ISupportInitialize).BeginInit()
			Me.panel1.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"
			' 
			' DisplayGrid
			' 
			Me.DisplayGrid.DataMember = ""
			Me.DisplayGrid.Dock = System.Windows.Forms.DockStyle.Fill
			Me.DisplayGrid.HeaderForeColor = System.Drawing.SystemColors.ControlText
			Me.DisplayGrid.Location = New System.Drawing.Point(0, 67)
			Me.DisplayGrid.Name = "DisplayGrid"
			Me.DisplayGrid.Size = New System.Drawing.Size(880, 372)
			Me.DisplayGrid.TabIndex = 5
			' 
			' panel1
			' 
			Me.panel1.BackColor = System.Drawing.SystemColors.ControlDark
			Me.panel1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
			Me.panel1.Controls.Add(Me.sheetCombo)
			Me.panel1.Controls.Add(Me.label1)
			Me.panel1.Dock = System.Windows.Forms.DockStyle.Top
			Me.panel1.Location = New System.Drawing.Point(0, 38)
			Me.panel1.Name = "panel1"
			Me.panel1.Size = New System.Drawing.Size(880, 29)
			Me.panel1.TabIndex = 6
			' 
			' sheetCombo
			' 
			Me.sheetCombo.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList
			Me.sheetCombo.Location = New System.Drawing.Point(65, 3)
			Me.sheetCombo.Name = "sheetCombo"
			Me.sheetCombo.Size = New System.Drawing.Size(391, 21)
			Me.sheetCombo.TabIndex = 1
'			Me.sheetCombo.SelectedIndexChanged += New System.EventHandler(Me.sheetCombo_SelectedIndexChanged)
			' 
			' label1
			' 
			Me.label1.ForeColor = System.Drawing.SystemColors.HighlightText
			Me.label1.Location = New System.Drawing.Point(8, 8)
			Me.label1.Name = "label1"
			Me.label1.Size = New System.Drawing.Size(40, 23)
			Me.label1.TabIndex = 0
			Me.label1.Text = "Sheet:"
			' 
			' 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
			' 
			' mainToolbar
			' 
			Me.mainToolbar.Items.AddRange(New System.Windows.Forms.ToolStripItem() { Me.btnOpenFile, Me.toolStripSeparator1, Me.btnFormatValues, Me.toolStripSeparator2, Me.btnValueInCellA1, 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 = 11
			Me.mainToolbar.Text = "mainToolbar"
			' 
			' 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)
			' 
			' toolStripSeparator1
			' 
			Me.toolStripSeparator1.Name = "toolStripSeparator1"
			Me.toolStripSeparator1.Size = New System.Drawing.Size(6, 38)
			' 
			' btnFormatValues
			' 
			Me.btnFormatValues.CheckOnClick = True
			Me.btnFormatValues.Image = (CType(resources.GetObject("btnFormatValues.Image"), System.Drawing.Image))
			Me.btnFormatValues.ImageTransparentColor = System.Drawing.Color.Magenta
			Me.btnFormatValues.Name = "btnFormatValues"
			Me.btnFormatValues.Size = New System.Drawing.Size(85, 35)
			Me.btnFormatValues.Text = "&Format values"
			Me.btnFormatValues.TextImageRelation = System.Windows.Forms.TextImageRelation.ImageAboveText
			' 
			' toolStripSeparator2
			' 
			Me.toolStripSeparator2.Name = "toolStripSeparator2"
			Me.toolStripSeparator2.Size = New System.Drawing.Size(6, 38)
			' 
			' btnValueInCellA1
			' 
			Me.btnValueInCellA1.Image = (CType(resources.GetObject("btnValueInCellA1.Image"), System.Drawing.Image))
			Me.btnValueInCellA1.ImageTransparentColor = System.Drawing.Color.Magenta
			Me.btnValueInCellA1.Name = "btnValueInCellA1"
			Me.btnValueInCellA1.Size = New System.Drawing.Size(91, 35)
			Me.btnValueInCellA1.Text = "&Value in cell A1"
			Me.btnValueInCellA1.TextImageRelation = System.Windows.Forms.TextImageRelation.ImageAboveText
'			Me.btnValueInCellA1.Click += New System.EventHandler(Me.btnValueInCurrentCell_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.panel1)
			Me.Controls.Add(Me.statusBar)
			Me.Controls.Add(Me.mainToolbar)
			Me.Name = "mainForm"
			Me.Text = "Reading Excel Files"
			CType(Me.DisplayGrid, System.ComponentModel.ISupportInitialize).EndInit()
			Me.panel1.ResumeLayout(False)
			Me.mainToolbar.ResumeLayout(False)
			Me.mainToolbar.PerformLayout()
			Me.ResumeLayout(False)
			Me.PerformLayout()

		End Sub
		#End Region

		Private mainToolbar As ToolStrip
		Private WithEvents btnOpenFile As ToolStripButton
		Private toolStripSeparator1 As ToolStripSeparator
		Private WithEvents btnInfo As ToolStripButton
		Private WithEvents btnExit As ToolStripButton
		Private WithEvents btnValueInCellA1 As ToolStripButton
		Private toolStripSeparator2 As ToolStripSeparator
		Private btnFormatValues As ToolStripButton
	End Class
End Namespace

Form1.vb

Imports System.Collections
Imports System.ComponentModel

Imports FlexCel.Core
Imports FlexCel.XlsAdapter

Namespace ReadingFiles
	''' <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

		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 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, btnFormatValues.Checked)
		End Sub

		Private Sub ImportFile(ByVal FileName As String, ByVal Formatted As Boolean)
			Try
				'Open the Excel file.
				Dim xls As New XlsFile(False)
				Dim StartOpen As Date = Date.Now
				xls.Open(FileName)
				Dim EndOpen As Date = Date.Now

				'Set up the Grid
				DisplayGrid.DataBindings.Clear()
				DisplayGrid.DataSource = Nothing
				DisplayGrid.DataMember = Nothing
				Dim dataSet1 As New DataSet()
				sheetCombo.Items.Clear()

				'We will create a DataTable "SheetN" for each sheet on the Excel sheet.
				For sheet As Integer = 1 To xls.SheetCount
					xls.ActiveSheet = sheet

					sheetCombo.Items.Add(xls.SheetName)

					Dim Data As DataTable = dataSet1.Tables.Add("Sheet" & sheet.ToString())
					Data.BeginLoadData()
					Try
						Dim ColCount As Integer = xls.ColCount
						'Add one column on the dataset for each used column on Excel.
						For c As Integer = 1 To ColCount
							Data.Columns.Add(TCellAddress.EncodeColumn(c), GetType(String)) 'Here we will add all strings, since we do not know what we are waiting for.
						Next c

						Dim dr(ColCount - 1) As String

						Dim RowCount As Integer = xls.RowCount
						For r As Integer = 1 To RowCount
							Array.Clear(dr, 0, dr.Length)
							'This loop will only loop on used cells. It is more efficient than looping on all the columns.
							For cIndex As Integer = xls.ColCountInRow(r) To 1 Step -1 'reverse the loop to avoid calling ColCountInRow more than once.
								Dim Col As Integer = xls.ColFromIndex(r, cIndex)

								If Formatted Then
									Dim rs As TRichString = xls.GetStringFromCell(r, Col)
									dr(Col - 1) = rs.Value
								Else
									Dim XF As Integer = 0 'This is the cell format, we will not use it here.
									Dim val As Object = xls.GetCellValueIndexed(r, cIndex, XF)

									Dim Fmla As TFormula = TryCast(val, TFormula)
									If Fmla IsNot Nothing Then
										'When we have formulas, we want to write the formula result. 
										'If we wanted the formula text, we would not need this part.
										dr(Col - 1) = Convert.ToString(Fmla.Result)
									Else
										dr(Col - 1) = Convert.ToString(val)
									End If
								End If
							Next cIndex
							Data.Rows.Add(dr)
						Next r
					Finally
						Data.EndLoadData()
					End Try

					Dim EndFill As Date = Date.Now
					statusBar.Text = String.Format("Time to load file: {0}    Time to fill dataset: {1}     Total time: {2}", (EndOpen.Subtract(StartOpen)).ToString(), (EndFill.Subtract(EndOpen)).ToString(), (EndFill.Subtract(StartOpen)).ToString())

				Next sheet

				'Set up grid.
				DisplayGrid.DataSource = dataSet1
				DisplayGrid.DataMember = "Sheet1"
				sheetCombo.SelectedIndex = 0
				DisplayGrid.CaptionText = FileName

			Catch
				DisplayGrid.CaptionText = "Error Loading File"
				DisplayGrid.DataSource = Nothing
				DisplayGrid.DataMember = ""
				sheetCombo.Items.Clear()
				Throw
			End Try
		End Sub

		Private Sub sheetCombo_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles sheetCombo.SelectedIndexChanged
			If (TryCast(sender, ComboBox)).SelectedIndex < 0 Then
				Return
			End If
			DisplayGrid.DataMember = "Sheet" & ((TryCast(sender, ComboBox)).SelectedIndex + 1).ToString()
		End Sub

		Private Sub AnalizeFile(ByVal FileName As String, ByVal Row As Integer, ByVal Col As Integer)
			Dim xls As New XlsFile()
			xls.Open(FileName)

			Dim XF As Integer = 0
			MessageBox.Show("Active sheet is """ & xls.ActiveSheetByName & """")
			Dim v As Object = xls.GetCellValue(Row, Col, XF)

			If v Is Nothing Then
				MessageBox.Show("Cell A1 is empty")
				Return
			End If

			'Here we have all the kind of objects FlexCel can return.
			Select Case Type.GetTypeCode(v.GetType())
				Case TypeCode.Boolean
					MessageBox.Show("Cell A1 is a boolean: " & CBool(v))
					Return
				Case TypeCode.Double 'Remember, dates are doubles with date format.
					Dim CellColor As TUIColor = Color.Empty
					Dim HasDate, HasTime As Boolean
					Dim CellValue As String = TFlxNumberFormat.FormatValue(v, xls.GetFormat(XF).Format, CellColor, xls, HasDate, HasTime).ToString()

					If HasDate OrElse HasTime Then
						MessageBox.Show("Cell A1 is a DateTime value: " & FlxDateTime.FromOADate(CDbl(v), xls.OptionsDates1904).ToString() & vbLf & "The value is displayed as: " & CellValue)
					Else
						MessageBox.Show("Cell A1 is a double: " & CDbl(v) & vbLf & "The value is displayed as: " & CellValue & vbLf)
					End If
					Return
				Case TypeCode.String
					MessageBox.Show("Cell A1 is a string: " & v.ToString())
					Return
			End Select

			Dim Fmla As TFormula = TryCast(v, TFormula)
			If Fmla IsNot Nothing Then
				MessageBox.Show("Cell A1 is a formula: " & Fmla.Text & "   Value: " & Convert.ToString(Fmla.Result))
				Return
			End If

			Dim RSt As TRichString = TryCast(v, TRichString)
			If RSt IsNot Nothing Then
				MessageBox.Show("Cell A1 is a formatted string: " & RSt.Value)
				Return
			End If

			If TypeOf v Is TFlxFormulaErrorValue Then
				MessageBox.Show("Cell A1 is an error: " & TFormulaMessages.ErrString(CType(v, TFlxFormulaErrorValue)))
				Return
			End If

			Throw New Exception("Unexpected value on cell")

		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" & vbLf & "The 'Open File' button will load an Excel file into a dataset. Depending on the button 'Format Values' it will load the actual values (this is the fastest) or the formatted values." & vbLf & "The 'Format Values' button will modify how the files are read when you press 'Open File'. Formated values are slower, but they will look just how Excel shows them." & vbLf & "The 'Value in Cell A1' button will load an Excel file and show the contents of cell a1 on the active sheet.")
		End Sub

		''' <summary>
		''' This method will not do anything truly useful, but it alows you to see how to 
		''' process the different types of objects that GetCellValue can return
		''' </summary>
		''' <param name="sender"></param>
		''' <param name="e"></param>
		Private Sub btnValueInCurrentCell_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnValueInCellA1.Click
			If openFileDialog1.ShowDialog() <> System.Windows.Forms.DialogResult.OK Then
				Return
			End If
			AnalizeFile(openFileDialog1.FileName, 1, 1)
		End Sub
	End Class
End Namespace

Program.vb

Namespace ReadingFiles
	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