WPF Epplus export 10M+ items in excel with multiple sheets batch by batch

 

Install-Package Epplus;

 

 

private async Task ExportAsExcelCommandExecuted(object? obj)
{
    try
    {
        await PopulateStatusMsg($"Start exporting");
        exportedIdx = 0;
        var dg = obj as DataGrid;
        var items = dg.Items.Cast<Book>()?.ToList();
        batchCount = items.Count() % batchSize > 0 ? items.Count() / batchSize + 1 : items.Count() / batchSize;
        SaveFileDialog dialog = new SaveFileDialog();
        dialog.Filter = $"Excel Files|*.xlsx";
        dialog.FileName = $"Excel_{DateTime.Now.ToString("yyyyMMddHHmmssffff")}_{Guid.NewGuid():N}";
        if (dialog.ShowDialog() == true)
        {
            await Task.Run(async () =>
            {
                await ExportListDataAsync(items, dialog.FileName);
                await Application.Current.Dispatcher.InvokeAsync(() =>
                {
                    MessageBox.Show($"Exported file in {dialog.FileName}!");
                });
            });
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);                 
    }           
}

private async Task ExportListDataAsync<T>(List<T> dataList, string excelFileName)
{
    EPPlusLicense license = new EPPlusLicense();
    license.SetNonCommercialPersonal("Fred");

    using (ExcelPackage package = new ExcelPackage())
    {
        var props = typeof(T).GetProperties();
        for (int i = 0; i < batchCount; i++)
        {
            var tempBooksList = dataList.Skip(i * batchSize).Take(batchSize).ToList();
            var workSheet = package.Workbook.Worksheets.Add($"Sheet_{i + 1}");
            for (int j = 0; j < props.Count(); j++)
            {
                workSheet.Cells[1, j + 1].Value = props[j].Name;
            }

            for (int row = 0; row < tempBooksList.Count; row++)
            {
                ++exportedIdx;
                for (int col = 0; col < props.Length; col++)
                {
                    var value = props[col].GetValue(tempBooksList[row]);
                    workSheet.Cells[row + 2, col + 1].Value = value;
                }
            }
            // Format headers
            using (var range = workSheet.Cells[1, 1, 1, props.Length])
            {
                range.Style.Font.Bold = true;
                range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                range.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightBlue);
            }

            // Auto-fit columns
            workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns();
            await PopulateStatusMsg($"Exported {exportedIdx} items");
        }
        await PopulateStatusMsg($"Writing in {excelFileName} items");
        package.SaveAs(new FileInfo(excelFileName));
    }
    await PopulateStatusMsg($"Exported in {excelFileName} successfully!");
}


private async Task PopulateStatusMsg(string msgValue)
{
    await Application.Current.Dispatcher.InvokeAsync(() =>
    {
        StatusMsg = msgValue;
    });
}

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Install-Package Microsoft.Extensions.DependencyInjection;
Install-Package CommunityToolkit.mvvm;
Install-Package Epplus;

 

 

//App.xaml
<Application x:Class="WpfApp31.App"
             xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
             xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
             xmlns:local="clr-namespace:WpfApp31">
    <Application.Resources>
         
    </Application.Resources>
</Application>


//App.xaml.cs
using Microsoft.Extensions.DependencyInjection;
using System.Configuration;
using System.Data;
using System.Windows;

namespace WpfApp31
{
    /// <summary>
    /// Interaction logic for App.xaml
    /// </summary>
    public partial class App : Application
    {
        IServiceProvider serviceProvider;
        protected override void OnStartup(StartupEventArgs e)
        {
            base.OnStartup(e);
            var services = new ServiceCollection();
            ConfigureServices(services);
            serviceProvider = services.BuildServiceProvider();

            var mainWin=serviceProvider.GetRequiredService<MainWindow>();
            mainWin?.Show();
        }

        private void ConfigureServices(ServiceCollection services)
        {
            services.AddSingleton<IIDService, IDService>();
            services.AddSingleton<INameService, NameService>();
            services.AddSingleton<IISBNService, ISBNService>();
            services.AddSingleton<MainWindow>();
            services.AddSingleton<MainVM>();
        }
    }

}


//MainWindow.xaml
<Window x:Class="WpfApp31.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:WpfApp31"
        WindowState="Maximized"
        mc:Ignorable="d"
        Title="{Binding MainTitle}" Height="450" Width="800">
    <Grid>
        <Grid.RowDefinitions>
            <RowDefinition/>
            <RowDefinition Height="Auto"/>
        </Grid.RowDefinitions>
        <DataGrid Grid.Row="0"
                  ItemsSource="{Binding BooksCollection,Mode=TwoWay,UpdateSourceTrigger=PropertyChanged}"
                  VirtualizingPanel.IsVirtualizing="True"
                  VirtualizingPanel.VirtualizationMode="Recycling"
                  VirtualizingPanel.CacheLength="2,2"
                  VirtualizingPanel.CacheLengthUnit="Item"
                  ScrollViewer.IsDeferredScrollingEnabled="True"
                  ScrollViewer.CanContentScroll="True"
                  AutoGenerateColumns="False"
                  CanUserAddRows="False">
            <DataGrid.Columns>
                <DataGridTemplateColumn>
                    <DataGridTemplateColumn.CellTemplate>
                        <DataTemplate>
                            <Grid Width="{Binding DataContext.GridWidth,RelativeSource={RelativeSource AncestorType=Window}}"
                                  Height="{Binding DataContext.GridHeight,RelativeSource={RelativeSource AncestorType=Window}}">
                                <Grid.Background>
                                    <ImageBrush ImageSource="{Binding ImgSource}" Stretch="Uniform"/>
                                </Grid.Background>
                                <Grid.RowDefinitions>
                                    <RowDefinition/>
                                    <RowDefinition/>
                                </Grid.RowDefinitions>
                                <Grid.ColumnDefinitions>
                                    <ColumnDefinition/>
                                    <ColumnDefinition/>
                                    <ColumnDefinition/>
                                    <ColumnDefinition/>
                                </Grid.ColumnDefinitions>
                                <Grid.Resources>
                                    <Style TargetType="TextBlock">
                                        <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>
                                </Grid.Resources>
                                <TextBlock Text="{Binding Id}" Grid.Row="0" Grid.Column="0"/>
                                <TextBlock Text="{Binding Name}" Grid.Row="0" Grid.Column="1"/>
                                <TextBlock Text="{Binding Title}" Grid.Row="0" Grid.Column="2"/>
                                <TextBlock Text="{Binding Topic}" Grid.Row="0" Grid.Column="3"/>
                                <TextBlock Text="{Binding ISBN}" Grid.Row="1" Grid.Column="0" Grid.ColumnSpan="4"
                                           HorizontalAlignment="Center"/>
                            </Grid>
                        </DataTemplate>
                    </DataGridTemplateColumn.CellTemplate>
                </DataGridTemplateColumn>
            </DataGrid.Columns>
            <DataGrid.ContextMenu>
                <ContextMenu>
                    <MenuItem Header="Export As Excel"
                              Command="{Binding ExportAsExcelCommand}"
                              CommandParameter="{Binding RelativeSource={RelativeSource AncestorType=ContextMenu},Path=PlacementTarget}"/>
                </ContextMenu>
            </DataGrid.ContextMenu>
        </DataGrid>

        <TextBlock Grid.Row="1"
                   Text="{Binding StatusMsg}"
                   FontSize="30"/>
    </Grid>
</Window>


//MainWindow.xaml.cs
using CommunityToolkit.Mvvm.ComponentModel;
using Microsoft.Win32;
using OfficeOpenXml;
using System.Collections.Concurrent;
using System.Collections.ObjectModel;
using System.ComponentModel;
using System.Diagnostics;
using System.IO;
using System.Runtime.CompilerServices;
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 WpfApp31
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow(MainVM vm)
        {
            InitializeComponent();
            this.DataContext = vm;
            this.Loaded += async (s, e) =>
            {
                vm.GridWidth = this.ActualWidth;
                vm.GridHeight = this.ActualHeight / 2;
                await vm.InitPopulateAsync();
            };
        }
    }

    public partial class MainVM : ObservableObject
    {
        IIDService idService;
        INameService nameService;
        IISBNService isbnService;
        List<string> imgsList;
        int imgsCount = 0;
        int imgsIdx = 0;
        int batchSize = 1_000_000;
        int batchCount = 0;
        int exportedIdx = 0;
        private ConcurrentDictionary<string, ImageSource> imgDicCache = new ConcurrentDictionary<string, ImageSource>();
        public ICommand ExportAsExcelCommand { get; private set; }
        public MainVM(IIDService idServiceValue, INameService nameServiceValue, IISBNService isbnServiceValue)
        {
            idService = idServiceValue;
            nameService = nameServiceValue;
            isbnService = isbnServiceValue;
            MainTitle = $"Now is {DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.ffff")}";
            ExportAsExcelCommand = new DelCommand(async (obj) => await ExportAsExcelCommandExecuted(obj));
            InitImgsList();
            Task.Run(() =>
            {
                InitTimer();
            });
        }

        private async Task ExportAsExcelCommandExecuted(object? obj)
        {
            try
            {
                await PopulateStatusMsg($"Start exporting");
                exportedIdx = 0;
                var dg = obj as DataGrid;
                var items = dg.Items.Cast<Book>()?.ToList();
                batchCount = items.Count() % batchSize > 0 ? items.Count() / batchSize + 1 : items.Count() / batchSize;
                SaveFileDialog dialog = new SaveFileDialog();
                dialog.Filter = $"Excel Files|*.xlsx";
                dialog.FileName = $"Excel_{DateTime.Now.ToString("yyyyMMddHHmmssffff")}_{Guid.NewGuid():N}";
                if (dialog.ShowDialog() == true)
                {
                    await Task.Run(async () =>
                    {
                        await ExportListDataAsync(items, dialog.FileName);
                        await Application.Current.Dispatcher.InvokeAsync(() =>
                        {
                            MessageBox.Show($"Exported file in {dialog.FileName}!");
                        });
                    });
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);                 
            }           
        }

        private async Task ExportListDataAsync<T>(List<T> dataList, string excelFileName)
        {
            EPPlusLicense license = new EPPlusLicense();
            license.SetNonCommercialPersonal("Fred");

            using (ExcelPackage package = new ExcelPackage())
            {
                var props = typeof(T).GetProperties();
                for (int i = 0; i < batchCount; i++)
                {
                    var tempBooksList = dataList.Skip(i * batchSize).Take(batchSize).ToList();
                    var workSheet = package.Workbook.Worksheets.Add($"Sheet_{i + 1}");
                    for (int j = 0; j < props.Count(); j++)
                    {
                        workSheet.Cells[1, j + 1].Value = props[j].Name;
                    }

                    for (int row = 0; row < tempBooksList.Count; row++)
                    {
                        ++exportedIdx;
                        for (int col = 0; col < props.Length; col++)
                        {
                            var value = props[col].GetValue(tempBooksList[row]);
                            workSheet.Cells[row + 2, col + 1].Value = value;
                        }
                    }
                    // Format headers
                    using (var range = workSheet.Cells[1, 1, 1, props.Length])
                    {
                        range.Style.Font.Bold = true;
                        range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                        range.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightBlue);
                    }

                    // Auto-fit columns
                    workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns();
                    await PopulateStatusMsg($"Exported {exportedIdx} items");
                }
                await PopulateStatusMsg($"Writing in {excelFileName} items");
                package.SaveAs(new FileInfo(excelFileName));
            }
            await PopulateStatusMsg($"Exported in {excelFileName} successfully!");
        }

        private async Task PopulateStatusMsg(string msgValue)
        {
            await Application.Current.Dispatcher.InvokeAsync(() =>
            {
                StatusMsg = msgValue;
            });
        }

        private void InitImgsList()
        {
            var imgDir = @"../../../Images";
            if (!Directory.Exists(imgDir))
            {
                return;
            }

            imgsList = new List<string>(Directory.GetFiles(imgDir));
            imgsCount = imgsList.Count;
        }

        public async Task InitPopulateAsync()
        {
            BooksCollection = new ObservableCollection<Book>();
            List<Book> booksList = new List<Book>();
            for (int i = 1; i < 10000101; i++)
            {
                booksList.Add(new Book()
                {
                    Id = idService.GetID(),
                    Name = nameService.GetName(),
                    ISBN = isbnService.GetISBN(),
                    Title = $"Title_{i}",
                    Topic = $"Topic_{i}",
                    ImgSource = GetImgSourceViaUrl(imgsList[imgsIdx % imgsCount])
                });
                ++imgsIdx;

                if (i % 100000 == 0)
                {
                    await PopulateBooksCollectionAsync(booksList);
                }
            }

            if (booksList.Any())
            {
                await PopulateBooksCollectionAsync(booksList);
            }

            StatusMsg = $"Loaded completely,now is {DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.ffff")}," +
                $"loaded {BooksCollection.Count} items,memory:{GetMemory()}";
        }

        private ImageSource GetImgSourceViaUrl(string imgUrl)
        {
            if (!File.Exists(imgUrl))
            {
                return null;
            }

            if (imgDicCache.TryGetValue(imgUrl, out var img))
            {
                return img;
            }

            BitmapImage bmi = new BitmapImage();
            bmi.BeginInit();
            bmi.UriSource = new Uri(imgUrl, UriKind.RelativeOrAbsolute);
            bmi.EndInit();
            bmi.CacheOption = BitmapCacheOption.OnDemand;
            if (bmi.CanFreeze)
            {
                bmi.Freeze();
            }
            imgDicCache[imgUrl] = bmi;
            return bmi;
        }

        private async Task PopulateBooksCollectionAsync(List<Book> booksList)
        {
            var tempBooks = booksList.ToList();
            booksList.Clear();
            await Application.Current.Dispatcher.InvokeAsync(() =>
            {
                foreach (var bk in tempBooks)
                {
                    BooksCollection.Add(bk);
                }
                StatusMsg = $"Now is {DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.ffff")}," +
                $"loaded {BooksCollection.Count} items,memory:{GetMemory()}";
            }, System.Windows.Threading.DispatcherPriority.Background);
        }

        private string GetMemory()
        {
            var memory = Process.GetCurrentProcess().PrivateMemorySize64;
            return $"{(memory / 1024 / 1024).ToString("#,##0.00")} M";
        }

        private void InitTimer()
        {
            System.Timers.Timer tmr = new System.Timers.Timer();
            tmr.Interval = 1000;
            tmr.Elapsed += Tmr_Elapsed;
            tmr.Start();
        }

        private void Tmr_Elapsed(object? sender, System.Timers.ElapsedEventArgs e)
        {
            MainTitle = $"Now is {DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.ffff")}";
        }


        [ObservableProperty]
        private ObservableCollection<Book> booksCollection;

        [ObservableProperty]
        private string mainTitle;

        [ObservableProperty]
        private double gridWidth;

        [ObservableProperty]
        private double gridHeight;

        [ObservableProperty]
        private string statusMsg;
    }

    public class DelCommand : ICommand
    {
        private Action<object?> execute;
        private Predicate<object?> canExecute;
        public DelCommand(Action<object?> executeValue, Predicate<object?> canExecuteValue = null)
        {
            execute = executeValue;
            canExecute = canExecuteValue;
        }

        public event EventHandler? CanExecuteChanged
        {
            add
            {
                CommandManager.RequerySuggested += value;
            }
            remove
            {
                CommandManager.RequerySuggested -= value;
            }
        }

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

        public void Execute(object? parameter)
        {
            execute(parameter);
        }
    }

    public class Book
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string ISBN { get; set; }
        public string Title { get; set; }
        public string Topic { get; set; }
        public ImageSource ImgSource { get; set; }
    }

    public interface IIDService
    {
        int GetID();
    }

    public class IDService : IIDService
    {
        int idx = 0;

        public int GetID()
        {
            return Interlocked.Increment(ref idx);
        }
    }


    public interface INameService
    {
        string GetName();
    }

    public class NameService : INameService
    {
        int idx = 0;

        public string GetName()
        {
            return $"Name_{++idx}";
        }
    }

    public interface IISBNService
    {
        string GetISBN();
    }

    public class ISBNService : IISBNService
    {
        int idx = 0;

        public string GetISBN()
        {
            return $"ISBN_{++idx}_{Guid.NewGuid():N}";
        }
    }
}