ASP.NET 导出 Excel 数据：OLEDB 与 NPOI 方案对比与代码实现
基于 OLEDB 的 Excel 导出方案
OLEDB 方式的核心思想是将 Excel 文件视为关系型数据库，通过执行 SQL 语句（如 CREATE TABLE 和 INSERT INTO）来写入数据。这种方式的缺点在于每次插入都需要与文件系统进行交互，导致在数据量较大时性能急剧下降。此外，它对服务器环境的依赖较强（需要安装相应的 Access Database Engine）。因此，在实际的企业级项目中，通常不建议将其作为首选方案，仅适用于极小数据量的简单导出场景。
以下是优化后的 OLEDB 导出实现，采用了参数化查询以防止特殊字符（如单引号）导致的 SQL 语法错误：
public static void ExportViaOleDb(DataTable sourceTable, string targetFilePath, string sheetName)
{
// 使用 ACE OLEDB 12.0 驱动以支持 .xlsx 格式
string connectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={targetFilePath};Extended Properties='Excel 12.0 Xml;HDR=YES;'";
using (var connection = new OleDbConnection(connectionString))
{
connection.Open();
// 动态构建建表 SQL 语句
var createTableSql = new StringBuilder($"CREATE TABLE [{sheetName}] (");
foreach (DataColumn column in sourceTable.Columns)
{
createTableSql.Append($"[{column.ColumnName}] TEXT,");
}
createTableSql.Length--; // 移除末尾多余的逗号
createTableSql.Append(")");
using (var createCmd = new OleDbCommand(createTableSql.ToString(), connection))
{
createCmd.ExecuteNonQuery();
}
// 构建参数化的插入 SQL 语句
var insertSql = new StringBuilder($"INSERT INTO [{sheetName}] (");
var paramNames = new List<string>();
foreach (DataColumn column in sourceTable.Columns)
{
insertSql.Append($"[{column.ColumnName}],");
paramNames.Add($"@p{column.Ordinal}");
}
insertSql.Length--;
insertSql.Append(") VALUES (");
insertSql.Append(string.Join(",", paramNames));
insertSql.Append(")");
using (var insertCmd = new OleDbCommand(insertSql.ToString(), connection))
{
// 预定义参数
foreach (DataColumn column in sourceTable.Columns)
{
insertCmd.Parameters.Add(new OleDbParameter($"@p{column.Ordinal}", OleDbType.VarChar));
}
// 遍历数据行并赋值
foreach (DataRow row in sourceTable.Rows)
{
for (int i = 0; i < sourceTable.Columns.Count; i++)
{
insertCmd.Parameters[i].Value = row[i]?.ToString() ?? string.Empty;
}
insertCmd.ExecuteNonQuery();
}
}
}
}
基于 NPOI 的 Excel 导出方案
NPOI 是一个纯 C# 实现的开源库，无需在服务器上安装 Microsoft Office 即可读写 Excel 文件。它通过在内存中构建文档对象模型（DOM），最后一次性序列化到流中，因此在性能和灵活性上远优于 OLEDB。
导出为 Excel 2007+ 格式 (.xlsx)
处理 .xlsx 格式时，需使用 XSSF 命名空间下的 XSSFWorkbook。以下代码展示了如何将系统操作日志列表导出为 .xlsx 文件，并直接通过 ASP.NET MVC 的 FileResult 返回给客户端下载。
public FileResult ExportOperationLogsToXlsx(LogSearchCriteria criteria)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("OperationLogs");
// 从服务层获取业务数据
var logs = _logService.GetLogs(criteria).ToList();
// 定义并写入表头
string[] headers = { "日志ID", "用户账号", "操作模块", "操作类型", "IP地址", "操作时间", "执行结果" };
IRow headerRow = sheet.CreateRow(0);
for (int i = 0; i < headers.Length; i++)
{
headerRow.CreateCell(i).SetCellValue(headers[i]);
}
// 遍历集合填充数据行
for (int i = 0; i < logs.Count; i++)
{
IRow dataRow = sheet.CreateRow(i + 1);
var log = logs[i];
dataRow.CreateCell(0).SetCellValue(log.Id);
dataRow.CreateCell(1).SetCellValue(log.UserName ?? "");
dataRow.CreateCell(2).SetCellValue(log.ModuleName ?? "");
dataRow.CreateCell(3).SetCellValue(log.ActionType ?? "");
dataRow.CreateCell(4).SetCellValue(log.IpAddress ?? "");
dataRow.CreateCell(5).SetCellValue(log.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"));
dataRow.CreateCell(6).SetCellValue(log.IsSuccess ? "成功" : "失败");
}
// 将工作簿写入内存流并转换为字节数组
using (var memoryStream = new MemoryStream())
{
workbook.Write(memoryStream);
byte[] fileContents = memoryStream.ToArray();
string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
return File(fileContents, contentType, "OperationLogs.xlsx");
}
}
导出为 Excel 97-2003 格式 (.xls)
处理 .xls 格式时，需使用 HSSF 命名空间下的 HSSFWorkbook。由于 Excel 2003 单个工作表的最大行数限制为 65536 行，在导出海量数据时，必须在代码层面实现自动分页（分 Sheet）逻辑。
public ActionResult ExportReportToXls(DateTime startDate, DateTime endDate, string reportName)
{
IWorkbook workbook = new HSSFWorkbook();
DataTable reportData = _reportService.GenerateReport(startDate, endDate);
// Excel 2003 最大行数为 65536，预留一行给表头，故单 Sheet 最大数据行为 65535
const int maxRowsPerSheet = 65535;
int totalRows = reportData.Rows.Count;
if (totalRows <= maxRowsPerSheet)
{
WriteDataToSheet(workbook, "Report", reportData, 0, totalRows - 1);
}
else
{
int sheetIndex = 0;
int currentIndex = 0;
// 循环分片写入多个 Sheet
while (currentIndex < totalRows)
{
int rowsToWrite = Math.Min(maxRowsPerSheet, totalRows - currentIndex);
string sheetName = $"Report_{sheetIndex + 1}";
WriteDataToSheet(workbook, sheetName, reportData, currentIndex, currentIndex + rowsToWrite - 1);
currentIndex += rowsToWrite;
sheetIndex++;
}
}
using (var memoryStream = new MemoryStream())
{
workbook.Write(memoryStream);
byte[] fileContents = memoryStream.ToArray();
return File(fileContents, "application/vnd.ms-excel", $"{reportName}.xls");
}
}
/// <summary>
/// 将 DataTable 的指定行范围写入到特定的 Sheet 中
/// </summary>
private void WriteDataToSheet(IWorkbook workbook, string sheetName, DataTable data, int startRow, int endRow)
{
ISheet sheet = workbook.CreateSheet(sheetName);
// 写入列名作为表头
IRow headerRow = sheet.CreateRow(0);
for (int i = 0; i < data.Columns.Count; i++)
{
headerRow.CreateCell(i).SetCellValue(data.Columns[i].ColumnName);
}
// 写入数据内容
int rowIndex = 1;
for (int i = startRow; i <= endRow; i++)
{
IRow dataRow = sheet.CreateRow(rowIndex++);
for (int j = 0; j < data.Columns.Count; j++)
{
dataRow.CreateCell(j).SetCellValue(data.Rows[i][j]?.ToString() ?? "");
}
}
}
NPOI 核心命名空间与版本差异
在使用 NPOI 时，必须根据目标 Excel 文件的版本选择正确的命名空间和实现类：
- Excel 97-2003 (.xls)：基于二进制格式，需引用
NPOI.HSSF.UserModel命名空间，并使用HSSFWorkbook类。 - Excel 2007+ (.xlsx)：基于 Office Open XML 格式，需引用
NPOI.XSSF.UserModel命名空间，并使用XSSFWorkbook类。
为了提升代码的可维护性，建议在业务逻辑中尽量面向接口编程（如使用 IWorkbook、ISheet、IRow 和 ICell），将具体的实例化逻辑（HSSF 或 XSSF）通过工厂模式或依赖注入进行解耦，从而在需要切换导出格式时只需修改极少量的代码。
