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