如何透過先進流技術在C#中讀取超大型Excel文件
包含數十萬行的電子表格通常會打破通用的方法。 WorkBook.Load建立了一個完整的內存模型,這使得編輯、樣式設置、公式和隨機訪問成為可能,也正因如此,導出400 MB的內容可能需要幾分鐘,並在讀取第一個數值之前消耗幾GB的內存。
IronXL的先進流技術閱讀器WorkBook.StreamRows存在的目的是僅需要讀取數值的工作。 它每次直接從磁碟中傳回一行而非物化整個工作簿,這使得無論表單擁有多少行,內存都能保持平坦。
理解邊界比API本身更重要,因為誤解它是此功能被誤用的主要原因。 StreamRows是只讀、僅限XLSX和僅限前進的:是批量提取數值的快車道。 編輯、樣式設置、公式、圖表、隨機訪問和非XLSX格式仍由WorkBook.Load來處理。
StreamRows並將無法編譯成功。將StreamRows指向一個文件路徑並枚舉結果。 在幕後,字串路徑重載從磁碟懶讀讀取工作簿的部分,並且一次只物化一行,這使得內存能在任意大型表單中保持平坦。
-
1使用 IronXL 安裝 NuGet Package Manager
-
2複製並運行這段程式碼。
foreach (StreamRow row in WorkBook.StreamRows("large-export.xlsx")) Console.WriteLine(row[0]?.Text);For Each row As StreamRow In WorkBook.StreamRows("large-export.xlsx") Console.WriteLine(row(0)?.Text) NextFor Each row As StreamRow In WorkBook.StreamRows("large-export.xlsx") Console.WriteLine(row(0)?.Text) Next -
3部署到您的生產環境進行測試
立即在您的項目中開始使用 IronXL,並使用 免費試用
最小工作流程(5 個步驟)
- 安裝或升級到IronXL 2026.9.2或更高版本並應用有效的授權金鑰
- 如果需要發現目標表單,請呼叫
WorkBook.GetSheetNames(path) - 使用工作表索引、工作表名稱或
StreamReadOptions呼叫WorkBook.StreamRows(path, ...) - 枚舉返回的
IEnumerable<StreamRow>,每次單元格訪問都檢查是否為null - 在讀取
Value之前檢查StreamCell.Type,並批量寫出結果
我應該選擇 StreamRows 還是 WorkBook.Load?
分歧是一個有關工作需要的問題,而不僅僅是文件大小的問題。 需要重新設置儲存格樣式的50 MB文件屬於Load。 需要將三列資料推入數據庫的500 MB文件屬於StreamRows。
在流式閱讀器和完整工作簿模型之間的選擇
| 要求 | StreamRows | WorkBook.Load |
|---|---|---|
| 批量數值提取、導入、ETL、驗證 | 是 | 是(較慢) |
| 編輯或保存 | 無 | 是 |
| 樣式設置、格式化對象 | 無 | 是 |
| 公式表達式和重新計算 | 否(僅緩存結果) | 是 |
| 對任意單元格的隨機訪問 | 否(僅限前進) | 是 |
| XLS、CSV、TSV、加密工作簿 | 無 | 是 |
| 圖表和圖片 | 無 | 是 |
差距大到足以改變工作設計。 流式傳輸23.4 MB工作簿的全部500,000行大約花了六秒,佔用了大約75 MB,首行在四分之一秒內可用; 對同一文件的WorkBook.Load在這兩方面都重得多。 硬體和工作簿形狀會對這些數字產生影響,而具有異常寬行或繁重的共享字串表的工作簿又將表現出不同的結果。 數據加載性能指南涵蓋編寫端的等效權衡。
如何流式傳輸工作簿中的行?
WorkBook.StreamRows 是在 WorkBook 上的靜態方法。 若提供來源和工作表,然後枚舉返回的內容。
輸入
五十萬行的交易數據,八列寬,磁碟上占23.4 MB。 Excel可以無誤地打開它; 麻煩發生在程式嘗試將整個內容讀入內存時。
using IronXL;
using System;
// The string path overload reads the workbook's parts lazily from disk.
// Nothing is loaded up front, and only one row exists in memory at a time.
foreach (StreamRow row in WorkBook.StreamRows("large-export.xlsx"))
{
// Cells holds only the columns that actually have content on this row.
foreach (StreamCell cell in row.Cells)
{
Console.WriteLine("{0}{1} = {2}", cell.ColumnLetter, row.RowNumber + 1, cell.Text);
}
}using IronXL;
using System;
// The string path overload reads the workbook's parts lazily from disk.
// Nothing is loaded up front, and only one row exists in memory at a time.
foreach (StreamRow row in WorkBook.StreamRows("large-export.xlsx"))
{
// Cells holds only the columns that actually have content on this row.
foreach (StreamCell cell in row.Cells)
{
Console.WriteLine("{0}{1} = {2}", cell.ColumnLetter, row.RowNumber + 1, cell.Text);
}
}Imports IronXL
Imports System
' The string path overload reads the workbook's parts lazily from disk.
' Nothing is loaded up front, and only one row exists in memory at a time.
For Each row As StreamRow In WorkBook.StreamRows("large-export.xlsx")
' Cells holds only the columns that actually have content on this row.
For Each cell As StreamCell In row.Cells
Console.WriteLine("{0}{1} = {2}", cell.ColumnLetter, row.RowNumber + 1, cell.Text)
Next
NextImports IronXL
Imports System
' The string path overload reads the workbook's parts lazily from disk.
' Nothing is loaded up front, and only one row exists in memory at a time.
For Each row As StreamRow In WorkBook.StreamRows("large-export.xlsx")
' Cells holds only the columns that actually have content on this row.
For Each cell As StreamCell In row.Cells
Console.WriteLine("{0}{1} = {2}", cell.ColumnLetter, row.RowNumber + 1, cell.Text)
Next
Next輸出
A1 = TxnId
B1 = Date
C1 = Sku
D1 = Description
E1 = Qty
F1 = UnitPrice
G1 = Region
H1 = Status
A2 = 1
B2 = 2026-01-01
C2 = SKU-079109
D2 = Item 0
E2 = 24
F2 = 8.62
G2 = North
H2 = OK
A3 = 2
B3 = 2026-02-02
C3 = SKU-011965
D3 = Item 1
E3 = 43
F3 = 460.94
G3 = South
H3 = PENDING
一行一個單元格,因為循環遍歷row.Cells而不是固定的列集合。 RowNumber是零基,範例中將輸出與Excel自己的編號對齊時會加一。 請注意,這裡的標題作為普通的行到達:沒有提供 StreamReadOptions,TxnId 和 Date 作為第一行的單元格值,而不是命名列。
返回類型是IEnumerable<StreamRow>,重要的區別是直到序列被疊代之前什麼都不讀取。 行會在循環向前推進時一行一行生成,因此對50萬行的foreach 僅在內存中保存一行,而不是50萬行。 結果是序列是單次訪問且僅前進:無法返回到先前的行,也無法對某行進行中間索引。 LINQ運算符可正常地與其合成,這意味著.Take(1000)讀取了一千行並在此處停止。
每個 StreamRow 只包含具有內容的單元格,這在下面的處理 StreamRow中涵蓋。
我應該使用哪個重載?
九個重載涵蓋三個來源和命名工作表的三種方式。 來源是必須要決定的,因為只有string path重載從磁碟上懶讀。 Stream 和 byte[] 重載必須在讀取期間將包的解壓內容保持在記憶體中。
九個 StreamRows 重載,全部返回 IEnumerable<StreamRow>
| 重載 | 低內存 |
|---|---|
StreamRows(string path, int sheetIndex = 0) | 是 |
StreamRows(string path, string sheetName) | 是 |
StreamRows(string path, StreamReadOptions options) | 是 |
StreamRows(Stream stream, int sheetIndex = 0) | 無 |
StreamRows(Stream stream, string sheetName) | 無 |
StreamRows(Stream stream, StreamReadOptions options) | 無 |
StreamRows(byte[] data, int sheetIndex = 0) | 無 |
StreamRows(byte[] data, string sheetName) | 無 |
StreamRows(byte[] data, StreamReadOptions options) | 無 |
Stream 和 byte[] 重載方便於文件已經在記憶體中,例如已緩衝的上傳。 它們仍然一行一行地流式傳輸,避免建立完整的工作簿模型,但是解壓縮的內容在整個讀取過程中保持駐留。 預計其運行的磁碟大小大約是文件大小的十倍,因此23.4 MB的工作簿將佔用略超過200 MB,而不是其文件大小所顯示的數字。 對於最大的文件請使用文件路徑。
三者皆返回相同的行和總數; 代價是內存,而不是速度或正確性
| 來源 | 經過 | 峰值記憶體 |
|---|---|---|
string path | 6.3 秒 | 75 MB |
Stream | 6.2 秒 | 309 MB |
byte[] | 6.3 秒 | 291 MB |
從三者之間實際上經過的時間是相同的,需要注意:選錯重載不會使讀取變慢,而是使其更重。 一個在內存中舒適適配的工作不會感覺到差異,直到文件增大或容器的限制降低。
我該如何挑選工作表?
工作表選擇有三種形式:從零開始的索引、精確的工作表名稱不區分大小寫地匹配或StreamReadOptions實例。 未知的工作表名稱或範圍外的索引會拋出ArgumentException。
using IronXL;
using System;
// 1. By zero-based index. Omitting the argument streams the first sheet.
foreach (StreamRow row in WorkBook.StreamRows("large-export.xlsx", 2))
{
Console.WriteLine(row[0]?.Text);
}
// 2. By sheet name. Matching is case-insensitive, so "transactions" also works.
foreach (StreamRow row in WorkBook.StreamRows("large-export.xlsx", "Transactions"))
{
Console.WriteLine(row[0]?.Text);
}
// 3. By StreamReadOptions, which is the only route to header support.
// SheetName takes precedence over SheetIndex whenever it is set.
StreamReadOptions options = new StreamReadOptions
{
SheetName = "Transactions",
HasHeaderRow = true
};
foreach (StreamRow row in WorkBook.StreamRows("large-export.xlsx", options))
{
Console.WriteLine(row["Reference"]?.Text);
}
// An unknown sheet name or an out-of-range index throws ArgumentException.
try
{
foreach (StreamRow row in WorkBook.StreamRows("large-export.xlsx", "NoSuchSheet"))
{
Console.WriteLine(row.RowNumber);
}
}
catch (ArgumentException ex)
{
Console.WriteLine("Sheet selection failed: {0}", ex.Message);
}using IronXL;
using System;
// 1. By zero-based index. Omitting the argument streams the first sheet.
foreach (StreamRow row in WorkBook.StreamRows("large-export.xlsx", 2))
{
Console.WriteLine(row[0]?.Text);
}
// 2. By sheet name. Matching is case-insensitive, so "transactions" also works.
foreach (StreamRow row in WorkBook.StreamRows("large-export.xlsx", "Transactions"))
{
Console.WriteLine(row[0]?.Text);
}
// 3. By StreamReadOptions, which is the only route to header support.
// SheetName takes precedence over SheetIndex whenever it is set.
StreamReadOptions options = new StreamReadOptions
{
SheetName = "Transactions",
HasHeaderRow = true
};
foreach (StreamRow row in WorkBook.StreamRows("large-export.xlsx", options))
{
Console.WriteLine(row["Reference"]?.Text);
}
// An unknown sheet name or an out-of-range index throws ArgumentException.
try
{
foreach (StreamRow row in WorkBook.StreamRows("large-export.xlsx", "NoSuchSheet"))
{
Console.WriteLine(row.RowNumber);
}
}
catch (ArgumentException ex)
{
Console.WriteLine("Sheet selection failed: {0}", ex.Message);
}Imports IronXL
Imports System
' 1. By zero-based index. Omitting the argument streams the first sheet.
For Each row As StreamRow In WorkBook.StreamRows("large-export.xlsx", 2)
Console.WriteLine(row(0)?.Text)
Next
' 2. By sheet name. Matching is case-insensitive, so "transactions" also works.
For Each row As StreamRow In WorkBook.StreamRows("large-export.xlsx", "Transactions")
Console.WriteLine(row(0)?.Text)
Next
' 3. By StreamReadOptions, which is the only route to header support.
' SheetName takes precedence over SheetIndex whenever it is set.
Dim options As New StreamReadOptions With {
.SheetName = "Transactions",
.HasHeaderRow = True
}
For Each row As StreamRow In WorkBook.StreamRows("large-export.xlsx", options)
Console.WriteLine(row("Reference")?.Text)
Next
' An unknown sheet name or an out-of-range index throws ArgumentException.
Try
For Each row As StreamRow In WorkBook.StreamRows("large-export.xlsx", "NoSuchSheet")
Console.WriteLine(row.RowNumber)
Next
Catch ex As ArgumentException
Console.WriteLine("Sheet selection failed: {0}", ex.Message)
End TryImports IronXL
Imports System
' 1. By zero-based index. Omitting the argument streams the first sheet.
For Each row As StreamRow In WorkBook.StreamRows("large-export.xlsx", 2)
Console.WriteLine(row(0)?.Text)
Next
' 2. By sheet name. Matching is case-insensitive, so "transactions" also works.
For Each row As StreamRow In WorkBook.StreamRows("large-export.xlsx", "Transactions")
Console.WriteLine(row(0)?.Text)
Next
' 3. By StreamReadOptions, which is the only route to header support.
' SheetName takes precedence over SheetIndex whenever it is set.
Dim options As New StreamReadOptions With {
.SheetName = "Transactions",
.HasHeaderRow = True
}
For Each row As StreamRow In WorkBook.StreamRows("large-export.xlsx", options)
Console.WriteLine(row("Reference")?.Text)
Next
' An unknown sheet name or an out-of-range index throws ArgumentException.
Try
For Each row As StreamRow In WorkBook.StreamRows("large-export.xlsx", "NoSuchSheet")
Console.WriteLine(row.RowNumber)
Next
Catch ex As ArgumentException
Console.WriteLine("Sheet selection failed: {0}", ex.Message)
End Try完全不傳遞表格參數會流式傳輸第一個表格,因為 sheetIndex 預設為 0。 StreamReadOptions是唯一支持標題行的途徑,所以只要需要命名列就應該使用它。
在流式傳輸前如何發現工作表?
WorkBook.GetSheetNames列出工作簿中所有的工作表順序,而不載入工作簿的內容。 它僅閱讀工作簿的部分列表,因此即使在非常大的文件上也保持廉價:在23.4 MB、500,000行的工作簿上大約四分之一秒,這與在幾千字節的文件上所需的時間相同。 這使它成為從布局未知的外部系統接收文件時的自然首選。
三個 GetSheetNames 重載,全部返回 IReadOnlyList<string>
| 重載 | 來源 |
|---|---|
GetSheetNames(string path) | 磁碟上的文件 |
GetSheetNames(Stream stream) | 打開的流 |
GetSheetNames(byte[] data) | 內存緩衝 |
返回列表中的位置等於 StreamRows 接受的表格索引,因此這兩個 API 直接對齊:此列表中的索引 2 即是 StreamRows(path, 2) 讀取的表格。
using IronXL;
using System;
using System.Collections.Generic;
// GetSheetNames reads only the workbook's part list, not sheet data, so it is cheap
// even on a very large file. List position equals the sheet index StreamRows accepts.
IReadOnlyList<string> sheetNames = WorkBook.GetSheetNames("large-export.xlsx");
for (int i = 0; i < sheetNames.Count; i++)
{
Console.WriteLine("[{0}] {1}", i, sheetNames[i]);
}
// Stream by exact sheet name. Matching is case-insensitive.
// An unknown name, or an out-of-range index, throws ArgumentException.
foreach (StreamRow row in WorkBook.StreamRows("large-export.xlsx", "Transactions"))
{
StreamCell reference = row[0];
if (reference is not null)
{
Console.WriteLine(reference.Text);
}
}using IronXL;
using System;
using System.Collections.Generic;
// GetSheetNames reads only the workbook's part list, not sheet data, so it is cheap
// even on a very large file. List position equals the sheet index StreamRows accepts.
IReadOnlyList<string> sheetNames = WorkBook.GetSheetNames("large-export.xlsx");
for (int i = 0; i < sheetNames.Count; i++)
{
Console.WriteLine("[{0}] {1}", i, sheetNames[i]);
}
// Stream by exact sheet name. Matching is case-insensitive.
// An unknown name, or an out-of-range index, throws ArgumentException.
foreach (StreamRow row in WorkBook.StreamRows("large-export.xlsx", "Transactions"))
{
StreamCell reference = row[0];
if (reference is not null)
{
Console.WriteLine(reference.Text);
}
}Imports IronXL
Imports System
Imports System.Collections.Generic
' GetSheetNames reads only the workbook's part list, not sheet data, so it is cheap
' even on a very large file. List position equals the sheet index StreamRows accepts.
Dim sheetNames As IReadOnlyList(Of String) = WorkBook.GetSheetNames("large-export.xlsx")
For i As Integer = 0 To sheetNames.Count - 1
Console.WriteLine("[{0}] {1}", i, sheetNames(i))
Next
' Stream by exact sheet name. Matching is case-insensitive.
' An unknown name, or an out-of-range index, throws ArgumentException.
For Each row As StreamRow In WorkBook.StreamRows("large-export.xlsx", "Transactions")
Dim reference As StreamCell = row(0)
If reference IsNot Nothing Then
Console.WriteLine(reference.Text)
End If
NextImports IronXL
Imports System
Imports System.Collections.Generic
' GetSheetNames reads only the workbook's part list, not sheet data, so it is cheap
' even on a very large file. List position equals the sheet index StreamRows accepts.
Dim sheetNames As IReadOnlyList(Of String) = WorkBook.GetSheetNames("large-export.xlsx")
For i As Integer = 0 To sheetNames.Count - 1
Console.WriteLine("[{0}] {1}", i, sheetNames(i))
Next
' Stream by exact sheet name. Matching is case-insensitive.
' An unknown name, or an out-of-range index, throws ArgumentException.
For Each row As StreamRow In WorkBook.StreamRows("large-export.xlsx", "Transactions")
Dim reference As StreamCell = row(0)
If reference IsNot Nothing Then
Console.WriteLine(reference.Text)
End If
Next如何按列名讀取行?
設置HasHeaderRow命名列並允許按標題名稱讀取行。 標頭行被用於命名而不作為數據輸出,且所有在HeaderRowIndex之前的行將被跳過,這處理了通常位於真正標頭之上的標題或前言的常見情況。
using IronXL;
using System;
// This source file carries a title and a blank spacer above the real header,
// so the header sits on Excel row 3, which is HeaderRowIndex 2 (zero-based).
StreamReadOptions options = new StreamReadOptions
{
SheetName = "Transactions",
HasHeaderRow = true,
HeaderRowIndex = 2
};
foreach (StreamRow row in WorkBook.StreamRows("large-export.xlsx", options))
{
// The header row is consumed to name columns and is never yielded as data.
// Rows before HeaderRowIndex are skipped.
// Named access requires HasHeaderRow; without it the string indexer throws
// InvalidOperationException.
StreamCell reference = row["Reference"];
StreamCell amount = row["Amount"];
// Both the sparse-row case and a blank cell produce null, so null-check before use.
if (reference is null || amount is null)
{
continue;
}
Console.WriteLine("{0}: {1}", reference.Text, amount.Text);
}using IronXL;
using System;
// This source file carries a title and a blank spacer above the real header,
// so the header sits on Excel row 3, which is HeaderRowIndex 2 (zero-based).
StreamReadOptions options = new StreamReadOptions
{
SheetName = "Transactions",
HasHeaderRow = true,
HeaderRowIndex = 2
};
foreach (StreamRow row in WorkBook.StreamRows("large-export.xlsx", options))
{
// The header row is consumed to name columns and is never yielded as data.
// Rows before HeaderRowIndex are skipped.
// Named access requires HasHeaderRow; without it the string indexer throws
// InvalidOperationException.
StreamCell reference = row["Reference"];
StreamCell amount = row["Amount"];
// Both the sparse-row case and a blank cell produce null, so null-check before use.
if (reference is null || amount is null)
{
continue;
}
Console.WriteLine("{0}: {1}", reference.Text, amount.Text);
}Imports IronXL
Imports System
' This source file carries a title and a blank spacer above the real header,
' so the header sits on Excel row 3, which is HeaderRowIndex 2 (zero-based).
Dim options As New StreamReadOptions With {
.SheetName = "Transactions",
.HasHeaderRow = True,
.HeaderRowIndex = 2
}
For Each row As StreamRow In WorkBook.StreamRows("large-export.xlsx", options)
' The header row is consumed to name columns and is never yielded as data.
' Rows before HeaderRowIndex are skipped.
' Named access requires HasHeaderRow; without it the string indexer throws
' InvalidOperationException.
Dim reference As StreamCell = row("Reference")
Dim amount As StreamCell = row("Amount")
' Both the sparse-row case and a blank cell produce null, so null-check before use.
If reference Is Nothing OrElse amount Is Nothing Then
Continue For
End If
Console.WriteLine("{0}: {1}", reference.Text, amount.Text)
NextImports IronXL
Imports System
' This source file carries a title and a blank spacer above the real header,
' so the header sits on Excel row 3, which is HeaderRowIndex 2 (zero-based).
Dim options As New StreamReadOptions With {
.SheetName = "Transactions",
.HasHeaderRow = True,
.HeaderRowIndex = 2
}
For Each row As StreamRow In WorkBook.StreamRows("large-export.xlsx", options)
' The header row is consumed to name columns and is never yielded as data.
' Rows before HeaderRowIndex are skipped.
' Named access requires HasHeaderRow; without it the string indexer throws
' InvalidOperationException.
Dim reference As StreamCell = row("Reference")
Dim amount As StreamCell = row("Amount")
' Both the sparse-row case and a blank cell produce null, so null-check before use.
If reference Is Nothing OrElse amount Is Nothing Then
Continue For
End If
Console.WriteLine("{0}: {1}", reference.Text, amount.Text)
NextHasHeaderRow。 沒有它,字符串索引器和 TryGetCell 都會引發 InvalidOperationException。標頭處理有意嚴格。 如果表格在配置的 HeaderRowIndex 時或之前沒有行,枚舉會引發 InvalidOperationException 並顯示“未找到標題行”消息,並且當數據行超過標題索引時即會引發。 這確保消費者使用 Take 或早期的 break 時,立即看到錯誤配置,而不是靜默地讀取不到任何東西。
列名匹配不區分大小寫並修剪周圍的空白。 重複的標頭名稱解決為第一個,最左側的匹配列。
StreamReadOptions設置了什麼?
StreamReadOptions屬性,通過對象初始化器設置
| 屬性 | 類型 | Default | 行為 |
|---|---|---|---|
SheetIndex | int | 0 | 零基工作表索引。 當設置SheetName時忽略 |
SheetName | string | null | 工作表名稱,不區分大小寫地匹配。 取代SheetIndex |
HasHeaderRow | bool | false | 當為真時,HeaderRowIndex上的行命名列且不作為數據輸出 |
HeaderRowIndex | int | 0 | 控制標頭的零基行數。 在此之前的行將被跳過 |
我如何處理選擇性列?
兩種命名訪問器在處理不在標頭中的列時不同,選擇錯誤的將會將一個可以容忍的源文件變成崩潰。 字符串索引器拋出ArgumentException; TryGetCell 返回 false。
using IronXL;
using System;
StreamReadOptions options = new StreamReadOptions
{
SheetIndex = 0,
HasHeaderRow = true
};
foreach (StreamRow row in WorkBook.StreamRows("large-export.xlsx", options))
{
// The string indexer throws ArgumentException when the column name is absent
// from the header. TryGetCell returns false instead, which makes it the correct
// choice for a column that may not exist in every source file.
if (row.TryGetCell("CostCentre", out StreamCell costCentre) && costCentre is not null)
{
Console.WriteLine("Cost centre: {0}", costCentre.Text);
}
else
{
Console.WriteLine("Cost centre: not supplied");
}
// Header is positioned by column, so Header[i] lines up with column i.
// A blank header column is null.
for (int i = 0; i < row.Header.Count; i++)
{
string columnName = row.Header[i];
if (string.IsNullOrEmpty(columnName))
{
continue;
}
StreamCell cell = row[i];
Console.WriteLine(" {0} ({1}) = {2}", columnName, cell?.ColumnLetter, cell?.Text);
}
}using IronXL;
using System;
StreamReadOptions options = new StreamReadOptions
{
SheetIndex = 0,
HasHeaderRow = true
};
foreach (StreamRow row in WorkBook.StreamRows("large-export.xlsx", options))
{
// The string indexer throws ArgumentException when the column name is absent
// from the header. TryGetCell returns false instead, which makes it the correct
// choice for a column that may not exist in every source file.
if (row.TryGetCell("CostCentre", out StreamCell costCentre) && costCentre is not null)
{
Console.WriteLine("Cost centre: {0}", costCentre.Text);
}
else
{
Console.WriteLine("Cost centre: not supplied");
}
// Header is positioned by column, so Header[i] lines up with column i.
// A blank header column is null.
for (int i = 0; i < row.Header.Count; i++)
{
string columnName = row.Header[i];
if (string.IsNullOrEmpty(columnName))
{
continue;
}
StreamCell cell = row[i];
Console.WriteLine(" {0} ({1}) = {2}", columnName, cell?.ColumnLetter, cell?.Text);
}
}Imports IronXL
Imports System
Dim options As New StreamReadOptions With {
.SheetIndex = 0,
.HasHeaderRow = True
}
For Each row As StreamRow In WorkBook.StreamRows("large-export.xlsx", options)
' The string indexer throws ArgumentException when the column name is absent
' from the header. TryGetCell returns false instead, which makes it the correct
' choice for a column that may not exist in every source file.
Dim costCentre As StreamCell = Nothing
If row.TryGetCell("CostCentre", costCentre) And costCentre IsNot Nothing Then
Console.WriteLine("Cost centre: {0}", costCentre.Text)
Else
Console.WriteLine("Cost centre: not supplied")
End If
' Header is positioned by column, so Header[i] lines up with column i.
' A blank header column is null.
For i As Integer = 0 To row.Header.Count - 1
Dim columnName As String = row.Header(i)
If String.IsNullOrEmpty(columnName) Then
Continue For
End If
Dim cell As StreamCell = row(i)
Console.WriteLine(" {0} ({1}) = {2}", columnName, cell?.ColumnLetter, cell?.Text)
Next
NextImports IronXL
Imports System
Dim options As New StreamReadOptions With {
.SheetIndex = 0,
.HasHeaderRow = True
}
For Each row As StreamRow In WorkBook.StreamRows("large-export.xlsx", options)
' The string indexer throws ArgumentException when the column name is absent
' from the header. TryGetCell returns false instead, which makes it the correct
' choice for a column that may not exist in every source file.
Dim costCentre As StreamCell = Nothing
If row.TryGetCell("CostCentre", costCentre) And costCentre IsNot Nothing Then
Console.WriteLine("Cost centre: {0}", costCentre.Text)
Else
Console.WriteLine("Cost centre: not supplied")
End If
' Header is positioned by column, so Header[i] lines up with column i.
' A blank header column is null.
For i As Integer = 0 To row.Header.Count - 1
Dim columnName As String = row.Header(i)
If String.IsNullOrEmpty(columnName) Then
Continue For
End If
Dim cell As StreamCell = row(i)
Console.WriteLine(" {0} ({1}) = {2}", columnName, cell?.ColumnLetter, cell?.Text)
Next
NextTryGetCell解答的問題比表面看上去要窄:報告標頭是否包含列名,而不是這特定行是否有一個填充值。 已知列中有稀疏間隙的行返回帶有 null 單元格的 true,因此仍然需要在輸出時進行空值檢查。 對於來自某些源文件中可能缺少的任何列,使用TryGetCell,而在保證存在列的地方僅使用字符串索引器。
我如何與StreamRow互動?
StreamRow是枚舉期間生成的一行。 它公開其位置,實際包含的單元格,以及位置和命名查找。
using IronXL;
using System;
StreamReadOptions options = new StreamReadOptions
{
SheetName = "Transactions",
HasHeaderRow = true
};
foreach (StreamRow row in WorkBook.StreamRows("large-export.xlsx", options))
{
// RowNumber is zero-based, so Excel row 1 is RowNumber 0.
// With a header consumed, the first data row keeps its real sheet position.
Console.WriteLine("Sheet row {0}, {1} populated cells", row.RowNumber, row.Cells.Count);
// Index access. Rows are sparse, so a column with no content returns null
// rather than a blank cell object.
StreamCell firstColumn = row[0];
Console.WriteLine(" column A: {0}", firstColumn?.Text ?? "(empty)");
// Name access. Requires HasHeaderRow, and throws ArgumentException if the
// header has no such column. Matching is case-insensitive and trims whitespace.
StreamCell reference = row["Reference"];
Console.WriteLine(" Reference: {0}", reference?.Text ?? "(empty)");
// Header is positioned by column: Header[i] names column i.
// A column with a blank header cell is null.
for (int i = 0; i < row.Header.Count; i++)
{
string name = row.Header[i];
Console.WriteLine(" column {0} is named {1}", i, name ?? "(unnamed)");
}
}using IronXL;
using System;
StreamReadOptions options = new StreamReadOptions
{
SheetName = "Transactions",
HasHeaderRow = true
};
foreach (StreamRow row in WorkBook.StreamRows("large-export.xlsx", options))
{
// RowNumber is zero-based, so Excel row 1 is RowNumber 0.
// With a header consumed, the first data row keeps its real sheet position.
Console.WriteLine("Sheet row {0}, {1} populated cells", row.RowNumber, row.Cells.Count);
// Index access. Rows are sparse, so a column with no content returns null
// rather than a blank cell object.
StreamCell firstColumn = row[0];
Console.WriteLine(" column A: {0}", firstColumn?.Text ?? "(empty)");
// Name access. Requires HasHeaderRow, and throws ArgumentException if the
// header has no such column. Matching is case-insensitive and trims whitespace.
StreamCell reference = row["Reference"];
Console.WriteLine(" Reference: {0}", reference?.Text ?? "(empty)");
// Header is positioned by column: Header[i] names column i.
// A column with a blank header cell is null.
for (int i = 0; i < row.Header.Count; i++)
{
string name = row.Header[i];
Console.WriteLine(" column {0} is named {1}", i, name ?? "(unnamed)");
}
}Imports IronXL
Imports System
Dim options As New StreamReadOptions With {
.SheetName = "Transactions",
.HasHeaderRow = True
}
For Each row As StreamRow In WorkBook.StreamRows("large-export.xlsx", options)
' RowNumber is zero-based, so Excel row 1 is RowNumber 0.
' With a header consumed, the first data row keeps its real sheet position.
Console.WriteLine("Sheet row {0}, {1} populated cells", row.RowNumber, row.Cells.Count)
' Index access. Rows are sparse, so a column with no content returns null
' rather than a blank cell object.
Dim firstColumn As StreamCell = row(0)
Console.WriteLine(" column A: {0}", If(firstColumn?.Text, "(empty)"))
' Name access. Requires HasHeaderRow, and throws ArgumentException if the
' header has no such column. Matching is case-insensitive and trims whitespace.
Dim reference As StreamCell = row("Reference")
Console.WriteLine(" Reference: {0}", If(reference?.Text, "(empty)"))
' Header is positioned by column: Header[i] names column i.
' A column with a blank header cell is null.
For i As Integer = 0 To row.Header.Count - 1
Dim name As String = row.Header(i)
Console.WriteLine(" column {0} is named {1}", i, If(name, "(unnamed)"))
Next
NextImports IronXL
Imports System
Dim options As New StreamReadOptions With {
.SheetName = "Transactions",
.HasHeaderRow = True
}
For Each row As StreamRow In WorkBook.StreamRows("large-export.xlsx", options)
' RowNumber is zero-based, so Excel row 1 is RowNumber 0.
' With a header consumed, the first data row keeps its real sheet position.
Console.WriteLine("Sheet row {0}, {1} populated cells", row.RowNumber, row.Cells.Count)
' Index access. Rows are sparse, so a column with no content returns null
' rather than a blank cell object.
Dim firstColumn As StreamCell = row(0)
Console.WriteLine(" column A: {0}", If(firstColumn?.Text, "(empty)"))
' Name access. Requires HasHeaderRow, and throws ArgumentException if the
' header has no such column. Matching is case-insensitive and trims whitespace.
Dim reference As StreamCell = row("Reference")
Console.WriteLine(" Reference: {0}", If(reference?.Text, "(empty)"))
' Header is positioned by column: Header[i] names column i.
' A column with a blank header cell is null.
For i As Integer = 0 To row.Header.Count - 1
Dim name As String = row.Header(i)
Console.WriteLine(" column {0} is named {1}", i, If(name, "(unnamed)"))
Next
NextStreamRow成員,具有位置和命名訪問
| 成員 | 類型 | 行為 |
|---|---|---|
RowNumber | int | 零基。 Excel的第1行是在RowNumber 0中 |
Cells | IReadOnlyList<StreamCell> | 稀疏。 僅包含內容的列是存在的 |
Header | IReadOnlyList<string> | 按列定位的列名,因此 Header[i] 名稱為列 i。 空白標題列是 null,如果未配置標題 Header 自己是 null |
this[int columnIndex] | StreamCell | 零基列索引處的單元格,或null用於稀疏空隙 |
this[string columnName] | StreamCell | 在命名標題列下的單元格。 當名稱不在時拋出ArgumentException |
TryGetCell(string, out StreamCell) | bool | 當標題包含名稱時 true,當不包含時 false |
Header 是 null 除非 StreamReadOptions.HasHeaderRow 設置了。 在迭代之前保護它,否則迴圈將解引用空列表。稀疏性是塑造調用代碼的屬性。 一行不是固定寬度的記錄:具有A到F列的表單生成的行,其中三個列填充了三個單元格,位置索引器為所缺空表示null。 RowNumber為零基與IronXL的其他部分一致,其中數字索引從零開始,而A1樣式的字串地址則不然。
如何從StreamCell讀取型別值?
StreamCell是一個單元格的不變快照。 與正常的Cell不同,它不支持工作簿,無法用於回寫值。
using IronXL;
using System;
foreach (StreamRow row in WorkBook.StreamRows("large-export.xlsx", "Transactions"))
{
StreamCell cell = row[1];
// Sparse gap: no cell at this column on this row.
if (cell is null)
{
continue;
}
// Position: zero-based index, plus the spreadsheet column letter.
Console.WriteLine("Column {0} (letter {1})", cell.ColumnIndex, cell.ColumnLetter);
// Type classifies the value. For a formula cell it reports the type of the
// cached result, never a "formula" type.
Console.WriteLine(" Type: {0}", cell.Type);
// IsFormula is how formula-ness is exposed. The expression itself is not
// available from the streaming reader, only the cached result.
Console.WriteLine(" IsFormula: {0}", cell.IsFormula);
// Value is the raw typed value, and is null for a blank cell.
Console.WriteLine(" Value: {0}", cell.Value ?? "(null)");
// Text is the formatted display text, which is what Excel shows in the cell.
Console.WriteLine(" Text: {0}", cell.Text);
}using IronXL;
using System;
foreach (StreamRow row in WorkBook.StreamRows("large-export.xlsx", "Transactions"))
{
StreamCell cell = row[1];
// Sparse gap: no cell at this column on this row.
if (cell is null)
{
continue;
}
// Position: zero-based index, plus the spreadsheet column letter.
Console.WriteLine("Column {0} (letter {1})", cell.ColumnIndex, cell.ColumnLetter);
// Type classifies the value. For a formula cell it reports the type of the
// cached result, never a "formula" type.
Console.WriteLine(" Type: {0}", cell.Type);
// IsFormula is how formula-ness is exposed. The expression itself is not
// available from the streaming reader, only the cached result.
Console.WriteLine(" IsFormula: {0}", cell.IsFormula);
// Value is the raw typed value, and is null for a blank cell.
Console.WriteLine(" Value: {0}", cell.Value ?? "(null)");
// Text is the formatted display text, which is what Excel shows in the cell.
Console.WriteLine(" Text: {0}", cell.Text);
}Imports IronXL
Imports System
For Each row As StreamRow In WorkBook.StreamRows("large-export.xlsx", "Transactions")
Dim cell As StreamCell = row(1)
' Sparse gap: no cell at this column on this row.
If cell Is Nothing Then
Continue For
End If
' Position: zero-based index, plus the spreadsheet column letter.
Console.WriteLine("Column {0} (letter {1})", cell.ColumnIndex, cell.ColumnLetter)
' Type classifies the value. For a formula cell it reports the type of the
' cached result, never a "formula" type.
Console.WriteLine(" Type: {0}", cell.Type)
' IsFormula is how formula-ness is exposed. The expression itself is not
' available from the streaming reader, only the cached result.
Console.WriteLine(" IsFormula: {0}", cell.IsFormula)
' Value is the raw typed value, and is null for a blank cell.
Console.WriteLine(" Value: {0}", If(cell.Value, "(null)"))
' Text is the formatted display text, which is what Excel shows in the cell.
Console.WriteLine(" Text: {0}", cell.Text)
NextImports IronXL
Imports System
For Each row As StreamRow In WorkBook.StreamRows("large-export.xlsx", "Transactions")
Dim cell As StreamCell = row(1)
' Sparse gap: no cell at this column on this row.
If cell Is Nothing Then
Continue For
End If
' Position: zero-based index, plus the spreadsheet column letter.
Console.WriteLine("Column {0} (letter {1})", cell.ColumnIndex, cell.ColumnLetter)
' Type classifies the value. For a formula cell it reports the type of the
' cached result, never a "formula" type.
Console.WriteLine(" Type: {0}", cell.Type)
' IsFormula is how formula-ness is exposed. The expression itself is not
' available from the streaming reader, only the cached result.
Console.WriteLine(" IsFormula: {0}", cell.IsFormula)
' Value is the raw typed value, and is null for a blank cell.
Console.WriteLine(" Value: {0}", If(cell.Value, "(null)"))
' Text is the formatted display text, which is what Excel shows in the cell.
Console.WriteLine(" Text: {0}", cell.Text)
NextStreamCell成員,涵蓋位置、類型和值
| 成員 | 類型 | 行為 |
|---|---|---|
ColumnIndex | int | 零基列索引 |
ColumnLetter | string | 試算表列字母,例如“ A ”, “ C ”或“ AA ” |
Type | StreamCellType | 保存的值類型。 對於公式單元格,則是緩存結果的類型 |
Value | object | 原始類型值。 用於空白單元格的null |
Text | string | 格式化的顯示文本,Excel如是顯示 |
IsFormula | bool | true當單元格包含公式時 |
Value 被封箱,因此您轉換它的結果來自於 Type:
Number給出doubleString給出stringBoolean給出boolDate給出DateTimeBlank給出null
在接觸 Value 之前檢查 Type 是區分能夠處理實際數據的導入與會在第40,000行拋出異常的導入之間的關鍵。
using IronXL;
using System;
using System.Globalization;
StreamReadOptions options = new StreamReadOptions
{
SheetName = "Transactions",
HasHeaderRow = true
};
foreach (StreamRow row in WorkBook.StreamRows("large-export.xlsx", options))
{
StreamCell amount = row["Amount"];
// Sparse rows and blank cells both yield null. Check before touching Type or Value.
if (amount is null || amount.Type == StreamCellType.Blank)
{
continue;
}
// A formula cell reports IsFormula true, and Type reports the real type of the
// cached result. The formula expression itself is not exposed, and nothing is
// recalculated, which is why StreamCellType has no Formula member.
if (amount.IsFormula)
{
Console.WriteLine("Row {0} column {1} is a cached formula result.",
row.RowNumber, amount.ColumnLetter);
}
switch (amount.Type)
{
case StreamCellType.Number:
// Convert rather than casting directly: the boxed numeric type is not
// guaranteed to be the one you expect.
decimal value = Convert.ToDecimal(amount.Value, CultureInfo.InvariantCulture);
Console.WriteLine("Amount: {0}", value);
break;
case StreamCellType.Date:
// Date cells honour the workbook's date system, including 1904-based
// workbooks produced by Excel for Mac.
if (amount.Value is DateTime date)
{
Console.WriteLine("Date: {0:yyyy-MM-dd}", date);
}
break;
case StreamCellType.Boolean:
Console.WriteLine("Flag: {0}", Convert.ToBoolean(amount.Value));
break;
case StreamCellType.Error:
Console.WriteLine("Row {0} contains an error value.", row.RowNumber);
break;
default:
// Falls through to the formatted display text for String and anything else.
Console.WriteLine("Text: {0}", amount.Text);
break;
}
}using IronXL;
using System;
using System.Globalization;
StreamReadOptions options = new StreamReadOptions
{
SheetName = "Transactions",
HasHeaderRow = true
};
foreach (StreamRow row in WorkBook.StreamRows("large-export.xlsx", options))
{
StreamCell amount = row["Amount"];
// Sparse rows and blank cells both yield null. Check before touching Type or Value.
if (amount is null || amount.Type == StreamCellType.Blank)
{
continue;
}
// A formula cell reports IsFormula true, and Type reports the real type of the
// cached result. The formula expression itself is not exposed, and nothing is
// recalculated, which is why StreamCellType has no Formula member.
if (amount.IsFormula)
{
Console.WriteLine("Row {0} column {1} is a cached formula result.",
row.RowNumber, amount.ColumnLetter);
}
switch (amount.Type)
{
case StreamCellType.Number:
// Convert rather than casting directly: the boxed numeric type is not
// guaranteed to be the one you expect.
decimal value = Convert.ToDecimal(amount.Value, CultureInfo.InvariantCulture);
Console.WriteLine("Amount: {0}", value);
break;
case StreamCellType.Date:
// Date cells honour the workbook's date system, including 1904-based
// workbooks produced by Excel for Mac.
if (amount.Value is DateTime date)
{
Console.WriteLine("Date: {0:yyyy-MM-dd}", date);
}
break;
case StreamCellType.Boolean:
Console.WriteLine("Flag: {0}", Convert.ToBoolean(amount.Value));
break;
case StreamCellType.Error:
Console.WriteLine("Row {0} contains an error value.", row.RowNumber);
break;
default:
// Falls through to the formatted display text for String and anything else.
Console.WriteLine("Text: {0}", amount.Text);
break;
}
}Imports IronXL
Imports System
Imports System.Globalization
Dim options As New StreamReadOptions With {
.SheetName = "Transactions",
.HasHeaderRow = True
}
For Each row As StreamRow In WorkBook.StreamRows("large-export.xlsx", options)
Dim amount As StreamCell = row("Amount")
' Sparse rows and blank cells both yield null. Check before touching Type or Value.
If amount Is Nothing OrElse amount.Type = StreamCellType.Blank Then
Continue For
End If
' A formula cell reports IsFormula true, and Type reports the real type of the
' cached result. The formula expression itself is not exposed, and nothing is
' recalculated, which is why StreamCellType has no Formula member.
If amount.IsFormula Then
Console.WriteLine("Row {0} column {1} is a cached formula result.",
row.RowNumber, amount.ColumnLetter)
End If
Select Case amount.Type
Case StreamCellType.Number
' Convert rather than casting directly: the boxed numeric type is not
' guaranteed to be the one you expect.
Dim value As Decimal = Convert.ToDecimal(amount.Value, CultureInfo.InvariantCulture)
Console.WriteLine("Amount: {0}", value)
Case StreamCellType.Date
' Date cells honour the workbook's date system, including 1904-based
' workbooks produced by Excel for Mac.
If TypeOf amount.Value Is DateTime Then
Dim [date] As DateTime = DirectCast(amount.Value, DateTime)
Console.WriteLine("Date: {0:yyyy-MM-dd}", [date])
End If
Case StreamCellType.Boolean
Console.WriteLine("Flag: {0}", Convert.ToBoolean(amount.Value))
Case StreamCellType.Error
Console.WriteLine("Row {0} contains an error value.", row.RowNumber)
Case Else
' Falls through to the formatted display text for String and anything else.
Console.WriteLine("Text: {0}", amount.Text)
End Select
NextImports IronXL
Imports System
Imports System.Globalization
Dim options As New StreamReadOptions With {
.SheetName = "Transactions",
.HasHeaderRow = True
}
For Each row As StreamRow In WorkBook.StreamRows("large-export.xlsx", options)
Dim amount As StreamCell = row("Amount")
' Sparse rows and blank cells both yield null. Check before touching Type or Value.
If amount Is Nothing OrElse amount.Type = StreamCellType.Blank Then
Continue For
End If
' A formula cell reports IsFormula true, and Type reports the real type of the
' cached result. The formula expression itself is not exposed, and nothing is
' recalculated, which is why StreamCellType has no Formula member.
If amount.IsFormula Then
Console.WriteLine("Row {0} column {1} is a cached formula result.",
row.RowNumber, amount.ColumnLetter)
End If
Select Case amount.Type
Case StreamCellType.Number
' Convert rather than casting directly: the boxed numeric type is not
' guaranteed to be the one you expect.
Dim value As Decimal = Convert.ToDecimal(amount.Value, CultureInfo.InvariantCulture)
Console.WriteLine("Amount: {0}", value)
Case StreamCellType.Date
' Date cells honour the workbook's date system, including 1904-based
' workbooks produced by Excel for Mac.
If TypeOf amount.Value Is DateTime Then
Dim [date] As DateTime = DirectCast(amount.Value, DateTime)
Console.WriteLine("Date: {0:yyyy-MM-dd}", [date])
End If
Case StreamCellType.Boolean
Console.WriteLine("Flag: {0}", Convert.ToBoolean(amount.Value))
Case StreamCellType.Error
Console.WriteLine("Row {0} contains an error value.", row.RowNumber)
Case Else
' Falls through to the formatted display text for String and anything else.
Console.WriteLine("Text: {0}", amount.Text)
End Select
Next這裡有兩個習慣很重要。 第一項是對每個索引器訪問進行空值檢查,因為行是稀疏的,而 Value 對於空白單元格來說是 null,因此兩種情況都匯聚在同一檢查上。 其次是轉換而不是直觀投擲:Value保存原始類型值,從中直接投擲的代碼教導了一個只在某一列恰好以不同方式存儲的文件中才顯露的錯誤。 當目的地是一個字符串列時,閱讀Text而非這樣做完全合理。
StreamCellType報告了什麼?
由 StreamCell.Type 報告的 StreamCellType 值
| 價值 | 當報告 |
|---|---|
Blank | 單元格不包含值時。 Value 是 null |
Number | 單元格保留數值 |
String | 單元格保留文本 |
Boolean | 單元格保存 TRUE 或 FALSE |
Date | 單元格保留日期,遵循工作簿的日期系統 |
Error | 單元格保留Excel錯誤值 |
故意不設置Formula成員。 公式單元格報告其緩存結果的真實類型,例如 Number,公式特性則通過 IsFormula 單獨暴露。 任何在enum中掃描公式情況的人不會找到這樣的情況,這是出於設計而非遺漏。
流式讀取器的限制是什麼?
事先了解這些限制可以防止最常見的誤診。
僅有路徑重載是真正低內存的。 這是頁面上最重要的警告。 開發者錯誤地將400 MB的上傳作為byte[]通過,觀察到內存飆升,並得出的結論是功能損壞的情況正是因此。
其餘邊界是由設計所確定:
- 僅限只讀:流式讀取器無法修改或保存。 編輯意味著
WorkBook.Load。 - 僅限XLSX:沒有
.xls,沒有加密的工作簿,也沒有任何形式的寫入。 - 單次訪問且僅限前進:無隨機存取且無倒帶。 需要在表單中跳躍的程式需要
Load。 - 無樣式或格式化對象:未暴露單元格外觀。
- 僅緩存公式結果:公式表達式未暴露且不會重新計算。
StreamRows 和 GetSheetNames 都需要有效的 IronXL 授權,這在調用方法時強制執行,與 WorkBook.Load 完全一樣。一個進一步的警告位於限制造成的行為之間:自路徑上的低內存讀取是最好的努力。 zip中心目錄無法讀取的工作簿可能會回落到將包讀入內存。
如何建立流式導入管道?
支持生產工作的模式是提供批量寫入的流式讀取,因此源和目的地都沒有保持在完整大小的內存中。
using IronXL;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Threading.Tasks;
public class TransactionImporter
{
private const int BatchSize = 5000;
public async Task<int> ImportAsync(string path, ITransactionSink sink)
{
StreamReadOptions options = new StreamReadOptions
{
SheetName = "Transactions",
HasHeaderRow = true,
HeaderRowIndex = 0
};
List<TransactionRecord> batch = new List<TransactionRecord>(BatchSize);
int imported = 0;
// Enumeration is lazy: only one row is materialised at a time, so peak memory
// stays flat regardless of how many rows the sheet holds.
foreach (StreamRow row in WorkBook.StreamRows(path, options))
{
TransactionRecord record = MapRow(row);
if (record is null)
{
continue;
}
batch.Add(record);
if (batch.Count == BatchSize)
{
await sink.WriteAsync(batch);
imported += batch.Count;
batch.Clear();
}
}
if (batch.Count > 0)
{
await sink.WriteAsync(batch);
imported += batch.Count;
}
return imported;
}
private static TransactionRecord MapRow(StreamRow row)
{
StreamCell reference = row["Reference"];
StreamCell amount = row["Amount"];
StreamCell posted = row["PostedOn"];
// Required columns missing on this row: skip rather than throw, so one bad
// row does not abort an import of several hundred thousand.
if (reference is null || amount is null)
{
return null;
}
if (amount.Type != StreamCellType.Number)
{
return null;
}
DateTime? postedOn = posted?.Value as DateTime?;
// Optional column that only some source files carry.
string costCentre = row.TryGetCell("CostCentre", out StreamCell centre)
? centre?.Text
: null;
return new TransactionRecord
{
SourceRow = row.RowNumber,
Reference = reference.Text,
Amount = Convert.ToDecimal(amount.Value, CultureInfo.InvariantCulture),
PostedOn = postedOn,
CostCentre = costCentre
};
}
}
public class TransactionRecord
{
public int SourceRow { get; set; }
public string Reference { get; set; }
public decimal Amount { get; set; }
public DateTime? PostedOn { get; set; }
public string CostCentre { get; set; }
}
public interface ITransactionSink
{
Task WriteAsync(IReadOnlyList<TransactionRecord> batch);
}using IronXL;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Threading.Tasks;
public class TransactionImporter
{
private const int BatchSize = 5000;
public async Task<int> ImportAsync(string path, ITransactionSink sink)
{
StreamReadOptions options = new StreamReadOptions
{
SheetName = "Transactions",
HasHeaderRow = true,
HeaderRowIndex = 0
};
List<TransactionRecord> batch = new List<TransactionRecord>(BatchSize);
int imported = 0;
// Enumeration is lazy: only one row is materialised at a time, so peak memory
// stays flat regardless of how many rows the sheet holds.
foreach (StreamRow row in WorkBook.StreamRows(path, options))
{
TransactionRecord record = MapRow(row);
if (record is null)
{
continue;
}
batch.Add(record);
if (batch.Count == BatchSize)
{
await sink.WriteAsync(batch);
imported += batch.Count;
batch.Clear();
}
}
if (batch.Count > 0)
{
await sink.WriteAsync(batch);
imported += batch.Count;
}
return imported;
}
private static TransactionRecord MapRow(StreamRow row)
{
StreamCell reference = row["Reference"];
StreamCell amount = row["Amount"];
StreamCell posted = row["PostedOn"];
// Required columns missing on this row: skip rather than throw, so one bad
// row does not abort an import of several hundred thousand.
if (reference is null || amount is null)
{
return null;
}
if (amount.Type != StreamCellType.Number)
{
return null;
}
DateTime? postedOn = posted?.Value as DateTime?;
// Optional column that only some source files carry.
string costCentre = row.TryGetCell("CostCentre", out StreamCell centre)
? centre?.Text
: null;
return new TransactionRecord
{
SourceRow = row.RowNumber,
Reference = reference.Text,
Amount = Convert.ToDecimal(amount.Value, CultureInfo.InvariantCulture),
PostedOn = postedOn,
CostCentre = costCentre
};
}
}
public class TransactionRecord
{
public int SourceRow { get; set; }
public string Reference { get; set; }
public decimal Amount { get; set; }
public DateTime? PostedOn { get; set; }
public string CostCentre { get; set; }
}
public interface ITransactionSink
{
Task WriteAsync(IReadOnlyList<TransactionRecord> batch);
}Imports IronXL
Imports System
Imports System.Collections.Generic
Imports System.Globalization
Imports System.Threading.Tasks
Public Class TransactionImporter
Private Const BatchSize As Integer = 5000
Public Async Function ImportAsync(path As String, sink As ITransactionSink) As Task(Of Integer)
Dim options As New StreamReadOptions With {
.SheetName = "Transactions",
.HasHeaderRow = True,
.HeaderRowIndex = 0
}
Dim batch As New List(Of TransactionRecord)(BatchSize)
Dim imported As Integer = 0
' Enumeration is lazy: only one row is materialised at a time, so peak memory
' stays flat regardless of how many rows the sheet holds.
For Each row As StreamRow In WorkBook.StreamRows(path, options)
Dim record As TransactionRecord = MapRow(row)
If record Is Nothing Then
Continue For
End If
batch.Add(record)
If batch.Count = BatchSize Then
Await sink.WriteAsync(batch)
imported += batch.Count
batch.Clear()
End If
Next
If batch.Count > 0 Then
Await sink.WriteAsync(batch)
imported += batch.Count
End If
Return imported
End Function
Private Shared Function MapRow(row As StreamRow) As TransactionRecord
Dim reference As StreamCell = row("Reference")
Dim amount As StreamCell = row("Amount")
Dim posted As StreamCell = row("PostedOn")
' Required columns missing on this row: skip rather than throw, so one bad
' row does not abort an import of several hundred thousand.
If reference Is Nothing OrElse amount Is Nothing Then
Return Nothing
End If
If amount.Type <> StreamCellType.Number Then
Return Nothing
End If
Dim postedOn As DateTime? = CType(posted?.Value, DateTime?)
' Optional column that only some source files carry.
Dim costCentre As String = If(row.TryGetCell("CostCentre", Nothing),
If(Nothing Is Nothing, Nothing, Nothing.Text),
Nothing)
Return New TransactionRecord With {
.SourceRow = row.RowNumber,
.Reference = reference.Text,
.Amount = Convert.ToDecimal(amount.Value, CultureInfo.InvariantCulture),
.PostedOn = postedOn,
.CostCentre = costCentre
}
End Function
End Class
Public Class TransactionRecord
Public Property SourceRow As Integer
Public Property Reference As String
Public Property Amount As Decimal
Public Property PostedOn As DateTime?
Public Property CostCentre As String
End Class
Public Interface ITransactionSink
Function WriteAsync(batch As IReadOnlyList(Of TransactionRecord)) As Task
End InterfaceImports IronXL
Imports System
Imports System.Collections.Generic
Imports System.Globalization
Imports System.Threading.Tasks
Public Class TransactionImporter
Private Const BatchSize As Integer = 5000
Public Async Function ImportAsync(path As String, sink As ITransactionSink) As Task(Of Integer)
Dim options As New StreamReadOptions With {
.SheetName = "Transactions",
.HasHeaderRow = True,
.HeaderRowIndex = 0
}
Dim batch As New List(Of TransactionRecord)(BatchSize)
Dim imported As Integer = 0
' Enumeration is lazy: only one row is materialised at a time, so peak memory
' stays flat regardless of how many rows the sheet holds.
For Each row As StreamRow In WorkBook.StreamRows(path, options)
Dim record As TransactionRecord = MapRow(row)
If record Is Nothing Then
Continue For
End If
batch.Add(record)
If batch.Count = BatchSize Then
Await sink.WriteAsync(batch)
imported += batch.Count
batch.Clear()
End If
Next
If batch.Count > 0 Then
Await sink.WriteAsync(batch)
imported += batch.Count
End If
Return imported
End Function
Private Shared Function MapRow(row As StreamRow) As TransactionRecord
Dim reference As StreamCell = row("Reference")
Dim amount As StreamCell = row("Amount")
Dim posted As StreamCell = row("PostedOn")
' Required columns missing on this row: skip rather than throw, so one bad
' row does not abort an import of several hundred thousand.
If reference Is Nothing OrElse amount Is Nothing Then
Return Nothing
End If
If amount.Type <> StreamCellType.Number Then
Return Nothing
End If
Dim postedOn As DateTime? = CType(posted?.Value, DateTime?)
' Optional column that only some source files carry.
Dim costCentre As String = If(row.TryGetCell("CostCentre", Nothing),
If(Nothing Is Nothing, Nothing, Nothing.Text),
Nothing)
Return New TransactionRecord With {
.SourceRow = row.RowNumber,
.Reference = reference.Text,
.Amount = Convert.ToDecimal(amount.Value, CultureInfo.InvariantCulture),
.PostedOn = postedOn,
.CostCentre = costCentre
}
End Function
End Class
Public Class TransactionRecord
Public Property SourceRow As Integer
Public Property Reference As String
Public Property Amount As Decimal
Public Property PostedOn As DateTime?
Public Property CostCentre As String
End Class
Public Interface ITransactionSink
Function WriteAsync(batch As IReadOnlyList(Of TransactionRecord)) As Task
End Interface正是批量導入使該方法得以成立。行逐一到達,積累到固定大小的緩衝區中,當緩衝區填滿時再被刷新到數據庫或隊列。 內存峰值由批量大小而非行數所限制,這意味著同樣的程式碼可以一致地處理文件的五千行和五百萬行。
選擇跳過格式錯誤的行而不是拋出是針對批量導入的故意選擇。 在數十萬行中,一行無法解析的信息不應該使運行中止,且記錄每一條記錄旁邊的RowNumber提供運營者一種將錯誤值追溯到源文件中的單元格的方法。
我該處理哪些邊緣個案?
GetSheetNames返回工作簿中的所有工作表,包含圖表頁。 圖表頁無行,因此將其索引傳到StreamRows會返回空序列,而不是報錯。 遍歷每一種工作表索引的程式將會發現這一點,在此結果中會希望有空項。
using IronXL;
using System;
using System.Collections.Generic;
// GetSheetNames returns every sheet in workbook order, including chartsheets.
// List position equals the index that StreamRows accepts.
IReadOnlyList<string> sheetNames = WorkBook.GetSheetNames("large-export.xlsx");
for (int i = 0; i < sheetNames.Count; i++)
{
int rowCount = 0;
foreach (StreamRow row in WorkBook.StreamRows("large-export.xlsx", i))
{
rowCount++;
}
// A chartsheet holds no rows, so it streams as an empty sequence rather than
// raising an error. Zero rows here is expected, not a failure to investigate.
if (rowCount == 0)
{
Console.WriteLine("[{0}] {1}: no rows (chartsheet or empty worksheet)", i, sheetNames[i]);
}
else
{
Console.WriteLine("[{0}] {1}: {2} rows", i, sheetNames[i], rowCount);
}
}using IronXL;
using System;
using System.Collections.Generic;
// GetSheetNames returns every sheet in workbook order, including chartsheets.
// List position equals the index that StreamRows accepts.
IReadOnlyList<string> sheetNames = WorkBook.GetSheetNames("large-export.xlsx");
for (int i = 0; i < sheetNames.Count; i++)
{
int rowCount = 0;
foreach (StreamRow row in WorkBook.StreamRows("large-export.xlsx", i))
{
rowCount++;
}
// A chartsheet holds no rows, so it streams as an empty sequence rather than
// raising an error. Zero rows here is expected, not a failure to investigate.
if (rowCount == 0)
{
Console.WriteLine("[{0}] {1}: no rows (chartsheet or empty worksheet)", i, sheetNames[i]);
}
else
{
Console.WriteLine("[{0}] {1}: {2} rows", i, sheetNames[i], rowCount);
}
}Imports IronXL
Imports System
Imports System.Collections.Generic
' GetSheetNames returns every sheet in workbook order, including chartsheets.
' List position equals the index that StreamRows accepts.
Dim sheetNames As IReadOnlyList(Of String) = WorkBook.GetSheetNames("large-export.xlsx")
For i As Integer = 0 To sheetNames.Count - 1
Dim rowCount As Integer = 0
For Each row As StreamRow In WorkBook.StreamRows("large-export.xlsx", i)
rowCount += 1
Next
' A chartsheet holds no rows, so it streams as an empty sequence rather than
' raising an error. Zero rows here is expected, not a failure to investigate.
If rowCount = 0 Then
Console.WriteLine("[{0}] {1}: no rows (chartsheet or empty worksheet)", i, sheetNames(i))
Else
Console.WriteLine("[{0}] {1}: {2} rows", i, sheetNames(i), rowCount)
End If
NextImports IronXL
Imports System
Imports System.Collections.Generic
' GetSheetNames returns every sheet in workbook order, including chartsheets.
' List position equals the index that StreamRows accepts.
Dim sheetNames As IReadOnlyList(Of String) = WorkBook.GetSheetNames("large-export.xlsx")
For i As Integer = 0 To sheetNames.Count - 1
Dim rowCount As Integer = 0
For Each row As StreamRow In WorkBook.StreamRows("large-export.xlsx", i)
rowCount += 1
Next
' A chartsheet holds no rows, so it streams as an empty sequence rather than
' raising an error. Zero rows here is expected, not a failure to investigate.
If rowCount = 0 Then
Console.WriteLine("[{0}] {1}: no rows (chartsheet or empty worksheet)", i, sheetNames(i))
Else
Console.WriteLine("[{0}] {1}: {2} rows", i, sheetNames(i), rowCount)
End If
Next日期單元格遵循工作簿的日期系統,包括由Excel for Mac生成的1904年基礎工作簿。 無需在調用代碼中進行調整。
下一步我該去哪裡?
StreamRows很好的覆蓋了一個工作:從大的XLSX文件中快速提取數值,且內存保持平坦。 除了讀取數值外的需求屬於完整的工作簿模型,在一個管道中混合這兩者是正常的。 流式加入用於驗證或導入,而當需要編輯時則Load用於較小的工作文件。
關於周邊功能,C#讀取XLSX文件的操作指導涵蓋標準讀取,導入Excel文件操作指導涵蓋數據導入模式,並且C#中讀取Excel文件教學逐步介紹了基本原理。 文件大小限制故障排除頁面涵蓋格式級別的行和列上限,並且WorkBook API參考完整文檔化了該類。
Ahmad 是一位全端開發人員,精通 C#、Python 和 Web 技術。他對建立可擴展的軟體解決方案有著濃厚的興趣,並樂於探索如何在實際應用中實現設計與功能的完美結合。