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
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:
XlsFile.GetStringFromCell will return a rich string with the cell formatted.
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