Dapper+FastExcel SQL Server数据导出Excel

由于经常需要进行报表导出的操作，但有时候数据量比较大，趁手的工具不是收费就是学习使用也比较花费时间成本，所以找了些库进行简单的整合，能够满足需求，百万条数据几分钟即可导出，效率也能满足要求，所以将就着用

数据读取处理

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