0 Replies - 8902 Views - Last Post: 14 February 2015 - 03:32 AM

#1 djjeavons  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 114
  • View blog
  • Posts: 417
  • Joined: 09-January 09

Read Excel File into DataSet

Posted 14 February 2015 - 03:32 AM

The following snippet of code will read an entire Excel file into a DataSet.

You will need the following two using statements:
using System.Data;
using System.Data.OleDb;



To use, add a button to a form and place the following into it's click event (this is just for testing purposes). Be sure to change the connection string to point to an Excel file on your system:
            string sourceConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\temp\source.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES'";
            OleDbConnection connection = new OleDbConnection(sourceConnectionString);

            //Open the connection to the spread sheet and read the Sheet Names (Tables) into a DataTable
            connection.Open();
            DataTable tables = connection.GetSchema("Tables", new String[] { null, null, null, "TABLE" });
            connection.Dispose();

            DataSet spreadSheetData = new DataSet();

            //Now that we have the tables, we can read all data from each and add to a DataSet.
            if (tables != null && tables.Rows.Count > 0)
            {
                foreach (DataRow row in tables.Rows)
                {
                    OleDbDataAdapter adapter = new OleDbDataAdapter(String.Format("SELECT * FROM [{0}]", row["TABLE_NAME"].ToString()), sourceConnectionString);
                    DataTable currentSheet = new DataTable(row["TABLE_NAME"].ToString());
                    adapter.Fill(currentSheet);
                    spreadSheetData.Tables.Add(currentSheet);
                    adapter.Dispose();
                }
            }



How it works
The above code uses the GetSchema method of the OleDbConnection to get a list of all Tables (work sheets in this instance) in the Excel file. Then armed with this information, it loops through these and for each one selects all data from that work sheet and places the data into a new DataTable which is then added to a DataSet.

To verify that this works, place a breakpoint at the end of the code file and examine the spreadSheetData variable by placing your mouse cursor over the variable name. Then click the magnifying glass as in the screen shot below:

Attached Image

You will find that it contains multiple tables each of which contains the data from that worksheet.

Attached is a sample application which demonstrates this technique further by allowing you to select an Excel file. Once selected, a combo box is populated with all of the tables. When selecting a table from the combo box, the data is then displayed in a DataGridView.

Attached File  GetExcelData.zip (55.12K)
Number of downloads: 268

Is This A Good Question/Topic? 2
  • +

Page 1 of 1