Excel to SQL via System.Data.DataSet

Convert any XLS or XLSX Excel file (WorkBook) to a System.Data.DataTable for full interoperability with System.Data.SQL or to populate a DataGrid.

C#:

using IronXL;
using System.Data;

 //Open any Excel document.
WorkBook workbook = WorkBook.Load("test.xls");

// Convert the whole Excel WorkBook to a DataSet
// This allows us to work with DataGrids and System.Data.SQL nicely
var dataSet = workbook.ToDataSet();
foreach (DataTable table in dataSet.Tables)
{
    Console.WriteLine(table.TableName);

     //Enumerate by rows or columns first at your preference
    foreach (DataRow row in table.Rows)
    {
        for (int i = 0; i < table.Columns.Count; i++)
        {
            Console.Write(row[i]);
        }
    }
}

VB:

Imports IronXL
Imports System.Data

 'Open any Excel document.
Private workbook As WorkBook = WorkBook.Load("test.xls")

' Convert the whole Excel WorkBook to a DataSet
' This allows us to work with DataGrids and System.Data.SQL nicely
Private dataSet = workbook.ToDataSet()
For Each table As DataTable In dataSet.Tables
    Console.WriteLine(table.TableName)

     'Enumerate by rows or columns first at your preference
    For Each row As DataRow In table.Rows
        For i As Integer = 0 To table.Columns.Count - 1
            Console.Write(row(i))
        Next i
    Next row
Next table