IronXL在C#中有何用途？

IronXL是一個針對C#的強大程式庫，使開發者能夠輕鬆地建立、編輯和讀取Excel文件。它支援生成具有高級格式化、公式及資料整合的專業Excel報表。

IronXL提供哪些功能來生成Excel報表？

IronXL提供的功能包括單元格格式化、公式應用、資料驗證、圖表建立以及無縫的資料庫整合，以便於生成綜合的Excel報表。

IronXL是否支持資料庫整合？

是的，IronXL支援資料庫整合，讓您可以直接從資料庫提取資料並將其納入Excel報表中，簡化資料分析及報告。

IronXL能否處理複雜的Excel公式？

IronXL能處理範圍廣泛的Excel公式，使開發者能在Excel報表中執行複雜的計算及自動化資料處理。

是否可以使用IronXL格式化Excel單元格？

IronXL提供了廣泛的格式化能力，讓您可以自定義單元格外觀，包括字體樣式、顏色、邊框等，來建立視覺上吸引人的Excel報表。

可以使用IronXL在Excel中建立圖表嗎？

是的，IronXL允許您在Excel試算表中建立多種圖表，幫助以視覺化的方式呈現資料並增強報告的可解釋性。

IronXL如何簡化Excel報表生成的過程？

IronXL通過提供一整套的功能簡化了Excel報表生成的過程，涵蓋資料輸入、格式化、計算及輸出，這一切都在C#的程式環境中進行。

IronXL能否高效處理大型資料集？

IronXL經過性能優化，使其能高效地處理大型資料集，這使其在生成複雜報表時，既不妨礙速度又不減可靠性。

IronXL專為哪種程式語言設計？

IronXL專為使用C#而設計，提供了無縫整合及功能性，供在.NET架構下工作的開發者使用。

C# Excel 程式庫

Q: 如何使用IronXL建立Excel報表？

要使用IronXL建立Excel報表，您可以利用其強大的API來新增資料、格式化單元格、應用公式及整合資料庫，這一切均在C#環境中完成。

Explore IronXL C# .NET 的 Excel 試算表程式庫開始免費試用

無需 Interop 即可讀取 Excel 檔案

查看所有程式碼範例

using IronXL;
using System;
using System.Linq;

// Supported for XLSX, XLS, XLSM, XLTX, CSV and TSV
WorkBook workBook = WorkBook.Load("sample.xlsx");

// Select worksheet at index 0
WorkSheet workSheet = workBook.WorkSheets[0];

// Get any existing worksheet
WorkSheet firstSheet = workBook.DefaultWorkSheet;

// Select a cell and return the converted value
int cellValue = workSheet["A2"].IntValue;

// Read from ranges of cells elegantly.
foreach (var cell in workSheet["A2:A10"])
{
    Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
}

// Calculate aggregate values such as Min, Max and Sum
decimal sum = workSheet["A2:A10"].Sum();

// Linq compatible
decimal max = workSheet["A2:A10"].Max(c => c.DecimalValue);

using IronXL;
using System;
using System.Linq;

// Supported for XLSX, XLS, XLSM, XLTX, CSV and TSV
WorkBook workBook = WorkBook.Load("sample.xlsx");

// Select worksheet at index 0
WorkSheet workSheet = workBook.WorkSheets[0];

// Get any existing worksheet
WorkSheet firstSheet = workBook.DefaultWorkSheet;

// Select a cell and return the converted value
int cellValue = workSheet["A2"].IntValue;

// Read from ranges of cells elegantly.
foreach (var cell in workSheet["A2:A10"])
{
    Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
}

// Calculate aggregate values such as Min, Max and Sum
decimal sum = workSheet["A2:A10"].Sum();

// Linq compatible
decimal max = workSheet["A2:A10"].Max(c => c.DecimalValue);

PM >Install-Package IronXL.Excel

Install-Package IronXL.Excel

USING IRONXL

在C#中使用IronXL建立Excel報表

Curtis Chau

Updated: 2026年5月8日

在C#中建立Excel報表是現代商務和.NET應用程式中的基本需求。無論您是在產生財務報表、銷售分析或庫存儀表板，自動化建立Excel檔案和Excel試算表的過程，可以節省數小時的手動工作並減少錯誤。

IronXL 提供了一個功能強大且直觀的解決方案，使開發者可以在不需要Microsoft Office、MS Excel或傳統Interop依賴的情況下，用C#建立Excel報告。與依賴Excel應用程式的OLE Automation或其他方法不同，IronXL能夠直接在程式碼中生成Excel工作簿和Excel資料，只需一行程式碼。在本指南中，我們將教您如何使用IronXL建立Excel報表，並提供簡單易學的範例程式碼，讓您輕鬆整合到自己的專案中！

什麼是IronXL及其為何用於生成Excel檔案？

IronXL是一個.NET Excel程式庫，允許開發人員從C#或Visual Basic源程式碼直接建立、讀取Excel試算表及操作Excel檔案。 IronXL與依賴完整Excel應用或通過Open XML SDK逆向工程的方法不同，無需安裝Excel或第三方依賴即可在Windows、Linux、macOS和雲環境中工作。這使它非常適合伺服器端的Excel報告、自動化工作流程以及建基於.NET Core或.NET Framework的Web應用。

對於從手動Excel過程、老式程式庫或需要瀏覽各種XML命名空間的Open XML生產工具工作流程過渡的團隊，IronXL提供了一個直觀的API。它同時支持傳統的XLS文件和現代的XLSX文件，後者基本上是包含XML文件和各種文件夾的ZIP文件。無論您是想生成新的Excel工作簿、操作多個工作頁，還是從外部程式碼載入Excel資料，IronXL都能大幅簡化過程，無需了解底層格式。

使用IronXL開始建立Excel檔案

設置IronXL來生成Excel報告只需幾分鐘。通過Visual Studio中的NuGet Package Manager安裝程式庫：

PM > Install-Package IronXL.Excel

Install-Package IronXL.Excel

立即下載IronXL並開始自動生成您的Excel報表。

安裝完成後，建立您的第一個Excel報告只需要幾行程式碼：

using IronXL;
// Create a new Excel workbook for reports
WorkBook workBook = WorkBook.Create(ExcelFileFormat.XLSX);
// Add a worksheet for the report
WorkSheet reportSheet = workBook.CreateWorkSheet("Monthly Report");
// Add a title
reportSheet["A1"].Value = "Sales Report - January 2024";
reportSheet["A1"].Style.Font.Bold = true;
// Save the Excel report
workBook.SaveAs("MonthlyReport.xlsx");

using IronXL;
// Create a new Excel workbook for reports
WorkBook workBook = WorkBook.Create(ExcelFileFormat.XLSX);
// Add a worksheet for the report
WorkSheet reportSheet = workBook.CreateWorkSheet("Monthly Report");
// Add a title
reportSheet["A1"].Value = "Sales Report - January 2024";
reportSheet["A1"].Style.Font.Bold = true;
// Save the Excel report
workBook.SaveAs("MonthlyReport.xlsx");

Imports IronXL

' Create a new Excel workbook for reports
Dim workBook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
' Add a worksheet for the report
Dim reportSheet As WorkSheet = workBook.CreateWorkSheet("Monthly Report")
' Add a title
reportSheet("A1").Value = "Sales Report - January 2024"
reportSheet("A1").Style.Font.Bold = True
' Save the Excel report
workBook.SaveAs("MonthlyReport.xlsx")

Imports IronXL

' Create a new Excel workbook for reports
Dim workBook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
' Add a worksheet for the report
Dim reportSheet As WorkSheet = workBook.CreateWorkSheet("Monthly Report")
' Add a title
reportSheet("A1").Value = "Sales Report - January 2024"
reportSheet("A1").Style.Font.Bold = True
' Save the Excel report
workBook.SaveAs("MonthlyReport.xlsx")

這段程式碼建立了一個帶有格式化標題的新Excel報告文件。熟悉的儲存格參考語法(reportSheet["A1"])使開發人員能夠輕鬆地指定資料應出現的位置，就像手動操作Excel一樣。

輸出

在C#中使用IronXL建立Excel報表：影像1 - 範例Excel輸出

從多個來源載入資料

現實世界的Excel報告很少使用靜態資料。 IronXL擅於整合來自各種格式、API、新DataTable來源或甚至不同的XML檔案的Excel資料。這使其非常適合伺服器端或Web應用中的動態C# Excel報告生成。

資料庫整合

對於資料庫驅動的Excel報表，IronXL能無縫地與ADO.NET DataTables協同工作：

using System.Data;
using System.Data.SqlClient;
// Fetch data from database
string connectionString = "Server=localhost;Database=Sales;Integrated Security=true;";
DataTable salesData = new DataTable();
using (SqlConnection conn = new SqlConnection(connectionString))
{
    SqlDataAdapter adapter = new SqlDataAdapter(
        "SELECT ProductName, Quantity, Revenue FROM MonthlySales", conn);
    adapter.Fill(salesData);
}
// Create Excel report from DataTable
WorkBook workBook = WorkBook.Create();
WorkSheet sheet = workBook.CreateWorkSheet("Sales Data");
// Add headers
sheet["A1"].Value = "Product";
sheet["B1"].Value = "Quantity";
sheet["C1"].Value = "Revenue";
// Populate data
int row = 2;
foreach (DataRow dataRow in salesData.Rows)
{
    sheet[$"A{row}"].Value = dataRow["ProductName"];
    sheet[$"B{row}"].Value = dataRow["Quantity"];
    sheet[$"C{row}"].Value = dataRow["Revenue"];
    row++;
}

using System.Data;
using System.Data.SqlClient;
// Fetch data from database
string connectionString = "Server=localhost;Database=Sales;Integrated Security=true;";
DataTable salesData = new DataTable();
using (SqlConnection conn = new SqlConnection(connectionString))
{
    SqlDataAdapter adapter = new SqlDataAdapter(
        "SELECT ProductName, Quantity, Revenue FROM MonthlySales", conn);
    adapter.Fill(salesData);
}
// Create Excel report from DataTable
WorkBook workBook = WorkBook.Create();
WorkSheet sheet = workBook.CreateWorkSheet("Sales Data");
// Add headers
sheet["A1"].Value = "Product";
sheet["B1"].Value = "Quantity";
sheet["C1"].Value = "Revenue";
// Populate data
int row = 2;
foreach (DataRow dataRow in salesData.Rows)
{
    sheet[$"A{row}"].Value = dataRow["ProductName"];
    sheet[$"B{row}"].Value = dataRow["Quantity"];
    sheet[$"C{row}"].Value = dataRow["Revenue"];
    row++;
}

Imports System.Data
Imports System.Data.SqlClient

' Fetch data from database
Dim connectionString As String = "Server=localhost;Database=Sales;Integrated Security=true;"
Dim salesData As New DataTable()
Using conn As New SqlConnection(connectionString)
    Dim adapter As New SqlDataAdapter("SELECT ProductName, Quantity, Revenue FROM MonthlySales", conn)
    adapter.Fill(salesData)
End Using

' Create Excel report from DataTable
Dim workBook As WorkBook = WorkBook.Create()
Dim sheet As WorkSheet = workBook.CreateWorkSheet("Sales Data")

' Add headers
sheet("A1").Value = "Product"
sheet("B1").Value = "Quantity"
sheet("C1").Value = "Revenue"

' Populate data
Dim row As Integer = 2
For Each dataRow As DataRow In salesData.Rows
    sheet($"A{row}").Value = dataRow("ProductName")
    sheet($"B{row}").Value = dataRow("Quantity")
    sheet($"C{row}").Value = dataRow("Revenue")
    row += 1
Next

Imports System.Data
Imports System.Data.SqlClient

' Fetch data from database
Dim connectionString As String = "Server=localhost;Database=Sales;Integrated Security=true;"
Dim salesData As New DataTable()
Using conn As New SqlConnection(connectionString)
    Dim adapter As New SqlDataAdapter("SELECT ProductName, Quantity, Revenue FROM MonthlySales", conn)
    adapter.Fill(salesData)
End Using

' Create Excel report from DataTable
Dim workBook As WorkBook = WorkBook.Create()
Dim sheet As WorkSheet = workBook.CreateWorkSheet("Sales Data")

' Add headers
sheet("A1").Value = "Product"
sheet("B1").Value = "Quantity"
sheet("C1").Value = "Revenue"

' Populate data
Dim row As Integer = 2
For Each dataRow As DataRow In salesData.Rows
    sheet($"A{row}").Value = dataRow("ProductName")
    sheet($"B{row}").Value = dataRow("Quantity")
    sheet($"C{row}").Value = dataRow("Revenue")
    row += 1
Next

此方法可將銷售資訊直接從SQL Server載入到您的Excel報告中。 DataTable整合意味著您可以在不修改的情況下使用現有的資料存取程式碼。對於更複雜的場景，請查看如何從SQL資料庫載入Excel。

使用集合

對於記憶體中的資料、來自API響應的Excel資料或新DataTable，集合能使Excel工作頁易於填充，無需Microsoft Excel或第三方依賴：

var salesRecords = new List<SalesRecord>
{
    new SalesRecord { Product = "Widget A", Units = 150, Price = 29.99m },
    new SalesRecord { Product = "Widget B", Units = 82, Price = 49.99m }
};
// Convert collection to Excel
for (int i = 0; i < salesRecords.Count; i++)
{
    sheet[$"A{i+2}"].Value = salesRecords[i].Product;
    sheet[$"B{i+2}"].Value = salesRecords[i].Units;
    sheet[$"C{i+2}"].Value = salesRecords[i].Price;
}

var salesRecords = new List<SalesRecord>
{
    new SalesRecord { Product = "Widget A", Units = 150, Price = 29.99m },
    new SalesRecord { Product = "Widget B", Units = 82, Price = 49.99m }
};
// Convert collection to Excel
for (int i = 0; i < salesRecords.Count; i++)
{
    sheet[$"A{i+2}"].Value = salesRecords[i].Product;
    sheet[$"B{i+2}"].Value = salesRecords[i].Units;
    sheet[$"C{i+2}"].Value = salesRecords[i].Price;
}

Imports System.Collections.Generic

Dim salesRecords As New List(Of SalesRecord) From {
    New SalesRecord With {.Product = "Widget A", .Units = 150, .Price = 29.99D},
    New SalesRecord With {.Product = "Widget B", .Units = 82, .Price = 49.99D}
}

' Convert collection to Excel
For i As Integer = 0 To salesRecords.Count - 1
    sheet($"A{i + 2}").Value = salesRecords(i).Product
    sheet($"B{i + 2}").Value = salesRecords(i).Units
    sheet($"C{i + 2}").Value = salesRecords(i).Price
Next

Imports System.Collections.Generic

Dim salesRecords As New List(Of SalesRecord) From {
    New SalesRecord With {.Product = "Widget A", .Units = 150, .Price = 29.99D},
    New SalesRecord With {.Product = "Widget B", .Units = 82, .Price = 49.99D}
}

' Convert collection to Excel
For i As Integer = 0 To salesRecords.Count - 1
    sheet($"A{i + 2}").Value = salesRecords(i).Product
    sheet($"B{i + 2}").Value = salesRecords(i).Units
    sheet($"C{i + 2}").Value = salesRecords(i).Price
Next

此方法允許在.NET應用程式中直接用C#建立Excel報表，使生成多個工作頁或XLSX檔案的過程比手動OLE Automation或反向工程XML檔案要簡單得多。

輸出

在C#中使用IronXL建立Excel報表：影像2 - 從資料輸出的範例工作頁

格式化專業的Excel報表

僅有原始資料並不能構成一份專業的報表。 IronXL提供全面的儲存格格式選項，以建立精緻的商業就緒Excel文件。以下程式碼展示了使用IronXL的簡單性：

using IronXL;
using IronXL.Styles;

class Program
{
    static void Main(string[] args)
    {
        // Create a new workbook
        var workbook = WorkBook.Create(ExcelFileFormat.XLSX);
        // Add a new worksheet
        var sheet = workbook.CreateWorkSheet("MySheet");
        // Add header values
        sheet["A1"].Value = "Product";
        sheet["B1"].Value = "Quantity";
        sheet["C1"].Value = "Price";
        // Add sample data rows
        sheet["A2"].Value = "Laptop";
        sheet["B2"].Value = 5;
        sheet["C2"].Value = 1299.99;
        sheet["A3"].Value = "Headphones";
        sheet["B3"].Value = 15;
        sheet["C3"].Value = 199.50;
        sheet["A4"].Value = "Keyboard";
        sheet["B4"].Value = 10;
        sheet["C4"].Value = 89.99;
        sheet["A5"].Value = "Monitor";
        sheet["B5"].Value = 7;
        sheet["C5"].Value = 249.00;
        // Header formatting
        var headerRange = sheet["A1:C1"];
        headerRange.Style.Font.Bold = true;
        headerRange.Style.SetBackgroundColor("#4472C4");
        headerRange.Style.Font.Color = "#FFFFFF";
        headerRange.Style.BottomBorder.Type = BorderType.Thick;
        // Number formatting for currency
        sheet["C:C"].FormatString = "$#,##0.00";
        // Alternating row colors for readability
        for (int row = 2; row <= 10; row++)
        {
            if (row % 2 == 0)
            {
                sheet[$"A{row}:C{row}"].Style.SetBackgroundColor("#F2F2F2");
            }
        }
        // Column width adjustment
        sheet.Columns[0].Width = 15 * 256; // Width in 1/256th of character width
        sheet.Columns[2].Width = 12 * 256;
        // Add borders around data
        var dataRange = sheet["A1:C10"];
        dataRange.Style.TopBorder.Type = BorderType.Thin;
        dataRange.Style.RightBorder.Type = BorderType.Thin;
        dataRange.Style.BottomBorder.Type = BorderType.Thin;
        dataRange.Style.LeftBorder.Type = BorderType.Thin;
        // Save the workbook to a file
        workbook.SaveAs("MyWorkbook.xlsx");
    }
}

using IronXL;
using IronXL.Styles;

class Program
{
    static void Main(string[] args)
    {
        // Create a new workbook
        var workbook = WorkBook.Create(ExcelFileFormat.XLSX);
        // Add a new worksheet
        var sheet = workbook.CreateWorkSheet("MySheet");
        // Add header values
        sheet["A1"].Value = "Product";
        sheet["B1"].Value = "Quantity";
        sheet["C1"].Value = "Price";
        // Add sample data rows
        sheet["A2"].Value = "Laptop";
        sheet["B2"].Value = 5;
        sheet["C2"].Value = 1299.99;
        sheet["A3"].Value = "Headphones";
        sheet["B3"].Value = 15;
        sheet["C3"].Value = 199.50;
        sheet["A4"].Value = "Keyboard";
        sheet["B4"].Value = 10;
        sheet["C4"].Value = 89.99;
        sheet["A5"].Value = "Monitor";
        sheet["B5"].Value = 7;
        sheet["C5"].Value = 249.00;
        // Header formatting
        var headerRange = sheet["A1:C1"];
        headerRange.Style.Font.Bold = true;
        headerRange.Style.SetBackgroundColor("#4472C4");
        headerRange.Style.Font.Color = "#FFFFFF";
        headerRange.Style.BottomBorder.Type = BorderType.Thick;
        // Number formatting for currency
        sheet["C:C"].FormatString = "$#,##0.00";
        // Alternating row colors for readability
        for (int row = 2; row <= 10; row++)
        {
            if (row % 2 == 0)
            {
                sheet[$"A{row}:C{row}"].Style.SetBackgroundColor("#F2F2F2");
            }
        }
        // Column width adjustment
        sheet.Columns[0].Width = 15 * 256; // Width in 1/256th of character width
        sheet.Columns[2].Width = 12 * 256;
        // Add borders around data
        var dataRange = sheet["A1:C10"];
        dataRange.Style.TopBorder.Type = BorderType.Thin;
        dataRange.Style.RightBorder.Type = BorderType.Thin;
        dataRange.Style.BottomBorder.Type = BorderType.Thin;
        dataRange.Style.LeftBorder.Type = BorderType.Thin;
        // Save the workbook to a file
        workbook.SaveAs("MyWorkbook.xlsx");
    }
}

Imports IronXL
Imports IronXL.Styles

Module Program
    Sub Main(args As String())
        ' Create a new workbook
        Dim workbook = WorkBook.Create(ExcelFileFormat.XLSX)
        ' Add a new worksheet
        Dim sheet = workbook.CreateWorkSheet("MySheet")
        ' Add header values
        sheet("A1").Value = "Product"
        sheet("B1").Value = "Quantity"
        sheet("C1").Value = "Price"
        ' Add sample data rows
        sheet("A2").Value = "Laptop"
        sheet("B2").Value = 5
        sheet("C2").Value = 1299.99
        sheet("A3").Value = "Headphones"
        sheet("B3").Value = 15
        sheet("C3").Value = 199.5
        sheet("A4").Value = "Keyboard"
        sheet("B4").Value = 10
        sheet("C4").Value = 89.99
        sheet("A5").Value = "Monitor"
        sheet("B5").Value = 7
        sheet("C5").Value = 249.0
        ' Header formatting
        Dim headerRange = sheet("A1:C1")
        headerRange.Style.Font.Bold = True
        headerRange.Style.SetBackgroundColor("#4472C4")
        headerRange.Style.Font.Color = "#FFFFFF"
        headerRange.Style.BottomBorder.Type = BorderType.Thick
        ' Number formatting for currency
        sheet("C:C").FormatString = "$#,##0.00"
        ' Alternating row colors for readability
        For row As Integer = 2 To 10
            If row Mod 2 = 0 Then
                sheet($"A{row}:C{row}").Style.SetBackgroundColor("#F2F2F2")
            End If
        Next
        ' Column width adjustment
        sheet.Columns(0).Width = 15 * 256 ' Width in 1/256th of character width
        sheet.Columns(2).Width = 12 * 256
        ' Add borders around data
        Dim dataRange = sheet("A1:C10")
        dataRange.Style.TopBorder.Type = BorderType.Thin
        dataRange.Style.RightBorder.Type = BorderType.Thin
        dataRange.Style.BottomBorder.Type = BorderType.Thin
        dataRange.Style.LeftBorder.Type = BorderType.Thin
        ' Save the workbook to a file
        workbook.SaveAs("MyWorkbook.xlsx")
    End Sub
End Module

Imports IronXL
Imports IronXL.Styles

Module Program
    Sub Main(args As String())
        ' Create a new workbook
        Dim workbook = WorkBook.Create(ExcelFileFormat.XLSX)
        ' Add a new worksheet
        Dim sheet = workbook.CreateWorkSheet("MySheet")
        ' Add header values
        sheet("A1").Value = "Product"
        sheet("B1").Value = "Quantity"
        sheet("C1").Value = "Price"
        ' Add sample data rows
        sheet("A2").Value = "Laptop"
        sheet("B2").Value = 5
        sheet("C2").Value = 1299.99
        sheet("A3").Value = "Headphones"
        sheet("B3").Value = 15
        sheet("C3").Value = 199.5
        sheet("A4").Value = "Keyboard"
        sheet("B4").Value = 10
        sheet("C4").Value = 89.99
        sheet("A5").Value = "Monitor"
        sheet("B5").Value = 7
        sheet("C5").Value = 249.0
        ' Header formatting
        Dim headerRange = sheet("A1:C1")
        headerRange.Style.Font.Bold = True
        headerRange.Style.SetBackgroundColor("#4472C4")
        headerRange.Style.Font.Color = "#FFFFFF"
        headerRange.Style.BottomBorder.Type = BorderType.Thick
        ' Number formatting for currency
        sheet("C:C").FormatString = "$#,##0.00"
        ' Alternating row colors for readability
        For row As Integer = 2 To 10
            If row Mod 2 = 0 Then
                sheet($"A{row}:C{row}").Style.SetBackgroundColor("#F2F2F2")
            End If
        Next
        ' Column width adjustment
        sheet.Columns(0).Width = 15 * 256 ' Width in 1/256th of character width
        sheet.Columns(2).Width = 12 * 256
        ' Add borders around data
        Dim dataRange = sheet("A1:C10")
        dataRange.Style.TopBorder.Type = BorderType.Thin
        dataRange.Style.RightBorder.Type = BorderType.Thin
        dataRange.Style.BottomBorder.Type = BorderType.Thin
        dataRange.Style.LeftBorder.Type = BorderType.Thin
        ' Save the workbook to a file
        workbook.SaveAs("MyWorkbook.xlsx")
    End Sub
End Module

這些格式選項將基本資料轉化為專業報告。風格API涵蓋從字體和顏色到邊界和對齊的一切，提供對Excel報表外觀的完全控制。對於進階格式需求，請探索條件格式，以自動突出顯示重要資料。

在C#中使用IronXL建立Excel報表：影像3 - 格式化的Excel輸出

使用公式進行動態Excel報表計算

Excel的強大之處在於其公式，IronXL完全支援Excel公式建立：

// Add formula for row totals
sheet["D1"].Value = "Total";
sheet["D2"].Formula = "=B2*C2";
// Copy formula down the column
for (int row = 3; row <= 10; row++)
{
    sheet[$"D{row}"].Formula = $"=B{row}*C{row}";
}
// Add summary formulas
sheet["A12"].Value = "Summary";
sheet["B12"].Formula = "=SUM(B2:B10)";
sheet["C12"].Formula = "=AVERAGE(C2:C10)";
sheet["D12"].Formula = "=SUM(D2:D10)";

// Add formula for row totals
sheet["D1"].Value = "Total";
sheet["D2"].Formula = "=B2*C2";
// Copy formula down the column
for (int row = 3; row <= 10; row++)
{
    sheet[$"D{row}"].Formula = $"=B{row}*C{row}";
}
// Add summary formulas
sheet["A12"].Value = "Summary";
sheet["B12"].Formula = "=SUM(B2:B10)";
sheet["C12"].Formula = "=AVERAGE(C2:C10)";
sheet["D12"].Formula = "=SUM(D2:D10)";

' Add formula for row totals
sheet("D1").Value = "Total"
sheet("D2").Formula = "=B2*C2"
' Copy formula down the column
For row As Integer = 3 To 10
    sheet($"D{row}").Formula = $"=B{row}*C{row}"
Next
' Add summary formulas
sheet("A12").Value = "Summary"
sheet("B12").Formula = "=SUM(B2:B10)"
sheet("C12").Formula = "=AVERAGE(C2:C10)"
sheet("D12").Formula = "=SUM(D2:D10)"

' Add formula for row totals
sheet("D1").Value = "Total"
sheet("D2").Formula = "=B2*C2"
' Copy formula down the column
For row As Integer = 3 To 10
    sheet($"D{row}").Formula = $"=B{row}*C{row}"
Next
' Add summary formulas
sheet("A12").Value = "Summary"
sheet("B12").Formula = "=SUM(B2:B10)"
sheet("C12").Formula = "=AVERAGE(C2:C10)"
sheet("D12").Formula = "=SUM(D2:D10)"

公式支援包括所有標準的Excel函式，如SUM, AVERAGE, IF, VLOOKUP等。 IronXL自動處理公式依賴性和計算順序，使具有復雜計算的Excel報告生成變得簡單明瞭。了解更多IronXL中的數學函式。

在C#中使用IronXL建立Excel報表：影像4 - 含公式的範例輸出

基於範本的報表

對於周期性的Excel報表或標準化的工作簿，IronXL支援基於範本的生成，允許開發者從範本中建立Excel文件，無需處理Open XML SDK、rels文件或各種文件夾：

// Load existing template
Workbook templateBook = Workbook.Load("ReportTemplate.xlsx");
Worksheet templateSheet = templateBook.DefaultWorksheet;
// Find and replace template markers
foreach (var cell in templateSheet["A1:Z100"])
{
    if (cell.Text.Contains("{{CompanyName}}"))
        cell.Value = cell.Text.Replace("{{CompanyName}}", "Acme Corp");
    if (cell.Text.Contains("{{ReportDate}}"))
        cell.Value = cell.Text.Replace("{{ReportDate}}", DateTime.Now.ToString("MMMM yyyy"));
}
// Save as new report
templateBook.SaveAs($"Report_{DateTime.Now:yyyyMMdd}.xlsx");

// Load existing template
Workbook templateBook = Workbook.Load("ReportTemplate.xlsx");
Worksheet templateSheet = templateBook.DefaultWorksheet;
// Find and replace template markers
foreach (var cell in templateSheet["A1:Z100"])
{
    if (cell.Text.Contains("{{CompanyName}}"))
        cell.Value = cell.Text.Replace("{{CompanyName}}", "Acme Corp");
    if (cell.Text.Contains("{{ReportDate}}"))
        cell.Value = cell.Text.Replace("{{ReportDate}}", DateTime.Now.ToString("MMMM yyyy"));
}
// Save as new report
templateBook.SaveAs($"Report_{DateTime.Now:yyyyMMdd}.xlsx");

Imports System

' Load existing template
Dim templateBook As Workbook = Workbook.Load("ReportTemplate.xlsx")
Dim templateSheet As Worksheet = templateBook.DefaultWorksheet
' Find and replace template markers
For Each cell In templateSheet("A1:Z100")
    If cell.Text.Contains("{{CompanyName}}") Then
        cell.Value = cell.Text.Replace("{{CompanyName}}", "Acme Corp")
    End If
    If cell.Text.Contains("{{ReportDate}}") Then
        cell.Value = cell.Text.Replace("{{ReportDate}}", DateTime.Now.ToString("MMMM yyyy"))
    End If
Next
' Save as new report
templateBook.SaveAs($"Report_{DateTime.Now:yyyyMMdd}.xlsx")

Imports System

' Load existing template
Dim templateBook As Workbook = Workbook.Load("ReportTemplate.xlsx")
Dim templateSheet As Worksheet = templateBook.DefaultWorksheet
' Find and replace template markers
For Each cell In templateSheet("A1:Z100")
    If cell.Text.Contains("{{CompanyName}}") Then
        cell.Value = cell.Text.Replace("{{CompanyName}}", "Acme Corp")
    End If
    If cell.Text.Contains("{{ReportDate}}") Then
        cell.Value = cell.Text.Replace("{{ReportDate}}", DateTime.Now.ToString("MMMM yyyy"))
    End If
Next
' Save as new report
templateBook.SaveAs($"Report_{DateTime.Now:yyyyMMdd}.xlsx")

此方法保持一致的格式，同時更新動態內容，非常適合月度報表或標準化文件。

在C#中使用IronXL建立Excel報表：影像5 - 報表範本與使用範本建立的Excel文件比較

最佳實踐與故障排除

在實施Excel報表生成時，請注意以下提示：

大文件的記憶體用量：處理資料時，要逐塊處理而非載入整個資料集（Microsoft關於大Excel文件的建議）
日期格式問題：使用DateTime.ToOADate()格式相容Excel日期（Excel日期系統解釋）
文件鎖定錯誤：始終使用MemoryStream方法來適當處理Excel物件。
欠缺樣式：某些樣式屬性需要先設定背景顏色

結論

IronXL將Excel報告生成從繁瑣的手動過程轉變為自動化的可靠工作流程。憑藉其直觀的API、跨平台支援和全面的功能集，開發者可以在幾分鐘內建立專業的Excel報表，而非花費數小時。便捷的資料整合、強大的格式選項和公式支持使IronXL成為任何C#開發人員處理Excel報告的必要工具。對有興趣的開發者，IronXL提供免費試用和進一步的授權選項供公司及個人選擇。

第一步：