如何使用 IronXL.Excel 在 C# 中輕鬆匯入、讀取和處理 MS Excel 檔案資料

using IronXL;
static void Main(string[] args)
{
 // Load any Excel file
 WorkBook workBook = WorkBook.Load("ProductData.xlsx");
 // Access the first worksheet
 WorkSheet sheet = workBook.WorkSheets[0];
 // Read a cell value
 string value = sheet["B1"].StringValue;
 Console.WriteLine(value);
}

Imports IronXL

Module Program
    Sub Main(args As String())
        ' Load any Excel file
        Dim workBook As WorkBook = WorkBook.Load("ProductData.xlsx")
        ' Access the first worksheet
        Dim sheet As WorkSheet = workBook.WorkSheets(0)
        ' Read a cell value
        Dim value As String = sheet("B1").StringValue
        Console.WriteLine(value)
    End Sub
End Module

// Load the Excel file
WorkBook workBook = WorkBook.Load("ProductData.xlsx");
WorkSheet sheet = workBook.DefaultWorkSheet;
// Read a range of cells
 var products = new List<Product>();
 foreach (var row in sheet.Rows)
 {
     var product = new Product
     {
         Id = row.Columns[0].IntValue,
         Name = row.Columns[1].StringValue,
         Price = row.Columns[2].DecimalValue,
         IsAvailable = row.Columns[3].BoolValue
     };
     products.Add(product);
 }
 foreach (var product in products)
 {
     Console.WriteLine($"Id: {product.Id}, Name: {product.Name}, Price: {product.Price}, Available: {product.IsAvailable}");
 }

Imports System
Imports System.Collections.Generic

' Load the Excel file
Dim workBook As WorkBook = WorkBook.Load("ProductData.xlsx")
Dim sheet As WorkSheet = workBook.DefaultWorkSheet
' Read a range of cells
Dim products As New List(Of Product)()
For Each row In sheet.Rows
    Dim product As New Product With {
        .Id = row.Columns(0).IntValue,
        .Name = row.Columns(1).StringValue,
        .Price = row.Columns(2).DecimalValue,
        .IsAvailable = row.Columns(3).BoolValue
    }
    products.Add(product)
Next
For Each product In products
    Console.WriteLine($"Id: {product.Id}, Name: {product.Name}, Price: {product.Price}, Available: {product.IsAvailable}")
Next

using System.Data;
// Load Excel file
WorkBook workBook = WorkBook.Load("inventory.xlsx");
// Select first Excel worksheet
WorkSheet sheet = workBook.WorkSheets.First();
// Convert to DataTable with headers
DataTable dataTable = sheet.ToDataTable(true);
// The DataTable is ready for database operations
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString))
{
    bulkCopy.DestinationTableName = "Inventory";
    bulkCopy.WriteToServer(dataTable);
}

Imports System.Data

' Load Excel file
Dim workBook As WorkBook = WorkBook.Load("inventory.xlsx")
' Select first Excel worksheet
Dim sheet As WorkSheet = workBook.WorkSheets.First()
' Convert to DataTable with headers
Dim dataTable As DataTable = sheet.ToDataTable(True)
' The DataTable is ready for database operations
Using bulkCopy As New SqlBulkCopy(connectionString)
    bulkCopy.DestinationTableName = "Inventory"
    bulkCopy.WriteToServer(dataTable)
End Using

// Custom DataTable conversion
DataTable customTable = new DataTable();
// Define columns based on Excel structure
for (int col = 0; col < sheet.ColumnCount; col++)
{
    customTable.Columns.Add($"Column{col + 1}", typeof(string));
}
// Import data with validation
foreach (var row in sheet.Rows)
{
    var dataRow = customTable.NewRow();
    for (int col = 0; col < sheet.ColumnCount; col++)
    {
        dataRow[col] = row.Columns[col].Value ?? DBNull.Value;
    }
    customTable.Rows.Add(dataRow);
}
// Print column headers
foreach (DataColumn column in customTable.Columns)
{
    Console.Write($"{column.ColumnName}\t");
}
Console.WriteLine();
// Print each row
foreach (DataRow row in customTable.Rows)
{
    foreach (var item in row.ItemArray)
    {
        Console.Write($"{item}\t");
    }
    Console.WriteLine();
}

Imports System
Imports System.Data

' Custom DataTable conversion
Dim customTable As New DataTable()
' Define columns based on Excel structure
For col As Integer = 0 To sheet.ColumnCount - 1
    customTable.Columns.Add($"Column{col + 1}", GetType(String))
Next
' Import data with validation
For Each row In sheet.Rows
    Dim dataRow = customTable.NewRow()
    For col As Integer = 0 To sheet.ColumnCount - 1
        dataRow(col) = If(row.Columns(col).Value, DBNull.Value)
    Next
    customTable.Rows.Add(dataRow)
Next
' Print column headers
For Each column As DataColumn In customTable.Columns
    Console.Write($"{column.ColumnName}" & vbTab)
Next
Console.WriteLine()
' Print each row
For Each row As DataRow In customTable.Rows
    For Each item In row.ItemArray
        Console.Write($"{item}" & vbTab)
    Next
    Console.WriteLine()
Next

// Read calculated values from formulas
WorkSheet sheet = workBook.GetWorkSheet("Reports");
decimal calculatedTotal = sheet["E10"].DecimalValue; // Reads formula result
// Formulas remain intact
string formula = sheet["E10"].Formula; // Returns "=SUM(E2:E9)"

' Read calculated values from formulas
Dim sheet As WorkSheet = workBook.GetWorkSheet("Reports")
Dim calculatedTotal As Decimal = sheet("E10").DecimalValue ' Reads formula result
' Formulas remain intact
Dim formula As String = sheet("E10").Formula ' Returns "=SUM(E2:E9)"

var products = sheet.Rows.Skip(1)
    .Select(row => new Product
    {
        Name = row.Columns[0].StringValue,
        Price = row.Columns[1].DecimalValue
    })
    .ToList();
using (var context = new AppDbContext())
{
    context.Products.AddRange(products);
    await context.SaveChangesAsync();
}

Imports System.Linq

Dim products = sheet.Rows.Skip(1) _
    .Select(Function(row) New Product With {
        .Name = row.Columns(0).StringValue,
        .Price = row.Columns(1).DecimalValue
    }).ToList()

Using context As New AppDbContext()
    context.Products.AddRange(products)
    Await context.SaveChangesAsync()
End Using