Dapper+FastExcel SQL Server数据导出Excel

Dapper+FastExcel SQL Server数据导出Excel已关闭评论
278 次浏览

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);    }

完整代码下载