Save Excel to Database

Shows how to iterate data in an Excel WorkSheet and import that data into a System.Data.DataTable so that it may be saved to any SQL database supported by C#.

Entire WorkBooks can also be converted to System.Data.DataSet

C#:

using IronXL;
using System.Data;


WorkBook workbook = WorkBook.Load("test.xls");
WorkSheet sheet = workbook.DefaultWorkSheet;

//This is how you convert your worksheet to DataTable
//Boolean parameter allows you to set whether you want to treat your first row as column names of your table.
//The default value is false
var dataTable = sheet.ToDataTable(true);

//Optionally inspect your data
foreach (DataRow row in dataTable.Rows)
{
    for (int i = 0; i < dataTable.Columns.Count; i++)
    {
        Console.Write(row[i]);
    }
}



//Also you can convert the whole workbook to DataSet and work with it the way you like.
var dataSet = workbook.ToDataSet();

foreach (DataTable table in dataSet.Tables)
{
    Console.WriteLine(table.TableName);
}

VB:

Imports IronXL
Imports System.Data


Private workbook As WorkBook = WorkBook.Load("test.xls")
Private sheet As WorkSheet = workbook.DefaultWorkSheet

'This is how you convert your worksheet to DataTable
'Boolean parameter allows you to set whether you want to treat your first row as column names of your table.
'The default value is false
Private dataTable = sheet.ToDataTable(True)

'Optionally inspect your data
For Each row As DataRow In dataTable.Rows
    For i As Integer = 0 To dataTable.Columns.Count - 1
        Console.Write(row(i))
    Next i
Next row



'Also you can convert the whole workbook to DataSet and work with it the way you like.
Dim dataSet = workbook.ToDataSet()

For Each table As DataTable In dataSet.Tables
    Console.WriteLine(table.TableName)
Next table