在 C# 中使用 Dapper 查询数据并导出 Excel
1. 配景先容在项目中，我们经常需要查询数据库中的数据并导出为 Excel 举行分析或存档。本篇文章先容如何在 C# 中使用 Dapper 查询 SQL Server 数据，并通过 EPPlus 天生 Excel 文件，支持 动态对象 (ExpandoObject) 和静态实体对象 的数据导出。
2. 代码结构概览
该实现包罗以下部分：
[*]数据库查询 (DapperHelper.Query)：实验 SQL 查询并返回 DynamicParameters 形式的效果。
[*]数据转换 (ExpandoObject)：查询效果转换为动态对象。
[*]静态对象列表 (Person)：示例数据。
[*]Excel 导出 (ExcelHelper.ExportToExcel)：将数据转换为 Excel 文件，支持 动态对象和静态对象，自动适配嵌套属性。
3. 代码实现
Dictionary<string, object> keyValuePairs = new();
[*] 为了方便我们举行多个Sheet和多个数据查询，我这边会把多个数据存储到一个键值对里面，键值对的Key是Sheet的名字，Value是我们查出来的静态或者动态实体内容的集合
3.1 使用 Dapper 查询数据库
public static async Task<IEnumerable<DynamicParameters>> Query(string sql, object? @params = null, int? commandTimeout = null, CommandType? commandType = null)
{
string ConnStr = AppConfig.SqlLinks;
using (var con = new SqlConnection(ConnStr))
{
var results = await con.QueryAsync(sql, @params, commandTimeout: commandTimeout, commandType: commandType);
return results.Select(r => new DynamicParameters(r));
}
}
[*]该方法使用 Dapper 查询数据库，并返回 DynamicParameters。
[*]连接字符串从 AppConfig.SqlLinks 获取。
[*]我们在不想创建实体对象的环境下直接选择把内容转化为动态实体类型
3.2 查询 orders 表并转换为动态对象
string sql = "Select * From .. With(NoLock)";
var results = await DapperHelper.Query(sql);
if (results.Count() > 0 && results != null)
{
var objects = results.Select(r =>
{
var expando = new ExpandoObject();
var dict = (IDictionary<string, object>)expando!;
foreach (var key in r.ParameterNames)
{
dict = r.Get<object>(key);
}
return expando;
});
}
[*]查询 orders 表，并转换查询效果为 动态对象 (ExpandoObject)。
[*]我们把他转换为动态对象之后，后续就可以使用它来直接展示
[*]把他直接存储键值对中，这个肯定都会存储，我这边就少些一行代码了
3.3 示例：静态对象列表 Person
var people = new List<Person>
{
new Person
{
Id = 1,
Name = "张三",
BirthDate = new DateTime(1990, 1, 1),
Salary = 5000.50m,
Address = new Address { City = "北京", State = "北京" },
Info = new Info {A = "2", B = "C"}
},
new Person
{
Id = 2,
Name = "李四",
BirthDate = new DateTime(1985, 5, 12),
Salary = 8000.75m,
Address = new Address { City = "上海", State = "上海" },
Info = new Info {A = "1", B = "B"}
}
};
[*]界说 静态对象 Person，这里都是我的示例，举例阐明的，可以是任何静态对象多层嵌套也是没问题的。
[*]把他直接存储键值对中，这个肯定都会存储，我这边就少些一行代码了
3.4 调用 Excel 导出方法
var excelData = ExcelHelper.ExportToExcel<Task>(keyValuePairs);
[*]通过 ExcelHelper.ExportToExcel<T> 方法，将 keyValuePairs 导出为 Excel。
[*]在这一步也就是调用我们主要的实现方法，这个方法里面主要是处理Excel和我们的实体区分处理。以实现全自动处理不区分类型
3.5 Excel 导出实现 (ExcelHelper)
public static byte[] ExportToExcel<T>(Dictionary<string, object> dataSets)
{
// 首先我们不能让我们的集合是空的
if (dataSets == null || !dataSets.Any())
{
throw new ArgumentException("数据列表不能为空");
}
// 因为这个分商用版和个人版本，一个收费一个不收费，所以我选择个人使用
ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;
// 创建一个处理Excel的生命周期
using (var package = new ExcelPackage())
{
// 这一步也就是我上面所说的，如果有多个集合进行区分Sheet处理不同Sheet存放不同集合数据
foreach (var dataSet in dataSets)
{
var sheetName = dataSet.Key;
var data = dataSet.Value;
var worksheet = package.Workbook.Worksheets.Add(sheetName);
if (data is IEnumerable<ExpandoObject> dynamicList)
{// 这一步主要是处理动态集合元素
// 获取动态集合的属性，这里一般动态元素的类型我们是获取不到的都是null
var properties = GetAllProperties(dynamicList.FirstOrDefault(), typeof(T));
for (int i = 0; i < properties.Count; i++)
{
// 写入第一行属性名称
worksheet.Cells.Value = properties.DisplayName ?? properties.PropertyName;
}
for (int row = 0; row < dynamicList.Count(); row++)
{
var item = dynamicList.ElementAt(row);
for (int col = 0; col < properties.Count; col++)
{
// 这一步用来处理嵌套属性
worksheet.Cells.Value = GetNestedPropertyValue(item, properties.PropertyName);
}
}
}
else if (data is IEnumerable<object> list)
{// 这一步主要是处理静态集合元素
// 获取静态集合的属性，像是静态属性，这里是可以获取到的
var properties = GetAllProperties(list.FirstOrDefault(), typeof(T));
for (int i = 0; i < properties.Count; i++)
{
// 写入第一行属性名称
worksheet.Cells.Value = properties.DisplayName ?? properties.PropertyName;
}
int row = 2;
foreach (var item in list)
{
for (int col = 0; col < properties.Count; col++)
{
// 写入内容
worksheet.Cells.Value = GetNestedPropertyValue(item, properties.PropertyName);
}
row++;
}
}
// 自动调整列宽
worksheet.Cells.AutoFitColumns();
}
// 返回Excel文件的字节数组
return package.GetAsByteArray();
}
}
private static List<PropertyMetadata> GetAllProperties(object obj, Type type = null, string prefix = "")
{
// 处理 动态属性
if (obj is IDictionary<string, object> dict)
{
// 遍历 ExpandoObject 的键值对
foreach (var key in dict.Keys)
{
// 添加属性元数据（ExpandoObject 没有 PropertyInfo）
properties.Add(new PropertyMetadata(null, key, key));
}
}
// 处理静态属性
else
{
// 获取类型的公共实例属性
foreach (var prop in type.GetProperties(BindingFlags.Public | BindingFlags.Instance))
{
// 构造属性名称（考虑前缀）
var propertyName = string.IsNullOrEmpty(prefix) ? prop.Name : $"{prefix}.{prop.Name}";
// 处理基本类型属性
if (prop.PropertyType.IsPrimitive || prop.PropertyType == typeof(string) || prop.PropertyType == typeof(DateTime) || prop.PropertyType == typeof(decimal))
{
// 获取显示名称（DisplayNameAttribute）
var displayName = prop.GetCustomAttribute<DisplayNameAttribute>()?.DisplayName;
// 添加属性元数据
properties.Add(new PropertyMetadata(prop, propertyName, displayName));
}
// 处理嵌套对象属性
else if (prop.PropertyType.IsClass)
{
// 递归获取嵌套属性
properties.AddRange(GetAllProperties(null, prop.PropertyType, propertyName));
}
}
}
// 返回属性元数据列表
return properties;
} /// <summary>
/// 属性名称（包括前缀）
/// </summary>
public string PropertyName { get; }
/// <summary>
/// 属性显示名称（DisplayNameAttribute）
/// </summary>
public string DisplayName { get; }
/// <summary>
/// 构造函数
/// </summary>
/// <param name="propertyInfo">PropertyInfo 对象（如果是基本类型，则为 null）</param>
/// <param name="propertyName">属性名称（包括前缀）</param>
/// <param name="displayName">属性显示名称（DisplayNameAttribute）</param>
public PropertyMetadata(PropertyInfo propertyInfo, string propertyName, string displayName)
{
PropertyInfo = propertyInfo;
PropertyName = propertyName;
DisplayName = displayName;
}
private static object GetNestedPropertyValue(object obj, string propertyName)
{
// 分割属性名称（支持嵌套属性）
var props = propertyName.Split('.');
// 初始化属性值
object value = obj;
// 遍历属性名称
foreach (var prop in props)
{
// 检查属性值是否为空
if (value == null)
{
return null; // 如果属性值为空，则返回 null
}
// 处理 动态属性，一般这个区分不了类型，后续可以在写入文档的时候区分类型
if (value is IDictionary<string, object> dict)
{
// 尝试获取属性值
dict.TryGetValue(prop, out value);
}
else // 处理静态属性
{
// 获取属性信息
var propertyInfo = value.GetType().GetProperty(prop);
// 检查属性是否存在
if (propertyInfo == null)
{
return null; // 如果属性不存在，则返回 null
}
// 获取属性值
value = propertyInfo.GetValue(value);
}
}
// 返回属性值
return value;
}
[*]自动剖析动态对象和静态对象
[*]支持嵌套对象属性导出
[*]格式化 Excel，自动调整列宽
3.6 最后输出效果展示
https://i-blog.csdnimg.cn/direct/d4b3dff2e43c4b81965233b75a8ee2bc.pnghttps://i-blog.csdnimg.cn/direct/52cd97195dbf4dc29b3c9b972e5989eb.png
4. 总结
页:
[1]