OOXML PasteDataTable too slow
Posted on 2012-09-20
I am trying to find a way to place a DataTable set into an Excel sheet using OOXML.
I have tried using the PasteDataTable function, but this takes 3 minutes to paste 11,000 records. I know there must be a quicker way to do this - but uptil now I have not been able to find it. A lmgtfy answer will not suffice ;)
There must be a quicker way to do this.
I am using Office 2010 (Excel 2010) with OOXML, C#, Visual Studios 2010. The code will be in a webpart used in SharePoint 2010 to generate an Excel file based on information from an SQL server 2010 database.
The code calls a stored procedure which returns a DataSet. This DataSet contains one DataTable. I pass this DataTable to the PasteDataTable function of OOXML. It pastes it properly (not looking at formatting right now - will pick this up later) but it takes way too long.
Currently the excel file has a Data Connection which grabs the information from the database. As this excel sheet needs to be opened by many different users on many different computers I would rather not use this Excel data connection. As they get the Excel file from SharePoint I decided to create a webpart which will "Generate" the data into a new Excel sheet - and thus no longer needing the Data Connection.
Anyone have any ideas how I can make a function like PasteDataTable work just as fast as the original Data Connection in Excel (less than a second compared to 3 minutes)?