如何使用 C# 建立Excel試算表(.NET 完整教學)
This article was translated from English: Does it need improvement?
Translated
View the article in English
IronXL是一個功能強大的 C# 和.NET Excel 庫，它允許您以程式設計方式建立Excel試算表、讀取和編輯 XLSX 電子表格，而無需 Microsoft Office或 Interop 依賴項。 它支援.NET 8、 .NET 9、 .NET Core、 .NET Framework，並可在 Windows、Linux、macOS、Azure 和 AWS 上運行。
本教學將引導您使用 C# 建立Excel試算表的完整流程：設定儲存格值、應用程式格式、新增公式以及匯出為 XLSX、CSV 等格式——並提供逐步程式碼範例。
快速入門：建立 Excel 文件
使用IronXL可以立即建立一個新的 Excel 檔案——只需選擇格式、新增工作表、設定任意儲存格值並儲存即可。 這是產生 XLSX 檔案的最快方法，它具有直覺的 API 調用，並且不會出現任何互通性問題。
### 最簡工作流程(5個步驟)
- 下載並安裝IronXL C# 庫
- 設定你的.NET項目
- 建立工作簿和工作表
- 設定單元格值、格式和公式
- 儲存您的 Excel 文件
什麼是IronXL ？為什麼要使用它來建立 Excel 檔案？
IronXL是一個直觀的 C# 和 VB Excel API ，使您能夠在.NET中讀取、編輯和創建 Excel 電子表格文件，並具有卓越的性能。 與傳統方法不同，無需安裝 Microsoft Office 或使用 Excel Interop，讓部署更簡單、更可靠。
IronXL完全支援.NET 9、 .NET 8、 .NET Core、 .NET Framework、Xamarin、行動裝置、Linux、macOS 和 Azure 環境。
IronXL功能
- 由我們的.NET開發團隊直接提供人工支援 使用 Microsoft Visual Studio 快速安裝
- 免費用於開發。 許可證來自 $799
如何快速建立並儲存Excel檔案？
透過NuGet安裝IronXL或直接下載 DLL 檔案。 WorkBook 類別是您進行所有 Excel 操作的入口點，而 WorkSheet 類別提供了操作單一工作表的方法－完整的逐步演練從下面的步驟 1 開始。
How Do I Install the IronXL C# Library?
在 Visual Studio 中透過NuGet套件管理器安裝IronXL ，或使用套件管理器控制台：
Install-Package IronXL.Excel
透過"專案"功能表或在"解決方案資源管理器"中右鍵點選專案來存取NuGet套件管理器。
**圖 3** –*透過"專案"選單存取NuGet套件管理器*
**圖 4** –*解決方案資源管理器中的右鍵點選上下文選單*
在軟體包清單中瀏覽 IronXL.Excel，然後按一下"安裝"。
**圖 5** –*透過NuGet套件管理器安裝IronXL .Excel*
或者，直接下載IronXL DLL ，然後透過"解決方案資源管理器">"引用">"瀏覽"將其作為引用新增至您的專案。
**圖 6** –*從官方網站下載IronXL庫*
IronXL無需安裝 Microsoft Office 或 Excel Interop，可在任何支援 .NET 的平台上執行，包括 Windows、Linux、macOS 和雲端環境。
如何設定我的.NET專案？
IronXL適用於任何.NET專案類型—控制台應用程式、 ASP.NET Web 應用程式、API 或桌面工具。 下面的範例使用ASP.NET Web 應用程序，但所有專案範本的處理過程都相同。
請依照以下步驟建立ASP.NET網站：
- 開啟 Visual Studio
- 點選"檔案">"新建專案"
- 在"專案類型"清單中，選擇"Visual C#"下的"Web"。
-
選擇ASP.NET Web 應用程式
圖 1 – Create new ASP.NET project
- 按一下"確定"
-
選擇 Web 表單模板
圖 2 – Select Web Forms template
- 按一下"確定"
專案準備就緒後，安裝IronXL即可開始以程式設計方式建立 Excel 檔案。
How Do I Create an Excel Workbook in C#?
使用IronXL建立新的 Excel 工作簿只需要一行程式碼：
using IronXL;
// Create workbook with XLSX format (recommended for modern Excel)
WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
// Alternative: Create legacy XLS format for older Excel versions
WorkBook legacyWorkbook = WorkBook.Create(ExcelFileFormat.XLS);using IronXL;
// Create workbook with XLSX format (recommended for modern Excel)
WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
// Alternative: Create legacy XLS format for older Excel versions
WorkBook legacyWorkbook = WorkBook.Create(ExcelFileFormat.XLS);Imports IronXL
' Create workbook with XLSX format (recommended for modern Excel)
Private workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
' Alternative: Create legacy XLS format for older Excel versions
Private legacyWorkbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLS)
$vbLabelText
$csharpLabel
WorkBook.Create 方法同時支援 XLS(Excel 97-2003)和 XLSX(Excel 2007+)格式。 建議使用 XLSX 格式以獲得更好的效能和更小的檔案大小。
- XLSX ：建議用於所有現代 Excel 版本(2007 及更高版本)－檔案更小，效能更佳
- XLS ：與 Excel 97–2003 相容的舊格式
如何在我的工作簿中新增工作表？
新增工作表很簡單：
// Create a worksheet with custom name for budget tracking
WorkSheet budgetSheet = workbook.CreateWorkSheet("2020 Budget");
// Add multiple worksheets for different purposes
WorkSheet salesSheet = workbook.CreateWorkSheet("Sales Data");
WorkSheet inventorySheet = workbook.CreateWorkSheet("Inventory");
// Access existing worksheet by name
WorkSheet existingSheet = workbook.GetWorkSheet("2020 Budget");// Create a worksheet with custom name for budget tracking
WorkSheet budgetSheet = workbook.CreateWorkSheet("2020 Budget");
// Add multiple worksheets for different purposes
WorkSheet salesSheet = workbook.CreateWorkSheet("Sales Data");
WorkSheet inventorySheet = workbook.CreateWorkSheet("Inventory");
// Access existing worksheet by name
WorkSheet existingSheet = workbook.GetWorkSheet("2020 Budget");' Create a worksheet with custom name for budget tracking
Dim budgetSheet As WorkSheet = workbook.CreateWorkSheet("2020 Budget")
' Add multiple worksheets for different purposes
Dim salesSheet As WorkSheet = workbook.CreateWorkSheet("Sales Data")
Dim inventorySheet As WorkSheet = workbook.CreateWorkSheet("Inventory")
' Access existing worksheet by name
Dim existingSheet As WorkSheet = workbook.GetWorkSheet("2020 Budget")
$vbLabelText
$csharpLabel
一本練習冊包含一張或多張練習題。 每個工作表都由行和列組成，單元格位於行和列的交叉點。 使用CreateWorkSheet 方法為工作簿新增工作表。
CreateWorkSheet("name")：新增一個具有指定選項卡名稱的新工作表GetWorkSheet("name")：依名稱擷取現有工作表 工作簿中的工作表名稱必須唯一。
如何在Excel中設定單元格值？
如何手動設定儲存格值？
設定單一儲存格值時，可以使用類似 Excel 的直覺式語法：
// Set month names in first row for annual budget spreadsheet
workSheet["A1"].Value = "January";
workSheet["B1"].Value = "February";
workSheet["C1"].Value = "March";
workSheet["D1"].Value = "April";
workSheet["E1"].Value = "May";
workSheet["F1"].Value = "June";
workSheet["G1"].Value = "July";
workSheet["H1"].Value = "August";
workSheet["I1"].Value = "September";
workSheet["J1"].Value = "October";
workSheet["K1"].Value = "November";
workSheet["L1"].Value = "December";
// Set different data types - IronXL handles conversion automatically
workSheet["A2"].Value = 1500.50m; // Decimal for currency
workSheet["A3"].Value = DateTime.Now; // Date values
workSheet["A4"].Value = true; // Boolean values// Set month names in first row for annual budget spreadsheet
workSheet["A1"].Value = "January";
workSheet["B1"].Value = "February";
workSheet["C1"].Value = "March";
workSheet["D1"].Value = "April";
workSheet["E1"].Value = "May";
workSheet["F1"].Value = "June";
workSheet["G1"].Value = "July";
workSheet["H1"].Value = "August";
workSheet["I1"].Value = "September";
workSheet["J1"].Value = "October";
workSheet["K1"].Value = "November";
workSheet["L1"].Value = "December";
// Set different data types - IronXL handles conversion automatically
workSheet["A2"].Value = 1500.50m; // Decimal for currency
workSheet["A3"].Value = DateTime.Now; // Date values
workSheet["A4"].Value = true; // Boolean values' Set month names in first row for annual budget spreadsheet
workSheet("A1").Value = "January"
workSheet("B1").Value = "February"
workSheet("C1").Value = "March"
workSheet("D1").Value = "April"
workSheet("E1").Value = "May"
workSheet("F1").Value = "June"
workSheet("G1").Value = "July"
workSheet("H1").Value = "August"
workSheet("I1").Value = "September"
workSheet("J1").Value = "October"
workSheet("K1").Value = "November"
workSheet("L1").Value = "December"
' Set different data types - IronXL handles conversion automatically
workSheet("A2").Value = 1500.50D ' Decimal for currency
workSheet("A3").Value = DateTime.Now ' Date values
workSheet("A4").Value = True ' Boolean values
$vbLabelText
$csharpLabel
Cell.Value 屬性接受各種資料類型，包括字串、數字、日期和布林值。 IronXL會根據資料類型自動格式化儲存格。
如何動態設定單元格值？
動態值設定非常適合數據驅動型應用：
// Initialize random number generator for sample data
Random r = new Random();
// Populate cells with random budget data for each month
for (int i = 2; i <= 11; i++)
{
// Set different budget categories with increasing ranges
workSheet[$"A{i}"].Value = r.Next(1, 1000); // Office Supplies
workSheet[$"B{i}"].Value = r.Next(1000, 2000); // Utilities
workSheet[$"C{i}"].Value = r.Next(2000, 3000); // Rent
workSheet[$"D{i}"].Value = r.Next(3000, 4000); // Salaries
workSheet[$"E{i}"].Value = r.Next(4000, 5000); // Marketing
workSheet[$"F{i}"].Value = r.Next(5000, 6000); // IT Services
workSheet[$"G{i}"].Value = r.Next(6000, 7000); // Travel
workSheet[$"H{i}"].Value = r.Next(7000, 8000); // Training
workSheet[$"I{i}"].Value = r.Next(8000, 9000); // Insurance
workSheet[$"J{i}"].Value = r.Next(9000, 10000); // Equipment
workSheet[$"K{i}"].Value = r.Next(10000, 11000); // Research
workSheet[$"L{i}"].Value = r.Next(11000, 12000); // Misc
}
// Alternative: Set range of cells with same value
workSheet["A13:L13"].Value = 0; // Initialize totals row// Initialize random number generator for sample data
Random r = new Random();
// Populate cells with random budget data for each month
for (int i = 2; i <= 11; i++)
{
// Set different budget categories with increasing ranges
workSheet[$"A{i}"].Value = r.Next(1, 1000); // Office Supplies
workSheet[$"B{i}"].Value = r.Next(1000, 2000); // Utilities
workSheet[$"C{i}"].Value = r.Next(2000, 3000); // Rent
workSheet[$"D{i}"].Value = r.Next(3000, 4000); // Salaries
workSheet[$"E{i}"].Value = r.Next(4000, 5000); // Marketing
workSheet[$"F{i}"].Value = r.Next(5000, 6000); // IT Services
workSheet[$"G{i}"].Value = r.Next(6000, 7000); // Travel
workSheet[$"H{i}"].Value = r.Next(7000, 8000); // Training
workSheet[$"I{i}"].Value = r.Next(8000, 9000); // Insurance
workSheet[$"J{i}"].Value = r.Next(9000, 10000); // Equipment
workSheet[$"K{i}"].Value = r.Next(10000, 11000); // Research
workSheet[$"L{i}"].Value = r.Next(11000, 12000); // Misc
}
// Alternative: Set range of cells with same value
workSheet["A13:L13"].Value = 0; // Initialize totals row' Initialize random number generator for sample data
Dim r As New Random()
' Populate cells with random budget data for each month
For i As Integer = 2 To 11
' Set different budget categories with increasing ranges
workSheet($"A{i}").Value = r.Next(1, 1000) ' Office Supplies
workSheet($"B{i}").Value = r.Next(1000, 2000) ' Utilities
workSheet($"C{i}").Value = r.Next(2000, 3000) ' Rent
workSheet($"D{i}").Value = r.Next(3000, 4000) ' Salaries
workSheet($"E{i}").Value = r.Next(4000, 5000) ' Marketing
workSheet($"F{i}").Value = r.Next(5000, 6000) ' IT Services
workSheet($"G{i}").Value = r.Next(6000, 7000) ' Travel
workSheet($"H{i}").Value = r.Next(7000, 8000) ' Training
workSheet($"I{i}").Value = r.Next(8000, 9000) ' Insurance
workSheet($"J{i}").Value = r.Next(9000, 10000) ' Equipment
workSheet($"K{i}").Value = r.Next(10000, 11000) ' Research
workSheet($"L{i}").Value = r.Next(11000, 12000) ' Misc
Next i
' Alternative: Set range of cells with same value
workSheet("A13:L13").Value = 0 ' Initialize totals row
$vbLabelText
$csharpLabel
字串插值($"A{i}")使動態引用單元格變得容易。 Range 索引器支援單一儲存格和範圍。
如何從資料庫匯入資料到Excel？
將資料庫中的資料匯入Excel是一項常見需求：
using System.Data;
using System.Data.SqlClient;
using IronXL;
// Database connection setup for retrieving sales data
string connectionString = @"Data Source=ServerName;Initial Catalog=SalesDB;Integrated Security=true";
string query = "SELECT ProductName, Quantity, UnitPrice, TotalSales FROM MonthlySales";
// Create DataSet to hold query results
DataSet salesData = new DataSet();
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlDataAdapter adapter = new SqlDataAdapter(query, connection))
{
// Fill DataSet with sales information
adapter.Fill(salesData);
}
// Write headers for database columns
workSheet["A1"].Value = "Product Name";
workSheet["B1"].Value = "Quantity";
workSheet["C1"].Value = "Unit Price";
workSheet["D1"].Value = "Total Sales";
// Apply header formatting
workSheet["A1:D1"].Style.Font.Bold = true;
workSheet["A1:D1"].Style.SetBackgroundColor("#4472C4");
workSheet["A1:D1"].Style.Font.FontColor = "#FFFFFF";
// Populate Excel with database records
DataTable salesTable = salesData.Tables[0];
for (int row = 0; row < salesTable.Rows.Count; row++)
{
int excelRow = row + 2; // Start from row 2 (after headers)
workSheet[$"A{excelRow}"].Value = salesTable.Rows[row]["ProductName"].ToString();
workSheet[$"B{excelRow}"].Value = Convert.ToInt32(salesTable.Rows[row]["Quantity"]);
workSheet[$"C{excelRow}"].Value = Convert.ToDecimal(salesTable.Rows[row]["UnitPrice"]);
workSheet[$"D{excelRow}"].Value = Convert.ToDecimal(salesTable.Rows[row]["TotalSales"]);
// Format currency columns
workSheet[$"C{excelRow}"].FormatString = "$#,##0.00";
workSheet[$"D{excelRow}"].FormatString = "$#,##0.00";
}
// Add summary row with formulas
int summaryRow = salesTable.Rows.Count + 2;
workSheet[$"A{summaryRow}"].Value = "TOTAL";
workSheet[$"B{summaryRow}"].Formula = $"=SUM(B2:B{summaryRow-1})";
workSheet[$"D{summaryRow}"].Formula = $"=SUM(D2:D{summaryRow-1})";using System.Data;
using System.Data.SqlClient;
using IronXL;
// Database connection setup for retrieving sales data
string connectionString = @"Data Source=ServerName;Initial Catalog=SalesDB;Integrated Security=true";
string query = "SELECT ProductName, Quantity, UnitPrice, TotalSales FROM MonthlySales";
// Create DataSet to hold query results
DataSet salesData = new DataSet();
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlDataAdapter adapter = new SqlDataAdapter(query, connection))
{
// Fill DataSet with sales information
adapter.Fill(salesData);
}
// Write headers for database columns
workSheet["A1"].Value = "Product Name";
workSheet["B1"].Value = "Quantity";
workSheet["C1"].Value = "Unit Price";
workSheet["D1"].Value = "Total Sales";
// Apply header formatting
workSheet["A1:D1"].Style.Font.Bold = true;
workSheet["A1:D1"].Style.SetBackgroundColor("#4472C4");
workSheet["A1:D1"].Style.Font.FontColor = "#FFFFFF";
// Populate Excel with database records
DataTable salesTable = salesData.Tables[0];
for (int row = 0; row < salesTable.Rows.Count; row++)
{
int excelRow = row + 2; // Start from row 2 (after headers)
workSheet[$"A{excelRow}"].Value = salesTable.Rows[row]["ProductName"].ToString();
workSheet[$"B{excelRow}"].Value = Convert.ToInt32(salesTable.Rows[row]["Quantity"]);
workSheet[$"C{excelRow}"].Value = Convert.ToDecimal(salesTable.Rows[row]["UnitPrice"]);
workSheet[$"D{excelRow}"].Value = Convert.ToDecimal(salesTable.Rows[row]["TotalSales"]);
// Format currency columns
workSheet[$"C{excelRow}"].FormatString = "$#,##0.00";
workSheet[$"D{excelRow}"].FormatString = "$#,##0.00";
}
// Add summary row with formulas
int summaryRow = salesTable.Rows.Count + 2;
workSheet[$"A{summaryRow}"].Value = "TOTAL";
workSheet[$"B{summaryRow}"].Formula = $"=SUM(B2:B{summaryRow-1})";
workSheet[$"D{summaryRow}"].Formula = $"=SUM(D2:D{summaryRow-1})";Imports System.Data
Imports System.Data.SqlClient
Imports IronXL
' Database connection setup for retrieving sales data
Private connectionString As String = "Data Source=ServerName;Initial Catalog=SalesDB;Integrated Security=true"
Private query As String = "SELECT ProductName, Quantity, UnitPrice, TotalSales FROM MonthlySales"
' Create DataSet to hold query results
Private salesData As New DataSet()
Using connection As New SqlConnection(connectionString)
Using adapter As New SqlDataAdapter(query, connection)
' Fill DataSet with sales information
adapter.Fill(salesData)
End Using
End Using
' Write headers for database columns
workSheet("A1").Value = "Product Name"
workSheet("B1").Value = "Quantity"
workSheet("C1").Value = "Unit Price"
workSheet("D1").Value = "Total Sales"
' Apply header formatting
workSheet("A1:D1").Style.Font.Bold = True
workSheet("A1:D1").Style.SetBackgroundColor("#4472C4")
workSheet("A1:D1").Style.Font.FontColor = "#FFFFFF"
' Populate Excel with database records
Dim salesTable As DataTable = salesData.Tables(0)
For row As Integer = 0 To salesTable.Rows.Count - 1
Dim excelRow As Integer = row + 2 ' Start from row 2 (after headers)
workSheet($"A{excelRow}").Value = salesTable.Rows(row)("ProductName").ToString()
workSheet($"B{excelRow}").Value = Convert.ToInt32(salesTable.Rows(row)("Quantity"))
workSheet($"C{excelRow}").Value = Convert.ToDecimal(salesTable.Rows(row)("UnitPrice"))
workSheet($"D{excelRow}").Value = Convert.ToDecimal(salesTable.Rows(row)("TotalSales"))
' Format currency columns
workSheet($"C{excelRow}").FormatString = "$#,##0.00"
workSheet($"D{excelRow}").FormatString = "$#,##0.00"
Next row
' Add summary row with formulas
Dim summaryRow As Integer = salesTable.Rows.Count + 2
workSheet($"A{summaryRow}").Value = "TOTAL"
workSheet($"B{summaryRow}").Formula = $"=SUM(B2:B{summaryRow-1})"
workSheet($"D{summaryRow}").Formula = $"=SUM(D2:D{summaryRow-1})"
$vbLabelText
$csharpLabel
本範例示範如何從資料庫讀取 Excel 資料、應用格式以及使用公式進行計算。 FormatString 屬性可以像在 Excel 中一樣自訂數字格式。
如何對Excel單元格套用格式？
如何在Excel中設定背景顏色？
單元格樣式可以增強可讀性和視覺吸引力：
// Set header row background to light gray using hex color
workSheet["A1:L1"].Style.SetBackgroundColor("#d3d3d3");
// Apply different colors for data categorization
workSheet["A2:A11"].Style.SetBackgroundColor("#E7F3FF"); // Light blue for January
workSheet["B2:B11"].Style.SetBackgroundColor("#FFF2CC"); // Light yellow for February
// Highlight important cells with bold colors
workSheet["L12"].Style.SetBackgroundColor("#FF0000"); // Red for totals
workSheet["L12"].Style.Font.FontColor = "#FFFFFF"; // White text
// Create alternating row colors for better readability
for (int row = 2; row <= 11; row++)
{
if (row % 2 == 0)
{
workSheet[$"A{row}:L{row}"].Style.SetBackgroundColor("#F2F2F2");
}
}// Set header row background to light gray using hex color
workSheet["A1:L1"].Style.SetBackgroundColor("#d3d3d3");
// Apply different colors for data categorization
workSheet["A2:A11"].Style.SetBackgroundColor("#E7F3FF"); // Light blue for January
workSheet["B2:B11"].Style.SetBackgroundColor("#FFF2CC"); // Light yellow for February
// Highlight important cells with bold colors
workSheet["L12"].Style.SetBackgroundColor("#FF0000"); // Red for totals
workSheet["L12"].Style.Font.FontColor = "#FFFFFF"; // White text
// Create alternating row colors for better readability
for (int row = 2; row <= 11; row++)
{
if (row % 2 == 0)
{
workSheet[$"A{row}:L{row}"].Style.SetBackgroundColor("#F2F2F2");
}
}' Set header row background to light gray using hex color
workSheet("A1:L1").Style.SetBackgroundColor("#d3d3d3")
' Apply different colors for data categorization
workSheet("A2:A11").Style.SetBackgroundColor("#E7F3FF") ' Light blue for January
workSheet("B2:B11").Style.SetBackgroundColor("#FFF2CC") ' Light yellow for February
' Highlight important cells with bold colors
workSheet("L12").Style.SetBackgroundColor("#FF0000") ' Red for totals
workSheet("L12").Style.Font.FontColor = "#FFFFFF" ' White text
' Create alternating row colors for better readability
For row As Integer = 2 To 11
If row Mod 2 = 0 Then
workSheet($"A{row}:L{row}").Style.SetBackgroundColor("#F2F2F2")
End If
Next row
$vbLabelText
$csharpLabel
Style.SetBackgroundColor 方法接受十六進位顏色代碼。 將背景顏色與字體顏色結合使用，打造專業美觀的電子表格。
如何在Excel中建立邊框？
邊界有助於定義資料區域並改善資料結構：
using IronXL;
using IronXL.Styles;
// Create header border - thick bottom line to separate from data
workSheet["A1:L1"].Style.TopBorder.SetColor("#000000");
workSheet["A1:L1"].Style.TopBorder.Type = BorderType.Thick;
workSheet["A1:L1"].Style.BottomBorder.SetColor("#000000");
workSheet["A1:L1"].Style.BottomBorder.Type = BorderType.Thick;
// Add right border to last column
workSheet["L2:L11"].Style.RightBorder.SetColor("#000000");
workSheet["L2:L11"].Style.RightBorder.Type = BorderType.Medium;
// Create bottom border for data area
workSheet["A11:L11"].Style.BottomBorder.SetColor("#000000");
workSheet["A11:L11"].Style.BottomBorder.Type = BorderType.Medium;
// Apply complete border around summary section
var summaryRange = workSheet["A12:L12"];
summaryRange.Style.TopBorder.Type = BorderType.Double;
summaryRange.Style.BottomBorder.Type = BorderType.Double;
summaryRange.Style.LeftBorder.Type = BorderType.Thin;
summaryRange.Style.RightBorder.Type = BorderType.Thin;
summaryRange.Style.SetBorderColor("#0070C0"); // Blue bordersusing IronXL;
using IronXL.Styles;
// Create header border - thick bottom line to separate from data
workSheet["A1:L1"].Style.TopBorder.SetColor("#000000");
workSheet["A1:L1"].Style.TopBorder.Type = BorderType.Thick;
workSheet["A1:L1"].Style.BottomBorder.SetColor("#000000");
workSheet["A1:L1"].Style.BottomBorder.Type = BorderType.Thick;
// Add right border to last column
workSheet["L2:L11"].Style.RightBorder.SetColor("#000000");
workSheet["L2:L11"].Style.RightBorder.Type = BorderType.Medium;
// Create bottom border for data area
workSheet["A11:L11"].Style.BottomBorder.SetColor("#000000");
workSheet["A11:L11"].Style.BottomBorder.Type = BorderType.Medium;
// Apply complete border around summary section
var summaryRange = workSheet["A12:L12"];
summaryRange.Style.TopBorder.Type = BorderType.Double;
summaryRange.Style.BottomBorder.Type = BorderType.Double;
summaryRange.Style.LeftBorder.Type = BorderType.Thin;
summaryRange.Style.RightBorder.Type = BorderType.Thin;
summaryRange.Style.SetBorderColor("#0070C0"); // Blue bordersImports IronXL
Imports IronXL.Styles
' Create header border - thick bottom line to separate from data
workSheet("A1:L1").Style.TopBorder.SetColor("#000000")
workSheet("A1:L1").Style.TopBorder.Type = BorderType.Thick
workSheet("A1:L1").Style.BottomBorder.SetColor("#000000")
workSheet("A1:L1").Style.BottomBorder.Type = BorderType.Thick
' Add right border to last column
workSheet("L2:L11").Style.RightBorder.SetColor("#000000")
workSheet("L2:L11").Style.RightBorder.Type = BorderType.Medium
' Create bottom border for data area
workSheet("A11:L11").Style.BottomBorder.SetColor("#000000")
workSheet("A11:L11").Style.BottomBorder.Type = BorderType.Medium
' Apply complete border around summary section
Dim summaryRange = workSheet("A12:L12")
summaryRange.Style.TopBorder.Type = BorderType.Double
summaryRange.Style.BottomBorder.Type = BorderType.Double
summaryRange.Style.LeftBorder.Type = BorderType.Thin
summaryRange.Style.RightBorder.Type = BorderType.Thin
summaryRange.Style.SetBorderColor("#0070C0") ' Blue borders
$vbLabelText
$csharpLabel
IronXL支援多種邊框類型，包括細邊框、中等邊框、粗邊框、雙邊框、點狀邊框和虛線邊框。 邊框的每一邊都可以獨立設定樣式。
How Do I Use Excel Formulas in C#?
IronXL提供強大的公式支援和自動計算功能：
// Use built-in aggregation functions for ranges
decimal sum = workSheet["A2:A11"].Sum();
decimal avg = workSheet["B2:B11"].Avg();
decimal max = workSheet["C2:C11"].Max();
decimal min = workSheet["D2:D11"].Min();
// Assign calculated values to cells
workSheet["A12"].Value = sum;
workSheet["B12"].Value = avg;
workSheet["C12"].Value = max;
workSheet["D12"].Value = min;
// Or use Excel formulas directly
workSheet["A12"].Formula = "=SUM(A2:A11)";
workSheet["B12"].Formula = "=AVERAGE(B2:B11)";
workSheet["C12"].Formula = "=MAX(C2:C11)";
workSheet["D12"].Formula = "=MIN(D2:D11)";
// Complex formulas with multiple functions
workSheet["E12"].Formula = "=IF(SUM(E2:E11)>50000,\"Over Budget\",\"On Track\")";
workSheet["F12"].Formula = "=SUMIF(F2:F11,\">5000\")";
// Percentage calculations
workSheet["G12"].Formula = "=G11/SUM(G2:G11)*100";
workSheet["G12"].FormatString = "0.00%";
// Ensure all formulas calculate
workSheet.EvaluateAll();// Use built-in aggregation functions for ranges
decimal sum = workSheet["A2:A11"].Sum();
decimal avg = workSheet["B2:B11"].Avg();
decimal max = workSheet["C2:C11"].Max();
decimal min = workSheet["D2:D11"].Min();
// Assign calculated values to cells
workSheet["A12"].Value = sum;
workSheet["B12"].Value = avg;
workSheet["C12"].Value = max;
workSheet["D12"].Value = min;
// Or use Excel formulas directly
workSheet["A12"].Formula = "=SUM(A2:A11)";
workSheet["B12"].Formula = "=AVERAGE(B2:B11)";
workSheet["C12"].Formula = "=MAX(C2:C11)";
workSheet["D12"].Formula = "=MIN(D2:D11)";
// Complex formulas with multiple functions
workSheet["E12"].Formula = "=IF(SUM(E2:E11)>50000,\"Over Budget\",\"On Track\")";
workSheet["F12"].Formula = "=SUMIF(F2:F11,\">5000\")";
// Percentage calculations
workSheet["G12"].Formula = "=G11/SUM(G2:G11)*100";
workSheet["G12"].FormatString = "0.00%";
// Ensure all formulas calculate
workSheet.EvaluateAll();' Use built-in aggregation functions for ranges
Dim sum As Decimal = workSheet("A2:A11").Sum()
Dim avg As Decimal = workSheet("B2:B11").Avg()
Dim max As Decimal = workSheet("C2:C11").Max()
Dim min As Decimal = workSheet("D2:D11").Min()
' Assign calculated values to cells
workSheet("A12").Value = sum
workSheet("B12").Value = avg
workSheet("C12").Value = max
workSheet("D12").Value = min
' Or use Excel formulas directly
workSheet("A12").Formula = "=SUM(A2:A11)"
workSheet("B12").Formula = "=AVERAGE(B2:B11)"
workSheet("C12").Formula = "=MAX(C2:C11)"
workSheet("D12").Formula = "=MIN(D2:D11)"
' Complex formulas with multiple functions
workSheet("E12").Formula = "=IF(SUM(E2:E11)>50000,""Over Budget"",""On Track"")"
workSheet("F12").Formula = "=SUMIF(F2:F11,"">5000"")"
' Percentage calculations
workSheet("G12").Formula = "=G11/SUM(G2:G11)*100"
workSheet("G12").FormatString = "0.00%"
' Ensure all formulas calculate
workSheet.EvaluateAll()
$vbLabelText
$csharpLabel
Range 類提供了諸如 Max() 和 Min() 之類的方法，用於快速計算。 對於更複雜的場景，可以使用Formula 屬性直接設定 Excel 公式。
處理範圍時，建議優先使用 IronXL 內建的 .Max() 和 .Min()字串方法，而不是原始公式—它們類型安全，並且避免公式語法錯誤。
如何設定工作表和列印屬性？
使用IronXL保護單一工作表、凍結行和列，以及設定列印格式選項。
如何配置工作表屬性？
保護工作表並控制檢視選項：
// Protect worksheet with password to prevent unauthorized changes
workSheet.ProtectSheet("SecurePassword123");
// Freeze panes to keep headers visible while scrolling
workSheet.CreateFreezePane(0, 1); // Freeze first row
// workSheet.CreateFreezePane(1, 1); // Freeze first row and column
// Set worksheet visibility options
workSheet.ViewState = WorkSheetViewState.Visible; // or Hidden, VeryHidden
// Configure gridlines and headers
workSheet.ShowGridLines = true;
workSheet.ShowRowColHeaders = true;
// Set zoom level for better viewing
workSheet.Zoom = 85; // 85% zoom// Protect worksheet with password to prevent unauthorized changes
workSheet.ProtectSheet("SecurePassword123");
// Freeze panes to keep headers visible while scrolling
workSheet.CreateFreezePane(0, 1); // Freeze first row
// workSheet.CreateFreezePane(1, 1); // Freeze first row and column
// Set worksheet visibility options
workSheet.ViewState = WorkSheetViewState.Visible; // or Hidden, VeryHidden
// Configure gridlines and headers
workSheet.ShowGridLines = true;
workSheet.ShowRowColHeaders = true;
// Set zoom level for better viewing
workSheet.Zoom = 85; // 85% zoom' Protect worksheet with password to prevent unauthorized changes
workSheet.ProtectSheet("SecurePassword123")
' Freeze panes to keep headers visible while scrolling
workSheet.CreateFreezePane(0, 1) ' Freeze first row
' workSheet.CreateFreezePane(1, 1); // Freeze first row and column
' Set worksheet visibility options
workSheet.ViewState = WorkSheetViewState.Visible ' or Hidden, VeryHidden
' Configure gridlines and headers
workSheet.ShowGridLines = True
workSheet.ShowRowColHeaders = True
' Set zoom level for better viewing
workSheet.Zoom = 85 ' 85% zoom
$vbLabelText
$csharpLabel
工作表保護功能可防止意外修改，而凍結窗格功能可在捲動時保持重要行或列可見。
**圖 7** –*凍結的標題行在滾動時仍然可見*
**圖 8** –*密碼保護防止未經授權的編輯*
如何配置頁面和列印設定？
透過 IronXL 設定專業列印選項：
using IronXL.Printing;
// Define print area to exclude empty cells
workSheet.SetPrintArea("A1:L12");
// Configure page orientation for wide data
workSheet.PrintSetup.PrintOrientation = PrintOrientation.Landscape;
// Set paper size for standard printing
workSheet.PrintSetup.PaperSize = PaperSize.A4;
// Adjust margins for better layout (in inches)
workSheet.PrintSetup.LeftMargin = 0.5;
workSheet.PrintSetup.RightMargin = 0.5;
workSheet.PrintSetup.TopMargin = 0.75;
workSheet.PrintSetup.BottomMargin = 0.75;
// Configure header and footer
workSheet.PrintSetup.HeaderMargin = 0.3;
workSheet.PrintSetup.FooterMargin = 0.3;
// Scale to fit on one page
workSheet.PrintSetup.FitToPage = true;
workSheet.PrintSetup.FitToHeight = 1;
workSheet.PrintSetup.FitToWidth = 1;
// Add print headers/footers
workSheet.Header.Center = "Monthly Budget Report";
workSheet.Footer.Left = DateTime.Now.ToShortDateString();
workSheet.Footer.Right = "Page &P of &N"; // Page numberingusing IronXL.Printing;
// Define print area to exclude empty cells
workSheet.SetPrintArea("A1:L12");
// Configure page orientation for wide data
workSheet.PrintSetup.PrintOrientation = PrintOrientation.Landscape;
// Set paper size for standard printing
workSheet.PrintSetup.PaperSize = PaperSize.A4;
// Adjust margins for better layout (in inches)
workSheet.PrintSetup.LeftMargin = 0.5;
workSheet.PrintSetup.RightMargin = 0.5;
workSheet.PrintSetup.TopMargin = 0.75;
workSheet.PrintSetup.BottomMargin = 0.75;
// Configure header and footer
workSheet.PrintSetup.HeaderMargin = 0.3;
workSheet.PrintSetup.FooterMargin = 0.3;
// Scale to fit on one page
workSheet.PrintSetup.FitToPage = true;
workSheet.PrintSetup.FitToHeight = 1;
workSheet.PrintSetup.FitToWidth = 1;
// Add print headers/footers
workSheet.Header.Center = "Monthly Budget Report";
workSheet.Footer.Left = DateTime.Now.ToShortDateString();
workSheet.Footer.Right = "Page &P of &N"; // Page numberingImports IronXL.Printing
' Define print area to exclude empty cells
workSheet.SetPrintArea("A1:L12")
' Configure page orientation for wide data
workSheet.PrintSetup.PrintOrientation = PrintOrientation.Landscape
' Set paper size for standard printing
workSheet.PrintSetup.PaperSize = PaperSize.A4
' Adjust margins for better layout (in inches)
workSheet.PrintSetup.LeftMargin = 0.5
workSheet.PrintSetup.RightMargin = 0.5
workSheet.PrintSetup.TopMargin = 0.75
workSheet.PrintSetup.BottomMargin = 0.75
' Configure header and footer
workSheet.PrintSetup.HeaderMargin = 0.3
workSheet.PrintSetup.FooterMargin = 0.3
' Scale to fit on one page
workSheet.PrintSetup.FitToPage = True
workSheet.PrintSetup.FitToHeight = 1
workSheet.PrintSetup.FitToWidth = 1
' Add print headers/footers
workSheet.Header.Center = "Monthly Budget Report"
workSheet.Footer.Left = DateTime.Now.ToShortDateString()
workSheet.Footer.Right = "Page &P of &N" ' Page numbering
$vbLabelText
$csharpLabel
PrintSetup 類別提供與 Excel 列印設定相符的全面列印設定選項。
**圖 9** –*橫向列印預覽及自訂邊距*
如何儲存我的Excel工作簿？
將工作簿儲存為多種格式：
// Save as XLSX (recommended for modern Excel)
workBook.SaveAs("Budget.xlsx");
// Save as XLS for legacy compatibility
workBook.SaveAs("Budget.xls");
// Save as CSV for data exchange
workBook.SaveAsCsv("Budget.csv");
// Save as JSON for web applications
workBook.SaveAsJson("Budget.json");
// Save to stream for web downloads or cloud storage
using (var stream = new MemoryStream())
{
workBook.SaveAs(stream);
byte[] excelData = stream.ToArray();
// Send to client or save to cloud
}
// Save with specific encoding for international characters
workBook.SaveAsCsv("Budget_UTF8.csv", System.Text.Encoding.UTF8);// Save as XLSX (recommended for modern Excel)
workBook.SaveAs("Budget.xlsx");
// Save as XLS for legacy compatibility
workBook.SaveAs("Budget.xls");
// Save as CSV for data exchange
workBook.SaveAsCsv("Budget.csv");
// Save as JSON for web applications
workBook.SaveAsJson("Budget.json");
// Save to stream for web downloads or cloud storage
using (var stream = new MemoryStream())
{
workBook.SaveAs(stream);
byte[] excelData = stream.ToArray();
// Send to client or save to cloud
}
// Save with specific encoding for international characters
workBook.SaveAsCsv("Budget_UTF8.csv", System.Text.Encoding.UTF8);' Save as XLSX (recommended for modern Excel)
workBook.SaveAs("Budget.xlsx")
' Save as XLS for legacy compatibility
workBook.SaveAs("Budget.xls")
' Save as CSV for data exchange
workBook.SaveAsCsv("Budget.csv")
' Save as JSON for web applications
workBook.SaveAsJson("Budget.json")
' Save to stream for web downloads or cloud storage
Using stream = New MemoryStream()
workBook.SaveAs(stream)
Dim excelData() As Byte = stream.ToArray()
' Send to client or save to cloud
End Using
' Save with specific encoding for international characters
workBook.SaveAsCsv("Budget_UTF8.csv", System.Text.Encoding.UTF8)
$vbLabelText
$csharpLabel
IronXL支援多種匯出格式，包括 XLSX、XLS、CSV、TSV 和 JSON。 SaveAs 方法會自動根據檔案副檔名決定格式。
- XLSX / XLS ：完整的 Excel 格式，包含格式、公式和多個工作表
- CSV ：純文字格式，用於資料交換－每個文件匯出一個工作表 JSON ：用於 Web API 和資料管道的結構化輸出 -流：用於記憶體輸出－適用於網頁下載或雲端存儲
概括
本教學課程示範如何使用IronXL在 C# 中建立不依賴 Microsoft Office 的 Excel 檔案。 您學習了Excel的基本操作，包括建立工作簿、儲存格操作、格式設定、公式使用以及各種儲存選項。 IronXL 直覺的 API 讓.NET開發人員能夠輕鬆實現Excel 自動化。
接下來是什麼？
既然現在可以用 C# 建立 Excel 文件，不妨探索一下IronXL 的這些相關功能：
-讀取並編輯現有 Excel 文件 -在 Excel 中合併儲存格 -使用 C# 建立 Excel 圖表 -電子表格格式之間的轉換 IronXL API 參考
常見問題解答
如何在不使用 Interop 的情況下在 C# 中創建 Excel 檔案？
您可以使用 IronXL 創建不需要 Interop 的 Excel 檔案，這提供了一個簡單的 API：WorkBook workBook = WorkBook.Create(ExcelFileFormat.XLSX)。此方法適用於任何 .NET 平台，無需安裝 Microsoft Office。
在 C# 平台上支援 Excel 檔案創建的平台有哪些？
IronXL 支援在 .NET 10、.NET 9、.NET 8、.NET Core、.NET Framework 4.6.2+ 上創建 Excel 檔案，運行於 Windows、macOS、Linux、Docker、Azure 和 AWS 環境。
我如何安裝一個 C# 庫來生成 Excel？
在 Visual Studio 中通過 NuGet 包管理器安裝 IronXL，使用命令 PM> Install-Package IronXL.Excel，或直接從 nuget.org 下載。
我如何以程式化方式創建一個新的 Excel 工作簿？
使用 IronXL 創建一個工作簿：WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX)，然後使用 workbook.CreateWorkSheet("SheetName") 添加工作表。
我如何在 Excel 工作表中設置單元格值，使用 C#？
使用 IronXL 設置單元格值，語法簡單：worksheet["A1"].Value = "Hello World" 或設置範圍：worksheet["A1:A10"].Value = 100。
我可以以編程方式為 Excel 單元格應用格式嗎？
是的，IronXL 支援全面的格式設置，包括背景顏色（cell.Style.SetBackgroundColor("#FF0000")）、邊框、字體和數字格式。
我如何在 C# 中使用 Excel 公式？
使用 IronXL 的公式屬性應用公式：worksheet["A1"].Formula = "=SUM(B1:B10)"，或使用內置方法如 range.Sum()、range.Avg()。
我如何用密碼保護 Excel 工作表？
使用 IronXL 保護工作表，代碼為 worksheet.ProtectSheet("YourPassword")，以防止未授權的修改。
我如何配置 Excel 檔案的打印設置？
使用 IronXL 的打印設置：worksheet.PrintSetup.PrintOrientation = PrintOrientation.Landscape 和 worksheet.SetPrintArea("A1:Z100")。
我如何以不同格式保存 Excel 工作簿？
使用 IronXL 的 SaveAs 方法保存工作簿：workbook.SaveAs("file.xlsx") 為 XLSX，或使用 SaveAsCsv()、SaveAsJson() 作為其他格式。
我如何從資料庫填充 Excel 表格中的資料？
使用 IronXL 通過從資料庫提取資料並使用方法如 worksheet["A1"].Value = dataFromDatabase 將其設置在單元格中以填充工作表。
我如何使用 C# 在 Excel 表格中實現凍結窗格？
使用 IronXL 在工作表中凍結窗格，代碼為 worksheet.FreezePanes(1, 1) 以便鎖定頂部行和左側列，便於導航。
準備好開始了嗎？
Nuget 下載 1,950,735 | 版本： 2026.4 剛剛發布
還在捲動嗎？
想要快速證明？ PM > Install-Package IronXL.Excel
執行範例 觀看您的資料變成試算表。
免費開始使用
免費開始使用
預約免費現場演示
無需信用卡或創建帳戶
