如何在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>,每次访问单元格时进行空值检查 - 在读取
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 将作为第1行的单元格值出现,而不是用来命名列。
返回类型是IEnumerable<StreamRow>,重要的区别在于,在序列被迭代之前没有读取任何内容。 随着循环前进,行被一个一个地产生,因此在500,000行的表格上进行foreach时,内存中只会保留一行,而不是五十万行。 结果是序列是单遍且仅向前的:不能返回到前面的行,也不能索引到中间的一个。 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报告了什么?
StreamCellType 值,由StreamCell.Type报告
| 价值 | 报告时间 |
|---|---|
Blank | 单元格不包含值。 Value 是null |
Number | 单元格持有数值 |
String | 单元格持有文本 |
Boolean | 单元格保存TRUE或FALSE |
Date | 单元格持有日期,遵循工作簿的日期系统 |
Error | 单元格持有Excel错误值 |
有意没有Formula成员。 公式单元格在此报告其缓存结果的真实类型,例如Number,通过IsFormula单独暴露公式性。 扫描枚举以寻找公式情况的人将找不到一个,这不是遗漏,而是经过设计的。
流式读取器的限制是什么?
提前知道这些可以避免最常见的误诊。
只有路径重载是真正低内存的。 这是本页最重要的警告。 开发人员对400 MB上传传递为byte[]、观察内存攀升并得出功能损坏的结论正好撞到了这一点。
其余的界限是由设计固定的:
- 只读:流式读取器不能修改或保存。 编辑意味着
WorkBook.Load。 - 仅支持XLSX:不支持
.xls,不支持加密工作簿,也不支持任何形式的书写。 - 仅向前且单遍:无随机访问和倒回功能。 需要在工作表中跳转的代码需要
Load。 - 无样式或格式化对象:单元格外观不可见。
- 仅缓存公式结果:公式表达式不可见且没有重新计算。
StreamRows和GetSheetNames都需要有效的IronXL许可证,在调用方法时严格执行,就像WorkBook.Load。还有一个坐落在限制和行为之间的警告:从路径读取的低内存是尽最大努力。 一个无法读取其压缩目录的工作簿可能会回退到将包加载到内存中。
如何构建流式传输导入管道?
生产工作所采用的模式是流式读取饲料分批写入,因此源和目的地均未以完整大小保存在内存中。
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分批使这得以实现。行逐个到达,积累在一个固定大小的缓冲区中,当缓冲区填满时刷新到数据库或队列。 峰值内存由批次大小界定而不是行数,这意味着相同的代码相同地处理一个5,000行的文件和一个5,000,000行的文件。
跳过格式不正确的行而不是抛出是一个故意选择用于批量导入。 几千行中有一行无法解析不应中断运行,并且将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 技术基础。他对构建可扩展的软件解决方案深感兴趣,并喜欢探索设计和功能在实际应用中如何结合。