using IronXL; using System; using System.Linq; // Supported for XLSX, XLS, XLSM, XLTX, CSV and TSV WorkBook workBook = WorkBook.Load("sample.xlsx"); // Select worksheet at index 0 WorkSheet workSheet = workBook.WorkSheets[0]; // Get any existing worksheet WorkSheet firstSheet = workBook.DefaultWorkSheet; // Select a cell and return the converted value int cellValue = workSheet["A2"].IntValue; // Read from ranges of cells elegantly. foreach (var cell in workSheet["A2:A10"]) { Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text); } // Calculate aggregate values such as Min, Max and Sum decimal sum = workSheet["A2:A10"].Sum(); // Linq compatible decimal max = workSheet["A2:A10"].Max(c => c.DecimalValue);

using IronSoftware.Drawing; using IronXL; using IronXL.Styles; using System.Linq; WorkBook workBook = WorkBook.Load("sample.xls"); WorkSheet workSheet = workBook.WorkSheets.First(); var range = workSheet["A1:H10"]; var cell = range.First(); // Set background color of the cell with an rgb string cell.Style.SetBackgroundColor("#428D65"); // Apply styling to the whole range. // Set underline property to the font // FontUnderlineType is enum that stands for different types of font underlying range.Style.Font.Underline = FontUnderlineType.SingleAccounting; // Define whether to use horizontal line through the text or not range.Style.Font.Strikeout = false; // Define whether the font is bold or not range.Style.Font.Bold = true; // Define whether the font is italic or not range.Style.Font.Italic = false; // Get or set script property of a font // Font script enum stands for available options range.Style.Font.FontScript = FontScript.Super; // Set the type of the border line // There are also TopBorder,LeftBorder,RightBorder,DiagonalBorder properties // BorderType enum indicates the line style of a border in a cell range.Style.BottomBorder.Type = BorderType.MediumDashed; // Indicate whether the cell should be auto-sized range.Style.ShrinkToFit = true; // Set alignment of the cell range.Style.VerticalAlignment = VerticalAlignment.Bottom; // Set border color range.Style.DiagonalBorder.SetColor("#20C96F"); // Define border type and border direction as well range.Style.DiagonalBorder.Type = BorderType.Thick; // DiagonalBorderDirection enum stands for direction of diagonal border inside cell range.Style.DiagonalBorderDirection = DiagonalBorderDirection.Forward; // Set background color of cells range.Style.SetBackgroundColor(Color.Aquamarine); // Set fill pattern of the cell // FillPattern enum indicates the style of fill pattern range.Style.FillPattern = FillPattern.Diamonds; // Set the number of spaces to intend the text range.Style.Indention = 5; // Indicate if the text is wrapped range.Style.WrapText = true; workBook.SaveAs("stylingOptions.xls");

使用IRONXL

在 .NET Core 中使用 Excel

已更新 2023年12月19日

.NET Core Excel 概述

在這個現代化時代，我們需要一種更好的方式來在我們的 .NET Core 應用程式中操作 Excel 試算表。在以下的教學中，我們將學習如何在 .NET Core Excel 專案中訪問試算表並使用 C# 修改其值。

第一步

1. 下載 IronXL 庫

如果您需要在 .NET Core 中輕鬆處理 Excel 文件，請嘗試使用 IronXL。下載 IronXL DLL 或使用 NuGet 安裝免費用於開發專案。

Install-Package IronXL.Excel

如何操作教程

2. .NET Core Excel 編輯專案

現在您已經下載了 IronXL，讓我們開始吧。在專案中載入 Excel 檔案並訪問 工作表 需要編輯數據並做出更改的地方。

3. 編輯特定的單元格值

為了編輯 Excel 文件，將引用 IronXL 添加到您的項目中，並通過 using IronXL 導入該庫。

3.1. 載入範例檔案

在以下例子中，我們的 Excel 檔案名稱為 sample.xlsx 且存在專案的 bin> Debug> netcoreapp3.1 資料夾中。我們將使用此程式碼來編輯 sample.xlsx 中單元格 A1 的值為 new value。

/**
Load WorkSheet
anchor-load-a-sample-file
**/
using IronXL;
static void Main(string [] args)
{          
    WorkBook wb = WorkBook.Load("sample.xlsx"); //load Excel file 
    WorkSheet ws = wb.GetWorkSheet("Sheet1"); //Get sheet1 of sample.xlsx
    ws ["A1"].Value = "new value"; //access A1 cell and edit the value
    wb.SaveAs("sample.xlsx");   //save changes        
}

/**
Load WorkSheet
anchor-load-a-sample-file
**/
using IronXL;
static void Main(string [] args)
{          
    WorkBook wb = WorkBook.Load("sample.xlsx"); //load Excel file 
    WorkSheet ws = wb.GetWorkSheet("Sheet1"); //Get sheet1 of sample.xlsx
    ws ["A1"].Value = "new value"; //access A1 cell and edit the value
    wb.SaveAs("sample.xlsx");   //save changes        
}

'''
'''Load WorkSheet
'''anchor-load-a-sample-file
'''*
Imports IronXL
Shared Sub Main(ByVal args() As String)
	Dim wb As WorkBook = WorkBook.Load("sample.xlsx") 'load Excel file
	Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1") 'Get sheet1 of sample.xlsx
	ws ("A1").Value = "new value" 'access A1 cell and edit the value
	wb.SaveAs("sample.xlsx") 'save changes
End Sub

VB C#

4. 批次賦值給多個儲存格

您可以輕鬆地編輯多個儲存格，並同時賦值靜態值，通過使用 (冒號 :). 左側表示特定列的起始單元格，右側表示該列的最後一個單元格。

sheet [從:到]這將編輯單元格A1到A9的欄 A中的new value`。

/**
Assign Value Multi Cells
anchor-assign-value-to-multiple-cells
**/
using IronXL;
static void Main(string [] args)
{
    WorkBook wb = WorkBook.Load("sample.xlsx");
    WorkSheet ws = wb.GetWorkSheet("Sheet1");
    ws ["A1:A9"].Value = "new value";
    wb.SaveAs("sample.xlsx");
}

/**
Assign Value Multi Cells
anchor-assign-value-to-multiple-cells
**/
using IronXL;
static void Main(string [] args)
{
    WorkBook wb = WorkBook.Load("sample.xlsx");
    WorkSheet ws = wb.GetWorkSheet("Sheet1");
    ws ["A1:A9"].Value = "new value";
    wb.SaveAs("sample.xlsx");
}

'''
'''Assign Value Multi Cells
'''anchor-assign-value-to-multiple-cells
'''*
Imports IronXL
Shared Sub Main(ByVal args() As String)
	Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
	Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
	ws ("A1:A9").Value = "new value"
	wb.SaveAs("sample.xlsx")
End Sub

VB C#

5. 使用者輸入編輯儲存格

這裡是一個替代情況，我們可以從使用者那裡獲取值並編輯Excel文件。

/**
Edit Cells User Input
anchor-edit-cells-with-user-inputs
**/
using IronXL;
static void Main(string [] args)
{
    string _from, _to, newValue ;

    Console.Write("Enter Starting Cell :");
    _from = Console.ReadLine();

    Console.Write("Enter Last Cell :");
    _to = Console.ReadLine();

    Console.Write("Enter value:");
    newValue = Console.ReadLine();

    WorkBook wb = WorkBook.Load("sample.xlsx");
    WorkSheet ws = wb.GetWorkSheet("Sheet1");
    ws [_from + ":" + _to].Value = newValue;
    wb.SaveAs("sample.xlsx"); 
    Console.WriteLine("Successfully Changed...!");
    Console.ReadKey();
}

/**
Edit Cells User Input
anchor-edit-cells-with-user-inputs
**/
using IronXL;
static void Main(string [] args)
{
    string _from, _to, newValue ;

    Console.Write("Enter Starting Cell :");
    _from = Console.ReadLine();

    Console.Write("Enter Last Cell :");
    _to = Console.ReadLine();

    Console.Write("Enter value:");
    newValue = Console.ReadLine();

    WorkBook wb = WorkBook.Load("sample.xlsx");
    WorkSheet ws = wb.GetWorkSheet("Sheet1");
    ws [_from + ":" + _to].Value = newValue;
    wb.SaveAs("sample.xlsx"); 
    Console.WriteLine("Successfully Changed...!");
    Console.ReadKey();
}

'''
'''Edit Cells User Input
'''anchor-edit-cells-with-user-inputs
'''*
Imports IronXL
Shared Sub Main(ByVal args() As String)
	Dim _from, _to, newValue As String

	Console.Write("Enter Starting Cell :")
	_from = Console.ReadLine()

	Console.Write("Enter Last Cell :")
	_to = Console.ReadLine()

	Console.Write("Enter value:")
	newValue = Console.ReadLine()

	Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
	Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
	ws (_from & ":" & _to).Value = newValue
	wb.SaveAs("sample.xlsx")
	Console.WriteLine("Successfully Changed...!")
	Console.ReadKey()
End Sub

VB C#

上述代碼將顯示以下輸出並接收用戶輸入：

在 .NET Core 中處理 Excel，圖1：具有使用者輸入的主控台應用程式使用者介面

使用者輸入的控制台應用程式 UI

Excel工作表中 B4 到 B9 的值已更改，我們可以看到：

在 .NET Core 中處理 Excel，圖 2：新值填充在 B4 到 B9

新值填充自 B4 到 B9

6. 編輯多個具有靜態值的單元格

編輯多個單元格並分配動態值非常簡單。讓我們看看以下示例：

/**
Edit Multi Cells Static Value
anchor-edit-multiple-cells-with-static-value
**/
using IronXL;
static void Main(string [] args)
{
    WorkBook wb = WorkBook.Load("sample.xlsx");
    WorkSheet ws = wb.GetWorkSheet("Sheet1");
    for (int i = From; i <= To; i++) //Set cell range of column A to be edit.
    {
        ws ["A" + i].Value = "Value"+i;
    }
    wb.SaveAs("sample.xlsx");  
}

/**
Edit Multi Cells Static Value
anchor-edit-multiple-cells-with-static-value
**/
using IronXL;
static void Main(string [] args)
{
    WorkBook wb = WorkBook.Load("sample.xlsx");
    WorkSheet ws = wb.GetWorkSheet("Sheet1");
    for (int i = From; i <= To; i++) //Set cell range of column A to be edit.
    {
        ws ["A" + i].Value = "Value"+i;
    }
    wb.SaveAs("sample.xlsx");  
}

'''
'''Edit Multi Cells Static Value
'''anchor-edit-multiple-cells-with-static-value
'''*
Imports IronXL
Shared Sub Main(ByVal args() As String)
	Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
	Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
	For i As Integer = From To [To] 'Set cell range of column A to be edit.
		ws ("A" & i).Value = "Value" & i
	Next i
	wb.SaveAs("sample.xlsx")
End Sub

VB C#