Fast way of going from Excel to a DataSet or DataGridView

I have data that I'm taking from an Excel sheet with the ultimate goal of displaying in a DataGridView.

Currently I am just going over the UsedRange of excel data and plugging it into a datagridview. This works fine, but can get bogged down whe开发者_如何学编程n there 100+ rows of data.

I have also toyed with going from excel into a dataset (using the same wasteful method), and as expected it takes about the same time to load the data.

I was wondering if anyone had any information for a better way? Maybe use the XML from the Excel file?

EDIT:

Some Additional Information:

This is a WinForms application and the user will be picking and loading the excel file at run-time.

EDIT

The Return of Some Additional Information:

The Excel file is located on the user's pc. The general assumption is that they will be loading different files each time they use the application. [not sure if this helps, but might be good to know :)]

I faced this same problem recently...I wasn't able to really find an out-of-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 OLEDB and the other uses Excel Interop. From your question, I'm guessing you've been attempting to use an interop solution, and it's too slow. If all you want to do is read tables directly from Excel, where there is one table per worksheet, the OLEDB solution is probably cleaner and faster. If you ever get to the point where you need to write to an Excel file, though, you may find that OLEDB is just too limited. That was my experience for one project.

Anyway, I'll post a few bits and pieces from my OLEDB solution that will hopefully get you started:

    public void FillDataTables(DataSet dataSet)
    {
        var worksheetNames = GetWorksheetNames();
        foreach (DataTable table in dataSet.Tables)
        {
            if (!worksheetNames.Contains(table.TableName + "$")) continue;
            FillDataTable(table);
        }
    }

    public void FillDataTable(DataTable table)
    {
        using (var connection = ConnectionProvider.GetNew())
        {
            var adapter = GetNewDataAdapter(table, connection);
            adapter.Fill(table);
        }
    }

    private IEnumerable<string> GetWorksheetNames()
    {
        return GetSchemaTable()
            .AsEnumerable()
            .Select(dr => dr.Field<string>("TABLE_NAME"));
    }

    public DataTable GetSchemaTable()
    {
        using (var connection = (OleDbConnection)ConnectionProvider.GetNew())
        {
            connection.Open();
            return connection.GetSchema("Tables");
        }
    }

Here is the code for the ExcelConnectionProvider:

public class ExcelConnectionProvider : IConnectionProvider
{
    private readonly string _connectionString;
    private readonly string _workbookPath;

    public string ConnectionString
    {
        get { return _connectionString; }
    }

    public string WorkbookPath
    {
        get { return _workbookPath; }
    }

    public ExcelConnectionProvider(string workbookPath)
    {
        _workbookPath = workbookPath;
        _connectionString = GetConnectionString();
    }

    private string GetConnectionString()
    {
        var builder = new OleDbConnectionStringBuilder { Provider = "Microsoft.ACE.OLEDB.12.0", DataSource = _workbookPath };
        builder.Add("Extended Properties", "Excel 12.0 Xml;HDR=Yes");
        return builder.ConnectionString;
    }

    public DbConnection GetNew()
    {
        return new OleDbConnection(_connectionString);
    }
}

If you run into any problems with this compiling, try including the "System.Data.DataSetExtensions" assembly in your project.

EDIT: : IConnectionProvider is not needed -- it's an interface I added to my library because I have other connection providers besides Excel.

If you have the choice, I'd export the excel data to a database or xml file first (just once, not each time the app runs). Unless your users are updating this excel file on a regular basis, and want to see the results show-up in your app immediately.

Here's how to use Excel as a datasource (you probably already have it, but worth proofing, since it's just performance that's the issue for now): http://support.microsoft.com/default.aspx?scid=kb;en-us;311731

But yeah -- convert it to a sql-server table, or xml file before your app gets a hold of it, and you'll be a lot happier, I'd think.

Are these Office Open XML documents? If they were, you could use the System.IO.Packaging namespace or the Open XML SDK 2.0 to go through the data. This should be pretty fast since it's just working with XML files. Of course, if they're not OOXML, then this really isn't useful, and I apologize.