C# 將列表物件導出到 Excel

Install-Package IronXL

dotnet add package IronXL

using IronXL;
using System.Data;

// Define the Employee model
record Employee(int Id, string Name, string Department, decimal Salary, DateTime HireDate);

// Create sample employee data
List<Employee> employees =
[
    new(1, "Alice Johnson", "Engineering", 95000, new DateTime(2020, 3, 15)),
    new(2, "Bob Smith",    "Marketing",   75000, new DateTime(2021, 7, 1)),
    new(3, "Carol Williams","Engineering",105000, new DateTime(2019, 11, 20))
];

// Build a DataTable from the list
DataTable dataTable = new();
dataTable.Columns.Add("Id",         typeof(int));
dataTable.Columns.Add("Name",       typeof(string));
dataTable.Columns.Add("Department", typeof(string));
dataTable.Columns.Add("Salary",     typeof(decimal));
dataTable.Columns.Add("HireDate",   typeof(DateTime));

foreach (var emp in employees)
    dataTable.Rows.Add(emp.Id, emp.Name, emp.Department, emp.Salary, emp.HireDate);

// Create an IronXL workbook and worksheet
WorkBook workbook  = new();
WorkSheet worksheet = workbook.CreateWorkSheet("Employees");

// Write headers
for (int col = 0; col < dataTable.Columns.Count; col++)
    worksheet.SetCellValue(0, col, dataTable.Columns[col].ColumnName);

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

// Save as XLSX
workbook.SaveAs("EmployeeReport.xlsx");
Console.WriteLine("EmployeeReport.xlsx saved.");

Imports IronXL
Imports System.Data

' Define the Employee model
Public Class Employee
    Public Property Id As Integer
    Public Property Name As String
    Public Property Department As String
    Public Property Salary As Decimal
    Public Property HireDate As DateTime

    Public Sub New(id As Integer, name As String, department As String, salary As Decimal, hireDate As DateTime)
        Me.Id = id
        Me.Name = name
        Me.Department = department
        Me.Salary = salary
        Me.HireDate = hireDate
    End Sub
End Class

' Create sample employee data
Dim employees As New List(Of Employee) From {
    New Employee(1, "Alice Johnson", "Engineering", 95000D, New DateTime(2020, 3, 15)),
    New Employee(2, "Bob Smith", "Marketing", 75000D, New DateTime(2021, 7, 1)),
    New Employee(3, "Carol Williams", "Engineering", 105000D, New DateTime(2019, 11, 20))
}

' Build a DataTable from the list
Dim dataTable As New DataTable()
dataTable.Columns.Add("Id", GetType(Integer))
dataTable.Columns.Add("Name", GetType(String))
dataTable.Columns.Add("Department", GetType(String))
dataTable.Columns.Add("Salary", GetType(Decimal))
dataTable.Columns.Add("HireDate", GetType(DateTime))

For Each emp In employees
    dataTable.Rows.Add(emp.Id, emp.Name, emp.Department, emp.Salary, emp.HireDate)
Next

' Create an IronXL workbook and worksheet
Dim workbook As New WorkBook()
Dim worksheet As WorkSheet = workbook.CreateWorkSheet("Employees")

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

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

' Save as XLSX
workbook.SaveAs("EmployeeReport.xlsx")
Console.WriteLine("EmployeeReport.xlsx saved.")

using IronXL;
using System.Data;

// Define the Product model with a computed property
record Product(
    string SKU,
    string ProductName,
    string Category,
    decimal Price,
    int StockLevel,
    bool IsActive,
    DateTime LastRestocked)
{
    public decimal CalculatedValue => Price * StockLevel;
}

// Build the product list
List<Product> products =
[
    new("TECH-001", "Wireless Mouse",      "Electronics",     29.99m, 150, true,  DateTime.Now.AddDays(-5)),
    new("TECH-002", "Mechanical Keyboard", "Electronics",     89.99m,  75, true,  DateTime.Now.AddDays(-12)),
    new("OFF-001",  "Desk Organizer",      "Office Supplies", 15.99m,   0, false, DateTime.Now.AddMonths(-1))
];

// Populate a DataTable
DataTable dt = new();
dt.Columns.Add("SKU",             typeof(string));
dt.Columns.Add("ProductName",     typeof(string));
dt.Columns.Add("Category",        typeof(string));
dt.Columns.Add("Price",           typeof(decimal));
dt.Columns.Add("StockLevel",      typeof(int));
dt.Columns.Add("IsActive",        typeof(bool));
dt.Columns.Add("LastRestocked",   typeof(DateTime));
dt.Columns.Add("CalculatedValue", typeof(decimal));

foreach (var p in products)
    dt.Rows.Add(p.SKU, p.ProductName, p.Category, p.Price,
                p.StockLevel, p.IsActive, p.LastRestocked, p.CalculatedValue);

// Create the workbook
WorkBook  wb = WorkBook.Create();
WorkSheet ws = wb.CreateWorkSheet("Inventory");

// Write column headers
string[] headers = ["SKU","ProductName","Category","Price",
                    "StockLevel","IsActive","LastRestocked","CalculatedValue"];
for (int col = 0; col < headers.Length; col++)
    ws.SetCellValue(0, col, headers[col]);

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

// Auto-size columns for readability
for (int col = 0; col < headers.Length; col++)
    ws.AutoSizeColumn(col);

wb.SaveAs("ProductInventory.xlsx");
Console.WriteLine("ProductInventory.xlsx saved.");

Imports IronXL
Imports System.Data

' Define the Product model with a computed property
Public Class Product
    Public Property SKU As String
    Public Property ProductName As String
    Public Property Category As String
    Public Property Price As Decimal
    Public Property StockLevel As Integer
    Public Property IsActive As Boolean
    Public Property LastRestocked As DateTime

    Public ReadOnly Property CalculatedValue As Decimal
        Get
            Return Price * StockLevel
        End Get
    End Property

    Public Sub New(sku As String, productName As String, category As String, price As Decimal, stockLevel As Integer, isActive As Boolean, lastRestocked As DateTime)
        Me.SKU = sku
        Me.ProductName = productName
        Me.Category = category
        Me.Price = price
        Me.StockLevel = stockLevel
        Me.IsActive = isActive
        Me.LastRestocked = lastRestocked
    End Sub
End Class

' Build the product list
Dim products As New List(Of Product) From {
    New Product("TECH-001", "Wireless Mouse", "Electronics", 29.99D, 150, True, DateTime.Now.AddDays(-5)),
    New Product("TECH-002", "Mechanical Keyboard", "Electronics", 89.99D, 75, True, DateTime.Now.AddDays(-12)),
    New Product("OFF-001", "Desk Organizer", "Office Supplies", 15.99D, 0, False, DateTime.Now.AddMonths(-1))
}

' Populate a DataTable
Dim dt As New DataTable()
dt.Columns.Add("SKU", GetType(String))
dt.Columns.Add("ProductName", GetType(String))
dt.Columns.Add("Category", GetType(String))
dt.Columns.Add("Price", GetType(Decimal))
dt.Columns.Add("StockLevel", GetType(Integer))
dt.Columns.Add("IsActive", GetType(Boolean))
dt.Columns.Add("LastRestocked", GetType(DateTime))
dt.Columns.Add("CalculatedValue", GetType(Decimal))

For Each p In products
    dt.Rows.Add(p.SKU, p.ProductName, p.Category, p.Price, p.StockLevel, p.IsActive, p.LastRestocked, p.CalculatedValue)
Next

' Create the workbook
Dim wb As WorkBook = WorkBook.Create()
Dim ws As WorkSheet = wb.CreateWorkSheet("Inventory")

' Write column headers
Dim headers As String() = {"SKU", "ProductName", "Category", "Price", "StockLevel", "IsActive", "LastRestocked", "CalculatedValue"}
For col As Integer = 0 To headers.Length - 1
    ws.SetCellValue(0, col, headers(col))
Next

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

' Auto-size columns for readability
For col As Integer = 0 To headers.Length - 1
    ws.AutoSizeColumn(col)
Next

wb.SaveAs("ProductInventory.xlsx")
Console.WriteLine("ProductInventory.xlsx saved.")

using IronXL;

WorkBook  wb = WorkBook.Load("ProductInventory.xlsx");
WorkSheet ws = wb.DefaultWorkSheet;

// Bold header row with a blue background and white text
Range headerRange = ws["A1:H1"];
headerRange.Style.Font.Bold            = true;
headerRange.Style.BackgroundColor      = "#4472C4";
headerRange.Style.Font.Color           = "#FFFFFF";

// Format the Price column as currency
Range priceColumn = ws["D2:D100"];
priceColumn.Style.NumberFormat = "$#,##0.00";

// Highlight low-stock rows in red
for (int row = 2; row <= 4; row++)
{
    var stockCell = ws[$"E{row}"];
    if (stockCell.IntValue < 10)
        stockCell.Style.BackgroundColor = "#FF6B6B";
}

wb.SaveAs("FormattedInventory.xlsx");
Console.WriteLine("FormattedInventory.xlsx saved.");

Imports IronXL

Dim wb As WorkBook = WorkBook.Load("ProductInventory.xlsx")
Dim ws As WorkSheet = wb.DefaultWorkSheet

' Bold header row with a blue background and white text
Dim headerRange As Range = ws("A1:H1")
headerRange.Style.Font.Bold = True
headerRange.Style.BackgroundColor = "#4472C4"
headerRange.Style.Font.Color = "#FFFFFF"

' Format the Price column as currency
Dim priceColumn As Range = ws("D2:D100")
priceColumn.Style.NumberFormat = "$#,##0.00"

' Highlight low-stock rows in red
For row As Integer = 2 To 4
    Dim stockCell = ws($"E{row}")
    If stockCell.IntValue < 10 Then
        stockCell.Style.BackgroundColor = "#FF6B6B"
    End If
Next

wb.SaveAs("FormattedInventory.xlsx")
Console.WriteLine("FormattedInventory.xlsx saved.")