C＃創建Excel

Translated

View the article in English

以程式設計方式創建 Excel 試算表文件是 C# 開發者常見的任務，今天我們將處理這個任務，包括學習創建新的 Excel 文件、設定單元格樣式以及使用 C# 編程插入數據。使用正確的代碼，您可以完全自定義和設計您的工作表以滿足您的需求。我們將逐步說明如何為您的 .NET 專案創建 C# Excel 工作簿。

C# Excel 工作簿操作步骤

下載 IronXL 庫給 C#
以程式方式將資料插入儲存格和範圍
將 Excel 文件保存到指定路徑
從 DataTable 插入 Excel 資料
設置活頁簿文字、儲存格、頁面樣式等

第一步

1. 使用IronXL創建C# Excel試算表

今天我們將使用IronXL，這是一個用於Excel功能的C#程式庫，使得操作Excel文件變得更加高效。它可免費用於開發專案。安裝它並按照教程操作。

下載到您的專案或遵循使用 NuGet 安裝到 Visual Studio.

Install-Package IronXL.Excel

如何操作教程

2. C# 創建 Excel 工作簿

一旦我們在我們的項目中安裝了IronXL，我們就可以創建一個Excel工作簿。使用 WorkBook.Create()IronXL 的功能

WorkBook wb = WorkBook.Create();

WorkBook wb = WorkBook.Create();

Dim wb As WorkBook = WorkBook.Create()

VB C#

它將創建一個新的Excel工作簿wb。我們可以指定 XL 工作簿的類型(.xlsx 或 xls)使用 ExcelFileFormat 作為 WorkBook.Create 中的參數()函數如下：

/**
Create Csharp WorkBook 
anchor-c-num-create-excel-workbook
**/
//for creating .xlsx extension file
WorkBook wb = WorkBook.Create(ExcelFileFormat.XLSX);
//for creating .xls extension file
WorkBook wb = WorkBook.Create(ExcelFileFormat.XLS);

/**
Create Csharp WorkBook 
anchor-c-num-create-excel-workbook
**/
//for creating .xlsx extension file
WorkBook wb = WorkBook.Create(ExcelFileFormat.XLSX);
//for creating .xls extension file
WorkBook wb = WorkBook.Create(ExcelFileFormat.XLS);

'''
'''Create Csharp WorkBook 
'''anchor-c-num-create-excel-workbook
'''*
'for creating .xlsx extension file
Dim wb As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
'for creating .xls extension file
Dim wb As WorkBook = WorkBook.Create(ExcelFileFormat.XLS)

VB C#

現在，我們可以使用 wb 創建工作表。

3. C# 建立 Excel 試算表

要创建工作表，IronXL 提供了 Workbook.CreateWorkSheet 方法。()函式。該函數需要一個字符串參數，我們可以在其中指定WorkSheet的名稱。

WorkSheet ws = wb.CreateWorkSheet("SheetName");

WorkSheet ws = wb.CreateWorkSheet("SheetName");

Dim ws As WorkSheet = wb.CreateWorkSheet("SheetName")

VB C#

wb 是工作簿(見上文)和 ws 是一個新建的工作表。同樣地，我們可以根據需要創建任意多的工作表。例如：

/**
Create Csharp WorkSheets 
anchor-c-num-create-excel-workbook
**/
WorkSheet ws1 = wb.CreateWorkSheet("Sheet1");
WorkSheet ws2 = wb.CreateWorkSheet("Sheet2");

/**
Create Csharp WorkSheets 
anchor-c-num-create-excel-workbook
**/
WorkSheet ws1 = wb.CreateWorkSheet("Sheet1");
WorkSheet ws2 = wb.CreateWorkSheet("Sheet2");

'''
'''Create Csharp WorkSheets 
'''anchor-c-num-create-excel-workbook
'''*
Dim ws1 As WorkSheet = wb.CreateWorkSheet("Sheet1")
Dim ws2 As WorkSheet = wb.CreateWorkSheet("Sheet2")

VB C#

4. 插入單元格數據

現在，我們可以在指定的工作表中插入數據。我們將使用 Excel 的單元格地址系統：

/**
Insert Data in Cell Address
anchor-insert-cell-data
**/
WorkSheet ["CellAddress"].Value = "Value";

/**
Insert Data in Cell Address
anchor-insert-cell-data
**/
WorkSheet ["CellAddress"].Value = "Value";

'''
'''Insert Data in Cell Address
'''anchor-insert-cell-data
'''*
WorkSheet ("CellAddress").Value = "Value"

VB C#

5. 在範圍內插入數據

此方法還允許我們在多個單元格中插入所需的值。我們可以使用Range來在一系列單元格中插入數據。

/**
Insert Data in Range
anchor-insert-data-in-range
**/
WorkSheet ["From Cell Address : To Cell Address"].Value="value";

/**
Insert Data in Range
anchor-insert-data-in-range
**/
WorkSheet ["From Cell Address : To Cell Address"].Value="value";

'''
'''Insert Data in Range
'''anchor-insert-data-in-range
'''*
WorkSheet ("From Cell Address : To Cell Address").Value="value"

VB C#

這將在指定範圍內的所有單元格中插入value。您可以閱讀更多有關C# Excel 範圍用於您的 .NET 專案。

6. 儲存 Excel 檔案

在插入數據後，我們需要將 Excel 文件保存在指定的路徑中。

/**
Save Excel File
anchor-save-excel-file
**/
WorkBook.SaveAs("Path + Filename");

/**
Save Excel File
anchor-save-excel-file
**/
WorkBook.SaveAs("Path + Filename");

'''
'''Save Excel File
'''anchor-save-excel-file
'''*
WorkBook.SaveAs("Path + Filename")

VB C#

透過上述單行程式碼，新建的 Excel 工作簿將保存在指定位置。深入探索C# 建立 Excel 試算表範例.

創建、插入數據和儲存範例

/**
Complete Example
anchor-create-insert-data-and-save-example
**/
using IronXL;
static void Main(string [] args)
{
    //Create new XL WorkBook
    WorkBook wb = WorkBook.Create(ExcelFileFormat.XLSX);
    //Create worksheet of specified WorkBook
    WorkSheet ws = wb.CreateWorkSheet("Sheet1");
    //insert data by cell addressing
    ws ["A1"].Value = "Welcome";
    ws ["A2"].Value = "To";
    ws ["A3"].Value = "IronXL";
    //insert data by range
    ws ["C3:C8"].Value = "Cell Value";
    //save the file in specified path
    wb.SaveAs("sample.xlsx");
    Console.WriteLine("successfully created.");
    Console.ReadKey();    
}

/**
Complete Example
anchor-create-insert-data-and-save-example
**/
using IronXL;
static void Main(string [] args)
{
    //Create new XL WorkBook
    WorkBook wb = WorkBook.Create(ExcelFileFormat.XLSX);
    //Create worksheet of specified WorkBook
    WorkSheet ws = wb.CreateWorkSheet("Sheet1");
    //insert data by cell addressing
    ws ["A1"].Value = "Welcome";
    ws ["A2"].Value = "To";
    ws ["A3"].Value = "IronXL";
    //insert data by range
    ws ["C3:C8"].Value = "Cell Value";
    //save the file in specified path
    wb.SaveAs("sample.xlsx");
    Console.WriteLine("successfully created.");
    Console.ReadKey();    
}

'''
'''Complete Example
'''anchor-create-insert-data-and-save-example
'''*
Imports IronXL
Shared Sub Main(ByVal args() As String)
	'Create new XL WorkBook
	Dim wb As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
	'Create worksheet of specified WorkBook
	Dim ws As WorkSheet = wb.CreateWorkSheet("Sheet1")
	'insert data by cell addressing
	ws ("A1").Value = "Welcome"
	ws ("A2").Value = "To"
	ws ("A3").Value = "IronXL"
	'insert data by range
	ws ("C3:C8").Value = "Cell Value"
	'save the file in specified path
	wb.SaveAs("sample.xlsx")
	Console.WriteLine("successfully created.")
	Console.ReadKey()
End Sub

VB C#

這是我們新建的 Excel 工作簿名為 sample.xlsx 的截圖：

8. C# 從 DataTable 到 Excel

在原本可能是複雜的代碼行中，IronXL 提供了一種高效的方法將 DataTable 數據轉換成 Excel 文件並保存到指定位置。創建一個新的Excel檔案，並從DataTable中填充數據。簡單!

首先，讓我們創建一個帶有數據的新 DataTable。然後，創建Excel文件，插入並保存。

/**
Excel from DataTable
anchor-c-num-excel-from-datatable
**/
using IronXL;
static void Main(string [] args)
{
    //create new datatable
    DataTable dt = new DataTable();
    dt.Columns.Add("id");
    dt.Columns.Add("name");
    dt.Columns.Add("phone");
    //fill data in datatable
    for (int i = 0; i < 5; i++)
    {
        dt.Rows.Add("id" + i.ToString(), "name" + i.ToString(), "phone" + i.ToString());
    }
    //Create new XL file
    WorkBook wb = WorkBook.Create(ExcelFileFormat.XLSX);
    //Create WorkSheet
    WorkSheet ws = wb.CreateWorkSheet("sheet1");
    //send data in worksheet from datatable
    int j = 1; 
    foreach (DataRow row in dt.Rows)
    {
        ws ["A" + j].Value = row ["id"].ToString();
        ws ["B" + j].Value = row ["name"].ToString();
        ws ["C" + j].Value = row ["phone"].ToString();
        j = j + 1;
    }
    //save the file
    wb.SaveAs("sample.xlsx");
}

/**
Excel from DataTable
anchor-c-num-excel-from-datatable
**/
using IronXL;
static void Main(string [] args)
{
    //create new datatable
    DataTable dt = new DataTable();
    dt.Columns.Add("id");
    dt.Columns.Add("name");
    dt.Columns.Add("phone");
    //fill data in datatable
    for (int i = 0; i < 5; i++)
    {
        dt.Rows.Add("id" + i.ToString(), "name" + i.ToString(), "phone" + i.ToString());
    }
    //Create new XL file
    WorkBook wb = WorkBook.Create(ExcelFileFormat.XLSX);
    //Create WorkSheet
    WorkSheet ws = wb.CreateWorkSheet("sheet1");
    //send data in worksheet from datatable
    int j = 1; 
    foreach (DataRow row in dt.Rows)
    {
        ws ["A" + j].Value = row ["id"].ToString();
        ws ["B" + j].Value = row ["name"].ToString();
        ws ["C" + j].Value = row ["phone"].ToString();
        j = j + 1;
    }
    //save the file
    wb.SaveAs("sample.xlsx");
}

'''
'''Excel from DataTable
'''anchor-c-num-excel-from-datatable
'''*
Imports IronXL
Shared Sub Main(ByVal args() As String)
	'create new datatable
	Dim dt As New DataTable()
	dt.Columns.Add("id")
	dt.Columns.Add("name")
	dt.Columns.Add("phone")
	'fill data in datatable
	For i As Integer = 0 To 4
		dt.Rows.Add("id" & i.ToString(), "name" & i.ToString(), "phone" & i.ToString())
	Next i
	'Create new XL file
	Dim wb As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
	'Create WorkSheet
	Dim ws As WorkSheet = wb.CreateWorkSheet("sheet1")
	'send data in worksheet from datatable
	Dim j As Integer = 1
	For Each row As DataRow In dt.Rows
		ws ("A" & j).Value = row ("id").ToString()
		ws ("B" & j).Value = row ("name").ToString()
		ws ("C" & j).Value = row ("phone").ToString()
		j = j + 1
	Next row
	'save the file
	wb.SaveAs("sample.xlsx")
End Sub

VB C#

這是我們的輸出:

現在，讓我們使用 IronXL 為 XL WorkBook 設置儲存格屬性。

9. 設定 Excel 工作簿樣式

現在，讓我們設置單元格屬性。有時我們需要以多種不同的需求以程式設定樣式。 IronXL 為我們提供了許多選項，用於輕鬆設置不同函數的單元格樣式。

我們可以使用 Excel 檔案的單元格地址系統來指定應該在哪裡應用樣式。讓我們設定一些在日常生活中經常使用的基本樣式屬性。

//bold the text of specified cell
WorkSheet ["CellAddress"].Style.Font.Bold =true;

//Italic the text of specified cell
WorkSheet ["CellAddress"].Style.Font.Italic =true;

//Strikeout the text of specified cell
WorkSheet ["CellAddress"].Style.Font.Strikeout = true;

//border style of specific cell 
WorkSheet ["CellAddress"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Dotted;

//border color of specific cell 
WorkSheet ["CellAddress"].Style.BottomBorder.SetColor("color value");

//bold the text of specified cell
WorkSheet ["CellAddress"].Style.Font.Bold =true;

//Italic the text of specified cell
WorkSheet ["CellAddress"].Style.Font.Italic =true;

//Strikeout the text of specified cell
WorkSheet ["CellAddress"].Style.Font.Strikeout = true;

//border style of specific cell 
WorkSheet ["CellAddress"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Dotted;

//border color of specific cell 
WorkSheet ["CellAddress"].Style.BottomBorder.SetColor("color value");

'bold the text of specified cell
WorkSheet ("CellAddress").Style.Font.Bold =True

'Italic the text of specified cell
WorkSheet ("CellAddress").Style.Font.Italic =True

'Strikeout the text of specified cell
WorkSheet ("CellAddress").Style.Font.Strikeout = True

'border style of specific cell 
WorkSheet ("CellAddress").Style.BottomBorder.Type = IronXL.Styles.BorderType.Dotted

'border color of specific cell 
WorkSheet ("CellAddress").Style.BottomBorder.SetColor("color value")

VB C#

IronXL 亦提供了一種方法來設定指定範圍內的所有上述屬性。設置儲存格樣式將適用於該範圍內的所有儲存格，如下所示：

//bold the text of specified range cells
WorkSheet ["FromCellAddress : ToCellAddress"].Style.Font.Bold =true;

//Italic the text of specified range cells
WorkSheet ["FromCellAddress : ToCellAddress"].Style.Font.Italic =true;

//Strikeout the text of specified range cells
WorkSheet ["FromCellAddress : ToCellAddress"].Style.Font.Strikeout = true;

//border style of specified range cells 
WorkSheet ["FromCellAddress : ToCellAddress"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Dotted;

//border color of specified range cells 
WorkSheet ["FromCellAddress : ToCellAddress"].Style.BottomBorder.SetColor("color value");

//bold the text of specified range cells
WorkSheet ["FromCellAddress : ToCellAddress"].Style.Font.Bold =true;

//Italic the text of specified range cells
WorkSheet ["FromCellAddress : ToCellAddress"].Style.Font.Italic =true;

//Strikeout the text of specified range cells
WorkSheet ["FromCellAddress : ToCellAddress"].Style.Font.Strikeout = true;

//border style of specified range cells 
WorkSheet ["FromCellAddress : ToCellAddress"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Dotted;

//border color of specified range cells 
WorkSheet ["FromCellAddress : ToCellAddress"].Style.BottomBorder.SetColor("color value");

'bold the text of specified range cells
WorkSheet ("FromCellAddress : ToCellAddress").Style.Font.Bold =True

'Italic the text of specified range cells
WorkSheet ("FromCellAddress : ToCellAddress").Style.Font.Italic =True

'Strikeout the text of specified range cells
WorkSheet ("FromCellAddress : ToCellAddress").Style.Font.Strikeout = True

'border style of specified range cells 
WorkSheet ("FromCellAddress : ToCellAddress").Style.BottomBorder.Type = IronXL.Styles.BorderType.Dotted

'border color of specified range cells 
WorkSheet ("FromCellAddress : ToCellAddress").Style.BottomBorder.SetColor("color value")

VB C#

讓我們來看一個完整的範例，包括建立一個新的 XL WorkBook 並應用單元格樣式。

/**
Set Workbook Styling
anchor-set-excel-workbook-style
**/
using IronXL;
static void Main(string [] args)
{
    WorkBook wb = WorkBook.Create(ExcelFileFormat.XLSX);
    WorkSheet ws = wb.CreateWorkSheet("Sheet1");
    ws ["B2:G2"].Value = "Range1";          
    ws ["B4:G4"].Value = "Range2";
    //------setting the styles----------
    ws ["B2:D2"].Style.Font.Bold = true;
    ws ["E2:G2"].Style.Font.Italic = true;
    ws ["B4:D4"].Style.Font.Strikeout = true;
    ws ["E4:G4"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Dotted;
    ws ["E4:G4"].Style.BottomBorder.SetColor("#ff6600");
    wb.SaveAs("sample.xlsx");
    Console.WriteLine("successfully created.");
    Console.ReadKey();
}

/**
Set Workbook Styling
anchor-set-excel-workbook-style
**/
using IronXL;
static void Main(string [] args)
{
    WorkBook wb = WorkBook.Create(ExcelFileFormat.XLSX);
    WorkSheet ws = wb.CreateWorkSheet("Sheet1");
    ws ["B2:G2"].Value = "Range1";          
    ws ["B4:G4"].Value = "Range2";
    //------setting the styles----------
    ws ["B2:D2"].Style.Font.Bold = true;
    ws ["E2:G2"].Style.Font.Italic = true;
    ws ["B4:D4"].Style.Font.Strikeout = true;
    ws ["E4:G4"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Dotted;
    ws ["E4:G4"].Style.BottomBorder.SetColor("#ff6600");
    wb.SaveAs("sample.xlsx");
    Console.WriteLine("successfully created.");
    Console.ReadKey();
}

'''
'''Set Workbook Styling
'''anchor-set-excel-workbook-style
'''*
Imports IronXL
Shared Sub Main(ByVal args() As String)
	Dim wb As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
	Dim ws As WorkSheet = wb.CreateWorkSheet("Sheet1")
	ws ("B2:G2").Value = "Range1"
	ws ("B4:G4").Value = "Range2"
	'------setting the styles----------
	ws ("B2:D2").Style.Font.Bold = True
	ws ("E2:G2").Style.Font.Italic = True
	ws ("B4:D4").Style.Font.Strikeout = True
	ws ("E4:G4").Style.BottomBorder.Type = IronXL.Styles.BorderType.Dotted
	ws ("E4:G4").Style.BottomBorder.SetColor("#ff6600")
	wb.SaveAs("sample.xlsx")
	Console.WriteLine("successfully created.")
	Console.ReadKey()
End Sub

VB C#

以下是我們新創建的名為 sample.xlsx 的 Excel 工作簿的樣子：