How to read dataset from XLS file in VB.NET

Home
/
Articles
/
How to read dataset from XLS file in VB.NET
The sample is how to import Excel file (XLS) to dataset in Visual Basic .NET. This source code sample shows how to use vbnet read dataset from xls with Spreadsheet SDK.
VB.NET
Module1.vb:
 
 
Imports Bytescout.Spreadsheet
Imports System.IO
 
Module <span data-scayt_word="Module1" data-scaytid="4">Module1</span>
 
   Sub Main()
       ' Open Spreadsheet
       Dim document As New Spreadsheet()
       document.LoadFromFile("AdvancedReport.xls")
 
       Dim <span data-scayt_word="dataSet" data-scaytid="6">dataSet</span> As New <span data-scayt_word="DataSet" data-scaytid="8">DataSet</span>("<span data-scayt_word="AdvancedReport" data-scaytid="9">AdvancedReport</span>")
 
       For i As Integer = 0 To document.Workbook.Worksheets.Count - 1
           Dim worksheet As Worksheet = document.Workbook.Worksheets(i)
 
           Dim table As <span data-scayt_word="DataTable" data-scaytid="10">DataTable</span> = dataSet.Tables.Add(worksheet.Name)
 
           For column As Integer = 0 To worksheet.UsedRangeColumnMax
               table.Columns.Add(String.Format("Column{0}", column))
           Next
 
           For row As Integer = 0 To worksheet.UsedRangeRowMax
               Dim data() As Object
 
               [Array].Resize(data, worksheet.UsedRangeColumnMax + 1)
 
               For column As Integer = 0 To worksheet.UsedRangeColumnMax
                   data(column) = worksheet.Cell(row, column).Value
               Next
 
               table.Rows.Add(data)
           Next
       Next
 
       ' Close Spreadsheet
       document.Close()
 
       <span data-scayt_word="PrintDataSet" data-scaytid="11">PrintDataSet</span>(<span data-scayt_word="dataSet" data-scaytid="14">dataSet</span>)
   End Sub
 
   Private Sub <span data-scayt_word="PrintDataSet" data-scaytid="13">PrintDataSet</span>(<span data-scayt_word="ByRef" data-scaytid="20">ByRef</span> <span data-scayt_word="ds" data-scaytid="21">ds</span> As <span data-scayt_word="DataSet" data-scaytid="15">DataSet</span>)
       Console.WriteLine("<span data-scayt_word="DataSet" data-scaytid="16">DataSet</span> name: {0}", ds.DataSetName)
       For Each table As <span data-scayt_word="DataTable" data-scaytid="24">DataTable</span> In ds.Tables
           Dim <span data-scayt_word="rowCount" data-scaytid="27">rowCount</span> As Integer = table.Rows.Count
           Dim <span data-scayt_word="columnCount" data-scaytid="30">columnCount</span> As Integer = table.Columns.Count
 
           Console.WriteLine("<span data-scayt_word="nTable" data-scaytid="31">nTable</span>: {0} ({1} rows)", table.TableName, <span data-scayt_word="rowCount" data-scaytid="33">rowCount</span>)
           For Each column As DataColumn In table.Columns
               Console.Write("{0}t", column.ColumnName)
           Next
 
           Console.WriteLine()
 
           For i As Integer = 0 To <span data-scayt_word="rowCount" data-scaytid="34">rowCount</span> - 1
               For column As Integer = 0 To <span data-scayt_word="columnCount" data-scaytid="39">columnCount</span> - 1
                   Console.Write("{0}t", table.Rows(i)(column))
 
                   Console.WriteLine()
               Next
           Next
       Next
 
   End Sub
 
End Module
Tutorials:

Web API
