C# 寫入 Excel [無需使用 Interop] 範例教學

.NET Excel庫可革新地將資料導入和導出到網頁應用程式和商業系統中。
伊萊賈·威廉姆斯
2019年5月26日
已更新 2024年12月10日
分享:
This article was translated from English: Does it need improvement?
Translated
View the article in English

按照逐步示例學習如何使用C#創建、打開和保存Excel文件,並執行基本操作,如獲得總和、平均值、計數等。 IronXL.Excel 是一款獨立的 .NET 軟體庫,用於讀取多種電子表格格式。 它不需要安裝Microsoft Excel,也不依賴於Interop。


概述

使用 IronXL 開啟和寫入 Excel 檔案

使用簡單易用的[IronXL C# 庫](/csharp/excel/" target="_blank)打開、寫入、保存和自訂 Excel 文件。

下載一個[從 GitHub 獲取的範例專案](https://github.com/magedo93/IronSoftware.git" target="_blank)或使用您自己的專案,然後按照教程進行。

  1. 從[NuGet](https://www.nuget.org/packages/IronXL.Excel" target="_blank)或 DLL 下載中安裝 IronXL Excel 庫

  2. 使用 WorkBook.Load 方法來讀取任何 XLS、XLSX 或 CSV 文件。

  3. 使用直觀語法獲取單元格值:sheet ["A11"].DecimalValue

    在本教學中,我們將引導您:

    • 安裝 IronXL.Excel:如何將 IronXL.Excel 安裝到現有的專案中。
    • 基本操作:使用 Excel 進行基本操作步驟,包括創建或打開工作簿、選擇工作表、選擇單元格以及保存工作簿。
    • 進階工作表操作:如何利用不同的操作功能,例如添加頁眉或頁腳、數學運算文件及其他功能。

      開啟 Excel 文件:快速代碼

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-1.cs
using IronXL;

WorkBook workBook = WorkBook.Load("test.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;
IronXL.Range range = workSheet["A2:A8"];
decimal total = 0;

// iterate over range of cells
foreach (var cell in range)
{
    Console.WriteLine("Cell {0} has value '{1}'", cell.RowIndex, cell.Value);
    if (cell.IsNumeric)
    {
        // Get decimal value to avoid floating numbers precision issue
        total += cell.DecimalValue;
    }
}

// Check formula evaluation
if (workSheet["A11"].DecimalValue == total)
{
    Console.WriteLine("Basic Test Passed");
}
Imports IronXL

Private workBook As WorkBook = WorkBook.Load("test.xlsx")
Private workSheet As WorkSheet = workBook.DefaultWorkSheet
Private range As IronXL.Range = workSheet("A2:A8")
Private total As Decimal = 0

' iterate over range of cells
For Each cell In range
	Console.WriteLine("Cell {0} has value '{1}'", cell.RowIndex, cell.Value)
	If cell.IsNumeric Then
		' Get decimal value to avoid floating numbers precision issue
		total += cell.DecimalValue
	End If
Next cell

' Check formula evaluation
If workSheet("A11").DecimalValue = total Then
	Console.WriteLine("Basic Test Passed")
End If
$vbLabelText   $csharpLabel

將變更寫入並保存至 Excel 文件:快速代碼

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-2.cs
workSheet["B1"].Value = 11.54;

// Save Changes
workBook.SaveAs("test.xlsx");
workSheet("B1").Value = 11.54

' Save Changes
workBook.SaveAs("test.xlsx")
$vbLabelText   $csharpLabel

第一步

免費安裝 IronXL C# 庫

立即在您的專案中使用IronXL,並享受免費試用。

第一步:
green arrow pointer


IronXL.Excel 提供了一個靈活且功能強大的庫,用於在 .NET 中打開、讀取、編輯和保存 Excel 文件。 它可以安裝並用於所有的 .NET 項目類型,如 Windows 應用程序、ASP.NET MVC 和 .NET Core 應用程序。

使用 NuGet 將 Excel 庫安裝到您的 Visual Studio 專案中

首先要安裝 IronXL.Excel。 要在項目中添加 IronXL.Excel 庫,我們有兩種方法:NuGet 包管理器或 NuGet 包管理器控制台。

要使用 NuGet 將 IronXL.Excel 程式庫添加到我們的項目中,我們可以使用可視化界面,即 NuGet 包管理器:

  1. 使用滑鼠 -> 右鍵點擊項目名稱 -> 選擇管理 NuGet 套件

    Select Manage Nuget Package related to 使用 NuGet 將 Excel 庫安裝到您的 Visual Studio 專案中

  2. 從「瀏覽」標籤 -> 搜尋 IronXL.Excel -> 安裝

    Search For Ironxl related to 使用 NuGet 將 Excel 庫安裝到您的 Visual Studio 專案中

  3. 我們完成了

    And We Are Done related to 使用 NuGet 將 Excel 庫安裝到您的 Visual Studio 專案中

    使用 NuGet 套件管理器控制台進行安裝

  4. 從工具 -> NuGet 套件管理器 -> 套件管理器控制台

    Package Manager Console related to 使用 NuGet 將 Excel 庫安裝到您的 Visual Studio 專案中

  5. 運行命令 -> Install-Package IronXL.Excel -Version 2019.5.2

    Install Package Ironxl related to 使用 NuGet 將 Excel 庫安裝到您的 Visual Studio 專案中

    手動使用DLL安裝

    您也可以選擇手動將DLL 安裝到您的專案或全域組件快取中。 ```

PM > Install-Package IronXL.Excel

<hr class="separator">
<h4 class="tutorial-segment-title">操作教程</h4>

## 2. 基本操作:創建、打開、保存 &num;&num;

### 2.1. 範例專案:HelloWorld 控制台應用程式 &num;&num;&num;

<p class="list-description">創建一個HelloWorld專案</p>
<p class="list-decimal">2.1.1. 打開 Visual Studio</p>
<a rel="nofollow" href="/img/tutorials/csharp-open-write-excel-file/open-visual-studio.png" target="_blank"><img src="/img/tutorials/csharp-open-write-excel-file/open-visual-studio.png" alt="" class="img-responsive add-shadow img-margin" style="max-width:100%;"></a>
<p class="list-decimal">2.1.2. 選擇建立新專案</p>
<a rel="nofollow" href="/img/tutorials/csharp-open-write-excel-file/choose-create-new-project.png" target="_blank"><img src="/img/tutorials/csharp-open-write-excel-file/choose-create-new-project.png" alt="" class="img-responsive add-shadow img-margin" style="max-width:100%;"></a>
<p class="list-decimal">  選擇控制台應用程式 (.NET 框架)</p>
<a rel="nofollow" href="/img/tutorials/csharp-open-write-excel-file/choose-console-app.jpg" target="_blank"><img src="/img/tutorials/csharp-open-write-excel-file/choose-console-app.jpg" alt="" class="img-responsive add-shadow img-margin" style="max-width:100%;"></a>
<p class="list-decimal">將我們的範例命名為“HelloWorld”,然後點擊創建。</p>
<a rel="nofollow" href="/img/tutorials/csharp-open-write-excel-file/give-our-sample-name.jpg" target="_blank"><img src="/img/tutorials/csharp-open-write-excel-file/give-our-sample-name.jpg" alt="" class="img-responsive add-shadow img-margin" style="max-width:100%;"></a>
<p class="list-decimal">2.1.5. 現在我們已經建立了控制台應用程序</p>
<a rel="nofollow" href="/img/tutorials/csharp-open-write-excel-file/console-application-created.jpg" target="_blank"><img src="/img/tutorials/csharp-open-write-excel-file/console-application-created.jpg" alt="" class="img-responsive add-shadow img-margin" style="max-width:100%;"></a>
<p class="list-decimal">2.1.6. Add IronXL.Excel => click install</p>
<a rel="nofollow" href="/img/tutorials/csharp-open-write-excel-file/add-ironxl-click-install.jpg" target="_blank"><img src="/img/tutorials/csharp-open-write-excel-file/add-ironxl-click-install.jpg" alt="" class="img-responsive add-shadow img-margin" style="max-width:100%;"></a>
<p class="list-decimal">2.1.7. 添加我們的前幾行代碼來讀取 Excel 文件中第一張工作表的第一個單元格,並打印。</p>

```cs
:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-3.cs

2.2. 建立新的 Excel 檔案 ###

使用 IronXL 創建新的 Excel 文件

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-4.cs
WorkBook workBook = WorkBook.Create(ExcelFileFormat.XLSX);
workBook.Metadata.Title = "IronXL New File";
WorkSheet workSheet = workBook.CreateWorkSheet("1stWorkSheet");
workSheet["A1"].Value = "Hello World";
workSheet["A2"].Style.BottomBorder.SetColor("#ff6600");
workSheet["A2"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Dashed;
Dim workBook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
workBook.Metadata.Title = "IronXL New File"
Dim workSheet As WorkSheet = workBook.CreateWorkSheet("1stWorkSheet")
workSheet("A1").Value = "Hello World"
workSheet("A2").Style.BottomBorder.SetColor("#ff6600")
workSheet("A2").Style.BottomBorder.Type = IronXL.Styles.BorderType.Dashed
$vbLabelText   $csharpLabel

2.3. 開啟(CSV、XML、JSON 列表)為工作簿 ###

2.3.1. 開啟 CSV 檔案

2.3.2 建立一個新的文字檔案,並在其中添加姓名和年齡列表(見示例),然後將其保存為CSVList.csv

Code Snippet related to 2.3. 開啟(CSV、XML、JSON 列表)為工作簿 ###

您的代碼片段應該看起來像這樣

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-5.cs
WorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\CSVList.csv");
WorkSheet workSheet = workBook.WorkSheets.First();
string cell = workSheet["A1"].StringValue;
Console.WriteLine(cell);
Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\CSVList.csv")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
Dim cell As String = workSheet("A1").StringValue
Console.WriteLine(cell)
$vbLabelText   $csharpLabel

2.3.3. Open XML File Create an XML file that contains a countries list: the root element “countries”, with children elements “country”, and each country has properties that define the country like code, continent, etc.

<?xml version="1.0" encoding="utf-8"?>
<countries xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <country code="ae" handle="united-arab-emirates" continent="asia" iso="784">United Arab Emirates</country>
        <country code="gb" handle="united-kingdom" continent="europe" alt="England Scotland Wales GB UK Great Britain Britain Northern" boost="3" iso="826">United Kingdom</country>
        <country code="us" handle="united-states" continent="north america" alt="US America USA" boost="2" iso="840">United States</country>
        <country code="um" handle="united-states-minor-outlying-islands" continent="north america" iso="581">United States Minor Outlying Islands</country>
</countries>
<?xml version="1.0" encoding="utf-8"?>
<countries xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <country code="ae" handle="united-arab-emirates" continent="asia" iso="784">United Arab Emirates</country>
        <country code="gb" handle="united-kingdom" continent="europe" alt="England Scotland Wales GB UK Great Britain Britain Northern" boost="3" iso="826">United Kingdom</country>
        <country code="us" handle="united-states" continent="north america" alt="US America USA" boost="2" iso="840">United States</country>
        <country code="um" handle="united-states-minor-outlying-islands" continent="north america" iso="581">United States Minor Outlying Islands</country>
</countries>
HTML

2.3.4. 複製以下程式碼片段以將 XML 作為工作簿打開

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-7.cs
DataSet xmldataset = new DataSet();
xmldataset.ReadXml($@"{Directory.GetCurrentDirectory()}\Files\CountryList.xml");
WorkBook workBook = IronXL.WorkBook.Load(xmldataset);
WorkSheet workSheet = workBook.WorkSheets.First();
Dim xmldataset As New DataSet()
xmldataset.ReadXml($"{Directory.GetCurrentDirectory()}\Files\CountryList.xml")
Dim workBook As WorkBook = IronXL.WorkBook.Load(xmldataset)
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
$vbLabelText   $csharpLabel

2.3.5. Open JSON List as workbook Create JSON country list

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-8.cs
[
    {
        "name": "United Arab Emirates",
        "code": "AE"
    },
    {
        "name": "United Kingdom",
        "code": "GB"
    },
    {
        "name": "United States",
        "code": "US"
    },
    {
        "name": "United States Minor Outlying Islands",
        "code": "UM"
    }
]
'INSTANT VB TODO TASK: The following line uses invalid syntax:
'[{ "name": "United Arab Emirates", "code": "AE" }, { "name": "United Kingdom", "code": "GB" }, { "name": "United States", "code": "US" }, { "name": "United States Minor Outlying Islands", "code": "UM" }]
$vbLabelText   $csharpLabel

2.3.6. 建立一個將映射到 JSON 的國家模型

Create Country Model related to 2.3. 開啟(CSV、XML、JSON 列表)為工作簿 ###

這是類別代碼片段

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-9.cs
public class CountryModel
    {
        public string name { get; set; }
        public string code { get; set; }
    }
Public Class CountryModel
		Public Property name() As String
		Public Property code() As String
End Class
$vbLabelText   $csharpLabel

2.3.8. 添加Newtonsoft庫以將JSON轉換為國家模型列表

Add Newtonsoft Library To Convert Json related to 2.3. 開啟(CSV、XML、JSON 列表)為工作簿 ###

2.3.9 為了將清單轉換為資料集,我們必須為清單創建一個新的擴展。新增名為 “ListConvertExtension” 的擴展類別。

Convert List To Dataset related to 2.3. 開啟(CSV、XML、JSON 列表)為工作簿 ###

然後添加此代碼片段

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-10.cs
public static class ListConvertExtension
    {
        public static DataSet ToDataSet<T>(this IList<T> list)
        {
            Type elementType = typeof(T);
            DataSet ds = new DataSet();
            DataTable t = new DataTable();
            ds.Tables.Add(t);
            //add a column to table for each public property on T
            foreach (var propInfo in elementType.GetProperties())
            {
                Type ColType = Nullable.GetUnderlyingType(propInfo.PropertyType) ?? propInfo.PropertyType;
                t.Columns.Add(propInfo.Name, ColType);
            }
            //go through each property on T and add each value to the table
            foreach (T item in list)
            {
                DataRow row = t.NewRow();
                foreach (var propInfo in elementType.GetProperties())
                {
                    row[propInfo.Name] = propInfo.GetValue(item, null) ?? DBNull.Value;
                }
                t.Rows.Add(row);
            }
            return ds;
        }
    }
Public Module ListConvertExtension
		<System.Runtime.CompilerServices.Extension> _
		Public Function ToDataSet(Of T)(ByVal list As IList(Of T)) As DataSet
			Dim elementType As Type = GetType(T)
			Dim ds As New DataSet()
'INSTANT VB NOTE: The variable t was renamed since Visual Basic does not allow local variables with the same name as method-level generic type parameters:
			Dim t_Conflict As New DataTable()
			ds.Tables.Add(t_Conflict)
			'add a column to table for each public property on T
			For Each propInfo In elementType.GetProperties()
				Dim ColType As Type = If(Nullable.GetUnderlyingType(propInfo.PropertyType), propInfo.PropertyType)
				t_Conflict.Columns.Add(propInfo.Name, ColType)
			Next propInfo
			'go through each property on T and add each value to the table
			For Each item As T In list
				Dim row As DataRow = t_Conflict.NewRow()
				For Each propInfo In elementType.GetProperties()
					row(propInfo.Name) = If(propInfo.GetValue(item, Nothing), DBNull.Value)
				Next propInfo
				t_Conflict.Rows.Add(row)
			Next item
			Return ds
		End Function
End Module
$vbLabelText   $csharpLabel

最後將此數據集載入為工作簿

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-11.cs
StreamReader jsonFile = new StreamReader($@"{Directory.GetCurrentDirectory()}\Files\CountriesList.json");
var countryList = Newtonsoft.Json.JsonConvert.DeserializeObject<CountryModel[]>(jsonFile.ReadToEnd());
var xmldataset = countryList.ToDataSet();
WorkBook workBook = IronXL.WorkBook.Load(xmldataset);
WorkSheet workSheet = workBook.WorkSheets.First();
Dim jsonFile As New StreamReader($"{Directory.GetCurrentDirectory()}\Files\CountriesList.json")
Dim countryList = Newtonsoft.Json.JsonConvert.DeserializeObject(Of CountryModel())(jsonFile.ReadToEnd())
Dim xmldataset = countryList.ToDataSet()
Dim workBook As WorkBook = IronXL.WorkBook.Load(xmldataset)
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
$vbLabelText   $csharpLabel

2.4. 儲存和匯出 ###

我們可以使用以下指令將 Excel 檔案儲存或匯出為多種檔案格式,如(“.xlsx”,”.csv”,”.html”)。

2.4.1. Save to “.xlsx” To Save to “.xlsx” use saveAs function

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-12.cs
WorkBook workBook = WorkBook.Create(ExcelFileFormat.XLSX);
workBook.Metadata.Title = "IronXL New File";

WorkSheet workSheet = workBook.CreateWorkSheet("1stWorkSheet");
workSheet["A1"].Value = "Hello World";
workSheet["A2"].Style.BottomBorder.SetColor("#ff6600");
workSheet["A2"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Dashed;

workBook.SaveAs($@"{Directory.GetCurrentDirectory()}\Files\HelloWorld.xlsx");
Dim workBook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
workBook.Metadata.Title = "IronXL New File"

Dim workSheet As WorkSheet = workBook.CreateWorkSheet("1stWorkSheet")
workSheet("A1").Value = "Hello World"
workSheet("A2").Style.BottomBorder.SetColor("#ff6600")
workSheet("A2").Style.BottomBorder.Type = IronXL.Styles.BorderType.Dashed

workBook.SaveAs($"{Directory.GetCurrentDirectory()}\Files\HelloWorld.xlsx")
$vbLabelText   $csharpLabel

2.4.2. Save to csv “.csv” To save to “.csv” we can use SaveAsCsv and pass to it 2 parameters 1st parameter the file name and path the 2nd parameter is the delimiter like (“,” or “” or “:”)

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-13.cs
workBook.SaveAsCsv($@"{Directory.GetCurrentDirectory()}\Files\HelloWorld.csv", delimiter: "|");
workBook.SaveAsCsv($"{Directory.GetCurrentDirectory()}\Files\HelloWorld.csv", delimiter:= "|")
$vbLabelText   $csharpLabel

2.4.3. 儲存為 JSON “.json” 要儲存為 Json “.json”,請按照以下方式使用 SaveAsJson

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-14.cs
workBook.SaveAsJson($@"{Directory.GetCurrentDirectory()}\Files\HelloWorldJSON.json");
workBook.SaveAsJson($"{Directory.GetCurrentDirectory()}\Files\HelloWorldJSON.json")
$vbLabelText   $csharpLabel

結果檔案應該看起來像這樣

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-15.cs
[
    [
        "Hello World"
    ],
    [
        ""
    ]
]
'INSTANT VB TODO TASK: The following line uses invalid syntax:
'[["Hello World"], [""]]
$vbLabelText   $csharpLabel

2.4.4. Save to XML “.xml” To save to xml use SaveAsXml as follow

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-16.cs
workBook.SaveAsXml($@"{Directory.GetCurrentDirectory()}\Files\HelloWorldXML.XML");
workBook.SaveAsXml($"{Directory.GetCurrentDirectory()}\Files\HelloWorldXML.XML")
$vbLabelText   $csharpLabel

結果應該是這樣的

<?xml version="1.0" standalone="yes"?>
<_x0031_stWorkSheet>
  <_x0031_stWorkSheet>
    <Column1 xsi:type="xs:string" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">Hello World</Column1>
  </_x0031_stWorkSheet>
  <_x0031_stWorkSheet>
    <Column1 xsi:type="xs:string" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" />
  </_x0031_stWorkSheet>
</_x0031_stWorkSheet>
<?xml version="1.0" standalone="yes"?>
<_x0031_stWorkSheet>
  <_x0031_stWorkSheet>
    <Column1 xsi:type="xs:string" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">Hello World</Column1>
  </_x0031_stWorkSheet>
  <_x0031_stWorkSheet>
    <Column1 xsi:type="xs:string" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" />
  </_x0031_stWorkSheet>
</_x0031_stWorkSheet>
HTML

3. 高級操作:總和、平均、計數等。

讓我們應用常見的 Excel 函數如 SUM、AVG、Count,並查看每個程式碼片段。

3.1. 求和範例&###

讓我們來計算這個列表的總和。我創建了一個 Excel 文件,命名為 “Sum.xlsx”,並手動添加了這個數字列表。

Sum Example related to 3.1. 求和範例&###

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-18.cs
WorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
WorkSheet workSheet = workBook.WorkSheets.First();
decimal sum = workSheet["A2:A4"].Sum();
Console.WriteLine(sum);
Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
Dim sum As Decimal = workSheet("A2:A4").Sum()
Console.WriteLine(sum)
$vbLabelText   $csharpLabel

3.2. 平均範例 ###

使用相同的文件,我們可以得到平均值:

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-19.cs
WorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
WorkSheet workSheet = workBook.WorkSheets.First();
decimal avg = workSheet["A2:A4"].Avg();
Console.WriteLine(avg);
Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
Dim avg As Decimal = workSheet("A2:A4").Avg()
Console.WriteLine(avg)
$vbLabelText   $csharpLabel

3.3. 計數範例 ###

使用相同的檔案,我們也可以獲取序列中的元素數量:

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-20.cs
WorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
WorkSheet workSheet = workBook.WorkSheets.First();
decimal count = workSheet["A2:A4"].Count();
Console.WriteLine(count);
Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
Dim count As Decimal = workSheet("A2:A4").Count()
Console.WriteLine(count)
$vbLabelText   $csharpLabel

3.4. 最大範例 ###

使用同一個文件,我們可以得到單元格範圍的最大值:

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-21.cs
WorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
WorkSheet workSheet = workBook.WorkSheets.First();
decimal max = workSheet["A2:A4"].Max();
Console.WriteLine(max);
Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
Dim max As Decimal = workSheet("A2:A4").Max()
Console.WriteLine(max)
$vbLabelText   $csharpLabel

- 我們可以將變換函數應用於最大值函數的結果:

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-22.cs
WorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
WorkSheet workSheet = workBook.WorkSheets.First();
bool max2 = workSheet["A1:A4"].Max(c => c.IsFormula);
Console.WriteLine(max2);
Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
Dim max2 As Boolean = workSheet("A1:A4").Max(Function(c) c.IsFormula)
Console.WriteLine(max2)
$vbLabelText   $csharpLabel

此範例在控制台中寫入“false”。

3.5. 最小範例 ###

使用相同的檔案,我們可以獲取單元格範圍的最小值:

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-23.cs
WorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
WorkSheet workSheet = workBook.WorkSheets.First();
decimal min = workSheet["A1:A4"].Min();
Console.WriteLine(min);
Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
Dim min As Decimal = workSheet("A1:A4").Min()
Console.WriteLine(min)
$vbLabelText   $csharpLabel

3.6. 排序單元格示例 ###

使用相同的文件,我們可以按升序或降序排序儲存格:

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-24.cs
WorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
WorkSheet workSheet = workBook.WorkSheets.First();
workSheet["A1:A4"].SortAscending();
// workSheet["A1:A4"].SortDescending(); to order descending
workBook.SaveAs("SortedSheet.xlsx");
Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
workSheet("A1:A4").SortAscending()
' workSheet["A1:A4"].SortDescending(); to order descending
workBook.SaveAs("SortedSheet.xlsx")
$vbLabelText   $csharpLabel

3.7. 條件範例 ###

使用同一個檔案,我們可以使用 Formula 屬性來設置或獲取儲存格的公式:

3.7.1. 儲存為 XML “.xml”

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-25.cs
WorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
WorkSheet workSheet = workBook.WorkSheets.First();
int i = 1;
foreach (var cell in workSheet["B1:B4"])
{
    cell.Formula = "=IF(A" + i + ">=20,\" Pass\" ,\" Fail\" )";
    i++;
}
workBook.SaveAs($@"{Directory.GetCurrentDirectory()}\Files\NewExcelFile.xlsx");
Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
Dim i As Integer = 1
For Each cell In workSheet("B1:B4")
	cell.Formula = "=IF(A" & i & ">=20,"" Pass"" ,"" Fail"" )"
	i += 1
Next cell
workBook.SaveAs($"{Directory.GetCurrentDirectory()}\Files\NewExcelFile.xlsx")
$vbLabelText   $csharpLabel

7.2. 使用前一個範例生成的檔案,我們可以獲取儲存格的公式:

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-26.cs
WorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\NewExcelFile.xlsx");
WorkSheet workSheet = workBook.WorkSheets.First();
foreach (var cell in workSheet["B1:B4"])
{
    Console.WriteLine(cell.Formula);
}
Console.ReadKey();
Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\NewExcelFile.xlsx")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
For Each cell In workSheet("B1:B4")
	Console.WriteLine(cell.Formula)
Next cell
Console.ReadKey()
$vbLabelText   $csharpLabel

3.8. 修剪範例

要套用修剪功能(消除儲存格中的所有多餘空格),我將此列添加到 sum.xlsx 文件中。

Trim Example related to 3.8. 修剪範例

請使用以下代碼

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-27.cs
WorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\NewExcelFile.xlsx");
WorkSheet workSheet = workBook.WorkSheets.First();
int i = 1;
foreach (var cell in workSheet["f1:f4"])
{
    cell.Formula = "=trim(D" + i + ")";
    i++;
}
workBook.SaveAs("editedFile.xlsx");
Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\NewExcelFile.xlsx")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
Dim i As Integer = 1
For Each cell In workSheet("f1:f4")
	cell.Formula = "=trim(D" & i & ")"
	i += 1
Next cell
workBook.SaveAs("editedFile.xlsx")
$vbLabelText   $csharpLabel

因此,您可以以相同的方式應用公式。


4. 使用多工作表活頁簿 ##

我們將介紹如何處理具有多個工作表的工作簿。

4.1. 從同一工作簿中的多個工作表讀取資料 ###

我創建了一個包含兩個工作表的 xlsx 文件:“Sheet1”、“Sheet2”

到目前為止,我們一直使用 WorkSheets.First() 處理第一個工作表。在此範例中,我們將指定工作表名稱並進行處理。

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-28.cs
WorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\testFile.xlsx");
WorkSheet workSheet = workBook.GetWorkSheet("Sheet2");
var range = workSheet["A2:D2"];
foreach (var cell in range)
{
    Console.WriteLine(cell.Text);
}
Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\testFile.xlsx")
Dim workSheet As WorkSheet = workBook.GetWorkSheet("Sheet2")
Dim range = workSheet("A2:D2")
For Each cell In range
	Console.WriteLine(cell.Text)
Next cell
$vbLabelText   $csharpLabel

4.2. 新增工作表到工作簿 ###

我們還可以在工作簿中添加新工作表:

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-29.cs
WorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\testFile.xlsx");
WorkSheet workSheet = workBook.CreateWorkSheet("new_sheet");
workSheet["A1"].Value = "Hello World";
workBook.SaveAs(@"F:\MY WORK\IronPackage\Xl tutorial\newFile.xlsx");
Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\testFile.xlsx")
Dim workSheet As WorkSheet = workBook.CreateWorkSheet("new_sheet")
workSheet("A1").Value = "Hello World"
workBook.SaveAs("F:\MY WORK\IronPackage\Xl tutorial\newFile.xlsx")
$vbLabelText   $csharpLabel

5. 與 Excel 資料庫整合 ##

讓我們看看如何將數據導出/導入到數據庫。

我創建了名為 "TestDb" 的資料庫,其中包含一個 Country 表,該表有兩個欄位:Id(int,自增)和 CountryName(string)。

5.1. 用資料庫資料填充 Excel 工作表 ###

在這裡我們將創建一個新工作表,並用國家表中的數據填充它。

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-30.cs
TestDbEntities dbContext = new TestDbEntities();
var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\testFile.xlsx");
WorkSheet sheet = workbook.CreateWorkSheet("FromDb");
List<Country> countryList = dbContext.Countries.ToList();
sheet.SetCellValue(0, 0, "Id");
sheet.SetCellValue(0, 1, "Country Name");
int row = 1;
foreach (var item in countryList)
{
    sheet.SetCellValue(row, 0, item.id);
    sheet.SetCellValue(row, 1, item.CountryName);
    row++;
}
workbook.SaveAs("FilledFile.xlsx");
Dim dbContext As New TestDbEntities()
Dim workbook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\testFile.xlsx")
Dim sheet As WorkSheet = workbook.CreateWorkSheet("FromDb")
Dim countryList As List(Of Country) = dbContext.Countries.ToList()
sheet.SetCellValue(0, 0, "Id")
sheet.SetCellValue(0, 1, "Country Name")
Dim row As Integer = 1
For Each item In countryList
	sheet.SetCellValue(row, 0, item.id)
	sheet.SetCellValue(row, 1, item.CountryName)
	row += 1
Next item
workbook.SaveAs("FilledFile.xlsx")
$vbLabelText   $csharpLabel

5.2. 使用 Excel 表格中的數據填充資料庫 ###

將數據插入到 TestDb 資料庫的 Country 表格中

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-31.cs
TestDbEntities dbContext = new TestDbEntities();
var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\testFile.xlsx");
WorkSheet sheet = workbook.GetWorkSheet("Sheet3");
System.Data.DataTable dataTable = sheet.ToDataTable(true);
foreach (DataRow row in dataTable.Rows)
{
    Country c = new Country();
    c.CountryName = row[1].ToString();
    dbContext.Countries.Add(c);
}
dbContext.SaveChanges();
Dim dbContext As New TestDbEntities()
Dim workbook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\testFile.xlsx")
Dim sheet As WorkSheet = workbook.GetWorkSheet("Sheet3")
Dim dataTable As System.Data.DataTable = sheet.ToDataTable(True)
For Each row As DataRow In dataTable.Rows
	Dim c As New Country()
	c.CountryName = row(1).ToString()
	dbContext.Countries.Add(c)
Next row
dbContext.SaveChanges()
$vbLabelText   $csharpLabel

進一步閱讀

若要進一步了解如何使用 IronXL,您可以查看本節中的其他教程,以及我們首頁上的範例,大多數開發者發現這些足以讓他們開始使用。

我們的API 參考包含對WorkBook類的具體參考。


快速指南

Brand Visual Studio related to 快速指南

Download this Tutorial as C# Source Code

The full free C# for Excel Source Code for this tutorial is available to download as a zipped Visual Studio 2017 project file.

Download

在 GitHub 上探索此教學

該專案的源代碼在GitHub上提供C#和VB.NET版本。

只需幾分鐘即可使用此代碼輕鬆啟動和運行。該項目保存為 Microsoft Visual Studio 2017 項目,但與任何 .NET IDE 兼容。

如何在 GitHub 上使用 C# 打開和寫入 Excel 文件
Github Icon related to 快速指南
Documentation related to 快速指南

IronXL API 參考文檔

Explore the IronXL API 參考文檔, outlining the details of all of IronXL’s features, namespaces, classes, methods fields and enums.

查看 API 參考資料
.NET Excel庫可革新地將資料導入和導出到網頁應用程式和商業系統中。
伊萊賈·威廉姆斯
產品開發工程師

Elijah 是一家負責大型企業財務報告系統的開發和測試團隊的工程師。Elijah 是 IronXL 庫的早期採用者,並將其用於其 Excel 報告介面的核心。