Export more than 100,000 rows from wpf datagrid to Excel - C# Microsoft.Office Interop 14
I have the following problem: in my WPF-application I do provide data in a datagrid and the user can export the grid`s data to Excel via an object array as it is described in this SO-thread. Well, it works fine as long as there are less than 100,000 rows to export. But I cannot export more than 100,000 rows. Then Excel won`t open, but I can see in task manager that an Excel process has been startet but is doing nothing and has around 5 MB assigned to it. However, my program ist not blocked and doesn`t report any exception. And it doesn`t matter how many columns there are - the limit seems to be 100,000 rows.
Further details that may be relevant: Tested environment: - Win 7/XP/8 (32 Bit) - Office 2010 - Microsoft.Office.Interop 14 - .NET 4.0 Client Profile
Althoug I`m offering the user a lot of filter options so he/she can easily export packets to excel files and put them together to one big file - but it should be possible to let it be done by the software as the Excel limit in 2010 is about 1,000,000 rows.
Well, in the end I couldn`t find out why 100,000 rows was the limit for export via Interop. I ended up using Open XML SDK with the help of the Open XML Powertools in a streaming approach as documented by Eric White. Work`s perfectly for any size (performance is 1,000 rows/second) and you don`t need the Interop. Just start the created XLSX-file by using the following line: