使用ExcelDataReader预设列类型并优化Excel转DataTable导入效率

一、读取阶段直接配置列类型与空值转换 #

无需事后克隆表格，可在Excel读取阶段直接指定列类型，并将空单元格转换为DBNull.Value（DataTable标准空值表示），从根源解决空白Object问题。

// 定义列索引与目标数据类型的映射
var columnTypeMapping = new Dictionary<int, Type>
{
    { 0, typeof(float) },
    { 26, typeof(DateTime) },
    { 27, typeof(DateTime) },
    // 其他列若默认是string可省略，按需添加即可
};

var result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
    ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
    {
        UseHeaderRow = true,
        // 提前设置列数据类型
        FilterColumn = (columnReader, columnIndex) =>
        {
            if (columnTypeMapping.ContainsKey(columnIndex))
            {
                columnReader.DataType = columnTypeMapping[columnIndex];
            }
            return true;
        },
        // 转换行数据，将空单元格转为DBNull.Value
        TransformRow = (rowReader) =>
        {
            var rowValues = new object[rowReader.FieldCount];
            for (int i = 0; i < rowReader.FieldCount; i++)
            {
                rowValues[i] = rowReader.IsDBNull(i) ? DBNull.Value : rowReader.GetValue(i);
            }
            return rowValues;
        }
    }
});

var table = result.Tables.Cast<DataTable>().First();
// 添加自定义列并设置默认值
table.Columns.Add("DateCreated", typeof(DateTime)).DefaultValue = DateTime.Now;

二、批量插入性能优化（若需克隆表格场景） #

如果必须使用克隆表格的方式，避免使用ImportRow（该方法会触发大量事件与约束检查，性能极低），改用BeginLoadData+LoadDataRow组合关闭约束与事件，大幅提升插入速度：

// 手动构建目标DataTable（比Clone后修改类型更高效，避免潜在类型冲突）
DataTable targetTable = new DataTable();
// 按顺序添加列并指定类型
targetTable.Columns.Add("列名0", typeof(float));
targetTable.Columns.Add("列名1", typeof(string));
// ... 依次添加剩余25个string类型列
targetTable.Columns.Add("列名26", typeof(DateTime));
targetTable.Columns.Add("列名27", typeof(DateTime));
targetTable.Columns.Add("DateCreated", typeof(DateTime)).DefaultValue = DateTime.Now;

// 关闭约束检查与事件触发，提升插入性能
targetTable.BeginLoadData();
foreach (DataRow sourceRow in table.Rows)
{
    var rowValues = new object[sourceRow.ItemArray.Length + 1];
    // 处理空值，将空白Object转为DBNull.Value
    for (int i = 0; i < sourceRow.ItemArray.Length; i++)
    {
        var cellValue = sourceRow.ItemArray[i];
        rowValues[i] = cellValue is { } && string.IsNullOrWhiteSpace(cellValue.ToString()) 
            ? DBNull.Value 
            : cellValue;
    }
    // DateCreated使用列默认值，此处可省略赋值
    rowValues[sourceRow.ItemArray.Length] = DateTime.Now;
    
    targetTable.LoadDataRow(rowValues, true);
}
targetTable.EndLoadData();

三、额外性能建议 #

• 使用最新版本的ExcelDataReader，新版本针对大表格读取有性能优化；
• 若内存占用过高，可尝试分批读取Excel数据（比如每次读取1000行），再批量插入目标DataTable。

内容的提问来源于stack exchange，提问作者Caleb