.net - Fast way of going from Excel to a DataSet or DataGridView -

I have data that I am taking from the final goal of displaying in an aggregate sheet in the DataGrid view.

Currently I am going to the range used for excel data and plugging it into Datagram view. It works fine, but when the data has 100+ rows, it gets trapped below.

I have run excel in a dataset (using the same method of idle), and as expected, the same time to load the data.

I was thinking that anyone has information in a better way? Maybe use XML from Excel file?

Edit:

Some additional information:

This is a WinForms application and user excel Select the file and load it at run-time.

Edit

Return of some additional information:

The excel file is located on the user's PC is. The general assumption is that whenever they use the application, they will load different files. [Not sure that it helps, but may be good to know :)]

I had to face this problem recently ... I was not really able to find an out-the-box solution that would do this for me, so I ended up writing some code by hand.

I actually have two different solutions in my code library: one uses the OLEDB and the second uses Excel interop, with your question, I guess you try to use the interop solution , And it is very slow if you want to do whatever you want, read tables directly from Excel, where there is a table per worksheet, then the OLEDB solution is probably cleaner and faster. If you ever reach the point where you have to type in the Excel file, however, you think the OLEDB is very limited. This was my experience of a project. Anyway, I will post some bits and pieces from my OLDB solution which will start your expectations:

  Public Zero FillDataTables (Dataset Dataset) {var worksheet name = GetWorksheetNames (); Forex Currency (DataTable Table in DataSet.Tables) {If (Worksheet.Compiled Resources (Table. Enabled Name + "$")) Continue; FillDataTable (table); }} Public Zero FillDataTable (DataTable Table) {(var connection = ConnectionProvider.GetNew ()) {var adapter = GetNewDataAdapter (table, connection); Adapter.Fill (table); }} Private IEnumerable & lt; String & gt; GetWorksheetNames () Get GetSchemaTable (). AEnumerableable () Select (dr = & gt; dr.Field & lt; string & gt; ("TABLE_NAME")); } Public Databble GetSchemaTable () {(var connection = (OleDbConnection) ConnectionProvider.GetNew ()) {connection.Open (); Return connection Gateskema ("Tables"); }}  

Here is the code for ExcelConnectionProvider :

  public class ExcelConnectionProvider: IConnectionProvider {Private redone string string; Private readonly string _workbookpath; Public string string string {get {return _connectionString; }} Public string workspace {get {return _workbookPath; }} Public ExcelConnectionProvider (string workspace) {_workbookPath = workbookPath; _connectionString = GetConnectionString (); } Private string GetConnectionString () {var Builder = New OleDbConnectionStringBuilder {Provider = "Microsoft.ACE.OLEDB.12.0", Data Source = _workbookPath}; Builder. Add ("Extended Properties", "Excel 12.0 XML; HDR = Yes"); Return Builder connection string; } Public DbConnection GetNew () {New OleDbConnection (_connectionString)); }}  

If you run into any problem with this compilation, try adding the "System.Data.DataSetExtensions" assembly to your project.

Edit: : IConnectionProvider is not required - this is an interface that I've added to my library because I have other connections than Excel Provider.