C# export List<T> to excel file via EPPlus

Install-Package EPPlus
Install-Package Newtonsoft.Json

using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Windows;

namespace WpfApp7
{
    public static class ExportListTHelper<T> where T : class
    { 
        static ExportListTHelper()
        {
            ExcelPackage.License.SetNonCommercialPersonal("Fred");
        }

        public static async Task ExportListTToExcel(List<T> dataList,string excelFilePath="")
        {
           await Task.Run(() =>
            {
                using (var package = new ExcelPackage())
                {
                    var worksheet = package.Workbook.Worksheets.Add("Sheet_1");

                    // Auto map List<T> to sheet (header + rows)
                    worksheet.Cells["A1"].LoadFromCollection(dataList, PrintHeaders: true);

                    // Auto-fit columns
                    worksheet.Cells.AutoFitColumns();
                    FileInfo fileInfo = new FileInfo(excelFilePath);
                    package.SaveAs(fileInfo);
                }
            });

            await Application.Current?.Dispatcher?.InvokeAsync(() =>
            {
                MessageBox.Show($"Saved {dataList.Count} items in {excelFilePath}",
                    $"{DateTime.Now}");
            },System.Windows.Threading.DispatcherPriority.Background);
        }
    }
}

 

private DelCmd firstCmd;
public DelCmd FirstCmd
{
    get
    {
        if (firstCmd == null)
        {
            firstCmd = new DelCmd(FirstCmdExecuted);
        }
        return firstCmd;
    }
}

private void FirstCmdExecuted(object?obj)
{
    Task.Run(() =>
    {
        _ = SaveFileInExcelAsync(obj);
    });
}
private async Task SaveFileInExcelAsync(object? obj)
{
    var items = ((System.Collections.IList)obj)?.Cast<Book>()?.ToList();
    if (items == null || !items.Any())
    {
        return;
    }
    string excelPath = GetExcelFilePath();
    if(!string.IsNullOrWhiteSpace(excelPath))
    {
        await ExportListTHelper<Book>.ExportListTToExcel(items, excelPath);
    }
}

using System;
using System.Collections.Generic;
using System.Text;
using System.Windows;
using System.Windows.Input;

namespace WpfApp7
{
    public class DelCmd : ICommand
    {
        private readonly Action<object?> execute;
        private readonly Func<object?, bool> canExecute;
        public DelCmd(Action<object?> executeValue, Func<object?, bool> canExecuteValue = null)
        {
            execute = executeValue ?? throw new ArgumentNullException(nameof(executeValue));
            canExecute = canExecuteValue;
        }

        public event EventHandler? CanExecuteChanged;

        public bool CanExecute(object? parameter)
        {
            return canExecute == null ? true : canExecute(parameter);
        }

        public void Execute(object? parameter)
        {
            if (!CanExecute(parameter))
            {
                return;
            }

            execute?.Invoke(parameter);            
        }

        public void RaiseCanExecuteChanged()
        {
            var handler = Volatile.Read(ref CanExecuteChanged);
            if (handler == null)
            {
                return;
            }

            if (Application.Current?.Dispatcher?.CheckAccess() == true)
            {
                handler?.Invoke(this, EventArgs.Empty);
            }
            else
            {
                Application.Current?.Dispatcher?.Invoke(() =>
                {
                    handler?.Invoke(this, EventArgs.Empty);
                });
            }
        }
    }
}

 

 

 

 

 

 

 

 

//D:\C\WpfApp7\WpfApp7\UCDG.xaml
<UserControl x:Class="WpfApp7.UCDG"
             xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
             xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
             xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" 
             xmlns:d="http://schemas.microsoft.com/expression/blend/2008" 
             xmlns:local="clr-namespace:WpfApp7"
             mc:Ignorable="d" 
             d:DesignHeight="450" d:DesignWidth="800">
    <UserControl.Resources>
        <local:ProxyBinding x:Key="ProxyBinding"
                            SourceObject="{Binding RelativeSource={RelativeSource AncestorType=UserControl}}" />
    </UserControl.Resources>
    <Grid>
        <DataGrid ItemsSource="{Binding UCDCCollection,RelativeSource={RelativeSource AncestorType=UserControl}}"
                  VirtualizingPanel.IsVirtualizing="True"
                  VirtualizingPanel.VirtualizationMode="Recycling"
                  VirtualizingPanel.CacheLengthUnit="Item"
                  VirtualizingPanel.CacheLength="5,5"
                  ScrollViewer.CanContentScroll="True"
                  ScrollViewer.IsDeferredScrollingEnabled="True"
                  EnableRowVirtualization="True"
                  EnableColumnVirtualization="True"
                  UseLayoutRounding="True"
                  SnapsToDevicePixels="True"
                  CanUserAddRows="False"
                  AutoGenerateColumns="True"
                  SelectionMode="Extended">
            <DataGrid.Resources>
                <Style TargetType="DataGridRow">
                    <Setter Property="FontSize" Value="30"/>
                    <Style.Triggers>
                        <Trigger Property="IsMouseOver" Value="True">
                            <Setter Property="FontSize" Value="50"/>
                            <Setter Property="Foreground" Value="Red"/>
                        </Trigger>
                    </Style.Triggers>
                </Style>
            </DataGrid.Resources>
            <DataGrid.ContextMenu>
                <ContextMenu>
                    <MenuItem Header="{Binding SourceObject.UCFirstHeader,Source={StaticResource ProxyBinding}}"
                              Command="{Binding SourceObject.UCFirstCmd,Source={StaticResource ProxyBinding}}"
                              CommandParameter="{Binding RelativeSource={RelativeSource AncestorType=ContextMenu},
                        Path=PlacementTarget.SelectedItems}"/>
                </ContextMenu>
            </DataGrid.ContextMenu>
        </DataGrid>
    </Grid>
</UserControl>


//D:\C\WpfApp7\WpfApp7\UCDG.xaml.cs
using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

namespace WpfApp7
{
    /// <summary>
    /// Interaction logic for UCDG.xaml
    /// </summary>
    public partial class UCDG : UserControl
    {
        public UCDG()
        {
            InitializeComponent();
        }

        public IList UCDCCollection
        {
            get { return (IList)GetValue(UCDCCollectionProperty); }
            set { SetValue(UCDCCollectionProperty, value); }
        }

        // Using a DependencyProperty as the backing store for UCDCCollection.  This enables animation, styling, binding, etc...
        public static readonly DependencyProperty UCDCCollectionProperty =
            DependencyProperty.Register(
                nameof(UCDCCollection), 
                typeof(IList), 
                typeof(UCDG), 
                new PropertyMetadata(null));





        public string UCFirstHeader
        {
            get { return (string)GetValue(UCFirstHeaderProperty); }
            set { SetValue(UCFirstHeaderProperty, value); }
        }

        // Using a DependencyProperty as the backing store for UCFirstHeader.  This enables animation, styling, binding, etc...
        public static readonly DependencyProperty UCFirstHeaderProperty =
            DependencyProperty.Register(
                nameof(UCFirstHeader),
                typeof(string), 
                typeof(UCDG), 
                new PropertyMetadata(null));




        public DelCmd UCFirstCmd
        {
            get { return (DelCmd)GetValue(UCFirstCmdProperty); }
            set { SetValue(UCFirstCmdProperty, value); }
        }

        // Using a DependencyProperty as the backing store for UCFirstCmd.  This enables animation, styling, binding, etc...
        public static readonly DependencyProperty UCFirstCmdProperty =
            DependencyProperty.Register(
                nameof(UCFirstCmd), 
                typeof(DelCmd), 
                typeof(UCDG), 
                new PropertyMetadata(null));

    }


    public class ProxyBinding : Freezable
    {
        protected override Freezable CreateInstanceCore()
        {
            return new ProxyBinding();
        }



        public object SourceObject
        {
            get { return (object)GetValue(SourceObjectProperty); }
            set { SetValue(SourceObjectProperty, value); }
        }

        // Using a DependencyProperty as the backing store for SourceObject.  This enables animation, styling, binding, etc...
        public static readonly DependencyProperty SourceObjectProperty =
            DependencyProperty.Register(
                nameof(SourceObject),
                typeof(object),
                typeof(ProxyBinding),
                new PropertyMetadata(null));


    }
}

 

//D:\C\WpfApp7\WpfApp7\MainWindow.xaml
<Window x:Class="WpfApp7.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:WpfApp7"
        mc:Ignorable="d"
        Title="{Binding MainTitle}"
        WindowState="Maximized">
    <Window.DataContext>
        <local:MainVM/>
    </Window.DataContext>
    <Grid>
        <local:UCDG UCDCCollection="{Binding BooksCollection}"
                    UCFirstCmd="{Binding FirstCmd}"
                    UCFirstHeader="{Binding FirstHeader}"/>
    </Grid>
</Window>


//D:\C\WpfApp7\WpfApp7\MainWindow.xaml.cs

using Microsoft.Win32;
using Newtonsoft.Json;
using System.Collections.ObjectModel;
using System.ComponentModel;
using System.ComponentModel.Design.Serialization;
using System.Net.Http;
using System.Runtime.CompilerServices;
using System.Text;
using System.Text.Json.Serialization;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

namespace WpfApp7
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }
    }

    public class MainVM : INotifyPropertyChanged
    {
        private readonly object isloadingLock = new object();
        HttpClient client;
        string originalUrl = $"http://localhost:55622/BookService.svc/getbookslist?cnt=";
        private readonly SemaphoreSlim concurrentThrottle = new SemaphoreSlim(10, 10);
        public MainVM()
        {
            if (!DesignerProperties.GetIsInDesignMode(new DependencyObject()))
            {
                client = new HttpClient();
                Task.Run(async () =>
                {
                    await LoadDataEndless();
                });
            }
        }

        private async Task LoadDataEndless()
        {
            try
            {
                while (true)
                {
                    await InitBooksAsync(1000000);
                    await Task.Delay(20000);
                }
            }
            catch (Exception ex)
            {
                PrintMsg($"In LoadDataEndless(),ex:{ex?.Message}");
            }
        }

        private async Task InitBooksAsync(int cnt = 1000000)
        {
            if (!await concurrentThrottle.WaitAsync(30000))
            {
                return;
            }

            if (IsLoading)
            {
                return;
            }

            try
            {
                MainTitle = $"{DateTime.Now},loading...";
                string requestUrl = $"{originalUrl}{cnt}";
                var bksList = await Task.Run(async () =>
                {
                    var jsonStr = await client.GetStringAsync(requestUrl);
                    var bks = JsonConvert.DeserializeObject<List<Book>>(jsonStr);
                    return bks;
                }).ConfigureAwait(false);

                if (bksList == null || !bksList.Any())
                {
                    return;
                }

                await Application.Current?.Dispatcher?.InvokeAsync(() =>
                {
                    BooksCollection = new ObservableCollection<Book>(bksList);
                    MainTitle = $"{DateTime.Now},loaded {BooksCollection?.Count} items,FirstId:{BooksCollection[0]?.Id},LastId:{BooksCollection[^1]?.Id}";
                }, System.Windows.Threading.DispatcherPriority.Background);
            }
            catch (Exception ex)
            {
                PrintMsg(ex?.Message);
                IsLoading = false;
            }
            finally
            {
                IsLoading = false;
                concurrentThrottle.Release();
            }
        }

        private void PrintMsg(string? msg)
        {
#if DEBUG
            System.Diagnostics.Debug.WriteLine($"{DateTime.Now},{msg}");
#else
            System.Diagnostics.Trace.WriteLine($"{DateTime.Now},{msg}");
#endif
        }

        private bool isLoading;
        public bool IsLoading
        {
            get
            {
                lock (isloadingLock)
                {
                    return isLoading;
                }
            }
            set
            {
                lock (isloadingLock)
                {
                    if (value != isLoading)
                    {
                        isLoading = value;
                        OnPropertyChanged();
                    }
                }
            }
        }

        private string firstHeader = "Save In Excel";
        public string FirstHeader
        {
            get
            {
                return firstHeader;
            }
            set
            {
                if (value != firstHeader)
                {
                    firstHeader = value;
                    OnPropertyChanged();
                }
            }
        }

        private DelCmd firstCmd;
        public DelCmd FirstCmd
        {
            get
            {
                if (firstCmd == null)
                {
                    firstCmd = new DelCmd(FirstCmdExecuted);
                }
                return firstCmd;
            }
        }

        private void FirstCmdExecuted(object?obj)
        {
            Task.Run(() =>
            {
                _ = SaveFileInExcelAsync(obj);
            });
        }
        private async Task SaveFileInExcelAsync(object? obj)
        {
            var items = ((System.Collections.IList)obj)?.Cast<Book>()?.ToList();
            if (items == null || !items.Any())
            {
                return;
            }
            string excelPath = GetExcelFilePath();
            if(!string.IsNullOrWhiteSpace(excelPath))
            {
                await ExportListTHelper<Book>.ExportListTToExcel(items, excelPath);
            }
        }

        private string GetExcelFilePath()
        {
            string excelPath = "";
            Application.Current?.Dispatcher?.Invoke(() =>
            {
                SaveFileDialog dlg = new SaveFileDialog();
                dlg.Filter = $"Excel Files|*.xlsx;*.xls";
                dlg.FileName = $"Excel_{DateTime.Now.ToString("yyyyMMddHHmmssfff")}";
                if (dlg.ShowDialog() == true)
                {
                    excelPath= dlg.FileName;
                }
            },System.Windows.Threading.DispatcherPriority.Send);
            return excelPath;
        }

        private string mainTitle = $"{DateTime.Now}";
        public string MainTitle
        {
            get
            {
                return mainTitle;
            }
            set
            {
                if (value != mainTitle)
                {
                    mainTitle = value;
                    OnPropertyChanged();
                }
            }
        }

        private ObservableCollection<Book> booksCollection;
        public ObservableCollection<Book> BooksCollection
        {
            get
            {
                return booksCollection;
            }
            set
            {
                if (value != booksCollection)
                {
                    booksCollection = value;
                    OnPropertyChanged();
                }
            }
        }
        public event PropertyChangedEventHandler? PropertyChanged;
        private void OnPropertyChanged([CallerMemberName] string propName = "")
        {
            var handler = Volatile.Read(ref PropertyChanged);
            if (handler == null)
            {
                return;
            }
            handler?.Invoke(this, new PropertyChangedEventArgs(propName));
        }
    }
}


//D:\C\WpfApp7\WpfApp7\Book.cs
using System;
using System.Collections.Generic;
using System.Text;

namespace WpfApp7
{
    public class Book
    {
        public long Id { get; set; }
        public string ISBN { get; set; }
        public string Name { get; set; }
        public string Author { get; set; }
        public string Abstract { get; set; }
        public string Comment { get; set; }
        public string Content { get; set; }
        public string Summary { get; set; }
        public string Title { get; set; }
        public string Topic { get; set; }
    }
}

//D:\C\WpfApp7\WpfApp7\DelCmd.cs
using System;
using System.Collections.Generic;
using System.Text;
using System.Windows;
using System.Windows.Input;

namespace WpfApp7
{
    public class DelCmd : ICommand
    {
        private readonly Action<object?> execute;
        private readonly Func<object?, bool> canExecute;
        public DelCmd(Action<object?> executeValue, Func<object?, bool> canExecuteValue = null)
        {
            execute = executeValue ?? throw new ArgumentNullException(nameof(executeValue));
            canExecute = canExecuteValue;
        }

        public event EventHandler? CanExecuteChanged;

        public bool CanExecute(object? parameter)
        {
            return canExecute == null ? true : canExecute(parameter);
        }

        public void Execute(object? parameter)
        {
            if (!CanExecute(parameter))
            {
                return;
            }

            execute?.Invoke(parameter);            
        }

        public void RaiseCanExecuteChanged()
        {
            var handler = Volatile.Read(ref CanExecuteChanged);
            if (handler == null)
            {
                return;
            }

            if (Application.Current?.Dispatcher?.CheckAccess() == true)
            {
                handler?.Invoke(this, EventArgs.Empty);
            }
            else
            {
                Application.Current?.Dispatcher?.Invoke(() =>
                {
                    handler?.Invoke(this, EventArgs.Empty);
                });
            }
        }
    }
}


//D:\C\WpfApp7\WpfApp7\ExportListTInExcel.cs
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Windows;

namespace WpfApp7
{
    public static class ExportListTHelper<T> where T : class
    { 
        static ExportListTHelper()
        {
            ExcelPackage.License.SetNonCommercialPersonal("Fred");
        }

        public static async Task ExportListTToExcel(List<T> dataList,string excelFilePath="")
        {
           await Task.Run(() =>
            {
                using (var package = new ExcelPackage())
                {
                    var worksheet = package.Workbook.Worksheets.Add("Sheet_1");

                    // Auto map List<T> to sheet (header + rows)
                    worksheet.Cells["A1"].LoadFromCollection(dataList, PrintHeaders: true);

                    // Auto-fit columns
                    worksheet.Cells.AutoFitColumns();
                    FileInfo fileInfo = new FileInfo(excelFilePath);
                    package.SaveAs(fileInfo);
                }
            });

            await Application.Current?.Dispatcher?.InvokeAsync(() =>
            {
                MessageBox.Show($"Saved {dataList.Count} items in {excelFilePath}",
                    $"{DateTime.Now}");
            },System.Windows.Threading.DispatcherPriority.Background);
        }
    }
}

 

//Server
//D:\C\WcfService2\WcfService2\IBookService.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;

namespace WcfService2
{
    // NOTE: You can use the "Rename" command on the "Refactor" menu to change the interface name "IBookService" in both code and config file together.
    [ServiceContract]
    public interface IBookService
    {
        [OperationContract]
        [WebGet(UriTemplate =("/getbookslist?cnt={cnt}"),RequestFormat =WebMessageFormat.Json,ResponseFormat =WebMessageFormat.Json)]
        List<Book> GetBooksList(int cnt = 1000000); 
    }

    public class Book
    {
        public long Id { get; set; }
        public string ISBN { get; set; }
        public string Name { get; set; }
        public string Author { get; set; }
        public string Abstract { get; set; }
        public string Comment { get; set; }
        public string Content { get; set; }
        public string Summary { get; set; }
        public string Title { get; set; }
        public string Topic { get; set; }
    }
}

//D:\C\WcfService2\WcfService2\BookService.svc.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.Threading;

namespace WcfService2
{
    // NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "BookService" in code, svc and config file together.
    // NOTE: In order to launch WCF Test Client for testing this service, please select BookService.svc or BookService.svc.cs at the Solution Explorer and start debugging.
    public class BookService : IBookService
    {
        private static long idx = 1;
        private static (long,long) GetStartEndRange(int cnt=1000000)
        {
            long end=Interlocked.Add(ref  idx, cnt);
            long start = end - cnt;
            return (start,end);
        }

        public List<Book> GetBooksList(int cnt = 1000000)
        {
            List<Book> bksList=new List<Book>();
            var(start,end) = GetStartEndRange(cnt);
            for(long i=start;i<end;i++)
            {
                bksList.Add(new Book()
                {
                    Id=i,
                    Name=$"Name_{i}",
                    ISBN=$"ISBN_{i}_{Guid.NewGuid():N}",
                    Author=$"Author_{i}",
                    Abstract=$"Abstract_{i}",
                    Comment=$"Comment_{i}",
                    Content=$"Content_{i}",
                    Summary=$"Summary_{i}",
                    Title=$"Title_{i}",
                    Topic=$"Topic_{i}"
                });
            }
            return bksList;
        }
    }
}

//D:\C\WcfService2\WcfService2\Web.config
<?xml version="1.0"?>
<configuration>

  <appSettings>
    <add key="aspnet:UseTaskFriendlySynchronizationContext" value="true" />
  </appSettings>
  <system.web>
    <compilation debug="true" targetFramework="4.8" />
    <httpRuntime targetFramework="4.8"/>
  </system.web>
  <system.serviceModel>
      <bindings>
          <webHttpBinding>
              <binding name="BookServiceWebHttpBinding"
                        maxBufferSize="2147483647"
                        maxBufferPoolSize="2147483647"
                        maxReceivedMessageSize="2147483647"
                        closeTimeout="01:00:00"
                        openTimeout="01:00:00"
                        sendTimeout="01:00:00"
                        receiveTimeout="01:00:00"
                        transferMode="Buffered">
                  <readerQuotas maxArrayLength="2147483647"
                                 maxBytesPerRead="2147483647"
                                 maxDepth="2147483647"
                                 maxNameTableCharCount="2147383647"
                                 maxStringContentLength="2147483647"/>
                  <security mode="None"/>
              </binding>
          </webHttpBinding>
      </bindings>
    <behaviors>
      <serviceBehaviors>
        <behavior name="BookServiceBehavior">
          <!-- To avoid disclosing metadata information, set the values below to false before deployment -->
          <serviceMetadata httpGetEnabled="true" httpsGetEnabled="true"/>
          <!-- To receive exception details in faults for debugging purposes, set the value below to true.  Set to false before deployment to avoid disclosing exception information -->
          <serviceDebug includeExceptionDetailInFaults="false"/>
        </behavior>
      </serviceBehaviors>
        <endpointBehaviors>
            <behavior name="BookServiceEndpointBehavior">
                <webHttp/>
            </behavior>
        </endpointBehaviors>
    </behaviors>
    <protocolMapping>
        <add binding="basicHttpsBinding" scheme="https" />
    </protocolMapping>    
    <serviceHostingEnvironment aspNetCompatibilityEnabled="true" multipleSiteBindingsEnabled="true" />
      <services>
          <service name="WcfService2.BookService"                   
                   behaviorConfiguration="BookServiceBehavior">
              <endpoint  address=""
                         binding="webHttpBinding"
                         bindingConfiguration="BookServiceWebHttpBinding"
                         behaviorConfiguration="BookServiceEndpointBehavior"                          
                         contract="WcfService2.IBookService">                  
              </endpoint>
          </service>
      </services>
  </system.serviceModel>
  <system.webServer>
    <modules runAllManagedModulesForAllRequests="true"/>
    <!--
        To browse web app root directory during debugging, set the value below to true.
        Set to false before deployment to avoid disclosing web app folder information.
      -->
    <directoryBrowse enabled="true"/>
  </system.webServer>

</configuration>