当前位置：首页 > news >正文

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

news 2025/10/9 19:50:28

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 headersusing (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 headersusing (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}";}}
}