- A+
所属分类:.NET技术
由于经常需要进行报表导出的操作,但有时候数据量比较大,趁手的工具不是收费就是学习使用也比较花费时间成本,所以找了些库进行简单的整合,能够满足需求,百万条数据几分钟即可导出,效率也能满足要求,所以将就着用
数据读取处理
public class DBConnectFactory { public const string ReadConnectString = "server=SQL1;database=Test;User ID=sa;Password=sa"; public static SqlConnection CreateMssqlConnection(string connectionString) { var connection = new SqlConnection(connectionString); if (connection.State == System.Data.ConnectionState.Closed) connection.Open(); return connection; } } public IEnumerable<T> FindAll<T>(string sqltext) { using (SqlConnection connection = DBConnectFactory.CreateMssqlConnection(DBConnectFactory.ReadConnectString)) { var result = connection.Query<T>(sqltext); OnMessaged?.Invoke(this, $"已查询到{result.Count()}条记录..."); return result; } }
Excel数据写入处理
// source数据源 outputFileName导出文件名 public string Write<T>(IEnumerable<T> source, string outputFileName) { if (source == null || source.Count() == 0) return ""; if (!outputFileName.EndsWith(FileSuffix)) outputFileName += FileSuffix; string output = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory), outputFileName); string Template = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory), "Template.xlsx"); if (!File.Exists(Template)) File.Create(Template); int pageSize = source.Count() / size+ 1; var templateFile = new FileInfo(Template); var outputFile = new FileInfo(output); using (FastExcel.FastExcel fastExcel = new FastExcel.FastExcel(templateFile, outputFile)) { for (int i = 0; i < pageSize; i++) { var items = source.Skip(i * size).Take(size); fastExcel.Write(items, $"sheet{i + 1}", true); OnMessaged?.Invoke(this, $"{i + 1}页已导出..."); } OnMessaged?.Invoke(this, $"数据已导出,共{source.Count()}条...,{output}"); } return output; }
定义一个消息通知事件
public event EventHandler<string> OnMessaged;
工具调用
static void Main(string[] args) { var service = new ExcelService(); service.OnMessaged += Service_OnMessaged; try { var all = service.FindAll<Models.Wlyk>(SqlContants.SQLTEXT_SELECT_wlyk); service.Write(all, "导出文件名"); } catch (Exception ex) { Console.WriteLine(ex.Message); } Console.WriteLine("ok"); } private static void Service_OnMessaged(object sender, string e) { Console.WriteLine(e); }
工具库的开源地址,都是非常不错的库 可以给作者点点小星星✨✨✨
Dapper:https://github.com/DapperLib/Dapper
FastExcel:https://github.com/ahmedwalid05/FastExcel