C# 寫入 Excel [不使用 Interop] 程式碼範例教程
按照逐步示例學習如何使用C#創建、打開和保存Excel文件,並執行基本操作,如獲得總和、平均值、計數等。 IronXL.Excel 是一款獨立的 .NET 軟體庫,用於讀取多種電子表格格式。 它不需要微軟 Excel無需安裝,也不依賴於Interop。
如何用 C# 編寫 Excel 檔案
- 下載 Write Excel C# 函式庫
- 創建並打開新的 CSV 或 XML Excel 文件作為 Excel 活頁簿
- 保存並匯出您的 Excel 活頁簿
- 在多個 Excel 工作表中應用高級操作
- 與 Excel 數據庫整合
概述
使用 IronXL 開啟和寫入 Excel 檔案
開啟、寫入、儲存和自訂 Excel 文件,簡單易用 IronXL C# 函式庫。
下載 a 來自 GitHub 的範例專案 或使用您自己的,並按照教程操作。
從 NuGet 安裝 IronXL Excel 函式庫 NuGet 或者DLL下載
使用 WorkBook.Load 方法來讀取任何 XLS、XLSX 或 CSV 文件。
使用直观语法获取单元格的值:
工作表 ["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
將變更寫入並保存至 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")
第一步
1. 免費安裝 IronXL C# 程式庫
立即開始在您的專案中使用IronPDF,並享受免費試用。
查看 IronXL 上 Nuget 方便快速安裝和部署。擁有超過 800 萬次下載,它正在使用 C# 改造 Excel。
Install-Package IronXL.Excel
請考慮安裝 IronXL DLL 直接下載並手動安裝到您的專案或GAC表單: IronXL.zip
手動安裝到您的項目中
下載DLLIronXL.Excel 提供了一個靈活且功能強大的庫,用於在 .NET 中打開、讀取、編輯和保存 Excel 文件。 它可以安裝並用於所有的 .NET 項目類型,如 Windows 應用程序、ASP.NET MVC 和 .NET Core 應用程序。
使用 NuGet 將 Excel 庫安裝到您的 Visual Studio 專案中
首先要安裝 IronXL.Excel。 要在項目中添加 IronXL.Excel 庫,我們有兩種方法:NuGet 包管理器或 NuGet 包管理器控制台。
要使用 NuGet 將 IronXL.Excel 程式庫添加到我們的項目中,我們可以使用可視化界面,即 NuGet 包管理器:
使用 NuGet 套件管理器控制台進行安裝
手動使用DLL安裝
您也可以選擇手動安裝 DLL 到您的項目或全域程序集快取。 請提供內容以進行翻譯。
PM > Install-Package IronXL.Excel 請提供內容以進行翻譯。
操作教程
2. 基本操作:建立、開啟、儲存
2.1. 範例專案:HelloWorld 控制台應用程式
創建一個HelloWorld專案
.1.1. 打開 Visual Studio
.1.2. 選擇建立新專案
選擇控制台應用程式 (.NET 框架)
將我們的範例命名為“HelloWorld”,然後點擊創建。
.1.5. 現在我們已經建立了控制台應用程序
.1.6. 添加 IronXL.Excel => 点击安装
.1.7. 添加我們的前幾行代碼來讀取 Excel 文件中第一張工作表的第一個單元格,並打印。
:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-3.cs
WorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\HelloWorld.xlsx");
WorkSheet workSheet = workBook.WorkSheets.First();
string cell = workSheet["A1"].StringValue;
Console.WriteLine(cell);
Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\HelloWorld.xlsx")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
Dim cell As String = workSheet("A1").StringValue
Console.WriteLine(cell)
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
2.3. 將 (CSV、XML、JSON 列表) 打開為工作簿
.3.1. 開啟 CSV 檔案
.3.2 建立一個新的文字檔案,並在其中添加姓名和年齡列表(見示例),然後將其保存為CSVList.csv
您的代碼片段應該看起來像這樣
: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)
2.3.3. 打開 XML 檔案 創建一個包含國家列表的 XML 文件:根元素為“countries”,子元素為“country”,每個國家都有定義國家屬性的屬性,如代碼、大陸等。
<?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>
.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()
2.3.5. 將 JSON 列表作為工作簿打开 建立 JSON 國家列表
: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" }]
.3.6. 建立一個將映射到 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
.3.8. 添加Newtonsoft庫以將JSON轉換為國家模型列表
.3.9 為了將清單轉換為資料集,我們必須為清單創建一個新的擴展。新增名為 “ListConvertExtension” 的擴展類別。
然後添加此代碼片段
: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
最後將此數據集載入為工作簿
: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()
2.4. 儲存和匯出
我們可以使用以下指令將 Excel 檔案儲存或匯出為多種檔案格式,如(“.xlsx”,”.csv”,”.html”)。
2.4.1. 保存為 ".xlsx" 要保存為“.xlsx”,使用saveAs函數
: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")
2.4.2. 儲存為 csv “.csv” 要保存為“ .csv ”,我們可以使用 SaveAsCsv 並傳遞給它兩個參數:第一個參數是文件名和路徑,第二個參數是分隔符。例如 (“,”或“ ” 或 “:”)
: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:= "|")
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")
結果檔案應如下所示
: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"], [""]]
2.4.4. 儲存為 XML “.xml” 要保存為 XML,請按以下方式使用 SaveAsXml
: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")
結果應如下所示
<?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>
3. 進階操作:求和、平均、計數等。
讓我們應用常見的 Excel 函數如 SUM、AVG、Count,並查看每個程式碼片段。
3.1. 加總範例
讓我們來計算這個列表的總和。我創建了一個 Excel 文件,命名為 “Sum.xlsx”,並手動添加了這個數字列表。
: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)
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)
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)
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)
- 我們可以將變換函數應用於最大值函數的結果:
: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)
此範例在控制台中寫入“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)
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")
3.7. 如果條件範例
使用同一個檔案,我們可以使用 Formula 屬性來設置或獲取儲存格的公式:
.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")
.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()
3.8. 範例修剪
要套用修剪功能(消除儲存格中的所有多餘空格),我將此列添加到 sum.xlsx 文件中。
請使用以下代碼
: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")
因此,您可以以相同的方式應用公式。
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
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")
5. 與 Excel 資料庫整合
讓我們看看如何將數據導出/導入到數據庫。
我創建了一個名為"TestDb"的數據庫,其中包含一個國家表,該表有兩列:Id(int
, identity
),國家名稱(字串)
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")
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()
進一步閱讀
若要進一步了解如何使用 IronXL,您可以查看本節中的其他教程,以及我們首頁上的範例,大多數開發者發現這些足以讓他們開始使用。
我们的API 參考文獻 包含對於該的具體參考 工作簿
類別。
快速指南
在 GitHub 上探索此教學
該專案的源代碼在GitHub上提供C#和VB.NET版本。
只需幾分鐘即可使用此代碼輕鬆啟動和運行。該項目保存為 Microsoft Visual Studio 2017 項目,但與任何 .NET IDE 兼容。
如何在 GitHub 上用 C# 開啟和寫入 Excel 檔案