使用 C# 匯出 Excel 資料表 | 轉換 Excel 數據

using IronXL;
using System.Data;

// Load the workbook from disk
WorkBook workbook = WorkBook.Load("customers.xlsx");

// Grab the default (first) worksheet
WorkSheet worksheet = workbook.DefaultWorkSheet;

// Convert the entire sheet; first row becomes column headers
DataTable dataTable = worksheet.ToDataTable(true);

// Iterate every row and print each cell value
foreach (DataRow row in dataTable.Rows)
{
    for (int i = 0; i < dataTable.Columns.Count; i++)
    {
        Console.Write(row[i] + "\t");
    }
    Console.WriteLine();
}

Imports IronXL
Imports System.Data

' Load the workbook from disk
Dim workbook As WorkBook = WorkBook.Load("customers.xlsx")

' Grab the default (first) worksheet
Dim worksheet As WorkSheet = workbook.DefaultWorkSheet

' Convert the entire sheet; first row becomes column headers
Dim dataTable As DataTable = worksheet.ToDataTable(True)

' Iterate every row and print each cell value
For Each row As DataRow In dataTable.Rows
    For i As Integer = 0 To dataTable.Columns.Count - 1
        Console.Write(row(i) & vbTab)
    Next
    Console.WriteLine()
Next

using IronXL;
using System.Data;

WorkBook workbook = WorkBook.Load("financial_report.xlsx");

// Access a named worksheet
WorkSheet worksheet = workbook.GetWorkSheet("Summary");

// Select only the region A1:D20 and convert it
DataTable dt = worksheet["A1:D20"].ToDataTable(true);

Console.WriteLine($"Rows: {dt.Rows.Count}, Columns: {dt.Columns.Count}");

foreach (DataRow row in dt.Rows)
{
    foreach (var item in row.ItemArray)
    {
        Console.Write(item + "\t");
    }
    Console.WriteLine();
}

Imports IronXL
Imports System.Data

Dim workbook As WorkBook = WorkBook.Load("financial_report.xlsx")

' Access a named worksheet
Dim worksheet As WorkSheet = workbook.GetWorkSheet("Summary")

' Select only the region A1:D20 and convert it
Dim dt As DataTable = worksheet("A1:D20").ToDataTable(True)

Console.WriteLine($"Rows: {dt.Rows.Count}, Columns: {dt.Columns.Count}")

For Each row As DataRow In dt.Rows
    For Each item In row.ItemArray
        Console.Write(item & vbTab)
    Next
    Console.WriteLine()
Next

using IronXL;
using System.Data;

WorkBook workbook = WorkBook.Load("multi_sheet.xlsx");

// Each worksheet becomes a DataTable inside the DataSet
DataSet dataSet = workbook.ToDataSet();

foreach (DataTable table in dataSet.Tables)
{
    Console.WriteLine($"Sheet: {table.TableName}");
    Console.WriteLine($"Rows:  {table.Rows.Count}");
    Console.WriteLine();
}

Imports IronXL
Imports System.Data

Dim workbook As WorkBook = WorkBook.Load("multi_sheet.xlsx")

' Each worksheet becomes a DataTable inside the DataSet
Dim dataSet As DataSet = workbook.ToDataSet()

For Each table As DataTable In dataSet.Tables
    Console.WriteLine($"Sheet: {table.TableName}")
    Console.WriteLine($"Rows:  {table.Rows.Count}")
    Console.WriteLine()
Next

using IronXL;
using System.Data;

// Build a sample DataTable
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("ProductID", typeof(int)));
dt.Columns.Add(new DataColumn("ProductName", typeof(string)));
dt.Columns.Add(new DataColumn("UnitPrice", typeof(decimal)));
dt.Rows.Add(1, "Widget Pro", 29.99m);
dt.Rows.Add(2, "Gadget Max", 49.99m);
dt.Rows.Add(3, "Sensor Kit", 14.50m);

// Create a new workbook and worksheet
WorkBook workbook = WorkBook.Create();
WorkSheet worksheet = workbook.CreateWorkSheet("Products");

// Write column headers from DataTable.Columns
for (int col = 0; col < dt.Columns.Count; col++)
{
    worksheet.SetCellValue(0, col, dt.Columns[col].ColumnName);
}

// Write data rows
for (int row = 0; row < dt.Rows.Count; row++)
{
    for (int col = 0; col < dt.Columns.Count; col++)
    {
        worksheet.SetCellValue(row + 1, col, dt.Rows[row][col]);
    }
}

// Save as XLSX
workbook.SaveAs("products_export.xlsx");
Console.WriteLine("Export complete.");

Imports IronXL
Imports System.Data

' Build a sample DataTable
Dim dt As New DataTable()
dt.Columns.Add(New DataColumn("ProductID", GetType(Integer)))
dt.Columns.Add(New DataColumn("ProductName", GetType(String)))
dt.Columns.Add(New DataColumn("UnitPrice", GetType(Decimal)))
dt.Rows.Add(1, "Widget Pro", 29.99D)
dt.Rows.Add(2, "Gadget Max", 49.99D)
dt.Rows.Add(3, "Sensor Kit", 14.50D)

' Create a new workbook and worksheet
Dim workbook As WorkBook = WorkBook.Create()
Dim worksheet As WorkSheet = workbook.CreateWorkSheet("Products")

' Write column headers from DataTable.Columns
For col As Integer = 0 To dt.Columns.Count - 1
    worksheet.SetCellValue(0, col, dt.Columns(col).ColumnName)
Next

' Write data rows
For row As Integer = 0 To dt.Rows.Count - 1
    For col As Integer = 0 To dt.Columns.Count - 1
        worksheet.SetCellValue(row + 1, col, dt.Rows(row)(col))
    Next
Next

' Save as XLSX
workbook.SaveAs("products_export.xlsx")
Console.WriteLine("Export complete.")

using IronXL;
using System.Data;
using System.Windows.Forms;

WorkBook workbook = WorkBook.Load("inventory.xlsx");
WorkSheet worksheet = workbook.DefaultWorkSheet;
DataTable dataTable = worksheet.ToDataTable(true);

// Bind directly to a DataGridView
dataGridViewInventory.DataSource = dataTable;

Imports IronXL
Imports System.Data
Imports System.Windows.Forms

Dim workbook As WorkBook = WorkBook.Load("inventory.xlsx")
Dim worksheet As WorkSheet = workbook.DefaultWorkSheet
Dim dataTable As DataTable = worksheet.ToDataTable(True)

' Bind directly to a DataGridView
dataGridViewInventory.DataSource = dataTable

using IronXL;
using System.Data;
using System.Linq;

WorkBook workbook = WorkBook.Load("sales.xlsx");
DataTable dt = workbook.DefaultWorkSheet.ToDataTable(true);

// Filter rows where the "Revenue" column exceeds 10000
var highRevenue = dt.AsEnumerable()
    .Where(row => row.Field<double>("Revenue") > 10000)
    .OrderByDescending(row => row.Field<double>("Revenue"))
    .ToList();

Console.WriteLine($"High-revenue records: {highRevenue.Count}");
foreach (var row in highRevenue)
{
    Console.WriteLine($"{row["Product"]}: {row["Revenue"]:C}");
}

Imports IronXL
Imports System.Data
Imports System.Linq

Dim workbook As WorkBook = WorkBook.Load("sales.xlsx")
Dim dt As DataTable = workbook.DefaultWorkSheet.ToDataTable(True)

' Filter rows where the "Revenue" column exceeds 10000
Dim highRevenue = dt.AsEnumerable() _
    .Where(Function(row) row.Field(Of Double)("Revenue") > 10000) _
    .OrderByDescending(Function(row) row.Field(Of Double)("Revenue")) _
    .ToList()

Console.WriteLine($"High-revenue records: {highRevenue.Count}")
For Each row In highRevenue
    Console.WriteLine($"{row("Product")}: {row("Revenue"):C}")
Next

using IronXL;
using System.Data;

WorkBook workbook = WorkBook.Load("large_dataset.xlsx");
WorkSheet worksheet = workbook.DefaultWorkSheet;

int batchSize = 1000;
int totalRows = worksheet.RowCount;

for (int startRow = 1; startRow <= totalRows; startRow += batchSize)
{
    int endRow = Math.Min(startRow + batchSize - 1, totalRows);
    string rangeAddress = $"A{startRow}:Z{endRow}";

    DataTable batch = worksheet[rangeAddress].ToDataTable(false);
    // Process each batch -- write to database, transform, etc.
    Console.WriteLine($"Processed rows {startRow} to {endRow}");
}

Imports IronXL
Imports System.Data

Dim workbook As WorkBook = WorkBook.Load("large_dataset.xlsx")
Dim worksheet As WorkSheet = workbook.DefaultWorkSheet

Dim batchSize As Integer = 1000
Dim totalRows As Integer = worksheet.RowCount

For startRow As Integer = 1 To totalRows Step batchSize
    Dim endRow As Integer = Math.Min(startRow + batchSize - 1, totalRows)
    Dim rangeAddress As String = $"A{startRow}:Z{endRow}"

    Dim batch As DataTable = worksheet(rangeAddress).ToDataTable(False)
    ' Process each batch -- write to database, transform, etc.
    Console.WriteLine($"Processed rows {startRow} to {endRow}")
Next

using IronXL;
using System.Data;
using Microsoft.Data.SqlClient;

WorkBook workbook = WorkBook.Load("orders.xlsx");
DataTable dataTable = workbook.DefaultWorkSheet.ToDataTable(true);

string connection細繩 = "Server=.;Database=OrdersDB;Trusted_Connection=True;";

using SqlConnection connection = new(connection細繩);
connection.Open();

using SqlBulkCopy bulkCopy = new(connection);
bulkCopy.DestinationTableName = "dbo.Orders";
bulkCopy.BatchSize = 500;
bulkCopy.BulkCopyTimeout = 60;

// Map DataTable columns to database columns
bulkCopy.ColumnMappings.Add("OrderID", "OrderID");
bulkCopy.ColumnMappings.Add("CustomerName", "CustomerName");
bulkCopy.ColumnMappings.Add("TotalAmount", "TotalAmount");

bulkCopy.WriteToServer(dataTable);
Console.WriteLine("Bulk insert complete.");

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

Dim workbook As WorkBook = WorkBook.Load("orders.xlsx")
Dim dataTable As DataTable = workbook.DefaultWorkSheet.ToDataTable(True)

Dim connection細繩 As String = "Server=.;Database=OrdersDB;Trusted_Connection=True;"

Using connection As New SqlConnection(connection細繩)
    connection.Open()

    Using bulkCopy As New SqlBulkCopy(connection)
        bulkCopy.DestinationTableName = "dbo.Orders"
        bulkCopy.BatchSize = 500
        bulkCopy.BulkCopyTimeout = 60

        ' Map DataTable columns to database columns
        bulkCopy.ColumnMappings.Add("OrderID", "OrderID")
        bulkCopy.ColumnMappings.Add("CustomerName", "CustomerName")
        bulkCopy.ColumnMappings.Add("TotalAmount", "TotalAmount")

        bulkCopy.WriteToServer(dataTable)
    End Using
End Using

Console.WriteLine("Bulk insert complete.")