How to Create an Excel File in C# (.NET Tutorial)

如何在我的工作簿中新增工作表？

新增工作表很簡單：

// 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");

一本練習冊包含一張或多張練習題。 每個工作表都由行和列組成，單元格位於行和列的交叉點。 使用CreateWorkSheet 方法為工作簿新增工作表。

• CreateWorkSheet("name") ：新增一個具有指定選項卡名稱的新工作表
• GetWorkSheet("name") ：依名稱擷取現有工作表 工作簿中的工作表名稱必須唯一。

如何手動設定儲存格值？

設定單一儲存格值時，可以使用類似 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

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

字串插值($"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})";

本範例示範如何從資料庫讀取 Excel 資料、應用格式以及使用公式進行計算。 FormatString 屬性可以像在 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");
    }
}

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 borders

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 borders

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

工作表保護功能可防止意外修改，而凍結窗格功能可在捲動時保持重要行或列可見。

**圖 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 numbering

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 numbering

PrintSetup 類別提供與 Excel 列印設定相符的全面列印設定選項。

**圖 9** –*橫向列印預覽及自訂邊距*

既然現在可以用 C# 建立 Excel 文件，不妨探索一下IronXL 的這些相關功能：

-讀取並編輯現有 Excel 文件 -在 Excel 中合併儲存格 -使用 C# 建立 Excel 圖表 -電子表格格式之間的轉換 IronXL API 參考

教程快速訪問

### 下載本教學的 C# 原始碼

用於建立 Excel 檔案的完整 C# 原始程式碼以 Visual Studio 專案檔案的形式提供。

下載

### 在GitHub上查看此教學。

此專案的原始碼可在GitHub上找到，提供 C# 和.NET。

使用此程式碼，您可以在幾分鐘內輕鬆建立 Excel 檔案。此項目儲存為 Microsoft Visual Studio 項目，但與任何.NET IDE 都相容。

在GitHub上使用 C# 建立 Excel 文件

### 請閱讀IronXL API 參考文檔

探索IronXL的 API 參考，其中詳細介紹了所有功能、命名空間、類別、方法和屬性，以便在.NET中進行全面的 Excel 操作。

查看 API 參考