如何在 VB.NET 中撰寫 Excel 文件（簡便方法）

我如何在VB.NET中開始使用Excel自動化？

要在您的VB.NET專案中開始寫入Excel文件，首先需要新增IronXL程式庫。 該過程非常簡單，可以直接在Visual Studio中完成。

首先，確保您的開發環境已準備好：

• Visual Studio 2022或更高版本。
• 一個目標.NET Framework 4.6.2或更高版本，或.NET Core/.NET 5+的活動專案。 IronXL與所有現代.NET版本相容，包括.NET 9和10。

接下來，安裝IronXL NuGet套件：

1. 打開Visual Studio中的NuGet包管理器。
2. 搜索包IronXL.Excel。
3. 點擊"安裝"。

或者，您可以通過包管理器控制台使用以下命令安裝：

Install-Package IronXL.Excel

在Visual Studio的選單中導航至NuGet包管理器以尋找適合VB.NET Excel整合的正確包。

安裝後，您就可以開始寫程式碼了。 只需在VB.NET文件的頂部新增Imports IronXL以將程式庫的類導入範圍內。

搜尋IronXL包以安裝所需程式庫，以便使用VB.NET將資料寫入Excel文件。

我如何建立一個新的Excel文件並向其中寫入資料？

從頭開始建立一個新的Excel文件是一項基本任務。 使用IronXL，您可以生成一個工作簿，新增工作表，並從任何來源（如一組物件）填充單元格。 這非常適合於導出應用程式資料或生成每日報告等任務。

讓我們看一個實際的例子。 想像我們有一個產品資料列表，想要將其導出到Excel電子表格。

Imports IronXL
Imports System.Collections.Generic
Imports System.Linq

Public Class Product
    Public Property ID As Integer
    Public Property Name As String
    Public Property Price As Decimal
End Class

Module Program
    Sub Main(args As String())
        ' 1. Prepare a list of data to write to Excel.
        Dim products As New List(Of Product) From {
            New Product With {.ID = 1, .Name = "Laptop", .Price = 1200.50D},
            New Product With {.ID = 2, .Name = "Keyboard", .Price = 75.00D},
            New Product With {.ID = 3, .Name = "Mouse", .Price = 25.99D}
        }

        ' 2. Create a new Excel workbook and select the default worksheet.
        Dim workbook As WorkBook = WorkBook.Create()
        Dim sheet As WorkSheet = workbook.DefaultWorkSheet

        ' 3. Write headers for our data.
        sheet("A1").Value = "Product ID"
        sheet("B1").Value = "Product Name"
        sheet("C1").Value = "Price"

        ' Style the header row.
        Dim headerRange = sheet.GetRange("A1:C1")
        headerRange.Style.Font.Bold = True
        headerRange.Style.SetBackgroundColor("#D3D3D3") ' Light Gray

        ' 4. Iterate through the product list and write data to subsequent rows.
        For i As Integer = 0 To products.Count - 1
            Dim product = products(i)
            Dim row = i + 2 ' Start from the second row
            sheet("A" & row).Value = product.ID
            sheet("B" & row).Value = product.Name
            sheet("C" & row).Value = product.Price
        Next

        ' 5. Apply currency formatting to the price column.
        Dim priceColumn = sheet.GetRange("C2:C" & products.Count + 1)
        priceColumn.Style.Format = "$#,##0.00"

        ' 6. Auto-size columns for better readability.
        sheet.Columns.AutoFit()

        ' 7. Save the newly created and populated workbook to a file.
        workbook.SaveAs("ProductReport.xlsx")

    End Sub
End Module

Imports IronXL
Imports System.Collections.Generic
Imports System.Linq

Public Class Product
    Public Property ID As Integer
    Public Property Name As String
    Public Property Price As Decimal
End Class

Module Program
    Sub Main(args As String())
        ' 1. Prepare a list of data to write to Excel.
        Dim products As New List(Of Product) From {
            New Product With {.ID = 1, .Name = "Laptop", .Price = 1200.50D},
            New Product With {.ID = 2, .Name = "Keyboard", .Price = 75.00D},
            New Product With {.ID = 3, .Name = "Mouse", .Price = 25.99D}
        }

        ' 2. Create a new Excel workbook and select the default worksheet.
        Dim workbook As WorkBook = WorkBook.Create()
        Dim sheet As WorkSheet = workbook.DefaultWorkSheet

        ' 3. Write headers for our data.
        sheet("A1").Value = "Product ID"
        sheet("B1").Value = "Product Name"
        sheet("C1").Value = "Price"

        ' Style the header row.
        Dim headerRange = sheet.GetRange("A1:C1")
        headerRange.Style.Font.Bold = True
        headerRange.Style.SetBackgroundColor("#D3D3D3") ' Light Gray

        ' 4. Iterate through the product list and write data to subsequent rows.
        For i As Integer = 0 To products.Count - 1
            Dim product = products(i)
            Dim row = i + 2 ' Start from the second row
            sheet("A" & row).Value = product.ID
            sheet("B" & row).Value = product.Name
            sheet("C" & row).Value = product.Price
        Next

        ' 5. Apply currency formatting to the price column.
        Dim priceColumn = sheet.GetRange("C2:C" & products.Count + 1)
        priceColumn.Style.Format = "$#,##0.00"

        ' 6. Auto-size columns for better readability.
        sheet.Columns.AutoFit()

        ' 7. Save the newly created and populated workbook to a file.
        workbook.SaveAs("ProductReport.xlsx")

    End Sub
End Module

我如何將資料寫入現有的Excel文件？

通常，您不需要建立新文件，而是將資料新增到現有文件中。 這在日誌記錄、向資料集附加記錄或更新報告時很常見。 IronXL使此過程變得和建立新文件一樣簡單。

以下範例演示如何打開現有電子表格、找到最後使用的行並附加新資料。

Imports IronXL
Imports System.Collections.Generic
Imports System.Linq

' (Assuming the Product class from the previous example is available)

Module Program
    Sub Main(args As String())
        ' Ensure the file from our previous example exists.
        If Not System.IO.File.Exists("ProductReport.xlsx") Then
            Console.WriteLine("Please run the first example to create ProductReport.xlsx")
            Return
        End If

        ' 1. Load the existing workbook from the file system.
        Dim workbook As WorkBook = WorkBook.Load("ProductReport.xlsx")
        Dim sheet As WorkSheet = workbook.DefaultWorkSheet

        ' 2. Find the first empty row to append new data.
        ' The LastRowUsed property gives us the last row with data.
        Dim lastRow = sheet.Info.LastRowUsed
        Dim newRowIndex = lastRow.RowNumber + 1

        ' 3. Define the new data to be added.
        Dim newProducts As New List(Of Product) From {
            New Product With {.ID = 4, .Name = "Monitor", .Price = 350.00D},
            New Product With {.ID = 5, .Name = "Webcam", .Price = 99.99D}
        }

        ' 4. Loop through the new data and write it to the worksheet.
        For i As Integer = 0 To newProducts.Count - 1
            Dim product = newProducts(i)
            Dim currentRow = newRowIndex + i
            sheet("A" & currentRow).Value = product.ID
            sheet("B" & currentRow).Value = product.Name
            sheet("C" & currentRow).Value = product.Price
        Next

        ' 5. Re-apply formatting and auto-fit columns to include new data.
        Dim priceColumn = sheet.GetRange("C2:C" & sheet.Info.LastRowUsed.RowNumber)
        priceColumn.Style.Format = "$#,##0.00"
        sheet.Columns.AutoFit()

        ' 6. Save the changes back to the original file.
        workbook.Save()

        ' Or save as a new file to preserve the original.
        ' workbook.SaveAs("ProductReport_Updated.xlsx")
    End Sub
End Module

Imports IronXL
Imports System.Collections.Generic
Imports System.Linq

' (Assuming the Product class from the previous example is available)

Module Program
    Sub Main(args As String())
        ' Ensure the file from our previous example exists.
        If Not System.IO.File.Exists("ProductReport.xlsx") Then
            Console.WriteLine("Please run the first example to create ProductReport.xlsx")
            Return
        End If

        ' 1. Load the existing workbook from the file system.
        Dim workbook As WorkBook = WorkBook.Load("ProductReport.xlsx")
        Dim sheet As WorkSheet = workbook.DefaultWorkSheet

        ' 2. Find the first empty row to append new data.
        ' The LastRowUsed property gives us the last row with data.
        Dim lastRow = sheet.Info.LastRowUsed
        Dim newRowIndex = lastRow.RowNumber + 1

        ' 3. Define the new data to be added.
        Dim newProducts As New List(Of Product) From {
            New Product With {.ID = 4, .Name = "Monitor", .Price = 350.00D},
            New Product With {.ID = 5, .Name = "Webcam", .Price = 99.99D}
        }

        ' 4. Loop through the new data and write it to the worksheet.
        For i As Integer = 0 To newProducts.Count - 1
            Dim product = newProducts(i)
            Dim currentRow = newRowIndex + i
            sheet("A" & currentRow).Value = product.ID
            sheet("B" & currentRow).Value = product.Name
            sheet("C" & currentRow).Value = product.Price
        Next

        ' 5. Re-apply formatting and auto-fit columns to include new data.
        Dim priceColumn = sheet.GetRange("C2:C" & sheet.Info.LastRowUsed.RowNumber)
        priceColumn.Style.Format = "$#,##0.00"
        sheet.Columns.AutoFit()

        ' 6. Save the changes back to the original file.
        workbook.Save()

        ' Or save as a new file to preserve the original.
        ' workbook.SaveAs("ProductReport_Updated.xlsx")
    End Sub
End Module

我如何將DataTable寫入Excel工作表？

對於從SQL Server等資料來源獲取資料的開發人員，一個常見任務是將DataTable的內容直接寫入Excel工作表。 IronXL通過內建的方法簡化了此過程，無需手動逐行逐列迴圈。

此範例顯示如何填充DataTable並一步將其導出到新的Excel文件。

Imports IronXL
Imports System.Data

Module Program
    Sub Main(args As String())
        ' 1. Create and populate a DataTable. This often comes from a database query.
        Dim dt As New DataTable("EmployeeData")
        dt.Columns.Add("EmployeeID", GetType(Integer))
        dt.Columns.Add("FullName", GetType(String))
        dt.Columns.Add("Department", GetType(String))
        dt.Columns.Add("HireDate", GetType(Date))

        dt.Rows.Add(101, "John Smith", "Sales", New Date(2022, 5, 20))
        dt.Rows.Add(102, "Jane Doe", "Engineering", New Date(2021, 8, 15))
        dt.Rows.Add(103, "Peter Jones", "Marketing", New Date(2023, 1, 10))

        ' 2. Create a new workbook.
        Dim workbook As WorkBook = WorkBook.Create()
        Dim sheet As WorkSheet = workbook.CreateWorkSheet("Employees")

        ' 3. Write the DataTable to the worksheet starting at cell A1.
        ' The second parameter (True) indicates that column headers should be included.
        sheet.Write(dt, "A1", True)

        ' 4. Apply some styling for a more polished look.
        sheet.Columns.AutoFit()
        Dim headerRange = sheet.GetRange("A1:D1")
        headerRange.Style.Font.Bold = True
        headerRange.Style.SetBackgroundColor("#C5D9F1") ' Light Blue

        Dim dateColumn = sheet.GetRange("D2:D" & dt.Rows.Count + 1)
        dateColumn.Style.Format = "yyyy-mm-dd"

        ' 5. Save the workbook.
        workbook.SaveAs("EmployeeDatabaseExport.xlsx")
    End Sub
End Module

Imports IronXL
Imports System.Data

Module Program
    Sub Main(args As String())
        ' 1. Create and populate a DataTable. This often comes from a database query.
        Dim dt As New DataTable("EmployeeData")
        dt.Columns.Add("EmployeeID", GetType(Integer))
        dt.Columns.Add("FullName", GetType(String))
        dt.Columns.Add("Department", GetType(String))
        dt.Columns.Add("HireDate", GetType(Date))

        dt.Rows.Add(101, "John Smith", "Sales", New Date(2022, 5, 20))
        dt.Rows.Add(102, "Jane Doe", "Engineering", New Date(2021, 8, 15))
        dt.Rows.Add(103, "Peter Jones", "Marketing", New Date(2023, 1, 10))

        ' 2. Create a new workbook.
        Dim workbook As WorkBook = WorkBook.Create()
        Dim sheet As WorkSheet = workbook.CreateWorkSheet("Employees")

        ' 3. Write the DataTable to the worksheet starting at cell A1.
        ' The second parameter (True) indicates that column headers should be included.
        sheet.Write(dt, "A1", True)

        ' 4. Apply some styling for a more polished look.
        sheet.Columns.AutoFit()
        Dim headerRange = sheet.GetRange("A1:D1")
        headerRange.Style.Font.Bold = True
        headerRange.Style.SetBackgroundColor("#C5D9F1") ' Light Blue

        Dim dateColumn = sheet.GetRange("D2:D" & dt.Rows.Count + 1)
        dateColumn.Style.Format = "yyyy-mm-dd"

        ' 5. Save the workbook.
        workbook.SaveAs("EmployeeDatabaseExport.xlsx")
    End Sub
End Module

sheet.Write()方法在這種情況下效率非常高。 它智能地將DataTable結構映射到工作表，包括（如有指定的）欄標題。 這是任何以資料為驅動的應用程式的一個強大功能，與手工迴圈相比節省了大量時間。 有關資料導入和導出的更多細節，請查看我們的不同電子表格格式之間轉換的教程。

在編寫Excel文件時，我還可以做什麼？

寫入資料僅僅是開始。 一個強大的VB.NET Excel程式庫應該提供一套完整的功能，以自動化任意電子表格任務。 IronXL提供了廣泛的功能，包括：

• 公式：程式化地設置和計算公式。 您可以將公式分配給單元格的sheet("C1").Value = "=SUM(A1:B1)"），IronXL將計算結果。
• 圖表：從工作表資料建立各種型別的圖表，以視覺化資訊。
• 資料驗證：對單元格實施規則，例如僅允許在特定範圍內的數字或從下拉列表中選擇的值。
• 條件格式化：根據單元格的值應用樣式，輔助突出重要資料點。
• 安全和保護：保護工作表或整個工作簿以密碼控管存取和防止修改。

這些功能允許完全自動化複雜的報告生成和資料分析工作流程，直接在您的.NET應用程式中。

今天就開始在VB.NET中寫入Excel文件

如所展示的，通過使用如IronXL這樣的現代程式庫，在VB.NET中寫入Excel文件變得簡單許多。 通過避免Office Interop的複雜性和依賴性，您可以構建更可靠、性能更好、且更容易部署的應用程式。 無論您是需要從頭開始建立Excel文件、附加資料到現有報告還是導出DataTable，IronXL提供了直觀且強大的API來有效完成工作。

您可以下載IronXL並使用免費試用版授權來嘗試看看它如何輕鬆整合到您的專案中。

對於有更大需求的人，請記住IronXL也是Iron Suite for .NET的一部分。 只需一個授權，您就可以獲得一套全面的程式庫來處理PDF、條碼、OCR等，為任何.NET開發人員提供非凡的價值。