using IronXL 將 Excel 檔案匯入 DataTable

using IronXL;
using System;
using System.Data;
class Program
{
    static void Main(string[] args)
    {
        // Load the Excel file from the specified path
        string filePath = "sales-data.xlsx";
        WorkBook workbook = WorkBook.Load(filePath);
        // Access the first worksheet in the workbook
        WorkSheet worksheet = workbook.DefaultWorkSheet;
        // Convert worksheet to DataTable with first row as column headers
        DataTable dt = worksheet.ToDataTable(true);
        // Display the imported data
        foreach (DataRow row in dt.Rows)
        {
            Console.WriteLine(string.Join(" | ", row.ItemArray));
        }
    }
}

Imports IronXL
Imports System
Imports System.Data

Module Program
    Sub Main(args As String())
        ' Load the Excel file from the specified path
        Dim filePath As String = "sales-data.xlsx"
        Dim workbook As WorkBook = WorkBook.Load(filePath)
        ' Access the first worksheet in the workbook
        Dim worksheet As WorkSheet = workbook.DefaultWorkSheet
        ' Convert worksheet to DataTable with first row as column headers
        Dim dt As DataTable = worksheet.ToDataTable(True)
        ' Display the imported data
        For Each row As DataRow In dt.Rows
            Console.WriteLine(String.Join(" | ", row.ItemArray))
        Next
    End Sub
End Module

using IronXL;
using System.Data;
// Load workbook from file path
string filePath = @"C:\Data\inventory.xlsx";
WorkBook workbook = WorkBook.Load(filePath);
WorkSheet worksheet = workbook.WorkSheets[0];
// First row as headers (true) or as data (false)
DataTable dataTableWithHeaders = worksheet.ToDataTable(true);
DataTable dataTableWithoutHeaders = worksheet.ToDataTable(false);
// Process each row in the DataTable
foreach (DataRow row in dataTableWithHeaders.Rows)
{
    for (int i = 0; i < dataTableWithHeaders.Columns.Count; i++)
    {
        object value = row[i];
        Console.Write($"{value}\t");
    }
    Console.WriteLine();
}

Imports IronXL
Imports System.Data

' Load workbook from file path
Dim filePath As String = "C:\Data\inventory.xlsx"
Dim workbook As WorkBook = WorkBook.Load(filePath)
Dim worksheet As WorkSheet = workbook.WorkSheets(0)

' First row as headers (true) or as data (false)
Dim dataTableWithHeaders As DataTable = worksheet.ToDataTable(True)
Dim dataTableWithoutHeaders As DataTable = worksheet.ToDataTable(False)

' Process each row in the DataTable
For Each row As DataRow In dataTableWithHeaders.Rows
    For i As Integer = 0 To dataTableWithHeaders.Columns.Count - 1
        Dim value As Object = row(i)
        Console.Write($"{value}" & vbTab)
    Next
    Console.WriteLine()
Next

using IronXL;
using System;
using System.Data;
// Load multi-sheet workbook
WorkBook workbook = WorkBook.Load("quarterly-reports.xlsx");
// Convert entire workbook to DataSet (each sheet becomes a DataTable)
DataSet dataset = workbook.ToDataSet();
// Iterate through all tables in the DataSet
foreach (DataTable table in dataset.Tables)
{
    Console.WriteLine($"Sheet: {table.TableName}");
    Console.WriteLine($"Rows: {table.Rows.Count}, Columns: {table.Columns.Count}");
    foreach (DataRow row in table.Rows)
    {
        Console.WriteLine(string.Join(", ", row.ItemArray));
    }
}

Imports IronXL
Imports System
Imports System.Data

' Load multi-sheet workbook
Dim workbook As WorkBook = WorkBook.Load("quarterly-reports.xlsx")
' Convert entire workbook to DataSet (each sheet becomes a DataTable)
Dim dataset As DataSet = workbook.ToDataSet()
' Iterate through all tables in the DataSet
For Each table As DataTable In dataset.Tables
    Console.WriteLine($"Sheet: {table.TableName}")
    Console.WriteLine($"Rows: {table.Rows.Count}, Columns: {table.Columns.Count}")
    For Each row As DataRow In table.Rows
        Console.WriteLine(String.Join(", ", row.ItemArray))
    Next
Next

using IronXL;
using System.Data;
using System.Data.SqlClient;
string filePath = @"C:\imports\customer-data.xlsx";
WorkBook workbook = WorkBook.Load(filePath);
WorkSheet worksheet = workbook.DefaultWorkSheet;
// Convert Excel data to DataTable
DataTable dt = worksheet.ToDataTable(true);
// Validate data before database insert
if (dt == null || dt.Rows.Count == 0)
{
    Console.WriteLine("No data to import");
    return;
}
// Insert into database using SqlBulkCopy
string connectionString = "Server=.;Database=Sales;Trusted_Connection=True;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
    {
        bulkCopy.DestinationTableName = "Customers";
        bulkCopy.WriteToServer(dt);
    }
}
Console.WriteLine($"Imported {dt.Rows.Count} records successfully");

Imports IronXL
Imports System.Data
Imports System.Data.SqlClient

Module Module1
    Sub Main()
        Dim filePath As String = "C:\imports\customer-data.xlsx"
        Dim workbook As WorkBook = WorkBook.Load(filePath)
        Dim worksheet As WorkSheet = workbook.DefaultWorkSheet
        ' Convert Excel data to DataTable
        Dim dt As DataTable = worksheet.ToDataTable(True)
        ' Validate data before database insert
        If dt Is Nothing OrElse dt.Rows.Count = 0 Then
            Console.WriteLine("No data to import")
            Return
        End If
        ' Insert into database using SqlBulkCopy
        Dim connectionString As String = "Server=.;Database=Sales;Trusted_Connection=True;"
        Using connection As New SqlConnection(connectionString)
            connection.Open()
            Using bulkCopy As New SqlBulkCopy(connection)
                bulkCopy.DestinationTableName = "Customers"
                bulkCopy.WriteToServer(dt)
            End Using
        End Using
        Console.WriteLine($"Imported {dt.Rows.Count} records successfully")
    End Sub
End Module

using IronXL;
using System.Data;
// Create sample DataTable
DataTable dt = new DataTable("Products");
dt.Columns.Add("ProductID", typeof(int));
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Price", typeof(decimal));
dt.Rows.Add(1, "Widget", 29.99m);
dt.Rows.Add(2, "Gadget", 49.99m);
// Create new workbook and load DataTable
WorkBook workbook = WorkBook.Create();
workbook.LoadWorkSheetsFromDataSet(new DataSet { Tables = { dt } });
// Save as Excel file
workbook.SaveAs("exported-products.xlsx");

Imports IronXL
Imports System.Data

' Create sample DataTable
Dim dt As New DataTable("Products")
dt.Columns.Add("ProductID", GetType(Integer))
dt.Columns.Add("Name", GetType(String))
dt.Columns.Add("Price", GetType(Decimal))
dt.Rows.Add(1, "Widget", 29.99D)
dt.Rows.Add(2, "Gadget", 49.99D)

' Create new workbook and load DataTable
Dim workbook As WorkBook = WorkBook.Create()
workbook.LoadWorkSheetsFromDataSet(New DataSet With {.Tables = {dt}})

' Save as Excel file
workbook.SaveAs("exported-products.xlsx")